Document toolboxDocument toolbox

Connecting to Microsoft Power BI through Spark SQL Connector

Owned by Sarabjeet Kaur
Last updated: Oct 03, 2024
4 min read

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

You can integrate Kyvos with Power BI using the Spark SQL connector. Using this connector, you can do the following:

Connecting to Power BI Desktop

To connect the Kyvos semantic model data in Power BI, perform the following steps.

  1. Open the Power BI Desktop application. Under Get Data, click the More option.

  2. Search and select Spark in the Get Data list. Click Connect.

  3. In the Spark Connection dialog box, enter the required details.
    Example: ServerName or IP :Port/kyvos/sql

    1. Choose HTTP Protocol.

    2. Choose Direct Query mode and click OK.

      image-20240709-100847.png

  4. The Import mode extracts the data from the Kyvos semantic model and pulls it into the Power BI Desktop.

  5. We recommend the Direct Query mode as it leaves data in the semantic model and sends queries to Kyvos to pull the needed information, so you can query data without any data movement time.

  6. Enter your Kyvos credentials and click Connect.

  7. In the Navigator, search and select a semantic model name and wait for the preview. After the preview completes, click the Load option to load the semantic model.

Publishing Workbook into Power BI Services

Once you have created a dashboard on the Power BI desktop, you can publish it on the Power BI services. For this, perform the following steps.

  1. On the Power BI Desktop select File > Publish > Publish to Power BI or select the Publish option from the ribbon.

  2. Enter your credentials in the Sign In dialog box to log into Power BI.

  3. Select a workspace to publish the workbook.

  4. When the publishing is successful, you receive a link to your report. Click the link to open the report on the Power BI site.

Configuring a Gateway

If your data source is directly accessible to the Power BI Services, you will need not to configure a gateway connection. Otherwise, you must configure the gateway as explained below:

  1. Step1: Download and Install the Gateway | Microsoft Docs

  2. Step 2: Configure the on-premises data gateway app | Microsoft Docs

Creating a connection using a Gateway

To create a connection using a Gateway for Spark SQL connector, perform the following steps.

  1. Sign in to Power BI services.

  2. Go to Settings > Manage Connections and Gateways.

  3. Choose the Connections tab and then select New.

    image-20241003-104950.png

  4. On the New Connection pane, provide the required details.

    1. Select the Gateway cluster name.

    2. Enter the Connection name.

    3. Select Spark from the Connection Type list.

    4. Enter the Server name. For Example: ServerName or IP :Port/kyvos/sql

    5. Choose HTTP Protocol.

  5. Choose an authentication method:

    1. Username and Password: Enter your Kyvos credentials.

    2. Choose the required Privacy level.

    3. Select Create.

Configuring published semantic models

To configure published semantic models, perform the following steps.

  1. Go to published semantic model > Settings > Gateway and cloud connections. Navigate to the Gateway connections section, select the connection if already created. Otherwise, select the gateway, in the Actions column, click the View Datasources button.

  2. The Datasource included in the semantic model section is expanded:

    1. If you view the Add to Gateway link, click the link and it will display the New Connection pane. Perform the Step 4 and Step 5, as mentioned above in this section.

    2. If connection is already created in the gateway, from the Maps to list, select the created connection. Otherwise click the manually add to gateway option from the list and then perform Step 4 and Step 5, as mentioned above in this section.

    3. Select Apply.

    4. Now open the published Power BI workbook.

Power BI Spark SQL -Known limitation

You may encounter the following limitations when performing data analysis and visualization tasks after setting up Spark SQL connectivity in Power BI. These will be fixed in the upcoming versions.

  • You cannot apply a measure filter if that measure is not part of the Kyvos visualization. To do so, you need to create a calculated measure using the desired measure, and then you can use that calculated measure in the filter. This is required because when we place the measure in the filter, it sends an unaggregated query to the Kyvos. 

  • Kyvos does not support conditional formatting using the 'field value' format style 

  • Following Kyvos features are not supported: 

    • Scope Script

    • Unconditional Masking

    • Default Member Script

  • Key influencer is not working. This is a known limitation of Power BI on direct query. For more information, see Microsoft Power BI documentation. 

  • Conditional formatting for the field value and icons is not working due to Power BI handling issue.

  • Sorting of month is not displaying as per the nomenclature because Power BI itself handles sorting.

  • Power BI generates queries in a such way due to which a user might get data inaccuracy or may get extra records in the case of topN query.

Related Topics

Copyright Kyvos, Inc. All rights reserved.