Propagating DAX BLANK() Over Relationships

/0 Comments/in /by

Happy 4th of July with a DAX puzzle!

Consider the following three tables. Notice that InvoiceDetails has a data integrity issue where the third row doesn’t have InvoiceNo (InvoiceNo is blank).

What would a measure with COUNTROWS(Invoice) return for Teo? BLANK(), right? And that’s correct because Teo has no invoices. Now, what would COUNTROWS(InvoiceDetails) return for Teo? BLANK() again given that Teo has no line items? Nope, it returns 1. In other words, it will return the count of all line items whose InvoiceNo is blank. How come?

COUNTROWS(Invoice) returns blank for all customers with no matching rows in Invoice because these customers are mapped to the blank row in Invoice, which is excluded from COUNTROWS by default. But if you change COUNTROWS(Invoice) to COUNTROWS(VALUES(Invoice)), then 1 will be returned for these customers. Then, the filter flows to InvoiceDetals through the blank row and counts all rows with blank InvoiceNo.

This behavior wouldn’t have happened in SQL. However, DAX blank is not SQL null. Blank sales in DAX means no sales or zero sales, but null sales in SQL means unknown sales. Therefore, in DAX blank = blank, but in SQL null is always different than null.

Thanks to the Father of DAX, Jeffrey Wang, for shedding light in dark places.

Atlanta MS BI and Power BI Group Meeting on July 6th

/0 Comments/in , /by

MS BI fans, please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, July 6th, at 6:30 PM. David Eldersveld, a Microsoft MVP, will demonstrate cool visualization techniques using DAX. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).

Presentation:DAX Visualization Techniques for the Power BI Table/Matrix
Date:July 6th, 2020
Time6:30 – 8:30 PM ET
Place:Join Microsoft Teams Meeting

Learn more about Teams | Meeting options

Computer audio is recommended

Overview:Did you know that DAX is more than a modeling language and can also be used for data visualization? From sparklines to bullet charts, Unicode text to SVG, icons to maps; learn different techniques to build visuals within visuals using DAX. In this session, David Eldersveld guides you through techniques that go beyond basic conditional formatting to enhance your table and matrix visuals.
Speaker:David is a Solution Architect and Microsoft MVP who has employed skills in technology development, data integration, data analysis, and Microsoft BI for over ten years. David enjoys building BI and analytics solutions with technologies in Microsoft Azure and the Power Platform. In addition to his work with the Microsoft Data Platform at BlueGranite, he also blog at dataveld.com.
Prototypes without pizza:Power BI latest features

PowerBILogo

A False Sense of Data Security

/0 Comments/in /by

UPDATE 11/26/2020: After the latest update to tenant settings, I’m glad that the new “Export to …” settings don’t affect external connections anymore. There is now a new setting “Allow live connections” to control external connectivity. Read “Updated Export Settings” to learn more.

The Power BI “Export data” setting is horrible. I wish Microsoft never came up with it and no other Microsoft tool has it. Apparently, the premise here is to prevent users from exporting data behind the report so it does not end up in wrong hands. But all it accomplishes is a false sense of data security and does more damage than good. Like users can’t take screenshots and share them wherever they please. Or print and mail reports. Unless of course, you disable printing reports too, but where is that path of not trusting your users going? Why don’t you just disallow them from viewing reports at all? “No data, no problem”, right?

Besides “exporting” data, this setting effectively disables all external connections to published datasets. Therefore, the following Power BI features won’t work:

  1. Creating reports from published datasets, which is a best practice.
  2. Publishing semantic models from Visual Studio via the Power BI Premium XMLA endpoint. Even if you enable the XMLA endpoint for read/write, “Export data” will overwrite it because it’s highest level overwrite
  3. Connecting other tools to published datasets, such as SSMS, Tabular Editor, DAX Studio, Profiler, and third-party reporting tools.
  4. Using the “Analyze in Excel” feature for creating Excel reports connected to published datasets.

So, leave “Export data” enabled. If you really want to prevent sensitive data outside your organization, consider applying sensitivity labels which integrate with Office 365 information protection.

A Gateway to Hell

/0 Comments/in /by

“Life is suffering and suffering is unavoidable”. Buddha must have dealt with data gateways. Two “gotchas” that surfaced recently after countless hours of troubleshooting:

  1. A bug in the data gateway AD lookup cache – In my “Gateway AD Lookups” post, I described a very useful gateway feature to look up the on-prem user identity from a cloud identity so that you don’t have to create explicit mappings. The gateway maintains an internal dictionary cache so that it doesn’t have to look up Windows Active Directory for every query. This cache can’t be disabled. The issue is that If you have an older gateway build, e.g. December 2019, there is a bug (and a security vulnerability) with concurrent access to cache which may cause the gateway to map the interactive user to a wrong AD user under heavy load. The issue was fixed in a later build, but the fix introduced another issue by imposing a very coarse lock (probably the entire cache) which may reduce concurrency with many users running reports. In other words, under a heavy load user requests queue up to wait for the cache lock to be released. This issue will be fixed in the next July 2020 gateway build.
  2. Beware stale data sources registered in the gateway – Now that we’ve addressed the concurrency bug, the same client reported a new issue where every hour or so users can’t render reports connected to the gateway for a couple of minutes and then the issue resolves itself. The culprit turned out to be neglected Analysis Services data sources registered in the gateway. By default, Power BI dashboards will query these connections every hour to cache tile data (the frequency can be changed on the dataset settings page, but it can’t be disabled). The problem was that the master account used to connect to Analysis Services changed its password. While the team updated the connections in the actively used data sources, it did not do so for the neglected data sources (no one is using them, right?). However, the dashboards still submit queries to update the tile cache and the gateway asks AD to authenticate the master account.  Then, the domain controller locks the master account for a few minutes after a certain number of attempts since it rejects the logon attempt with the old password. The solution was to remove unused data sources from the gateway.

Designing Responsive Power BI Reports

/0 Comments/in /by

I’m currently providing advisory services to an enterprise client for architecting and implementing an executive dashboard. As a typical dashboard, the UX design included various KPIs that look like these:

The Power BI report implementation followed the design and the above visualization was implemented as four cards and two textboxes. Including other UX elements, such as icons, labels, etc., the most important summary page of the report ended up having more than 100 visuals. It took 25 seconds to render the page on average, which is horrible performance. Performance Analyzer showed that DAX queries are very fast and most of the time was spent in the “Other” category. This means that because JavaScript is single-threaded, visual rendering is sequential. Indeed, the SQL Server Profiler revealed that out of 25 seconds, the first 15 seconds were spent elsewhere before Analysis Services Tabular starts receiving DAX queries. The Performance Analyzer document provides the essential coverage about how Power BI renders visuals, but the bottom line is this:

The more visuals the page has, the slower it will be. If you find that most of the time is spent in the “Other” category on page refresh, more than likely this is caused by Power BI serializing the visual rendering. If this is the case, the best course of action would be to reduce the number of visuals.

For example, the above visualization can be rendered with one visual only (Matrix) and some blackbelt visualization techniques. Matrix supports rendering measures on rows (in the Values section of the Format tab, turn on “Show on rows”). Spacing the rows can be achieved by increasing row padding. And icons can be rendered with conditional formatting. Another technique for reducing visuals and faster rendering is eliminating many labels with a page background image. What didn’t help was disabling visual interactions although it doesn’t hurt disabling it if you don’t need it. We’ve also found that browsers differ in how fast their render visuals. Internet Explorer was the slowest while Chrome and the new Edge were the fastest.

How fast did their report get after applying such optimizations? Between 50 and 60% faster.

True, Power BI has report design limitations. Developers with SSRS background will miss more advanced features, such as nesting visuals (e.g. bullet graph inside a matrix) and asymmetrical crosstab layouts. Still, techniques as the one I shared above will help you create more responsive reports.

Links to Power BI Mobile Reports or Apps

/0 Comments/in /by

You have deployed your Power BI reports/apps and now management is eager to use them. But how do you provide an easy one-tap shortcut for them to launch a specific report or app on mobile devices and open them in Power BI Mobile? For example, in a recent project, we had to provide executives with an easy “button” to see their executive dashboard on iPhone/iPad. To make things more interesting, the ask was to auto-provision “the button” so the users don’t have to create it manually.

Each approach has specifics controlled by the operating system on the device. Your best option will probably be Siri or Android shortcuts.

  1. Siri or Android shortcuts – Use this option when you want the user to initiate the link either by clicking on it or by speaking the associated phrase. For more information, read “Using Siri Shortcuts in Power BI Mobile iOS App” and “Use Android app Shortcuts in the Power BI Android app” The downside of this approach is that Apple doesn’t currently have shortcut APIs so you can’t provision the shortcuts, such as by using Microsoft Intune or AirWatch. In other words, the user must manually create the shortcut within Power BI Mobile. However, they can send the shortcut to other users as “untrusted shortcut”.
  2. Redirect links – Use this option when you want the device to prompt the user to install the app if it is not already installed. For more information, read “Create a link to a specific location in the Power BI mobile apps“. The steps to create a shortcut are device specific. In this project, the client used AirWatch to provision “weblink/webclip” icons to launch the redirect links. The caveat for using redirect links is that the user will be always prompted to “close” the prompt or open the app, and as far as I know, there is no way to bypass this prompt.
  3. Universal links – Luckily, Power BI supports iOS universal links. Just navigate to the report in the browser, copy the link, and associate it with an icon. The old way to create the link, which is still supported, was to use the mspbi scheme, such as mspbi://app/OpenReport?appId=1f3d811e-8d6c-4708-9034-ad5c2db1615b&reportObjectId=16c484cd-d2b2-40ab-bddc-3cb97b62e518. Choose this option when you want to bypass the prompt asking the user to open the app. Universal links work if the link is in email message, Siri shortcut or some other app, such as Notes. The downside is that if the user creates a “weblink/webclip” shortcut with the link using the “Add to Home screen” feature or auto-provision with AirWatch, the shortcut will always open in the browser and bypass Power BI Mobile. In other words, the link won’t work when launched from a “weblink/webclip” shortcut.

Let’s summarize the three approaches.

OptionProsCons
Siri or Android shortcuts

(Recommended)

No prompt, can be voice activatedSince there are no APIs to work with shortcuts, they can’t be automatically provisioned
Redirect “deep” linksAsk the user to install the app if not presentAlways prompts the user to “close” or open Power BI Mobile.
Universal linksDon’t prompt the userDon’t work from “weblink/webclip” shortcuts

Atlanta MS BI and Power BI Group Meeting on June 1st

/0 Comments/in , /by

MS BI fans, please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, June 1st, at 6:30 PM. Stacey Jones, a Principal Data Solutions Architect at the Atlanta Microsoft Technology Center, will introduce you to Microsoft Cognitive Services and show you how to integrate it with Power BI. For more details, visit our group page and don’t forget to RSVP (fill in the RSVP survey if you’re planning to attend).

Presentation:AI + BI = Easier paths to Insights & Action
Date:Monday, June 1st, 2020
Time6:30 – 8:30 PM ET
Place:Join Microsoft Teams Meeting

Learn more about Teams | Meeting options

Computer audio is recommended

Conference bridge (toll) number 1 605 475 4300, Access Code: 208547

Overview:Have you ever wondered how to leverage AI in your Reports? In this presentation, I will show you how to use Azure Cognitive Services in Power BI to accomplish things like detecting the language of a given passage of text, translate that same text into another language, detected the sentiment of the text and more. The possibilities are endless with these techniques, you could use it to assess your companies brand image, analyze what your customers are saying about your company on social media, and many others. No data scientist required!
Speaker:Stacey Jones specializes in mentoring and guiding firms in their efforts to build a modern Data, AI & BI governance programs that empower their business with Self-Service BI and Data Science capabilities. He currently serves as the Principal Data Solutions Architect at the Atlanta Microsoft Technology Center (MTC).
Prototypes without pizza:“Power BI latest features” by Teo Lachev

PowerBILogo

Analyzing Microsoft Teams Data

/0 Comments/in /by

More and more organizations are switching to Microsoft Teams mostly for online meetings. However, Microsoft Teams can deliver much more than that and it will be unjust to compare it with other popular meetings apps. What Microsoft has done with Teams is amazing! Now that I got to study and program Microsoft Teams, I have found it a powerful and extensible platform. For example, Microsoft has provided add-ons for common verticals, such as Teams for Education. Microsoft Teams can be further enriched with apps and can be used to centralize all these Excel spreadsheets that are floating around. And the best of it: most of the Microsoft Teams data is exposed via a single data source: Microsoft Graph API, with the caveat that it is not that easy to get the data out.

I’ve added a new case study “Public School District Gets Insights from Microsoft Teams” that shows how Prologika helped one of the largest public school districts in the Atlanta Metro area to derive insights from Teams. They struggled for a long time trying to get the necessary data from Microsoft Teams to analyze how effectively teachers and students utilize Microsoft Teams in these challenging times. Prologika developed a custom app to extract the required data by calling the Microsoft Graph APIs. The key solution benefits were:

  • Automated daily retrieval of Teams data
  • Enabled a digital feedback loop to learn from the gathered insights and improve operations
  • Ability to analyze educational data by various perspectives (school, class, teacher, student, assignments, submissions, and events)

Showing Database Images in Power BI and Tabular

/0 Comments/in /by

The Power BI image-rendering visualizations, such as Table or Card, expect image links to public servers hosting the images with anonymous permissions. This has obvious shortcomings. Can we load images from a database or Power BI data table? You bet, as Jason Thomas has demonstrated a long time ago. Here are the steps I followed to show the images from the Production.ProductPhoto table in the AdventureWorks2012 (or later) database. If you want to embed a few images into a Power BI data table (instead of an external database), you can convert them manually to Base64 using any of the online image converters, such as https://codebeautify.org/image-to-base64-converter and embed the resulting string into a Power BI data table (the Enter Data feature). Gerhard Brueckl takes this one step further by showing you how to automate the base64 conversion with many images.

  1. Import the table with the image column as usual in Power BI.
  2. In Power Query, change the data type of the image column to Text.
  3. Add a new custom column that prefixes the base64 string with “data:image/jpeg;base64,” (not the comma at the end) for jpeg images or “data:image/png;base64,” for png.
    = Table.AddColumn(#”Changed Type”, “ProductImageEncoded”, each “data:image/jpeg;base64,” & [ThumbNailPhoto]).
    The ProductImageEncoded column below shows what the final Power Query transformation should look like. Click Close & Apply to import the table in the data model as you’re done with the transformation part.
  4. In Power BI Desktop, select the ProductImageEncoded field in the Fields pane. Assuming the new ribbon, in the Column Tools ribbon, change the field category to Image URL.
  5. Add the ProductImageEncoded field to a Table, Card, Multicard, or Slicer visuals.

Power BI Time Adventures

/0 Comments/in /by

A customer reported that a Power BI date filter/slicer set to a specific date in Date dimension, let’s say April 24, 2020, doesn’t return some rows from the fact table that match that date. Upon some digging, the data was imported from Dynamics CRM and the source date column had UTC time. Power Query showed Date/Time/Timezone as a data type. However, the developer has converted the corresponding field in the model to the Power BI Date data type to remove the time portion. And indeed, the Data View would show that date as April 24, 2020 (without the time portion). So, why no match?

The xVelocity storage engine (the storage engine behind Power BI and Analysis Services Tabular) has only one data type for dates: DateTime. If you convert the field in the data model to Date, it still keeps the time portion (UTC or not) and it doesn’t change the column storage. It just changes to the column formatting to show the date only.

To get rid off the time portion, cast to Date in the data source (e.g. in a wrapper SQL view) or change the data type of the column in Power Query to Date. This will “remove” the time portion. In reality, xVelicity will convert the time to midnight.