Everyone likes to get a project done, but many times in our rush to deploy we bypass some of the things that demonstrate the consistency and usability that give our projects the credibility they need. How many times have you used an application that had weird glitches but the functionality was cool? Most of the time you wound up not using it after a while because the inconsistency was too much.

It is the same with a SSAS Cube. When you develop a cube for reporting and analysis, those thousands of possible options that make SSAS so powerful are also great opportunities to leave unfinished business for your users to find. In my travels I run across many cubes that are "almost there". Now I don’t mean performance tuning, or high availability I am just talking about cleanup and user friendliness.

Here are my Super Seven Cleanup Steps you should take before deploying your cube:

1. Fix your names:

   Nothing looks worse than a cube that is released to users with dimension names like DIM_DATE or CUSTOMER_DIM. Not only does this confuse users they may think your making a dig at their intelligence with the DIM_EMPLOYEE dimension you left. Instead, please rename all dimensions and attributes to have names that the users will want to see.

   CAUTION !!! – If you have written all your MDX for calculations, KPI’s etc.. And then you go change all the names, this will break your code. Please make this a habit you do when building the cube the first time through.

2. Verify "Display Folders" for your measures:

   Often measures and calculations will be placed in display folders to organize them better for your users. Go Through each folder and make sure the measures in the folder belong together. If they don’t this will again confuse the user base. If you are use where this is check out the properties for your measure like such..

   

3. Ensure consistent color coding:

   When you apply color coding to a Calculation, make sure there is consistency between them. If one profit number turns red when it’s below goal, then make sure the other one doesn’t turn blue, etc..

4. Verify Attribute Relationships for All Dimensions:

   I cannot stress enough that attribute relationships are the foundation for performance and optimization in SSAS. There are lots of good posts on the web about this so we wont’ rehash it here, but suffice it to say, that if you have built User Hierarchies in your cube (The middle pane in dimension designer), you need to verify that your attribute relationships are setup correctly. This will enable SSAS to properly optimize, index and rollup your data internally for quick response to your users.

5. Hide or remove Unnecessary Attributes and Measures:

   Some attributes and measures will get added but will really not ever be needed by users or for calculation. These should be removed to avoid the processing and storage overhead they present. Also if there are attributes or measures that are not needed for reporting, but are needed for calculation, they should be hidden to avoid user confusion. You can do this by setting the VISIBLE = FALSE property for Measures and the AttributeHierarchyEnabled = FALSE property for Dimension Attributes.

6. Verify your AggregationFunction Settings for your Measures:

   Each Measure has an AggregationFunction Property that controls how it will behave when it rolls up. I have a future post coming on these , but for now, just make sure that it is set to the correct type for what the measure is representing. Hint if your data is snapshot data (inventory) it will unlikely be a sum of the snapshots so since SUM is the default, you will want to check these. It is a measure level property.

   

7. Get User Feedback:

   Make sure a small group of your user base gets the chance to review the cube before the really UAT group gets their hands on it. They will catch a large number of glaring things that either got missed in requirements, were improperly scoped, or were done due to confusion between users and developers. Getting these fixed before UAT will really improve your credibility and your project’s chance at adoption.

   Keep Up The Good work !

   Don’t forget to post your thoughts or email me your questions to ajorgensen@pragmaticworks.com. As always, this blog is to help you better understand the tools at your disposal …