A Few Tips on SSAS Cube and Dimension Development

Question

The following are a few tricks I've found to fix reoccurring issues that don't seem to be fixed from any other question or forum.

Answer 1

1. My cube has data when I filter but not when I want to show rows. Reoccurring answers that I've seen:

   • Calculate Statement is missing/ commented out.
   • Your partitions/ aggregations are set up improperly.
   • Your dimensions were set up improperly.

Now if you are doing a basic cube set up like I was, meaning you throw in dimensions and use the measures that come up. The first two shouldn't be an issue. The third one can be checked by browsing the dimension, if the info is not there then your dimension is an issue.

The issue I was having: I had a few attributes of the View I was using that were dates, so I linked them to a timetable. Which, for me, if set up properly, adding the time dimension to the cube would not add "Time" to the cube, but would add the attributes themselves. I.E. "Last Collection Date" And so on.

The issue I found was the time dimension was not set up properly. Either not all of my dates in my view had relationships with the time table, or there were nulls in the dates, which can be handled by creating an unknown member. Most times no info was in my cube taking care of the time dimension fixed it.

2. My cube takes too long to process. Reoccurring answers that I've seen.
   • Your partitions are set up improperly
   • Your view has too many columns/ items.

Now if you're like me and have to work with what you are given as far as the view goes then the second one doesn't apply/ cannot be fixed. The first one I have yet to play around with partitions too much because doing the counts takes too long for me to be able to sit around at work for.

The issue I found was having too many distinct counts Unfortunately for some, you may need a distinct count. However, even with messing with aggregations, I was still unable to reduce the time it takes to process distinct counts.

3. My cube will not deploy.

This can happen for a few reasons, something was previously deleted and the cube was not updated. The measure relationships are not set up properly. (this will especially happen if you change a 'Key' of a dimension) Or a dimension is set up improperly. If you did change the key of a dimension be sure to change its attribute relationships as well.