Subscribe: Nick Barclay: BI-Lingual
Added By: Feedage Forager Feedage Grade A rated
Language: English
attributes  bit  city  created  data  entities  entity  master data  mds  members  model  new  product  select  server 
Rate this Feed
Rate this feedRate this feedRate this feedRate this feedRate this feed
Rate this feed 1 starRate this feed 2 starRate this feed 3 starRate this feed 4 starRate this feed 5 star

Comments (0)

Feed Details and Statistics Feed Statistics
Preview: Nick Barclay: BI-Lingual

Nick Barclay: BI-Lingual

Speaking the language of business intelligence with an Australian accent

Last Build Date: Tue, 09 Jan 2018 03:28:01 PST


Dashboard Requirement Gathering Satire

Fri, 30 Mar 2012 13:04:00 PDT

A colleague of mine put together a hilarious PeepzMovie that was inspired by some frustrating projects we're working on currently.

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

allowfullscreen="" frameborder="0" height="315" src="//" width="560">

Updated with new link to YouTube version.

Book Review: Microsoft SQL Server 2008 R2 Master Data Services

Thu, 08 Sep 2011 04:53:00 PDT

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

Tue, 29 Dec 2009 11:25:00 PST

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 Co[...]

Enough Pies, I’m Full!

Wed, 25 Nov 2009 08:53:00 PST

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? DECLARE   @PieCounter INT = 1,   @EnoughPies INT = 10 -- enough pies, I'm full! WHILE @PieCounter < @EnoughPies   BEGIN         PRINT 'The only thing better than ' + CONVERT(VARCHAR(2), @PieCounter)         + ' pie chart/s is ' + CONVERT(VARCHAR(2), @PieCounter + 1)         + ' pie charts!'   SET @PieCounter += 1   END  [...]

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

Tue, 24 Nov 2009 16:44:00 PST

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. -- get your model ID  SELECT *  FROM   mdm.tblModel  WHERE  [Name] = 'Geography' 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. SELECT *  FROM   mdm.tblEntity  WHERE  Model_ID = 15  -- change to your model number    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. -- city entity  SELECT *  FROM   mdm.tbl_15_53_EN   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. SELECT   a.*     FROM     mdm.tblAttribute a     WHERE    EXISTS (     SELECT *         FROM   mdm.tblEntity e         WHERE  e.ID = a.Entity_ID             AND e.model_ID = 15) -- change to your model number           AND MemberType_id = 1 -- leaf attribute           AND DataType_id = 1 -- user defined       ORDER BY Entity_ID Notice [...]

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

Tue, 24 Nov 2009 16:43:00 PST

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)

Tue, 24 Nov 2009 16:43:00 PST

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 SELECT *     FROM   mdm.tblStgMember 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 [...]

Beginning MDS - Creating Attributes (Part 4 of 7)

Tue, 24 Nov 2009 16:42:00 PST

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)

Tue, 24 Nov 2009 16:41:00 PST

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)

Tue, 24 Nov 2009 16:40:00 PST

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)

Tue, 24 Nov 2009 16:39:00 PST

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?

Mon, 16 Nov 2009 17:13:00 PST

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?

Fri, 13 Nov 2009 07:07:00 PST

‘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 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? Pro[...]

PerformancePoint Services - What’s Deprecated

Thu, 05 Nov 2009 04:47:00 PST

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

Thu, 05 Nov 2009 04:46:00 PST

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 towar[...]

PerformancePoint Services - What’s New

Thu, 05 Nov 2009 04:46:00 PST

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 New Changed / Improved / Different Deprecated 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 [...]

Un-cooking the books with Benford’s Law

Mon, 26 Oct 2009 10:44:00 PDT

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    &#[...]

Conditionally Hiding Axes for Trellis Displays

Wed, 21 Oct 2009 10:37:00 PDT

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. Trellis / Lattice displays Win / Loss Charts Bullet Graphs in SSRS 2008 Bullet Graphs in SSRS 2005 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 =II[...]

Windows Problem Steps Recorder

Fri, 16 Oct 2009 09:35:00 PDT

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

Fri, 16 Oct 2009 07:07:00 PDT

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

Thu, 15 Oct 2009 08:16:00 PDT

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

Thu, 15 Oct 2009 07:32:00 PDT

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: x64: 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

Sun, 11 Oct 2009 17:00:00 PDT

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

Tue, 22 Sep 2009 08:46:00 PDT

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 doe[...]

Excel 2010 gets sparklines, finally

Wed, 15 Jul 2009 10:37:00 PDT

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