Know-how is rising quick and we’re having fun with it. We reshape our day by day created information in a type that satisfies our wants. One of many applied sciences which is used extra generally lately is SQL Server Tabular Fashions, SSAS Tabular in brief. A number of industries resolve to go together with SSAS Tabular of their new initiatives and a few outlined new initiatives to slowly swap their current SSAS Multidimensional to SSAS Tabular. I do know, there’s a huge debate round SSAS Multidimensional vs. SSAS Tabular. However my purpose is to stop going by means of that kind of dialogue. Because the title implies, this put up is about documentation that I imagine is likely one of the most essential components of each challenge which can be sacrificed essentially 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 you’ll find within the web that may generate documentation in varied codecs like Phrase, PDF, HTML and so forth. However, In the event you’re in search of a free and by some means extra intuitive approach of documenting your SSAS Tabular Fashions with Energy BI then this text is for you.  By way of this text, we create a documentation instrument with Energy BI. I name it SSAS Tabular Mannequin Documenter. On this methodology we don’t use DMVs in any respect. For individuals who usually are not conversant in DMVs I shortly clarify what DMVs are, for those who’re already conversant in DMVs you possibly can 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 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 under:

SELECT * FROM $System.<schemaRowset>

So you possibly can 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 number 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 learn how to visualise SQLite information 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 want to add some annotations or feedback to the outcomes.

The place to Discover SSAS Tabular Metadata Database?

Properly, it will depend on your SSAS Tabular on the spot configuration. In the event you caught to the default settings you’ll find it in your database folder underneath “Knowledge” folder. It’s a must to search for “metadata.sqlitedb” file. As an 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 can be completely different for various on the spot 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 learn how to load SQLite information in Energy BI Desktop so I assume you already know learn how to load information 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 info in Energy BI.

After you navigate the database in Energy BI you possibly can both choose all tables or you possibly can 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 it’s a must to delete the relationships and recreate them manually as under:

Relationship mapping is as under:

Now you possibly can 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.

Take a look at the “Measure” desk and also you see that it comprises DAX expressions used to outline the measures. You too can see the partition queries in “Partition” desk which is actually superior isn’t it?

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

Knowledge Transformation in Energy Question

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

As you possibly can 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 are able to deal with this in varied methods. I personally desire to do these kind 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 doable.

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 helpful perform in Energy Question “Desk.NestedJoin” which within the UI is named “Merge Queries”.

Open Question Editor to merge the “PerspectiveTable” question with “Desk” on the “ID” column from “Desk” and “TableID” from “PerspectiveTable”. The be 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 checklist

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

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

You now have a brand new “Desk” column.

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

“Desk Title” 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 Knowledge Supply

Question parameters have been round for fairly a very long time. I wrote a collection of weblog posts about Question Parameters. It’s sensible to parameterise the info supply for our mannequin in order that we are able to simply swap to a special information 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 “Urged Values” as is

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

Now you could modify the queries to make use of the parameter

Click on “Carried out”

Defining Helpful Measures

It’s now time to outline some easy and helpful measures. A measure to indicate 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 here are the measures I outlined, you in all probability 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 Studies

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

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

Numeric Date/Time

any of the tables loaded in Energy BI you’ll discover at lease one column that supposed to indicate related Date/Time. The column is both “ModifiedTime”, “StructureModifiedTime” or “RefreshedTime”. As a matter of reality these columns include essential date/time associated info. However that is what you possibly can see once you load information 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 received which each are fairly unsuitable:

Listed here 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 unsuitable integer values! Let me clarify. Once you connect with a SQLite database by way of ODBC you have got two possibility of loading all numeric values as Int32 or Int64. In the event you 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 could have already seen 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 possibly can create a Person DSN in ODBC and tick the corresponding possibility.

Nonetheless, once we allow BigInt, all numeric values no matter their authentic information varieties get transformed to BigInt. It’s getting worst once you load that information in Energy BI as Energy BI considers all BigInt values as Binary. That signifies 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 broaden this any additional because it makes this writing even longer.

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

Be aware:In the event you run DMVs then you definitely don’t get Unix Epoch values. You certainly get good and tidy date/time values.

Encrypted Knowledge Supply

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

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

As soon as once more, if you understand how to do 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. Nonetheless, there’s a house for the instrument to rapidly undergo the “metadata.sqlitedb” file and get a number of insights about your SSAS Tabular mannequin.

I made the instrument 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