You can translate the document:

Overview

In addition to the native Denodo SQL based interface in PowerBI, it is now also possible to query Denodo in natural language via a PowerBI Widget. This is achieved by calling the Denodo AI SDK API with PowerBI widget. The Denodo AI PowerBI Widget allows users to gain deeper insights from the reports they are viewing and to get insights based on additional data proxied by Denodo. It enables users to ask complex questions about their current report or explore the entire dataset behind it, providing a comprehensive understanding beyond the visible dashboard data.

In this document we will review the integration architecture and detail how to implement your own PowerBI Widget leveraging Denodo AI SDK API.

Architecture

The widget is based on the Power BI SDK to create custom visualizations that can integrate with Denodo AI SDK API. This integration allows direct querying and interaction with Denodo’s AI capabilities within Power BI dashboards.

Key Components

The Power BI widget integrates with the Denodo AI SDK by embedding API calls, dynamically contextualizing queries, and customizing parameters to ensure precise and relevant insights. User queries are sent via HTTPS API calls to the Denodo AI SDK REST API endpoint, and responses are displayed directly in the dashboard.

The Denodo AI SDK offers a variety of services and parameters to cater to individual requirements. For example, it allows leveraging the entire data products hosted on the Denodo Platform or specifying particular views to funnel the responses to a specific data product scope. It can also dynamically adapt to user-specific dashboards, incorporating inputs like the tables used and filters to tailor responses to each scenario. To support these capabilities, the Power BI widget should include input fields, different checkboxes designed as well as response areas to accommodate different user requirements. This flexibility enables users to configure parameters, define the scope of data queries, and dynamically adjust inputs based on the dashboard context.

The following diagram illustrates the relationship between the Denodo AI SDK and the Power BI widget. The two components can run on separate servers, with the Power BI widget connecting to Denodo’s AI SDK Endpoints over secured HTTPS-enabled API calls. The widget's components are highly customizable, allowing for tailored input sessions that capture user-specific requirements and pass them as parameters to the AI SDK endpoints.

Prerequisites

To create your own custom Power BI widget that leverages the Denodo AI SDK API you need to follow  the steps below.

Step 0: Software requirement

  1. Denodo AI SDK can be obtained from Github here and it is also available as a Denodo Connect from the Denodo support site.
  2. pbiviz the Microsoft’s command-line tool to create widgets for Power BI.
  3. Get the Denodo AI PowerBI Widget sample code from Github here.

Step 1: Install Node.js

  1. Open your web browser and navigate to the Node.js website.
  2. Download the latest recommended MSI installer for your system.
  3. Run the installer and follow these steps:
  • Accept the terms of the license agreement.
  • Use all default settings.
  1. Restart your computer after the installation is complete.

Step 2: Install pbiviz

  1. The pbiviz tool compiles the visual source code for Power BI projects into a zipped package (pbiviz file) containing all scripts and assets.
  2. Open Windows PowerShell (or any terminal with administrative privileges).
  3. Enter the following command to install the latest version of the pbiviz tool globally:

npm i -g powerbi-visuals-tools@latest

Step 3: Create a New Power BI Visual Project

  1. Navigate to the directory where you want to create your project.
  2. Run the following command to start a new project:

pbiviz new your-project-name

  1. This will create a new folder (your-project-name) containing the necessary files and structure for a Power BI visual.

You now have the SDK framework set up, enabling further development to customize and adapt your Power BI visuals to meet your specific requirements.

Walk Through the Code

The entire Power BI widget deployment is built using TypeScript, and the process involves the following main steps:

  1. Modify capabilities.json file:
  • Add any columns or tables from Power BI that you want to map to your widget into this file.
  • Enable cross-server API calls in this file to connect to the AI SDK.
  1. Modify visual.ts file:
  • Modify the visual.ts file to include the components required for your widget.
  • Implement the logic to send API calls to the AI SDK, ensuring they align with the widget's functional requirements.
  1. Deploy:
  • Use the command pbiviz package to build the final .pbiviz file.

1. Modify capabilities.json file

The capabilities.json file serves as the bridge between the Power BI widget and the dashboard. Within this file, you can define fields that allow users to drag and drop columns for the widget to utilize. Additionally, since the widget interacts with the AI SDK, which may be hosted on a separate server, you must configure the file to enable cross-server API access for the required API calls.

"dataRoles": [

        {

            "name": "used_tables",

            "kind": "Grouping",

            "displayName": "used tables"

        }

    ]

"privileges": [

        {

            "name": "WebAccess",

            "essential": true,

            "parameters": [

                "https://host:port/"

            ]

        }

    ]

2. Modify visual.ts File

In the visual.ts file, implement all the required functionalities for your customized widget, including a main page with key components such as a question input field, a submit button, and response areas to display API results.

  • Add event triggers to monitor user interactions, such as an event for the submit button that triggers a call with the user's query to the Denodo AI SDK API Endpoints.
  • Include logic to format user inputs and construct HTTPS API calls, ensuring proper error handling for connectivity or response issues.
  • Additionally, update the widget's UI dynamically to display the responses or error messages from the API call.

The update function in the visual.ts file ensures that the Power BI custom visual stays updated by rendering and refreshing components based on data, settings, or user interactions. It handles changes like resizing, updating the data model, and applying configuration updates, ensuring the visual remains dynamic and responsive. Below is a sampled update function:

public update(options: VisualUpdateOptions): void {

  // Step 1: Create UI components

  createQuestionInput();   // Text input for user to enter a question

  createModeDropdown();    // Dropdown for selecting mode

  createAuthButton();      // Button for user authentication

  createSubmitButton();    // Button to submit the API request

   

  // Step 2: Handle user interaction

  authButton.onClick(() => {

      authenticateUser();  // Trigger authentication logic

  });

   

  submitButton.onClick(() => {

      const question = getQuestionInputValue();  // Retrieve the question

      const mode = getSelectedMode();           // Retrieve the selected mode

      const isAuthenticated = checkAuthStatus(); // Verify authentication

   

      if (!isAuthenticated) {

          showErrorMessage("Please authenticate first.");

          return;

      }

   

      // Step 3: Make the API call

      callAPI({

          question: question,

          mode: mode

       }).then(response => {

           displayResponse(response);  // Display the API response

       }).catch(error => {

           showErrorMessage("API call failed: " + error.message);

       });

 });

The callAPI function sends user inputs (question, mode) to the Denodo AI SDK API. It builds the endpoint URL with query parameters, includes necessary headers (e.g., auth token), and performs a GET request. On success, it resolves with the API response; on failure, it rejects with an error. Below is the sampled function to work together with the update function.

public callAPI(requestData: { question: string; mode: string }): Promise<any> {

        return new Promise((resolve, reject) => {

            // Step 1: Construct the API endpoint with query parameters

            const baseUrl = "https://your-api-endpoint.com/answerQuestion"; // Replace with actual endpoint

            const queryParams = new URLSearchParams({

                question: requestData.question,

                mode: requestData.mode

            }).toString();

            const apiUrl = `${baseUrl}?${queryParams}`;

   

            // Step 2: Set up request headers (if needed)

            const headers = {

                "Authorization": getAuthToken() // Assume you have a function to get the auth token

            };

   

            // Step 3: Perform the GET API call

            fetch(apiUrl, {

                method: "GET",  // Use GET method

                headers: headers // Include any headers if necessary

            })

            .then(response => {

                if (!response.ok) {

                    reject(new Error("API responded with an error: " + response.status));

                }

                return response.json(); // Parse the JSON response

            })

            .then(data => resolve(data)) // Resolve the promise with the data

            .catch(error => reject(error)); // Catch and reject on network or other errors

        });

    }

3. Deploy Your Power BI Widget

To deploy your Power BI widget, navigate to your project’s root folder and run the command:

pbiviz package

This will generate the .pbiviz file under dist folder, which can be then imported into Power BI.

Importing the Widget into Power BI Desktop

Once you have built your customized widget you can then import it into powerBI  to leverage the Denodo AI SDK in your dashboard, below are the steps you need to follow to set up the connection.

1. OpenSSL Certificate Generation/Configuration

  • Certificate Generation:
  • Ensure openssl has been installed in your machine.
  • Create an openssl.conf file having the server where your AI SDK is running on as ‘CN’.
  • Generate certificate and key based on your openssl.cnf file.
  • Certificate Configuration:
  • Import the provided certificate (cert.pem) into the Trusted Root Authorities on the machine where Power BI Desktop is running. This step ensures Power BI Desktop has the necessary permissions to call the Denodo AI SDK securely.

2. AI SDK Set UP

  • Install Denodo AI SDK.
  • Provide the cert.pem and key.pem locations in the sdk_config.env file.
  • Start AI SDK API Endpoints server.

3. Widget Import

Once the above steps are completed, you can directly import the Power BI widget into your report without requiring additional code deployment.

  1. Open Power BI Desktop.
  2. Click on “Import a visual from a file” in the Visualizations pane.
  3. Select the file .pbiviz under dist file and click Open.
  4. Upon successful import, a Denodo icon will appear in the Visualizations pane.

4. Add the Widget to Your Dashboard

  1. Drag the imported icon onto your dashboard.
  2. Map the column you want to add into this widget into the field you have added in capabilities.json file.

5. Configure and Use the Widget

Once the widget is set up you are able to ask questions as you wish.

Sampled Denodo AI PowerBI Widget

This sample widget is designed to enable asking questions to your data directly from PowerBI, leveraging both tables and filters applied to your dashboard for accurate insights. You should follow the steps below to import it into your dashboard and start using it seamlessly.

1. Check if your AI SDK is running on port 8008 over HTTPS

  • If yes, go to 2. Step.
  • If not, modify the port number in capabilities.json files then deploy it using the command pbiviz package.

2. Prepare the used_tables Column

Within Power BI, create a new column in any table that is connected to your Power BI report. This column should define the tables and filters used in your dashboard in the following format:

used_tables = “db1.view1 with filters column1 = value1, db2.view2 with filters column2 = value2_1 or value2_2; db3.views without filters; …”

Example:

used_tables = “sales.region with filters country = Germany; customers.details without filters;”

This column provides context for the AI SDK to limit queries to the relevant tables and filters.

3. Import the Custom Visual

  1. Open Power BI Desktop.
  2. Click on “Import a visual from a file” in the Visualizations pane.
  3. Select the file .pbiviz under dist folder and click Open.
  4. Upon successful import, a Denodo icon will appear in the Visualizations pane.

4. Add the Widget to Your Dashboard

  1. Drag the Denodo AI PowerBI Widget onto your dashboard.
  2. Map the used_tables column to the Used Tables field in the widget.
  • This action synchronizes the data from your dashboard with the widget and limits the AI SDK’s scope for answering queries.

5. Configure and Use the Widget

Once the widget is set up, you will see the following components:

Widget Features:

  1. Question Input Field:
  • Enter your question here to query the AI SDK.
  1. Mode Selection:
  • Options:
  • Metadata: Limit the question to metadata-related queries.
  • Data: Execute the query on the Denodo platform.
  • Default: Allow the AI SDK to decide the most suitable mode.
  1. Options:
  • Restrict Answer to Report Tables:
  • Limits the query scope to the tables specified in the used_tables column.
  • If not enabled, the query will include all accessible data.
  • Apply Same Filters as Report:
  • Uses filters from the used_tables column for additional scope limitation.
  • If the “Restrict Answer” option is disabled, this will be grayed out and non-clickable.
  1. Submit Button:
  • Send your question to the AI SDK for processing.
  1. Chat History:
  • Displays a history of successful interactions between you and the AI SDK.
  1. Login Indicator (Orange Circle at the Bottom Right):
  • Orange: You are not logged in.
  • Green: Indicates successful login after entering credentials.

6. Final Setup

After completing these steps, the widget will be ready for use. You can now:

  • Ask questions based on your report data.
  • Adjust the query scope using the Restrict Answer and Apply Filters options.
  • View real-time responses and history within the widget interface.

Putting it all together

Summary

Deploying your own widget integrated with the Denodo AI SDK unlocks a new level of data exploration and insight generation. The Denodo AI SDK enhances Power BI’s visualization capabilities by enabling users to ask natural language questions, not only about the tables and data in their dashboard but also across the entire dataset accessible via Denodo. This integration allows users to gain richer, context-aware insights that go beyond the visible data. Even non-technical users can intuitively explore and understand complex datasets, leveraging Denodo's advanced data virtualization and AI capabilities to make smarter decisions with ease.

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here