Why OLAP?
For the first 3 quarters of my career I didn’t pay attention to Online Analytical Processing (OLAP) technology, everything I did was with relational structures because everything I did was for users who wanted reports handed to them. In the last few years that has changed for me in the regard that users are more technically educated and the reports that are being generated don’t move at the speed of business and can’t answer the question “Why is this number the way it is?”
Why OLAP?
For the first 3 quarters of my career I didn’t pay attention to Online Analytical Processing (OLAP) technology, everything I did was with relational structures because everything I did was for users who wanted reports handed to them. In the last few years that has changed for me in the regard that users are more technically educated and the reports that are being generated don’t move at the speed of business and can’t answer the question “Why is this number the way it is?”
Giving an analyst direct access to the data via a cube only makes sense because only they know the questions that are going to pop in to their head based on a static report. By giving them this access, they don’t have to come back to IT and ask for a new report and it affords them a level of “Self-Service” business intelligence. Users have the ability to slice and dice the data as they see fit, drill down to new and deeper levels of detail and find things within their data that they might not have uncovered with a standard report or dashboard. Cubes also aggregate the data so that if you have a cube with tens of millions of rows you can get the answer you need is seconds. This helps the end user for sure, but it also helps the IT report developer of dashboard creator in that their performance should also see improvement and access and the speed of data retrieval is improved.
Now with the addition of tools like PowerPivot and Tableau users can take these cubes and build their own visualizations which make these cubes even more powerful. Think of the cube as a semantic layer on steroids!
In addition, cubes are more agile and easier to maintain and extend than a relational structure because you have the ability to create calculations as they are needed rather than creating ETL packages, modifying data structures and modifying reports.
My Current Situation
I currently work for a company who has SAP Business Warehouse (BW) which is uses for financial reporting, which is fed from our SAP ERP system. If every piece of data we had been native in SAP then it would make sense to use SAP BW exclusively and handle all the report in that manner, but we don’t. We have data in external systems, cloud/hosted systems and in spreadsheets to the point that some BI professionals would sit under their desk and weep. We need an enterprise data warehouse to bring all those pieces of data together, and we have a user base who is hungry to access that data, so the obvious choice is by using a cube. Cubes present very well in Excel, which out users are very versed at using and they have a high comfort level. To date, our cubes have been well received and we had very positive feedback on their day-to-day business use of them to answer the questions that arise at any give point in their day.
In my previous position we graduated to cube usage as the users got more and more comfortable with BI and in using the data. When I left we had just deployed our first cube base on general ledger information, and from what I am told it is still being heavily used and relied upon by the user community there.
Should You OLAP?
That’s a valid question, and I suppose it depends on the organization and user base, but if you have a product like Microsoft SQLServer you already have the tool in-house so why not give it a try? If you are already building star schemas you are more than halfway there! Analysis Services is fairly easy to learn and use, just like most Microsoft products, and you can have a full functioning cube in less than a day on your first time developing with Analysis Services. There are plenty of resources on the web, and I’m sure if you are really serious there are training classes in your local area.
If you were to look at the needs of your users, the majority of them just need access to the data, probably more than 50% of them fall in to that category, so why not present a cube to them? They don’t have to learn SQL, they can drag and drop in Excel to build what they need and they will stop coming to IT every time they need a new view of the data. The ROI on a cube can be incredible!
