Often when I talk to people about using the BI reporting features of Excel, particularly the new features that are part of Excel 2010, the initial reaction is one of trepidation and concern.  To them, Excel = Spreadmarts.

Spreadmarts are “spreadsheets gone wild,” with data sourced from various locations, and thus contain data of unknown origin, unknown age, and unknown veracity.   These spreadsheets then get passed around the organization, and no one knows what data is correct, what data to base key decisions on.

In my opinion, if you have spreadmarts, you are not using Excel properly.  As the internet meme goes, “You’re doing it wrong!”

In a typical scenario, people find that their standard BI reporting tool is too inflexible or slow, or they just are not as familiar with the interface.  People are naturally more comfortable with Excel, since it is so pervasive.  Consequently, users will extract the data they want using the “export to excel” option in their BI reporting tool, and then begin to slice and dice and analyze the data in Excel.  When new reports are created by several people – and these reports are shared – you have a spreadmart.   Data is no longer connected to the original source, and is no longer up to date.

Thankfully, the solution is very simple.   You need to ensure your Excel workbooks use a live connection, an Office Data Connection (.odc), to the source data.  Don’t store the data in Excel; store the query in Excel.  In essence, Excel always remembers the external query necessary for the last data and layout of your spreadsheet. Upon opening an existing spreadsheet, just use the Refresh All button in the Data area of the ribbon.   Excel will re-emit the necessary SQL to a relational source, and Excel PivotTables will re-emit the necessary MDX queries to your multi-dimensional cubes.

Simba’s MDX Provider for Oracle OLAP is a simple client-side “driver” that allows native Excel PivotTables to communicate directly to an Oracle OLAP database.   You have a Teradata Data Warehouse?  Teradata provides an MDX provider known as the Teradata OLAP Connector that allows direct connection from Excel to your Teradata Data Warehouse.   How about Kognitio WX2?   Just last week Kognitio announced Pablo, allowing Excel to connect directly against the Kognitio WX2 database.

Spreadsheets that have live connections to the database can be passed around within the organization, since the data is can be refreshed with a click and thus be guaranteed to be up-to-date.  In addition, this approach can ensure that appropriate security is maintained on the data, so users can only see the data they are allowed to see.

You don’t have to ban Excel in favour of your standard BI reporting tool, and you don’t have to throw out your BI reporting tool because your users are demanding an Excel interface.  Use Excel to complement your BI reporting tool set.  Your users will be happier, and you will have only one copy of the data, one version of the truth.