Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘sage alchemex

Sage Intelligence Go!

with 4 comments

Introduction

A new flavor of Sage Intelligence was demonstrated by Himanshu Palsule during his keynote at Sage Summit called Sage Intelligence Go! (SIG). I thought I’d spend a bit of time this week providing a few more details about what it is and where it fits into the scheme of things.

Sage Intelligence is a business intelligence/reporting tool used by many Sage ERP products. For those who have been around the Sage 300 ERP community, they will recognize it as Alchemex which Sage purchased a few years ago. This is a product that runs as a Microsoft Excel add-in which extracts data from the ERP to be manipulated by the power of the Excel spreadsheet engine. This is a very popular way of doing Financial Reporting since you often want to present the data graphically or you want to perform complex calculations or you want to slice and dice the data using pivot tables. Excel provides a great platform for performing these tasks. The original Financial Reporter bundled with Sage 300 ERP works in a similar manner as an Excel Add-in. Sage Intelligence is used for quite a few things beyond Financial Accounting including Sales Analysis and other predictive type BI functions.

Sage Intelligence has been around for a while and is a good mature product. Since it is written with the full Excel add-in SDK, it must run in Excel and specifically the locally installed version of Excel. This means it isn’t particularly well suited for cloud applications such as Office Online.

Microsoft has now released a newer SDK for developing Office apps. This new SDK is designed to allow you to develop applications that can still run in the locally installed full Microsoft Excel, but they can also run inside the cloud/web based Excel Online as well as the new specialty versions of Office like the one for the iPad.

As Sage ERP applications move to the cloud, you would want to have your Financial Reporter be cloud based as well. You would want to be able to edit Financial Reports in the browser as well as display and interact with reports on any device including your tablet or smart phone. This is where Sage Intelligence Go! comes in. It is written in the new Office Apps SDK and will run on the online and device version s of Excel as well as the full locally installed Excel. This you don’t need a Windows PC at all to use your cloud based ERP and cloud based Financial Reporter. However you still have all the power of Excel helping you visualize and manipulate your reports.

sig1

Sage One Accounting

Sage One Accounting by Sage Pastel is such a cloud based ERP system. This product has Sage Intelligence Go! as an option for performing various reporting needs. When SIG started out here, it was much simpler. The original Office Apps SDK was very simple and didn’t nearly have the power of the various older SDKs supported by local Excel. However as time as gone by, the Office App SDK has become much stronger and the functionality is now much more in line with what we expect of such a solution.

Office Apps

For those of you who attended Sage Summit and saw James Whittaker’s keynote, they would have seen the importance Microsoft is placing on the new Office Apps. Basically to switch from using the browser and search, or using Apps from a mobile device App store, you will get your data directly in your business applications via this new sort of App. If you go to the Office App Store, and search on Sage you will find a Sage One app for Outlook and Sage Intelligence Go! If you search a bit more broadly, you won’t find that many non-trivial applications. Sage Intelligence Go! is probably the most sophisticated Office App in the store.

Notice that you don’t run SIG reports from the ERP application, they run from Excel. Once you have the XLSX file, you can just run it anytime from any version of Excel and have full access to your data. This is really a new paradigm for doing reporting. For SIG this works really well. Whether other applications fit this model is yet to be seen.

Web Services

SIG needs to communicate both with the Office API (whether cloud or local) as well as with the ERP to get the data to process. Both of these functions are accomplished via RESTful web services. The communication with the ERP must be via Web Services since these could originate from the SIG Office App running in the Microsoft Office Online Cloud or from the Office App running in the local Excel. It isn’t possible to use the traditional methods of database integration like ODBC when the two may be running in completely different locations.

Basically the Sage Cloud based ERP exposes a standard set of Web Services that are protected by Sage ID. When the user starts SIG, they get a Sage ID login prompt from with-in Excel and then this is transmitted through to the Sage Cloud ERP which uses the Sage ID to look up which tenants this user runs as. This information is related back to SIG in case it needs a second prompt to act which tenant (company) to access.

Sage Intelligence Go consists of a server component that runs in the cloud as well as the Office App that runs in Excel. The Server portion of SIG uses the provided Web Services to load the data to be reported on from the ERP into its in-memory database for processing. This in-memory database is maintained in the cloud where the SIG service runs. Then the Office App part of SIG interacts with the server side to present the correct required data and perform various processing functions.

sig2

ERP Integration

This solution requires the ERP provide Web Services that are exposed to the Internet in general, so that it can be access via Excel running anywhere whether installed locally, running on an iPad or running as a Web Application in the cloud. This means these Web Services need to be secure and available 24×7 with very good availability. For this reason you will see SIG first integrating to Sage Cloud based ERPs (like the current Sage One Accounting by Sage Pastel) and later via the Sage Data Cloud which will offer these services on behalf of on-premised installed ERP systems.

Summary

Sage Intelligence Go! is our solution for cloud reporting. Its primary purpose is to provide Financial Reporting capabilities, but it is also capable of handling other BI type reporting needs. This is our solution to moving a lot of reporting functions into the cloud.

sig3

 

 

Advertisements

Sage Intelligence 7.4

with 11 comments

Introduction

With our upcoming Sage 300 ERP 2014 version we will be integrated with Sage Intelligence Reporting 7.4. Within our product this will also be labeled as version 2014. This is a fairly major upgrade to the Sage Intelligence package and definitely worth a look. There have been a great many feature, usability and performance enhancements throughout the product.

This product is integrated to many sister Sage ERP packages and this is where Sage is investing in improving Excel based reporting. For ERP a big usage of this is for Financial Reporting, but don’t forget this is a useful tools for many other applications like Sales Analysis.

Financial Reporting

Many of our Sage sister products used Microsoft FRX as their financial reporter. This product has now been discontinued by Microsoft. To provide a replacement we spent quite a bit of time adding features contained in FRX into Sage Intelligence. Sage 300 never used FRX, but we now benefit from all this work with better Financial Reporting from Sage Intelligence.

For Sage 300 ERP installed on-premise, it still includes the Financial Reporter bundled with General Ledger. This F/R is still there and supported. We just added support for Excel 2013 which was a major job due to the changes in Excel from a multiple document interface (MDI) to a single document interface (SDI) along with Excel now having data execution protection (DEP) turned on. We understand that people have invested a lot in developing many powerful reports with this technology and are quite happy with how it works.

For the Sage 300 Online product we have removed this Financial Reporter and are only hosting Sage Intelligence for Financial Reporting. With this cloud offering we are looking to the future. With version 1.0 of this product we are running the regular desktop version of Excel to work with Sage Intelligence. But this isn’t really what we wanted. As a cloud offering we wanted to integrate to the cloud version of Excel, namely the browser based Excel 365 version. However the API for this product isn’t sufficient yet to do sophisticated Financial Reporting. Once Excel 365 develops sufficient power, we will be integrating Sage Intelligence to this and allow cloud based use of Office 365. By using Sage Intelligence it means that when we switch from the local installation of Excel to using the cloud version, all your report will continue to work and won’t need to be re-written. For the Sage 300 built-in Financial Reporter it will only continue to work with the local installed version of Excel. As a result of this besides adding features needed for FRX users, we have also added functionality to Sage Intelligence to make it more comparable to the built in Financial Reporter.

Performance

We now load all the data into memory at the beginning and then do all reporting from this in-memory database. This way as you change reporting options and interact with the report, you get far better performance than before. No more re-reading things from the database as you go along.

Excel Support

Sage Intelligence 7.4 now supports running Excel 2013 both 32 and 64 bits. For Sage 300 ERP the built-in F/R supports Excel 2013 but only running in 32 bit. If you absolutely need 64 Bits then give this solution a look. However MS does install 32 bits by default now for Excel 2013 due to all the compatibility problems with the 64 bit version (it isn’t only us). Similarly Sage Intelligence also no uses .Net framework 4 which is better supported and avoids installing old versions of the .Net runtime.

Screenshots

Let’s look at a few of the new things in the product. First we’ve modernize the toolbar in the Report Manager to the Office ribbon style. We’ve also fixed up the wording and hints to make this a bit easier to use.

sai4

As part of providing an out of the box Financial Reporting solution for Sage 300 ERP, we’ve added a number of sample reports that match the Financial Reports bundled with our built in F/R. Below is he home page for the Financial Ratio report:

sai1

And then the actual report. Note that there are a few more ratios here than in the built-in report along with graphs and such.

sai2

For the Balance Statement and Income Statement reports each three are incorporated into one Sage Intelligence report, each on a separate tab. One of the income statements is shown below.

sai3

The new Report Designer boasts a fresh new User Interface to supplement a powerful generation engine utilizing the Report Designer In-Memory technology allowing for improved performance and usability. This tool makes it very easy to create new powerful Financial Reports without having to edit every Excel cell by hand. Automated layout generation for a single company:

sai5

Plus screens to specify your account ranges and labels:

sai6

Summary

Sage 300 ERP 2014 will be bundled with Sage Intelligence 2014 which is version Sage Alchemex Intelligence 7.4. This is a fairly major release which greatly increases the power of Sage Intelligence as a Financial Reporter. It adds many features to be comparable to both FRX and to the built-in F/R from General Ledger. This version supports Excel 2013, including the 64 bit version and this is the product that we will be adding cloud based Excel 365 support to.