Connecting to Microsoft Power BI through Spark SQL

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

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

To retrieve 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.

      Open

      

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.

Points to remember

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

   1. Step1: Download the Gateway Installer

   2. Step 2: Install an on-premises data gateway | Microsoft Docs

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

2. While creating a semantic model in Power BI similar to a Kyvos semantic model design, remember:

   1. Power BI lists all the attributes/levels/measures as Fields, so you cannot differentiate between the dimensions and measures defined in the semantic model.

   2. You cannot organize fields as attributes and hierarchies inside a group or folder to represent a dimension.

   3. You cannot define default summary functions for measures in Power BI.

3. Once you have connected to Kyvos semantic models through Power BI, you can Creating Kyvos Semantic Layer in Power BI and publish.

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.