Consejos para el Diseño de Cubos OLAP – PARTE 2
In the first post in this series, Cube design considerations—A gentle reminder, I offered a few opinions about some easily forgotten 101’s about cube building. So, now we have done a quick refresher on that what is the single thing you can make sure you are NOT doing that will help with your cube design?
The answer? Avoid creating redundant attributes.
The second recommendation listed in the SQL Server best practice document is to “Avoid creating attributes that will not be used”. I would extend this to say “Avoid creating attributes that are not used, are not useful, or are redundant”, and I’d move it to number one.
This is not specific to ZAP BI in any way, but it’s by far the most common issue I see with the customers I have worked with, and it is the first area I focus on when reviewing a cube design.
Creating a redundant attribute can affect performance, either at query time or at processing time, and it will certainly affect the cube’s usability from a front end user’s perspective. Which, as we all know, is a Cube Design 101 worst practice.
What is a redundant attribute, then? It’s an attribute that is at the same level of granularity as another attribute in a given dimension. Or simply one that is not being used.
For example: remembering that less is almost always better, the Customer dimension should ideally just have the one Customer attribute. But then someone gets creative and creates more attributes: Customer Name, Customer ID, Customer ID – Name and Customer Name (ID), and so on, like this:
So now at processing time, you can see that SSAS has to query the Customers table four times at the same granularity to get distinct versions of each attribute, and add indexes for the intersections it has with each measure it relates to. That’s four times the work. Do the same with Vendors dimensions’ attributes and we have eight times the work.
So, what is the actual requirement here for the Customer dimension? If it is to have the ability to see the different properties of the customer for display, then we can do that by using member properties. I hear clients say “I need to be able to sort by ID or Name”. We can do that too.
The first thing to change in our design is to remove all those additional attributes. We can simply change them to member properties and reprocess the cube.
Your Customers dimension now looks like this. Notice there’s only one version of the Customer attribute.
And here are the Member Properties for the alternate display options.
The first thing you will notice after this change is that the Customer attribute now has a default caption of Customer ID:
But we can easily see a different display caption by using the member properties options in ZAP BI.
Right-click any member and select Member Properties.
Select the property or properties you wish to display:
The report should now look like:
You may then want to order by Name also.
Right-click the Customer level on Rows and choose Set > Order to open the Order function dialog. Add the Member Property that you wish to order by.
And the result, ordered by Name, looks like this:
One further tip is to try to create a caption for members that is likely to be the most common requirement, leaving the Member Property fiddling for the less popular entries.
Summary
There are many small things that can be done to improve your overall cube design. I consider this to be one that falls into the low-hanging fruit basket. Make sure you take a good look at your cubes, one dimension at a time, to see if you can find any low hanging fruit.
