Jeremy Kashel's Blog

Debug Custom MDS Workflows

2013-05-20T14:19:04Z

The custom MDS workflows, AKA External Action Business Rules or custom MDS assemblies, are one of the more advanced features of MDS. They allow you to extend the standard MDS business rules by getting MDS to fire Entity Member member data at an external assembly of your choice.

There are a lot of moving parts to this feature, MDS business rules, the custom .Net assembly, service broker and a windows service! When a business rule meets your condition, then the rule is written to a service broker queue. The windows service interrogates the queue and then passes the information to the custom assembly. I’m not going to show how to do this, this is already done by Johan Machielse and also on MSDN. What I would like to show is how to debug the use of the custom business rules, just due to the number of moving parts and also a couple of quirks that I’ve encountered when using them.

Custom Assembly Deployment

One of the first things that may catch you out is the deployment of the assembly. To troubleshoot this, I recommend the “-console” argument of the workflow service. If you run it in command line mode you may encounter a few errors:

“Could not load file or assembly 'YourAssembly' or one of its dependencies. This assembly is built by a runtime newer than
the currently loaded runtime and cannot be loaded.”

This means that you need to go the properties of your assembly in Visual Studio and change the Target Framework from .NET Framework 4 to 3.5.

Another error that you may get is "ERROR: Could not create type. Ignored.” Assuming that you have actually put the assembly in the bin directory, this is most likely to be that the either the namespace or the class name in your DLL doesn’t match what you have in your config file. Check the contents of Microsoft.MasterDataServices.Workflow.exe.config match your DLL.

You may also get, “ERROR: Type does not implement IWorkflowTypeExtender.” This means that your assembly has been picked up correctly, but it does not implement IWorkflowTypeExtender.

If the assembly does start up ok without errors, you may still get errors when the exe tries to take a message off the service broker queue. An error such as “Could not dispatch due to missing extender <tag>” means that the tag that you’ve given to the assembly in the config file does not match the “Workflow Type” value of the MDS business rule:

<setting name="WorkflowTypeExtenders" serializeAs="String">
<value>MDSTEST=MDS.CustomWorkflow, CustomWorkflow</value>
</setting>

Debugging the Custom Assembly

Once you do get the assembly working, you may find that the code isn’t quite doing what you want. A good way to troubleshoot this is to choose “Attach To Process” from the Debug menu in Visual Studio, as shown below:

If you do this, then you can add breakpoints to your .Net code as normal and step through in order to troubleshoot issues.

To conclude, this probably isn’t an exhaustive list of all the sort of errors that could occur with custom workflows, but with any luck it will save someone a few hours of troubleshooting.

Data Quality Services - Kimball ETL Screening

2013-02-28T10:39:53Z

Although it’s arguably not the most popular in the Kimball series, The Data Warehouse ETL Toolkit contains a huge amount of vital information that’s focussed on the ETL side of data warehousing. In particular I’ve found the data quality screens design to be useful, so I thought it might be worth exploring if this concept can be applied to Data Quality Services.

What is a Kimball Data Quality Screen?

A screen is simply a check that is made against data that comes into the ETL pipeline and is used to measure data quality. Physically, the Kimball screen design is typically implemented as a table called Screen, which contains, as rows, the actual checks to perform against staged data. In addition, the screen table will contain metadata to aid data quality measurement, such as screen severity, category, source system and possibly exception actions.

Each time that a screen yields some results (i.e. data quality issues) then another table, Error Event Fact, is populated with the results. Therefore, by using the data generated by the screening process, The Error Event Fact becomes the place to look for an overall measurement of data quality. The schema for this design from the Kimball book (with a few modifications) is as follows:

The check performed by each screen is typically a snippet of SQL that checks for a particular condition, e.g. Is the customer’s postcode missing, or is the tax amount within permitted ranges?

Can Data Quality Services assist with ETL screening?

DQS is capable of auditing and checking data, as each domain within a knowledge base can have its own domain rules. In order to see how this might work as Kimball screens, I’ve set up a simple knowledge base around purchase orders. Here’s the domain management window from the DQS client:

Within the Delivery Charge domain, I’ve setup a domain rule called ‘Delivery Charge Negative’ in order to ensure that the delivery charge cannot be negative:

I’ve done something similar with the PO Date domain, setting up a rule to state that the PO date must be greater than 01/01/2012 in this case. Data run against the PO knowledge base will now be enforced against these two rules, so these are effectively my two Kimball style screens. To recap, DQS cleaning can be run either from the Data Quality Client or from SSIS.

Populating the Error Event Fact with SSIS

I’m going to be running my DQS domain rules via SSIS, as my end goal is to populate the Error Event Fact. Remember, if any DQ issues occur, I want the Error Event Fact to know about them.

In this scenario, the data from the source system has already been staged to a SQL table, which gives the following results in the SSIS source adaptor preview:

This data is passed to the DQS Cleansing SSIS task, with the data mapped to the DQS Knowledge Base in the following way:

Once the package is run, the data will be passed to DQS, which will run the data against its knowledge base, including running the domain rules. The data that is returned from DQS is as shown below. Note, PO55 row has failed both rules, whereas the other two rows have failed one row each.

Now we know exactly which rows have failed each data quality screen. The only trouble is that the data is in the wrong format for our error event fact. We want a total of 4 rows in the error event fact has PO Id 5 has failed 2 screens, whereas the other two rows have failed 1 screen. Therefore, we carry out a unpivot before inserting into the Error Event Fact. For this blog post my ScreenId is hard coded, but in reality you would have to lookup an appropriate ID for the DQS screen. The full SSIS package is as follows:

Data Quality Reporting

With the Error Event Fact populated, a variety of reports can be produced. In this case I’ve made a very simple Analysis Services cube and made a quick report in Excel 2013, using slicers, sparklines and also the new Timeline feature:

From this report I can see the screen that’s failed the most rows, see a trend of data quality and filter on specific issues.

Summary

With a bit of work, DQS is capable of producing the rows needed for the Kimball Error Event Fact, but how good is it? I see the advantages of using DQS as follows:

Front end user interface in order to allow data stewards to set up their own rules to screen the data – much better than holding rules in SSIS or manually in a table;
DQS becomes a one stop shop for all data quality, as, in addition to holding the screens as domain rules, DQS will clean your data for you, according to the valid/invalid domain values that you set up in the knowledge base.

There are a few disadvantages to it also:

The domain rules are not as flexible as pure SQL or SSIS, meaning that you may not be able to express all the screening rules that you had in mind;
DQS can't maintain all the metadata that you need to implement Kimball screening, so you’ll need to supplement it with some SQL tables to hold things like screen severity, screen type etc;
Even though the performance has been improved in CUI, it’s still not suitable for large amounts of data. Here’s an overview of how long you can expect the SSIS cleansing to take.

So my view is that if you’re already using DQS and data stewards like it using it to manage data quality, and your data volumes are not too big, then implement the classic Kimball screening design using DQS. But there are more cons than pros unfortunately, so if you’ve not already made an investment in DQS, and you have the time to invest in your own Kimball style screening solution, then I think you can come up with something that’s a bit more flexible and scalable.

Unique Values in Master Data Services

2012-12-07T13:29:27Z

I’ve been doing quite a few demos and Proof of Concept (POC) projects recently for MDS. Understandably there are always a raft of questions that come up during demos and POCs, one of which recently was “how do I ensure that this attribute’s value is unique?”.

MDS can easily deal with simple uniqueness enforcement, which is something that anyone who has done any MDS development will no doubt know straight away. But if the requirement is more complex, such as ensuring that the attribute is unique in conjunction with other attributes, then I don’t think it’s as well known. Therefore I thought it would be worth a quick post on how to do this.

Code Attribute

To get the simple case out of the way, each MDS entity is always created with an attribute called Code, which must be unique. Therefore if you try and create/update a member with a duplicate Code, then you get the following error message:

Business Rules

To enforce uniqueness on any other attribute, you have to use business rules. Therefore, if I want to ensure that the attribute called Name must be unique, then I must carry out the following:

Go to the business rules editor and create a new rule
Drag the action of “must be unique” over to the actions node
Drag the Name attribute over to the “Select attribute” node, meaning that the action should look as follows:
Once that’s done, save the rule step and then re-publish the rule.

Trying to enter a duplicate value for the Name attribute will now give the following validation error:

Business Rules – Multiple Attributes

Now for the harder scenario. In the sample MDS product model, there is a Country attribute and also a Product Sub Category. I want to ensure that Name is unique, but only within a Country and Sub Category combination. To do this we go to the same business rules interface, and add the same action as before. Once that is done we carry out the following additional steps:

Change the radio button to “In combination with the following”
This is the part that I don’t think is that obvious. Drag over the Country attribute and then the Product Sub Category attribute onto the “attributes” node. This should give the following:
As before, save the rule step, then publish the rule.

Now a duplicate name will be permitted, but only within a country and sub category combination. If we do enter a duplicate value within a given Country/Product Sub Category group, then the following validation message is shown:

Master Data Services SQL Server 2012 Vs 2008 R2

2012-11-09T15:03:14Z

I’ve given quite a few MDS demos recently and one question I seem to get asked often is “What features does MDS 2012 have compared to MDS 2008R2?”. As its happened quite a few times, I thought it would be a good idea to put a quick post together to explain the differences. The table below details all the features MDS has and compares the two versions:

Feature	MDS 2012	MDS 2008 R2
Front End	· New Silverlight UI added for the Explorer area. Better performance, with less clicks to get tasks done. · Excel Add-in introduced	· HTML based ASP.NET implementation. · No Excel Add-in
Modelling	· Carry out in the web UI, plus some tasks can be carried out in Excel · Allows the auto generation of a code without using business rules	· Carry out modelling in the web UI only
Hierarchies	· Three types of hierarchies · Manage hierarchies in the new Silverlight UI	· Three types of hierarchies · Manage hierarchies in the older UI style
Collections	· Add members from Explicit Hierarchies or other Collections · Maintain a weighting on a member by member basis	· Add members from Explicit Hierarchies or other Collections
Staging Process	· A staging table is created per entity that you create in MDS. The table mirrors the exact structure that you see when you view the entity in the front end, making loading easier.	· A generic staging table is used for all entities. This means, for example, you must load attribute changes in attribute name/value pairs
Deployment	· Deploy the model structure from the front end. · Deploy the data from the new command line deployment tool	· Deploy model structure and data from the front end
Security	· Grant permissions to models, entities, attributes and attribute groups · Security cleaned up and simplified (no longer necessary to grant permissions to hierarchies)	· Grant permissions to models, entities, attributes, attribute groups and hierarchies
Business Rules	· Alter attribute values, validate data integrity, initiate workflow. · Some minor validation changes around using business rules to auto-generate Code values	· Alter attribute values, validate data integrity, initiate workflow.
SharePoint Integration	· Initiate a SharePoint workflow from MDS business rules · New functionality to embed the web UI inside a SharePoint page	· Initiate a SharePoint workflow from MDS business rules
Data Quality Services (DQS) Integration	· Connect to a DQS matching policy from Excel, against MDS master data	· No DQS capability, as DQS is new in SQL Server 2012
Version Management	· Create multiple copies of your master data and work with them independently as needed · Some minor UI improvements have been added	· As SQL 2012, create multiple copies of your master data and work with them independently as needed
Transactions (auditing)	· Transactions removed from Explorer and now are only available in Version Management · Therefore users cannot reverse their own transactions, only administrators can do this · Annotations are now permanent and cannot be deleted	· Transactions viewable in two areas, Explorer and Version Management · Users can reverse their own transactions · Annotations can be deleted
Metadata	· No support to view Metadata – this is now a deprecated feature	· Customise the metadata for most objects in MDS · View metadata in the metadata explorer
Installation & Configuration	· Installation from the main SQL Server setup program · Minor changes to the configuration settings	· Installation from the SQL Server media, but not included in the main setup program

For me the improvements to the performance and the addition of the Excel Add-in are the most compelling reasons to upgrade. The great thing in particular about the Excel Add-in is that data stewards can manage data with the familiarity of Excel, but the data itself will be stored in SQL Server 2012.

Matching with Master Data Services, DQS and SSIS

2012-10-11T16:34:37Z

If you have an MDM requirement to consolidate multiple sources of Master Data together into a single golden record, then you have a few different ways to achieve this on the Microsoft platform. This blog post gives an overview of the different ways that various matching methods can be used in conjunction with Master Data Services for consolidation in Master Data Management, outlining the pros and cons of each option. In summary, the options are:

Data Quality Services (DQS)
SQL Server Integration Services (SSIS)
Plus Master Data Services itself has a few (not that well known) matching capabilities

Data Quality Services

A key requirement in all but the simplest MDM solutions is that the matching/consolidation must be carried out in an automated manner, with a Data Steward alerted to take action if needed (e.g. the matching engine incorrectly matches two customers, the Data Steward opens MDS and corrects this). This scenario would be hard to achieve with DQS, as it’s possible to automate the DQS cleaning, but it’s not possible to automate the DQS matching. This is something that I’ve raised connect issues about, here and here.

If your data to be matched into MDS is not coming in on a daily basis, and you therefore need to do more ad-hoc matching to produce your golden Master Data records, then DQS could be for you. The MDS Excel Add-in will give you the capability of matching data that you import into Excel with MDS members, harnessing the power of DQS. An overview of how this works is available here.

Integration Services

SSIS has been around for a long time now and, as many of you will know, contains fuzzy matching components. With the right MDS model design, its possible to carry out a batch based fuzzy match between your master records and end up with a mapping between your the records that exist in your source systems and your MDS golden records. The rough conceptual steps to do this are:

Load the new and changed records from the source systems into a staging area.
Clean and standardise your data. This is actually something that DQS cleaning can help with.
Query your staging area to get the new records that you want to insert/update into Master Data Services.
Now the question arises, do we have an exact or close match for these records already in MDS? While the exact matches are easy to deal with, use the SSIS Fuzzy Lookup component to establish whether there are any approximate matches.
Link the source records to master records (if the match is high enough) using MDS Domain Attributes.
Carry out appropriate inserts and updates into MDS using the MDS staging tables.
Ensure that a Data Steward is alerted in some way if necessary (e.g. if the match threshold is below x% confidence). This can be done with Email or MDS Notifications, for example.

This process can run in batch overnight, with the Data Steward approving or rejecting the matches that SSIS has carried out the following morning. Whilst the above over-simplifies the process and technical work required, hopefully the process makes sense at a high level.

Master Data Services

Although you cannot feed MDS your source data and get it to automatically carry out matching for you, it does actually contain the raw components in order to do this. By this I mean the MDS database contains an assembly called Microsoft.MasterDataServices.DataQuality, which gives you a number of fuzzy matching T-SQL functions. These are called from the MDS front end when you carry out some filtering when viewing entities. Using them just for filtering in the front end really isn’t using the functions to their true capability, but thankfully you can use these functions in your own code.

You can use the MDS T-SQL functions in a similar way to the conceptual SSIS method outlined above, in order to match and eliminate duplicates. In addition, the MDS web API can also be used to carry out a fuzzy match, as mentioned in this forum post. Retrieving match candidates using a web service may be an attractive option if you’re trying to do real time MDM.

Conclusion

Essentially until it’s possible to automate DQS matching, we have a choice between SSIS and the MDS matching functions. The following e-book gives a very detailed overview of the matches that both are capable of doing. The MDS T-SQL functions are more flexible than the SSIS fuzzy components as you can choose what fuzzy algorithm you want to use, but the SSIS components let you choose between Fuzzy Grouping and Fuzzy Lookup out of the box, without having to write SQL. Although I tend find that both give very good matching results, the MDS T-SQL functions produce slightly better matches in my experience, plus give you the option of trying different algorithms to suit different scenarios.

It’s also worth mentioning that Profisee Maestro (full disclosure, we are a partner) integrates with MDS, offering its own matching algorithms. Maestro also has a front end with the ability to assist with survivorship and approval, which I think is a useful addition to MDS. Speaking of survivorship and approval, there are two options in MDS out-of-the box. The new Master Data Manager web front-end is much improved, but potentially the MDS Excel Add-In allows a bit more flexibility for survivorship carried out by a Data Steward, just due to its natural ability for filling/copying/pasting/filtering.

So overall, due to the various improvements, Master Data Services is now capable of tackling more complex MDM scenarios than in the 2008 R2 version.

Analysis Services Member Properties and Excel

2012-09-18T16:40:15Z

When working with users who are browsing a cube using Excel 2010, a common requirement that I find is that the users want to create tabular or grid based reports. E.g. “I have a list of currencies, I want to display the currency code in an adjacent column”. Or “Here are my account codes, I want to also see the account type”.

The common way that I see users attempt this is to stack attribute hierarchies on top of one another. For example, using Adventure Works, to get the currency names and codes together, I can stack the two attribute hierarchies:

This produces the following report:

Not exactly what we want – ideally we want the currency codes in separate columns, on the same line as the name.

An Alternative Approach

One well known way to achieve this layout is to right click on the relevant member and display its member properties, whereas another is to set the layout to Tabular Form. I tend to prefer the member properties route as I think it produces slightly better reports, so I’ll be covering a simple member properties design tip shortly. First of all, here’s the sort of report that I’m able to create using member properties:

I’m able to do this because the Adventure Works Source Currency Code Attribute Hierarchy has the Source Currency (names) as a member property. Therefore, I get the following option when I right click on the currency code:

Displaying member properties will only work if the attribute relationships are set up correctly. The reason that I’m able to see the member property ‘Source Currency’ is because of the following attribute relationship:

However, if we just display the currency name, using the Source Currency attribute hierarchy and then try and right click to see the currency code, we’ll unfortunately see nothing in the ‘Show Properties in Report’ submenu. This is because the currency name is a property of the code, rather than the other way around. I’d argue that for a user it’s intuitive to get the name from the code, or vice versa, as they sit at the same level. With a few simple changes we can achieve this, essentially by adding an extra attribute to the dimension…

Within the dimension editor in BIDS, the first step is to add the new attribute. In this case it should be based on the Currency Code column that is, of course, already in use. That doesn’t matter, we want to use it twice. Therefore, I’ve created a new attribute called Currency Code and have set AttributeHierarchyEnabled = False, as there’s no need for anyone to browse this attribute. Now onto the all important attribute relationships. The attribute hierarchy that contains the names is called Source Currency. As it’s not possible to get the currency codes by right clicking on this attribute hierarchy, Source Currency is the attribute needs to be related to the new Currency Code attribute:

Finally, the end result for the user is that they now get the ability to easily get access to the currency codes by right clicking on the names:

Power View in SQL Server 2012 RTM

2012-03-28T13:13:01Z

Power View has been given a couple of small changes from RC0 to the RTM release:

Firstly, you can now choose the colour of the various visualisations via the Themes section, which you’ll find on the new Styles ribbon tab:

This will allow you to change the colour for a slicer, table or card, whereas for a chart it will set the palette that gets used for the chart series. Surprisingly it seems to set the theme globally, rather than allowing you to set the colour/palette for a single object:

The other change that I’ve noticed is that the Card visualisation now has a Style option on the ribbon:

This allows the cards to be displayed without normal coloured background, with a much larger font. So potentially useful for highlighting key numeric facts:

I do really like Power View, in particular the clean look & feel, fast performance, export to PowerPoint and animations to name a few. However, even though RC0 added a lot of new features, I think it’s still missing some of the functionality offered by other reporting tools. I know that it’s marketed as a data visualisation tool rather than a reporting tool, but those lines will become a bit blurred for users. This is where Connect comes in…

One connect suggestion that I’ve created is to allow drill down in Power View charts. If you’ve used Power View to perhaps spot a trend or spike in the data, then it makes sense for you to manipulate the view to find the root cause, which I think drill down would help with. Other suggestions that I think would be useful are heat maps and allowing Power View to access multidimensional models. Please vote on Connect if you agree!

DQS Matching Vs SSIS Fuzzy Grouping and Lookup

2012-03-23T11:06:59Z

When the term fuzzy matching comes up in the Microsoft world, it’s natural for anyone who’s used the BI stack to think of SQL Server Integration Services (SSIS), due to the fact that it has both Fuzzy Grouping and Fuzzy Matching components. Therefore, when using the matching in Data Quality Services (DQS) the other day, I thought it might be worth contrasting the matching capabilities in the two products.

Overview

The SSIS Fuzzy Grouping transformation is typically used on a set of data containing duplicates, as it will operate across all of the rows, grouping similar rows together based on fuzzy comparisons, with a view to eliminating duplicates. In contrast, the Fuzzy Lookup transformation takes a value in the SSIS pipeline and uses fuzzy matching to match the input value against a set of clean reference data in a database.

The matching in DQS takes a set of data and groups the data into clusters, based on a number of rules and other factors, again with a view to removing duplicates. It’s therefore only directly comparable against the SSIS Fuzzy Grouping, rather than the Fuzzy Lookup.

Test Scenario

For the purposes of this blog post, I’ve got a sample query from AdventureWorksDW, taking data from the DimCustomer table. I’ve unioned all that data with a few sample records that contain typos. E.g. I’ve got 'Pamela Fernndez' misspelt for one customer and '4610 Pinto Rd' instead of ‘Road’ for another. I’m going to attempt to de-duplicate data in SSIS first and then do the same thing in DQS to see if there is any difference.

SSIS Fuzzy Grouping

As shown below, I’ve just got a very simple OLE DB source passing some data into a Fuzzy Grouping, then I’m going to examine the data via a data viewer:

The columns that I’m passing into the Fuzzy Grouping for the Customers are Full Name, Address Line 1, Gender and City just for the purposes of this blog post. I’ve configured the Fuzzy Grouping transform to carry out Fuzzy Matching on Address Line 1 and Full Name, as these are the most inconsistent (intentionally) across my made up data. I’ve also dropped the threshold right down to 0.40 to see a full range of matches that we may or may not get:

I’ve filtered the data for customers with a surname of Fernandez or a first name of Pamela, which includes two made up records. When I run the package, the data viewer produces the following results:

The _score column has a high score of 0.8245935, which is based on the similarity scores of FullName and AddressLine1. There are 3 records included in the cluster, including someone called ‘P Fernandez’ who is male. Although that’s been matched, it’s only because I decreased the threshold, plus it can easily be excluded by adding an exact match for Gender, as shown below:

DQS Matching

So overall SSIS copes well with the test scenario, how does DQS compare?

After creating a knowledge base, creating domains and carrying out knowledge discovery, it’s possible to use DQS to create a Matching Policy. The Matching Policy involves building a number of rules that determine how the data matches. The starting point in creating a matching rule is determining which domains you want to match on and whether they should be matched using the fuzzy algorithms (similar) or matched exactly:

So not too dissimilar to SSIS. You then choose to give each domain a percentage weight, which must add up to 100%. For example, if you give Full Name a weight 30% and, when run, its match score is 61% accuracy, then you have Address Line 1 at a 70% weight and a 45% accuracy, then an overall match score for a row will be (0.3 * 0.61) + (0.7 * 0.45) = 49.8% match score.

Starting off with a 50% weight on the two domains, I get the following results when operating on the same data:

As with SSIS, it hasn’t got it quite right, but using the Gender domain will help. What I quite like in DQS is that I can flag a domain as being a pre-requisite:

Flagging a domain as a pre-requisite means that the domain will be used to eliminate matches, but will not contribute to the matching score. If I wanted it to contribute to the matching score I would just have to uncheck pre-requisite and give it a weight. Running the matching again gives the following results:

So the duplicate is correctly mapped with a score of 75%. Drilling down on the match was achieved is quite nice in DQS also:

This brings me to an interesting observation about the matching. I originally had my dummy record with a value of “3 Pierr Demoulainn” for the address. So in addition to having a typo in the second name and missing the word “Rue”, my other test also missed the second e out of the word “Pierre”. If I run my test data with these values, I get no matches at all. The lack of the second e in “Pierr” seems to have made all the difference. I can get it to match if I give a lot of weighting to Full Name. If I do this, we see the following results:

Address Line 1 has been given a score of 0%. Making the same change to my test data and then running in SSIS gives different results. The similarity for the address drops from 0.81 to 0.75, but it certainly doesn’t drop to zero. Although it will depend massively on your own data, the matching in DQS seems a bit more stringent. This can be mitigated by using more domains for the matching, rather than the simple 2 or 3 domains that I’ve used. To back that statement up, using a composite domain on both Address Line 1 and City does yield a strong match (71%) for the same data.

Conclusion

The rule based matching in DQS gives a lot of flexibility on how to weight matching across different domains. Coupled with the knowledge base components that interact with the matching, such as cleaning and Term-Based Relations, DQS has more features specifically aimed at matching than SSIS. It all depends on what sort of data you’re working with, but in theory, as you maintain the knowledge base over time, DQS should give you strong matches based on the knowledge you’ve given it.

However, there are some drawbacks – it’s a shame that the matching can’t be automated (please vote for my connect suggestion if you agree). SSIS by its very nature can be automated, meaning the matching in SSIS will be more suitable for a lot of implementations. Secondly, I’d like to be able to match external records against correct values already in the knowledge base, rather than just doing a fuzzy grouping on the external records. I’m a bit surprised DQS can’t do a fuzzy lookup, maybe this will change in V2. Finally, as I’ve shown above, some of the matching results are a little unexpected, whereas in SSIS they were good pretty much straight away. To be fair to DQS this could probably be mitigated with a better choice of matching fields and also running cleaning against a fully populated knowledge base beforehand.

Mean, Median and Mode in MDX

2012-03-19T11:25:28Z

I recently delivered an Analysis Services training course to a client who were a bit surprised to learn that the standard list of Aggregation Functions for an Analysis Services measure doesn’t include Mean, Median and Mode. My answer was of course that all three can delivered using MDX, so I thought it might be a good blog post to show how it’s done.

Before I begin, just a quick refresher for anyone who needs it, the following article seems to be a good reference on the difference between Mean, Median and Mode. Essentially Mean is the average, Median is the middle value, whereas Mode is the value that occurs the most.

Mean and Median

The Mean and the Median are both relatively easy to do in MDX as there are built-in functions for both AVG() and Median(). As they’re well documented here and here, I won’t spend too long covering them. In the following MDX query I’m producing the Mean and the Median of the product sub categories in Adventure Works:

WITH 
MEMBER  [Measures].[Mean] AS AVG([Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount])

MEMBER  [Measures].[Median] AS Median([Product].[Subcategory].[Subcategory].Members,
        [Measures].[Internet Sales Amount])

SELECT  {[Measures].[Internet Sales Amount], [Measures].[Mean], [Measures].[Median]} ON 0,
        NonEmpty([Product].[Subcategory].[Subcategory].Members, [Measures].[Internet Sales Amount]) ON 1
FROM    [Adventure Works]
WHERE   ([Date].[Date].&[20070727])

If you run the query and copy the data out to Excel you’ll see that it matches the result of Excel’s Median and Mode functions.

Mode

Mode is harder to achieve as there’s no built in MDX function. Fortunately, Excel is on hand to help out, as Analysis Services allows you to use some of the Excel functions in MDX. There are warnings that come with this approach, as you need to have Excel installed on the server and there can also be performance problems. But, if you’re comfortable with these caveats, then the Excel mode function may be an option for you.

If you do use the Excel Mode function then there are a few things to watch out for. Firstly, you’ll have to use the MDX SetToArray() function to pass the set in the format that Excel expects. Secondly, you may get an error returned with the description: “#Error The following system error occurred: Invalid flags.” This is due to the data type of the Measure – it seems that Currency measures are not supported by this function. Therefore, I’m using Cdbl() on Internet Sales Amount to get this to work:

WITH 
MEMBER  [Measures].[Demo] AS Cdbl([Measures].[Internet Sales Amount])
    
MEMBER  [Measures].[ExcelMode] AS
        Excel!MODE(SetToArray(NONEMPTY([Product].[Subcategory].[Subcategory].Members, [Measures].[Demo])
        * [Measures].[Demo]))

Applying this to the example query I’ve got above, the mode returned is 8.99, as its the only number that appears twice.

Bi Modal Result Set

There is a bit more complexity to Mode in some situations, essentially as you can get a bi-modal result set - where there is more than one value occurring the most. E.g. the mode of 1,2,2,3 is just 2, but the mode of 1,2,2,3,3,4 is both 2 and 3 – so it’s known as bi-modal.

The Excel Mode function won’t help in this scenario, as it will just return one of the mode values. Interestingly Excel 2010 does support this, with the Mode.Mult() function, but I’ve been unable to get this to work, presumably as the Excel function returns an array.

All is not lost though, it’s possible to produce mode using MDX. First of all, the numbers that I’m operating on are shown below. As you can see, there are two sets of two numbers that are the same:

As the following forum thread shows, mode can be achieved using MDX. I’m adapting that approach to give the following MDX:

WITH 
--Produce a result set that will guarantee bi modal results
MEMBER    [Measures].[Demo] AS
        CASE WHEN [Product].[Subcategory].CurrentMember IS [Product].[Subcategory].&[31]  THEN 65.91
        ELSE Cdbl([Measures].[Internet Sales Amount]) END

--Count how often each value appears
MEMBER [Measures].[ValueCount] AS 
SUM( 
    Union([Product].[Subcategory].CurrentMember.Level.Members,
        {[Product].[Subcategory].CurrentMember} AS Currentsub)
    , IIF(([Product].[Subcategory].CurrentMember, [Measures].[Demo]) = 
        (Currentsub.Item(0).Item(0), [Measures].[Demo]), 1, null)
)

--Only get the items that appear the most
SET [MaxModes] AS 
    ORDER(FILTER(NONEMPTY([Product].[Subcategory].[Subcategory].Members, {[Measures].[Demo]}),
    [Measures].[ValueCount] = MAX(NONEMPTY([Product].[Subcategory].[Subcategory].Members, [Measures].[Demo]), 
        [Measures].[ValueCount])), [Measures].[Demo], ASC)

SELECT   {[Measures].[Demo], [Measures].[ValueCount]} on 0,
        [MaxModes]
        --Filter out the duplicates 
        HAVING [MaxModes].CurrentOrdinal = 0 OR [Measures].[Demo] <> 
            ([Measures].[Demo], [MaxModes].Item([MaxModes].CurrentOrdinal - 2)) ON 1
FROM    [Adventure Works]
WHERE    ([Date].[Date].&[20070727])

This gives the following correct result set, assuming we just want the two bi modal values:

An alternative is to use an MDX Filter() or HAVING clause to just display all the sub categories that have the mode values, which would just require a small modification to the above code.

I’ve not used these approaches with big data volumes etc, but they should at least give you a few options if you’ve got to do these sort of calculations in your own environment.

Power View Default Field Set

2012-02-23T11:57:10Z

Here's another quick tip when creating data models for use with Power View - Default Field Sets can be created in both Tabular SSAS and PowerPivot that give the user a shortcut for automatically adding multiple fields to a report.

As an example, here's a screenshot of the Default Field Set in PowerPivot - this will tell Power view that the following fields should be automatically selected when the table is added to a Power View report:

I'd seen this in the documentation a while back, but hadn't actually used it, nor could I see a way to select the table. In fact, although there's no checkbox, it's just a simple single click on the actual table name, as I’ve highlighted below:

When you click on the table name Product above, you will now get the following fields automatically added to a table visualisation:

So not exactly rocket science, but worth doing! Here's how to set up the Default Field set for PowerPivot and Tabular Analysis Services.

PowerPivot Settings for Power View

2012-02-20T16:30:59Z

I’ve been using both PowerPivot and Power View quite a bit recently and, in addition to the post I did a while back, have made a few further observations on getting the two to play nicely together.

Building an Example PowerPivot Model

For this post I’ve created a very very simple PowerPivot model, based on the Adventure Works Internet Sales Fact table and its related tables. What this means is that after the model is built, I can build a variety of Excel reports that summarise the measures in the FactInternetSales Adventure Works fact table, such as Sales Amount, Tax Amount or Order Quantity. In the example below I’ve built a very basic report showing Order Quantity by year:

On to Power View

Having already published the workbook to SharePoint, my task now is to create a Power View report, which can be done via the PowerPivot Gallery. If wanted to try and create a visual version of the above report in Power View (E.g. a graph), my steps would be to do first of all drag Calendar Year into the report, then Order Quantity. However, when doing this recently, the result wasn’t quite what I expected:

Order Quantity has not been summed at all, which is the opposite behaviour to that of PowerPivot. Consequently I can’t create a graph visualisation, because Power View thinks I don’t have any measures in my report:

Yet if we contrast this to measures such as Internet Sales Amount or Tax Amount, they all work fine. Upon looking at the field list for Internet Sales, we will see that Order Quantity is missing the sum symbol, as Power View has not interpreted it as a measure:

We can get around this in Power View with a bit of dragging and dropping, but we may as well look why it’s happened in the first place. The reason for this is the way in which the PowerPivot import wizard interprets SQL data types. The OrderQuantity column in the FactInternetSales SQL table has a data type of Smallint, which will not be summarised by default in Power View. Decimal and Money types are fine, hence why Tax Amount and Sales Amount above look ok.

Rather than changing the data types, we can force Power View to interpret the column as a measure by going to the PowerPivot advanced tab and clicking on Summarize By. If the Summarize By value is changed from Default to Sum, then Power View will interpret the column as a measure:

If we republish the model and then go back to Power View, we will see quite a difference with the same actions as before. First of all the field list is looking correct:

Secondly, only dragging Calendar Year and Order Quantity will now give the following correct results:

Now that we have a measure, it means that we can now change the table into a visualisation of our choice:

Power View is the kind of product that power users are hopefully going to find intuitive, but we can make things even easier for users by making small changes like this.

Master Data Services in SQL Server 2012 RC0

2011-11-30T13:31:34Z

There’s been a whole host of changes to MDS in the SQL Server 2012 RC (Release Candidate) 0 that came out the other week. This blog post gives an overview of the changes, before diving into detail on a few of them. At a high level, the following changes have been made to MDS:

Improved Master Data Manager front screen UI and navigation paths
Collections interface updated to Silverlight
Improved Excel user interface & functionality
Auto generation of entity code values, without using business rules
New deployment tool
SharePoint integration

Let’s take a look at each of these changes:

Improved Master Data Manager UI

Although the UI improvement (shown below) is good, the best thing about the Master Data Manager changes, in my opinion, is that clicking on the Explorer feature no longer takes you into the Model View, but instead takes you straight into your master data for your core entity (e.g. in a Customer model this entity would be Customer). I’m not sure if this would get a bit frustrating if you didn’t want the core entity, but then again everything seems very quick in RC0, so I don’t think it’s really going to matter.

There’s also a new button next to a domain-based attribute that apparently has been designed to help with Many-to-Many relationships. I can definitely see that working, but it’s useful to have anyway to jump to the member details for the domain based attribute that you are viewing, many-to-many or not:

Collections Interface Updated to Silverlight

The collections interface is now much slicker, getting the Silverlight makeover seen in other areas of Master Data Manager. Switching between collections, for example, which could take a while in R2, now happens very quickly, making collections far more useable. The screen shot below shows how you edit collection members by picking members from one of the entities and adding those over into the collection by clicking the Add button:

Collections now have the concept of a Weight, meaning that you can alter the weighting value associated with the collection members, which could be useful for reporting purposes. The idea is that you extract the collection members and the weight values in a subscription view. A Weight column is actually included in the 2008 R2 collection subscription views, but there was no front end to modify the weight value, which has of course now changed:

Improved Excel User Interface and Functionality

The MDS Excel Ribbon has been given a makeover, meaning that you now see the following in the ribbon:

It’s interesting to see that the Favourites section has been replaced with the concept of ‘queries’. The idea is that you can send a query file via email to another user, providing that user has the Excel add-in. When the user double clicks on the query file (extension *.mdsqx), Excel will open and make a connection to MDS, using the connection and filter information provided in the file. This will result in Excel opening, with the user prompted if they would like to connect to MDS:

It’s also good to see that a few of the domain-based attribute issues have been addressed, namely the display of domain-based attribute names when you filter an entity and also the display of the names and the codes together in the Excel sheet. Here’s a screen shot of the how the attribute names are now visible when filtering the Country domain-based attribute that exists in the Customer model:

Auto Generation of Entity Code Values

If you wanted the code to be auto-generated in 2008 R2, you had to use a business rule. You can still do that if you want, but the create entity admin screen has changed slightly to include an option to auto generate the code. This works slightly better than business rules in my opinion, at least as far as the Excel add-in is concerned, as the code is returned to the user immediately after publishing, whereas the business rules require you to do a refresh in Excel and of course need more development! Here’s a screenshot of the add entity screen:

New Deployment Tool

Deployment has been altered in SQL 2012, with the addition of a new deployment tool, plus the fact that subscription views now get deployed. It seems that the current R2 deployment method (in the Administration section of Master Data Manager) is still included but now will not deploy data. To do that you need to use MDSModelDeploy.exe, as explained here. As an example, here’s how you can deploy the sample Customer model using MDSModelDeploy.exe, for default installations:

MDSModelDeploy deploynew –package “C:\Program Files\Microsoft SQL Server\110\Master Data Services\Samples\customer_en.pkg” –model “Customer” –service “MDS1”

SharePoint Integration and Further Details

Master Data Manager now supports a set of parameters that allow the MDS UI to be displayed without the header, menu bar and padding area. This means that MDS can now be incorporated into SharePoint or other websites. For the details on this, as well as more details on the above points, take a look at the following Technet article.

Working with Images in Power View

2011-11-29T13:04:31Z

Power View includes several ways to create visualisations with images, ranging from a simple table, to the scrollable tile visualisation. In this post I’m going to explain a bit about the tile visualisation, showing how it can be used in Power View, before showing a couple of tips for working with images in both PowerPivot and Analysis Services Tabular models.

Power View Tiles

The Power View Tiles visualisation allows you to display a scrollable series of images, which, when clicked on, will display data of your choosing. The report below is an example of this:

What’s nice about the View above is that the names of the products are automatically displayed underneath the images. This is a sensible place to display the product names, as it means the area below can focus on whatever product level data you want, in this case Revenue by Month for the selected product.

This works fine in the sample models (in my case the Tailspin Toys model that I think came with CTP3) but it wont work in vanilla PowerPivot / Tabular models unless you configure a few model settings. Trying to reproduce the report above without these settings will give the following tile visualisation, minus the product names:

PowerPivot Advanced Settings

To address this in PowerPivot, you first of all need to switch to Advanced Mode, which you can do by selecting this option from the File menu in PowerPivot:

This gives you the following advanced tab:

Clicking on the highlighted Table Behaviour button in the ribbon gives you the following Table Behaviour window:

Here you can “change the default behaviour for different visualisation types and default grouping behaviour in client tools for this table”. The following properties should be set in order to get the tiles working correctly:

Row Identifier – Specifies a column that only contains unique values, which allows the column to be used as an internal grouping key in client tools.
Default Label – Specifies which column gives a row level display name, e.g. the product name in a Product table. This is key, as when a row level image is used, this property specifies which value to display alongside the image in tiles and other image-based visualisation.
Default Image – Specifies which column contains images representing the row level data, e.g. pictures of products.

The full details for all these properties can be found here.

Tabular Models

Tabular Models contain the same properties, which can be edited when you open a tabular project in the new SQL Server Data Tools. The image below shows how the properties have been configured for the column in my model called Image:

Back to Power View

If we now build a tile visualisation using the image column, we will see that we get the names of the products returned along with the image. It’s also worth noting that the Image and Product Name columns have now have an icon next to them, indicating that the field exhibits “row level” behaviour:

Finally, for some examples of what’s new in Power View SQL 2012 RC0 take a look at this post on the Reporting Services Team Blog.

Managing SSAS Named Sets with Master Data Services Collections

2011-11-07T17:04:00Z

Master Data Services Collections are probably not the first feature that come to mind when you think of the MDS product. The hierarchies and member grids tend to be the core functionality, but as this post will hopefully show, MDS Collections are useful also.

Collections are essentially managed lists of members that can come from multiple different MDS explicit hierarchies, or also from another collection. The idea is that this “subset of members” can be maintained in MDS by a business user and then fed to external applications for reporting or other purposes.

Analysis Services Named Sets

One example of how collections can be used is to maintain Analysis Services named sets. Some named sets, such as a Top 50 Customers, don't require any maintenance, as it’s just the top 50 of all customers, based on a particular measure. On the other hand, sometimes named sets can be static lists, e.g. a list of core products that the user wants to see for reporting.

In the latter example, if a user wants the definition of a named set to change, they have to get IT to change the MDX script. MDS collections can help by allowing the user to control the named set definition, reducing the burden on IT.

Example Scenario

Here’s an example of how this is done. First of all, the end game is that I have a named set in Analysis Services that is currently just for 3 products. Therefore, a user can easily drop this set into an Excel report to get quick access to the products that are important to them:

So the challenge is that we need to find some way of extending this named set without doing it manually. This is where MDS starts to come in, so using the sample Product model that comes with MDS, I’ve created a new collection called Favourite Products, as shown in the image below:

If I go and edit the Favourite Products collection in MDS, then I can drag and drop more products into this collection, or remove some existing members. In this case, I’ve chosen to add two new products:

C#, AMO and the MDS API

So the named set represents the target that we need to get to, whereas the MDS collection that’s shown is the source. To get the contents of the MDS collection to update the named set, one way of doing it is to use the MDS API to pick up the collection members, and then to use AMO in order to write the named set into the MDX script. I’m just doing this in a C# windows application, but you could do it via a batch process, such as SSIS. For this post I’m just going to show the code, so here goes:

This post is already starting to feel too long so I’m not going to show the basics of the MDS API. For that, take a look at a good posting by the MDS Team blog here. Also, as anyone who knows me will no doubt agree, I don’t tend to get involved in doing C#, so don’t consider this to be production ready! It should give you an idea of the basics though. Anyway, assuming that we’re now connected to the MDS Web Service, I’ve created the following method that will return the members from the collection:

private HierarchyMembers ObtainHierarchyMembers(string entityId, string hierarchyId, string modelId, string versionId)
{
    HierarchyMembersGetRequest request = new HierarchyMembersGetRequest();
    HierarchyMembersGetResponse response = new HierarchyMembersGetResponse();
    request.HierarchyMembersGetCriteria = new HierarchyMembersGetCriteria();

    //State that our hierarhcy type is a collection and that we want collection members
    request.HierarchyMembersGetCriteria.HierarchyType = HierarchyType.Collection;
    //Pass in the key search criteria to identify the correct collection in MDS
    request.HierarchyMembersGetCriteria.ParentEntityId = new Identifier { Name = entityId };
    request.HierarchyMembersGetCriteria.HierarchyId = new Identifier { Name = hierarchyId };
    request.HierarchyMembersGetCriteria.ModelId = new Identifier { Name = modelId };
    request.HierarchyMembersGetCriteria.VersionId = new Identifier { Name = versionId };

    request.HierarchyMembersGetCriteria.RowLimit = 50;
    request.International = new International();
    OperationResult result = new OperationResult();
    //Return the hierarchy members from the service
    return service.HierarchyMembersGet(request.International, request.HierarchyMembersGetCriteria, out result);
}

Before we use the above method, we need to connect to SSAS using AMO. That can be done quite easily with the following code:

Server server = new Server();
string connection = "Data Source=.;Catalog=Adventure Works DW 2008R2;";
server.Connect(connection);

After we’ve done all the usual error handling associated with connecting to a database, we need to pick up the SSAS database and cube:

Database database = server.Databases["Adventure Works DW 2008R2"];
Cube cube = database.Cubes["Adventure Works"];

Now we’re ready to call our ObtainHierarchyMembers method, before looping through it to build the named set:

StringBuilder mdx = new StringBuilder("\n//Auto generated named set at " + DateTime.Now.ToString() + 
    "\nCREATE SET CurrentCube.[Favourite Products] AS {");
int count = 1;
//Loop through the collection to build the mdx
foreach (ParentChild pc in hm.Members)
{
    //Add the members to the MDX string
    //This references the member by name
    //It would be possible to reference by member key, but would require more work
    mdx.Append("[Product].[Product].[" + pc.Child.Name + "]");
    if (count < hm.Members.Count())
    {
        mdx.Append(", ");
    }
    count++;
}
mdx.Append("};");

Now we need to insert the named set in the correct place within the existing MDX script, bearing in mind it could already exist:

string currentScript = cube.MdxScripts[0].Commands[0].Text;
//Find the correct place to insert the named set within the MDX script:
int start = currentScript.IndexOf("\n//Auto generated named set at");
int end = 0;
StringBuilder newScript = new StringBuilder(currentScript);
if (start != -1)
{
    end = currentScript.IndexOf(";", start);
    //If the named set already exists, remove it
    newScript.Remove(start, end - start + 1);
}
else
{
    start = currentScript.Length;
}
//Insert the named set in the correct place
newScript.Insert(start, mdx.ToString());
//Update the cube's MDX script
cube.MdxScripts[0].Commands[0].Text = newScript.ToString();

Finally we just need to update the cube in order to write the MDX back to the cube:

//Call the update methods to update the cube
cube.MdxScripts[0].Update();
cube.Update();

User Reports

Now that the cube has been updated, if the Excel report is refreshed, then the two new products will appear in the list:

Summary

Controlling SSAS named sets like this won’t be for everybody. I’ve certainly worked in a few clients where strict process has to be followed to update this sort of thing, but I can think of other companies that I know where this would be really useful.

Managing Analysis Services named sets is just one use for collections. Another example might be managing default multi-select parameters for SSRS reports. As collections are just lists of members that can be extracted easily, what you do with them is up to you!

Master Data Services Training in the UK

2011-11-01T23:33:00Z

This is just a quick post to announce a range of SQL Server training courses, organised (and in some cases delivered) by Chris Webb. To start off there’s a SQL Server course delivered by Christian Bolton in December, followed by an Analysis Services course delivered by Chris in February. I’ll be delivering a Master Data Services course in February, before Chris delivers an MDX course in March.

The details for all the courses are:

SQL Server Internals and Troubleshooting Workshop - Christian Bolton – 6th – 7th December 2011

The Advanced Troubleshooting Workshop for SQL Server 2005, 2008 and R2 provides attendees with SQL Server internals knowledge, practical troubleshooting skills and a proven methodical approach to problem solving. The workshop will enable attendees to tackle complex SQL Server problems with confidence.

Full details and registration here.

Real World Cube Design and Performance Tuning with Analysis Services – Chris Webb – February 2012

A two day course that takes real world experience in showing you how to build a best practice Analysis Services cube, covering design issues such as data warehouse design and complex cube modelling. Day two then covers performance optimisation for Analysis Services, including MDX optimisation and cube processing.

Full details and registration here.

Introduction to Master Data Services with Jeremy Kashel – February 2012

An end to end look inside Master Data Services, this full day course will begin with a synopsis of Master Data Management before moving on to an overview of Microsoft SQL Server 2008 R2 Master Data Services (MDS). The remainder of the course will cover the major MDS topics, such as modelling and business rules, which will include a number of practical exercises.

More details and registration for here.

Introduction to MDX with Chris Webb – March 2012

The Introduction to MDX course aims to take you from the point of being a complete beginner with no previous knowledge of MDX up to the point where you can write 90% of the MDX calculations and queries you’ll ever need to write. The three day course covers the basics, such as sets, tuples, members to more advanced concepts such as scoped assignments and performance tuning.

Full details and registration here.