Tel: +44 7712 048925

Email: mike.booty@sorsis.com

© Sorsis Limited 2009-2024

Website last updated 29 Aug 2024

In our experience reporting tends to be an afterthought, stuck on the end of an implementation  with time and budgets at full stretch.  In recent times there has, however, been a huge push towards Business Intelligence.  Is it time to review the way that we report from our finance systems, to make the most of new technology?

The first step is to create a reporting database, preferably on another server, to hold the finance data in a structure that is tuned for reporting and is based on your structure.  A properly designed reporting database will produce results in a fraction of the time and should not cause contention with your live system.

The load process will take small increments of data from your finance system into your reporting system.  Typically this could happen every few minutes, with the process taking no more than a few seconds.  This provides you with an ‘up-to-date’ reporting database that can be used even through the month end process.

Creating cubes in Analysis Services from a properly configured reporting database is straightforward - trying to do it from an application database is not.  If you spend the time ensuring that your reporting database is fit for the job you should be able to create the cubes you need in no time.  

Creating pivot tables from a Business Analysis cube is easy.  Excel provides a visual drag-and-drop interface so that you can move fields from rows to columns and change selections from lists of data directly from your database.

In our view, the data for fixed format reports should come from stored procedures rather than from  tables or views.  This allows for all the aggregations and calculations  to be carried out by the database server (typically the most powerful machine available), and it reduces the size of datasets travelling across the network.

Even so, it is much easier to create the stored procedures from a reporting database than the application tables.

Executives long for pretty charts on their mobile phones, live from their finance data.  Give them what they want with Power BI, taking data from your reporting database.