Sutha's SQL BI Blog

4Ps of MDM

tsutha — Tue, 15 Dec 2009 09:15:00 GMT

On my previous post I did talk about MDM project will not succeed unless organisations change their processes and policies.

There are 4Ps in MDM process. They are

Practices
Policies
Politics
Products

Products is the technology, all the other 3 listed above are processes.

Thanks
Sutha

MDM Project Approach

tsutha — Tue, 08 Dec 2009 14:33:00 GMT

Ideally we would like the MDM to feed our source systems and data warehouse. I personally think it is a wishful thinking. I don’t think we will ever achieve that. MDM is the place of entry for any master data. It is not only an entry point, but it also records all the changes.

The above scenario is very optimistic and probably unrealistic expectation to run our business in this manner.

Best possible way to succeed in any MDM project is to start small (specially when you are new to the technology) and gradually increase the scope. If you decide to do comprehensive solution it will be very expensive and likely to fail. For most organisations the great place to start is Products. Ask questions such as does this domain add value to the organisation and can this project be reused when we come to do Customers? It is generally easier to get budget approval for small amount from a department or organisation. The general principle is I am only going to pay to fix my departmental issue.

Ideally you should select a domain / model for MDM and work on it initially. Identify the entities within the model. Identify attributes and hierarchies related to the Entity. This could be a repetitive process until you have a good design.

Once entity, model, attributes and hierarchies are identified, you can move on to business rules, process flows and user / group permissions.

The above diagram explains how we could go about implementing MDM project. We could do in small chunks or phases. In Phase1, we could get data from operational source systems such as Microsoft Dynamics and SAP into MDM. These source systems import could act as an initial system entry point for MDM. Once data is in MDM data governor or steward can modify or add additional information through the front end provided. The modified data in MDM is the source for a data warehouse across the enterprise.

During Phase 2 of the project, MDM is the system of record and entry for Microsoft Dynamics. In other words MDM is the master data owner for any reference data in Microsoft Dynamics.

Even though we would like to be in ideal scenario as I mentioned in my first diagram, but in reality it is near to impossible to achieve that goal. In reality MDM can be the system of entry for some of the data like Products, but not all of the data in ERP systems like Customers. When customer calls with an enquiry SAP system would need to log about it. They can’t and won’t wait until the data is in MDM.

During the analysis phase of the MDM project establish Stakeholders, Business Drivers, Processes (Current & Desired), Decide what is nice to have and what is out of scope of each Phase.

As I mentioned before start the project SMALL, have a sponsor (Someone at the top of the organisation). Solve a real business problem by embarking on a project like this. You should have really good technical resources on this project as well as someone from business who knows the entity very well. Make sure you have a business user involved in the MDM project as most technical people will NOT have business knowledge which is vital for the success of this type of project.

Please note MDM is software it is NOT a solution. MDM project will NOT succeed unless you change your processes and policies. For the project to succeed you would need the support from the top management to data steward. Select your project carefully, where you are going to get the backing for your project. I cant emphasise enough the important of the process. Process is more important than technology in MDM project.

What MDM is NOT

tsutha — Tue, 08 Dec 2009 11:27:00 GMT

It is not a transactional data store.
MDM is used to produce transactional data but transactional data is not master data. Product master is in MDM, but Orders is NOT.
It is not a data warehouse.
MDM is likely to be used as a dimensional data in the warehouse / BI environment. MDM doesn't hold any facts.
It is not a metadata management.
MDM is about managing and storing metadata about the master data. It doesn't mean it is a central metadata repository.
It is not a taxonomy management.
Master data entities and hierarchies look very similar to taxonomies but we don't have tool to gather and manage taxonomies.

MDS vs AS

tsutha — Mon, 07 Dec 2009 14:51:00 GMT

MDM / MDS is an application for creating and maintaining master view of reference data / master data. This application maintains policies and procedures as well as work flow management. Master data is slowly changing reference data shared across the whole enterprise. In most organisations the classic example of MDM are Customers and Products.

In the data warehousing or BI world all the master data would be used as Dimensions. MDM has central repository for reference data that is highly available across the enterprise. This would allow anyone to build multiple dimensions. The application can be web based. Security model can be configured to allow users to have limited access to any sensitive data like someone's DOB. This is a server based application and therefore it will be available to business around the clock, unlike master product file on someone's PC.Analysis Services cube is great for 3 dimensional reporting. It allows slicing and dicing using pre aggregated values where possible. Analysis Services not good at managing a change as AS engine is not tuned for edits. AS does allow write back, but very difficult to manage or audit. It doesn't have any workflow, notifications or business rules definition, stewardship built into the system.

I would recommend anyone to use MDS to manage the Master Data at all times and push the data out to source system and data warehouse using SSIS.

Thanks
Sutha

Staging Tables

tsutha — Mon, 07 Dec 2009 14:00:00 GMT

Data which get loaded into MDS Staging tables must certain standards. There are three staging tables.

tblStgMember - Members staging table.
tblStgMemberAttribute - Attribute assignment staging table.
tblStgRelationship - Parent / Child relationship staging table.
tblStgBatch - I think it is related to Staging Batches. This is under Integration Management, which would enable import / exprot process.

Thanks
Sutha

MDS System Administrator Tasks

tsutha — Thu, 03 Dec 2009 09:43:00 GMT

MDS administrator typically would have following responsibilities.

Create the dimension models and associated hierarchies, entities and attributes.
Manage model versions. This would involve copying versions, locking versions, validate for completeness and committing versions.
Manage users. Create users and assign appropriate security.
Process staging tables. Populate MDM database with dimension model data.
Load MDM data views. Load the model versions into the subscribing systems to be viewed.
Format and input data. Format the data from the source systems and populate the MDM staging tables with the dimension model data.
Manage business rules. Create and manage business rules to enforce corporate wide standards. Business rules would ensure dimensional accuracy across MDM.

Thanks
Sutha

Name & Code in MDS

tsutha — Wed, 02 Dec 2009 14:32:00 GMT

Nick Barclay has put together a wonderful blog entries. It gets you going on setting the model, entity, attributes etc.

Nick talks about how to create an attributes for a given Entity. By default you will get Name and Code as leaf attributes. YOU CAN NOT REMOVE THESE 2 LEAF ATTRIBUTES.

Name - This is a system attribute. It is required by MDS. It is assigned to a member that describes the member within a model. The member name "Customer Jo", could describe the customer called Jo.

Code - This is a system attribute. It is required by MDS. It is assigned to a member that uniquely identifies the member within the model. This code MUST be unique within the model.

Thanks
Sutha

Configuring MDS - MDS Configuration Manager

tsutha — Thu, 26 Nov 2009 12:07:00 GMT

MDS is part of SQL 2008 R2 (10.50.1352).

Please note you would need Internet Information Services (IIS) installed.

Once you have installed MDS you would need to configure MDS. By default MDS Configuration Manager would start as soon as the installation is completed. You don't have to configure straight away. You can dot it any time using MDS Configuration Manager from Programs\Microsoft SQL Server 2008 R2 November CTP\Master Data Services\ Configuration Manager.

When you start Configuration Manager the initial screen should look something similar to the one below.

Configuring MDS

If you are doing this first time, you would need to Create Database by selecting “Create Database”. This will start a new wizard as shown below.

Click Next and configure your database server and Test the connection:

Once you have configured and tested the Database Server click next. In this screen you can specify the MDS database name. It can be anything, but recommended to use some meaningful name. I prefer to use MDM as shown below. In this screen you can set the collation. Default is set to SQL Server Default Collation.

You need to give Service Account and Administrator Account in the next two screens as shown below:

At this point summary information will be displayed for us to review. If we are not happy we can go back and change the above information.

If you are happy with the summary information click next to create the MDS database. This step could take some time.

If all created successfully, you should success messages as shown below.

Click Finish to complete the Database Creation wizard. At this point you can see a database called “MDM” in SQL Server Management Studio if you connect to the server you have specified.

When you click on Finish the screen below will appear. Here you can change the system settings to suit your own environement.

Main system setting you need to be aware is Master Data Services URL. You can configure database mail at this point.

Click on Web Configurations, which should bring screen below.

As we don’t have MDS Site created and configured at this point, click on “Create Site”, which will bring a screen below.

In the above screen you need to specify the Web Site Name, Protocol, IP & Port. You would also need to give a name for Application Pool with username and password. Once you specified the values and click OK. At this point it will create the website and application for MDS. When completed the screen should look something similar to this.

Please note Server and Database name is populated for you. If you do want web services to access MDS programmatically you would need to select “Enable Web Services for this Web Application".

Now we are done. Click Apply, which should bring screen below.

When the web application is launched it might ask you for login details. When you enter the username and password the “GettingStarted” page will appear as shown below.

Here you go. Now Enjoy MDS!!!

You can reconfigure MDS website at anytime by just deleting Site and Application Pools. These two can be deleted by using IIS Manager.

Thanks
Sutha

Master Data Services - 64 Bit Install - How can you get around on your 32bit machine.

tsutha — Thu, 26 Nov 2009 10:24:00 GMT

Master Data Services (MDS) is one of the new features in SQL2008 R2. You can download SQL2008 R2 from here.

MDS is separate install. It is NOT part of SQL2008 R2 installation.

MDS is located at MasterDataServices\x64\1033_ENU. Installation is quite straight forward. You can install MDS using the MSI file in this directory.

You would notice it is only 64bit installation. I have got around that by 64bit VMWare Win-2008 Server on my 32bit laptop. I have Dell-Latitude-D630. I got help from Andrew Sadler as I didn't have a clue how to do this. I flushed the memory with latest BIOS (A16) and then enabled the Vitualisation setting on BIOS. As soon as that's done you are ready to go. Install any 64 bit OS environment and install SQL 2008 R2. Once SQL 2008 R2 is installed you can double click on the MasterDataServices.MSI file and install MDS. I haven't tried it on VPC yet.

In the same SQL 2008 R2 download you do get 32 & 63bit StreamInsight.

Thanks
Sutha

Made It To The Top of Kilimanjaro

tsutha — Wed, 28 Oct 2009 07:06:00 GMT

All

I have managed to climb Kilimanjaro and went to the summit (Uhuru Peak) on 17/10/2009. It was absolute torture, but it was worth it. Thank you for all the support and donations.

If anyone wish to donate, can do so via http://www.justgiving.com/sutha/

Microsoft didn't sponser me by the way.

Thanks
Sutha

Kilimanjaro

tsutha — Mon, 23 Mar 2009 19:50:00 GMT

All

I am climbing Kilimanjaro end of Sep 09 / Oct 09 (Depending on travel arrangements) well before Kilimanjaro is Released by Microsoft.

I am doing this out of my own money, but I would like to raise some money to the charity which helped my colleague and friend Jim Wright in his last weeks and days.

Maybe Microsoft can double the collection I make though friends. I think it would be great publicity for Microsoft. SQL Server flag on top Kili

If anyone wish to donate please go to JustGiving website. Please donate for a very good cause.

Thanks
Sutha

Connecting To Sybase Via SSIS

tsutha — Mon, 22 Dec 2008 20:30:00 GMT

Microsoft does not provide any drivers to connect to Sybase. Sybase have their own drivers (only 32 bit) but they do not have 64bit. I cant understand why they dont provide the 64bit drivers. Flipside of that why dont Microsoft provide any drivers for SSIS.

If you are using Oracle or Teradata as your source you can have Attunity Connectors for FREE as long as you have got SQL Server Enterprise Licence.

Unfortunately Microsoft didn't sponse Sybase Connector from Attunity. There is another option available from Data Direct. You have to purchase both connectors from respectable vendors. These connectors are not cheap!!

Thanks
Sutha

Late Arriving Facts & SSIS Contd

tsutha — Sat, 22 Nov 2008 17:41:00 GMT

As discussed before if you have a type 2 dimension and late arriving facts you are in a messy situation. Most of us do it using SQL Statements.

Lets see we could do this using SSIS. One of my colleague the following way, which seems to be a very good option but SSIS data flow task might be bit messy.

Let me describe each steps.
1. Read the data from Stage using OLE DB Source.
2. Conditionally split into 2 pipeline. Conditionally split would be done on a key date column. For example if you loading orders on a daily basis and you have a control table which data you have loaded already. You could use that table as a source and map it to a variable. Then anything less than that variable would be sent down the late arriving facts pipeline, where the others will send down the default (New records) pipeline. Some of you would be happy would setting late arring facts as anythign less than 2 days old. That would work too as long as you are happy with that scenario.
3. New records pipeline would use full cache lookups to get their surrogate keys. They will run fast.
4. Late arriving facts need to do the Lookups with the range value. This will work but will be slow. The reason for that is it needs to go to database for each record. By splitting up this way we have suggested only few records should go to the database for the surrogate keys.
5. Please note if you do this on SQL 2005, it will definitely go to disk for each record. In SQL2008, you can use Partial Cache to build the cache.

Since I wrote this article Matt Masson has put a great blog together about this. You can read it here.

Thanks
Sutha

Late Arriving Facts & SSIS

tsutha — Fri, 21 Nov 2008 15:29:00 GMT

One of my colleague asked us how are we currently handling Late Arriving Facts against Type2 dimension within SSIS.My answer was we don’t. Well I do ask the client do you have late arriving facts. Most of the time they don’t know what is late arriving facts. Once explained they turned around and say this never happens in our organisation. I am sure pretty sure it does happen and most of the time client doesn’t recognise it.

If you do have a warehouse with Type1 dimension late arriving facts is not an issue for you. This is an issue when you have type2 dimension. Kimball call this "messy situation".

I did ask this to 3 SSIS developers at SQLPass this week. The answer is not easy and will get back to you. In the meantime we have discussed about this scenario among ourselves and decided one of two ways.
1. Do it using SQL rather than SSIS
2. Split the records first in the Data Flow Task and handle it using Lookup. I will explain it in detail how to do in a separate posting.

Thanks
Sutha

Dependancy Analysis Viewer / Resource Governor

tsutha — Fri, 21 Nov 2008 15:16:00 GMT

I have been asking for Dependancy Analysis Viewer within SSIS since TAP for SQL2005. At last Microsoft seems to have listened to most of our needs and it might appear in the next release. Well done, well overdue but well appreciated.

In SQL2008, MS have released Resource Governor, but it does NOT govern Analysis Services. I have been working with AS since SQL7 and we always have long running MDX queries, bu we dont know who is running etc. I understand next release might have Resource Governor which would support AS.

Thanks
Sutha