SQL Server 2017 Hosting – Document And Track Changes In Your Tabular Model

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.

A typical Tabular Model design in VS.

Finally, I designed a tool that will extract the Tabular Metadata and save it into an SQL Server database. Click here for more details and codes.

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?

1. Track/trace down the metadata changes after each deployment
2. Document each object in the system from a business and IT perspective (object naming, business naming, description, etc…)
3. “Translation” in SSAS Tabular (Multi-Language in SSAS)
4. Data lineage/flow for IT and Business groups.
5. Data type lineage from the Data Warehouse (DW) table fields up to the DM attribute.
6. BI auditing (Pre-auditing data before loading to the DW)
7. Incremental Partition Processing (incremental loading)
8. Evaluate/understand the design.
9. Business rules and requirements
10. Data growth
11. SSAS and SQL security.

To extract the Metadata I have provided a step by step solution with full codes in details.

Solution Breakdown

1. Databases (DM DB)
2. Tables
3. Columns (Fields)
4. Calculated Columns
5. Hierarchies
6. Table Annotation
7. Relationship
8. Perspective
9. Perspective Table
10. Perspective Relationship
11. Perspective Column
12. Perspective Hierarchies
13. Perspective Measures
14. Measures
15. KPI
16. Partitions
17. etc…

Conclusion

You should now be able to accurately and efficiently obtain the metadata from a tabular model.