Jeremy Kashel

Jeremy Kashel's Blog

Master Data Services Kindle Contest

This is a quick blog post to announce that Adatis are running a contest in conjunction with Packt around Master Data Services. The winner will receive an Amazon Kindle pre-loaded with our Microsoft SQL Server 2008 R2 Master Data Services book.

In order to enter, all you need to do is follow @AdatisBI and copy and paste our eBook on Kindle message and retweet it using your twitter account. The message is:

RT & Follow @AdatisBI for a chance to WIN the #Adatis MDS book pre-loaded on a Kindle ! (http://bit.ly/roogTd)

Please mark your retweet with the hashtag (#), so that we can find and consider your entry. Only one entry per person will be accepted.

You can find out the full details of how to enter, as well as terms and conditions, on the following page.

Good luck!

Master Data Services Excel Add-in

Master Data Services in SQL Server Denali now includes an Excel add-in that allows MDS users to add and update data that exists within Master Data Services. For those of you that haven’t had a chance to download the latest CTP, this post gives an overview of what’s possible in the Excel Add-in.

Excel Add-in Overview

Once you install the Excel add-in (available here for download) you will see an additional toolbar in the ribbon, as shown below:

image

At a high level, the following functionality is available within the MDS add-in:

  • Retrieve members from a master data entity
  • Add or Update members (including annotations) and publish the changes back to MDS
  • Create entities in MDS if you have sufficient permission
  • Run the business rules

I don’t want this post to go on for ever, so today I’m going to focus on retrieving and updating members.

Retrieving MDS Entity Members

The Excel Add-in comes with a sidebar called the Explorer that can be used to connect to an MDS model and retrieve data from an entity within the model. What I quite like is that there is a filter button that allows a user to specify how to filter the data before loading it. In the example below, I’ve connected to the sample customer model, and have filtered the customer entity to only show customers of type 1 and customers from Seattle:

image

On the same filtering window its possible to choose the attributes to display. Therefore in the above example, by clicking the ‘Load Data’ button, a filtered list of customer members will be shown:

image

Editing Members

Once the members have been retrieved, editing is just a simple case of typing into the field that you want to change. In the example below I’ve chosen to change the names of two of the customers, which has caused the cells to be highlighted, informing me of the changes that I’ve made:

image

Domain-based attributes are also picked up nicely by Excel. Not that I imagine it would make too much sense, but it’s possible to change the Sales District North Western US (WUSSL) to Central US (CEUS), for example. Excel handles this by rendering a drop down for this domain attribute:

image

As it is in Master Data Manager, within Excel it’s also possible to display a domain attribute’s name.

Regardless of the type of attribute that gets changed, the changes will just remain in the Excel sheet until I click the publish button:

image

Clicking the the publish button will result in being prompted to make an optional annotation for each of the changes:

image

The annotations, as well as the member updates, will be committed to the Master Data Services database. The end result is that the transactions can be viewed as per 2008 R2 in Master Data Manager, or by right clicking in Excel and choosing ‘View Transactions’.

Summary

The Excel add-in doesn’t contain all the functionality available within Master Data Manager, but provides an interface that clearly Excel-based data stewards will be very comfortable with. It’s also much easier to do bulk updates in the Excel front-end, when compared to the web front-end.

That’s about if for now. There’s more to the Excel add-in, which I’m aiming to cover at SQLBits 9, plus Master Data Manager has been given a complete overhaul in Denali – hopefully I’ll find the time to cover that soon…

Master Data Services Training

As I’m sure you’ve seen, registration for SQLBits 9 – Query Across the Mersey is now open.

This time around I’ll be running a deep-dive training day with Tim Kent on Master Data Services. This will be a full day of training, showing you how MDS can be used to manage the master data in your organisation. We’re going to start by giving an overview of Master Data Management, before moving on to covering the following MDS topics in detail:

  • Installing, configuring, and maintaining Master Data Services
  • Creating and using models
  • Version management
  • Business rules and workflow
  • Importing data into Master Data Services
  • Integrating with other systems
  • Security

You can register for the MDS Training day, or one of the other 10 training days, by using the following registration page.

Remember, the early bird discount expires at midnight on Friday this week!

Update – We are running another MDS course in February 2012. Click here for the details.

Master Data Services in SQL Server Denali

If you didn’t make it to Tech Ed in North America last week, then you can download session recordings and take part in the labs at the Tech Ed site here. The session recordings are also available for streaming or download on Channel 9.

Today I wanted to highlight in particular John McAllister’s session titled Managing Master Data with MDS and Microsoft Excel. We’ve heard for a while about the use of Excel with the new version of MDS, so it’s great to finally see it. The key points from the session for me are:

  • The new Excel add-in:
    • Allows the editing of Master Data (even lets you use Excel formulas) and allows you to publish back to MDS. It seems to provide the same functionality as the UI that we’re used to – it’s aware of the domain attributes, giving you drop downs in the relevant cells, and is aware of the business rules for an entity.
    • Also lets you actually build an MDS model from Excel.
  • Improvements to the current web-based UI (Master Data Manager). The design has changed in several places, for example it seems that the grid now has a side bar to display the member that you’ve clicked on. This “side bar” looks to have better validation capability than the current R2 member editing experience.
  • Integration with Data Quality Services. It’s a little unclear exactly what this will be, as it’s not shown in the video, so I think we’ll have to wait a while to find out.
  • The database now contains a staging table per entity, as opposed to a single shared staging table.
  • Improved Performance – this is in several areas of the product apparently, but it’s certainly visible in the video that the Master Data Manager UI is quicker.

I’ll be doing a series of blog posts on the new features once the new CTP/beta of Denali is released.

New Master Data Services Book

For the past seven months, Adatis have been working on new book for Master Data Services. The book, titled SQL Server 2008 R2 Master Data Services, will be published by Packt Publishing and will be released on 30th June.

For those of you who don’t know (I certainly didn’t), Packt operate something called the RAW program. This allows you to download an electronic copy of the book in draft format, as the chapters are made available. If you want, you can also feedback any changes that you’d like to see in the book, which means you get a chance to shape the book with the content that you want to include. What’s great is that when the book is finished, you get to download a copy of the e-book for free.

We’re almost done, just another month or so of late nights and early mornings before the June release date! Until then, the book is available on Packt’s site here, and is also available for pre-order on Amazon here.

image

Explicit Hierarchies Vs Recursive Hierarchies in MDS

Derived Hierarchies in Master Data Services are hierarchies created from the domain-based attribute relationships between entities, as explained in the following documentation, resulting in a fixed-depth hierarchy. A less obvious feature is that they can also be recursive, which by its very nature implies a ragged, variable-depth hierarchy. This is a characteristic that’s shared by Explicit Hierarchies, so it got me thinking that it would be worth a blog post to contrast Explicit Hierarchies and Recursive Hierarchies.

Explicit Hierarchies

Explicit Hierarchies use Consolidated Members to group other Consolidated and Leaf Members in order to make a hierarchy. The structure can be completely ragged, with each Consolidated Member having an unlimited number of Leaf or Consolidated Members beneath it.

An example of using Explicit Hierarchies is in the sample Product model that comes with MDS, which has an Explicit Hierarchy called Product Management. This groups members as shown in the (slightly edited) image below:

image

Note that the hierarchy is ragged, and that the Consolidated Members (PL, SL, CL, AC, ACC and CPT) can all have different attributes to the leaf members.

Recursive Hierarchies

Recursive Hierarchies allow an Entity Member to contain an attribute that points to another Member within the same Entity. Essentially this is a classical parent-child structure, which can of course also be ragged.

An example of using recursive hierarchies is in the Employee_Hierarchies sample model which can be downloaded from here. The image below shows one of the recursive hierarchies in the model, for employee managers:

image

Comparison

So, as we can see, both can be ragged and have a variable depth, so which one should we use? The following key points may help decide:

  • Explicit Hierarchies must use Consolidated Members for the grouping. So a Leaf Member can’t suddenly become a parent. E.g. in an employee manager type hierarchy, where it would be common for an employee to become a manager once they have the relevant experience, with Explicit Hierarchies it would involve converting the Leaf Member to a Consolidated Member, which would be a pain.
  • Its slightly easier to work with Recursive Hierarchies as you don’t need to worry about Consolidated Members. It can be a bit awkward to continually switch between Leaf and Consolidated Members in the explorer grid, so good to avoid it if possible.
  • It’s not possible to set individual member permissions on a Recursive Hierarchy, whereas it is with an Explicit Hierarchy.
  • Explicit Hierarchies can be added to Collections for more flexibility, whereas Recursive Hierarchies cannot.
  • You won’t be able to have multiple hierarchies by using a Recursive Hierarchy (unless you go down the root of having multiple self-pointing attributes), whereas you can create multiple Explicit Hierarchies quite easily.

Considering all the above points, in my opinion anyway, a Recursive Hierarchy is more suitable when you have a genuine parent child requirement, e.g. for an employee hierarchy, or a chart of accounts. This is just down to the ease of use. An Explicit Hierarchy is more suitable when you just want to create groupings for your leaf members, but have the requirement to potentially have multiple ragged reporting hierarchies.

Handling of NULLs and Blanks in MDS Business Rules

For any given entity in Master Data Services, its only the Code attribute that cannot be left blank when entering data in the front end or via the MDS staging tables. For any of the other attributes, you can then use the MDS business rules to enforce that the attribute must have a value, using the Is Required action, for example.

All this works well in my opinion, but I have found a difference in how blanks and NULLs are handled in the front end Vs in the MDS staging process, which has a knock on impact on the business rules.

Example Model

To explain I’ve created an example model called Supplier, with an entity called Supplier and a test attribute called Attribute1. There is also a simple business rule that will set the value of Attribute1 to the value ‘Name blank’ when the built in Name attribute is left blank:

image

Adding Data via Master Data Manager

My first step is to add a new member to the Supplier entity, which I will just give a code of 1 and intentionally leave the name blank:

image

Clicking the save button runs the single business rule that exists and correctly sets Attribute1 to the value of ‘Name blank’

image

If we determine the entity table for the Supplier entity (which can be done by looking at the [mdm].[viw_SYSTEM_SCHEMA_ENTITY] view) then in my case the entity table is called tbl_24_97_EN. Running a select statement on this table shows the following result:

image

The name has been entered as NULL, which is fine, as we’ve got the behaviour that we want.

Adding Data via the MDS Staging Tables

Now lets look what happens when we do the same operation via the MDS staging tables. First of all, we need to load a supplier member into the mdm.tblStgMember table, which we can carry out with the following SQL:

INSERT INTO mdm.tblStgMember 
( 
                    ModelName,
                    EntityName, 
                    MemberType_ID,
                    MemberName,
                    MemberCode
)
--Insert member with no name and a Code of 2
VALUES
                    ('Supplier',
                    'Supplier',
                    1,
                    NULL,
                    2)

The null has been left in the statement as we’re simulating, for example, not being given a name for the member from our data source. You could then have an MDS business rule set up to handle missing names, perhaps setting them to ‘Unknown Supplier’.

To load from the staging tables into the entity table, we run the staging sweep stored procedure:

DECLARE @ModelName nVarchar(50) = 'Supplier'
DECLARE @Version_ID int

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = @ModelName)
                   
--This will cause the members in the staging tables to be loaded into MDS
EXECUTE mdm.udpStagingSweep 1, @Version_ID, 1

To finish up we will also validate the model, which will cause the business rule to fire:

DECLARE @ModelName nVarchar(50) = 'Supplier'
DECLARE @Model_id int 
DECLARE @Version_ID int

SET @Version_ID = (SELECT MAX(ID)  
                   FROM mdm.viw_SYSTEM_SCHEMA_VERSION  
                   WHERE Model_Name = @ModelName)
                   
SET @Model_ID = (SELECT Model_ID 
                 FROM mdm.viw_SYSTEM_SCHEMA_VERSION 
                 WHERE Model_Name = @ModelName) 

--Cause validation to occur
EXECUTE mdm.udpValidateModel 1, @Model_ID, @Version_ID, 1

If we now do a select from the entity table, we will see that the member with the code of 2 doesn’t have a NULL for the name column, but instead has a blank string:

image

If we now go and take a look at the Master Data Manager front end, we will see that the business rule condition hasn’t been met for the the member that we added, as its ‘Attribute1’ attribute is still blank:

image

This has happened because the stored procedure that implements the business rules carries out the ‘Name is equal to Blank’ check that we’re relying on by checking if the name column is NULL.

How this affects you will obviously depend on how you’re using the MDS business rules. It doesn’t affect the other member attributes, only the Name attribute. If you want to handle null Names on entities then a good work around is to use the business rules in the front end, and then to use SSIS to replace null names with ‘Unknown’ or something similar.

Master Data Services – Business Rules and the Notification Interval

For your Master Data services business rules that have a validation action, a notification, if you’ve set one up, will be sent if the rule condition gets satisfied.

I’ve noticed a quirk to the way that the notifications are sent – I was getting unexpected email notifications when creating members – which as it happens is not a bug, but is worth exploring to understand how the MDS rules work.

The above was essentially due to the two step save process that occurs in the MDS front end. When adding a member, you first of all enter the name and the code, then you click the save button. At this point, the business rules are run, and validation issues are created in a table called [mdm].[tblValidationLog], and then displayed to the user at the bottom of the screen, before the user has entered the attributes. This is shown below on the sample Product model, which I have changed to enable notifications on a few of the rules:

image

Then click the save button, which will display the attributes pane below. As the sample Product model contains a rule called “Required Fields”, with a condition of “None”, then the validation issues will also be displayed:

image

At this point we’ve got no way to avoid getting the validation issues, as the rule has no condition, and we’ve not had any opportunity yet to alter the attributes that the rule checks. The email notifications behaviour to be aware of at this point is:

  • The notifications are sent on an interval, defined by the system setting called “Notification e-mail interval”. Its default is 120 seconds.
  • If you rectify the issues that you’ve been warned about in the gap between the last email notification cycle, and the next one, then [mdm].[tblValidationLog] will be updated to say that the validation issue doesn’t need a notification, and you will get no emails.
  • On the other hand, if you’re in the middle of a few things and essentially don’t act quick enough, then you will get a notification with the above issues displayed, as the email interval has kicked in during the middle of your save.

Hence I was sometimes getting email notifications as shown below:

image

But sometimes I was getting no notification for carrying out what I perceived to be exactly the same behaviour. So by no means a bug, but worth explaining to your users how the notifications work.

Master Data Services Subscription Views

Subscription Views are one of the ways to extract data from Master Data Services. Created via the ‘Data Integration’ section of the MDS Master Data Manager front end, they result in the creation of a physical SQL Server view in the underlying database.

All this you will probably know from the MSDN article. The purpose of this post, however, is to explain a few of the more subtle differences between the different types of subscription views that get created, and looking how they can be used to feed a data warehouse.

Derived Hierarchies

You can create a subscription view from a derived hierarchy, which will return all the levels from your hierarchy in a de-normalised form. If you choose the sample Product model, then you will see that one of the Derived Hierarchies that you can pick from is called Category. As your Product dimension in a data warehouse would include attributes such as Product Category and Sub Category, then on the surface this view looks to be ideal.

Unfortunately there is a slight issue with subscription views based on derived hierarchies. Users could have altered one of the members that exist within the hierarchy before the data warehouse nightly load has taken place. If the MDS validation of that member hasn’t occurred yet (which it doesn’t in some situations), then the member could potentially contain some incorrect attribute values that we don’t want to expose to the data warehouse.

For example, a user has edited a product, as shown below, but chosen a different Sub Category to that of its adjacent members:

image

As indicated by the yellow question mark above, the member is awaiting validation. However, when I run a select statement against my view that’s based on Derived Hierarchy Subscription view, then the member that is marked above will still appear in the view results. We would not want to load this change into the data warehouse, as until the business rules have been run, we dont know if the new Sub Category is valid in the context of the other attributes. For reference, the same behaviour exists for members that have explicitly failed validation.

Leaf Attributes

You can also create a subscription view by picking an entity, and then choosing the format of Leaf Attributes, as shown below:

image

In my case, I’ve set up a subscription view called ProductLeafAtributes, and when I run a select statement against this view in SQL Server, then I get the following results:

image

As with the subscription view that’s based on a Derived Hierarchy format, I still get all the members returned, but there’s also a column called Validation Status, that shows that one of the members is awaiting revalidation. Therefore, this column can be filtered on in the data warehouse load, just to take through the changes that have definitely passed validation.

Conclusion

Creating the subscription views based on entities, rather than Derived Hierarchies, exposes the validation status of a member, and is therefore the preferred subscription view format to use in this case. The one catch is that the Leaf Attributes format will not return attributes of any related entities, e.g. Product Category. It will return the Sub Category code, so therefore its necessary to join to separate Subscription Views for both Sub Category and Category in order to pick up the missing attributes.

Master Data Services Learning Resources

Back to a bit of Master Data Services this week – there’s been a few learning resources that have appeared here and there for MDS over the past couple of months. I thought that it would be worth rounding up the ones that I’ve noticed:

  • Installing and Configuring Master Data Services – Starting right at the beginning, this video is useful if you haven’t installed the product yet. It takes everything step by step, showing you how to do the basic install then configure the database and website.
  • Implementing MDM Using Master Data Services – This is one of the recent videos from 24 Hours of SQL Pass, which are now available online.
  • Microsoft E-Learning Master Data Services – One of the free clinics available on the MS E-Learning site. The content delivery is a good mix between articles, video tutorials and self tests, so really it’s a good couple of hours of decent content, covering everything from business rules to subscription views.
  • SQL Server 2008 R2 Labs – There’s hands on labs for all parts of R2 here, and the MDS one is definitely worth taking a look at.
  • Analysis Services Dimension Management with Master Data Services – A video from the recent Tech Ed conference in New Orleans. Another one that’s really worth watching as it covers how MDS can be utilised in conjunction with Analysis Services.