Tag Archive for SQL Server 2012

Adding User Configurations to an Analysis Server Cube Part 2

Part 2: Dynamic User Configurations

In Part 1 of this series, we hard-coded some MDX values into the cube. That approach works in that it produces the desired end result, but if the values need to change, a developer is needed to make it happen. What is needed is a way to persist the configuration values outside of the cube itself. In Part 2, we will create a configuration table to store the values. The structure is borrowed from that used by earlier versions of Integration Services:

Really, the only two fields absolutely required here are the Name and Value fields; the other two are added for administration and clarification. Next we’ll insert some fictional values into this configuration table:

Next, create a view that pivots the Configuration Name and Configuration Value fields with T-SQL code like this:


The dataset returned by this view will be a single row of data with one column for each Configuration named in the PIVOT section, and a static [DummyKey] value of -1.


Again, some purists may dislike my use of “SELECT * FROM …” in my view definition, but since I am limiting the columns returned via the ” . . . FOR ConfigurationName IN (…) . . .” statement of the PIVOT clause, there is not much chance of getting unneeded columns.


Next, add this view to the cube project Data Source View, then add it as a Measure Group to your cube. Delete the COUNT and the SUM(Dummy Key) measures that were added by the Measure Group wizard. Since there is only one row in the measure group’s base table, a SUM( ) aggregations for the configurations are fine. Lastly, since a Measure Group MUST be joined to at least one Dimension, on the Dimension Usage tab join the Configuration Measure Group to a dimension in your cube that meets the following criteria:

  1. The dimension has a member row with a key value of -1. (Data Warehouse designers typically add a -1 key as the “Unknown” member of the dimension table.)
  2. You will NOT be using the dimension in conjunction with the Configuration Values. This sounds rather counter intuitive based on cube design practices, but it is explained below.

Browsing the cube by any dimension OTHER than the one used to join the Configuration Measure Group will return the configuration measure values at every cube intersection. This is because you are actually selecting the [All] member of that one dimension, which includes the SUM of each Configuration Value. And since there is only one row at the [Unknown] member (Key = -1), the SUM at the [All] level is the one row. Browsing the cube INCLUDING the one dimension will show that the configuration values are ONLY available for the “Unknown” member, and not for any others. If your configuration values, whatever they represent, will NEVER be used with the dimension you have them joined to, then this is just fine. But if there is any possibility that the Configuration Measures would be needed for any and every dimension in the cube, then you need to do a little editing of the view. We’ll cover that in Part 3.

The advantage of this method over what was covered in Part 1 is that if the Configuration Values ever need to be changed, it is now simply a matter of changing a single value in a table and reprocessing the Measure Group instead of editing the cube design and redeploying the entire cube. To add additional configurations would involve the following:

  1. Add the entry in the table
  2. Edit the view to include the appropriate [ConfigurationName] in the PIVOT clause
  3. Refresh the Data Source View for the cube project
  4. Add a new measure to the Configuration Measure Group for the newly added column
  5. Deploy and process the cube

In Part 3, we will overcome the limitation of NOT being able to use the Configuration Measures for EVERY dimension.

Adding User Configurations to an Analysis Server Cube

Many times, clients ask me to hard-code various values into their cube logic, things like conversion rates or other common factors. This article series will cover several different methods for adding user specific configuration values to an Analysis Server (Dimensional Model, as opposed to a Tabular Model) cube. As with any technical solution, there may be other methods available. As the series progresses, the techniques will get more and more involved, but in conjunction, they will be more flexible. As you may already be aware, there are essentially three ways to get ‘stuff’ into a cube: as a Calculated Member, as part of a Dimension Member, or as a Measure in a Measure Group. In Part 1 of this series, we will be hard coding some values as MDX calculations. Most of the methods in this series will work with any version of SQL Server and Visual Studio. I will try to point out limitations in versions when they arrive.

Part 1: Static Conversion Factors as an MDX Calculated Member

If a conversion factor isn’t ever going to change (like converting miles to feet, or vice versa), we can simply add an MDX statement in the Calculation design tab as a static value. Open the cube project in Visual Studio, double-click on the cube object in the Solution Explorer to open the cube in design mode. Click the Calculations tab at the top. Enter the following MDX Statements in Script View:

If you prefer Form View over Script View, your screen for the first MDX Member would look like this:

The purist reader may point out that the latter measure is simply the inverse of the former, which is absolutely true. However, I would reply that a) it doesn’t take up any more cube space or processing resources, and b) I prefer to always be multiplying my conversion factors instead of worrying about multiplication or division depending on the direction of the conversion.

For example, I would prefer the following MDX calculation:

to this one:

The end result of the two statements is exactly the same, but logically to me, the first one makes more sense.

Note here that the VISIBLE property of the two base conversion factors are set to 0 or False (hidden) because in this instance, I don’t need to let the users see them.

This method is about as simple as it gets. But that simplicity comes with a price. The conversion rate between miles and feet is never going to change, but something like a business specific factor can, and most likely will change. Using the above method would require a Business Intelligence developer to edit the MDX code within the cube and re-deploy it. Starting in Part 2 of this series, the methods will involve saving the configuration value outside the cube and independent of the code.

Key Improvements to Master Data Services are on the way

SQL Server 2012 – formerly known as “Denali” – will be available March 7, 2012. My colleagues at Blum Shapiro have been participating in private technical sessions with the SQL Server team over the past 6 weeks to learn of the many improvements. Today, I want to share some details on what you can expect with regards to Master Data Services.

Silverlight to the Rescue

We’ve been working with Master Data Services since the release of SQL Server 2008 R2. No question that it is a flexible Master Data Management platform, but those of you who work extensively with the product are aware that the Master Data Portal, the primary way for Data Stewards to interact with the master data, has a very unfriendly user interface. In 2012, the data entry grids are all Silverlight based. This makes for a much improved experience for both filter and CRUD operations.

This is what the Explorer View looks like in the Master Data Portal. Note the improved Record Detail interface to the right.


Excel – the Microsoft BI Swiss Army Knife

Microsoft loves to extend the usability of Excel. No surprise then that 2012 includes an Excel Add-In for Master Data Services. Authorized users can insert, edit and delete master data records within Excel. Even better, data administrators can create entities and attributes directly in Excel! This is a huge benefit to MDS model builders who need to quickly get new master data entities created in Master Data Services. Simply select the data you want to import into your model and click Create Entity.


The thing that interests me most about the two improvements is that it seems to be highlighting the focus that Microsoft has on Master Data in the hands of Information Workers. For most of my MDM clients, Master Data can be found mostly in Business Systems, not on a file share. I think the question Microsoft is posing here is: are you sure you know where your master data is created? This is a challenging question for many organizations.

These are just two. Next time around we’ll talk about the improved Staging infrastructure.

Brian Berry is a Director of Technology Consulting with BlumShapiro, focusing on Microsoft Business Intelligence solutions, with a strong focus on Systems Integration, Master Data Management and PerformancePoint Services. He has been helping companies optimize their investments in Microsoft technology for over 12 years.