Please Lend Me Your Vote for Documentation of TMSCHEMA DMVs

I spent a good bit of time looking for the definitions/descriptions of the TMSCHEMA DMVs that allow us to view metadata and monitor the health of SSAS 2016 tabular models. As far as I can tell there are no details about them on any Microsoft site. Many of the columns are obvious, but there are a few fields that show IDs rather than descriptions (e.g., ExplicitDataType in TMSCHEMA_COLUMNS, Type in TMSCHEMA_DATA_SOURCES). It would be great to get the DMVs documented similar to the MDSCHEMA DMVs as they are quite useful for tasks like documenting your tabular model.  Since the TMSCHEMA DMVs work in Azure Analysis Services as well, I have logged this request on the Azure AS User Voice for that. Please lend me a vote so we can make this information more easily available.

How can we improve Microsoft Azure Analysis Services?

  • 1 vote
  • 0 comments

Document TMSCHEMA DMVs

The DMVs for SSAS Tabular (Azure and SQL 2016) are not documented anywhere. While the meaning of many of the fields is obvious, there are a few that are just IDs for which it would be nice to see all possible values and descriptions. It would make sense to add the definitions here: https://msdn.m…

Update On My PASS Summit Feedback

Back in June, I posted the feedback I received on the abstracts I submitted to PASS Summit 2016. I wasn’t originally selected to speak, but I did have one talk that was selected as an alternate. It turns out that a couple of speakers had to cancel , and I am now speaking at PASS Summit.  PASS contacted me two weeks ago to ask if I could fill in. Luckily, my session that was chosen as an alternate was one that I give frequently at SQL Saturdays so it was feasible to get it ready to present at PASS Summit.

I am excited and honored to be speaking at PASS Summit, but I’m not stressed about it. Part of it is that I’ve only had two weeks to stress, and part of it is that I have quite a bit of experience speaking and I feel confident that I can deliver a decent (if not downright good) session. I’m still updating my presentation and practicing the delivery in preparation for this week, but I feel comfortable with the process of preparing. I’ve spoken at PASS Summit and many other conferences and user groups over the last five years. Over that time I have developed a process for preparing to speak, which includes building and editing the presentation as well as some disaster recovery measures. Once I have checked all the boxes I can feel that I have sufficiently prepared. It doesn’t mean everything will go perfectly, but it helps make sure I haven’t forgotten anything.

There are a couple of things I hope you take away from this.

  1. If you are selected as an alternate, it is a real possibility that you will be added to the schedule. This worked out nicely for me because I was already scheduled to be at PASS Summit and my presentation was already in a fairly finished state. You will definitely never be chosen if you never submit. So if you are interested, throw your hat in the ring next year.
  2. Giving a good presentation (and feeling confident about it) takes practice. If you are just beginning your speaking efforts in the SQL community and you are nervous about it, know that it gets easier (for most people) as you gain more experience. If you enjoy it, keep putting yourself out there. Speaking is something I thoroughly enjoy these days, and I have (mostly) conquered the nerves. Some of that is because I have experienced failures in presentations and the world did not end. Some of that is the result of my presentation prep checklist, which helps me feel prepared.

The downside of the last-minute addition is that I haven’t had as much time to market my session. So if you like Power BI, data visualization, or Kansas City BBQ, and you are attending PASS Summit, I invite you to join me on Friday morning at 8 am in room 3AB. I’m discussing how to apply good data visualization practices in Power BI using data on BBQ restaurants.

Here’s the description for the session I’m presenting:

Overcoming Chartaphobia with Power BI

Do reports in your organization consist mostly of giant tables of data? Perhaps you have gotten as far as adding KPIs or conditional highlighting to the tables. Maybe you have charts, but they are hideous and distracting. Although tables of data presented as reports are commonly found in many organizations, they may be doing you and your users a disservice. We’ll discuss why cognitive psychological studies tell us that graphs and pictures are more effective at communicating trends and comparisons and how to prepare to create good data visualizations. Then we’ll explore how to employ purposeful data viz designs to help users achieve their goal of making informed decisions, using a fun and useful Power BI dashboard. You’ll leave with guidance on how to take boring or unreadable tables of data and turn them into useful and visually appealing reports. 

I hope to see you in my session on Friday!

Process Compatibility Level 1200 SSAS Tabular Model from SSIS 2014

A client wanted to upgrade their SSAS model to SSAS 2016 to take advantage of some of the features of the new level 1200 compatibility model. But they weren’t yet ready to upgrade their SSIS server from SQL 2014. This presented a problem because they had been using the Analysis Services Processing Task to process their tabular model nightly. This processing task in SSIS 2014 uses the old Analysis Management Objects, which aren’t compatible with the new SSAS tabular models.

Attempting to use the AS Processing Task results in the following error: “[Analysis Services Execute DDL Task] Error: This command cannot be executed on database ‘MySSASDB’ because it has been defined with StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database”

The reason for keeping SSAS processing in an SSIS package was because it kept consistent logging throughout their data refresh process. So we set out to find another solution.

The new SSAS Tabular models use Tabular Model Scripting Language (JSON) rather than XMLA. A simple process full command in TMSL might look like:

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "MySSASDB"
      }
    ]
  }
}

Just pasting the JSON to process the model into an Analysis Services Execute DDL Task didn’t work. It returned the error “DDL is not valid”.

I asked around and received a couple of solutions that worked.

Option 1 (my less preferred option): Create an OLEDB connection manager (rather than MSOLAP) and use an Execute SQL Task.

You can set up an OLE DB connection that looks like the below.  Create a new connection manager and choose the OLE DB connection manager type. Change the provider to Microsoft OLE DB Provider for Analysis Service 13.0 and fill in your connection information.

ms-oledb-provider-for-as-conmgr

You’ll see the connection manager show up in the Connection Managers pane looking like this:

ssas-oledb-prov

Drag in an Execute SQL task, use the previously defined connection manager and paste in the JSON. This works just fine, but feels a bit too much like a workaround rather than a solid solution to me.

Option #2: Use an Analysis Services Execute DDL task and wrap the JSON in XMLA

Drag in an Analysis Services Execute DDL task. Create a new connection manager by choosing New Analysis Services Connection.

ssis-new-ssas-con

Edit your connection information, click OK, and you will end up with a connection manager that looks like this:

ssis-ssas-con-mgr

Use that connection manager in the AS Execute DDL task. We can use the same JSON from earlier and wrap it in XMLA as shown below.

<Statement xmlns="urn:schemas-microsoft-com:xml-analysis">
{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "MySSASDB"
      }
    ]
  }
}

</Statement>

The XMLA/JSON command can be a direct source statement or placed in a variable and referenced from the task.

To test that the model is successfully processed, you can execute the SSIS task or package and then run the following query against the DMV.

Select [catalog_name], [date_modified], [compatibility_level] 
from $SYSTEM.DBSCHEMA_CATALOGS where [Catalog_Name] = 'MySSASDB'

So if you are caught between versions in SSIS and SSAS, do not despair. You can still process your new SSAS Tabular model from an SSIS package.

Documenting your Tabular or Power BI Model

If you were used to documenting your SSAS model using the MDSchema rowsets, you might have noticed that some of them do not work with the new tabular models. For example, the MDSCHEMA_MEASUREGROUP_DIMENSIONS DMV seems to just return one row per table rather than a list of the relationships between tables.

Not to worry, though. With the new compatibility level 1200 SSAS Tabular models came some new DMVs. They are, unfortunately, not documented at this time (as of 3 Oct 2016). I expect them to show up here at some point in the future. Until then I published a Gist that provides a list of those views as well as some notes on what kind of useful info is in each one. While there are 36 TMSCHEMA DMVs, the main DMVs you will want to check out for model documentation purposes are:

  • TMSCHEMA_COLUMNS
  • TMSCHEMA_MEASURES
  • TMSCHEMA_MODEL
  • TMSCHEMA_RELATIONSHIPS
  • TMSCHEMA_TABLES

There are a few fields that show IDs rather than descriptions (e.g., ExplicitDataType in TMSCHEMA_COLUMNS, Type in TMSCHEMA_DATA_SOURCES). I’ve been able to figure out the descriptions that correspond to some of the values, but I’m still missing a few.

As with the MDSCHEMA DMVs, the TMSCHEMA DMVs can be used to document your model. This also works with Power BI models. You’ll just need to change the connection information to your Power BI instnce while the .pbix file is open in Power BI Desktop. For more information analyzing your Power BI model, see Chris Webb’s post. Since DMVs require DMX queries, you are somewhat limited in your ability to join the data from the DMVs together. This makes it necessary to store the data somewhere before transforming and merging them. Since I needed to document a tabular model for a client, I created a Power Pivot model in Excel 2016. You can download my documentation model here.  My example model was connected to a quick demo SSAS model I made based upon the Wide World Importers DW database.

My Power Pivot Model

My Power Pivot model uses Power Query to import the data from the DMVs. This enables me to merge data into a model that I feel makes the most sense and doesn’t suffer from too many relationships and hidden tables (which is what would happen if you just imported the results of each DMV query into the model). In order to make this work for any model, I employed a trick I learned from Chris Webb to avoid duplicating connection information across queries. I built my model with the normal embedded connection strings and then converted them to use the connection Connection Info tables. As I did that, I ran into a Power Query limitation as evidenced by the error message below.

“Formula.Firewall: Query ‘QueryName’ (step ‘StepName’) references other queries or steps and so may not directly access a data source. Please rebuild this data combination.”

Ken Puls has a great explanation of how to get around this issue. Basically, you have to redesign your queries so that you have “staging tables”, which you can then use to build finalized tables. This redesign gets you around the error since the finalized tables don’t have the embedded source connection, just a reference to the results from another query.

This Excel file documents:

  • Database and model info
  • Tables and source queries
  • Columns
  • Hierarchies
  • Measures
  • KPIs
  • Security roles, membership, and permissions
  • Relationships
  • Perspectives and the columns, hierarchies, and measures they contain

The Database Info tab uses cube functions to display the values shown. The rest of the tabs use pivot tables and slicers.

You can download and use this model if you have Excel 2016 or 2013 with Power Query. Just download the file, change the values in the TabularInstanceName and TabularDBName queries, and refresh. I was able to open the file and update the queries with no issues and no changes in Excel 2013.

This is great if you just need documentation or a quick way to look up specific information. But we could really analyze this using visualizations other than tables in Power BI Desktop.

My Power BI Model

I imported the Excel model into Power BI, made a few adjustments, and then added some visualizations. This report is a bit paired down from a visualization standpoint compared to the Excel file because I eliminated some of the data that wasn’t analytically relevant for me.

Click here to view this report in Power BI.

On the first page, I have several summary numbers that tell me about the contents of the model. It gives me a decent idea of the size and complexity of the model.

tabular-doc-summary

The second page uses a bar chart as an interactive filter against a list of table source queries and columns.

The third page shows relationships between the tables. Slicers on the left filter both the force directed graph and the table. Smart filters at the top of the page let you filter the Table and To Table in the relationships.

tabular-doc-relationshipsThe fourth page shows the details behind each measure. A column chart at the bottom provides a count of measures by table and whether the measure is hidden, which acts as another interactive filter.

The last page shows security by role and table, again using column charts as interactive filters.

You can download the Power BI model here.

Update: Check out the Demo Day video I made on the BlueGranite blog for further explanation and a demo of the Tabular Model documentation. 

BYO Time Zone Conversion With SSAS DMVs

This is just a quick note (since I apparently forgot and was puzzled for a moment) that the MDSCHEMA_CUBES DMV shows you LAST_DATA_UPDATE (last processed date) in UTC, regardless of the timezone of your SSAS Server. Marco Russo has a great post about all the ways you can get your SSAS model’s last processed date, which does note that the DMV returns the datetime in UTC.

You can get your SSAS models’ last processed time by running this query (replacing MySSASDBName with the name of your SSAS database):

SELECT TOP 1 [CATALOG_NAME], [LAST_DATA_UPDATE]
FROM $System.MDSCHEMA_CUBES
WHERE [CATALOG_NAME] = 'MySSASDBName'

The datetime returned by this query is in UTC. My query returns 9/19/2016 7:43:03 PM.

If I go into the properties of my SSAS database, I can see this same info, but the timezone conversion has already been done for me (this server is in Central time zone).

ssas-properties

So if you have ever gotten results that seem to be in the future or wondered why these two times do not match, it’s probably due to time zone conversion.

DAX Date Dimension and Fun with Date Math

I was working on a SSAS Tabular 2016 solution for a project for which I had no data (an empty data model, but no data). I was not in control of the source data warehouse, so I couldn’t change what I had, but I needed to get started. So I went about creating a date dimension with a fiscal calendar entirely in DAX. There are several scripts out there to create a date dimension. Although there are many nice alternatives for Power BI that are built in M or a combination of M and DAX, I haven’t seen many full date dimensions with a fiscal calendar just using DAX.

I’ll explain some of the DAX calculations below, but I put the TMSL/JSON in Gist so you can easily paste into a SSAS 2016 Tabular project. The JSON has the added benefit of retaining data types, formatting, hidden, and sort by properties.

The fiscal calendar assumes calendar months with the fiscal calendar shifted by some number of months. It also assumes that the fiscal year is ahead of the calendar year. That is, fiscal year 2017 starts in the first day of some month in 2016. In my example script, the fiscal year starts in October. If you have some 4-4-5, 4-5-4, or other calendar, the fiscal calendar calculations in this script won’t help you.

To add this date dimension to your SSAS Tabular project:

  1. Right click on the Model.bim file in the Solution Explorer and choose View Code.
  2. If you have at least one table in the model already, locate the end of the definition of the previous table, inside the table array. Add a comma after the end curly brace for the previous table.
  3. Paste the JSON/TMSL from my Gist.
  4. Save and close the file.
  5. Right click on Model.Bim and choose View Designer.

TMSL

To adjust the date range included in the Date table, locate the [Date] field. The current calculation is:

=CALENDAR(DATE(2010,1,1),DATE(2025,12,31))

The current date range goes from 1 Jan 2010 through 31 Dec 2025.

To adjust the start of the fiscal year, change the expression in the [Fiscal Year Month Begin] field. The current calculation is

=10

This field represents the calendar month number in which the fiscal year begins. If your fiscal calendar begins in July, you’ll want to change the 10 to 7.

You shouldn’t have to adjust anything else in the date dimension to get correct values.

Calculation Explanations

I used the FORMAT() function to get a lot of the calendar fields as well as the [DateKey] field.

DateKey = VALUE(FORMAT([Date],"YYYYMMDD"))

Month Year Abbrev = FORMAT([Date],"mmm YYYY")

Month Name = FORMAT([Date],"mmmm")

Day of Week = FORMAT([Date],"dddd")

Qtr Nbr = VALUE(FORMAT([Date],"Q"))

Many of the fiscal calendar fields use the EDATE() to shift date calculations to the correct month. The [Fiscal Year Nbr] field uses the [Fiscal Year Month Begin] field to ensure the correct year is shown.

Month Shift = 13 - [Fiscal Year Month Begin]

Fiscal Month Nbr = MONTH(EDATE([Date],[Month Shift]))

Fiscal Quarter Nbr = ROUNDUP(MONTH(EDATE([Date],[Month Shift]))/3,0)

Fiscal Year Nbr = IF([Month Nbr] >= VALUE(FORMAT(Date([Year],
[Fiscal Year Month Begin],1),"M")), [Year]+1, [Year])

I also like to include the beginning and end dates for month, quarter, and year.

Begin of Month = DATE([Year],[Month Nbr],1)

End of Month = EOMONTH([Date],0)

Begin of Quarter = IF([Month Nbr]<4,DATE([Year],1,1),
IF([Month Nbr]<7,DATE([Year],4,1),
IF([Month Nbr]<10,DATE([Year],7,1),
DATE([Year],10,1))))

End of Quarter = IF([Month Nbr]<4,DATE([Year],3,31),
IF([Month Nbr]<7,DATE([Year],6,30),
IF([Month Nbr]<10,DATE([Year],9,30),
DATE([Year],12,31))))

Begin of Year = DATE([Year],1,1)

End of Year = DATE([Year],12,31)

DAX has a nice EOMONTH() function to get the end of the month now. There are multiple ways to get end of quarter. I used an if statement. You could also use SWITCH(). I also included begin and end dates for the fiscal quarter and year (end of month is the same since my calendar assumes fiscal months are calendar months).

Begin of Fiscal Year = DATE([Fiscal Year Nbr]-1,[Fiscal Year Month Begin],1)

End of Fiscal Year = EOMONTH(
Date([Fiscal Year Nbr],[Fiscal Year Month Begin],1),-1)

Begin of Fiscal Quarter = IF([Fiscal Quarter Nbr]=1,[Begin of Fiscal Year],
IF([Fiscal Quarter Nbr] = 2, EDATE([Begin of Fiscal Year],3),
IF([Fiscal Quarter Nbr] = 3, EDATE([Begin of Fiscal Year],6),
EDATE([Begin of Fiscal Year],9))))

End of Fiscal Quarter = IF([Fiscal Quarter Nbr]=1,
EDATE([Begin of Fiscal Year],3)-1,
IF([Fiscal Quarter Nbr] = 2, EDATE([Begin of Fiscal Year],6)-1,
IF([Fiscal Quarter Nbr] = 3, EDATE([Begin of Fiscal Year],9)-1,
EDATE([Begin of Fiscal Year],12)-1)))

The EOMONTH() function allows you to shift forward or backward from the current selected month. For the end of the fiscal year, I went to the beginning of the next fiscal year, subtracted a month, and then found the end of that month. The end of fiscal quarter calculation follows similar logic get the month that begins the next quarter and subtract one day.

 

Create a Date Dimension in Azure SQL Data Warehouse

Most data warehouses and data marts require a date dimension or calendar table. Those of us that have been building data warehouses in SQL Server for a while have collected our favorite scripts to build out a date dimension. For a standard date dimension, I am a fan of Aaron  Bertrand’s script posted on MSSQLTips.com. But the current version (as of Aug 8, 2016) of Azure SQL Data Warehouse doesn’t support computed columns, which are used in Aaron’s script.

I adapted Aaron’s script to work in Azure SQL Data Warehouse and am sharing it with you below, so you don’t have to do the same. I did leave out holidays because I didn’t need them in my calendar table.  To add them back in, just add the column back to the table and use Aaron’s update statements. I also changed the MonthYear field to include a space between the month and year. Otherwise, my script should produce the same results as Aaron’s in a Azure SQL DW friendly way. Notice that I chose to use Round Robin distribution for my date table. Also, tables are now created with clustered columnstore indexes by default in Azure SQL DW.

Instead of the computed columns, I inserted the dates, then updated the other columns in the temporary table. Then I used the Create Table As Select syntax to create my final RPT.Calendar table.

Happy time trending!