Introduction

Learn how to consume data from your Corporate Memory Knowledge Graph with our Microsoft Power-BI-Connector.This manual and tutorial describes how you can consume data from your knowledge graph in Microsoft Power BI through our Corporate Memory Power-BI-Connector. 

Power BI is a business analytics service by Microsoft. It aims to provide interactive visualizations and business intelligence capabilities with an interface simple enough for end users to create their own reports and dashboards. Power BI can be obtained from the official Microsoft page  and/or in the Windows Software Store.

The latest (unsigned) version of our Power-BI-Connector is available from its source repository a version signed by eccenca is available with each Corporate Memory release.


Installation

  • Download the .pqx or .mez file from the locations linked above.
  • Move the file into the folder Documents\Power BI Desktop\Custom Connectors .
    • Create the folder if it does not exist.
    • In case you are running Windows on Parallels Desktop: Do not use the Local Disk\Users\UserName\Documents folder but your shared folder with macOS.
  • Register the Thumbprint (for .pqx) or setup PowerBI Desktop to allow any 3rd party connector (for .pqx or .mez) (we recommend to register the Thumbprint)


  • In order to allow the eccenca Corporate Memory Power-BI-Connector in your Power BI Desktop installation you need to register the Thumbprint of the file signature in the windows registry.
  • Cf. official Microsoft documentation: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-trusted-third-party-connectors
    • The registry path is HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Power BI Desktop . Make sure the path exists, or create it.
    • Add a new value under the path specified above. The type should be “Multi-String Value” ( REG_MULTI_SZ ), and it should be called TrustedCertificateThumbprints 
    • Add the thumbprints of the certificates you want to trust. You can add multiple certificates by using “\0” as a delimiter, or in the registry editor, right click → modify and put each thumbprint on a new line.
  • (Re-)Start Power BI Desktop

if you wish to automate this setup you can use the reg windows command line tool to make this entry like:

REM list existing entries in Power BI Desktop > TrustedCertificateThumbprints
reg query ""HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Power BI Desktop" /v TrustedCertificateThumbprints

REM add eccenca Corporate Memory Power-BI-Connector Thumbprint
reg add "HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Power BI Desktop" /v TrustedCertificateThumbprints /t REG_MULTI_SZ /d FB6C562BD0B08107AAA420EDDE94507420C7FE1A
POWERSHELL
  • in case you are using the .mez (works for .pqx file too) file or simply want to trust any third party connector extension
  • (Re-)Start Power BI Desktop, go to File → Options and settings → Options → Security
  • Under Data Extensions, select (Not Recommended). Allow any extension to load without validation or warning.
  • Select OK, and then restart Power BI Desktop.
    tutorialCMEMPowerBIConnector1.png

(Optional) SSL Settings

In case your Corporate Memory instance run with a self signed SSL certificate you need to configure Power BI for this.

  • Go to File → Options and settings → Options → Security.
  • Uncheck Enable certificate revocation check.
  • (Re-)Start Power BI Desktop

Tutorial-CMEM-Power-BI-Connector-04.png

Add a Data Source

Use the Power-BI-Connector to login with your Corporate Memory Instance:

  • Open Power BI Desktop
  • Click Edit Queries → New Source (or directly Get Data)
  • In the dialog search for eccenca Corporate Memory, which is listed in the Database category
  • Select the connector and click Connect 
  • Read and accept the 3rd party connector notification
    Tutorial-CMEM-Power-BI-Connector-08.png
  • In the following dialog you need to specify the connection and information and access credentials, ask your Corporate Memory administrator for assistance if you miss any of the requested details. You have the option to use username + password or a client secret for login. In case of a custom setup is used advanced configuration can be provided:

In order to use username +  password based login you need to fill the details shown below:

  • First Step
    • Corporate Memory Base URI
    • Grant type = password
    • Client ID
  • Second Step
    • Password / Client Secret

Tutorial-CMEM-Power-BI-Connector-09.png Tutorial-CMEM-Power-BI-Connector-10.png

In order to use Client Secret based login you need to fill the details shown below:

  • First Step
    • Corporate Memory Base URI
    • Grant type = client_credentials
    • Client ID
  • Second Step
    • Username
    • Password / Client Secret

Tutorial-CMEM-Power-BI-Connector-13.png

In case you installation uses a custom service endpoint layout the individual URIs for DataPlatform and Keycloak can be configured individually. The configuration keys are the same as for cmemc.

  • The following configuration parameter can be provided:

    • DP_API_ENDPOINT - specifies the DataPlatform URI

    • OAUTH_TOKEN_URI - specifies the keycloak token URI

    • SSL_VERIFY - can be used to set certificate verification to False 
      Tutorial-CMEM-Power-BI-Connector-24.png

    • in case a Corporate Memory Base URI is configured too, the values from the Config ini section take precedence

Get Data

With the eccenca Corporate Memory Power-BI-Connector you can load data from SELECT queries stored in the query catalog of Corporate Memory. You can use queries without or with placeholders. The steps are different depending if your query uses placeholder:

  • SELECT queries that use no placeholders are shown with a table icon (e.g. ).
  • When selected a preview will be loaded.
  • Check the one(s) you want to load and click OK.
  • The tables will be added to your list of queries and to the fields inventory in Power BI
  • Start using your data in transformations, dashboards and analytics

Tutorial-CMEM-Power-BI-Connector-11.png

  • SELECT queries that take placeholder arguments are shown with a function icon (e.g. ).
  • You need to be in Edit Queries mode in Power BI so you can enter the required query parameter.
  • Check the one(s) to be added.
  • Power BI will add the selected query as a query entry.
  • When you select the query entry you can now fill the Parameter and Invoke the data loading.
    Tutorial-CMEM-Power-BI-Connector-16.png
  • This adds a new entry to the list of Power BI queries, which contains the actual data you requested. The new entry will be named “Invoked Function”.
  • It is recommended to rename this automatic generated name to a more speaking one. Right click on “Invoked Function” or select “Invoked Function” and press F2.
  • Rename the table (e.g. to “_search via regex match”). Click “Close & Apply“ to save changes.
    • Hint: You can call the function multi times with different parameter values to get different result tables into Power BI.
  • Start using your data in transformations, dashboards and analytics

Delete a Data Source

  • You can delete tables in the Edit Queries view or the Fields inventory by right clicking the respective entry and selecting Delete
  • Repeat this step for all tables that you want to remove, you can remove sources that take parameter in the same way.
  • If you also want to delete the credentials of the Data Source. Click “Data source settings” in the menu bar.
  • In the Data source settings select the Data Source from the list and click “Clear Permissions”. Click “Delete” to confirm. Click “Close & Apply” to save changes.