Posted on: Tuesday September 03rd, 2013

Author: Andrey

Introduction

In previous “Levels” we explored at a variety of tools that report directly off MYOB EXO ERP system.  This Level is about a more general information concept for a medium to large enterprise – Business Intelligence (BI).

Previously known as Decision Support Systems (1960s-1980s) or Executive Information Systems (1980s-1990s), Business Intelligence is a broad discipline that combines tools and techniques for using information to the advantage of an enterprise.  Sounds great, doesn’t it?  Just roll up the sleeves and take over the world with our reporting.  But on enterprise level there’s bound to be issues…

  • What if data resides in separate systems?

Only rare companies develop software systems in-house, most use existing products.  However, a combination of software products inside an organisation is quite unique.  So are business structure and rules.  Drawing data from multiple systems for a report may be a challenge.

  • Will transactional databases “like” analytical reporting?

Analytical reports tend to read orders across thousands of customers, months, products, etc.  They generate spikes of database activity as large amounts of data are summarised.  Running “heavy” reports during business hours may slow systems down.

The answer seems to be “yet another system” – an independent data store that combines all systems and can be used for reporting.…  Business intelligence practice suggests that “preparing” and “using” data should be done outside of business systems using an “architected environment”.  Such architected environment can range from a simple Excel based solution to multi-server platform combining data warehouse, data mart, OLAP cubes and some data mining.

Microsoft PowerPivot for Excel

Most data-savvy users “live” in Excel… and Pivot tables are their tool of choice.

However, Excel Pivot tables have limited processing capacity (a few million rows).  Since pivot tables do not reference data from more than one set, data from several systems has to be “staged” as a table and… VLOOKUP’ed.  Creating functions on large sets of data is also a challenge despite the ever growing capacity of our computers.

So the stage is set to introduce PowerPivot – a free add-on to Microsoft Excel 2010 and 2013.  It uses Excel as the interface and a special memory based engine to store, compress and crunch data.

Power Pivot

 

There are many advantages of PowerPivot over standard Excel Pivot tables.  Some of them are listed below:

  • PowerPivot can draw on massive data sets (as large as 100,000,000 rows) capped by 2Gb of compressed storage space in an Excel file.
  • PowerPivot allows “mashing up” data from various sources:  Excel tables, MS Access, SQL databases, SSRS Reports, other PowerPivot documents, ODBC, etc.
  • PowerPivot allows users create relationships between independent datasets and returns resulting data to a pivot table.
  • Formula engine is using DAX language that is similar to standard Excel formulas, but a lot more powerful.  There are functions like “year on year change as %”.
  • Solutions can be published on the corporate intranet website (Microsoft Sharepoint 2010 onwards) and upgraded to server based BI solutions (SSAS Tabular model).

The interface of PowerPivot is quite intuitive to work with and resembles Excel.  Each query with external data is loaded in a separate tab (Query1, Query2 in my example).  Tabs can be related to one another using “key” relationships (concept similar to joins).

Power Pivot Table

Power Pivot Table - Create Relationship

Once the solution is created, there are a few visualisation options to choose from.

Power Pivot Table Visualisation

End user experience is very similar to that of a pivot table, but multiple queries available for pivoting and extra features (like slicers) give it the distinct PowerPivot “flavour”.

Power Pivot Table - End User Experience

Up-market BI

A more comprehensive server based solution can be created using SQL server components of Microsoft SQL Standard edition and above: SQL Server Analysis Services (SSAS) and SQL Server Integration Services (SSIS).

First, a data warehouse – a separate database that contains amalgamated data from business systems, the architected environment.

Data warehouse is loaded from source systems using an Extract-Transform-Load process (ETL).  ETL is usually done via Integration Services:  data is extracted from source systems, staged, cleansed, normalised (sometimes lightly aggregated), audited and loaded into the warehouse.

Then the actual crunching…

There are two main vectors of BI reporting: analytical (i.e. investigation of existing data) and predictive (i.e. suggesting future trends based on past “experience”).

Analytical reporting is presented by OLAP cubes (SSAS 2005, 2008, 2012) and Tabular model (SSAS 2012).  In a nutshell, OLAP cube is a “pre-calculated” pivot table, while tabular model is server based PowerPivot.

Predictive reporting is using Data mining algorithms to find patterns and dependencies in OLAP cubes (SSAS 2005, 2008, 2012).  There is no predictive reporting out of tabular data… yet.

Both types can eventually be displayed through Microsoft Excel or Microsoft SQL Server Reporting Services.

Below a screenshot of my demo SSAS OLAP Cube for MYOB EXO Motopartz database.

SSAS OLAP Cube

 

And since it looks very much like a pivot table, same data can be provided to user via Microsoft Excel:

 

Adding Data From Other Sources

OLAP Line Total

Conclusion

Despite the overwhelming number of acronyms above, we barely scratched the surface of what business intelligence and Microsoft BI suite has to offer.

The architected solutions are custom built for client needs, size and systems in place.  Some are easy to build by an average super-user.  Some are an ongoing project to support and empower business users with insightful information.

Feel free to enquire with Kilimanjaro team or email myself andreym@k-c.com.au if you have questions on anything relating to this post or would like to discuss how Kilimanjaro can assist with your Business Intelligence ideas.

References

Go back to the beginning of the Reporting Series.