Perspective Hierarchy - Document and Track changes in your Tabular Model

Objective

Extract the metadata from a tabular model using a PowerShell script and SQL Server 2016. In this section, we are going to extract the list of Tabular perspective hierarchies and their corresponding metadata.

Requirements

Please see the requirements listed here: Document and Track changes in your Tabular Model

Steps

If you do not want to store metadata in a separate database, please proceed to step 3.

1. Download the script file: 0386 PerspectiveHierarchy.sql
2. Run the script in SSMS.
3. Download the PowerShell script: 386--PerspectiveHierarchy.ps1. 
4. Use PowerShell ISE (or other PowerShell tool) to open the PowerShell file.
5. Set the parameters in the PowerShell script to align with your environment (see Requirements).
1. This script can either display the information or populate the table created in the step above.
6. Run the PowerShell script (generally I use a *.bat file to load all of my PowerShell scripts)

Conclusion

Once the above steps have been completed, your SQL Server instance will have a table called [PerspectiveHierarchy] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

Querying this table should provide results similar to those seen below.