Microsoft Technologies, Power BI, SSAS

The Tabular Model Documenter is now a Power BI Template

A while back I created the Tabular Model Documenter Power BI model that can connect to your SSAS Tabular or Power BI model and display metadata about the model to help you see relationships, calculations, source queries, and more.  I had been meaning to turn it into a parameterized template since templates became available and just finally got around to it.

You can now download the PBIT file here. Note: This works for SSAS 2016/compatibility level 1200, but may need some adjustments for Azure AS and SSAS 2017.

When you open it, you’ll need to fill in the instance and database name, then allow some native queries to run. Then you will have your tabular model documented for you without a lot of manual effort.

tabular-doc-relationships

For more info on how to use the Tabular Model Documenter, see the original post.

Azure, Microsoft Technologies, Power BI, SSAS

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.

https://feedback.azure.com/forums/556165-azure-analysis-services/suggestions/16981015-document-tmschema-dmvs

Microsoft Technologies, SSAS, SSIS

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.

Microsoft Technologies, Power BI, Power Pivot, Power Query, SSAS

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.

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

Update 19 Sep 2017: I have turned the Power BI report into a Power BI Template for easier use. You can download it here

Microsoft Technologies, SSAS

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, Microsoft Technologies, SSAS

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.


{
"name": "Date",
"dataCategory": "Time",
"columns": [
{
"type": "calculatedTableColumn",
"name": "Date",
"dataType": "dateTime",
"isNameInferred": true,
"isDataTypeInferred": true,
"isKey": true,
"sourceColumn": "[Date]",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Month Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "month([Date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "DateKey",
"dataType": "int64",
"isDataTypeInferred": false,
"expression": "VALUE(FORMAT([Date],\"YYYYMMDD\"))",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Year",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "YEAR([Date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "YearMonthNum",
"dataType": "string",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "format([Date],\"YYYYMM\")"
},
{
"type": "calculated",
"name": "Month Year Abbrev",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "format([Date],\"mmm YYYY\")",
"sortByColumn": "YearMonthNum"
},
{
"type": "calculated",
"name": "Month Name",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "format([Date],\"mmmm\")",
"sortByColumn": "Month Nbr"
},
{
"type": "calculated",
"name": "Month Abbrev",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "Format([Date],\"mmm\")",
"sortByColumn": "Month Nbr"
},
{
"type": "calculated",
"name": "Day Of Week Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "Weekday([Date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Day Of Week",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "Format([date],\"dddd\")",
"sortByColumn": "Day Of Week Nbr"
},
{
"type": "calculated",
"name": "Weekday Abbrev",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "left([Day Of Week],3)",
"sortByColumn": "Day Of Week Nbr"
},
{
"type": "calculated",
"name": "Quarter Nbr",
"dataType": "double",
"isDataTypeInferred": true,
"expression": "VALUE(format([date],\"Q\"))",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Quarter",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"Q\" & [Quarter Nbr]",
"sortByColumn": "Quarter Nbr"
},
{
"type": "calculated",
"name": "Quarter Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "[Quarter] & \" \" & [Year]",
"sortByColumn": "YrQtrSort"
},
{
"type": "calculated",
"name": "YrQtrSort",
"dataType": "double",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "[year]*10+[Quarter Nbr]"
},
{
"type": "calculated",
"name": "Day of Month",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "day([date])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Month Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "month(edate([date],[Month Shift]))",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Month",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"FM \" & [Fiscal Month Nbr]",
"sortByColumn": "Fiscal Month Nbr"
},
{
"type": "calculated",
"name": "Fiscal Year Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": " if( [Month Nbr] >= VALUE(format(Date([Year],[Fiscal Year Month Begin],1),\"M\")), [Year]+1, [Year])",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"FY \" & [Fiscal Year Nbr]",
"sortByColumn": "Fiscal Year Nbr"
},
{
"type": "calculated",
"name": "FiscalMonthSort",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": " [Fiscal Year Nbr]*100+[Fiscal Month Nbr]"
},
{
"type": "calculated",
"name": "Fiscal Month Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "[Month Abbrev] & \" \" & [Fiscal Year Nbr]",
"sortByColumn": "FiscalMonthSort"
},
{
"type": "calculated",
"name": "Fiscal Quarter Nbr",
"dataType": "double",
"isDataTypeInferred": true,
"expression": "ROUNDUP(MONTH(EDATE([Date],[Month Shift]))/3,0)",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Fiscal Quarter",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"Q\" & [Fiscal Quarter Nbr]",
"sortByColumn": "Fiscal Quarter Nbr"
},
{
"type": "calculated",
"name": "Fiscal Quarter Year",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "[Fiscal Quarter] & \" \" & [Fiscal Year Nbr]",
"sortByColumn": "Fiscal Quarter Sort"
},
{
"type": "calculated",
"name": "End Of Month",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "EOMONTH([date],0)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "End Of Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "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))))",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "End of Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Year],12,31)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "End of Fiscal Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "EOMONTH(Date([Fiscal Year Nbr],[Fiscal Year Month Begin],1),-1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Fiscal Quarter Sort",
"dataType": "double",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "[year]*10+[Fiscal Quarter Nbr]"
},
{
"type": "calculated",
"name": "Calendar Week Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "WEEKNUM([Date],1)",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Begin of Month",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Year],[Month Nbr],1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "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))))",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Year],1,1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Fiscal Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": [
"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))))"
],
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Begin of Fiscal Year",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": "date([Fiscal Year Nbr]-1,[Fiscal Year Month Begin],1)",
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
},
{
"type": "calculated",
"name": "Fiscal Week Nbr",
"dataType": "int64",
"isDataTypeInferred": true,
"expression": "INT(([Date]-Weekday([Date]+1)-Date('Date'[Year]-([Month Nbr]<[Fiscal Year Month Begin]),[Fiscal Year Month Begin],1))/7)+2",
"formatString": "0",
"summarizeBy": "none"
},
{
"type": "calculated",
"name": "Calendar Week",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"W\"&[Calendar Week Nbr]",
"sortByColumn": "Calendar Week Nbr"
},
{
"type": "calculated",
"name": "Fiscal Week",
"dataType": "string",
"isDataTypeInferred": true,
"expression": "\"FW\"&[Fiscal Week Nbr]",
"sortByColumn": "Fiscal Week Nbr"
},
{
"type": "calculated",
"name": "Month Shift",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "13 – [Fiscal Year Month Begin]"
},
{
"type": "calculated",
"name": "Fiscal Year Month Begin",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "10"
},
{
"type": "calculated",
"name": "MonthDiff",
"dataType": "int64",
"isDataTypeInferred": true,
"isHidden": true,
"expression": "DateDiff([Begin of Fiscal Year],[Date],MONTH)"
},
{
"type": "calculated",
"name": "End of Fiscal Quarter",
"dataType": "dateTime",
"isDataTypeInferred": true,
"expression": [
"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)))",
""
],
"formatString": "Short Date",
"annotations": [
{
"name": "Format",
"value": "<Format Format=\"DateTimeShortDatePattern\" />"
}
]
}
],
"partitions": [
{
"name": "CalculatedTable 1",
"source": {
"type": "calculated",
"expression": "Calendar(DATE(2010,1,1),DATE(2025,12,31))"
}
}
]
}

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.

 

DAX, Microsoft Technologies, SSAS, Uncategorized

Trekking through the DAX Jungle In Search of Lost Customers

I like to think I’m proficient at writing DAX and building SSAS tabular models. I enjoy a good challenge and appreciate requirements that cause me to stretch and learn. But sometimes I hit a point where I realize I must go for help because I’m not going to complete this challenge in a timely manner on my own.  I think this is part of how most IT professionals operate.

This, but replace adultier adult with DAX-ier (more knowledgeable?) BI Consultant

Luckily, I am part of a great SQL Server community, and I also work with some really smart people.

I was testing some calculations in my tabular model when I noticed that my Lost Customers calculation wasn’t working as expected. This was rather interesting to me since the calculation I was using was from DAXPatterns.com.  After some experimentation, I determined that the difference between my data situation, and the situation that fit the DAX Pattern was that my customer dimension was a Type 2 Slowly Changing Dimension. That meant I couldn’t use the customer key to identify a unique customer, since each customer could have multiple customer keys  (surrogate keys) with the same customer number (business key). Specifically, if a customer made a purchase in December 2015, then changed their name, then made a purchase in January and February, my calculation was counting them as lost because it was not recognizing that the customer with the new name and same customer number was actually the same customer.

My original calculation that didn't properly handle Type 2 SCDs:
 Lost Customers :=
 IF (
     NOT (
         MIN ( 'Date'[Full Date] )
             CALCULATE ( MAX ( Sales[Invoice Date] )ALL ( Sales ) )
     ),
     COUNTROWS (
         FILTER (
             ADDCOLUMNS (
                 FILTER (
                     CALCULATETABLE (
                         ADDCOLUMNS (
                             VALUES ( Sales[CustomerNo] ),
                             "CustomerLostDate"                             CALCULATE ( MAX ( Sales[Invoice Date] ) )
                             + [Lost Days Limit]
                         ),
                         FILTER (
                             ALL ( 'Date' ),
                             AND (
                                 'Date'[Full Date] < MIN ( 'Date'[Full Date] ),
                                 'Date'[Full Date]
                                     >= MIN ( 'Date'[Full Date] ) 
                                         - [Lost Days Limit]
                             )
                         )
                     ),
                    AND (
                         AND (
                             [CustomerLostDate] >= MIN ( 'Date'[Full Date] ),
                             [CustomerLostDate] <= MAX ( 'Date'[Full Date] )
                         ),
                      [CustomerLostDate] <= 
                       CALCULATE (MAX ( Sales[Invoice Date] )ALL ( Sales ) )
                     )
                 ),
                 "FirstBuyInPeriod"CALCULATE ( MIN ( Sales[Invoice Date] ) )
             ),
             OR ( 
                 ISBLANK ( [FirstBuyInPeriod] ), 
                 [FirstBuyInPeriod] > [CustomerLostDate] 
              )
         )
     )
 )

Having put forth a good effort on my own and not wanting to go down the rabbit hole for several days, I decided to post my issue to the Power BI forums where Marco Russo (one of the authors of DAXPatterns.com) was able to help me.
Based upon his suggestions, my final calculation ended up as shown below. Only the bolded part (the innermost ADDCOLUMNS function) was changed

Lost Customers :=
 IF (
     NOT (
         MIN ( 'Date'[Full Date] )
             CALCULATE ( MAX ( Sales[Invoice Date] )ALL ( Sales ) )
     ),
     COUNTROWS (
         FILTER (
             ADDCOLUMNS (
                 FILTER (
                     CALCULATETABLE (
                         ADDCOLUMNS (
                              CALCULATETABLE ( 
                                 VALUES ( Customer[Customer No] ), Sales ),
                                 "CustomerLostDate"CALCULATE (
                                     MAX ( Sales[Invoice Date] ),
                                     ALLEXCEPT ( Customer, Customer[Customer No] )
                              )
                                  + [Lost Days Limit]
                          ),
                         FILTER (
                             ALL ( 'Date' ),
                             AND (
                                 'Date'[Full Date] < MIN ( 'Date'[Full Date] ),
                                 'Date'[Full Date] >= 
                                     MIN ( 'Date'[Full Date] ) - [Lost Days Limit]
                             )
                         )
                     ),
                     AND (
                         AND (
                             [CustomerLostDate] >= MIN ( 'Date'[Full Date] ),
                             [CustomerLostDate] <= MAX ( 'Date'[Full Date] )
                         ),
                         [CustomerLostDate] <=  
                            CALCULATE ( MAX ( Sales[Invoice Date] )ALL ( Sales )                            )
                     )
                 ),
                 "FirstBuyInPeriod"CALCULATE ( MIN ( Sales[Invoice Date] ) )
             ),
             OR ( 
                ISBLANK ( [FirstBuyInPeriod] ), 
                [FirstBuyInPeriod] > [CustomerLostDate] 
             )
         )
     )
 )

In more human-friendly terms, I think this calculation does the following:

  • Create a table of customer numbers that have sales, along with their customer lost date, which is defined as the max invoice date for that customer number after removing all context filters on the customer table except for customer number + the number returned by Lost Days Limit (in my case, 60)
  • Filter the dates on the calculated table such that the [min invoice date minus 60] is less than the selected date which is less than and the minimum invoice date
  • Filter the dates on the calculated table such that the customer lost date is between the minimum invoice date and the maximum invoice date selected and is less than the max invoice date for all sales.
  • Add a column called FirstBuyInPeriod that contains the min invoice date for the selected dates
  • Filter the calculated table such that the minimum invoice date in the selected dates is blank or is greater than the customer lost date

This worked fine and gave me the answer I was looking for, but was (as noted on the DAX Patterns page) rather computing-intensive.

My coworker, Javier Guillen offered a great alternative to Marco’s pattern.

First Date Selected :=
 FIRSTDATE ( DATEADD ( 'Date'[Full Date], 0MONTH ) )
Last Date Selected :=
 LASTDATE ( DATEADD ( 'Date'[Full Date], 0MONTH ) )
Customer Lost Date :=
 CALCULATE (
     MAX ( Sales[Invoice Date] ),
     CALCULATETABLE (
         Sales,
         FILTER (
             ALL ( Customer ),
             Customer[Customer No] = MAX ( Customer[Customer No] )
         ),
         FILTER ( ALL ( 'Date' ), 'Date'[Full Date] <= MAX ( 'Date'[Full Date] ) )
     )
 )
     + [Lost Days Limit]
Lost Customer :=
 SUMX (
     VALUES ( Customer[Customer No] ),
     IF (
         [Customer Lost Date] >= [First Date Selected]
             && [Customer Lost Date] <= [Last Date Selected],
         1
     )
 )

These calculations do the following:

  • First Date Selected: Calculate the minimum date of the dates selected
  • Last Date Selected: Calculate the maximum date of the dates selected
  • Customer Lost Date: Create a table based upon the Sales table that returns the max invoice date + [Lost Days Limit]  with a filter on customer to get the max customer number for each customer record (this is how we alleviate the type 2 SCD issue) and a filter on dates to get dates less than the max selected date
  • Lost Customer: For each customer number, count 1 if the customer lost date is between First Date Selected and Last Date Selected, otherwise count 0. Return the sum.

Javier’s suggestion is less resource intensive and returns results more quickly. But it requires that the date range you choose be contiguous. This means that I can’t create a pivot table and choose Jan and February of 2015 and January and February of 2016 and expect to get a correct answer. That is kind of an edge case situation that might not even be an issue, but I already had a situation for my project where YOY analysis of customer counts was needed, so I opted to go with Marco’s suggestion for now. If you don’t have this use case, I think Javier’s calculations are much easier to understand and implement. You could probably put some conditions around it to blank out if the dates are not contiguous and it would be ready to go.

It was cool to find something that wasn’t already covered by a common DAX pattern and wasn’t frequently blogged about, and I’m grateful for the insight from the solutions offered by both Marco and Javier.

DAX, Microsoft Technologies, Power BI, SSAS, Uncategorized

Creating a Matrix in Power BI With Multiple Values on Rows

This week I was asked to create a matrix in a Power BI report that looks like this:

Matrix with Values on Rows
Matrix with Values on Rows (numbers faked to protect the innocent)

To my surprise, Power BI only lets you put multiple values on columns in a matrix. You can’t stack metrics vertically. Note: this is true as of 8 Jan 2016 but may change in the future. If you agree that this should be a feature in Power BI, please make your voice heard and vote for this idea on the Power BI forum and encourage others to vote for it as well.

Since I needed to finish this report, I got a little creative with a disconnected table and some DAX to implement a workaround in my tabular model.

The Disconnected Table

I created a table called Row Order in my tabular model using the following SQL Query as a source.

select 1 as [Row Order], 'Lost Customers' as [Customer Metric]
union all
select 2 as [Row Order], 'Inactive Customers' as [Customer Metric]
union all
select 3 as [Row Order], 'New Customers' as [Customer Metric]

I set the Sort By Column property on the Customer Metric column to Row Order and hid the Row Order column.

Calculated Measures

I already had the base metrics for my matrix:

  • Lost Customers – Week (customers considered lost in the current week, based upon the selected date)
  • Lost Customers – Month (customers considered lost in the current month, based upon the selected date)
  • Lost Customers – Week (customers considered lost in the current month, based upon the selected date)
  • Inactive Customers – Week (customers considered to have moved to inactive status in the current week, based upon the selected date)
  • Inactive Customers – Month (customers considered to have moved to inactive status in the current month, based upon the selected date)
  • Inactive Customers – Quarter (customers considered to have moved to inactive status in the current quarter, based upon the selected date)
  • New Customers – Week (customers who have made their first purchase in the current week, based upon the selected date)
  • New Customers – Month (customers who have made their first purchase in the current month, based upon the selected date)
  • New Customers – Quarter (customers who have made their first purchase in the current quarter, based upon the selected date)

As a side note, when creating time-based measures I prefer to create the measure such that it accepts a date and then filters off of that rather than assuming everyone wants to see the current week/month/quarter. I then add an Is Today field on my date table so if someone does want to see the current week they easily select that in a filter on this field. This means that I can see lost customers for any week, not just the current. Maybe I want to filter my dashboard to the end of December. If I had just created a measure that assumed I wanted last month, I wouldn’t be able to just change my date filter and get the data I want.  As another example, lots of people create a metric to calculate sales in the last 6 weeks. But many people assume they want the last 6 weeks from today rather than allowing the user to choose a date and then calculating sales in the 6 weeks leading up to that day. I much prefer the more flexible calculation.

But having this flexibility in my calculations meant that I couldn’t just put some calculated column for time period in my date dimension. And even if I had made the assumption that I wanted to see the data as of today, it would probably be a separate table or a many-to-many relationship (e.g., 5 Jan 2016 would be in the last week, month, and year).

With my new disconnected table added, I just needed to add a few more measures. First, I needed something to check which customer metric I should be showing:
RowOrder:=Max('Row Order'[Row Order])

I hid the RowOrder measure. Then I created one measure for each time frame (week/month/quarter) that would select the correct measure based upon the Customer Metric on that row.
Weekly Customer:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Week], 2,[Inactive Customers - Week], 3,[New Customers - Week],BLANK()), BLANK())
Monthly Customers:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Month], 2,[Inactive Customers - Month], 3,[New Customers - Month],BLANK()), BLANK())
Quarterly Customers:=IF(HASONEVALUE(Row Order[Row Order]), SWITCH([RowOrder],1,[Lost Customers - Quarter], 2,[Inactive Customers - Quarter], 3,[New Customers - Quarter],BLANK()), BLANK())

Power BI Matrix

I opened my report on Power BI.com and created my matrix.

I put Customer Metric in Rows and Weekly Customers, Monthly Customers, and Quarter Customers in Values. Then I filtered my table to As of Today = “As Of Today”.

PBI Matrix
Fields List for Power BI Matrix

That gives me the matrix I wanted to create. I don’t love that I had to add the extra table, but it works for now until I can achieve this natively with Power BI.

Power BI Matrix
Power BI Matrix using disconnected table and calculated measures (numbers changed to protect the innocent)

 

BIDS Helper, SSAS

Webucator Made a Video of My Blog Post

Back in July, I wrote a blog post about My Favorite BIDS Helper features for SSAS development. Webucator contacted me about creating a video based upon it, and it’s now available.  They are doing a free series called SQL Server Solutions from the Web where they highlight different SQL Server solutions found on blog posts around the web, and I’m pleased and honored that my blog is included.

Webucator provides technical and business training via private classes for your organization, public classes in a live virtual environment, and self-paced online programs. They are a Microsoft Certified Partner for Learning Solutions, and they offer several classes on SQL Server, including SSAS, SSIS, and SSRS.

Without further ado, here’s the video for My Favorite BIDS Helper Features for SSAS Development:

You can check out another blog post that Webucator made into a video here. The post by Koen Verbeeck is a great explanation of why it’s important to be aware of the defaults in T-SQL windowing functions.

BIDS Helper, Microsoft Technologies, SSAS

My Favorite BIDS Helper Features for SSAS Development

Bill Fellows and I presented Somebody Got BIDS Helper in My Data Tools at Mile High Tech Con in Denver last weekend, and it reminded me how much I love BIDS Helper.  I use it to develop all of my SSIS and SSAS projects, but I realized I haven’t blogged much about it.  So here are my favorite BIDS Helper features for SSAS development. If you haven’t tried BIDS Helper yet, I highly recommend it.  It’s free and there is great documentation for it, so it’s easy to get started.

Printer Friendly Dimension Usage for SSAS Multidimensional

Printer Friendly Dimension Usage provides you with a nice Bus Matrix for your documentation or design reviews. You can choose between a summarized view and a detailed view.  When I have clients that I know will do a lot of ad hoc reporting in Excel using their cube, I like to create an Excel workbook with a data glossary, summary bus matrix, and a few pre-populated pivot tables that can be used to answer the most common questions.  With BIDS Helper 1.6.5 or later, I no longer have to create my bus matrix by hand. When you click the Printer Friendly Dimension Usage button, it will ask if you want the detailed or summary version.

PrinterFriendlyDimUsage

The report will open up in a new window, and you can choose to print it or export to Word, PDF, or Excel.

Bus Matrix report

Tri-State Perspectives and Deploy Perspectives for SSAS Multidimensional

The Tri-State Perspectives and Deploy Perspectives go together nicely. Tri-State Perspectives put a red box around any dimension or measure group in which not all children are included in the perspective. This can be very helpful as you update and enhance your cube.  New measures or dimension attributes are not added to perspectives by default when they are added to a cube, so this is a great way to double-check that you have added any new fields to the appropriate perspectives before you re-deploy.

TriStatePerspective

 

If you need to make changes only to your perspectives (perhaps you realized you forgot to add a new field to a perspective), the Deploy Perspectives functionality allows you to deploy only the perspectives rather than the entire cube. This feature will delete any perspectives on the destination database that aren’t in the source in addition to deploying perspectives.

DeployPerspective

Tabular Actions Editor

Tabular Actions Editor fills a feature gap in the Tabular SSAS world.  There is a nice UI for creating drillthrough actions for Multidimensional Analysis Services. By default, tabular models have drillthrough actions that simply contain the fields in the underlying fact table for that measure, which isn’t very useful or friendly to your users since it is mostly dimension keys and a few aggregatable values.

defaulttabulardrillthrough

Without BIDS Helper, the only way to alter that behavior is the manually change the .bim file, which can completely corrupt your tabular model if you aren’t careful. The Tabular Actions Editor provides a nice UI to create actions in a manner similar to multidimensional SSAS development so you can create a helpful detail data set that is analytically relevant.  With BIDS Helper you can also add actions other than drillthroughs, such as go to Url or go to report. With drillthrough actions, you choose:

  • action type
  • target type
  • target measure group
  • default setting
  • maximum number of rows
  • method of invocation
  • perspectives in which it should be available.

 

tabular actions editor

There are lots of good examples in the documentation.  You will also notice a warning in the documentation:

Warning: While actions work in Tabular models, they are not officially supported by Microsoft. If you encounter a bug in how Tabular handles actions and open a support case, Microsoft may not provide support.

I have not run into a situation where this has been an issue, but I wanted to make sure I called this out so you knowingly accept the risks.

I love BIDS Helper

If you can’t tell, I’m a fan of BIDS Helper.  I like it so much that I recruited a friend to help me create and deliver a presentation about it.  If you are developing in SSAS or SSIS and you aren’t familiar with BIDS Helper, I encourage you to check it out.  I believe is it one of the best free tools for MSBI developers available today.