TABLE OF CONTENTS

• Introduction
  • How to integrate a Datamining Report with Power BI
  • Step 1 - Configure Power BI Endpoints
  • Step 2 - Create the required Datamining Report
  • Step 3 - Connect Power BI with the Datamining Report
    • Anonymous Connection
    • How to use when establishing connection
    • Authenticated Connection - Request Header with Username and Password
    • Authenticated Connection - Request Header with a Generated Token
• Further Support

Introduction

Clear and accurate reporting tools play a vital role in any organisation decision today. As businesses strive to become more data-driven, vivid presentations offer powerful medium to harness and understand information. Power BI is a business analytics tools that is used to analyse data and share insights in the form of reports and dashboards.  A Datamining Report in ManagementStudio can be published as a Power BI Endpoint that Power BI can connect to either authenticated with a Username / Password or anonymously. This article walks through the steps required to create a Datamining report, publishing it as a Power BI Endpoint and integrating with Power BI.  For information on getting started with Power BI, please refer to this article.

How to integrate a Datamining Report with Power BI

The example that follows will use the User Migrations module to illustrate the concept, although this approach applies to all modules within ManagementStudio.

Step 1 - Configure Power BI Endpoints

• Navigate to Administration -> [Module] -> Portal (1). 
• Ensure the Portal Enabled checkbox is ticked (2).
• Within the Datamining Reports Endpoints panel, ensure the relevant checkboxes are ticked based on requirements (3).
• Allow Publish DMR as Authenticated Web Report
• Allow Publish DMR as Public Web Report
• Allow Publish DMR as Authenticated PowerBI/Json Endpoint
• Allow Publish DMR as Public PowerBI/Json Endpoint
• Click Save Changes (4).
• Restart the ManagementStudio client.

Step 2 - Create the required Datamining Report

The example that follows will generate a User-Applications report.

• Navigate to the User Migrations (1) module.
• Click on the Datamining Report button (2).

• Click Add Reporting Tier (3) and select Applications (4).
• Select the fields to be included in the report.
• Click Run Datamining Report (5).

• This will present a report similar to this. A User-App Datamining report.

An example of a User-Application report

• Perform any sorting, filtering, reordering and removing of columns in the DMR so that only the columns/rows you want to display remains. 
• Click Run Datamining Report.
• Click Save Report button.
• Enter the Name of the Report (1).
• Brief Description of the Report (2).
• Enable Adv. Options (3).
• Tick the required connection (4).
• Click Save New (5).

Step 3 - Connect Power BI with the Datamining Report

• Load and run the created report from step 2.
• Click Menu and select Web/PBI Endpoints.
• The screenshot below will be presented with URLs that a User can copy to the clipboard.

• Click the Copy to Clipboard icon next to the required URL.
• Launch the Power BI application.
• Click Get Data.
• Type in Web (1) in the search box.
• Select Web (2) from the results.
• Click Connect (3).

Anonymous Connection

• In the From Web dialog, paste the URL copied.

The Basic option is used for the Anonymous URL and the Advanced is used for the Authenticated URL.

In order to format the column in the report you can use the following format

• '?HeaderFormat=DisplayName'
• '?HeaderFormat=InternalName'
• '?HeaderFormat=PrefixedName'

How to use when establishing connection

Appending the header format required to the URL like the examples below will provide the desired outcome in PowerBI. Ensure you click on the Advanced option

• http://msiis:90/1/p/u/dmr/1017/pbi3/db68bf3b-def6-4be4-9449-3956f9f1c11d?Headerformat=PrefixedName
• http://msiis:90/1/p/u/dmr/1017/pbi3/db68bf3b-def6-4be4-9449-3956f9f1c11d?Headerformat=InternalName
• http://msiis:90/1/p/u/dmr/1017/pbi3/db68bf3b-def6-4be4-9449-3956f9f1c11d?Headerformat=DisplayName

Anonymous connection

• Click OK.
• You will be presented with the connecting dialog box.

• You will be presented with the interface similar to the below displaying the contents of the datamining report with the display names (1).
• Click Close & Apply (2).

• Once the connection and model has been loaded, a user can now build visuals with the data.
• For further information about getting started with Power BI, please refer to this article.

Authenticated Connection - Request Header with Username and Password

• Copy the Power BI Authenticated Url.
• Launch Power BI application.
• Click Get Data.
• Type in Web in the search box.
• Select Web from the results.
• Click Connect.
• In the From Web dialog, click on Advanced option.
• Enter the Authenticated URL (1) from ManagementStudio.
• Within the HTTP request header parameters (Optional) (2). 
• Type in 'Username' and enter your ManagementStudio username.
• Click Add Header (3).
• Type in 'Password' and enter your Managementstudio password.
• Click OK (4).

Authenticated Connection - Request Header with a Generated Token

Generating the token

• Switch to ManagementStudio.
• Click Administration->Ms User Accounts.
• Select the User on the grid and click Create Auth Token.

• Enter the details of the User
• Set the Token expiry date.
• Click Create Token.
• Copy the token and send this to the User.

Establishing the Connection in Power BI

• In the From Web dialog, click on Advanced option.
• Enter the Authenticated URL (1) from ManagementStudio.
• Within the HTTP request header parameters (Optional) (2). 
• Type in 'Token' and enter the generated token.
• Click OK (4).

Further Support

If you require further support, please visit ManagementStudio's Service Desk to search the knowledge base or create a new support ticket.