Sutha's SQL BI Blog

4Ps of MDM

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

1. Practices
2. Policies
3. Politics
4. Products

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

Thanks
Sutha

MDM Project Approach

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

1. 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.
2. 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.
3. 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.
4. 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

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.

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.

Staging Tables

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

1. tblStgMember - Members staging table.
2. tblStgMemberAttribute - Attribute assignment staging table.
3. tblStgRelationship - Parent / Child relationship staging table.
4. 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

1. Create the dimension models and associated hierarchies, entities and attributes.
2. Manage model versions. This would involve copying versions, locking versions, validate for completeness and committing versions.
3. Manage users. Create users and assign appropriate security.
4. Process staging tables. Populate MDM database with dimension model data.
5. Load MDM data views. Load the model versions into the subscribing systems to be viewed.
6. Format and input data. Format the data from the source systems and populate the MDM staging tables with the dimension model data. 
7. 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

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

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

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

All

Kilimanjaro

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

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

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

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".

Thanks
Sutha

Dependancy Analysis Viewer / Resource Governor

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