Speaking the language of business intelligence with an Australian accent

Tuesday, November 24, 2009

Beginning MDS – Getting at the Data with TSQL (Part 7 of 7)

OK, so we’ve created the objects and loaded data into them. Now we can have a closer look at what has happened to the MDS database. What has been built? Where is the data stored?

The aim of this final post is to get you started towards locating your data stored in the MDS repository database. There are plenty of ways to get at the data but we’re going to just take a quick peek at accessing the data via TSQL. Remember that TSQL isn’t the only way to get at this data. I just haven’t had much of a chance to have a detailed look at the MDS web service and API yet.

Walkthrough

So where’s our Geography model data? Let’s start by finding the identifier of the model itself.

-- get your model ID 
SELECT
FROM   mdm.tblModel 
WHERE  [Name] = 'Geography'
The ID for your model will vary, mine is 15.
 
SQL_tblModel

Note that there are a number of different metadata UDFs that can return scalar and tabular data for a variety of things such as model ID, I’m just going to do it the manual way for the purposes of demonstration.
Armed with the model ID we can take a look at the Entities defined within that model.

SELECT
FROM   mdm.tblEntity 
WHERE  Model_ID = 15  -- change to your model number 
SQL_tblEntity
 
The most interesting stuff returned by this query are the table name references to the structures containing records concerning Entity, Security, Hierarchy, HierarchyParent etc. Note the format of the table names. For example tbl_15_53_EN refers to a “table for Model ID= 15, ID = 53, for Type = Entity”.
 
The two letter table suffixes refer to the following:
  • EN = Entity
  • MS = Security
  • HR = Hierarchy
  • HP = Hierarchy Parent
  • CN = Collection
  • CM = Collection Member

The MDS engine builds tables to store data for the objects that are created within models. Here is a list of all the tables created as a result of our efforts with the Geography (ID = 15) model.

SQL_ModelTables

Have a look inside the table that contains the records for the City entity, remembering that your own IDs (both for the model and the entity) will vary from mine.

-- city entity 
SELECT
FROM   mdm.tbl_15_53_EN

SQLCityEntity 
Among the other metadata related to the members of the City entity are the Name and Code fields, as expected. Note the column named uda_CAAPFLF at the far right of the table. The prefix “uda_” I assume stands for User Defined Attribute. Thanks to the referenced relationship created in one of the earlier walkthroughs this column participates in a physical FK relationship to the ID in the StateProvince entity table (in my case the StateProvince entity table is named mdm.tbl_15_54_EN).
 
Now take a look inside the mdm.tblAttribute table for all the user defined attributes in our model.
SELECT   a.*
   
FROM     mdm.tblAttribute a
   
WHERE    EXISTS (
   
SELECT *
       
FROM   mdm.tblEntity e
       
WHERE  e.ID = a.Entity_ID
           
AND e.model_ID = 15) -- change to your model number  
       
AND MemberType_id = 1 -- leaf attribute  
       
AND DataType_id = 1 -- user defined  
   
ORDER BY Entity_ID
SQLAttributes

Notice the uda_CAAPFLF reference in the TableColumn column for StateProvince. This provides a reference back to the column that links the StateProvince attribute to the City entity.

On top of the system-generated-model-centric tables that MDS generates there are also system views that already do much of the heavy lifting for you when it comes to getting at the data. Here are the views that MDS created by the Geography model objects.

SQLSystemViews

Based on what we have built the most useful views are the …CHILDATTRIBUTES ones. These will return the records within a particular entity including all the attributes that have been defined on it.

SELECT *
   
FROM   mdm.viw_SYSTEM_15_53_CHILDATTRIBUTES

All the human readable data is located at the far end of the table, so remember to scroll all the way to the right.

SQLSystemViewCityMembers

Note the friendly column names that have been created as part of the view definition.

If you want to look at the parent/child metadata that was defined as a result of the derived hierarchy we created look at the contents of the …PARENTCHILD_DERIVED views.

-- the 10 in this case refers to the ID in the mdm.tblDerivedHierarchy table 
SELECT *
   
FROM   mdm.viw_SYSTEM_15_10_PARENTCHILD_DERIVED

SQLSystemViewDerivedHierarchy

Hopefully this whirlwind tour of the MDS repository DB has been enough to pique your interest. Take time to explore the inner workings of the database and find all the good stuff that is baked into the product and how you can leverage it.

This post also marks the end of this series of walkthroughs, hope they were useful.

10 comments:

Martin Harmelin said...

Hi Nick

I am a bit curious about you wanting to dig into the tables inside MDS. Have seen some other posts around the place wanting to do the same thing. I always understood one of the key aspects of a product such as this was to protect us from these complexities and protect itself from those wanting to fiddle under the hood where they can perhaps cause some damage. The views provided by the product (which have progressively increased in number and variety through the +EDM versions) should cover most requirements.

Otherwise, I am glad to see MDS getting some exposure. Over the last few years (with +EDM) we have seen several cases of politics and people getting in the way - particularly some IT bods who wanted to do it all their own way and not let users anwyhere near the master data.

On another front, I don't think MDS is as immature a product as some might think. It started as a dimension management tool to support cubes before the broader application became obvious. +EDM was at version 4 just before becoming MDS, and prior to that was recognised as one of the major players in a field of 5 or 6 products. It has just been hard to spot for a couple of years while being transformed into MDS.

Also wondering if the old +EDM manuals and training material will be re-jigged for MDS - might save you some typing :)

Looking forward to seeing a lot more appearing about MDS in use for specific business issues.

Cheers

Anonymous said...

Thank you very much for this post. I actually found it much more helpful to understand than the EDM documentation/training manuals that I've seen.

Nick Barclay said...

Glad you found it useful :)

Anonymous said...

As an EDM user i am very happy for these blogs.
Some thinks are very familiar, other are interesting new concepts.
Waiting to find more.
Thanks for the info you have forwarded..

Subhasis said...

Hi Nick,

It was a wonderful article on MDS to start with. I have a question. Can I insert or update data into the tables of the source database through MDM?

Thanks,

Roy

Nick Barclay said...

"source database" - do you mean the MDS repository database or another separate DB?

NB

Subhasis said...

Seperate database, the database from where we are pulling the records, eg. AdventureWorksDW2008R2 database. Also, I need to know how Export option is performed.

Thanks.

Nick Barclay said...

You can export model data from within the Management UI.

As for updating an external DB this is something you would want to explore the MDS API - it would be a custom dev effort on your part.

Subhasis said...

Thanks Nick. I'll have a look at it.

Anonymous said...

Hi Nick,

Very good article thank you. I found when digging into mdm.tblAttribute - instead of DataType_ID = 1, shouldn't it read AttributeType_ID = 1 to filter for user defined attributes?

Kevin Pickering