Load your Meisterplan data in Microsoft Power BI so your reports look the way you and your management are used to.
- Loading Meisterplan Data in MS Power BI Desktop
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 MS 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 (MS Power BI service) and access them from anywhere.
Loading Meisterplan Data in MS Power BI Desktop
With our Reporting API, you can load your Meisterplan data in MS 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 MS 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.
To retrieve the data of an API endpoint, follow these steps:
- Open an empty MS Power BI file and save it to your computer. In the Home tab, click Get Data and select Web as data source:
- In the From Web window, select the Advanced option and enter the URL of the respective API endpoint (select this one for US systems and this one for EU systems) in the URL parts field. Under HTTP request header parameters, enter "Authorization" and next to it "Bearer <api-token>" with your API token:
- When you click OK, MS 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>"
For data security reasons, we recommend authenticating with the HTTP header as described above. However, for a quick start you can also add your API token as part of the URL using this structure:
"<URL endpoint>?access_token=<api token>"
The Reporting API endpoint returns data in a so-called JSON format. In the MS Power BI Power Query Editor, you can tranform the data into a table in three easy steps:
- Click List to transform the original JSON object into a list that contains the elements of the next level:
- 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:
- 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:
We recommend renaming the tables to be more descriptive. For example, the table below could be renamed to Milestones:
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 MS 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 MS 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
MS 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
MS 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 MS 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. MS Power BI will then adopt the new configuration.
In this article, you can find a description of how to upload reports to the web version (MS Power BI service).