SQL Server 2005 | SQL Server 2008 | SQL Server 2008 R2
Within BIDS, our development environment for SSAS applications, the Cube Wizard does a great job. With the wizard you get all the dimensions, the cube and the proper links between the cube and the dimensions. It even takes care of role playing dimensions and names them accordingly. But although it does a great job, the created cube is not ready for the end users. Recently I saw some cubes being just in the state where the wizard left them. So today’s post is about the most essential steps to perform after the cube wizard has finished creating the cube. I don’t go into too much detail here. Some of the topics have been addressed in other posts. Maybe I’m writing about other topics in upcoming posts. So the following list is more like a check list to verify your development.
1. Make the cube user friendly / do not use technical names
While the operational databases belong to a technical area, the OLAP solution has to be clear and easy to understand for the end users. Avoid technical names, use abbreviations only if they are commonly known and well documented and don’t prefix dimensions with ‘Dim’ or ‘D_’ or something like that (same with the fact-tables). The user wants to analyze sales by time not FactSales by DimTime.
2. Check all dimensions for their attributes
The cube wizard cannot know which attributes are important for a dimension. Check each attribute carefully. Delete attributes that are not needed at all. It does not make much sense to develop for future requirements. This only makes the cube harder to understand for the end users. Also, think about the usage of the attributes. For example, having an attribute “OptIn” for a CRM cube with members yes and no may not be ideal for usage in a pivot table as you would only see yes or no on the column/row. It is better to have “With OptIn” and “Without OptIn” as members because here, the meaning is immediately clear. Of course, numeric flags like gender being 0 or 1 also do not make much sense. Members should be clear and readable.
3. Create appropriate attribute relationships and create meaningful hierarchies
Attribute relationship-settings and hierarchies are the key to build a robust OLAP solution that enables query developers to write good MDX queries and also results in a better performance of the cube. However, attribute relationships can be tricky, so make sure you fully understand this topic. You should also use tools like BIDSHelper’s dimension health check to make sure that your attribute relationship really matches the source data.
4. Provide properties as needed (not every attribute of your dimension is needed as an attribute hierarchy!)
Define only those fields of your dimension table as attributes that you need either for hierarchies or as a filter or for pivot axis. Fields that are just informational (like telephone number of the employee for example) don’t make much sense as attribute hierarchies. Set the property “AttributeHierarchyEnabled” to false for these attributes. They are then shown grayed-out in the dimension designer. However, you can still use them in your OLAP tool, for example in Excel. It is important to understand how your attribute relationships are defined here.
5. Set the format for measures and calculations
The standard format for numbers is just a simple number format. This usually looks ugly in the cube browser or other frontend tools. It’s not a big deal to set the format for every measure. For example, you could use “#,##0.00” for numbers to get a nicely formatted two digit number representation. You should also do this for calculated measures and cube calculations.
For further explanation see http://ms-olap.blogspot.com/2009/11/how-to-define-excel-compliant-format.html
6. Set the proper dimension type. Especially, define a time dimension
Setting the dimension type of your time dimension to “time” makes it much easier for your OLAP client (like Excel) to provide special time dependent filtering options. Also, if you’re planning to use semi-additive measures, the time dimension must be marked accordingly.
Furthermore you also need to qualify the type of the time dimension’s attributes (using Years, HalfYears, Quarters, Months and Date). This is necessary for MDX functions like YTD(…), MTD(…) and ParallelPeriod(…) and for the “Add Time Intelligence” wizard.
7. Define the default measure for the cube
The default measure is used, when no measure is on the axis or the slicer (where – expression) of the query. If not set, SSAS takes the first measure in the first measure group. This can easily change if new measures or measure groups are created in the cube. Queries that rely on the default measure (which is in turn not a good practice) will then result in a different result. That’s why it is important to set a default measure.
8. Define the default member for dimensions/attributes which are not aggregatable
Similar to the default measure for the cube, each dimension has a default member that is used, if the dimension is not explicitly present in the query. For example, think of a dimension ‘Scenario’ with members like Actual, Forecast, Midterm plan etc. It does not make sense to aggregate the different members so you will usually set the IsAggregatable property to false. In this case you don’t have an All-element and you should provide a default member (in our example it would usually be ‘Actual’). If not specified, the first member is used which could again lead to errors if new members are created or the member names change. Keep in mind, that there are three ways to specify the default member: in the dimension itself, in the cube script (also useful for role-playing dimensions with different names) and in the security role definition.
9. Create a drill-through action for every measure group and make this the default action
Using a drill-through action shows the detail data behind a given cell. However, the default drill-through method only shows the name information of the dimensions’ key attributes. Often enough we would see meaning less surrogate keys here which do not make sense for our end users. By defining a drill through action the developer can set the displayed attributes for each dimension making the result of the drill through query much more readable. In addition, making this action the default action also works well with clients like Microsoft Excel. Here, a double-click on a cell triggers the default action.
10. Properly deal with unrelated dimensions
By default, values on unrelated dimensions are shown as the All-member of the unrelated dimension. For example, in Adventure Works, measures from the Internet Sales measure group are not linked to a reseller (obviously). However, if you try to analyze internet sales by reseller (for example by reseller type) you will see the same value (the total of sales) for each reseller. This is confusing. The property to control this behavior is the IgnoreUnrelatedDimensions setting in the measure group properties. When setting this to ‘false’ values for unrelated dimensions are not shown anymore. This is much easier to understand for the end users.
For further explanation see http://ms-olap.blogspot.com/2010/04/properly-showing-values-for-unrelated.html
Whats’ next?
So that’s all? Of course not. First, all the tips from above do not prevent a bad architecture or cube structure. A good architecture is important for each Business Intelligence solution and there are many good books (for example Kimball’s “The Data Warehouse Toolkit”) out there about setting up such an infrastructure. In general, building an SSAS solution on top of a well made dimensional model instead of some OLTP systems is a good idea.
And of course, the tips from above are not complete in any sense. If you asked someone else, you may get different tips. For example, it’s also important to set up the cube security or to define some time intelligence for easier analysis (like YTD-computations or year-over-year growth etc.). It is also a good practice to simplify complex cubes with many measure groups and dimensions using perspectives to make them easier to understand by the end user. And as many measures/calculations are not self-explaining just from their name, it is a good idea to link an html-document with a specification or helpful text with the member. You can use a cube action for this.
However, I guess you all have many more good ideas with best practices and most important tips. If you like, I would be very happy if you could share these tips with me and – who knows – maybe there will be a post like “25 tips for every SSAS developer” in the near future.
By the way, this is the second anniversary of my OLAP blog. It was quite an interesting time so far and I’m looking forward to more topics to write about. The main location of this blog is http://ms-olap.blogspot.com . The posts are mirrored to http://oraylis-olap.spaces.live.com/ and also reposted to the new blog system of ORAYLIS at http://blog.oraylis.de (here you can find more interesting posts about Sharepoint, Business Intelligence in general and much more). My posts are also readable by using some blog aggregators like http://www.ssas-info.com/ which I really recommend to learn more about SSAS, MDX and OLAP.
Kommentare (0)