Archive for SQL 2012

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.

Three Major Changes to Master Data Services in SQL 2012

Microsoft introduced Master Data Services as part of their SQL Server product starting with version 2008 R2. In SQL 2012, there were several enhancements that made the product a bit more usable. We’ll be highlighting three of the major changes in this article. I will be presenting a more in-depth look at Master Data Services at a SQL Saturday even in Providence, Rhode Island on September 21st, 2013 (#sqlsat; #sqlpass).

Master Data Services web application is now Silverlight enabled

The first version of MDS in SQL 2008 R2 had a basic web front-end that allowed the user to create models, entities, attributes, and hierarchies. But it was slow. It did the job OK, but could be very frustrating when working with more than a handful of records. With any decent amount of data, the Explorer page tended to be unmanageable. MDS web site in SQL 2012 is now Silverlight enabled and responds much faster. Fast enough, in fact, to NOT require any third-party tools to simply browse the data.


The Master Data Services Add-in for Microsoft Excel

I heard mentioned, tongue-in-cheek, at a Microsoft conference once that eventually all any developer would ever need would be Internet Explorer and Visual Studio. I submit that the third essential component in that set would be the right Add-in for Excel. Let’s face it, EVERYBODY uses Excel these days. Realizing this, Microsoft has done us a great service by including the install link right on the MDS web site. Once installed, connecting to the data is very straight-forward. Simply supply the URL of the site to connect, then choose the Model and Version to work with. Adding and editing data is intuitive as well. The Add-in keeps track of changed data and highlights it as you work. In the end, the Publish button on the tool bar pushes the data changes back into the database.


New Staging table structure for Entity imports

In 2008 R2, to load a single member row into an entity with, say, 5 attributes required a total of 6 rows of data to be sent to two staging tables. The staging table in MDS 2008 R2 handled bulk imports of ALL entities, ALL attributes, and ALL relationships with only 4 tables, regardless of the number of Models or Entities. The two main tables were for Members and Attributes, with the latter being in an ‘Attribute Name/Attribute Value pair’ format. So your one row of data with 5 attributes would turn into one row of data in the Members staging table, plus 5 rows of un-pivoted data in the Attributes table. Entities with few attributes did not cause major issues, but complex attributes could tend to bloat the staging tables. In addition, if two different processes were trying to load two different entities at the same time, there was the possibility that both sets of data could be co-mingled to the one set of staging tables. This would not be an issue if all data would be staged within one batch, but trying to then process a sub-set of staging data could be difficult.

In SQL 2012 MDS, each entity now gets its own staging table for imports, with meta-data to match, plus a dedicated stored procedure to process data in that table. Not only does this eliminate the need to un-pivot the attributes to attribute/value pairs, it eliminates any possibility that entities could be mixed in the staging import tables.

Dynamic Cell Formatting in SSRS

Early in my Business Intelligence Designer days using other report designer tools, I was all about the data. Get the data onto the report, black and white, never mind fancy fonts or colors. After all, the numbers won’t be any different if you try to make them prettier. A bit like putting lipstick on a pig! Then I came to realize that judicious deviation from the default fonts was not only acceptable, but desirable as evidenced in SQL Server Reporting Services report templates. Sub-total numbers started showing up in a heavier and more prominent font and weight. Grand total number even more so. Such emphasis on the more important numbers on a report help the eyes to quickly identify them as such, leaving the brain to concentrate on other things, such as the numbers themselves. You end up spending more time thinking about the data, and less time finding what you want.

But report cell formatting can go so much further than simply putting more emphasis on numbers at the bottom of the page. With SQL Server Reporting Services, you have the ability to do ‘conditional formatting’, much like can be done in Excel or Access. (If one number is greater than another, format the cell one way, if not, do something else.) To take advantage of the rich formatting options available in SSRS, you need to understand a few basic concepts, and some nuances of Reporting Services.

Know Your Properties

Click on a tablix cell in a report, then click the Properties button.

Note the myriad of properties that can be set just for the cell formatting. Each property, when you go to access its options via the drop-down menu, will have a choice labeled “Expression…”. This is where we will be putting in the ‘conditions’ of the conditional formatting.

Know your Expression Editor, Program Flow expressions, and Property expressions

The Expression Editor in Reporting Services is a powerful tool. For the purposes of our conditional formatting, we’ll be using it to a) examine some value, b) set a Property, and c) supply a default. For the Font Color property of a cell, click the Expression… option below the color palette (see the screen shot above). At this point, the Expression will most likely contain the single word “Black“, assuming you are going with a standard font color. Also note that the work Black is NOT enclosed in double quotes.


In the Category list in the lower left, expand the Common Functions node, then click on Program Flow. Note that there are three functions in the Item list:


Each of these can be used effectively for this task, but we’ll stick with the IIf ( ) function as it is probably the easiest to understand. Basically it works like this: IF a certain condition evaluates to TRUE, THEN do what it says in the second section, OTHERWISE, do what it says in the third section (the default). To see it in action, enter the following formula:

=IIf ( 1 = 1 , “Black” , “Red” )

Click OK, then Preview the report. Because 1 always equals 1, the first section evaluates to TRUE, so the second section is used, in this case “Black”. Note that we need to enclose our color names in double quotes in the Expression Editor for this to work. Now edit the formula and replace the 1 = 1 with 1 = 0. The preview will show the cell contents in a red font. Armed with this knowledge, we can replace the 1 = 0 with a more appropriate business condition. Delete the 1 = 0 text, but leave the cursor in that spot. Click on the Fields (DatasetName) node to see a list of fields available for the Dataset. Double click one to have it inserted in the formula. Add some examination criteria for it that makes sense to your business users. Remember that this part of the expression must evaluate to either True or False. Your final Expression might look like this:

= IIf ( Fields!YTD_Sales.Value >= Fields!PriorYear_YTD_Sales.Value , “Black”, “Red” )

The above conditional expression template can also be used to set any other property available. The only trick is to know what to put in place of the “Black” and “Red” components. The easiest way to determine that is to open the list of choices for the property, and use any set of values you see displayed, as long as you enclose them in double quotes in your expression. Shown below is a list of possible values for the Font Weight property


Using the same True/False comparison from the previous example, the Font Weight property could contain the expression:

= IIf ( Fields!YTD_Sales.Value >= Fields!PriorYear_YTD_Sales.Value , “Bold”, “Normal” )

Font Size is a little trickier as it does not list any valid values. Just know that “8pt” is an 8 point font, “10pt” is 10 point, and so on.

Other Conditional functions

We skipped over the Choose () and Switch () functions, but they operate in a similar manner to IIf(). They return a value based on some comparison. Microsoft’s example of the Choose() function leaves out one simple element: the return value. Sure, it is nice to show the function in use:

=Choose (2, “13”, “15”, “21)

But it would be even better to explain that the function would be ‘choosing’ the second option, which is “15”. How could we use this in a report? Suppose your Salespeople are ranked on performance, and that ranking is available as a report field as an integer. You could select a Font Weight based on the ranking as follows:

= Choose ( Fields!EmployeeRank.Value, “Heavy”, “ExtraBold”, “Bold”, “Semibold”, “Medium”, “Normal”)

The Switch() function lets you list ‘comparison/return’ pairs, all separated by commas, and the function will take the return value that matches the first comparison that evaluates to True. The basic format for this function is:

= Switch ( Comparison 2, Return Value 1, Comparison 2, Return Value 2, … Comparison N, Return Value N )

A Font Size expression could be written as:

= Switch ( Fields!EmployeeLevel.Value = “Manager”, “16pt”,
Fields!AnnualSales.Value <= Fields!SalesGoal.Value, “14pt”,
Fields!AnnualSales.Value > Fields!SalesGoal.Value, “10pt” )

Note here that we have used a different field in the second and third comparisons than is used in the first. There is no rule that says that the comparison elements need to look at the same fields.


It’s easy to see that the Expression editor in SQL Server Reporting Services can be used to build powerful and dynamic expressions that drive various formatting properties. By manipulating multiple Property Expressions for a single cell, the report designer can draw attention to various numbers and report elements if and when the numbers being reported warrant it. It is a much better viewing experience than a simple black on white report where every number is given the same emphasis.



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.