- Understand what the cube will be used for. This is a super critical success factor.
- Be careful when materialising a referenced dimension. They could produce incorrect results. See Alberto’s post. I have experienced this 3 times.
- Define aggregations, from actual cube usage if possible. They will speed up queries.
- Partition your cube. Align the cube partitions with the physical table partitions. This will speed up processing time.
- Define your SQL queries in the database views. Not in the named queries. Materialised them if possible, using indexed views. Only select columns that you need.
- Keep it simple. Only create required attributes and measures. You can add more attributes and measures later (and you will).
- Be careful when defining attribute relationships. They could produce incorrect results. Product1 is currently under category1, but next year it could be under category2. See this post.
- Arrange your DSV diagram and cube structure diagram by hand. Don’t use Arrange Tables. This is more of a personal taste; I like to know where my tables are in the diagram.
- Test your cube for accuracy, functionality and performance*. Accuracy is paramount in cube.
- Get early feedback. Give your key user early version of the cube. It’s OK if it’s not finished. Incorporating user feedback is a super critical success factor.
*Hmm, felt like another top 10 tips coming up soon: cube testing!
I came across Hilmar Buchta’s Self Service BI article last week. And when I came back to his blog this week, I saw his top 10 tips. So, yes, I was inspired by it. But above are encapsulation of my own experience, honest 🙂
Data Warehousing and Data Science 
[…] https://dwbi1.wordpress.com/2010/07/07/top-10-tips-building-cubes/ […]
Pingback by Top 10 Tips: Building Cubes « ellynguyen — 3 June 2011 @ 6:30 am |
Great Tips, actually from my experience all of them are true, I also could add one:
Let most calculations to a “fact-based view” rather to a “cube-based calculations” (YTD, MAT, Exchange Rates), this increases a little bit processing times but leads to an optimized relational fact table and an optimized cube calculations since most of them are already defined.
Excelentes tips, de mi experiencia todos son válidos, podria agregar uno inclusive:
Dejar la mayoría de calculos en una “vista basada en la tabla de hechos” en lugar de “cálculos a nivel de cubo” (Acumulados anuales, tipos de cambio), esto aumenta ligeramente los tiempos de proceso pero a cambio se obtiene una tabla de hechos optimizada y cálculos optimizados a nivel de cubo ya que la mayoría están previamente definidos.
Comment by Douglas — 30 July 2011 @ 6:14 pm |