Connecting Power BI to Kaivo

This article is a step-by-step guide on how to connect Kaivo to your Power BI using Google account authentication.

Written By Aapo Mannismäki

Last updated 11 days ago

Below are instructions on how to connect Kaivo to Power BI desktop and then to Power BI service after you’ve published your report to PBI Service.

Here is a step-by-step guide to connecting Power BI Desktop to Kaivo using Google BigQuery connector.

Connect to Power BI desktop

Step 1: Initiate Data Sourcing

  • Open Power BI Desktop. From the initial welcome or data source panel, click on Get data from other sources.

Step 2: Select Google BigQuery as the source

  • In the Get Data window, type "BigQuery" into the search box on the left or scroll to find it.

  • Select Google BigQuery from the list of data sources.

Step 3: Configure Connection Options

  • A Google BigQuery configuration window will appear.

  • If you have specific advanced settings, you can click Advanced options; otherwise, simply click OK to proceed.

Step 4: Choose Authentication Method

  • In the credentials window, select Organizational account from the left-hand navigation sidebar.

  • The main panel will state "You aren't signed in." if you haven’t authenticated BigQuery before. Click the Sign in button.

Step 5: Authenticate via Browser

  • A browser window will open titled Choose an account. Select the Google account that is connected to your Kaivo workspace.

  • On the next screen, review the permissions requested by Power BI Desktop to access your Google BigQuery data and click Continue.

  • Once you see the message "Sign in complete. You can return to the application. Feel free to close this browser tab.", you can safely close your browser window.

Step 6: Connect to BigQuery

  • Return to Power BI Desktop. The credentials window will now display "You are currently signed in.".

  • Click the Connect button in the bottom right corner to establish the data connection.

Step 7: Navigate and Select Your Tables

  • The Navigator window will open, displaying your accessible BigQuery datasets (e.g., bigquery-public-data).

  • Drill down into your BigQuery project folder and database schema and then expand the dataset you wish to connect.

  • Check the box next to the specific table(s) you want to import to view a preview of the columns and values on the right-hand side.

  • From here, you can click Load to import the data straight into your model, or click Transform Data to clean it first using Power Query.
    NOTE: Kaivo automatically transforms your data into right data types and Power BI recognizes them automatically so cleaning datasets from Kaivo is usually optional. However, sometimes you might want to change datetimes to dates etc. but this can be done later as well once you know what you need.

Step 8: Select Connection Mode

  • After clicking Load from the navigator window, a Connection settings prompt will appear.

  • Choose your preferred data connectivity mode:

    • Import (most commonly used): Downloads a static snapshot copy of the data directly into your Power BI file. Can be refreshed on-demand using Refresh button on the button ribbon on Power BI desktop.

    • DirectQuery: Connects live to Google BigQuery, querying the source database in real-time as you interact with reports.

  • Click OK to initiate the data loading process.

Step 9: Verify the Loaded Data

  • Once Power BI finishes processing the query, look at the Data pane located on the far right edge of your screen.

  • You will see your newly imported table listed and ready to expand for building reports and visualizations.

Edit credentials in Power BI Service

Once you’ve ready to publish your report to Power BI Service here are the steps you need to take. Please note that if you’ve published reports to your PBI Service workspace before, you might not need to edit the credentials so steps from 13 to 16 may need to be done only once.

Step 10: Publish the Report to Power BI Service

  • In Power BI Desktop, navigate to the top ribbon menu and click the Publish button.

Step 11: Choose the Target Workspace

  • A Publish to Power BI dialog window will appear.

  • Select your intended deployment destination from the list (such as My workspace) and click Select.

Step 12: Confirm Successful Publishing

  • Wait for the deployment process to complete until you see the Success! confirmation window.

  • You can click the direct link provided in the dialog or open your web browser to log into the Power BI Service portal.

Step 13: Locate the Semantic Model Settings

  • Once inside the Power BI Service cloud portal, open your target workspace.

  • Locate the asset row corresponding to your dataset, identified as a Semantic model.

  • Click the ellipsis icon (...) next to the semantic model name and choose Settings from the context dropdown menu.

Step 14: Edit the Data Source Credentials

  • On the settings configuration page, locate and click to expand the Data source credentials category.

  • Find the GoogleBigQuery data source entry and click the Edit credentials link alongside it.

Step 15: Configure Cloud Authentication Controls

  • A configuration pop-up window will appear for the database. Apply the following settings:

    • Authentication method: Open the drop-down menu and select OAuth2.

    • Privacy level setting for this data source: Open the drop-down menu and select the one that is suitable for your use case.

  • Click the green Sign in button to launch the verification flow.

Step 16: Complete Google OAuth Authentication

  • A secure Google login window titled Choose an account will open up.

  • Click on your corporate or organizational Google account (e.g., demo@kaivo.io) to finalize the authorization credentials. This links the cloud semantic model to your account, enabling scheduled dataset refreshes without further credential prompts.

Step 17: Set Up a Daily Refresh Schedule (Optional)

  • While still in the semantic model settings, scroll to and expand the Refresh section.

  • Select your preferred location from the Time zone drop-down menu.

  • Under the Configure a refresh schedule header, click the toggle switch to flip it to On.

  • Set the Refresh frequency drop-down menu to Daily or Weekly.

  • Click the Add another time link to designate the specific time slot(s) during the day when you want the data import to run automatically.

    QUICK TIP: Check your data source refresh schedule and run times in Kaivo and schedule the daily refreshes after that. Good practice is to leave some headroom if the daily Kaivo sync takes longer than expected. For example; your daily Kaivo sync is scheduled for 3:15 AM and usual run time is 12 minutes, choose 4:00 AM as the scheduled time in Power BI Service rather than 3:30 AM.

  • Ensure the Semantic model owner checkbox under Send refresh failure and critical warning notifications to is selected so you receive an email if an automated refresh ever fails.

  • Click the green Apply button at the bottom left to activate the automated daily timeline.