Tuesday 22 November 2016

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.

Perspective Hierarchy

Perspective Column - 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 columns 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: 0384 PerspectiveColumn.sql
  2. Run the script in SSMS.
  3. Download the PowerShell script: 384--PerspectiveColumn.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 [PerspectiveColumn] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Perspective Column

Tuesday 15 November 2016

Perspective Table - 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 tables 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: 0382 PerspectiveTable.sql
  2. Run the script in SSMS.
  3. Download the PowerShell script: 382--PerspectiveTable.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 [PerspectiveTable] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Perspective Table

Perspective - 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 perspectives 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: 0380 Perspective.sql
  2. Run the script in SSMS.
  3. Download the PowerShell script: 380--Perspective.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 [Perspective] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Perspective

Sunday 6 November 2016

Relationship - 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 relationships 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: 0335 Relationship.sql
  2. Run the script in SSMS.
  3. Download the PowerShell script: 335--Relationship.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 [Relationship] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Relationship

Measures - 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 measures 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: 0400 Measure.sql
  2. Run the script in SSMS.
  3. Download the PowerShell script: 400--Measure.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 [Measure] within your [ASTabularPropertyCollector] database containing the metadata information for your SQL tabular model.

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

Measures