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.

 

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

 

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.

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

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.