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

33 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. The actual time and effort took to create this wonderful article were really great and would like to read this blog regularly to get more updates...
    DevOps Training in Chennai | DevOps Online Training in Chennai

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

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


    RPA Training in Hyderabad

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

    ReplyDelete
  9. A bewildering web journal I visit this blog, it's unfathomably heavenly. Oddly, in this present blog's substance made purpose of actuality and reasonable. The substance of data is informative
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

    ReplyDelete
  10. A befuddling web diary I visit this blog, it's incredibly grand. Strangely, in this present blog's substance made motivation behind fact and sensible. The substance of information is instructive
    Oracle Fusion Financials Online Training
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training

    ReplyDelete
  11. 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
  12. Nice Blog, thanks a lot for this post. need help on same requirement like, need sql server authentication. i am new to Powershell

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

    ReplyDelete
  14. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery

    ReplyDelete
  15. 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
  16. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.salesforce course in chennai

    software testing course in chennai

    robotic process automation rpa course in chennai

    blockchain course in chennai

    devops course in chennai

    ReplyDelete
  17. My spouse and I love your blog and find almost all of your posts to be just what I’m looking for. Appreciating the persistence you put into your blog and the detailed information you provide. I found another one blog like you Data Analytics .Actually I was looking for the same information on internet for Data Analytics Service and came across your blog. I am impressed by the information that you have on this blog. Thanks once more for all the details.

    ReplyDelete
  18. Thanks for sharing this blog. The content is beneficial and useful. Very informative post. Visit here to learn more about Data Mining companies and Data analytics Companies. I am impressed by the information that you have on this blog. Thanks once more for all the details.Visit here for Top Data Migration Companies.

    ReplyDelete