Tuesday, November 24, 2009

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.

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.

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.

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

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.

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.

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.

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

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:

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.