Alan Whitehouse's Ramblings

Continuing to work until my heavy investment in lottery tickets finally pays off….

  • Categories

  • Archives

  • Deep Thought

    Historically speaking, all true change in the world has come thanks to leaders emerging, them taking charge and giving the masses someone to rally around. Can an intentionally "leaderless" movement survive or will it just slowly fade away?

  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 18 other followers

  • Me!

  • Disclaimer

    The views expressed in this blog, while intelligent and always right, are strictly my own, and do not necessarily reflect the views of anyone else with which I am in any way affiliated. And don't forget, I own the rights to all information on this blog (except for the stuff I stole from other people).
  • Current Rant

    *START OF RANT*

    You want change, then get involved. Vote, run for office, go to shareholder meetings and contact advertisers or investors. Sitting around banging drums, singing kumbaya, smoking weed and having a camp out under the stars is not going to get you the change you want.

    *END OF RANT*
  • Admin Stuff

TGO Budgeting Solution – Relational vs. OLAP

Posted by Alan on January 27, 2011

As we were brainstorming the design for the TGO Budgeting Solution we eventually touched upon the topic of the back-end storage model and whether we should go with a solution that is primarily relational or “cubular” in nature.  

[NOTE:  I am not sure if “cubular” is a real word or not, but those of us in the BI group here TGO Consulting use it in day-to-day conversation.  So if it isn’t a real word then consider this post is me copyrighting the term right now.  Over the course of time you will see more of these wacky words we are trying to get into the mainstream vernacular.]  

So in the past, along with Excel, I have worked with solutions that were completely relational based (i.e., Forecaster), those that were primarily relational based but with some smattering of OLAP (i.e., Enterprise Reporter), those that were fully OLAP based (i.e, PerformancePoint Planning) and those that utilized a combination of the two (i.e., Clarity CPM).  The fact that I have worked with 4 different structured solutions, that were created by 4 different developers and there are 4 different storage models, pretty much shows that there is no one general consensus of the best way to design the storage for an enterprise planning solution. 

No clear winner in this arena meant we had to make this decision for ourselves.  To aid our decision process we laid out the pros and cons of each type of storage model into an all encompassing matrix.  A  cut-down, bullet point version of our matrix is shown below:

Advantages of OLAP

  • Data is already stored in a format ideal for analysis
  • Thanks to hierarchies and roll-ups, consolidation of data is automatic
  • Large data sets can be retrieved quicker
  • It is easier to code complex calculations that span large member sets
Advantages of Relational
  • Capable of storing both numbers and text
  • Greater availability of IT people with knowledge of T-SQL as opposed to MDX
  • Better for situations where members (e.g., Employees, Projects, etc.)  need to be added “on the fly”
  • Supports both Windows Authentication and SQL Server Authentication

Now what does the above ultimately mean?  It means that OLAP storage is better for things like expense or revenue budgeting because the focus is on the numbers and quick roll-ups are important.  However, if organizations are doing compensation or capital expenditure budgeting  then relational probably makes more sense  as those budgeting processes tend to require a lot of input that are not strictly numbers based (e.g, yes/no questions, project descriptions, adding new employees or new asset purchases to budget against, etc.).

In our experience, the priorities for budgeting for organizations have tended to be first and foremost expenses, then compensation, then revenue and after that everything else.  So that means that neither OLAP nor relational on their own will fit the bill for an enterprise wide planning solution.   Therefore, TGO has decided that our solution will utilize a hybrid approach and utilize both OLAP (via SSAS) and relational components.   It is more development work and costs but ultimately will provide users a more powerful solution.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: