Investigate Power BI Integration

[klondikemarlen]

Relates to:

• TODO

Context

Is your feature request related to a problem? Please describe.
Power BI is popular right now, so we want the platform to support a set of instructions for getting the data into PowerBI.

Describe the solution you'd like

Part 1

• have a set of instructions for loading CSV data into PowerBI.

Part 2

• have a set of instructions for loading JSON data into PowerBI from an API endpoint.

Requirements

1. Add API endpoint like /api/datasets/:id-or-slug/power-bi - maybe takes preview or pretty or raw flags?
2. Generating an API token for the user (on account creation, or backfill migration)
3. (optional) if we are providing federated access to an external dataset, we could have an /api/datasets/:id-or-slug/raw endpoint that has a minimal policy and token only access?

API Tokens

The API token should be specific to the user.
Currently API tokens don't exist as a concept in the codebase. So we will need to create an api_tokens table with a user_id and token columns.
Users will need some UI to refresh/invalidate their API tokens.

Questions

1. Is there any point in generating an API token that is specific to a dataset? If the user is accessing through the platform, we can provide the appropriate policy restriction by mapping a user to their token. Any analytics and logging can be performed for everybody, regardless of the dataset they access. We could provide user's per-dataset tokens, but this strikes me as a bonus feature, rather than a base level one as it doesn't add anything except a small increase in security for the end-user.

   We could provide user's a token specific to a dataset when subscribe to a dataset; and use the IDP app as a "passthrough" to the external API, but that doesn't really add anything over just granting the user access via policy check. Unless its a speed thing? We could provide a special token that gives access to a specific dataset without complex policy logic so it was as fast as possible?

Describe alternatives you've considered
Attempt to generate a direct to PowerBI format; rejected as doesn't seem to be a straightforward task.

Additional context

Example of PowerBI CSV Load Instructions

---

Instructions to Pull in the CSV into Power BI

Step 1: Download the CSV File

1. Navigate to the Internal Data Portal:

   • Open your browser and go to the URL of your internal data portal.

2. Download the Dataset:

   • Locate the dataset you want to download (as shown in the provided screenshot).
   • Click on the "DOWNLOAD TO CSV" button to download the file to your local machine.

Step 2: Import the CSV File into Power BI

1. Open Power BI Desktop:

   • Launch the Power BI Desktop application on your computer.

2. Import the CSV File:

   • Click on Home in the top menu.
   • Select Get Data > Text/CSV.
   • Navigate to the location where you saved the downloaded CSV file.
   • Select the CSV file and click Open.

3. Load the Data:

   • A preview window will appear showing the first few rows of your data.
   • Click on Load to import the data into Power BI.

---

Example instructions for loading Power BI data via API endpoint

---

Instructions for Loading Data into Power BI via the Internal Data Portal API

Step 1: Import Data from API into Power BI

1. Open Power BI Desktop:

   • Launch the Power BI Desktop application on your computer.

2. Get Data from Web:

   • Click on Home in the top menu.
   • Select Get Data > Web.

3. Enter API URL:

   • In the dialog box, enter the URL for the API endpoint:

     https://internaldataportal.com/api/datasets/67890/preview
     

4. Authentication:

   • When prompted, select Web API as the authentication method.
   • Enter your API token in the following format:

     Bearer abcdef123456
     

5. Load Data:

   • Power BI will fetch the data from the API and show a preview.
   • Click Load to import the data into Power BI.

Step 2: Schedule Data Refresh

1. Publish to Power BI Service:

   • Click on File > Publish > Publish to Power BI.
   • Sign in to your Power BI account if prompted.
   • Select a workspace and click Select to publish your report.

2. Configure Data Source Credentials (if prompted):

   • After publishing, go to Power BI Service.
   • Navigate to the workspace where you published your report.
   • Click on Datasets and locate the dataset you just published.
   • If prompted for credentials, click on the ellipsis (...) next to the dataset name and select Settings.
   • Under Data source credentials, click Edit credentials.
   • Select Web API as the authentication method.
   • Enter your API token in the format Bearer abcdef123456.
   • Click Sign in or Connect.

3. Set Up Scheduled Refresh:

   • In the dataset settings, go to the Scheduled refresh section.
   • Toggle Keep your data up to date to On.
   • Set the Refresh frequency (e.g., daily, weekly).
   • Specify the Time zone and Time for the refresh.
   • Optionally, add additional refresh times if needed.
   • Click Apply.

By following these steps, you can import data from the Internal Data Portal API into Power BI and ensure it stays up-to-date with scheduled refreshes.