- 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 🙂