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.

Atlanta MS BI and Power BI Group Meeting on May 4th

/0 Comments/in , /by

MS BI fans, please join us online for the next Atlanta MS BI and Power BI Group meeting on Monday, May 4th, at 6:30 PM. Bill Anton will show you how to effectively apply time intelligence to your Power BI data models. 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:Power BI Time Intelligence – Beyond the Basics
Date:May 4th, 2020
Time6:30 – 8:30 PM ET
Place:Join Microsoft Teams Meeting

Learn more about Teams | Meeting options

Computer audio is recommended

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

Overview:Time-Intelligence refers to analyzing calculations and metrics across time and is the most common type of business intelligence reporting. Power BI has a lot of built in capabilities to help you get started but these alone are not always enough for most real-world solutions.

The key to mastering time-intelligence in Power BI is a good date table and understanding how to manipulate the filter context. This session will teach you how to do both!

In this (demo-heavy) session, we’ll quickly review Power BI’s built-in time intelligence capabilities and why you should avoid them! We’ll also cover the importance of a good date table, what attributes it should include, and how it can be leveraged to simplify complex time-intelligence calculations. Finally, we’ll breakdown a handful of the 40+ DAX time-intelligence functions, showing you how they work under the covers (hint: filter context) and how they can used in combination to accommodate complex business logic.

Speaker:Bill Anton is an independent consultant whose primary focus is designing and developing Data Warehouses and Business Intelligence solutions using the Microsoft BI stack. When he’s not working with clients to solve their data-related challenges, he can usually be found answering questions on the MSDN forums, attending PASS meetings, or writing blog posts over at byoBI.com.
Prototypes “without” pizza:Power BI latest features

PowerBILogo

Sentiment Analysis with Power BI

/0 Comments/in /by

A recent ask from an airline company was to perform sentiment analysis on comments in surveys collected from their customers. Sentiment analysis is a machine learning task that requires natural language processing.

In Power BI, we have at least two ways to approach this requirement: Cognitive Services and custom code, such as by using the Python Natural Language Toolkit (NLTK).

This post compares the pros and cons of each option based on my impressions so far.

Cognitive ServicesPython
LicensingIncluded in premium or embedded capacity

or, provisioned separately with Azure subscription with Power BI Pro

Freely available
ProvisioningAlready provisioned with premium or embedded (need to enable AI workloads)Install Python

Install pandas, matplotlib, and nltk packages

Language detectionYesNo
Data refreshNo gateway requiredPersonal gateway required
Enhanced dataset metadataSupportedNot supported

Cognitive Services

Cognitive Services is an Azure PaaS cloud service that supports text analytics and image recognition. It’s automatically included in Power BI Premium or Embedded capacities (make sure that AI workloads are enabled in the capacity settings). If you organization doesn’t have Power BI Premium or Embedded, you can provision Cognitive Services in Azure (requires an Azure subscription) and then write a custom Power Query function to invoke its APIs, as demonstrated by this tutorial. If you provision Cognitive Services outside Power BI Premium,  you’ll be charged per transaction. In the case of Power BI, the number of transactions equates to the number of rows in your table. So, if you refresh five times a table with 1,000 rows and calculate the sentiment polarity score for each row, you’ll be charged for 5,000 transactions.

You can integrate Power BI with Cognitive Services in a Power BI dataflow or within Power Query in Power BI Desktop. The latter option requires specifying a premium or embedded capacity if you want to go code-free and use the Text Analytics feature (Home ribbon in Power Query). Otherwise, you must write M code as the above tutorial shows.

One CS feature that proved very useful is the automatic language detection. In my case, I had comments in different languages. When each row is processed, Power BI will send a “transaction” to Cognitive Services. If you leave the second parameter (language) of the API call to null, Cognitive Services will try to detect it on its own!

Refreshing data and rescoring do not require a Power BI gateway because Cognitive Services is a cloud service.

Python

When budget is tight or you can’t get help from IT to provision Cognitive Services, Python might come to the rescue. The main advantage of this option is that is free. But, you need at least a few lines of Python code (or much more if English is not the only language you need to support), as this article demonstrates. You must install Python (TIP: install it from python.org as Anaconda doesn’t work with Python scripts since there isn’t way to start the Anaconda environment before the script runs), configure Power BI for Python scripting, and install pandas, matplotlib, and nltk packages . A great feature of Power Query is that you can add a Python transformation that can call the Python script inside your Power Query transformation steps.

As far as I could tell, handling multiple languages is not an easy task with Python NLTK. You can easily detect the language, but there are no built-in dictionaries for any other language than English when performing sentiment analysis. In addition, when you publish your Power BI Desktop file with Python transformations, you need to set up a gateway. The enterprise gateway doesn’t support Python scripts so you must install a personal gateway on the machine that was used to develop the Power BI Desktop file.

As a last caveat, note that Power BI Desktop “Enhanced Dataset Metadata” feature (currently in preview) doesn’t support R and Python scripts yet. So, if Power Query Preview Pane works but you get an error when importing your data in Power BI Desktop, you’ve probably turned this feature on. To resolve, turn it off and then create a new Power BI Desktop file.

Virtual Power BI Dashboard in a Day (DIAD) on April 30

/0 Comments/in /by

Register for my next online Power BI Dashboard in a Day (DIAD) workshop and hone your data analytics skills! Drive value for your organization and on the road to recovery after COVID-19. Attend from anywhere for only $199. I’ll teach you practical Power BI knowledge and data analytics skills that you can immediately apply to your job. See how Power BI can improve your usability and productivity even further.

Power BI Source Control

/0 Comments/in /by

Question: How to put Power BI files under source control?

Workaround: Power BI Desktop does not currently support source control and it doesn’t have an Excel-like extensibility mechanism to let developers take from where Microsoft left off.

However, thanks to the enhanced dataset metadata (currently in preview), you can implement a rudimentary source control policy while waiting for third parties to fill in the void by using these steps:

  1. In Power BI Desktop, go to File, Options and settings, Options. In the “Preview features” tab, check “Store datasets using enhanced metadata format” if this feature is still in preview. Restart Power BI Desktop when asked.
  2. In Power BI Desktop, go to File, Save As and save the file as a Power BI Template (*.pbit) file. This removes the imported data which you don’t need for source control and keeps the file small.
  3. If you want to put all artifacts (reports, queries, data model) under source control, upload the entire file to your source control provider. However, if you want to compare and restore the model schema, you have more work left.
  4. In Windows Explorer, right-click the pbit file and extract its content using your favorite extractor, such as 7-Zip. If this doesn’t work, rename the file extension to *.zip (Power BI files are saved as archive zip files) and extract.
  5. The DataModelSchema file stores the model schema. I recommend you version control it separately so you can easily compare what changes have been made to the model (DataModelSchema is described in the same JSON schema as Analysis Services Tabular).
  6. If you want to restore to a previous version of the schema or make changes, simply change the DataModelSchema file, zip the content back to a *.pbit file.

Other options to get to the model schema:

  1. Use Tabular Editor which can open *.pbit files.
  2. Use SSMS to connect to the running instance of Power BI Desktop (you can use the DAX Studio to obtain the port the AS instance is listening on) and then script the database as CREATE.

Two Virtual Workshops to Learn the Power BI Platform

/0 Comments/in /by

Take advantage of this exclusive opportunity to increase your data IQ from the comfort of your home wherever you are! Register today for my instructor-led digital training events and learn the Microsoft Power Platform consisting of Power BI, Power Apps, and Power Automate.

1-Day Power BI Dashboard in a Day (DIAD) Workshop

1-Day Application in a Day (AIAD) Workshop

The workshops will be conducted online. Login instructions will be sent to registered attendees before the event.