Master Data Services tables such as the staging tables have been well blogged and documented, but there are a host of other system tables and views that can help you with an MDM project. By querying this tables and views, we can gather MDS metrics such as the number of members, number of validation errors and staging errors, all of which can then be exposed to a data steward via a dashboard. Given all of the recent updates around Power BI, I decided to use Power BI Desktop in order to build an MDM dashboard for this blog post. The goal in this case is to produce a dashboard that allows a data steward to get an overview of metrics on an MDS model by model basis.
Power BI Desktop
The first step when working with Power BI is to import some data. Once we start Power BI Desktop the welcome screen gives you a shortcut to get data, or we can do this via the ribbon:
In this case we need to take data from SQL Server, so clicking the SQL Server option requires us to enter a server name and optionally a database name. Once we do this and pick the MDS database, we get a list of tables choose from:
Clicking on Load will load the tables into Power BI.
Master Data Services Objects
The question is what tables do we need? If you navigate the MDS database you will see that there is a mix of Master Data tables (e.g. post-fixed with _EN for entity), application tables and metadata views (prefixed with viw_SYSTEM). Its the system views that we need to start with, as we want to allow a breakdown by model, entity and version. To do this we need to load in the following views:
--Models SELECT ID AS ModelId, Name AS ModelName FROM MDM.viw_SYSTEM_SCHEMA_MODEL --Entities SELECT E.Id AS EntityId, E.Name AS EntityName, E.Model_ID, e.Model_Name, E.Model_MUID FROM MDM.viw_SYSTEM_SCHEMA_ENTITY E --Get a count of versions --This is used as a total and by version status SELECT V.ID AS VersionId, V.Name AS VersionName, V.Model_MUID, V.Model_Name, V.Status AS VersionStatus FROM [mdm].[viw_SYSTEM_SCHEMA_VERSION] V
Next it would be useful to report on the member count in each entity. This is a little harder as its not stored in the metadata tables, but we can combine the MDS metadata views with the SQL Server sys.dm_db_partition_stats Dynamic Management View in order to return the row counts per member:
--This gives the row count per entity for all models SELECT E.Name AS EntityName, M.Name AS ModelName, M.MUID, M.ID AS ModelId, 'mdm.' + E.EntityTable AS TableName, P.row_count FROM MDM.viw_SYSTEM_SCHEMA_ENTITY E INNER JOIN MDM.viw_SYSTEM_SCHEMA_MODEL M ON M.MUID = E.Model_MUID INNER JOIN sys.dm_db_partition_stats P on P.object_id = OBJECT_ID('mdm.' + e.EntityTable) AND P.index_id = 1
Finally a few more metrics that would be useful are the number of staging errors and a breakdown of the members by validation status (Validation Succeeded, Validation Failed, Awaiting Validation). To do this we can take in the following tables and stored procedures:
--Get the current count of errors that have occured during staging SELECT Entity_ID AS EntityId, ErrorMemberCount, Version_ID FROM mdm.tblStgBatch --Pass in the appropriate version id EXEC [mdm].[udpValidationStatusSummaryGet]
--This gets the validation issues by model/entity: SELECT ValidationIssue_ID, Version_ID, Model_ID, ModelName, Entity_ID AS EntityId, EntityName, Member_ID, BusinessRuleName FROM [mdm].[viw_SYSTEM_USER_VALIDATION]
Power BI Relationships and Visualisations
Once we import the data, Power BI Desktop will detect relationships automatically for us, but can alter them by clicking on Manage Relationships on the ribbon if we wish. The following shows the relationships between the various MDS objects mentioned above:
Once we’re happy with the model and the relationships, then we need to start dragging and dropping to build the Power BI report. As an example we can create a simple card visualisation to show the count of various metrics:
This can be used to produce the following metrics, the count of entities, versions, validation issues and staging errors:
Equally we can create a simple column chart by using the [mdm].[viw_SYSTEM_USER_VALIDATION] view. The count of issues is the Value section of the chart, whereas the Business Rule Name is on the Axis:
Putting it all together we can get the following report, shown for the sample Product model:
This is just a start, there are other metrics that you could put onto an MDM dashboard – as an example you could include MDM ETL metrics if you’ve logged them. But overall the combination of the rich MDS metadata and intuitive Power BI interface means its relatively easy to produce simple dashboards.