OLAP O'MY

February 10, 2008 12:22 PM

How does one get their head around OLAP?

Our agency has a large amount of data in an SQL server that covers all aspects of our business processes. Our CFO is looking at reporting packages, and ways to build reports from this data for management staff. We currently have no one who understands SQL outside of the CFO's elementary knowledge (Access), and my understanding from the world web development.

I am looking to leverage this opportunity and introduce reporting internally without having to spend who knows what on consulting services. In the past I have never had an issue picking up new technology and running with it, but OLAP is kicking my butt.

Is there a good primer for BI, OLAP, and solid examples? The Microsoft SQL Server book from Microsoft Press comes with some examples, but that isn't really doing it for me. All of the OLAP examples I have google'd dont seem to really explain what they are doing.. they just jump in with facts and dimensions.

How does one learn OLAP and presenting OLAP in a BI framework?! I have been producing flat reports, but OLAP is kicking my butt!

posted by SirStan to computers & internet (4 comments total)

Given that you are using SQL Server already, you may want to look into Microsoft Analysis Services. The preferred query language for OLAP there is not SQL but their proprietary MDX language, and learning the basics of MDX might give you a better vocabulary/framework to think about your data in a more OLAP/CUBE way. I wouldn't really recommend building OLAP style reports/queries by hand in SQL - if it were easy/natural/efficient, companies like Microstrategy or Cognos wouldn't exist.

Also, you might also need to read up the difference between MOLAP and ROLAP, different storage models that give you different query performance characteristics.
posted by of strange foe at 2:26 PM on February 10

The OLAP report is a useful point of reference....

And I agree with the above post that there are many good products out there (Cognos and Business Objects included) that suggest that relational technologies do not meet the needs of the more advanced analytical world and therefore require specialist OLAP products. In fact see the note in the above link re Ted Codd...

That said the relational vendors have been working hard (and buying in companies thick and fast) so they can bridge this gap.

Also, this is a different world to relational coding, so different skill sets will be needed.
posted by pettins at 5:08 PM on February 10

From my experience at developing OLAP systems, I will offer three pieces of advice:

1. A useful, effective OLAP system requires a great deal of analysis up front. If your previous experience comes from vendor demos or "OLAP in 7 Days" tutorials, you may not appreciate how important this is. You really have to understand the end user's view of the data in order to organize the reporting database in a useful way.

2. Because of #1, be prepared to throw away your initial design. I recommend that you include "throw away proof-of-concept and re-do from scratch" in your project plan. In all seriousness, any non-trivial OLAP database is complicated and getting all of the analysis right the first time is next to impossible. You need to do a pilot project first in order to fully understand the problem.

3. If you believe the end user will be able to "write their own reports" from your OLAP model, think again. From #1 and #2, you will come to realize that many end users, particularly managers that are used to receiving reports pre-digested by their staffs, do not really understand the minutia of day-to-day operations. Your end user audience will likely be the "power users" that are already compiling these reports now. The most you can hope for with an OLAP tool is to make their jobs a little easier.

Note that none of these three points are specific to a particular database or reporting package. Developing an OLAP system is primarily an analysis problem, not an implementation problem.
posted by SPrintF at 7:51 PM on February 10

This may come off as a bit argumentative, but your phrasing makes it sound like you may be more interested in these terms as buzzwords than useful tools. What kinds of reports are you currently getting from the data in your database, and how are those being generated? How are these reports lacking, and what would improve them?

Does the answer to this last question involve an entire redesign of your querying capabilities, as would be available through an OLAP cube? Or, is this something that you could easily fix with better traditional queries and methods?

I ask this because I've seen people waste a lot of time and money, a lot, chasing after consultants who shout these buzzwords without explaining why or how this would improve anything about your business. Usually these people are long gone by the time questions start getting asked.
posted by odinsdream at 8:30 PM on February 10