Friday 7 October 2016

Document and Track changes in your Tabular Model

Problem Statement

One of the biggest problems I have encountered was the inability to easily trace the variety of changes being made to my tabular model. Every developer was making and deploying changes to the model on the fly and I was having trouble tracking these down. On top of that, the client wanted me to document every single object within the tabular model, provide reports that can compare the changes between each deployment of the tabular model and so on...

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?

  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. 

You can see the list of the Tabular objects after you have installed SQL Server Data Tools in Visual Studio (SSDT).

Do you know who TOM is?

Let me introduce you to TOM, the tabular object model.

Example Tabular Object Model hierarchy

System Requirements

In order to proceed with the solution mentioned below, you must have the following installed/configured in your environment:
  1. SQL Server 2016 or higher.
  2. SQL Analysis Services 2016 (Tabular)
  3. 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) >
  4. PowerShell (ISE its more visualize it helps more)
  5. Create a SQL database to hold the tabular metadata information. In my example I have called it [ASTabularPropertyCollector].
  6. Create 3 schemas in your database : [Tab], [Ppt] and [Dsv] (which stands for Tabular model, Perspective and Data Source View, respectively)
NOTE:  In order to confirm that you have what you need installed correctly, you may run this PowerShell script: 002--TestTheRegistry.ps1. For more information, please see: Install, distribute, and reference the Tabular Object Model.

Solution

I 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).

Special Note
Each solution I have provided contains:
  1. A SQL script that creates the related SQL table - this script must be run first.
  2. A PowerShell script that will extract the metadata from the DM and INSERT it into the SQL database ([ASTabularPropertyCollector])
    1. 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
    2. Generally I use a *.bat file to load my PowerShell scripts and each time I change the $BatchLogID manually. 

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 tabular model.

References

Tabular Model Designer (SSAS)
Comparing Tabular and Multidimensional Solutions (SSAS)
https://msdn.microsoft.com/en-us/library/mt706505.aspx

24 comments:

  1. Thank you for any other excellent article. Data Modeling describes strategies and specific techniques for getting the best performance from your tabular models,

    ReplyDelete
  2. This works very well. We are using these scripts during development of multiple models in a small team. Running these scripts on schedule, and querying the tables from MS Power BI Desktop ensures we are always up to date of how our models are designed

    ReplyDelete
  3. hi ckunz70
    I am happy to see someone is using it, can you give me some feedback if you can? you can email me at SNikkhah@yahoo.com
    thanks and enjoy the code

    I have done the same thing for MS-CRM and SQL-DBs
    Sincerely
    Nik

    ReplyDelete
  4. Iam very happy after find this post. Thanks for sharing this Informative content. Well explained. Got to learn new things from your Blog. I like the way you describe this post. Its really helpful for the users of this site. If You Want More Details About SQL Click here.

    ReplyDelete
  5. Good post...Thanks for sharing the valuable information..keep on updating the latest updates. Best software Training institute in Bangalore

    ReplyDelete
  6. Very clear explanation. Please share more like that..


    RPA Training in Hyderabad

    ReplyDelete
  7. how can we implement the same solution on the hadoop using hive metastore.

    ReplyDelete
  8. Good blog information
    Sanjary Kids is one of the best play school and preschool in Hyderabad,India. The motto of the Sanjary kids is to provide good atmosphere to the kids.Sanjary kids provides programs like Play group,Nursery,Junior KG,Serior KG,and provides Teacher Training Program.We have the both indoor and outdoor activities for your children.We build a strong value foundation for your child on Psychology and Personality development.
    Preschool in hyderabad

    ReplyDelete
  9. Nice Blog, thanks a lot for this post. need help on same requirement like, need sql server authentication. i am new to Powershell

    ReplyDelete
  10. Great Information and good article nice blog. Thanks for sharing this useful information article. Online Computer Store | All in One Pc

    ReplyDelete
  11. This great article has really peaked my interest. I am going to book mark your blog and keep checking for new information about once per week.
    IELTS Coaching in chennai

    German Classes in Chennai

    GRE Coaching Classes in Chennai

    TOEFL Coaching in Chennai

    spoken english classes in chennai | Communication training

    ReplyDelete
  12. I expected to thank you for this in your appreciating ensnare!! I particularly participating in all little piece of it I have you ever bookmarked to take a gander at conveyed stuff you articulate. Hotspot Chield For Windows Crack

    ReplyDelete
  13. Imagenomic Portraiture enables us to accomplish excellence in portrait retouching by removing the arduous labour of choosing masking and pixel-by-pixel treatments.. Portraiture Plugin For Photoshop CC Crack

    ReplyDelete
  14. Public Cousins Day has been made so we require the day to perceive the long lasting connections that we have with our cousins. Frequently, Happy Cousins Day

    ReplyDelete
  15. very nice post well define Data Model database (DM) is important for various business intelligence projects, such as: documentation, data lineage, metadata design changes, data growth etc, i found Top 55+ Data Modeling Interview Questions with Answers this is very beneficial for us .

    ReplyDelete
  16. Thank you for putting together this guide on AWS certification training. It's inspiring to see experienced professionals like you sharing their knowledge and experiences to help others in their AWS journeys. Please visit our website:- aws certification training online

    ReplyDelete