Being able to accurately analyse a Data Model database (DM) is important for various business intelligence projects, such as: documentation, data lineage, metadata design changes, data growth and etc. In order to achieve this, you will need to extract the metadata objects from a DM and present them in a readable form, such as: SSRS, Power BI, or other business intelligence tool.
With SQL Server 2016 came the release of the tabular metadata object. The Tabular model decreases the amount of time required for designing and deploying.
Keeping this in mind, I said to myself, I must make a SQL database that will track the changes of each tabular metadata object. Basically, I need to extract the metadata of the tabular model or, in simple words, I need to make myself a "Metadata Dictionary".
NOTE: You can use Dynamic Management View (DMV) Queries to extract some of the objects. However, the DMV Queries do not provide the full breadth of the required information about the metadata.
What is a "Metadata Dictionary"?A Metadata Dictionary is a series of SQL tables in SQL Server that will contain the metadata (schema properties) of your tabular DM. In this article, we will achieve this through use of SQL and PowerShell scripts.
What are the uses of a Metadata Dictionary?
You can see the list of the Tabular objects after you have installed SQL Server Data Tools in Visual Studio (SSDT).
- Track/trace down the metadata changes after each deployment
- Document each object in the system from a business and IT perspective (object naming, business naming, description, etc...)
- "Translation" in SSAS Tabular (Multi-Language in SSAS)
- Data lineage/flow for IT and Business groups.
- Data type lineage from the Data Warehouse (DW) table fields up to the DM attribute.
- BI auditing (Pre-auditing data before loading to the DW)
- Incremental Partition Processing (incremental loading)
- Evaluate/understand the design.
- Business rules and requirements
- Data growth
- SSAS and SQL security.
Do you know who TOM is?
|Example Tabular Object Model hierarchy|
- SQL Server 2016 or higher.
- SQL Analysis Services 2016 (Tabular)
- At least one tabular model database within Analysis Services < If you are using AdventureWorks make sure it is set to, Version =13.0 and Compatibility Level =SQL Server 2016(1200) >
- PowerShell (ISE its more visualize it helps more)
- Create a SQL database to hold the tabular metadata information. In my example I have called it [ASTabularPropertyCollector].
- Create 3 schemas in your database : [Tab], [Ppt] and [Dsv] (which stands for Tabular model, Perspective and Data Source View, respectively)
SolutionI will provide you with everything you need to extract the tabular metadata information for each of the following. Each solution will extract the metadata from DM to DW. (I might add SSRS or PowerBI for visualization).
Each solution I have provided contains:
- A SQL script that creates the related SQL table - this script must be run first.
- A PowerShell script that will extract the metadata from the DM and INSERT it into the SQL database ([ASTabularPropertyCollector])
- The PowerShell script parameters need to be modified to fit your environment:
- $ASServer is your Analysis Service Server name (SSAS Instance Name)
- $SSASTabularDB is your Tabular Database name (Tabular DM database name)
- $SQLServer is your SQL Server name that contains the [ASTabularPropertyCollector] database
- $DBName is the name of the "[ASTabularPropertyCollector]" database
- $flgDisplayOnly, this is a flag that will allow you to see part of the extracted result in powershell ISE before inserting the data into the "[ASTabularPropertyCollector]", while testing use "TRUE" or else "FLASE" for a final insert
- $BatchLogID is a Batch Log ID for each Load of the Powershell scripts
- Databases (DM DB)
- Columns (Fields)
- Calculated Columns
- Table Annotation
- Perspective Table
- Perspective Relationship
- Perspective Column
- Perspective Hierarchies
- Perspective Measures
ReferencesTabular Model Designer (SSAS)
Comparing Tabular and Multidimensional Solutions (SSAS)