Some Excel BI myths debunked – #5 Real-time data exploration

#5: Lack of Real-time data exploration

The argument is often made that Excel is too inflexible to answer spur of the moment questions quickly and effectively. The scenario given is that you’re in a meeting with your Excel workbook, and someone asks a related question not currently accounted for. How embarrassing to have to look at your back up folder of printed Excel workbooks… Really? It may escape the attention of some, but Excel is actually an electronic product too so as a first point you wouldn’t need to dust off an enormous binder of printed reports.

That aside, the overall argument has some merit in specific cases. If Excel is acting as both the datastore and the presentation tool you have a problem. If the data you need isn’t in the workbook, you’re bang out of luck.

There are two key requirements to address the issue in Excel. Firstly the data needs to be stored outside the workbook; in the case of XLCubed that’s in AS cubes or tabular models. This means when the data isn’t currently visible in the workbook it can still easily be queried and brought into play.

Secondly, while it’s a huge step having the data in a cube, that in itself isn’t enough. You need to be able to get it out quickly, easily and flexibly and to display it as information rather than just data. There are significant limitations with pivot tables when used to report on a cube and XLCubed addresses these while adding a lot more capability on top. The additional data you need to answer the question is readily available, and you have tools to do something meaningful with it using slice and dice tabular reporting, interactive charting and straightforward user calculations.

So when someone asks the question in meeting, you can explore it interactively and on the spot. And in Excel.

Some Excel BI myths debunked: #4 – no maps

#4: no Maps

The next commonly listed criticism of Excel BI is the lack of integrated geospatial mapping. While for some reports maps remain an irrelevance, for others they can make a huge difference, with targeted advertising or awareness campaigns being the most obvious. While you can now use mapping in Power View, for most corporate office users that isn’t an option yet. XLCubed brings both point and shape based mapping to any version of Excel.

The example report below mixes mapping with the Small Multiples concept. The approach means you can have multiple identical maps which vary only by the time period. This helps you see change over time regionally, and also to make comparisons between the direction and pace of change by region.

 Map3

You can zoom in and pan the maps as needed and they’ll stay in sync, and individual points on the map can also be set up as report selectors which update the rest of the report in sync.

So, mapping in Excel? – absolutely, right now and in any version.