Sage ERP Accpac 6.0 Adhoc Query

A common complaint about Accpac today is the difficulty of getting the data out of the product. If there is a stock Crystal report then, great, you can get the data. If there isn’t a stock report that meets your needs then, typically, you need to hire your Business Partner to write a custom Crystal Report. Generally end users find Crystal Reports too intimidating to use on their own. Additionally if you take Crystal Report training, but don’t use the tool regularly, then you will lose the knowledge and have to re-learn it.

Many users use a combination of data export to Excel and printing reports to Excel to get their data into a tool they are more familiar with and then in Excel, sort and sum their data to get the numbers they need.

With the Adhoc Query tool we are looking to allow users to get at their data quickly and easily without jumping through all these hoops. We want to provide an extremely simple interface that anyone can use to query their data. No one will need to know anything about the Accpac database schema or about advanced formatting with groups, headers and footers. As a consequence this tool will give simple but powerful methods to sort, sum and choose your data. A simple way to preview, print and export the data. It will not allow fancy features to make the interface too complicated for occasional users. We are being very strict on keeping the interface simple and accessible. Hence the power will be in the accessibility of the data rather than in the formatting of the data.

Basically we define a number of “data domains” such as “A/R Customers and Transactions”. You choose which data domain you wish to query and then we give a simple interface where you can specify which data you wish, how you want it sorted and summed and then see the data right away instantly in a table control. Then we also give you easy ways to export, print, and email the data to anywhere else that you might need it.

Below is a screen shot of the current Ad Hoc Query tool returning all customers with their name containing the word “Mr.”.

As you can see from the screen shot, the interface is very simple. This is our main objective for this tool, that anyone can use it. But at the same time we want to ensure that you can get at the data you need easily. This is partly why we called it “Adhoc Query” rather than “Adhoc Reports”, because we didn’t want to set the expectation that this was all about formatting. It is in fact all about the data and hence the name.

The Sage ERP Accpac 6.0A Portal

The Sage ERP Accpac 6 Portal is the new Desktop that runs in a browser. Sometimes the word Portal beings up connotations of other things, I’m not sure if a better name is Landing Page, Home Page, Web Desktop, or Start Page. But when you run Accpac 6, this is where you will begin. The primary goal of this Portal is to allow you to navigate into the Accpac application as quickly and intuitively as possible.

When you login to the new Portal, rather than being presented with a tree of icons, you get a dashboard of information useful for doing your job. You can start work by drilling down from the dashboard to get to more detailed reports and inquiry screens. You can run screens by using the “My Shortcuts” area to run frequently accessed screens. Then if you really need the full tree of icons it is available under the Tasks menu.

Customization of the “Home” page and “My Shortcuts” area has been mad as easy as possible, so that any user can easily set these up in an optimal way for their role.

As you run additional tasks, new tabs are opened and you can switch back and forth between them.

Reports still use Crystal Reports but show the results using the Crystal web view in a separate tab in the Portal.

Hopefully this gives an idea of where we are going with the new Sage ERP Accpac 6 Web Desktop/Portal/Home Page. We feel we have come a long way from our original desktop, adding a lot of usability and making the product far more friendly to new and experienced users.

SData in Sage ERP Accpac 6

One of the big features in Accpac 6 is SData support. What is SData? SData is a web services protocol based on REST. What is REST? REST is a web services protocol that is very popular among Internet companies. There are basically two main competing standards for web services. One is SOAP which is promoted by Microsoft, Oracle and IBM; this is a fairly heavy duty protocol which requires a fair bit of infrastructure (which is provided by the companies promoting this protocol). The other is REST which was invented as a University Research project and basically uses existing Web Technologies such as HTTP and RSS to implement a web services protocol. The nice thing about REST is that it doesn’t require any extra middleware. You are basically doing everything via standard web URLs. The infrastructure that supports this is basically just the standard Internet. SData is just an extension to REST, similar to Google’s GData which is also based on REST. REST protocols are used by Google, Amazon, eBay, Yahoo and all the main Internet companies. Gartner estimates that 75% of web services in use on the Internet are REST based. REST/SData also provides a standard mechanism for performing CRUD (create, read, update, delete) on all records.

From Accpac, every Accpac View (or Business Logic Object) will be exposed via SData. This provides third party ISVs a complete, efficient Web Services interface to communicate to Accpac with. Also all Sage applications are being made SData aware, meaning that all Sage applications can manipulate SData Web Services (or feeds). For instance here is some Accpac A/R Customer and G/L Account data displayed within a Sage CRM Dashboard using SData.

From a simple wizard you can select any Accpac SData feed and once you’ve chosen a feed, you can select which columns you wish to display. Additionally Sage CRM and Accpac will be converting to use SData for our integration needs, this transition will occur over several versions of both products. In the same way you will be able to access CRM data from Accpac using SData.

All the new Sage ERP Accpac 6 screens are written entirely using SData. This means the screen can access any Accpac Views via SData, but additionally they can access any SData feeds from any product. In Accpac 5.x, the screen controls were mapped via datasource controls to views and view fields. This was great, but it limited you to only connecting our standard controls to fields in Views developed in our SDK. Now that the controls are mapped to a standards based SData fields, it means they will be able to be connected easily to much more data than just Accapc data. Look for other Sage products to start providing SData interfaces in their coming versions.

Sage is publishing SData as a publicly available standard. You can read all about it at: http://sdata.sage.com/.

Declarative Programming in Sage ERP Accpac 6.0A

In the Sage ERP Accpac 5.x series of product, all screen definitions were part of the Visual Basic programs that made up each user screen. The only way to get at these screen definitions was to embed the OCX for that screen in a VB program or VBA macro and then manipulate the screen programatically. With Accpac 6.0, we wanted to make many screen customizations way easier. The solution was to separate the screen definition from the User Interface program. In Accpac 6.0 each screen is defined in an XML file that contains all the layout information. This screen can be edited in a Visual Screen Designer that is part of the product, or since XML is just a text file, it can be edited in a standard text or XML editor. For that matter the screen can be generated from another program. The definition of the screen layout file (xsd file) is available and the format is completely open.

In Accpac 5.x, we saved a lot of programming time by binding the individual UI elements to datasources. This meant that an edit control was actually attached to a View field via its properties in the VB form designer. Then the control would be data aware and handle setting its data into the view and retrieving updated data from the View. In this way, no programming was required for most of the forms on the screen. We have exactly the same concept in Sage ERP Accpac 6.x. Only each control is bound to a field with in an SData feed and that SData feed could theoretically come from any Sage application that provides SData feeds or from any other application that has adopted the open SData format. Thus again no programming is required for most fields on the form.

Then we have gone one step further and provided standard actions that can be attached to controls in the layout. In Accpac 5.x you had to code all the logic that happened when a button was pressed. In Accpac 6.x we provide standard actions, so for instance you can attach a save action to the save button which will perform the save operation on the configured SData feed. This way we can develop simple forms with navigation, save, new and delete operations, with no programming. The declarative layout actually produces a complete running program can run with no additional code. Many of the Accpac setup and other simple screens can be developed this way, greatly speeding the development of Accpac 6.x.

The layouts for Accpac 6 are quite different from the screens in Accpac 5.x. For one thing all controls were placed on 5.x screens at specific x,y co-ordinates. In Accpac 6, all layout is handled by browser document object model layout nodes. Nothing is specified in x,y coordinates, you just specify which controls line up and how and then the process is dynamic depending on the length and size of the text. This is a great benefit when translating into languages that take more space like Spanish since the form will automatically adjust to the longer text. When running a right to left language like Arabic, it’s even smart enough to be able to re-arrange all the controls into a right to left ordering. All this is just functionality built into every browser and something we gladly don’t have to worry about.

These are just some of the new things that declarative layouts are bringing to Sage ERP Accpac 6.0A. This will really be a strong and flexible foundation to develop on.

On Agile Sprint Reviews

On part of the agile scrum development process I like are the regular sprint reviews. Basically in Scrum, you operate on a really short mini-release cycle called a sprint. Our sprints are 3 weeks long and on the last day of the sprint we hold a sprint review, which is a presentation on what was accomplished during the sprint. We have seven agile teams which all operate on the same sprint calendar. At each sprint review meeting, each team gets 15 minutes to present what they accomplished in the sprint. Attendance of this meeting tends to be quite high and diverse including all the sprint team members, people from Product Management, Support, Sales, etc. It is a really good way for everyone in the company to get a view to how development of the next release is going and provide feedback.

The best demos in the sprint review are of running the real product and accomplishing real user tasks (or stories). But sometime the demos can be of low-level processing as seen from the debugger or SQL analyzer, user centered designs, test cases, etc. The sprint review is a great motivator for the teams to finish stories and to be able to present them. There is a lot of competition between the teams to show the most progress and to present the most amazing demos.

The sprint review is just one small aspect of Agile programming, but it acts as a true focusing force to bring the product back together at the end of each sprint and to motivate the teams to finish their stories in time for the sprint review. The couple of days before the sprint review can be quite stressful for the teams as they try to finish things up. Remember Agile development has quite a strong “doneness” criteria which must be met before something can be demo’ed in the review. Any story that is demo’ed must be “done”, that means it must be fully finished, meaning fully tested and accepted by the product owner. If the story isn’t “done”, then it isn’t finished and can’t be demo’ed. It must be left to the next sprint and the team won’t get credit for that story until it meets the full “doneness” criteria.

Amazing Google Street View

I remember when SQL Server 7 was nearly ready for release, Microsoft research had a project to make a 1 terabyte database. Their project was to server up satellite images of anywhere on Earth. Basically they had 1 Terabyte of images, so that was their terabyte database. I didn’t think this was a realistic terabyte database since their weren’t that many records, just each one was an image and quite large. Anyway they put the thing up on the web as a beta, and as soon as a few people tried it, the whole thing collapsed under the load.

A year or two later Google launched Google Earth, which basically did the same thing, only more detailed. But Google Earth can easily handle the load of all the people around the world accessing it. Why the difference? Why could Google do this and Microsoft couldn’t? I think the main difference is that Microsoft hosted it on one single SQL Server and had no way to scale it besides beefing up the hardware at great expense. Whereas Google uses a massively distributed database running on many many servers all coordinated and all sharing and balancing the load. Google uses many low cost Linux based servers keeping costs down and performance high.

This week Google released Google StreetView for major Canadian cities including Vancouver, where I live. So I can virtually cruise around Vancouver streets with very good resolution including seeing my house and neighborhood. This is really amazing technology. Rather than just panning around a patchwork of satellite images, we are actually navigating in 3D around the world. Suddenly Google has produced a virtual model of the entire world at quite good photographic quality.

Think about the size of this distributed database with all these photos, plus all the data to allow them to be stitched together into 3D Views that you can navigate through. This is so far beyond Google Earth, it’s really amazing. Is this the first step to having a completely virtual alternate Earth? If you are wearing 3D goggles, will you be able to tell if they are transparent or viewing these images?

I think we are just seeing the first applications of what is possible with these giant distributed databases. I’m really looking forwards to seeing some really amazing and mind blowing applications in the future. The neat thing is that Google is starting to open source this database technology so others can use it. Are SQL databases just dinosaurs waiting to be replaced? What will be able to accomplish in our business/enterprise databases and data warehouses one we start apply and using this technology?

Diversity in Mobile Phone Platforms

There seems to be an explosion of mobile phone platforms these days. Mobile phones is certainly where the growth is. The number of people regularly using them is expanding, plus people tend to break, lose or get stolen phones, that the replacement cycle is very fast. We have a number of legacy platforms like Symbian, Microsoft Windows Mobile, Motorola. Then there are a great number of new contenders including Apple’s iPhone, Google’s Android, RIM’s Blackberry, Intel’s Moblin and Palm’s Pre. Then there are dozens of smaller players all contending for developer’s attention.

Each of these systems has its own SDK (whether closed, proprietary or open) where you can write applications for the particular platform. Then if you can certify your application you can get it sold through some sort of proprietary app store. So as an application developer, do you just choose one platform and lock yourself into that and live with the risk that the vendor can kick you off the app store on a whim if they think you are competing with them or annoying a major cell phone carrier? Or do you develop the same application over and over in each SDK? Doing this is a lot of work and you can still get kicked out of major markets.

So what to do? Each of the major platforms now comes with a good browser (ever built in or via an add in). Google as Chrome, Apple has Safari, etc. These cell phone mobile browsers can browse and run most AJAX/JavaScript/HTML based applications. This gives a point of standardization that is outside the control of the cell phone platform software vendors, hardware vendors and cell phone carriers. If you write your application using web based standards then you can run in the browser on all these cell phones. All you need to do is make sure you application can render the screens nicely for commonly available screen sizes and allows varied input mechanisms.

Now doing this you don’t have access to the cell phone hardware, so you can’t initiate calls and can’t access the GPS (at least yet). But you still have open to you a wide range of things you can do. Additionally you are writing an application that runs on any hardware, not just cell phones, so your application can be accessed from Windows PCs, Linux PCs, Apple PCs, Game Consoles, WiFi devices (like iPod touch or PSP), and many more.

It seems that as hardware platforms are diversifying, it doesn’t make sense to develop for a single platform’s SDK anymore whether its Windows or iPhone. It seems like the cost effective way to get the widest audience is via open web standards. The standards based JavaScript/HTML world as evolved to the point were you can write extremely rich client experience applications without the use of any proprietary SDK. Java tried to get us to a write once, run anywhere world, but failed. Now AJAX/JavaScript/HTML has gotten us there.

Learning About Agile

For Sage Accpac ERP development we adopted the Agile Scrum development methodology back in June. Since then we are now on Sprint 5 and learning all sorts of things about scrum and agile as we roll out and practice this methodology. There are quite a few aspects to agile, but one of the key ideas is to relentlessly breakdown large tasks into smaller manageable tasks. Then to finish those tasks completely, don’t let them drag on, say code now and leave it to be tested three months later. For us we’ve realized quite a few immediate benefits:

1. Teamwork. Our agile teams are typically 6 to 12 people (most close to 6). This fosters excellent teamwork and shared purpose for the team members. It is also far easier to manage such a team, than say manage 40 people working on the same thing. The teams are also cross functional, so it aligns programming, quality assurance, user centered design and documentation together on the same team, rather than being members of separate departments with their own agendas.
2. Sprints. By running in three week sprints, it forces us to keep the product integrated and greatly helps preventing large numbers of bugs accumulating that need to be cleaned up later.
3. Better estimation. By forcing us to break tasks down to take less than one sprint, it forces us to fully decompose tasks and greatly increases our estimating accuracy in the process.
4. Better release planning. The agile release planning process is far superior to the older waterfall methods, with all their hand overs. With agile release planning you create your story backlog and when planned this is what you directly act on and track, without an conversion processes.
5. Better project tracking. Tracking agile stories and story points and measuring team velocities is far easier than traditional project management techniques which track by days or hours with all their complicated formulas.
6. Pushing decision making down to the teams. This has greatly eliminated roadblocks and enhanced teamwork. The teams make their own decisions and are responsible and accountable for the results. Works far better than relying on a Directory or Manager for decision making.

When adopting any new process there is some pain as you switch from what you know and what is tried and trusted to something more unknown. But it’s really a matter of having the patience to give the new process a chance. To see it working and to learn to trust it. One of the harder things to accept for Agile is the concept of not designing things until they are needed. There is an element of trust and confidence here that you will be able to successfully do this when the time comes. That everything isn’t laid out at the beginning of the project.

Amazing CGI

I saw District 9 this weekend. Quite a good movie. Memorable CGI generated aliens “the prawns”. Quite an improvement from aliens that are clearly people with lots of makeup and fur suites, quite an improvement over using muppets and other robots/puppets. The aliens appear to move naturally, they have insect like mouth parts that are constantly moving and bodies that clearly an actor couldn’t fit into. Good work to the Vancouver company Imagine Engine that created them. The aliens fit right into the film and are completely realistic.

The I was blown away by the trailed for Cameron’s new movie Avatar which is coming in December. Again huge amounts of CGI creating a truly alien but beautiful planet and creatures. Really amazing how realistic these imagined worlds are becoming.

Even on standard PCs today with relatively inexpensive graphics cards from NVidia or AMD, its amazing the level of realistic graphics you can get in modern computer games. Each frame in the movies mentioned above might take hours to render to get the desired quality, but computer games today aren’t far behind and rendering 30 frames a second on current graphics co-processor cards. These cards often have 1 gig of their own memory and hundreds of parallel processors doing all the 3D calculations.

It looks like with modern movie making technology, truly whatever can be imagined can be created. Currently it might be limited to big budget productions cost $100 million to make. But prices keep coming down, techniques keep getting cheaper. Next we’ll see movies less expensive to produce, we’ll see this technology incorporated into video games. Should be amazing to see the crop of movies that start appearing over the next few years.

Frenzy in the WWW

Seems to be a lot happening in the web these days. Microsoft Bing goes live, Google goes into a frenzy of upgrades to their search engine. Microsoft makes a deal with Yahoo. Facebook buys FriendFeed. Twitter downed by a denial of service attack. Everyone frantically trying to be the ultimate search/social networking/communications service.

I received by test account for Google Wave today. The first thing that struck me, was that I would love it to be connected to FaceBook or LinkedIn, so I actually have someone to communicate with. Great tool, but the trick would be adoption. I think if any social networking site had developed this, it could really take off. Not sure how Google will manage alone. Maybe they’ll make a deal with Twitter, Facebook, Myspace, LinkedIn or someone. Someway to really spiff up the communications features of these sites, which tend to be a bit lacking.

It seems Microsoft unveiled Bing to much fanfare and have been running a massive number of TV commercials promoting it. But it seems that it is already standing still as everyone surpasses it. Google has already upgraded their search a couple of times, adding features Bing promises, but doesn’t deliver yet. Not sure if Microsoft understands how to compete in the Internet world. Still running on quite long software development life cycles, rather than operating in Internet time where product updates can be quickly rolled out.

Meanwhile Microsoft is playing Pepsi to Apple’s Coke in the music player category with Zune trying to compete with the iPod. I think they are starting from so far behind that they really don’t have a chance. Combine that with the power of the iPod/iPhone application store and they don’t seem to have much chance.

It seems online office productivity tools like word processing  and spreadsheets are quickly moving to the web. Probably much quicker than anyone anticipated. Meanwhile laptop prices continue to fall through the floor. Can now get good Linux based laptops for $200. Not much room in that price for the Microsoft Windows tax. With no real demand for office, these make a lot of sense now. The newer laptops can’t even run Windows since they are based on ARM CPUs.

Anyway all these developments, competitions and change make life interesting. Good time to be in the computer industry.