Microsoft Power BI

Microsoft Power BI is a cloud-based business analytics service that gives you a single view of your most critical business data.

This DSS Plugin offers the ability to export Dataiku DSS data to Power BI, using the regular Export infrastructure.

Microsoft Power BI and Dataiku DSS are 2 complementary solutions. In a typical usage scenario, users will be able to:

• build workflows in DSS to create complex data transformation pipelines and build machine learning models, possibly relying on other Microsoft technologies (such as Azure Blob Storage, Azure Data Lake Store, Azure HDInsight or SQL Server)
• then push the output of these workflows directly to Power BI to be consumed by end users, using its interactive visualization and dashboarding features.

The Power BI custom Exporters can be used directly from the Export menu of the Dataset, or through an Export Recipe. If the latter is used, it will be usable in a DSS Scenario and the data could be refreshed automatically on scheduled basis.

Plugin Information

Obtaining Valid Power BI Credentials To Use The Plugin

The Plugin uses the Power BI REST API to interact with Power BI, and requires a valid access token to be able to submit requests. The information required to get an access token are:

• the username (email address) of the user authorized to access to Power BI
• the associated password
• the Client ID of the Power BI client application
• the associated Client Secret

You will need to register a client application with the right permissions. The main steps are as follows (Azure interface may differ):

• (optional) In your Azure Active Directory, create a “generic” user that will be used as a service account for Power BI
• Navigate to https://dev.powerbi.com/apps and login using this account
• Register a new “Server-side Web app”, ticking all Dataset APIs boxes, and write down your application Client ID and Client Secret
• Set the server-side Web app’s redirect URI as https://<<your DSS host>>/dip/api/oauth2-callback
• Go back to the Azure portal
• In the Azure Active Directory section, go to “App registrations”
• Select the App you created above (you can filter by application ID)
• Under Required permissions, select Power BI Service, select all permissions, then click Grant permissions

Please refer to this official Azure documentation in case of questions, and contact your Power BI administrator.

How to set up

• In DSS, go to Plugins > Installed > Microsoft Power BI > Settings > Azure Single Sign On > Add preset. There, paste the App client ID and secret copied from previous step and save the preset.
• Go to Profile & settings > Credentials for your DSS account. Click the edit button for the Power BI preset you are configuring. This will redirect you to a Microsoft single sign on page. Log in to the account if necessary and click Yes

How To Use

The Plugin provides the following components:

• A DSS Exporter to export a DSS Dataset to Power BI, using Microsoft Single Sign On (preferred method to get access)
• A DSS Exporter to export a DSS Dataset to Power BI, generating a new access token at each build using the user’s Power BI credentials

Exporting a DSS Dataset to Power BI

The Plugin will appear through the regular Export facility of DSS. For one-off export, just click on the Export function of a Dataset, and under “OTHER (CUSTOM)”, you will be able to see the 2 custom Power BI exporters:

By default, the data will be exported into “My Workspace”. Power BI Pro license holders can export toward any other workspace simply by adding its name into the workspace box.

To automate the Export to Power BI, an Export to folder recipe can be used (you need to create an empty folder that acts as the output folder of the export recipe). The 2 Exporters will be also available there, but will now be part of the Flow and can then be automated via Scenarios. Here is a screenshot:

Using DSS data in Power BI

Once the export has completed, the data will be available for consumption in Power BI. The table associated with the dataset is called “dss-data” (default value), and the data types will be inferred from the DSS Dataset. Visualizations can be built from there: