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