Nick Barclay: BI-Lingual

Dashboard Requirement Gathering Satire

noreply@blogger.com (Nick Barclay) — Fri, 30 Mar 2012 20:04:00 +0000

A colleague of mine put together a hilarious PeepzMovie that was inspired by some frustrating projects.

If you're a BI pro, do yourself a favor and take a few mins to watch it.

Updated with new link to YouTube version.

Book Review: Microsoft SQL Server 2008 R2 Master Data Services

noreply@blogger.com (Nick Barclay) — Thu, 08 Sep 2011 11:53:00 +0000

Full disclosure: The authors of this book provided me with a free review copy.

Time to dust off this blog and post something. Wow, has it been that long?

Microsoft SQL Server 2008 R2 Master Data Services is just what I look for when I want to get up and running with a new product. Jeremy, Tim and Martyn have written a book for getting up to speed on just about every aspect of MDS. Experienced users can use the book to ensure existing knowledge gaps are filled and experiment with more advanced functionality.

I’m a big fan of technical books like this one: Explain the concept, take the reader through step-by-step instructions, build on what has been created in previous examples. By the time the reader has finished the book they have created a set of interrelated artifacts and performed tasks that touch almost all the major functional areas of the product.

The authors didn’t stop at the basic functionality of MDS, a significant portion of the book is dedicated to the more advanced aspects of the product. T-SQL / SSIS based data loads, integration with SharePoint workflows, BizTalk and the MDS API are all covered. Lots of useful sample code and reference material earns the book a place on the within-easy-reach shelf.

Kudos to the authors for not making the book too heavy on the process rigor of master data management. They are careful to keep focus on setup, usage and extensibility of the product on which the book is based. Extra credit must also be given in that they managed to make the UI look simple and intuitive; the MDS web UI must be one of the clunkiest and difficult-to-use that I have had the displeasure to work with. The book makes it look easy. Hopefully MS is addressing this large shortfall in what is otherwise a pretty solid V1 product.

As when reviewing Alberto, Chris & Marco’s "Expert Cube Development" book, my primary criticism remains directed at the book’s publisher, not its authors. Packt’s layout formula does not recognize the importance of reference numbers and caption text beneath screenshots, tables and figures. In this day and age publishers are focusing more on delivering content digitally. The layout of digital publications is often performed dynamically on devices such as the Kindle or iPad, as a result what is considered “a page” is not necessarily the same because each reader’s personal settings may differ. Text referring to “the screenshot above” or “the previous page” may not necessarily be accurate or helpful. Which page? Which screenshot? Annoying. What is so difficult about “refer to Figure 1.2”, or “as listed in Table 3.4”?

All in all this is a book well worth getting hold of if you want to get stuck into all aspects of MDS from installation to advanced usage.

MDS Architecture Notes

noreply@blogger.com (Nick Barclay) — Tue, 29 Dec 2009 19:25:00 +0000

While I was creating the recent series of walkthrough posts on I put together a diagram of the major objects that make up an MDS model. I figured it was worth sharing.

The diagram below shows a single MDS instance containing a single model: Product. The aim is to show, at a high level, the relationships and some of the functionality found within an individual model. I’ve provided a brief sentence or two on my understanding of the objects contained in the diagram as a basic primer. Wherever possible I have linked to the online documentation for that particular feature.

MDS (Instance) the container of containers, the Master Data Services application itself.

Models are the primary container for specific groupings of master data. The example architecture diagram shows an MDS instance containing a single model: Product.

Entities are containers created within a model. Entities provide a home for members, and are in many ways analogous to database tables. Product, Color, SubCategory and Category entities exist in the sample diagram.

Members are analogous to the records in a database table (Entity). Members are contained within entities. Each member is made up of two or more attributes.

Attributes are analogous to the columns within a table (Entity). Attributes exist within entities and help describe members (the records within the table). Name and Code attributes are created by default for each entity and serve describe and uniquely identify leaf members. Attributes can be related to other attributes from other entities as seen in the diagram. For example the Color attribute of the Product entity is linked to the members contained in the Color attribute, so too the SubCategory and Category entities are related in the same way. These relationships are analogous to foreign key constraints.

Attribute Groups are explicitly defined collections of particular attributes. Say you may have an entity that is comprised of 50 different attributes; too much information for many of your users. Attribute groups enable the creation of custom sets of hand-picked attributes that are relevant for specific audiences.

Hierarchies organize members into either Derived or Explicit hierarchical structures.

Derived hierarchies, as the name suggests, are derived by the MDS engine based on the relationships that exist between attributes.
Explicit hierarchies are created by hand using both leaf and consolidated members. Explicit hierarchies can be further classified as mandatory or non-mandatory.

Mandatory hierarchies must include all entity leaf members.
Non-mandatory hierarchies do not require all leaf members be included, although unused members are by default collected in a hierarchy node named “Unused”.

Collections are customized subsets of members contained within hierarchies or other collections. Any entity that has a hierarchy associated with them supports the creation of collections. Shaun Ryan has put together a useful post on creating collections here.

Business Rules can be created and applied against model data to ensure that custom business logic is adhered to. In order to be committed into the system data must pass all business rule validations applied to them. In its current CTP version the business rules UI takes a bit of getting used to, nonetheless there is a lots of good functionality when it comes to information running the gauntlet before it is allowed in. Jeremy Kashel has a good introductory post on business rules here.

Subscription Views are views that can be created by appropriately privileged MDS admins in order to provide an appropriately named view for external systems to subscribe to. It should be noted MDS automatically creates views based on objects created within a model. Subscription views are separate from these and give admins control over the names and content. Shaun Ryan has written a post on the creation of subscription views here.

Versions provide system owners / administrators with the ability to Open, Lock or Commit a particular version of a model and the data contained within it at a particular point in time. As the content within a model varies / grows / shrinks over time versions provide a way of managing metadata so that subscribing systems can access to the correct content.

Enough Pies, I’m Full!

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 16:53:00 +0000

In homage to the Thanksgiving celebration about to take place in the USA I thought I’d place a bet on what I think will be the most overused (and least useful) feature of PPS 2010 analytic reports.

Multiple pie charts!

Those who have used ProClarity will recognize this multi-pie functionality. See how easily you can determine which of the clothing, bikes and components categories sold the most in CY 2008?

I believe that pie charts were included in PPS 2010 as a “required feature” by the sales team. If you listen carefully to some of the PPS team members as they present the latest features you can hear a slight tinge of cynicism in their voices as they say “oh yeah, we support pie charts now too…”

So I ask you, what’s better than a single pie chart?

Beginning MDS – Getting at the Data with TSQL (Part 7 of 7)

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 00:44:00 +0000

OK, so we’ve created the objects and loaded data into them. Now we can have a closer look at what has happened to the MDS database. What has been built? Where is the data stored?

The aim of this final post is to get you started towards locating your data stored in the MDS repository database. There are plenty of ways to get at the data but we’re going to just take a quick peek at accessing the data via TSQL. Remember that TSQL isn’t the only way to get at this data. I just haven’t had much of a chance to have a detailed look at the MDS web service and API yet.

Walkthrough

So where’s our Geography model data? Let’s start by finding the identifier of the model itself.

The ID for your model will vary, mine is 15.

Note that there are a number of different metadata UDFs that can return scalar and tabular data for a variety of things such as model ID, I’m just going to do it the manual way for the purposes of demonstration.
Armed with the model ID we can take a look at the Entities defined within that model.

The most interesting stuff returned by this query are the table name references to the structures containing records concerning Entity, Security, Hierarchy, HierarchyParent etc. Note the format of the table names. For example tbl_15_53_EN refers to a “table for Model ID= 15, ID = 53, for Type = Entity”.

The two letter table suffixes refer to the following:

EN = Entity
MS = Security
HR = Hierarchy
HP = Hierarchy Parent
CN = Collection
CM = Collection Member

The MDS engine builds tables to store data for the objects that are created within models. Here is a list of all the tables created as a result of our efforts with the Geography (ID = 15) model.

Have a look inside the table that contains the records for the City entity, remembering that your own IDs (both for the model and the entity) will vary from mine.

Among the other metadata related to the members of the City entity are the Name and Code fields, as expected. Note the column named uda_CAAPFLF at the far right of the table. The prefix “uda_” I assume stands for User Defined Attribute. Thanks to the referenced relationship created in one of the earlier walkthroughs this column participates in a physical FK relationship to the ID in the StateProvince entity table (in my case the StateProvince entity table is named mdm.tbl_15_54_EN).

Now take a look inside the mdm.tblAttribute table for all the user defined attributes in our model.

Notice the uda_CAAPFLF reference in the TableColumn column for StateProvince. This provides a reference back to the column that links the StateProvince attribute to the City entity.

On top of the system-generated-model-centric tables that MDS generates there are also system views that already do much of the heavy lifting for you when it comes to getting at the data. Here are the views that MDS created by the Geography model objects.

Based on what we have built the most useful views are the …CHILDATTRIBUTES ones. These will return the records within a particular entity including all the attributes that have been defined on it.

All the human readable data is located at the far end of the table, so remember to scroll all the way to the right.

Note the friendly column names that have been created as part of the view definition.

If you want to look at the parent/child metadata that was defined as a result of the derived hierarchy we created look at the contents of the …PARENTCHILD_DERIVED views.

Hopefully this whirlwind tour of the MDS repository DB has been enough to pique your interest. Take time to explore the inner workings of the database and find all the good stuff that is baked into the product and how you can leverage it.

This post also marks the end of this series of walkthroughs, hope they were useful.

Beginning MDS - Creating a Derived Hierarchy (Part 6 of 7)

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 00:43:00 +0000

There are two kinds of Hierarchy that can be created within MDS: Derived and Explicit. We’re only going to deal with derived hierarchies for now.

As the name suggests, derived hierarchies are derived from the relationships between entities within a model. In our Geography model we have used attributes to define a relationship between the City and the StateProvince entities and another one between StateProvince and CountryRegion. These relationships will enable the easy creation of a derived hierarchy. CountryRegion > StateProvince > City.

Walkthrough

Once more browse to the Master Data Manager and select System Administration

In the Model Explorer page select Manage > Derived Hierarchies

In the Derived Hierarchy Maintenance page ensure Geography is selected in the Model dropdown and click the + sign to add a new derived hierarchy.

Type Cities in the Derived hierarchy name textbox. Click Save.

All that is left to do in the Edit Derived Hierarchy: Cities page is to drag and drop each of the desired entities from the Available Entities and Hierarchies area into the Current Levels area.

Start with the lowest (in this case the leaf) entity first. Drag and drop the City entity onto the Current levels: Cities area. Note that the preview area comes to life now too.

Now drag and drop the StateProvince entity from the Available Entities and Hierarchies onto the City item in the Current Levels area. Finally drag and drop the CountryRegion entity onto the StateProvince entity.

Once you’ve used up all three entities, you’ll be able to preview your complete derived hierarchy.

On to Getting at the Data with TSQL

Beginning MDS - Loading Members & Attributes (Part 5 of 7)

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 00:43:00 +0000

Now that we’ve defined some basic structures we can add some data. Through MDS' web based management interface we can manually add individual members or configure attributes on an entity one-by-one, or we can load them en masse. I'll leave the one-by-one method to the reader to figure out. What most will want to know is how to get a lot of data into the system in one hit.

For those familiar with the product formerly known as PerformancePoint Services 2007 Planning, the process of batch loading records is much the same. You insert the data to be loaded into system-defined staging tables, ensuring the appropriate metadata is defined on each record. MDS internal stored procedures are run over the staged data to check the validity of the records in accordance with the entity & attribute structures that have been set up. Each record is marked with a flag and an error code to show whether it has passed or failed validation and provides details as to why. Once validated and error-free the data can then be loaded into the appropriate area within MDS. Action can also be taken on the bad records in order to get them loaded too.

Walkthrough

In this walkthrough we're going to:

Load leaf members into the City, StateProvince and CountryRegion entities
Load related StateProvince attributes into the City entity
Load related CountryRegion attributes into the StateProvince entity
Load freeform Spanish and French country names into the CountryRegion entity

Open up SSMS and connect to your MDS repository database (whatever you've called it). Mine's called "MDS".

Run the following TSQL to insert data into the mdm.tblStgMember table (I am assuming you've got the AdventureWorksDW2008R2 database installed on the same SQL instance)

-- insert City-grained entity members into tblStgMember   
INSERT INTO mdm.tblStgMember                    
  (                    
          ModelName                    
        , EntityName                    
        , MemberType_ID                    
        , MemberName                    
        , MemberCode                    
  )                    
  SELECT                    
        'Geography'                    
      , 'City'                    
      , 1                    
      , City                    
      , StateProvinceCode + '_' + UPPER(SUBSTRING(City,1,4)) + '_' + PostalCode                    
  FROM   AdventureWorksDW2008R2..DimGeography

If you wish you can have a look at the inserted records by running the following

Now that we know the data is in the staging table we can kick off the batch load process. Browse to the Master Data Manager web page and select the Integration Management option ensuring that you select Geography in the Model dropdown and VERSION_1 in the Version dropdown.

On the Import page (with the appropriate Model and Version selected in the corresponding dropdowns) note that there are 655 total member records that are flagged in the Unbatched Staging Records section. Click the Process button located above the Model dropdown.

Now the Staging Batches area at the top of the page comes to life showing that a new staging batch instance has been spun up for our 655 records. In the background the loading process is already running to validate our 655 records.

You can check on the status of the batch by clicking on the batch line item and then clicking the View details for selected batch button. The end-to-end process should take a few seconds.

Note that if you check the loaded records in SSMS (the mdm.tblStgMember table) the Status_ID field should be populated with a 1 indicating success. The ErrorCode field will be populated with “ERR210000” which I guess must mean “success” (doesn’t seem to be any doco on these codes at the time of this writing).

UPDATE (Nov 26th): MDS PM Kirk Haselden has listed all the staging table error codes here.

Once the batch has loaded we can check on the new members. Browse back to the Master Data Manager page by clicking on the MDS logo in the top left of the screen and select the Explorer option.

Select Entities > City to view the members that have been successfully loaded.

Note the yellow question marks next to each record. This means that business rule validation has not yet been run against these members. We won’t worry about business rules for the purposes of this walkthrough, you can ignore the yellow question marks for now.

If the question marks are really annoying you press the Apply Business Rules button (the one with the green check mark) to change them into green checks. We haven’t created any business rules to be applied so the change here is really only cosmetic in the context of this walkthrough.

Now we need to load members into the StateProvince and CountryRegion entities using the same process, just different TSQL. Once you have run the code to stage the members go back to the Integration Management screen kick off the batch process to load the members into their corresponding entities.

StateProvince

CountryRegion

-- insert CountryRegion-grained entity members into mdm.tblStgMember  
INSERT INTO mdm.tblStgMember                    
  (                    
          ModelName                    
        , EntityName                    
        , MemberType_ID                    
        , MemberName                    
        , MemberCode                    
  )                    
  SELECT DISTINCT                    
        'Geography'                    
      , 'CountryRegion'                    
      , 1                    
      , EnglishCountryRegionName                    
      , CountryRegionCode                    
  FROM AdventureWorksDW2008R2..DimGeography

Next we'll use the same method to load the attribute information into each of the entities using the same technique. The only difference is that this time we insert the data into the mdm.tblStgMemberAttribute table.

Insert values for the StateProvince attribute in the City entity.

-- insert StateProvince attributes into mdm.tblStgMemberAttribute  
INSERT INTO mdm.tblStgMemberAttribute                    
  (                    
          ModelName                    
        , EntityName                    
        , MemberType_ID                    
        , MemberCode                    
        , AttributeName                    
        , AttributeValue                    
  )                    
  SELECT                    
        'Geography'                    
      , 'City'                    
      , 1                    
      , StateProvinceCode + '_' + UPPER(SUBSTRING(City,1,4)) + '_' + PostalCode                    
      , 'StateProvince'                    
      , StateProvinceCode                    
  FROM AdventureWorksDW2008R2..DimGeography

…and now insert values for the CountryRegion attribute in the StateProvince entity.

Follow the same batch loading process as before to load the new attribute values.

-- insert CountryRegion attributes into mdm.tblStgMemberAttribute  
INSERT INTO mdm.tblStgMemberAttribute                    
  (                    
          ModelName                    
        , EntityName                    
        , MemberType_ID                    
        , MemberCode                    
        , AttributeName                    
        , AttributeValue                    
  )                    
  SELECT DISTINCT                    
        'Geography'                    
      , 'StateProvince'                    
      , 1                    
      , StateProvinceCode                    
      , 'CountryRegion'                    
      , CountryRegionCode                    
  FROM   AdventureWorksDW2008R2..DimGeography

Return to the Master Data Manager and ensure Geography and VERSION_1 are selected in the Model and Version dropdowns. Click the Explorer option and examine the members and attributes of both the City and the StateProvince entities. The attributes within each will now have been populated with the corresponding member of the appropriate entity.

City entity, StateProvince attribute

StateProvince entity, CountryRegion attribute

All that is left to do now is populate the freeform FrenchCountryRegionName and SpanishCountryRegionName attributes of the CountryRegion entity. Same method, different TSQL. Here is the code:

-- insert French country name attribute values into mdm.tblStgMemberAttribute  
INSERT INTO mdm.tblStgMemberAttribute                    
  (                    
          ModelName                    
        , EntityName                    
        , MemberType_ID                    
        , MemberCode                    
        , AttributeName                    
        , AttributeValue                    
  )                    
  SELECT DISTINCT                    
        'Geography'                    
      , 'CountryRegion'                    
      , 1                    
      , CountryRegionCode                    
      , 'FrenchCountryRegionName'                    
      , FrenchCountryRegionName                    
  FROM AdventureWorksDW2008R2..DimGeography

-- insert Spanish country name attribute values into mdm.tblStgMemberAttribute  
INSERT INTO mdm.tblStgMemberAttribute                    
  (                    
          ModelName                    
        , EntityName                    
        , MemberType_ID                    
        , MemberCode                    
        , AttributeName                    
        , AttributeValue                    
  )                    
  SELECT DISTINCT                    
        'Geography'                    
      , 'CountryRegion'                    
      , 1                    
      , CountryRegionCode                    
      , 'SpanishCountryRegionName'                    
      , SpanishCountryRegionName                    
  FROM AdventureWorksDW2008R2..DimGeography

If you want to, admire your handiwork the same way you did before by browsing to the Explorer and viewing the contents of the CountryRegion entity, now complete with country names in French and Spanish.

TIP: if you want to clear out the staging tables run the following sproc.

On to Creating a Derived Hierarchy

Beginning MDS - Creating Attributes (Part 4 of 7)

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 00:42:00 +0000

Attributes are defined within entities. An attribute contains values that help to describe the member they’re related to. For example our ProductName leaf entity within a Product model could have a freeform attribute defined to hold each item's Standard Cost or Weight. Attributes can also reference members of other entities defined within the same model. By referencing members in other entities we can maintain a master list of say Colors (in the Color entity) and then relate members of the product entity to the color entity, very much like a foreign key relationship (in fact, it is a foreign key relationship)

Walkthrough

In this walkthrough we're going to create attributes on the City, StateProvince and CountryRegion entities within the Geography model.

Attributes are defined and maintained within entities, so on the Master Data Manager page select System Administration to administer the entities we created in the previous post.

In the Model Explorer page select Manage > Entities.

In the Entity Maintenance page ensure Geography is selected in the Model dropdown. Click the City entity to select it and note the toolbar buttons become visible. Click the pencil icon to edit the properties of the City entity.

In the Edit Entity: City screen in the Leaf Attributes section click the + sign to add a new attribute underneath the default Name and Code attributes that already exist by default.

In the Entity: City Add Attribute screen, select the Domain-based radio button, type StateProvince in the Name textbox and select StateProvince in the Entity dropdown. In the MDS repository DB this will create a physical foreign key constraint between the City and StateProvince entities. Click the save button when done. Click save again to save and exit the City entity maintenance screen.

Using the same steps as above create a domain-based attribute on the StateProvince entity with the name CountryRegion referring to CountryRegion entity. Click save and then save again to exit the StateProvince entity maintenance screen.

Now we'll add two attributes to the CountryRegion entity using the Free-form option, one for FrenchCountryRegionName and one for SpanishCountryRegionName. Use the same steps as before to create these two attributes.

Name: FrenchCountryRegionName, DataType: Text, Length: 100

Name: SpanishCountryRegionName, DataType: Text, Length: 100

After adding the FrenchCountryRegionName and SpanishCountryRegionName leaf attributes your CountryRegion entity should now look like the shot below.

Let's have a look at what we've got so far. Click on the Explorer link in the top left of the screen and click the Geography model to display the model and its entities on the right-hand side of the screen.

On to Loading Members & Attributes

Beginning MDS - Creating Entities (Part 3 of 7)

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 00:41:00 +0000

One or more Entities can be defined within a model. Entities are the foundational objects within an individual model and serve as the containers for Members, the data records themselves. For example a product model could contain entities such as ProductName, Category, SubCategory and Color to describe and classify the model contents. The Color entity would contain members for Blue, Red, Yellow etc. The ProductName entity would contain the names of the products themselves and so on.

Walkthrough

In this walkthrough we're going to create entities for City, StateProvince and CountryRegion

In the Master Data Manager select System Administration which is where we will manage the structures that make up the Geography model.

In the Model Explorer page select Manage > Entities

When we created the Geography model we chose to automatically create an entity with the same name as the model. We’re going to change the name of that auto-created entity from Geography to City. In the Entity Maintenance screen select Geography in the Model dropdown. This will display any entities defined within the model.

Click the line for the Geography entity (the only one there). This will display the tools available to us for working with the selected entity. Click the pencil icon to edit the entity metadata.

In the Edit Entity: City section change the value in the Entity name textbox from Geography to City. Click save when done.

Now we're going to add two new entities. In the Entity Maintenance screen hit the + sign to add a new entity.

In the Add Entity screen enter StateProvince in the Entity name textbox and choose No in the Enable Explicit hierarchies and collections dropdown. Click the Save button.

Create another entity with the same settings but call this one CountryRegion.

Your list of entities should look like the shot below.

On to Creating Attributes

Beginning MDS - Creating a Model (Part 2 of 7)

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 00:40:00 +0000

Models are the highest level container within an instance of MDS. Models are created to manage groups of similar data. In BI-speak it’s not much of a stretch to equate a model with a dimension, they’re not exactly the same but thinking about it in this way helps understand the concept. The two classic master data models are that you’ll see in most examples are Product or Customer. Once a model is created we can define objects within it including entities, attributes and hierarchies, among others.

Walkthrough

In this walkthrough we're going to create a Geography model to manage our geographical master data. Subsequent walkthroughs will then build other objects inside our Geography model.

Browse to the Master Data Manager page, the primary management web page for MDS found (if default settings are used) at http://localhost/MDS. Click System Administration.

In the Model Explorer page select Manage > Models

In the Model Maintenance screen you will see a list of all the existing models. If you’ve just done a fresh install the only model you’ll see will be Metadata. Click the + button to create a new model.

Name the model Geography and click Save.

The Geography model has now been created.

On to Creating Entities

Beginning Master Data Services (Part 1 of 7)

noreply@blogger.com (Nick Barclay) — Wed, 25 Nov 2009 00:39:00 +0000

Like many other geeks out there I learn by doing. One of the things on my todo list has been to get familiar with MDS. During my experimentation with the recently released CTP I figured I'd take some notes on what I learned. These notes have evolved into a series of posts that will walk through some of the basics in putting MDS to work.

At this point we're only in the first public CTP, but everyone's just a bit curious to kick the MDS tires a bit. We all know there's quite a lot of functionality baked into the product in terms of workflow, versioning, web services and APIs but how about just the basics. These posts act as a quick start to see MDS in action. Once you’ve put some data into the system you can pull back the covers and have a look at how it happened and where the data is. We all learn something that way.

The walkthroughs will go through the creation of a very simple Geography MDS model based on the data contained in the DimGeography table in the SQL 2008R2 release of the AdventureWorks DW database. In the posts to follow we will walk through the following:

All posts assume that you have already installed MDS and have the AdventureWorksDW2008R2 DB set up on the same server.

On to Creating a Model

How believable are paid evangelists, anyway?

noreply@blogger.com (Nick Barclay) — Tue, 17 Nov 2009 01:13:00 +0000

Many of you may know Don Dodge - he’s a start up and technology evangelist who, up until a day ago, worked for Microsoft. Apparently Don was part of the most recent round of layoffs. He was immediately snapped up by Google. Good for them.

The funny thing here is the contents of Don’s Thanks Microsoft, Hello Google post. While he’s completely entitled to his opinions, I am amazed at how quickly they changed. It really made me wonder just how much of an evangelist’s passion is determined by who signs their paycheck. As usual, Fake Steve Jobs provides analysis as only he can.

Who let the Bulldog out?

noreply@blogger.com (Nick Barclay) — Fri, 13 Nov 2009 15:07:00 +0000

‘member what happened when Microsoft said “Hey, let’s bundle a reporting engine into the SQL Server license”, “Hey let’s bundle an OLAP engine into the SQL Server license”, “Hey let’s bundle an ETL engine into the SQL Server license”? Well, they’re doing it again.

The other day I downloaded and installed the latest CTP of SQL Server 2008 R2. Although there are plenty of good things to talk about in this release the one that really interests me (and many others) is Master Data Services or MDS, originally codenamed “Bulldog”. Once again Microsoft is being disruptive by bundling yet another <InsertNameHere> Services product into the SQL Server stack.

MDM is Enterprise only. Not for long…

In its magic quadrants, Gartner splits analysis of Master Data vendors into Customer and Product master data categories. Their analysis of MDM players contains vendors that are very much enterprise focused and don’t sell huge volumes of licenses. Many of these vendors reference Fortune 500 companies as their customers. This reinforced by belief that MDM is very much an enterprise only playground. The license and maintenance revenue from small volumes of customers is enough to sustain these vendors’ business models. Translation: big license fees & big maintenance fees. I’m sure the products are worth every penny, but not every business can justify spending big money on buying and implementing MDM.

Companies that deal with hundreds of thousands, or even millions, of different SKUs or unique customers need a way to manage that one version of the truth for their incredibly large and complex global businesses. This is fine for those that can justify spending the amount of money needed to accomplish this, but what about the company with just 500 SKUs and 10,000 customers? They may still have tons of money, hell they my even be Fortune 500, but they may not have mountains of master data records to manage. Even the most cashed up companies would think twice about spending vast sums of money on ways to manage small volumes of critical master data. IMO the enterprise vendors are not interested in these companies and these companies are not interested in enterprise vendors.

Enter MDS. Cost? Included in SQL Server license.

The Incumbents

I’m sure the established players in the MDM space are snickering behind their hands at Microsoft’s audacity in trying to muscle in on the MDM market. They’re already hard at work compiling comprehensive lists of “but does it have…?”, “can it do…?”, “it can’t…” and the ever-popular “C’mon, it’s Microsoft! Wait ‘till SP1 comes out.”

To be sure, there are plenty of good reasons the incumbents have as to why MDS may pale in comparison to their own technology stack. There is no question that MDS will be playing catch up here. Most of the others have been in business a long time and have excellent, very mature products. No argument there. Keep in mind, though, that MDS is also based on a pretty mature MDM product, Stratature, that was acquired by Microsoft in 2007. Nonetheless I’m sure there will not be as many features baked into MDS v1 when compared with the other market players.

The incumbents are focused on the big enterprise fish who have nasty, hairy, complicated master data problems that need to be solved. Of course, that’s their target market. These are the customers who will can (and want to) pay for what the incumbents have to offer. No doubt it’s good stuff, but what about the business who just wants a central place to manage the names and hierarchies of their 100-ish sales territories and their exclusive list of 2,000 customers? Do they need all the enterprise MDM bells, whistles and cost? Probably not. They’ve been making do with Excel. Until now.

Got Lookup Tables?

Raise your hand if you’ve ever created a lookup table that had to be maintained or watched over by someone who is umm, not so technical. If you suggested forking out a large pile of cash to purchase MDM software to assist this non-technical person maintain proper control of small volumes of simple data you were probably laughed at.

“Do it in Excel”, “Create a table in a DB and build a UI for maintenance”, “Use Access…”. These are the thin-end-of-the-wedge scenarios that will allow MDS to gain footholds in places that the other vendors would not even get out of bed for. Like Analysis Services and Reporting Services the barriers of entry for the IT geeks to start playing around with and eventually deploying MDM into production will be drastically lowered.

Don’t get me wrong, there’s still going to be plenty of big, complex enterprise MDM scenarios that MDS will tackle as well. *But* (I think) there is going to be a whole new breed of non-enterprise MDM customer that will start making themselves known very soon.

The 4 P’s of MDM

When you start looking into the world of MDM one of the first things you quickly realize is that the software, while critical to the process, is not even close to the complete solution. Anyone who has been involved with an MDM project will tell you that while good software definitely helps, the real success of a master data initiative is inexorably linked to all of the 4 P’s of MDM:

Processes
People
Politics
Product

Notice that product only makes up 25% of this. Figuring out the technicalities how to use MDS is not going to be much of a chore for most BI / IT pros. The real challenge is getting the other 75% of the 4 P’s in place.

MDS, as part of the SQL stack, frees up funds to spend on getting all four P’s right. There is reasonable about of consulting hours that can be purchased with the money saved when you don’t see any increase in SW license costs.

What’s under the hood?

As I get stuck into the internals of the product I will blog more. From what I’ve toyed around with so far the product looks interesting, is simple to set up, and should be pretty easy for both geeks (getting stuck into the DB, web services and API) and the non-geeks (who will use the web-based UI to manage things) to get a handle on. On digging into some of the more complex looking objects within the repository DB and web-based UI you can see that there is a lot of good stuff to explore and experiment with.

Jamie Thompson blogged about his delight in discovering that MDS implements some very cool Regex and Fuzzy lookup functionality. I’m sure Jamie’s next question was whether there are any MDS-flavored custom SSIS transforms or tasks included in the initial release. I asked the same question. Answer: There aren’t any. Yet. The group PM for MDS is Kirk Haselden, you may remember Kirk from such products as SSIS where was the dev manager and one of the product’s primary designers. With Kirk’s involvement you can be pretty damn sure there will be some SSIS goodness that will make its way into MDS at some point in the foreseeable future. For now, though, you can interact with data via the MDS web service, API or just plain ‘ol TSQL. Plenty of options there for SSIS to hook into. More on this as I play around with the product.

Final Thoughts

MDS will go head-to-head with the established enterprise MDM players, no question. In the short term the product will probably not make much headway in that market, though. No surprises there. However, think of what the potential is for businesses that the big vendors don’t care about right now. Those who own SQL Server licenses and have even the smallest requirement for managed master data are all fair game.

PerformancePoint Services - What’s Deprecated

noreply@blogger.com (Nick Barclay) — Thu, 05 Nov 2009 12:47:00 +0000

Continuing on from the What’s New and What’s Changed / Improved / Different posts, here are some of the things that will be going away in PerformancePoint Services.

OWC Support

No more OWC-based PivotCharts, PivotTables, Trend Charts and Excel Spreadsheets. Good.

ASP.NET Dashboard Preview Site

One of my favorite features of PPS 2007 is now gone. Because the storage and management of elements are now almost entirely MOSS based you will need a complete installation of MOSS 2010 to be able to play with the new stuff. The silver lining here is that MOSS 2010 will be supported in a Developer configuration on Vista and Win7 PCs. So now developers will be able run their own sandbox environment locally.

Support for SSAS 2000 databases

Anyone who still has an SSAS 2000 DB running in production ought to be ashamed of themselves.

ODBC Tabular Data Sources

ODBC data sources were a rarely used feature of PPS 2007, and BSM for that matter. Although you could connect to just about any data source you wanted to, you could only bring return a scalar value per data source element which made these data source types tedious and of little real value.

32 bit Architecture

Because PPS is now part of the MOSS 2010 furniture it goes without saying that it only supported on 64 bit platforms. Hello better scalability.

PerformancePoint Services - What’s Changed / Improved / Different

noreply@blogger.com (Nick Barclay) — Thu, 05 Nov 2009 12:46:00 +0000

Continuing on from the What’s New post, here are some of the changes and improvements to the product. This post covers some of the more subtle changes and improvements to the product. You could argue that some of these belong in the “what’s new” post, but let’s not split hairs here.

Again this is by no means an exhaustive list, but I think I’ve got most of major ones in here. As before I’d love to include screenshots but cannot because they’re from the Beta 1 build and MS have asked me not to include these. Beta 2 should hopefully be out some time this month.

Analytic Charts & Grids

The overall improvements to the Analytic report engine both from a designer and end user perspective continues to get better. Here are some of the major improvements.

Improved chrome - Charts now look shinier. Like pie charts this is something that many users will appreciate but doesn’t really add any significant analytical value. Nonetheless pretty things tend to impress some users.

Select measures - Users can add or remove individual measures from chart or grid using a set of checkboxes.

Interactive chart labels - In PPS 2007 the graphical elements themselves i.e. the bars or lines within the graphs were the interactive parts. The labels on the X and Y axes of graphs can now also be right-clicked to expose interactive functionality too.

Filtering - Top / Bottom N filtering capabilities are available for both end users and developers.

SSAS cell formatting surfaced - Cube-based cell formats will be brought through and displayed in analytic grids. [Big round of applause]

Per measure formatting – The format of individual members can be altered in the designer. Nice feature but because the analytic reports are pulling data from a cube the formats should be correctly applied therein to begin with.

No more design time browse button - In order to test the interactivity of a particular analytic report there is no need to launch a separate window via the Browse button. Designers can interact with the charts and grids directly.

Better cube object browser experience - The cube metadata in the Details Browser is properly organized in the way we are used to within SSMS, Excel and others. Dimension attributes, attribute hierarchies and folders are grouped within their parent dimension containers. You can also filter the content of the pane for a specific measure group.

Scorecards

In general there are several new interactivity & layout capabilities that have made their way into scorecards. I haven’t had much of a chance to explore the changes to this element fully. Suffice it to say that there will be more opportunities for people to try and make scorecards into reports by treating the scorecard element as a pivot table and then being disappointed when it doesn’t deliver the exact functionality they expect. Hopefully the new scorecard power will be used with appropriate responsibility.

Dynamic dimensional axes – scorecards support user interactivity with dimension hierarchies. In PPS 2007 you had to add individual members or sets and craft the axis hierarchy by hand. Now you can add, say, the All member of the Product Categories hierarchy and you will have full interactive access to all its descendants within the scorecard.

New target metric display settings - have a new set of dialogs with several new options including the ability to calculate and display the variance between the target and its associated actual. You can also configure the variance calculation to show either a Percentage of Variance or just a number. Within the Percentage of variance there is an option to show either a Difference from value or Progress toward value.

Data Sources

Security per data source element - This is a really good one. Security context can now be configured on each individual data source element. In PPS 2007 data source security was a server level setting and was a source of many questions in the PPS forum. In the latest version you can choose what security scenario you’d like to apply to each data source definition within Dashboard Designer. The two primary methods being the use of the Unattended Service Account or Per-User Security. The only difference between the SSAS and Tabular data source configuration options respect is the SSAS data sources offer the ability to make use the CustomData connection string property as well.

KPIs

Multiple actuals per KPI – You can now create more than one Actual within an individual KPI. This one sounds trivial but it’s not, it opens up the door to much richer KPIs. BSM and PPS 2007 supported exactly one actual metric per KPI. Because you can now have more than one actual the designer provides the ability to link each target metric to the appropriate actual metric.

Metric cloning - When configuring the data source of a specific metric you can point towards another metric and clone its settings. So you’ve already created a metric that has a data source, calculation or setting that you want to clone you can point the data source property of your new metric to the existing metric. Under the covers Dashboard Designer copies the settings of the target metric into your new metric. A nice time saver. Note however that this is a one time clone of settings, the element definitions will not remain synced thereafter.

Calculated metrics – When configuring the data source property of a metric there are some new dialogs to help configure calculations. Using them you can create calculations that reference other KPI metrics. Some pre-built calculation will be available such as Growth as Percentage, all you need to do is fill in the formula to reference the appropriate metrics. The PPS team posted some details on this new feature the other day. This may come in handy for KPIs that source metric data from different places. On the other hand, if your KPI sources data from a single cube these kind of calculations should be baked into the cube itself whenever possible.

Time Intelligence Formulas

Two new functions have been added to the STPS. They are the <PeriodName>ToDate and the Full<PeriodName> functions. They have been created to enable better period-to-date functionality. But couldn’t we already to that kind of stuff with the original PPS TI formulas? Sort of. If you wanted to do a YTD using PPS 2007’s TI functionality you would define a range of values with an STPS formula. This would return a set of appropriate period members. For example Year.FirstMonth : Year.Month would return a set of month members from Jan-Nov for the current year. This was great if you wanted to apply the set of members across a scorecard or report axis but it did not help if you wanted to do something like provide a YTD column on a Scorecard using TI. The new functions fill that need. When used together the …ToDate and Full… functions return an aggregation object of sorts as opposed to a set of members. For example the STPS formula YearToDate.FullMonth returns a TI aggregation object for the year-to-date value up to the last full month. QuarterToDate.FullDaty returns an aggregation object for the current quarter up to the last full day.

PerformancePoint Services - What’s New

noreply@blogger.com (Nick Barclay) — Thu, 05 Nov 2009 12:46:00 +0000

Now that the NDA has been lifted it’s time to start talking about PerformancePoint Services. I plan to expand on many of these points in upcoming posts. For now I’ve put together a series of summary posts on some of the things that are

There is plenty to talk about in the new release, these lists are by no means comprehensive in their coverage of PerformancePoint Services. I also expect that the PPS team has a whole stack of useful posts loaded and ready to go, so keep and eye on their blog for a lot of new information in the near future.

UPDATE: Here is a detailed post from the PPS team (including screenshots) about all the new stuff

I wish I could provide screenshots of all that is contained in these posts but I am unable to post shots of Beta 1. More visuals will come as Beta 2 is released.

SharePoint Integration

OK, we all know this one, PerformancePoint Services is now part of the Enterprise CAL of MOSS 2010, it is no longer a separate product. Element definitions are stored and managed within SharePoint lists and libraries and will be recognized as first class citizens in the MOSS world. This is going to bring a number of advantages in the MOSS 2010 world and will be the subject of quite a few posts in the future. Lots to talk about here.

Unfortunately, the MOSS integration strategy that was announced in January this year seems to have taken a big slice of the dev team’s time and hence the amount of new and changed features in PPS are not huge in number. That being said I do not want to trivialize the time and effort that went into the MOSS integration of PPS. The benefits of this re-architecture effort will make themselves known as we all begin to explore the new MOSS platform and all the other BI goodness (outside of PPS) that is baked into it.

The 7th Element: Filters

Filters are now an element unto themselves. In PPS 2007 filters were part of dashboard element definitions and therefore could not be shared. In PPS 2010, if you’ve built a useful filter you can share it with all of your dashboards just as you would any of the other elements. This is a welcome architectural change that many were hoping for (not sure whether this really falls under the “new” category…).

The filter types that are available to us and their functionality have not changed very much in this release, they’re just a separate element now. With this new architectural change many will ask whether this means that we can pass values from one filter to another i.e. cascading filters. The answer: no, not yet. I wouldn’t be surprised if this feature was one that got cut in lieu of time required for the MOSS integration work.

Decomposition Tree Drilldown

I’ve always liked the ProClarity’s decomp tree and am really glad to see this new Silverlight-based version as part of drilldown interactivity within analytic reports. The decomp is not a report type unto itself but a “Analyze > Decomposition Tree” option from the right-click menu within the analytic chart and grid reports. Unfortunately the decomp tree is about the only recognizable ProClarity bit that made it into this release.

New Chart Type: Pie Charts (groan)

One of the things I liked about PPS 2007 was the fact that Analytic reports didn’t support pie charts. I think this was a feature that the MS sales team had a hand in. If you, or anyone you know, still believe that pie chart has any real analytical value please refer yourself (or the people you know) to Stephen Few’s excellent Save the Pies for Dessert whitepaper. If after reading it you still think pie charts are worth using on a dashboard then there is no hope for you.

KPI Details Report

As its name suggests this report supplies all the details about a particular KPI. This is a welcome addition. It’s a simple display of KPI metadata that can be hooked to any scorecard. Clicking on a KPI within the related scorecard will display its information in this report. When configuring the report you can choose which pieces of KPI metadata to display.

The lack of this feature in PPS 2007 was one of the driving reasons behind why I built the MAUDF project a while back; to provide simpler access to this kind of data. My customers wanted to see banding thresholds, descriptions and other metadata pertaining to a KPI. The new report does all of this.

Un-cooking the books with Benford’s Law

noreply@blogger.com (Nick Barclay) — Mon, 26 Oct 2009 17:44:00 +0000

So, if you take a set of real life numeric data (e.g. sales figures, customer sat scores, baseball game attendance figures) stripped the first digit off each number and counted those up what would the distribution of numbers be? i.e how many 7s would there be? How many 2s? Would there be a pattern to the distribution? The answer is actually yes. It’s known to many number crunchers as Benford’s Law.

Here's the Wikipedia definition: Benford's law, also called the first-digit law, states that in lists of numbers from many (but not all) real-life sources of data, the leading digit is distributed in a specific, non-uniform way. According to this law, the first digit is 1 almost one third of the time, and larger digits occur as the leading digit with lower and lower frequency, to the point where 9 as a first digit occurs less than one time in twenty.

Basically the law means that in many real-life (i.e. non-made-up or random) data the distribution of the first digit in a series of numbers will often look very similar to the graph below (also from Wikipedia).

My CPA brother-in-law refers to “running the Benfords” when on auditing gigs in order to perform a quick acid test on pertinent sets of numbers to see if there is something that warrants further investigation. If the distribution doesn’t look similar to the graph above then he looks a little closer; maybe someone’s been cooking the books.

As DBAs, BI pros (and maybe some former accountants) we have plenty of real world data at our fingertips. Why not test Mr. Benford out to see if our data conforms. Benford’s law states that the data has to be real-life so let’s test it by applying the theory to three different data sets:

Randomly generated numbers
Made up numbers (AdventureWorksDW2008 sales figures)
Real data from a real life data source

Random Numbers

I’ve created a simple script to create set of randomly generated numbers and perform a basic Benford analysis of the results. Here’s what it does:

Creates and populates a table variable with 65,536 random numbers
Populates CTE with the random numbers and a separate column holding the first digit of each number
Counts the instances of each digit and returns the results in a table including a simple histogram column for numbers 1-9 (excluding any 0 digit values sometimes returned by my simplistic random number algorithm)

USE tempdb;
GO

DECLARE @RandomNumbers TABLE
(
    RandomNumber INT
);

-- Use Itzik Ben Gan's technique to quickly generate 65536 records
WITH
n5(x) AS (SELECT 1 UNION SELECT 0),
n4(x) AS (SELECT 1 FROM n5 CROSS JOIN n5 AS x),
n3(x) AS (SELECT 1 FROM n4 CROSS JOIN n4 AS x),
n2(x) AS (SELECT 1 FROM n3 CROSS JOIN n3 AS x),
n1(x) AS (SELECT 1 FROM n2 CROSS JOIN n2 AS x)

-- Create 65536 random numbers
INSERT INTO @RandomNumbers
SELECT
    ABS(CHECKSUM(NEWID())) % 100000 AS RandomNumber
FROM n1;

-- CTE containing each random number and its first digit
WITH BenfordTest (FirstDigit, RandomNumber) AS
(
    SELECT
         SUBSTRING(CAST(RandomNumber AS VARCHAR(MAX)), 1,1) AS FirstDigit
        ,RandomNumber
    FROM @RandomNumbers
)

-- Count how many instances of each number there is from 1-9
SELECT
     FirstDigit
    ,COUNT(*) AS InstanceCount
    ,REPLICATE('|', 100. * (CAST(COUNT(*) AS NUMERIC(5,0)) / b2.Total)) AS PctDistribution
FROM BenfordTest b1,
    (SELECT CAST(COUNT(*) AS Numeric(5,0)) Total FROM BenfordTest) b2
WHERE FirstDigit > 0 -- exclude any zero digit records
GROUP BY FirstDigit, b2.Total
ORDER BY FirstDigit ASC;

And here are the results. Because these are random numbers you can see that the PctDistribution column, and the Excel graph I created is pretty much uniformly distributed between about 10.8% and 11.5% for all leading digits.

Made Up Numbers (AdventureWorksDW2008)

Now we all know that AdventureWorks data is not real, but is there any “realness” to it at all? Probably not. We’ll test against the SalesAmount column in the FactInternetSales table.

BTW, analysis like this is a great excuse to use the TABLESAMPLE clause in order to avoid querying the entire table.

USE AdventureWorksDW2008;
GO

WITH BenfordTest (FirstDigit) AS
(
    SELECT
         SUBSTRING(CAST(SalesAmount AS VARCHAR(MAX)), 1,1) AS FirstDigit
    FROM FactInternetSales
    TABLESAMPLE (20 PERCENT)
)

SELECT
     FirstDigit
    ,COUNT(*) AS InstanceCount
    ,REPLICATE('|', 100. * (CAST(COUNT(*) AS NUMERIC(5,0)) / b2.Total)) AS PctDistribution
FROM BenfordTest b1,
    (SELECT CAST(COUNT(*) AS Numeric(5,0)) Total FROM BenfordTest) b2
GROUP BY FirstDigit, b2.Total
ORDER BY FirstDigit ASC;

OK, I guess that proves just how real AdventureWorks data really is. This provides some pretty good evidence that AW numbers were not generated randomly and were almost certainly not based on real sales figures. On the other hand maybe the regional cycling gear reps are fudging the numbers a bit….

Real Numbers

Here are results of a query I ran on some real data I have available to me. Trust me, it is real data but, naturally, I can’t share it. The distribution looks about right, too. Kinda cool, eh?

You can try the sample code out yourself on some data that you have access to.

Here’s the code stub:

WITH BenfordTest (FirstDigit) AS
(
    SELECT
         SUBSTRING(CAST(<NumberFieldName> AS VARCHAR(MAX)), 1,1) AS FirstDigit
    FROM <SourceTableName>
    TABLESAMPLE (20 PERCENT)
)

SELECT
     FirstDigit
    ,COUNT(*) AS InstanceCount
    ,REPLICATE('|', 100. * (CAST(COUNT(*) AS NUMERIC(5,0)) / b2.Total)) AS PctDistribution
FROM BenfordTest b1,
    (SELECT CAST(COUNT(*) AS Numeric(5,0)) Total FROM BenfordTest) b2
GROUP BY FirstDigit, b2.Total
ORDER BY FirstDigit ASC

The complete code from this post can be downloaded here.

Conditionally Hiding Axes for Trellis Displays

noreply@blogger.com (Nick Barclay) — Wed, 21 Oct 2009 17:37:00 +0000

Over the last year MVP Tim Kent has put out a series of really useful posts showing how various data visualizations can be created using SSRS.

The latest post on Trellis displays got me thinking on how I could tweak a few of the settings in Tim’s very useful sample report just a bit more.

In order to show more sample data, I changed the top axis of Tim’s sample report to show sales Bike subcategories because as we all know AdventureWorks sells waaaay more bikes than anything else. Below is a shot of the original report after that change.

I made a few more tweaks and changes and came up with the report below

The main ink-saving tip is to conditionally hide / show labels on the X and Y axes based on the items at the top left and bottom left of the trellis. In this case it is Road Bikes and Northeast. All that is needed here is a small amount of extra MDX to ORDER and RANK members in both the Region and Subcategory sets to provide the right meta data required to perform the conditional hide / show. Here is the MDX for the report showing the ordering and ranking of the appropriate sets.

WITH
SET [SalesOrderedSubcategories] AS
    Order
    (
      [Product].[Product Categories].[Category].[Bikes].Children
     ,[Measures].[Sales Amount]
     ,BDESC
    )
SET [SalesOrderedRegions] AS
    Order
    (
      [Sales Territory].[Sales Territory].[Region].MEMBERS
     ,[Measures].[Sales Amount]
     ,BDESC
    )
MEMBER [Measures].[SubcategoryRank] AS
    Rank
    (
      [Product].[Product Categories].CurrentMember
     ,[SalesOrderedSubcategories]
    )
MEMBER [Measures].[RegionRank] AS
    Rank
    (
      [Sales Territory].[Sales Territory].CurrentMember
     ,[SalesOrderedRegions]
    )
SELECT
{
    [Measures].[Sales Amount]
   ,[Measures].[SubcategoryRank]
   ,[Measures].[RegionRank]
} ON COLUMNS
,(
    [SalesOrderedSubcategories]
   ,[SalesOrderedRegions]
   ,[Date].[Calendar Quarter of Year].[Calendar Quarter of Year].MEMBERS
) ON ROWS
FROM [Adventure Works];

Set the SORT property of each group to its respective GroupNameRank calculated member, this way we can be sure that in our example the top ranked Subcategory (Road Bikes) will be the left-most item and the lowest ranked Region (Southwest) will be the bottom-most item in the trellis.

The real trick here is to use an expression to conditionally hide / show the axis labels so that we only see the X-axis labels at the bottom of the trellis and Y-axis labels on the left side of the trellis.

Y-axis uses an expression that only shows the axis for the MIN ranked member for Subcategory

=IIF(
    Fields!SubcategoryRank.Value = MIN(Fields!SubcategoryRank.Value, "Trellis")
    , false
    , true
)

and X-axis uses the MAX ranked member for Region

=IIF(
    Fields!RegionRank.Value = MAX(Fields!RegionRank.Value, "Trellis")
    , false
    , true
)

It also helps to check the Hide first and last labels along this axis for the Y-axis as the zero value tends the throw off the alignment with the other charts where the X-axis is hidden.

The sample .rdl file can be downloaded here.

Windows Problem Steps Recorder

noreply@blogger.com (Nick Barclay) — Fri, 16 Oct 2009 16:35:00 +0000

Been meaning to write about this one for some time. This year’s TechEd USA keynote was centered around Windows Server 2008 R2. The speakers demoed plenty of good features but the one that stood out (and easily got the most spontaneous applause) was the Problem Steps Recorder. It does exactly as its name suggests: it records problem steps.

The PSR is a really simple app that is baked into both Windows Server 2008 R2 and Windows 7. It’s actually not that easy to find unless you know what you’re looking for – the Start menu doesn’t even list the app by its name.

The UI couldn’t be simpler as seen below.

The user opens the app, presses Start Record, performs the steps that reproduce a problem they’re experiencing (including their own comments if needed), then presses Stop Record. PSR then asks where they want to save the results. The output produced is a zip archive containing a single MHT file. The user then emails the zip archive to the helpdesk or whoever is trying to help them.

The generated MHT file is a navigable, screenshot-by-screenshot, annotated document of each step the user performed while recording. Below is an example step explanation with screenshot.

Here is a text summary of each step without screenshots that is found at the bottom of each MHT file.

OK, so why does PSR have to be just a helpdesk tool? Like many others in the audience I thought of this as a potential quick & dirty documentation tool. Admittedly out of the box the text produced describes every step as a “Problem” but once you look past that this could be a great little time saver in a number of situations.

Here’s an example output file that walks through creating an SSRS project and adding a new report in BIDS.

Data Dude Error TSD03006 – Explicit Database Reference

noreply@blogger.com (Nick Barclay) — Fri, 16 Oct 2009 14:07:00 +0000

Problem

You’ve just created a new Data Dude project and imported your DB schema, or you’ve just synchronized schemas with an existing project having added several new views / UDFs / SPROCs etc. When you try to build your project you find that there are tons of TSD03006 errors that are stopping you. Why?

TSD03006: View: [dbo].[vFactResellerSales] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[DimProduct].[s]::[ProductKey], [dbo].[FactResellerSales].[ProductKey] or [dbo].[FactResellerSales].[s]::[ProductKey]

Solution

Ensure any explicit database name references do not exist in your code. If you need to explicitly reference a database do it by using variables.

Removing the explicit DB references from the code should make the TDS03006 errors disappear. Admittedly you should be cleaning up any explicit DB references within your code, but when whipping up a new script in the early hours of the morning this may be something that you’d miss. And suddenly getting a whole heap of build-blocking errors at 3am can be incredibly annoying.

Sometimes explicit DB references can creep into code without you catching it. For example when creating views from large tables I often start by scripting out a SELECT statement within SSMS. The code that is generated by SSMS includes an explicit reference to the database.

SELECT [ProductKey]
       ,[OrderDateKey]
       ,[DueDateKey]
       ,[ShipDateKey]
       ,[ResellerKey]
       -- Shortened for brevity
       ,[CustomerPONumber]
FROM   [AdventureWorksDW2008].[dbo].[FactResellerSales]

It’s the explicit [AdventureWorksDW2008] reference that causes the TSD03006 errors. If you add to the SSMS generated code and join other tables but keep even one explicit DB reference you will continue to receive TSD03006 errors for each field in the view definition.

Data Dude is indeed bringing a valid problem to our attention but I think there should be a more elegant way to communicate the situation. One view containing 37 fields with 1 explicit DB reference returns 37 errors. In the end the fix is easy, but figuring out the fix takes more time than it should based on the content and volume of the error messages.

BTW make sure that you’re using Data Dude GDR R2 version 9.1.40413.00. I found an MS support document that detailed a similar issue pertaining to using Server and Database aliases in referenced projects that is fixed in this release. This issue is similar but different in that it’s not a direct problem with aliases or referenced projects, just careless coding on my part.

Cloud-based Tabular Data Sources

noreply@blogger.com (Nick Barclay) — Thu, 15 Oct 2009 15:16:00 +0000

I realize there are many out there who are sick of the term “the cloud”. Larry Ellison's rant on this topic is great.

Nonetheless I got my Azure invitation yesterday and for no other reason other than it’s geeky, tried to access it from PPS. As I’d hoped, it was simple (as was connecting using SSMS). I set up a sample DB in my allotted condensed water vapor storage area and created a tabular data source using the appropriate connection string. Easy!

…and here’s the data in it

Hopefully cloud-based SSAS is not far away.

Windows 7 and PerformancePoint M&A Setup Gotcha

noreply@blogger.com (Nick Barclay) — Thu, 15 Oct 2009 14:32:00 +0000

For those who have, or are about to, upgrade to Windows 7 and reinstall a local instance of PPS for the purposes of demos / experimentation / learning etc. here are a couple of quick tips on getting things up and running.

Before installing the M&A Server be sure to read this post and ensure all your pre-reqs are in place.
Install the latest hotfix (I understand that PPS SP3 coming out in a couple of days)

UPDATE: SP3 is now available so skip step 2 above and install SP3 instead

x86: http://www.microsoft.com/downloads/details.aspx?FamilyID=90c596a5-aca4-4ded-9072-facf834bc0c6&displaylang=en

x64: http://www.microsoft.com/downloads/details.aspx?FamilyID=3ad75ae5-d2cd-4953-87cf-5f74d79804c6&displaylang=en

And now the Win7 twist. Once you have installed, configured the M&A server and fired up Dashboard Designer (DD) you receive the following message when trying to create a data source:

OK, so this is an application pool account problem? Nope. The problem is you’re not an administrator on this Monitoring Server yet. So you go into the administrative section in DD and add your account. When you to the options section and try to administer the server you get this error:

And herein lies the problem. You’re not a monitoring server administrator yet, but how can you make yourself one if you can’t get into the admin screen?

The solution:

You need explicitly run Dashboard Designer as an administrator (“but I AM an administrator!”). By default you’re not running this application as an admin. This is related to the UAC settings in Win7. Sure you can alter (turn off) the UAC settings but you may not want to, or be allowed to, in some cases.

How do you run DD as an admin? The Start menu item that is created for DD is only a link to the ClickOnce launch URL so you won’t find the appropriate “Run as administrator” option on the context menu if you SHIFT + Right-click on it. In order to run DD as an administrator find the DD executable (PSCBuilder.exe) in the file system. On a default install it will be located in %Program Files\Microsoft Office PerformancePoint Server\3.0\Monitoring\PPSMonitoring_1\DesignerInstall\3.0

Right-click the executable and “Run as administrator” from there.

In this administrator instance of Dashboard Designer go into the Server Options section and add yourself as an administrator.

You should be good to go from there.

Book Review: Expert Cube Development with Analysis Services 2008

noreply@blogger.com (Nick Barclay) — Mon, 12 Oct 2009 00:00:00 +0000

Full disclosure: the authors of this book provided me with a free review copy of this book.

As the title suggests Expert Cube Development with Microsoft SQL Server 2008 Analysis Services is not a book for SSAS beginners. This book fills a need that has been out there for a while when it comes to Analysis Services publications: a concentrated volume focused on enhancing the knowledge of the experienced SSAS pro. From the outset the authors assume the reader already has experience with the product, cover a few ground rules and get right down to business.

The amount of real world SSAS implementation experience shared between Alberto, Marco & Chris shines through indicating just how much work they’ve collectively done with Analysis Services. Many technical books have advanced sections or whole chapters dedicated to more advanced development techniques and tips. Being pitched as an expert book enables this level of content to pretty much fill the entire publication.

There is a lot of goodness jammed into the book’s 320-ish pages. You can sense that the authors tried hard to fit as many tips, tricks and techniques into each chapter as possible without bloating the text. They do not waste page space explaining the simple stuff because, if you’re reading this book, you should know the simple stuff. Each chapter remains concise and tells you what you need to know and where to go if you want to find out more by means of links to blog posts, white papers and other books as well as downloadable sample code.

My only criticism is a somewhat superficial one and is probably directed more at the book’s editor than its authors. There were no reference numbers and caption text underneath any of the screenshots, tables and figures at all. The non-textual items seemed naked without them and this made the end product seem a little less polished. As a reader I prefer it when the text points specifically to “Figure 1.2” instead of “the screenshot”. On some occasions the text didn’t even make direct reference to the item that appeared on the page with it, the relationship was implied by proximity.

If you have not worked much with SSAS yet then this is not a book you should be starting with. If, however, you’ve been working with the product and want to ensure you’re squeezing every last bit of performance out of your OLAP databases, this is a book you’ll want to read cover to cover. Even the most seasoned SSAS experts will come across material or techniques they did not know of or had forgotten about.

The Microsoft Virtual Catch 64

noreply@blogger.com (Nick Barclay) — Tue, 22 Sep 2009 15:46:00 +0000

How does it go again, Steve? “Developers, developers, developers”? As we move further and further into the 64 bit world, Microsoft’s virtualization path on non-server operating systems will leave some developers out in the cold.

MOSS 2010 is going to be a huge release and will only be available in 64 bit. Naturally PerformancePoint Services and all the other good stuff that’s going to be baked into this next version will be 64 bit too. The pure 64 bit direction is a good one, I am looking forward to the solid baseline scalability and power that will come from not offering the 32 bit option. All of this is good news.

Now here’s the catch for developers looking to get up to speed with the latest and greatest. Virtual PC 2007 and the soon-to-be-released Windows Virtual PC do not support 64 bit guest operating systems. The only Microsoft supported way to run a 64 bit virtual environment is through Hyper-V. The inability to support a virtual 64 bit OS on a Vista / XP / Win7 box means that the usually trivial task of spinning up a VPC and kicking the tires with the beta bits is just not possible. This a pain point that is already shared by many, just take a look at this newsgroup thread.

If you’re a consultant like me your laptop is your life. You do a great deal of your learning, development, testing, demos, conference presentations on a variety of VPC images. You store these images on an external HDD and run them within your primary, non-server OS. If you want to continue doing this kind of thing (on a 64 bit platform) you will need to seek out non-MS virtualization technologies to support your efforts.

So what are your options if you want to retain a pure Microsoft environment? The only MS answer to a 64 bit virtual environment is Hyper-V, and that means running Server 2008. If the cost of licensing Server 2008 is not an issue here are some of your options:

Run Server 2008 as your primary OS
Dual boot your laptop using Server 2008 as a secondary OS
Buy another laptop and load Server 2008 on it

None of these choices really do it for me. I have a good laptop with plenty of RAM and a 64 bit processor that supports virtualization technology. Nonetheless I am unable to run a MS-based 64 bit virtual guest OS without using Server 2008. So in order to continue to do things the way I am used to I have to go with a non-Microsoft virtualization technology that supports 64 bit guests. Here are the two frontrunners in the desktop 64 bit virtualized guest world that I have found.

VirtualBox (free)
VMWare Workstation (around US $189)

I recently reimaged my laptop with the RTM build of Windows 7 and have been using VirtualBox for a few days now. I’m used to the VPC way of doing things but I’ll just have to adapt, I guess. That being said, VirtualBox is a pretty good alternative.

As MOSS RTM draws nearer in H1 2010 I have a feeling we may begin to see virtual demo environments that are not hosted in MS technology. MS is pushing virtualization more and more (and so they should) but how’s it going to look if the person presenting at a user group / conference / customer site is using non-MS virtualization technology to host their demo? The next time I present that may well be the case. Disappointing.

UPDATE (Oct 21 2009): It has just been announced that MOSS 2010 will be supported on both Vista and Win7 for developers (not production deployments, of course). Although this doesn’t completely eliminate the problem that is the subject of this post it very definitely helps. The ability to run your own local developer version of MOSS should be great. I had heard about this possibility of this kind of support under NDA some time ago but it was never confirmed until now.

Anyone else got an opinion, alternative to the situation we will soon find ourselves in? Feel free to comment.

Excel 2010 gets sparklines, finally

noreply@blogger.com (Nick Barclay) — Wed, 15 Jul 2009 17:37:00 +0000

There are a number of different Office 2010 features and functions we’re getting wind of now that the covers have come off at the WPC in New Orleans.

One feature that is of particular interest is sparklines. I would think that anyone who subscribes to this blog will know what a sparkline is and how useful native Excel support for this visualization will be.

If you’re looking some more visual information about this new feature take a look at the following:

There’s a good video on TechNet. The commentary is in German but the demo is pretty self-explanatory. Make sure you view it in full screen as you’ll get a good close-up view of the various display and formatting options available in the new Sparkline Tools ribbon tab.
Scoble has a post containing six great Office 2010 videos including one that demos the creation of sparklines (it’s the last video out of the six). In that same video there’s a demo of the new pivot table slicer functionality. Many would already be aware of slicer feature via the various Gemini demo videos that have been available for the last few months.

UPDATE: Here is the official Sparkline post from the Excel 2010 team blog