Adatis BI Blogs

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.

SQL Server Master Data Services now available

Whilst the core components of SQL 2008 R2 have been signing autographs on the red carpet, Master Data Services has sneaked quietly in through the back door.   If you have MSDN access, download a full version (guessing developer and enterprise only but haven’t checked) and off you go.  The install is a not part of the main installation either, you need to run it directly from the DVD\ISO in the MasterDataServices folder.  Setup is very simple and there’s some good sample packages to play with.  StreamInsight is on there as well :)

MDM White Papers

With the release of SQL Server 2008 R2 nearly upon us, it's a safe bet that the number of technical articles for MDS will start to increase a bit. In the meantime, I felt it was worth mentioning a few older MDM White Papers that may come in useful: Master Data Management (MDM) Hub Architecture by Roger Walter - Whereas you can find plenty of info on the web about the practice of MDM, this white paper provides some simple but good technical examples of how to approach MDM. Data Integration Solutions for Master Data Management by Elizabeth Vitt - This one is from a while back, but it brings SSIS into the mix so that you can see how SSIS can help with an MDM initiative. So again it's got some good technical examples. I've not seen too many articles covering MDM with SSIS on the web, so I felt that it was especially worth a mention. The What, Why, and How of Master Data Management by Roger Wolter and Kirk Haselden - This white paper covers the reasons to carry out MDM, plus explains the process of developing an MDM solution.

Master Data Services - Reversing Transactions

MDM tools give the control of the enterprise master data over to the data stewards and power users, rather than relying on automated data integration alone. Master Data Services is no exception to the above. One of the ways that this is true for MDS is that it allows users to inspect the transactions that have occurred (either internal to MDS or from a source system) and choose if they want to reverse them. In order to achieve this MDS has a useful log of all transactions that's viewable by users. Here's an example of some transactions that have occurred in my test system - some are from data that I've loaded up via the staging tables, some are from manual member additions that I've carried out in the front end, and some are from business rules that have automatically run: In the model that this example is taken from, I've got some business rules that look to address data quality issues. Taking the Kimball view on data quality issues in a data warehousing context - many can, and should, be addressed in the source system, then re-loaded. That isn't always possible, which is one of the reasons why we have business rules in MDS. However, as good any sort of automated rule is - there are always exceptions. In the transactions shown above, an automatic business rule has run that checks a Customer's overdraft limit, then sets it to 10,000 if its over 10,000. Therefore, when a value of 50,000 was encountered for Member Code 10311, the MDS business rules kicked in and quite correctly did their job. This was not what I wanted in this particular case. Thankfully we can click on the undo button that's shown above the grid, and reverse a chosen transaction, whether its come from a source system, a business rule or a manual edit. It doesn't seem possible to reverse many transactions at once, but that may be just due to the CTP. In my example, by selecting the first transaction in the list, then clicking the undo button, I've reversed my automatic business rule. Therefore, the user Kaylee Adams (10311) shown below now has her original overdraft limit: In conclusion, when some sort of manual intervention is needed to successfully manage master data, MDM tools allow that intervention to come from the power users, rather than having to wait for someone technical to address the issue.

Master Data Services - Business Rules

I've been keeping an eye on the SQL Server 2008 R2 CTPs over the past few months, but have been compelled to start blogging again following the release of Master Data Services (MDS) in the November CTP. The idea of a Microsoft MDM tool first caught my attention with the acquisition of Stratature, and since then I've seen a few talks on the subject, such as Kirk Haselden's talk on the subject back at the BI Conference last year. Now that I've got my hands on it, I've decided to cover the set up of business rules in MDS. Business rules are key to an MDM solution. If we want to use MDM to load data from disparate source systems, we will definitely have to carry out a lot of cleansing and confirming in order to ensure that the end users only consume clean and accurate data. To set the scene a bit, I've created several entities in my MDM model, namely Customer, Country and City. These could form a Customer Geography hierarchy for example, but for the moment I'm going to focus on Customer. The following shows the Customers that I've entered manually: When I add a Customer (via the button that is circled) or when I edit a customer, the third column of the grid for the relevant member will change from a tick to a question mark, indicating that data validation has not taken place. For this example, what I want to happen is for the Overdraft Limit attribute to validate that it is within normal boundaries that have been set by the business, e.g. a bank. To do this, I'm going to set up a simple business rule. Selecting Manage->Business Rules will take you to the Business Rules Maintenance screen, where the 'plus' icon will create you a new business rule. Editing the new blank rule will give a screen with a basic IF....THEN GUI to produce a basic business rule. On the IF part you pick conditions such as greater than, less than etc, alongside an all important dimension attribute. You do this by dragging and dropping conditions, in the screen below: In my case I've picked that the overdraft limit can't be greater than £10,000, and if it is greater, then set it back to £10,000. This will do for now, but I could have prevented validation from succeeding, or caused MDM workflow to start. Clicking the MDS back button will take us back to the business rules maintenance screen, where the rule is not active until we publish it: Once we do publish the rule, it will kick in whenever validation runs or when you manually run the business rules. In my grid of Customers above, I have an overdraft which is a mistake. When I validate the Customer entity, the 5555555 for the second customer automatically reverts to £10,000, as shown below: This is quite a simple example of course, and via manual editing. The real power of these business rules will come when loading masses of data from source systems, with the added power of workflow to prompt business users to deal with the validation issues that may occur. I'll aim to post about integrating from other systems via my next post in due course....

BI Conference - Day 2

First of all a quick correction following my post on day one where I had a bit of code name confusion.  Project "Madison" is the evolution of the DatAllegro acquisition, Project "Gemini" is the self service BI and "Kilimanjaro" is the code name for the (interim?) release in the first half of 2010 that will include "Madison" and "Gemini" So what of day two? The Ben Stein keynote was interesting, though not a patch on the Michael Treacy's  from last year.  This was followed by an extremely cheesy and completely staged "Q&A" session with the platinum sponsors about where BI will be in 2020.  I didn't stay to hear the answers! I know they have to keep these sponsors happy but do MS really think people take any notice of this stuff? I went for the MDM session next which was very heavy going and disappointing in that we still didn't get to see the product - though we may see it at next year's BI conference (same time next year)! Got to meet Patrick Husting and a number of other PPS experts over lunch, and in the afternoon we got a chance to quiz some of the Gemini team on a few of the points mentioned yesterday.  I raised the topic of "AS hell" where users are creating random cubes all over the place and they had a good response: People are always going to do self service reporting in some way whether we like it or not, no matter how good the underlying data is, so why not do it in a controlled manner where everything is audited and logged and the IT team has full visibility of what is going.  There was a "Gemini" breakout session which showed the operations dashboard behind the scenes - very cool.  "Gemini" really looks impressive and has obviously already had a lot of effort put in, I have a feeling the usual version 1 worries may not be surfacing too much here - people are going to be desperate to get their hands on it! Last session of the day for me was the  BI power hour which was very entertaining. A working Monopoly game in PPS with full analytics and Profit and Loss for each player, a girlfriend management Planning model (looking at how the seriousness of your relationship affects your future cashflow - ha ha) and two player battleships in Reporting Services - I want these guy's jobs! Also  a chance too briefly see MDM in action - it looks pretty good! Why they couldn't show it in the MDM session I don't understand... Finally the conference party which was held at the the impressive QWest stadium, home of the Seattle Seahawks, with casino games, lots of x-box and Football and American Football out on the pitch. Here's Jereminho scoring an absolute peach - back of the net!

Multi-dimensional modeling (ADAPT)

Everybody hates documentation but this is a really neat, free tool for modeling multi-dimensional databases and is particularly suited to MSAS 2005.  ADAPT™ (Application Design for Analytical Processing Technologies) from Symmetry Corporation is a Visio stencil.  Example of a time dimension is shown below: There's a white paper that's worth a read to get you started

Microsoft acquire Stratature

Microsoft have acquired the MDM company Stratature  So Kirk Hasselden's hints were as thinly veiled as they seemed after all! He was almost apologetic for not being able to tell us anymore at his session in Seattle and with hindsight it's clear why. This is definitely an interesting move and if MS are planning to bundle this very capable product in with Katmai (enterprise edition perhaps?) then it will really push them to the top of the league for enterprise data solutions.