Archive for November 22, 2013

Adding User Configurations to an Analysis Server Cube

Part 3: One Row for Each Dimension Member

In Part 2, we created a view that pivoted the Configurations table around a single [Dummy Key] value of -1 and joined that to a dimension. And the dimension we picked had to be one that would never be used with the Configuration Measure Group because the sum of the configuration measure was only available at the UNKNOWN dimension member. Now, perhaps we come to realize that there is no good candidate dimension to which we can join our configuration measure group because it needs to be available to ALL dimensions. What now?

To make this work, we will need to edit our view from Part 2. First, select a dimension with only a few members. I chose my Date dimension for this solution. We’ll need to create the dimension table and populate it with some sample data.

Create the table …

Insert some data …

Now alter the view by adding a CROSS JOIN to the dimension table, and substituting the dimension’s [Date Key] for the static [Dummy Key] as follows:

The CROSS JOIN of the sub-select will return the Cartesian product of all rows in the Date dimension table (7) and all rows in the Configuration table(2), or 14 rows. Each Configuration will have the exact same value for all dates. The PIVOT statement will bring those 14 rows back to 7 rows, with three columns: [Date Key] and two configuration columns.

Refreshing the Data Source View will remove the [Dummy Key] field and add the new [Date Key] column in its place. Next we need to edit the Dimension Usage tab such that the Configuration Measure Group is joined to the Date dimension on the [Date Key] field. But browsing the cube now along any other dimension will yield surprising results: The Configuration value is SUMMED for the number of days in the slice! Well, obviously as that is what we told it to do when we created the Measures back in Part 2! Go back to the Cube Structure tab and edit the individual Measure aggregations in the Configurations Measure Group to “First Non Empty” (if you have Enterprise Edition) or Min or Max (if you have only Standard Edition). Now, regardless of the number of rows in the cube slice, the aggregation will always yield the value desired.

As with Part 2, we still have a single point outside the cube where configurations are stored, but the configurations are available to all dimensions.

Adding additional Configuration entries would involve the following steps:

  • Add a row to the Configuration table
  • Edit the view to include the field based on its [Configuration Name]
  • Refresh the cube project’s Data Source view to get the new field included
  • Add a new Measure to the Configuration measure group.

So far, our solutions have all resulted in global configuration values that if changed, will change for all dimension slices. In our case this is for all dates in the Date dimension. In the next part, we’ll explore a few different methods that will allow for entering configuration values that are date dependent. Since I started using the Date dimension for this series I’m going to stick with it for clarity, and also because that is usually how configurations are needed for the business.