Problem Statement
I had a SSAS Tabular Model project and I had a few developers making changes on the fly without telling me. My biggest problem was I couldn't track the changes and my client had lots of requirements that I needed to design and provide.
They are lots of ways to extract the SSAS metadata things like the DMV (Dynamic Management View (DMV) Queries) but the question is - will it provide the entire breadth of the internal SSAS object? The answer is no, not yet, but with PowerShell scripts, you can extract any internal object from SSAS Tabular Model.
What are the uses of a Metadata Dictionary?
- 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.
To extract the Metadata I have provided a step by step solution with full codes in details.
Solution Breakdown (Document track changes tabular model)
- Databases (DM DB)
- Tables
- Columns (Fields)
- Calculated Columns
- Hierarchies
- Table Annotation
- Relationship
- Perspective
- Perspective Table
- Perspective Relationship
- Perspective Column
- Perspective Hierarchies
- Perspective Measures
- Measures
- KPI
- Partitions
- etc...
Conclusion
You should now be able to accurately and efficiently obtain the metadata from a tabular model.
References