Power BI Connector
The CoPlanner Power BI Connector is the perfect tool for CoPlanner PowerUsers with Power BI knowledge to distribute innovative dashboards with current CoPlanner data in the company. The CoPlanner Power BI Connector was developed to be able to obtain data from the CoPlanner without having to fetch it directly from the SQL database. With the connector it is possible to import tables, dimensions, subsets and functions from CoPlanner models into Power BI (no live connection) from any number of CoPlanner server instances.
This connector was developed following Microsoft's guidelines for creating a custom connector for Power BI.
Prerequisites
- Power BI Desktop is installed
- CoPlanner Server runs at https
- CoPlanner license must have Power BI enabled
Installation
The installation for the desktop version can be installed via the CoPlanner Power BI connector.msi.
This saves the "CoPlanner.pqx" connector to the directory C:\Users\<username>\Documents\Power BI Desktop\Custom Connectors and sets registry keys so that the connector is considered trustworthy.
The CoPlanner.pqx is also made available without the installer for use with the gateway.
Usage
In the "Retrieve data" menu item there is a connector called CoPlanner under "More...", provided that it has been installed correctly.
If you select this, you must enter the URL under which the server can be reached (e.g. https://myservername:4443/coplanner) and you can optionally enter the language.
If you connect to this URL for the first time, you will get a dialog where you can decide whether you want to log in via Windows authentication or via the CoPlanner user. This user must have been created and authorized accordingly in CoPlanner. If you want to authorize access to the data according to the CoPlanner when you have published the report, you should choose a user who has all rights, since data can only be removed later and cannot be added.
If you access this URL again, you will no longer get this dialog. If you want to change the user, you have to adjust this via the data source settings.
After it has been accessed,the navigator window opens, in which date tables, functions and tables, dimensions and subsets are listed, provided they have "Available for analyzes (Power BI)" checked. Here you select the relevant data that you want to display in this report and load it.
If you have forgotten something or simply want to expand the report, you can simply do this via the last used sources.
In general, it is recommended to restrict not only the objects, but also the fields of the objects to the required fields via the flag "Available for analyzes (Power BI)" in the object administration, since then only the relevant data is fetched and it is therefore more efficient from a performance point of view.
Manage relationships
When using more than one table, that also use the same dimensions, it is important to properly maintain the relationships. If these are properly maintained, it is possble to apply dimension filters in the report.
These connections are set in the creation process, but must then be checked to ensure that all relationships have been created and that they are correct.
Create hierarchy
In contrast to the CoPlanner, Power BI expects that all sheet elements are on the same level. The connector delivers the dimensions flattened accordingly and, if a leaf element is not on the right level, adds further levels with the name of the leaf element, otherwise Power BI would add further levels here that have no name.
Dimensions are delivered with fields Level_0 to Level_x. To create a hierarchy from the top level, right-click on Level_0 to open the context menu and choose "Create Hierarchy". If you then click on Level_1, for example, you can add this entry to the hierarchy via the context menu. All other level elements can then simply be added in ascending order.
In the data area you can then sort each level according to its corresponding order column. So Level_0 is sorted by Order_0, Level_1 by Order_1, etc.. This gives us a hierarchy and sorting like in CoPlanner.
For an easier overview, you can simply hide the level and order columns, as they are otherwise no longer needed.
PICTURES NEED TO BE ADDED HERE
date tables
In order for Quickmeasure or time intelligence functions to work in Power BI, a date table must be created according to Power BI specifications. Power BI date tables must have certain properties, such as a column of the data type date or date/dime must have unique values, no empty values,... The CoPlanner Connector provides valid Power BI date tables based on CoPlanner time dimensions.
In order to be able to use the date table created by the connector, it must be marked as a date table in the modeling via the context menu. In addition, a mapping must then take place via the date field of the normal time dimension. Once this has been done, you can use quick measures, e.g. to create the percentage of change compared to the previous year with just a few clicks, without having any DAX knowledge.
function "optimize data"
Tables, dimensions and subsets can be loaded via the navigator. However, we can have two issues here:
1.) All plan data is loaded independently of the plan. This can lead to performance problems and is unnecessary if I only want to look at one plan anyway and not plan comparisons.
2.) I want to use a subset. However, CoPlanner tables themselves have no mapping to the subsets. In order for this mapping to be generated and the data to be calculated correctly, the data must be specially loaded.
Both of these cases can be mapped accordingly using the "Optimize data" function.
Limit the data to one plan only
To do this, select the Optimize data function in the navigator and do not select "Load" but "Transform data". The Power Query Editor opens, in which the parameters can be defined.
First, the WebAppUrl of the CoPlanner and the table name must be specified.
If it is a plan table, you can now restrict the plan via the scenario if you want to do that. A constraint has advantages in speed. However, if you want to compare several plans with each other, it is not recommended.
| Hint You can define parameters in Power BI, which you can then use for these queries. It is recommended to create for the URL of the server and in the case for the scenario. This allows you to adjust all queries at the same time simply by changing the parameter. |
Specify the scenario parameter or scenario name for the constraint.
If you also want to link the table to a subset, you can also specify your subset mapping here (see Use of subsets) and select a language.
Once all parameters have been assigned, click on "Call".
A query opens with a preview of the result. This can be given a meaningful name and accessed in the visuals. In the Power BI model, the relationships should be maintained automatically, but you should always check them.
Example of a query with a Power BI parameter named ServerUrl, in which the URL of the CoPlanner server is stored, on thebalance sheet_PLAN table for the final budget scenario:
= #"Optimize data"(ServerUrl, "Bilanz_PLAN", "Budget final", null, null)
ServerUrl has the quotes removed here because it is a parameter.
Usage of subsets (option 1)
CoPlanner tables themselves have no mapping to the subsets. In order to generate this mapping and to calculate the data correctly, the data must be specially loaded.
To do this, select the "Optimize data" function in the navigator and do not select "Load" but "Transform data". The Power Query Editor opens, in which the parameters can be defined.
First, the WebAppUrl of the CoPlanner and the table name must be specified. Next, the scenario for plan tables can optionally be restricted.
In order to be able to implement future extensions, the subset mapping is a listing. The Power BI UI always wants to get listings from a column. Do not fill this parameter via "Select column...", but click directly on "Call" and define the mapping via the toolbar.
In the Subsetmapping parameter, define the column name of the base dimension in the table with the desired subset using the following syntax:
{"COLUMNNAME_OF_BASEDIMENSION_IN_THE_TABLE=SUBSET_NAME"}
Example of a query on the Bilanz_PLAN table with a mapping on the Sub_Bilanz subset, which was mapped using the Balance sheet structure column.
= #"Optimize data"("https://meinserver:4443/coplanner", "Bilanz_PLAN", null, {"Bilanz-Structur=Sub_Bilanz"}, null)
You can give this query a meaningful name and access it in the visuals.
| Hint Only one subset mapping can be defined per query on the table. |
| Hint This query only returns correct data if the subset itself is on the axis or in a filter, or the elements do not occur more than once in the subset. |
Funktion Subsetzuordnung
Available from CoP 11 HF 1.0.
CoPlanner tables themselves have no mapping to the subsets. In order to generate this mapping and to calculate the data correctly, the data must be specially loaded.
To do this, select the "Subset assignment" function in the Navigator and do not select "Load" but "Transform data". The Power Query Editor opens, in which the parameters can be defined.
First the WebAppUrl of the CoPlanner has to be specified, then the base dimension of the subset and the name of the subset.
The query is now created via "Call". Give the query a name. Now the correct relationships have to be activated and adjusted in the modeling. The subset should only have one connection to the subset mapping table (attention, this is usually deactivated at the beginning), the connection from the subset directly to the dimension should be removed and the relationship between subset mapping and dimension must be switched to cross-filtering "Both".
PICTURES NEED TO BE ADDED HERE
Berechtigungen verwalten (Funktion "Security (dynamic RLS)")
Permissions to the data source CoPlanner are managed under the data source settings (user/password). Here you can set whether these are saved locally or on the report.
For the correct authorization of the data as in the CoPlanner, you have to carry out further steps. This is because the data in the report is imported and does not need to be reloaded by another user. This could allow the user to see things they are not allowed to see. The data authorizations can be set up using the "Security (dynamic RLS)" function from the CoPlanner Connector. To do this, the report builder importing/updating the data should have all rights, as the RLS (row-level security) can only further restrict the existing data. Authorizations are assigned to master data in CoPlanner and in Power BI. In Power BI you also have the option of OLS (Object level security), which is not available in CoPlanner. Therefore, it is not further described here.
Note The authorization only works if you log in with a Power BI account, which is stored as an email with the corresponding user in the CoPlanner user administration and the user is assigned the role in Power BI.
Example: Not all report recipients are allowed to see all product data. The rights for the product dimension should be applied to the users COPLANNER\user1 with the stored e-mail address user1@coplanner.com and COPLANNER\user2 with the stored e-mail address user2@coplanner.com created in CoPlanner.
Open the navigator and select the Security (dynamic RLS) function. Then do not click on "Load", but on "Transform data".
Then enter the WebAppUrl of the server in the parameters, the dimension/subset for which the authorization should be drawn, and click on "Select column..." for the user name. Since you can specify a list of user names downstream, Power BI expects a selection here. Just select anything, because that will have to be corrected in the next step anyway. Then click on "Invoke".
A new query is created. In the formula window you will now find something along the lines of "= #"Security (dynamic RLS)"("https://myserver.coplanner.com:4443/coplanner", "Products", MyQuery[MyField])"
This query must now be adjusted so that the list of users for whom the permissions should be given is specified as the third parameter. The users are to be specified in curly brackets in single quotes, separated by commas. In our case, we generate the following query here:
= #"Security (dynamic RLS)"("https://myserver.coplanner.com:4443/coplanner", "Products", {"COPLANNER\user1","COPLANNER\user2"})
Under the properties we can assign a name for the query, e.g. Products_RLS.
Note The query for the dimension/subset name is case sensitive.
Note Only select the users for whom security is really required, since any additional data can impair performance.
Note There is also the option to manage parameters in Power BI. This can be used, for example, to save the server's WebAppUrl and then access this parameter.
If a parameter is defined, you can access the query via the parameter name without inverted commas.
In the modeling, the result of the query must be linked to the dimension. To do this, we create a relationship between the ID of the dimension (Products_ID) and the DimensionElement of our query.
It is important that the cross filter direction is set to Both and the security filter is applied in both directions. In our query, the DimensionElement can appear more than once if we're fetching security for multiple users, so n is to one (1) product_id.
In order for the RLS to really work for someone, at least one role must be created in Power BI.
With this role, a filter on the MailAddress must be set on the queries that were created using the Security (dynamic RLS) function (in our example Products_RLS). This should then be defined as follows in the table filter DAX expression: [MailAddress] = userprincipalname()
If the report was then published, you have to add the users as members of the group to the dataset of the report via the context menu entry "Security" so that the security takes effect.
Testen der Row Level Security in der Desktopversion
The row level security can also be tested without another user in the desktop version. Select "View as" in the menu and enter the Power BI user you want to test and select the role you created. If the user has limited rights to dimensions and these were also modeled in Power BI, then they must now be dragged.
Veröffentlichen von Berichten/Dashboards
Reports/dashboards are released with the Publish button.
Then the dataset and the visualization are transferred to the Power BI Service (Cloud). The dashboards are distributed to the users via a link/frame/Power BI Online. They need the rights to do this. A file share would be possible, but Power BI Desktop should only be used to create the reports. Published CoPlanner data can be updated from the cloud using a gateway. The Custom Connector must be stored in this gateway so that the data can be updated. In order for Row Level Security to work properly, the user who is stored at the data source in the cloud should have all rights in CoPlanner and be a CoPlanner user (i.e. Basic authentication method).
Tipps um Berichte/Dashboards optimieren
- Are non-relevant fields/tables/dimensions set to no for "Visible for analysis (Power BI)"? Delete unused tables/queries. By default, Power BI tries to recognize time information from columns and to offer an internal time dimension for it. This can cost performance, so this function should be disabled. See Load Data -> Time Intelligence -> Auto. Date / time Restrict plan tables to individual scenarios unless they are needed for comparisons Only load Row Level Security for users who actually have access
Caches leeren
Under the Power BI options under load data there is the possibility to delete caches. This can be useful if you're having performance issues or other unexpected problems with things that were already working.
FAQs
My node is shown in the hierarchy down to the level of the leaf elements even though it has child elements. Why is that?
This behavior is seen when there is a value on the node itself. Power BI does not behave like the CoPlanner here, where the value is simply summed up at the node element, but there must also be a branch for the value from the lowest level onwoards.
I get an error message on the server: Too many login attempts
This is the case when too many queries are generated, which are then run against the CoPlanner server. The permitted value can be configured via the svrconfig.xml with the parameters LogonMaximumPerTimeframe and LogonMaximumTimeframeInSeconds.
I can't see a table/dimension/subset in Power BI, but it is available in the model.
The object administration in CoPlanner can be used to control which tables, subsets or dimensions are visible in Power BI. In addition, fields, lookup tables, dimensions and calculated fields can be hidden here for each object. This is controlled via the "Available for analysis (Power BI)" setting.
Where is my CoPlanner data when I use it in Power BI
If a dashboard is created with Power BI Desktop, the loaded CoPlanner data is in the .pbix file. If the dashboard is published in the cloud, the data set with the CoPlanner data is also moved to the cloud and is therefore located in a Microsoft data center. This can be configured during installation, please ask your administrator about this.
How can my data be updated automatically
If the data is in the cloud, you can specify an update job on the data source, like for example you can specify a nightly update job from the Coplanner data.
Is there a way to distribute my reports without my data going to the cloud?
There are options, but they implicit some limitations.
Version 1:
All users own Power BI Desktop and access it through a file share.
Variant 2:
Power BI is also available via SQL Server Reporting Services. With this variant, however, you cannot update the CoPlanner data without re-uploading the report, since custom connectors are not supported here, which is why this method is rather theoretical. In addition, you need your own Power BI desktop version for this variant.
