Power BI Video Training Now Available

If you can’t make it to one of my classroom-based courses in London next year, today I’m pleased to announce that my new Power BI video training course is now available via my friends at Project Botticelli. It covers the following topics:

  • What is Power BI? (Free)
  • Connecting to Data Sources
  • Data Import Concepts
  • Transforming Data with the Query Editor
  • Advanced Data Loading
  • Modelling Data
  • Basic DAX
  • Power BI Desktop vs Excel
  • Cloud and Desktop Power BI Dashboards and Reports
  • Building Reports in Excel
  • Data Refresh (Free)
  • Administration and Auditing (Free)

Several of the shorter videos are free to view. Full details can be found here:

https://projectbotticelli.com/power-bi-desktop-and-cloud-course-video-tutorial?pk_campaign=tt2017a

Other courses available on the site include my MDX and SSAS Multidimensional courses, plus Marco Russo’s DAX course and many others.

If you’re quick, you can use the code CHRIS2017CYBER15 to get a 15% discount on subscriptions before Tuesday November 28th 2017!

In-Person Power BI And Azure Analysis Services Training In London

I’m pleased to announce two in-person training dates in London next year.

First of all, I’ll be running a three-day “Introduction to Power BI” course from January 29th to January 31st 2018.  Suitable for BI pros, analysts and anyone who needs to use Power BI to build reports, I’ll be covering data loading, data modelling, a bit of M, a lot of DAX, report design, publishing, security and administration. Full details and registration can be found here:

http://technitrain.com/coursedetail.php?c=84&trackingcode=CWB

Not long after, on February 22nd 2018, I’ll be teaching a full-day preconference seminar at SQLBits in London on Azure Analysis Services. The agenda can be found here:

http://sqlbits.com/information/event17/Introduction_to_Azure_Analysis_Services/trainingdetails.aspx

…but, to be honest, there’s likely to be a lot of cool new functionality released for Azure SSAS between now and then so that will all have to be fitted in too. SQLBits is, of course, the biggest SQL Server/Microsoft data platform conference in Europe, ridiculously good value for money and loads of fun. Pricing and registration details can be found here:

http://sqlbits.com/information/pricing

The Extension.Contents() M Function

Following on from my post last week about M functions that are only available in custom data extensions, here’s a quick explanation of one of those functions: Extension.Contents().

Basically, it allows you to access the contents of any file you include in the .mez file of your custom data connector. Say you have a text file called MyTextFile.txt:

image

If you create a new Power BI Custom Data Connector project using the SDK, you can add this file to your project in Visual Studio like any other file:

image

Next, select the file and in the Visual Studio Properties pane set the Build Action property to Compile:

image

Setting this property means that when your custom data connector is built, this file is included inside it (the .mez file is just a zip file – if you unzip it you’ll now find this file inside).

Next, in the .pq file that contains the M code for your custom data connector, you can access the contents of this file as binary data using Extension.Contents(“MyTextFile.txt”). Here’s an example function for use in a custom data connector that does this:

[DataSource.Kind="ExtensionContentsDemo", 
Publish="ExtensionContentsDemo.Publish"]
shared ExtensionContentsDemo.Contents = () =>
    let
        GetFileContents = Extension.Contents("MyTextFile.txt"),
        ConvertToText = Text.FromBinary(GetFileContents)
    in
        ConvertToText;

image

In the let expression here the GetFileContents step returns the contents of the text file as binary data and the ConvertToText step calls Text.FromBinary() to turn the binary data into a text value.

When this function is, in turn, called it returns the text from the text file. Here’s a screenshot of a query run from Power BI Desktop (after the custom data connector has been compiled and loaded into Power BI) that does this:

image

BI Survey 17: Power BI Highlights

Every year, in return for publicising it to my readers, I get sent a copy of the findings of the BI Survey – the largest survey of BI product users in the world. As always they make for very interesting reading indeed, and although I can’t give away all the details I have been allowed to blog about a few of the highlights for Power BI:

  • Power BI is now the third most frequently considered product in competitive evaluations, after QlikView and Tableau.
  • Indeed, based on responses from vendors and resellers, Microsoft is now the third ‘most significant competitor’ after Tableau and Qlik and is in a much stronger position than it was two years ago – clearly as a result of the impact that Power BI has made, although Excel, SSAS and SSRS contribute to this too.
  • Unsurprisingly Power BI’s exceptional price/performance ratio is the main reason that organisations purchase it. Nonetheless it gets very high satisfaction ratings too.
  • Power BI is also now the third most frequently used front-end for SSAS, after SSRS (which is in the #1 spot and quite a way out in front) and Excel (either on its own or with an AddIn).

Overall it’s a very strong showing for Microsoft. If you’re conducting a competitive evaluation of BI tools, or if you’re a BI vendor, it’s probably worth buying a copy of the full findings.

Which M Functions Are Only Available To Custom Data Connectors?

Here’s one for all you M geeks out there. If you look at the example code for custom data connectors on the Power BI custom data connectors GitHub repo it’s clear that there are several M functions that are available in custom data connectors that aren’t available in Power BI Desktop. But what are they? As I’m sure you know, you can get a list of all the functions, types and enums available in M using the #shared keyword. Well, I created a simple custom data connector that calls #shared and returned a table of all the functions, types and enums available to a custom data connector, and then compared that table with what #shared returns when you run it in Power BI Desktop. This made it easy to find a list of M functions, types and enums that are only available in custom data connectors, and here are all 34:

  • CryptoAlgorithm.Type
  • CryptoAlgorithm.SHA1
  • CryptoAlgorithm.SHA256
  • Crypto.CreateHmac
  • Crypto.CreateHash
  • Web.SignForOAuth1
  • OAuth1.Type
  • OAuth1.HMACSHA1
  • OAuth1.RSASHA1
  • Extension.Module
  • Extension.CurrentCredential
  • Extension.CurrentApplication
  • Extension.CredentialError
  • Extension.LoadString
  • Extension.Contents
  • Credential.AccessDenied
  • Credential.AccessForbidden
  • Credential.EncryptionNotSupported
  • Credential.NativeQueryPermission
  • Error.Unexpected
  • Uri.Type
  • Binary.End
  • Action.Type
  • Action.Sequence
  • Action.Return
  • Action.Try
  • Action.DoNothing
  • ValueAction.Replace
  • ValueAction.NativeStatement
  • TableAction.InsertRows
  • TableAction.UpdateRows
  • TableAction.DeleteRows
  • WebAction.Request
  • Delta.Since

Some of these, like the Action functions, are documented in the Power Query function reference, and as I said others are mentioned in the Power BI custom data connectors GitHub repo, but there’s definitely some detective work to do here…

PASS Summit 2017 Day 1 BI News

There haven’t been any really big BI announcements at the PASS Summit this year – I guess we get so much amazing new stuff every month with Power BI that there’s no need to make ‘big’ announcements at conferences any more. However there have been several cool new features unveiled that I thought it would be good to highlight.

Azure SSAS automated scale-out

I’m a big fan of Azure SSAS, and what I particularly like is the way the dev team are making tasks that are complex with on-premises SSAS much easier when you go to the cloud. SSAS scale-out is a great example: on-premises you have to solve this by buying multiple servers, licensing and maintaining multiple instances of SSAS, and then setting up network load balancing on top of them. Today the Azure SSAS team announced their automated scale-out feature is live, and now all you need to do to scale out is drag a slider to the right:

image

Of course this is all scriptable too, so if you know you need to have to handle more users on a Monday morning you can scale-out appropriately and then when the rush is over you can reduce the number of query replicas and pay less.

Power BI Report Server

There’s a new release of Power BI Report Server available, and you can read all about it here:

https://powerbi.microsoft.com/en-us/blog/new-version-of-power-bi-report-server-now-available/

The blog post highlights the fact that you can connect to SSRS shared datasets via OData – which is basically what I was talking about here.

Preview of in-memory/DirectQuery hybrid datasets

Christian Wade of the SSAS dev team gave a very cool demo of building Power BI reports on a trillion row dataset. Not many details are available about how this works, or when it will be released, but it seems to be through a feature where you can combine tables stored in-memory and tables that use DirectQuery storage (in the demo this was DirectQuery on Spark) in the same dataset. It sounds a bit like HOLAP: queries that request aggregated values hit the fast, in-memory data, but when you want to look at detail-level data queries use DirectQuery. Someone is very excited about this:

image

PowerApps/Power BI integration

Something that was going to be demoed – but the demo failed so we didn’t see it – was the promised integration between PowerApps and Power BI. Apparently this will be released soon.

ESRI Plus subscription for Power BI

Mentioned in the keynote but again with very few details is the fact that ESRI is going to offer a special subscription for users of the ESRI maps visual in Power BI. It looks like this is it and it is slated for release in November:

http://go.esri.com/plus-subscription

Here’s what the ESRI site says the benefits will be:

  • Access global demographics
  • Access verified ready-to-use data, curated from authoritative sources
  • Access more basemaps including satellite imagery and terrain
  • Map and view more locations on your visualization

Drillthrough On Calculated Members In SSAS MD 2017 Using DAX Expressions, Part 2

If you read part 1 of this series you probably already have a good idea of how I’m going to use DAX queries in actions to implement drillthrough on calculated members. However, there is one last major problem to solve – and no 100% fool-proof way of solving it.

That problem is that there is a maximum length of (as far as I can tell) 32768 characters for the DAX query that is generated by the action. If you exceed that limit you’ll see an error in Excel when you drillthrough:

image

This is the result of the generated DAX query being truncated before Excel tries to run it. As you can imagine, if you have large dimensions and you’re generating a DAX query with thousands of key values in an IN expression, it’s very easy to hit the maximum query length. What can you do to make this less likely?

Probably the most effective method is to check whether the IN clause will contain all of the possible key values on the dimension (most likely because no attributes from the dimension are used in the query), and if so don’t bother including that dimension in the DAX query. Here’s an example of how this can be done:

IIF(
COUNT(EXISTING [Date].[Date].[Date].MEMBERS) = 
COUNT([Date].[Date].[Date].MEMBERS),
"",
",'Date'[Date.Key0] IN {
" +
GENERATE(EXISTING [Date].[Date].[Date].MEMBERS , 
[Date].[Date].CURRENTMEMBER.PROPERTIES("KEY"), ",")
+ "}
")

Another option is to use NonEmpty() to remove any members from the drillthrough that have no data, like so:

IIF(
COUNT(EXISTING [Date].[Date].[Date].MEMBERS) = 
COUNT([Date].[Date].[Date].MEMBERS),
"",
",'Date'[Date.Key0] IN {
" +
GENERATE(EXISTING 
NONEMPTY([Date].[Date].[Date].MEMBERS, [Measures].[Sales Amount]) 
, [Date].[Date].CURRENTMEMBER.PROPERTIES("KEY"), ",")
+ "}
")

However, if these techniques (and there may be others) still don’t reduce the length of the query to an acceptable length you will want to try to fail as gracefully as possible and show a more helpful error message than the one above. You can do this with the DAX Error() function, like so:

IIF(
COUNT(EXISTING [Customer].[Customer].[Customer].MEMBERS) = 
COUNT([Customer].[Customer].[Customer].MEMBERS),
"",
",'Customer'[Customer.Key0] IN {
" +
IIF(COUNT(
{EXISTING 
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS, 
[Measures].[Sales Amount])} 
AS CustomerList)>7000,
"ERROR(
""Please reduce your selection on the Customer dimension
 or remove it from your PivotTable"")",
GENERATE(
CustomerList
, [Customer].[Customer].CURRENTMEMBER.PROPERTIES("KEY"), ",")
)
+ "}
")

In this example, if the resulting DAX IN expression for the Customer dimension will have more than 7000 keys in it, the entire DAX query returns a custom error message instead:

image

Once again this is a far from perfect solution – I would have liked to test the total number of characters in the query, but if you do that you have to write the expression twice, once in the first parameter of IIF() and once in one of the results, and that would be horrible. My gut feeling is that you should only use this technique on dimensions with a large number of members on the key attribute.

Putting this all together, for a simple cube based on data from Adventure Works with three dimensions (Date, Product and Customer) here’s what a complete Action Expression for a regular measure might look like:

"EVALUATE 
FILTER(
CALCULATETABLE(
SELECTCOLUMNS(
'Sales Order', 
""Sales Order Number"",
'Sales Order'[Sales Order Number],
""Sales Order Line Number"",
'Sales Order'[Sales Order Line Number],
""Sales Amount"",
[Sales Amount])
" +
IIF(
COUNT(EXISTING [Date].[Date].[Date].MEMBERS) = 
COUNT([Date].[Date].[Date].MEMBERS),
"",
",'Date'[Date.Key0] IN {
" +
GENERATE(EXISTING 
NONEMPTY([Date].[Date].[Date].MEMBERS, 
[Measures].[Sales Amount]) 
, [Date].[Date].CURRENTMEMBER.PROPERTIES("KEY"), ",")
+ "}
") +
IIF(
COUNT(EXISTING [Customer].[Customer].[Customer].MEMBERS) = 
COUNT([Customer].[Customer].[Customer].MEMBERS),
"",
",'Customer'[Customer.Key0] IN {
" +
IIF(
COUNT(
{EXISTING NONEMPTY([Customer].[Customer].[Customer].MEMBERS, 
[Measures].[Sales Amount])} AS CustomerList)>7000,
"ERROR(""Please reduce your selection on the Customer dimension 
or remove it from your PivotTable"")",
GENERATE(
CustomerList
, [Customer].[Customer].CURRENTMEMBER.PROPERTIES("KEY"), ",")
)
+ "}
") +
IIF(
COUNT(EXISTING [Product].[Product].[Product].MEMBERS) = 
COUNT([Product].[Product].[Product].MEMBERS),
"",
",'Product'[Product.Key0] IN {
" +
GENERATE(EXISTING 
NONEMPTY([Product].[Product].[Product].MEMBERS, 
[Measures].[Sales Amount]) 
, [Product].[Product].CURRENTMEMBER.PROPERTIES("KEY"), ",")
+ "}
") +
"),
[Sales Amount]>0)"

 

What about a calculated measure though? Assuming that  you have a time utility/date tool/shell dimension, you can use a Condition expression on the action above to make sure it only gets executed for the member on the time utility dimension that contains the non-calculated values, in this case called [Actual Value]:

([Measures].CURRENTMEMBER IS [Measures].[Sales Amount]) AND
([Date Calculations].[Date Calculations].CURRENTMEMBER IS 
[Date Calculations].[Date Calculations].&[Actual Value])

image

Now, let’s say there is a calculated member on the time utility dimension that contains a year-to-date calculation with the following definition:

CREATE MEMBER 
CURRENTCUBE.[Date Calculations].[Date Calculations].[Year-To-Date] 
AS
AGGREGATE(
PERIODSTODATE(
[Date].[Calendar].[Year],
[Date].[Calendar].CURRENTMEMBER),
[Date Calculations].[Date Calculations].&[Actual Value]);

You can create a new action that has a Condition expression as follows that restricts it to the year-to-date calculation:

([Measures].CURRENTMEMBER IS [Measures].[Sales Amount]) AND
([Date Calculations].[Date Calculations].CURRENTMEMBER IS 
[Date Calculations].[Date Calculations].[Year-To-Date])

Now, the final problem to solve is to generate a DAX query that returns all of the Sales Orders from the beginning of the current year up to and including the selected date – the Sales Orders that a user would expect to see when they drilled through on the year-to-date calculated member above.

Here’s a modified MDX expression for the Date dimension that returns a DAX expression that finds all of the dates associated with the current selection on the Date dimension, finds the maximum date, then filters the drillthrough DAX query by all dates from the beginning of the current calendar year up to and including the maximum date:

IIF(
COUNT(EXISTING [Date].[Date].[Date].MEMBERS) = 
COUNT([Date].[Date].[Date].MEMBERS),
"",
", VAR FilteredDates = FILTER('Date', 'Date'[Date.Key0] IN {
" +
GENERATE(
EXISTING 
NONEMPTY([Date].[Date].[Date].MEMBERS,[Measures].[Sales Amount]) , 
[Date].[Date].CURRENTMEMBER.PROPERTIES("KEY"), ",")
+ "})
VAR MaxDate = MAXX(FilteredDates, 'Date'[Date.Key0])
VAR MaxYear = MAXX(FilteredDates, 'Date'[Year])
RETURN 
FILTER('Date', 'Date'[Date.Key0]<=MaxDate && 'Date'[Year]=MaxYear)
")

Here’s the complete MDX expression for the year-to-date drillthrough:

"EVALUATE 
FILTER(
CALCULATETABLE(
SELECTCOLUMNS(
'Sales Order', 
""Sales Order Number"",
'Sales Order'[Sales Order Number],
""Sales Order Line Number"",
'Sales Order'[Sales Order Line Number],
""Sales Amount"",
[Sales Amount])
" +
IIF(
COUNT(EXISTING [Date].[Date].[Date].MEMBERS) = 
COUNT([Date].[Date].[Date].MEMBERS),
"",
", VAR FilteredDates = FILTER('Date', 'Date'[Date.Key0] IN {
" +
GENERATE(
EXISTING 
NONEMPTY([Date].[Date].[Date].MEMBERS,[Measures].[Sales Amount]) , 
[Date].[Date].CURRENTMEMBER.PROPERTIES("KEY"), ",")
+ "})
VAR MaxDate = MAXX(FilteredDates, 'Date'[Date.Key0])
VAR MaxYear = MAXX(FilteredDates, 'Date'[Year])
RETURN 
FILTER('Date', 'Date'[Date.Key0]<=MaxDate && 'Date'[Year]=MaxYear)
") +
IIF(
COUNT(EXISTING [Customer].[Customer].[Customer].MEMBERS) = 
COUNT([Customer].[Customer].[Customer].MEMBERS),
"",
",'Customer'[Customer.Key0] IN {
" +
IIF(COUNT({
EXISTING 
NONEMPTY([Customer].[Customer].[Customer].MEMBERS, 
[Measures].[Sales Amount])} AS CustomerList)>7000,
"ERROR(""Please reduce your selection on the Customer dimension or 
remove it from your PivotTable"")",
GENERATE(
CustomerList
, [Customer].[Customer].CURRENTMEMBER.PROPERTIES("KEY"), ",")
)
+ "}
") +
IIF(
COUNT(EXISTING [Product].[Product].[Product].MEMBERS) = 
COUNT([Product].[Product].[Product].MEMBERS),
"",
",'Product'[Product.Key0] IN {
" +
GENERATE(EXISTING 
NONEMPTY([Product].[Product].[Product].MEMBERS, 
[Measures].[Sales Amount]) 
, [Product].[Product].CURRENTMEMBER.PROPERTIES("KEY"), ",")
+ "}
") +
"),
[Sales Amount]>0)"

I warned you it was going to be complicated, didn’t I? You can download a SSAS MD 2017 .abf backup file containing the sample database and the two actions here.