This article shows how the connector works with the Reporting API.
About Power BI Desktop
Power BI Desktop lets you create intuitive reports for visual analysis of your data. Drag and drop content exactly where you want on your Power BI Desktop canvas. Quickly identify trends, thanks to a unified view and interactive visualisations.
- Transform and clean your data
Preparing data for analysis can take some time. Our objective is to simplify this task for you. Try Power BI Desktop’s data formatting and modelling features, and free up your time to concentrate on important data analysis.
- A complete analysis tool
Power BI Desktop is an end-to-end solution enabling you to analyse data with great precision. Power BI Desktop offers all features required to quickly connect, format, visualise and share data.
- Create and share your reports
We know that you need to share data with decision-makers whenever and wherever they need it. Power BI lets you easily publish and share interactive reports.
Learn more: https://powerbi.microsoft.com/en-us/desktop/
Start Power BI Desktop
To get started, simply download the desktop application at https://powerbi.microsoft.com/en-us/desktop/ and install it on your computer.
Once the application has been launched, you’ll be able to start importing data.
Import and format data
The application allows you to import from different data sources: Flat files, web data, or SQL Server.
Import Piano Analytics data
To import your Piano Analytics data into POWER BI, the simplest way is to use the web data connection feature, available in the drop-down menu (Get data > Web).
This will let you load an API URL that you’ll obtain directly from within the Data Query application of Piano Analytics.
Build your query in Data Query, and copy the API URL (GET) relating to your data set. Let’s go into Data Query.
We’ll select the URL that was created, on the right of our data set:
We’ll paste our API URL into the Power BI pop-up and click on "OK":
Once you have an API Key, select the "Basic" mode and enter your AccessKey as a user name and your SecretKey as a password, then click on "Connect":
Select data to visualise
A new pop-up will appear. Click on the double arrows next to "DataFeed.Rows" and click "Expand to New Rows":
Once it expanded, click again on the double arrows, and chose the columns you want to import (usually, all columns), then click "OK":
A last step will show you the results of the API Call, you can then click on "Close & Apply" at the top left.
You are now able to add a visualisation, and select the data you want from your API Call, thanks to the "Data" panel on the right:
"Relationship" view: Connect several data sources to each other
Your business might benefit from the table merging features available in Power BI: manage relationships.
Let’s take an example with the CRM table below, which contains "city" information which is also available in the Cities table. Merging these 2 will enable us to make tables and graphs using all information contained in these 2 tables.
To do this, let’s go into the "Relationship" view.
Once you’ve merged two tables with a relationship, you can use the data from both tables as if it were a single table. In this way, you won’t have to worry about details of the relationship, and you won’t need to flatten these tables into a single table before importing them.
In many cases, Power BI Desktop can automatically create relationships for you. Nonetheless, if Power BI Desktop cannot determine with a high degree of certitude that a relationship between two tables must exist, it will not automatically create a relationship. In this case, you’ll have to create it yourself.
By clicking on the "Relationship" view, different potential relationships are automatically displayed, showing relations between one or several tables with a single-direction or bi-directional arrow.
To customise other relationships or edit existing relationships, click on "Manage Relationships".
A window will open. Click on "New".
A new "Create Relationship" window will open, letting you select tables between which to create a relationship.
In the drop-down list, choose among the created tables, and click on the columns to merge. Selected columns appear in grey.
It is now very important to choose the best cardinality.
Many-to-One (*:1): This is the default type, and the most common. This means the column in one table can have more than one instance of a value, and the other related table, often known as the Lookup table, has only one instance of a value.
One-to-one (1:1): This means the column in one table has only one instance of a particular value, and the other related table has only one instance of a particular value. For more information about modifying cardinality, please see Power BI’s online documentation (https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/ ).
When the "Make this relationship active" option has been selected, the relationship serves as the active, default relationship. If there are more than one relationships between two tables, the active relationship provides a way for Power BI Desktop to automatically create visualizations that include both tables.
For more information about making a specific relationship active, please see Power BI’s online documentation (https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-create-and-manage-relationships/ ).
The new relationship will show up in your list of already existing relationships.
See a visual representation of relationships that have been created.
Use relationships in the "Relationship" view to visualise your creations. If you select fields from several related tables, their data will automatically be aggregated.
Here, for example, after merging the Cities and CRM tables, and then CRM and Profile, we get a dashboard listing the cities and regions of identified visitors, as well as profile qualification (beginner, average, expert).
"Report" view: format data
Build your report
You can reduce the "Visualisations" and "Fields" windows by selecting the small pointer arrow at the top right of each window. When you edit visualisations, you’ll also notice the presence of small arrows pointing up or sideways, which means you can expand or reduce these sections.
To create a visualisation, simply drag a field from the Fields list into the Relationship view. By dragging and dropping fields, you can format your data in a way that’s adapted to and recommended by Power BI.
Please note that the "Visualisations" window offers different types of graphs, so it’s possible to modify your graphs based on your needs.
Now that you have a relatively complete Power BI Desktop report, you can share it with other users of the Power BI service.
There are several ways to share your work:
- To publish on Power BI Online, load the file .pbix directly from Power BI Desktop
- Save the file .pbix and sen dit like any other file
Let’s start by publishing on the Power BI service directly from Power BI Desktop. In the home ribbon, select Publish.
You might be asked to log in to Power BI.
Once you’re logged in and the publishing process is complete, a dialog box will pop up will show successful action.
When you’re logged in to Power BI, the Power BI Desktop file that you’ve just loaded will appear in the Dashboards, Reports, and Data sets sections.
If any errors occur, please start again or try to publish your report using the second method.
Another way of sharing your work involves loading it from the Power BI service. The following link lets you access the Power BI service in a browser:
Click on Get data to start loading your Power BI Desktop report.
The Get data page opens, in which you’ll be able to select the source of your data. In the present case, select Get in the Files box. The Files view will appear. In our case, we’re going to select Local File.
Choose the file on your computer.
Once the file has been loaded, you can select it in Relationships, in the left-hand window of Power BI service.
Make the most of all your reports created in Power BI Online. To learn more about Power BI Online, please see the available user guide (contact the Centre Support).
Activating this partnership activates the export of Piano Analytics data into PowerBI. Only the data necessary for the proper functioning of the bridge are exported.
If you have already used PowerBI or you use it for different organisations at the same time, you may face an authentication error when trying to import an API URL as a Web source. It is because PowerBI is automatically trying to use the last credentials for the authentication, but the API Key do not have the rights to query the site of your API URL.
To fix this, you need to go to the data source settings, by clicking on the "File" tab, then "Options and settings > Data source settings", as below:
A pop-up will appear, from which you will need to clear all permissions related to our API. Once it's done, you can import your API URL and authenticate with the API Key of your choice.