SSAS Tabular Mannequin Documenter with Energy BI with out DMVs

Expertise is rising quick and we’re having fun with it. We reshape our every day created knowledge in a kind that satisfies our wants. One of many applied sciences which is used extra generally nowadays is SQL Server Tabular Fashions, SSAS Tabular in brief. Numerous 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 massive debate round SSAS Multidimensional vs. SSAS Tabular. However my goal is to forestall going by that type of dialogue. Because the title implies, this put up is about documentation that I consider is among the most essential components of each undertaking which can also be sacrificed essentially the most. On this put up I clarify find out how to doc your SSAS Tabular mannequin in Energy BI Desktop and Excel. I do know, there are some merchandise you’ll find within the web that may generate documentation in numerous codecs like Phrase, PDF, HTML and so forth. However, For those who’re in search of a free and someway extra intuitive means 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 methodology we don’t use DMVs in any respect. For many who aren’t acquainted with DMVs I shortly clarify what DMVs are, if you happen to’re already acquainted with DMVs you’ll be able to bounce this part.

You possibly can 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 Providers. 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, subsequently you utilize “SELECT” assertion adopted by “$System” which is an XMLA schema rowset. The DMV queries appear like beneath:

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 a lot of details about tables in your Tabular mannequin:

choose * from $SYSTEM.TMSCHEMA_TABLES

Learn extra about DMVs right here.

Be aware:This methodology 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 find out 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 find out how to discover the metadata file and find out how to construct a mannequin in Energy BI Desktop. I additionally clarify find out how to do the identical in Excel for these of you who want to add some annotations or feedback to the outcomes.

The place to Discover SSAS Tabular Metadata Database?

Properly, it is determined by your SSAS Tabular prompt configuration. For those who caught to the default settings you’ll find it in your database folder below “Information” folder. It’s important to search for “metadata.sqlitedb” file. For example, 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 completely different for various prompt names. My occasion title is “SQL2017Tabular” which is likely completely different than yours.

• The  “AdventureWorks2016.0.db” half is the database title that you just’re keen to have a look at

Doc SSAS Tabular in Energy BI

As I discussed earlier I beforehand defined find out how to load SQLite knowledge in Energy BI Desktop so I assume you already know find out 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 just 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 routinely detects the relationships, however, they’re all incorrect. So you need to delete the relationships and recreate them manually as beneath:

Relationship mapping is as beneath:

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 incorporates DAX expressions used to outline the measures. You too can see the partition queries in “Partition” desk which is admittedly superior isn’t it?

Up to now we imported knowledge from the “metadata.sqlitedb” file which is admittedly cool. However we aren’t completed but. We’ll must do some knowledge transformation in Question Designer. Earlier than going to the following step, I renamed all columns known as “Identify” to extra acceptable names like “Desk Identify” or “Perspective Identify”.

Information Transformation in Energy Question

For those who have 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 want to do these type of issues in Energy Question. Particularly if I want so as to add a brand new column, it’s the very best to deal with it in Energy Question versus DAX if attainable.

What I’m going to do is so as to add a “Desk Identify” column to the “PerspectiveTable”. The “Desk Identify” column comes from “desk” desk. To take action we now have to someway lookup the “Desk” desk to get the “Identify”. There’s a very helpful perform in Energy Question “Desk.NestedJoin” which within the UI is 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 sort is “Interior Be part of”.

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

• From prime proper click on “Merge Queries”

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

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

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

You now have a brand new “Desk” column.

Develop the column and choose “Desk Identify” column from the record then click on OK

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

It’s possible you’ll do the identical in “PerspectiveColumn”, “PerspectiveMeasure” and “PerspectiveHierarchy” tables so as to add the corresponding column with the related object title.

Parameterise Information Supply

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

• In Question Editor click on “Handle Parameters”

• Click on “New”

• Enter a “Identify” and “Description”

• Tick “Required”

• For our mannequin we will go away “Kind” and “Advised Values” as is

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

Now that you must modify the queries to make use of the parameter

Click on “Executed”

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 be taught extra about Measure Tables.

Listed here 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 Stories

The ultimate step is essentially the most attention-grabbing one. Now we will see what is occurring in our SSAS Tabular Mannequin.

I constructed a report like beneath that incorporates two pages. One report web page supplies basic 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 answer listed beneath. You guys could have a treatment for it, in that case please share it with us within the remark part beneath.

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 truth these columns include essential date/time associated info. However that is what you’ll be able to see if 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 obtained which each are fairly incorrect:

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

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

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

As a matter of truth the values loaded within the mannequin are incorrect integer values! Let me clarify. Whenever you connect with a SQLite database through ODBC you could have two possibility of loading all numeric values as Int32 or Int64. For those who browse the SQLite metadata database you’ll see that the “ModifiedTime” and all different dates are BigInt not Int.

This can be a screenshot of the “Desk” desk from “metadata.sqlitedb” file open in “DB Browser for SQLite”.

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

To get the right values in Energy BI, we now 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 Consumer DSN in ODBC and tick the corresponding possibility.

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

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

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

When you have any higher concept, I’m actually curious to listen to about it, so please go away your feedback down beneath.

Be aware:For those who run DMVs you then 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 incorporates 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.

For those who run DMVs you’ll get the decrypted worth in ConnectionString column.

As soon as once more, if you understand how to try this, then please go away your feedback within the remark part beneath 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 house for the device to rapidly undergo the “metadata.sqlitedb” file and get a lot of insights about your SSAS Tabular mannequin.

I made the device out there so that you can obtain without spending a dime.

That is my Christmas current for you.

Merry Christmas and see you in 2018.

Cheers!

Obtain SSAS Tabular Documenter in Energy BI

Associated