4 Tips for data modeling with cubes

Filter By Topic


cubesI was reminded recently by how ease cubes are to create and much fun they are.  What, me a nerd? Yeah, I’m a cube nerd.  I was with a client recently teaching them how to build a cube with their data.  They couldn’t explain how their data measures – accounts, cost centers, regions etc. related.  Were they hierarchies like cost centers rolling to regions or separate dimensions as cost centers can span regions.  So I said, “Let’s smash the cube together”.

We took a slow educational pace and in about 2 hours we had a cube. With CALUMO’s slice and dice, we were able to figure out how the measures related to each other.  I have worked with people who have spent 2 days scoping a simple cube to get it wrong because they didn’t know the data.  Had they smashed a cube together, they would have saved many hours.

The other great thing this 2 hour process did was give my client a light bulb moment!  The old “Uh HA!” and “Oh… now I get it!” Showing people their data and how it looks via a cube verses using words and pictures is so much better!

  1. If you’re not sure – build!  Don’t spin your wheels thinking and asking lots of questions or even describing.  Put the data together even with PowerPivot to get an real view on the data.  I have been told with 100% certainty that data IS a certain layout and model but…put the data in a cube and watch for the “oh, I forgot about that…” faces.
  2. Don’t worry about getting it 100% correct first go.  When you smash a cube together, you’re going to have some really funny looking dimensions and hierarchies but that’s OK.  We aren’t rolling this out to our users and no doubt we will delete this cube before long. So don’t stress about descriptions too much, or even calculations for planning models.  You will learn enough from a rough cut.
  3. Be prepared to build that cube more than once.  You’ll find some issues the first time like those two data measures are not a hierarchy but separate dimensions.  The data signs are back to front. We forgot this other data source. And list goes on...
  4. Set yourself a time limit. I like to set a 4 hour limit on building a cube for review.  We use Microsoft’s SQL Server Analysis Servers (SSAS) and PowerPivot so that’s plenty of time.  If you’re spending more time than that – you perhaps either have a very large and ugly data set or, you’re learning the tool set so think about doubling or tripling that.


Good luck and remember to have fun!

Leave a Reply

Your email address will not be published.