We sometimes get asked what is the advantage of using Excel PivotTables for BI against an OLAP source vs. pulling or pasting in a bunch of relational tables to the workbook and doing the pivot yourself?
Some users find Microsoft Excel’s PivotTables challenging, and whole books have been written about the subject. However, they are challenging only if you must to find and import fact and multiple dimension tables, then carefully define the relationships between the fact and dimension tables yourself for the pivot.
In contrast, almost no expertise is required to use an Excel PivotTable against an OLAP source. Your IT staff will give you an Office Data Connection (.odc) file, and you use it to connect hot to the OLAP source. All you have to do is decide whether to drag say a year/month dimension to rows or columns. You can then filter, slice, dice, and drill using simple GUI elements (e.g. to drill to the months of 2009, click on the ‘+’ node to the left of 2009). Excel just naturally knows about underlying OLAP cubes. And only the visible summary data is stored in the spreadsheet. When you re-open and operate on it, Excel goes back to the OLAP source. Finally, you don’t have to save your query; the PivotTable is itself the easily (visually) constructed query.
Some of the advantages of OLAP vs. tabular sources are:
- Less user expertise required.
- Smaller Excel files, and thus little wait for otherwise large Excel files to initially open.
- Faster refresh response time; only the summarized data need be re-obtained rather than extremely long tables.
- Faster query performance as the OLAP server does most of the heavy aggregation work.
- Likely “one version of the truth” as the OLAP data is maintained by IT in the OLAP server.
You thus have end-user, ad hoc BI, but IT makes sure the data is timely and secure. In contrast, if importing/pasting in many fact and dimension tables, you may be tempted to get tables from all sorts of places. You have to ensure that each of those places are updated with new data. And you must make sure that every fact and dimension table in your workbook is a refreshable connection (rather than a one-time import or paste into the workbook).