When do you need a cube?

I took part in some training this week, specifically for SQL 2008 Business Intelligence. The class was taught by Peter Myers of SolidQ, and he did a wonderful job. I learned quite a bit in the first ninety minutes and could tell that Peter knows his stuff.

One little tidbit that came my way on the first day was the answer to this question: If you currently have a relational database how do you know when it is time to build a cube for reporting off of that database?

The answer is incredibly simple, but often missed: When performance degrades to the point that users running reports brings everything else to a grinding halt. So, if you are packing more and more data into that database of yours, and think that having a terabyte of data is okay because the database is expected to grow that much, and you just accept the fact that when reports are run performance is awful, then you are a ripe candidate for a cube, my friend.

See, if you build yourself a cube you will be able to get all the reporting, including ad-hoc reporting, that you get when querying a relational database. In fact, you will get more, and you will not be getting in the way of someone else trying to insert or update information. So, if you find yourself running into this situation in your shop, where reports are killing your system, crack open a book on SSAS and see if you can maybe help everyone out by building a cube or two.

http://www.straightpathsql.com/blog Mike Walsh

Is that really true though? I mean what if the relational reporting is detailed and gets to the transactional level? A cube is a great structure for looking at summarized data, aggregate data.

I recently have been working on the opposite problem with poorly designed cubes handling a lot of relational reporting duties.

Agree that cubes are quite useful though and they can often answer a lot of great reporting questions.
SQLBatman

Mike,

Have I ever lied to you before, as far as you know?

Cubes can be very detailed as well, they just need to be built to the right specifications. The point here is that if you have performance issues related to one group of users entering data and another group trying to report on that data, you may want to think about a different architecture. In most cases people tend to just think about a seperate database for reporting. But you may want to consider a cube as well.
http://jeremiah.peschka.myopenid.com Jeremiah Peschka

Mike,

As far as I’m aware, you should actually be building your cubes down to the most atomic level possible. Kimball’s Data Warehouse Toolkit gets into a lot of the details of building and designing cubes. There’s some great stuff in there.
http://www.straightpathsql.com/blog Mike Walsh

A different architecture indeed. Trying to make that happen. May just have to skunk works it.

I guess it’s not the level of detail in the cube but the type of reports coming back from the cube. A lot of transactional listing type data. It’s not aggregates or summaries of that atomic data but transactional high row count reports.

After re-reading my comment, I see that I sounded rude in my response. You have never lied to me Batman. Don’t take my bat signal away. I guess I was frustrated with the cubes I am supporting, not you.

I agree having a separate database (or cube) for reporting does make a lot of sense as you explain it. Just saying that cubes are not the answer for every type of reporting.

I think we need to get Mr. Myers to come up here and work through our architecture when the powers that be allow it.

SQLBatman

mike,

i didn’t think that you were rude, but i could sense your frustration. and you are correct, a cube is not needed in every case, but a change will need to be made somewhere.

http://www.straightpathsql.com/blog Mike Walsh

But of course.
http://www@patcom.net Gert-Jan

Cubes are great for data-mining and ‘slicing and dicing’.