Load your Meisterplan data in Microsoft Power BI so your reports look the way you and your management are used to.

• Prerequisites
• Loading Meisterplan Data in Microsoft Power BI Desktop
  • Data Structure of the API Endpoints
  • Get Data
  • Transform Data
  • Further Details and Notes
• How to Upload Your Report to the Cloud
  • Refreshing Cloud Reports
  • Configuring Automatic Refresh
• How to Embed Your Report in Meisterplan

Prerequisites

Ensure that the following prerequisites are met to access Meisterplan data with Microsoft Power BI Desktop:

• Create an API token in your Meisterplan user profile and save it to a secure location.
• Install the current Microsoft Power BI Desktop version on your computer. Only the desktop version offers the complete range of functions. After creating your reports, you can upload them to the web version (Microsoft Power BI service) and access them from anywhere.

Loading Meisterplan Data in Microsoft Power BI Desktop

With our Reporting API, you can load your Meisterplan data in Microsoft Power BI to create personalized reports. For the most common report types, you can use our report templates and customize them. If you prefer to create your own reports, you can find a description of the required steps below.

Data Structure of the API Endpoints

The retrievable Meisterplan data is distributed over several endpoints in the Reporting API, e.g., projects, milestones or resources. You can find all endpoints together with a description in the API documentation (select this one for US systems and this one for EU systems). Each endpoint provides thematically connected data (e.g., projectname, projectstart) which can be transformed into a table. You can combine the data of different endpoints. Most combinations can be created in Power BI with these fields:

• scenarioProjectId: This field identifies a project in a scenario; it is relevant for all tables that contain both project- and scenario-specific data.
• resourceId: This field identifies a resource or role. It can be used to connect allocations, capacities, resource data and the OSPs of resources.
• projectId: This field identifies a project and can be used for scenario-independent project properties.

Capacities (capacitySlices) are a special case. They have no dependencies on single projects but are specific to a resource and a period.

Please note: We recommend to only retrieve and combine relevant data to minimize query and update efforts. Because of the data structure, it is not always useful to create a "flat" data model out of all available information. Milestones with custom dates, for example, may not match the time grid of allocations.
You can also limit the amount of data by using query parameters such as scenarios or includeFields.

Get Data

To retrieve the data of an API endpoint, follow these steps:

1. Open an empty Power BI file and save it to your computer. In the Home tab, click Get Data and select Web as data source:
   
2. In the From Web window, select the Advanced option and fill in the following fields:
   
   1. In the URL parts field, enter the URL of the respective API endpoint. You can find the endpoints and their URLs in the API Documentation (select this one for US systems and this one for EU systems). Add a question mark at the end,
   2. In the field below, enter the date parameters. Without date parameters, only the data for the last 30 days will be loaded in the report.
   3. Under HTTP request header parameters, click into the box and type in the word "Authorization". Please note: You cannot select this from the dropdown but you need to manually type it in.
   4. In the next box, paste "Bearer <api-token>" with your API token.
      
3. When you do this for the first time, the Access Web Content window may open. Always select Anonymous here, because the API token is sufficient authentication and other options may cause problems at a later time.
   
4. When you click OK, Power BI retrieves the data and the Power Query Editor window opens where you can transform the data into a table.

Filtering with Query Parameters

Query parameters are filters that define which endpoint details should be retrieved. This could be, for example, the date range with the query parameters startDate and finishDate, or the required scenario with the parameter scenario. If no filters are defined, default values are used.

This is how you add filters to your query:

"<URL endpoint>?<query parameter>=<value>"

You can combine two or more filters with "&":

"<URL endpoint>?<query parameter>=<value>&<query parameter>=<value>"

For example:

"https://api-reporting.eu.meisterplan.com/v1/projects?startDate=2020-01-01&finishDate=2020-12-31&scenarios=planOfRecord,yourScenarioName"

Microsoft Power BI for On-Premise
You can also create Power BI reports with On Premise systems. To do so, you need to change the URL of each endpoint to read as follows:
"https://<internal domain>/reporting-api/v1/<endpoint>"

Transform Data

The Reporting API endpoint returns data in a so-called JSON format. In the Power BI Power Query Editor, you can tranform the data into a table in three easy steps:

1. Click List to transform the original JSON object into a list that contains the elements of the next level:
   
2. Click To Table and accept the default settings in the new window by clicking OK. The list is then transformed into a table, and each line contains a JSON object with the values of the respective columns:
   
3. Click the double arrow icon on the upper right of the column and then click OK. The available fields are extracted and transformed into table columns. You can uncheck the Use original column name as prefix box as this is not necessary with this data:
   

If you want to undo one or more of the steps described above, you can do this from the Applied Steps list on the right:

Your data is now ready for further processing. Click Close and Apply in the upper left to load the data and return to the main screen.

Repeat these steps for each endpoint whose data you want to load in Power BI. You can find all API endpoints with a short description and its respective URL in the API documentation.

Further Details and Notes

The Portfolio Report Template as an Example

The Portfolio Report Template depicts typical reports for different kinds of portfolio overviews and dashboards. You can also use it as an example for connecting Power BI with the Reporting API. For example, see the data transformation, the conjunction of tables or the use of dynamic filter parameters.

All user-specific data is put in as "parameters" that can be easily adapted in the UI. Because of this, the URLs do not need to be modified by the user.

Please note that the Portfolio Report Template is optimized for the sample data of Meisterplan trials. Other datasets or structures may require some adjustments.

Types of Data

Power BI interprets all fields as text fields. It may be useful to change the data type for certain fields, especially date and numeric fields (e.g., allocations and finances). To do this, go to the Data view:

In addition to changing the data type you can also define the format in which you want to display the values (e.g., date format or number of decimal places).

Defining Relationships between Tables

Power BI automatically compares field names in tables and tries to find relationships between them. Usually, this works accurately, but it is still advisable to check the set relationships. On the left side of the window, select the Model icon to view and, if necessary, adjust the data model:

Meisterplan Custom Fields

Meisterplan users with the Manage Project Fields, General Settings and Project Settings right can add or delete project fields, and users with the Edit Resource Pool and Absences right can add or delete OSP units.
All of this impacts the fields that are available in the Reporting API as well as their names.

When you change fields, the Reporting API adopts the changes, but Power BI may not always recognize this during the data transformation. If this causes error messages or if new fields are not recognized, simply execute the steps for transforming data again. Power BI will then adopt the new configuration.

How to Upload Your Report to the Cloud

To make your report available in the web version of Power BI (Microsoft Power BI service), follow these steps:

• In the top left of the MS Power BI window, click File and select Publish.
• Click Publish to Power BI and in the new window, select My workspace. Your reports will then be accessible in the web version of MS Power BI web.

Refreshing Cloud Reports

When you upload a report to the online version, MS Power BI stores a data extract. This extract can be refreshed manually or automatically.

When you click Refresh in the toolbar above the report, only the view is refreshed, not the data extract from Meisterplan:

To refresh the data extract from Meisterplan, open My Workspace and select the Datasets tab. Click the Refresh symbol of the report you want to refresh. This refreshes the data of the Meisterplan extract:

If any problems occur, make sure your credentials and settings are configured as described under Step 2 in the following section Configuring Automatic Refresh.

Configuring Automatic Refresh

To save time, you can configure an automatic refresh of a report in MS Power BI service:

1. Open My Workspace and select the Datasets tab. Click the three dots after the report you want to refresh automatically and select Settings:
   
2. Expand the Data Source Credentials section and select Edit Credentials (there is one entry per API endpoint):
   
3. Select Anonymous as authentication method and Organizational as privacy level:
   
   It may be useful to check the Skip Test Connection box, because this can cause errors.
4. Select the frequency in which your data extract is refreshed under Scheduled Refresh:
   
5. You can edit the parameters of your data extract under Parameters:
   

How to Embed Your Report in Meisterplan

In the Meisterplan Reports view, you can embed your report as a Weblink report. Learn how to create the required link in the Microsoft Power BI documentation.