SSAS Tabular Mannequin Documenter with Energy BI with out DMVs

Know-how is rising quick and we’re having fun with it. We reshape our day by day created knowledge in a kind that satisfies our wants. One of many applied sciences which is used extra generally as of late is SQL Server Tabular Fashions, SSAS Tabular in brief. A number of industries determine to go together with SSAS Tabular of their new tasks and a few outlined new tasks to slowly swap their present SSAS Multidimensional to SSAS Tabular. I do know, there’s a large debate round SSAS Multidimensional vs. SSAS Tabular. However my purpose is to forestall going by that form of dialogue. Because the title implies, this put up is about documentation that I consider is without doubt one of the most vital components of each venture which can also be sacrificed probably the most. On this put up I clarify learn how to doc your SSAS Tabular mannequin in Energy BI Desktop and Excel. I do know, there are some merchandise yow will discover within the web that may generate documentation in numerous codecs like Phrase, PDF, HTML and so forth. However, Should you’re on the lookout for a free and by some means extra intuitive manner of documenting your SSAS Tabular Fashions with Energy BI then this text is for you.  By this text, we create a documentation device with Energy BI. I name it SSAS Tabular Mannequin Documenter. On this technique we don’t use DMVs in any respect. For many who will not be acquainted with DMVs I shortly clarify what DMVs are, for those who’re already acquainted with DMVs you’ll be able to bounce this part.

You may obtain a duplicate of SSAS Tabular Mannequin Documenter in Energy BI template format (pbit) on the finish of this put up. It’s a Christmas current for you.

Dynamic Administration Views, DMVs in brief, are queries that retrieve metadata details about an occasion of SQL Server Evaluation Companies. DMVs work on each SSAS Multidimensional and SSAS Tabular server modes. DMVs can be utilized to observe server operations and well being. The DMV question construction is similar to T-SQL, due to this fact you employ “SELECT” assertion adopted by “$System” which is an XMLA schema rowset. The DMV queries appear like under:

SELECT * FROM $System.<schemaRowset>

So you’ll be able to open SQL Server Administration Studio, connect with an occasion of SSAS (Tabular mannequin for the sake of this put up) and run the next question to get plenty of details about tables in your Tabular mannequin:

choose * from $SYSTEM.TMSCHEMA_TABLES

Learn extra about DMVs right here.

Observe:This technique solely works with SSAS Tabular 2016 and above.

In SSAS Tabular 2016 and above there’s a tiny metadata database that may be loaded in Energy BI Desktop or Excel to doc the corresponding SSAS Tabular mannequin. The database is a SQLite database. Within the earlier put up I defined learn how to visualise SQLite knowledge in Energy BI. The necessities for this put up are the identical as the earlier put up, so I encourage you to test it out. Due to this fact, I simply clarify learn how to discover the metadata file and learn how to construct a mannequin in Energy BI Desktop. I additionally clarify learn how to do the identical in Excel for these of you who wish to add some annotations or feedback to the outcomes.

The place to Discover SSAS Tabular Metadata Database?

Effectively, it depends upon your SSAS Tabular on the spot configuration. Should you caught to the default settings yow will discover it in your database folder below “Information” folder. You must search for “metadata.sqlitedb” file. As an illustration, I wish to doc my “AdventureWorks2016” Tabular Mannequin and my SSAS Tabular occasion configuration is the default configuration. So I can discover the “metadata.sqlitedb” file right here:

C:Program FilesMicrosoft SQL ServerMSAS14.SQL2017TABULAROLAPDataAdventureWorks2016.0.db

• The highlighted a part of the above path could be totally different for various on the spot names. My occasion identify is “SQL2017Tabular” which is most likely totally different than yours.

• The  “AdventureWorks2016.0.db” half is the database identify that you simply’re prepared to take a look at

Doc SSAS Tabular in Energy BI

As I discussed earlier I beforehand defined learn how to load SQLite knowledge in Energy BI Desktop so I assume you already know learn how to load knowledge from the “metadata.sqlitedb” file to Energy BI Desktop.

Suggestion: I like to recommend you to take a duplicate of the “metadata.sqlitedb” file earlier than getting the information in Energy BI.

After you navigate the database in Energy BI you’ll be able to both choose all tables or you’ll be able to choose just a few tables that you simply want for the documentation. Personally I loaded the next tables on the first time:

“Mannequin”

“Measure”

“KPI”

“Stage”

“Hierarchy”

“Column”

“Partition”

“Perspective”

“PerspectiveColumn”

“PerspectiveHierarchy”

“PerspectiveMeasure”

“PerspectiveTable”

“Desk”

Energy BI mechanically detects the relationships, however, they’re all incorrect. So it’s a must to delete the relationships and recreate them manually as under:

Relationship mapping is as under:

Now you’ll be able to create some measures like “Variety of Tables”, “Variety of Views”, “Variety of Measures” and so forth and create charts and tables to create superior report.

Have a look at the “Measure” desk and also you see that it accommodates DAX expressions used to outline the measures. You may as well see the partition queries in “Partition” desk which is admittedly superior isn’t it?

Thus far we imported knowledge from the “metadata.sqlitedb” file which is admittedly cool. However we’re not completed but. We’ll must do some knowledge transformation in Question Designer. Earlier than going to the following step, I renamed all columns referred to as “Title” to extra applicable names like “Desk Title” or “Perspective Title”.

Information Transformation in Energy Question

Should you take a look at the “PerspectiveTable” desk you see one thing like following:

As you’ll be able to see there’s a “TableID” column. So if we wish to see which tables are used within the views then we have to have desk names. We will deal with this in numerous methods. I personally desire to do these form of issues in Energy Question. Particularly if I want so as to add a brand new column, it’s the most effective to deal with it in Energy Question versus DAX if potential.

What I’m going to do is so as to add a “Desk Title” column to the “PerspectiveTable”. The “Desk Title” column comes from “desk” desk. To take action now we have to by some means lookup the “Desk” desk to get the “Title”. There’s a very useful operate in Energy Question “Desk.NestedJoin” which within the UI known as “Merge Queries”.

Open Question Editor to merge the “PerspectiveTable” question with “Desk” on the “ID” column from “Desk” and “TableID” from “PerspectiveTable”. The be a part of kind is “Interior Be a part of”.

• Click on “PerspectiveTable” from the “Queries” pane

• From high proper click on “Merge Queries”

• Within the “Merge” window, choose “Desk” from the dropdown listing

• Choose “TableID” column from”PerspectiveTable” and “ID” column from “Desk”

• Set the “Be a part of Sort” to “Interior Be a part of” then click on OK

You now have a brand new “Desk” column.

Develop the column and choose “Desk Title” column from the listing then click on OK

“Desk Title” column is now added to the desk.

You might do the identical in “PerspectiveColumn”, “PerspectiveMeasure” and “PerspectiveHierarchy” tables so as to add the corresponding column with the related object identify.

Parameterise Information Supply

Question parameters have been round for fairly a very long time. I wrote a collection of weblog posts about Question Parameters. It’s smart to parameterise the information supply for our mannequin in order that we are able to simply swap to a unique knowledge supply.

• In Question Editor click on “Handle Parameters”

• Click on “New”

• Enter a “Title” and “Description”

• Tick “Required”

• For our mannequin we are able to go away “Sort” and “Instructed Values” as is

• Enter the “metadata.sqlitedb” file path in “Present Worth” then click on OK

Now it is advisable modify the queries to make use of the parameter

Click on “Achieved”

Defining Helpful Measures

It’s now time to outline some easy and helpful measures. A measure to point out the variety of hidden tables or hidden columns, variety of outlined KPIs, variety of hierarchies outlined within the SSAS Tabular Mannequin and so forth. I put all measures in a Measure Desk. Click on right here to study extra about Measure Tables.

Listed below are the measures I outlined, you most likely wish to add some extra.

Hidden Columns = CALCULATE(COUNTROWS('Column'), 'Column'[IsHidden]=1)

Hidden Tables  = CALCULATE(COUNTROWS('Desk'), 'Desk'[IsHidden]=1)+0

Hierarchies  = COUNTROWS('Hierarchy')

KPIs  = COUNTROWS('KPI')

Measures  = CALCULATE(COUNTROWS('Measure'), 'Measure'[KPIID]=BLANK())

Partitions  = COUNTROWS(Partition)

Perspective KPIs  = CALCULATE(COUNTROWS(PerspectiveMeasure), PerspectiveMeasure[KPIID]<>BLANK())

Perspective Measures  = CALCULATE(COUNTROWS(PerspectiveMeasure), PerspectiveMeasure[KPIID]=BLANK())

Perspective Tables  = COUNTROWS(PerspectiveTable)

Views  = COUNTROWS(Perspective)

Tables  = COUNTROWS('Desk')

Creating Reviews

The ultimate step is probably the most attention-grabbing one. Now we are able to see what is occurring in our SSAS Tabular Mannequin.

I constructed a report like under that accommodates two pages. One report web page gives normal details about the mannequin and the second reveals extra particulars about measures and KPIs together with expressions and formatting.

Drawbacks!

Hmm. There are some drawbacks with the present resolution listed under. You guys might have a treatment for it, in that case please share it with us within the remark part under.

Numeric Date/Time

Taking a look at any of the tables loaded in Energy BI you’ll discover at lease one column that supposed to point out related Date/Time. The column is both “ModifiedTime”, “StructureModifiedTime” or “RefreshedTime”. As a matter of reality these columns include vital date/time associated info. However that is what you’ll be able to see once you load knowledge into Energy BI:

I do know, I do know… They appear like Unix Epoch timestamp however they’re not!

I transformed the values to DateTime utilizing each 1970 and 1900 as beginning date and that is what I acquired which each are fairly fallacious:

Listed below are the Energy Question (M) expressions I used:

1900 Unix Epoch to Date = #datetime(1900,1,1,0,0,0)+#period(0,0,0,[ModifiedTime])

1970 Unix Epoch to Date = #datetime(1970,1,1,0,0,0)+#period(0,0,0,[ModifiedTime])

As a matter of reality the values loaded within the mannequin are fallacious integer values! Let me clarify. If you connect with a SQLite database through ODBC you may have two choice of loading all numeric values as Int32 or Int64. Should you browse the SQLite metadata database you’ll see that the “ModifiedTime” and all different dates are BigInt not Int.

It is a screenshot of the “Desk” desk from “metadata.sqlitedb” file open in “DB Browser for SQLite”.

You could have already observed that the values don’t appear like  regular Unix Epoch that we are able to convert them to this point/time. They’re the certainly Unix Epoch in Nanoseconds, sure! nanoseconds, since 1st Jan 1601!

To get the proper values in Energy BI, now we have to allow BigInt numbers within the ODBC connection string or DSN.

You are able to do that simply by including “BigInt = True” in connection string or alternatively you’ll be able to create a Person DSN in ODBC and tick the corresponding choice.

Nevertheless, after we allow BigInt, all numeric values no matter their authentic knowledge varieties get transformed to BigInt. It’s getting worst once you load that knowledge in Energy BI as Energy BI considers all BigInt values as Binary. That implies that it’s a must to convert all Binary values to both Int or Int64 in Question Editor which is such a headache. However, it really works in spite of everything!

Right here is an screenshot of the accurately transformed Epoch timestamp to Date values:

I’m not going to develop this any additional because it makes this writing even longer.

If in case you have any higher concept, I’m actually curious to listen to about it, so please go away your feedback down under.

Observe:Should you run DMVs then you definately don’t get Unix Epoch values. You certainly get good and tidy date/time values.

Encrypted Information Supply

There’s a “DataSource” desk within the metadata file which I haven’t mentioned above. The “DataSource” desk accommodates the connection string to the supply knowledge that’s used within the SSAS Tabular Mannequin. The connection string is encrypted (Base64) and I couldn’t decrypt the worth.

Should you run DMVs you’ll get the decrypted worth in ConnectionString column.

As soon as once more, if you know the way to try this, then please go away your feedback within the remark part under this put up.

Conclusion

I nonetheless use DMVs to get some helpful info just like the connection string and regular date/time values. Nevertheless, there’s a area for the device to shortly undergo the “metadata.sqlitedb” file and get plenty of insights about your SSAS Tabular mannequin.

I made the device accessible so that you can obtain totally free.

That is my Christmas current for you.

Merry Christmas and see you in 2018.

Cheers!

Obtain SSAS Tabular Documenter in Energy BI