Skip to content

Business Intelligence Insights

Business Intelligence and Data Visualisation Articles

  • Home
  • About
  • Contact me
  • Visit Data Vizioner Now
    • Data Vizioner
    • Data Vizioner Blog
    • Start Using Power BI Documenter Now

Tag: Business Intelligence

BISM Tabular, DAX, Power BI, Power BI Desktop, Power Query, SQL Server Analysis Services - SSAS, SQLite

SSAS Tabular Model Documenter with Power BI without DMVs

SSAS Tabular Documenter with Power BITechnology is growing fast and we are enjoying it. We reshape our daily created data in a form that satisfies our needs. One of the technologies which is used more commonly these days is SQL Server Tabular Models, SSAS Tabular in short. Lots of industries decide to go with SSAS Tabular in their new projects and some defined new projects to slowly switch their existing SSAS Multidimensional to SSAS Tabular. I know, there is a big debate around SSAS Multidimensional vs. SSAS Tabular. But my aim is to prevent going through that sort of discussion. As the title implies, this post is about documentation that I believe is one of the most important parts of every project which is also sacrificed the most. In this post I explain how to document your SSAS Tabular model in Power BI Desktop and Excel. I know, there are some products you can find in the internet that can generate documentation in various formats like Word, PDF, HTML and so on. But, If you’re looking for a free and somehow more intuitive way of documenting your SSAS Tabular Models with Power BI then this article is for you.  Through this article, we create a documentation tool with Power BI. I call it SSAS Tabular Model Documenter. In this method we don’t use DMVs at all. For those who are not familiar with DMVs I shortly explain what DMVs are, if you’re already familiar with DMVs you can jump this section.

You can download a copy of SSAS Tabular Model Documenter in Power BI template format (pbit) at the end of this post. It is a Christmas present for you.

What are DMVs?

Dynamic Management Views, DMVs in short, are queries that retrieve metadata information about an instance of SQL Server Analysis Services. DMVs work on both SSAS Multidimensional and SSAS Tabular server modes. DMVs can be used to monitor server operations and health. The DMV query structure is very similar to T-SQL, therefore you use “SELECT” statement followed by “$System” which is an XMLA schema rowset. The DMV queries look like below:

SELECT * FROM $System.<schemaRowset>

So you can open SQL Server Management Studio, connect to an instance of SSAS (Tabular model for the sake of this post) and run the following query to get lots of information about tables in your Tabular model:

select * from $SYSTEM.TMSCHEMA_TABLES

Running DMVs in SSMSRead more about DMVs here.

Note:This method only works with SSAS Tabular 2016 and above.

Document SSAS Tabular without DMVs

In SSAS Tabular 2016 and above there is a tiny metadata database that can be loaded in Power BI Desktop or Excel to document the corresponding SSAS Tabular model. The database is a SQLite database. In the previous post I explained how to visualise SQLite data in Power BI. The requirements for this post are the same as the previous post, so I encourage you to check it out. Therefore, I just explain how to find the metadata file and how to build a model in Power BI Desktop. I also explain how to do the same in Excel for those of you who would like to add some annotations or comments to the outcomes.

Continue reading “SSAS Tabular Model Documenter with Power BI without DMVs” →

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Telegram (Opens in new window)
  • Click to share on Skype (Opens in new window)
  • More
  • Click to share on Pocket (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to print (Opens in new window)
  • Click to email this to a friend (Opens in new window)

Like this:

Like Loading...
December 12, 2017September 7, 2019BI, Business Intelligence, Data Management Views, Data Visualization, Dataviz, DAX, DMV, DMVs, Dynamic Management Views, M, Metadata, MSBI, Numeric DateTime, ODBC, Power BI, Power BI Desktop, Power Quey, Query Parameter, Schema Query, SQLite, SSAS, SSAS DMV, SSAS Metadata, SSAS Tabular, SSAS Tabular DMV, SSAS Tabular Documenter, Tabular 2016, Tabular DMV, Tabular Documenter, Tabular Metadata, Tabular Object Model, Tabular Schema Query, TOM, Unix Epoch, Unix Timestamp2 Comments
Microsoft MVP (Most Valuable Professional)

Keep in Touch

  • LinkedIn
  • Twitter
  • Google
  • Email

Categories

Recent Posts

  • Quick Tips: Line Chart and Area Chart Conditional Formatting in Power BI
  • Good Practice: Always Display the Reporting Environment
  • Power BI Ecosystem Report Authoring Tools Demystified
  • Quick Tips: Export Power BI Desktop and Power BI Service Model Data In One Shot with DAX Studio
  • What Does XMLA Endpoints Mean for Power BI and How to Test it for Free?

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org

RSS

RSS Feed RSS - Posts

Subscribe via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Follow me on Twitter

My Tweets
December 2019
MTWTFSS
« Sep  
 1
2345678
9101112131415
16171819202122
23242526272829
3031 

Archives

  • December 2019
  • September 2019
  • April 2019
  • March 2019
  • February 2019
  • November 2018
  • September 2018
  • July 2018
  • June 2018
  • May 2018
  • April 2018
  • March 2018
  • February 2018
  • December 2017
  • November 2017
  • July 2017
  • March 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • July 2016
  • June 2016
  • May 2016
  • April 2016
  • March 2016
  • February 2016
  • January 2016
  • December 2015
  • October 2015
  • September 2015
  • August 2015
  • July 2015
  • June 2015
  • May 2015
  • April 2015
  • March 2015
  • February 2015
  • January 2015
  • September 2014
  • August 2014
  • July 2014
  • April 2014
  • February 2014
  • December 2013
  • October 2013
  • September 2013
  • August 2013
  • July 2013
Proudly powered by WordPress / Theme: Shoreditch by Automattic.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: