Power BI Service

This document outlines the steps required to integrate Power BI with DataOS, ensuring a seamless connection to the Data Product Hub.

Prerequisites

Before proceeding with the data gateway configuration, ensure the following components are installed:

• Power BI Desktop installed on the system(version released after June 15, 2023).

• A Power BI service account.

• Npgsql version v4.0.10 (or earlier, up to v4.0.16).

• On-premises data gateway.

• Ensure that necessary ports (such as 6432) and services are whitelisted.

Installing the on-prem data gateway

1. Download the gateway installer from Microsoft.

2. Run the installer, keep the default installation path, accept the terms, and select Install.

   

3. Enter the email address for your Office 365 organization account, then select Sign in.

   

   You're now signed in to your account.

4. Select Register a new gateway on this computer > Next.

   

5. Enter a name for the gateway. The name must be unique across the tenant. Also, enter a recovery key. You'll need this key if you ever want to recover or move your gateway. Select Configure.

   

6. Review the information in the final window. Select Close.

   

Now that you've installed a gateway, install a npgsql.

Installing Npgsql for PostgreSQL connectivity

1. Download the Npgsql.msi installer from GitHub Make sure to download the v4.0.10 or earlier version.

2. Open the installer and click Next to begin the setup.

   

3. Accept the terms and conditions of use and click Next.

   

4. Ensure 'Npgsql GAC Installation' is selected. Verify that both the default-checked and unchecked files are selected before proceeding.

   

5. Click 'Install' to begin the installation.

   

6. Once completed, click Finish to exit the setup wizard.

Create and publish a Power BI Desktop file

Follow the below steps to sync the integrate the productaffinity semantic model with the Power BI and create a basic Power BI report Publish the report to the Power BI service to get a Power BI semantic model.

Prerequisite

To authenticate with DataOS when connecting via Power BI Desktop and Power BI Service both the DataOS ID and API key must be retrieved from the Profile section. These values act as the username and password during integration and should be copied and stored securely.

• DataOS Id (username): Click the profile icon (bottom-left corner) in the Data Product Hub and select Profile. The Id shown here serves as your DataOS username for authentication.

• DataOS API key (password): In the Tokens tab of the Profile section, click the eye icon to reveal and copy your API key, which is used as the password during authentication.

profile-section

Step 1: Navigate to the BI sync option

In Data Product Hub, choose a Data Product let's say product-affinity Navigate to the Access Options tab on the Data Product Hub application on DataOS. Within a specific Data Product, and under the BI Sync options, select 'Excel and Power BI'. Click on the download icon to download the .pbip file.

Step 2: Extract the files

After downloading, locate the ZIP file in file manager and extract it to the preferred destination.

The folder stores the main components of a Power BI project for syncing the semantic model (here Product Affinity). Here is the brief description of each:

This folder contains files that define the report's structure and visual elements.

• public_productaffinity.Report: This folder contains definition.pbir file related to the report definition in Power BI. It stores the report metadata such as the version and dataset reference in JSON format.

• public_productaffinity.SemanticModel: This folder contains files that define the underlying data model for Power BI project. The semantic model plays a crucial role in managing how Power BI interacts with data, setting up relationships, and measures.

  • definition.bism: Contains the overall definition of a semantic model and core settings. This file also specifies the supported semantic model definition formats through the 'version' property.

  • model.bim: The model.bim file is a JSON file that contains the Tabular Model Scripting Language (TMSL) definition of a Power BI semantic model. It's used to create a database from scratch by defining objects such as measures, tables, and connection sources.

• public_productaffinity.pbip: The .pbip file contains a pointer to a report folder, opening a .pbip opens the targeted report and model.

Step 4: Open the file in Power BI and connect

Open the public_productaffinity file in Power BI Desktop. A popup will appear prompting to enter 'DataOS username' and 'API key'.

After entering credentials, click 'Connect'. A confirmation popup will appear; click 'OK' to proceed.

Customize and create report as required.

Step 5: Publish the report to Power BI Service Account

On the Home tab, select Publish.

On the Publish to Power BI screen, choose My Workspace, and then select Select. Sign in to the Power BI service if necessary.

When the Success message appears, select Open productaffinity.pbip in Power BI. If prompted, sign in to Power BI service account to complete the process.

Configuring a data gateway for PostgreSQL interface

In Power BI Desktop, you connected directly to your on-premises PostgreSQL interface. In the Power BI service, you need a data gateway to act as a bridge between the cloud and your on-premises network. Follow these steps to add your on-premises PostgreSQL interface as a data source to a gateway and connect your semantic model to this data source.

Add a Gateway connection

To connect Power BI to the on-premises PostgreSQL interface via the data gateway, follow these steps:

1. In Power BI Service, navigate to the workspace where the semantic model has been published.

2. Locate the productaffinity semantic model in the content list.

   

3. From the File dropdown, select Settings.

   

4. In the Settings screen, locate and expand the Gateway and cloud connections section, and click the Create a connection button.

   

5. A new Connection screen will appear, here ensure On-premises is selected and complete the following fields(some fields are pre-configured):

   

6. Complete the following fields(some fields are pre-configured):

   • Gateway Cluster Name: Enter or verify the configured gateway name.
   • Connection Name: Provide a descriptive name (e.g., productaffinity-testing).
   • Connection Type: Ensure PostgreSQL interface is selected.
   • Server: Enter the PostgreSQL interface instance (e.g., tcp.dataos-training-dataos.app:6432).
   • Database: Specify the database name (e.g., lens:public:productaffinity).
   • Authentication: Select Basic authentication and enter the UserID and API Key (retrievable from the DataOS profile page).
   • General encryption: The encrypted connection should be set to 'Not encrypted'.

After filling out the required fields, click 'Add' to create the connection.

1. In the Settings screen, verify that the data gateway is running under the configured name (e.g., productaffinity-testing). Click Apply.

   

2. Click the public_productaffinity report icon to confirm the connection is active.

   

Supported data types

Category	Data Types	Support Status
Dimension	time, string, number, boolean	Supported
Measure	max, min, number, sum, count, boolean, string, time, avg, count_distinct	Supported
Measure	count_distinct_approx	Not Supported
Rolling Window	-	Not Supported (Power BI doesn’t support)

Important considerations

• In Power BI, measures typically have an 'm_' prefix to indicate they represent a measure. For example, a measure calculating total revenue might be named m_total_revenue.
• The connection is live, meaning any changes to the underlying data will be reflected in Power BI.
• When schema changes occur, such as CRUD operations (Create, Read, Update, Delete) on dimensions, measures, or other elements of the semantic model, a re-sync is required. To prevent losing previously created reports after the re-sync, download the new .pbip folder from the Data Product Hub, extract the zip file, and replace the existing folder with the new one.

Best practices

Adhering to best practices ensures the effective utilization of the Data Product Hub and maintains compatibility with the latest features and updates. Following these guidelines will help optimize workflow, enhance performance, and prevent potential issues.

File handling

Ensure that .pbip folders are fully extracted before opening them. Failure to do so may result in missing file errors, as shown below:

Data retrieval and field selection considerations

It is important to select fields from tables that are directly related or logically joined, as the system does not automatically identify relationships between tables through transitive joins. Selecting fields from unrelated tables may result in incorrect or incomplete results.

Connection reset

Upon encountering a 'connection reset' error during Power BI sync:

• Navigate to the Home tab in Power BI Desktop.
• Click the Refresh button in the Queries section.

This should resolve the error and restore the sync.

Unknown cluster

Upon encountering the error 'unknown cluster: ' as shown below, check if the cluster has been deleted. If deleted, redeploy the cluster. After redeploying the cluster, navigate to Power BI Desktop and click the 'Refresh' button to update the connection.

Limitations

• Power BI fails to handle special characters (e.g., &, %, #) when generating queries through the synced semantic model, causing errors in visualizations. Thus, it is best practice to address or remove special characters directly in the data itself.
• Power BI's Direct Query mode does not support creating custom dimensions and measures or querying the rolling window measure due to the lack of date hierarchy.
• DAX functions and Import query mode are not supported.

Governance of semantic model on Power BI Service

Data masking policies are enforced based on the user who creates and registers the gateway connection for the semantic model in Power BI Service.