Background
Connecting Power BI to the CS Lucas data repository to retrieve datasets for building dashboards. This data can be combined with other datasets and other datasets outside the CS Lucas. The purpose is to deliver comprehensive insights that support critical decision-making in real-time.
These instructions detail creating a Power BI template for retrieving datasets from the CS Lucas repository. The template may be shared with others with the necessary credentials so that they may retrieve the data, render it, and make further enhancements.
Pre-requisite
To access the data repository, you need the following:
| User ID: | A user ID of an account to access the repository. This account must be activated for OTP (MFA). See MFA activation: https://www.cslucas.com/user-guide/multi-factor-authentication/ |
| Token: | A 275-character text used for authentication, valid for 4 hours but token expiry can be configured. See Instructions: Get API Token. |
| Dataset: | The name of the required dataset from the repository. An inventory of all repositories and a sample dataset can be found here: Data Repository. |
| URL | A URL used to connect to the repository API. This URL contains all the above information. Construct this using Get API Token. |
| Power BI | You need to install Power BI Desktop. This guide is based on Version 2.138.782.0 |
The instructions use placeholders and sample values for illustration. Replace them as appropriate.
| Placeholder | Values |
|---|---|
| Domain | Production: api2p-live.cslucas.com/prod UAT: api2p-live.cslucas.com/uat |
| Userid | cslpowerbiuser |
| OTP_Token | eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJDUyBMdWNhcyIsImp0aSI6IjVhNGIwNzQ1LTU3MGYtNDVjZS05MjVhLTYwYzViMmVkYTAyNCIsImlhdCI6MTc0MjgxMDg0Niwic3ViIjoiYTlmMGE1YzAtY2Y4NS00N2NjLTk0NzQtZWNlZTdlNWEyZDI1IiwiZXhwIjoxNzQ0MDIwNDQ2fQ.oTf2M5ZABbKO5XerHJUPBpyW_gN7K4l6KYlNptUrMno |
| As_at_date | Enter the date in this format: YYYY-MM-DD |
| Others | None |
| Dataset | ds1426 |
| URL | https://api2p-live.cslucas.com/dataset_name/uat?userid=cslpowerbiuser&otp_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJDUyBMdWNhcyIsImp0aSI6IjVhNGIwNzQ1LTU3MGYtNDVjZS05MjVhLTYwYzViMmVkYTAyNCIsImlhdCI6MTc0MjgxMDg0Niwic3ViIjoiYTlmMGE1YzAtY2Y4NS00N2NjLTk0NzQtZWNlZTdlNWEyZDI1IiwiZXhwIjoxNzQ0MDIwNDQ2fQ.oTf2M5ZABbKO5XerHJUPBpyW_gN7K4l6KYlNptUrMno&as_at_date=now&others=none |
| Source | = Json.Document(Web.Contents(Text.Combine({"https://",Domain,"/ds1426?userid=",Userid,"&otp_token=",Text.From(OTP_Token),"&as_at_date=",Text.From(As_at_date),"&others=",Text.From(Others),""}))) |
Building a Power BI template
Step 1: Create the parameters
– Launch Power BI Desktop
– Start with a blank report
– In the ribbon, select Transform Data to launch the Power Query Editor
– Click on Manage Parameters
– Click on New
– Enter the following values to create the Domain parameter
Name: Domain (Case sensitive. Ensure the parameter name matches the <Source> in Step 4 below.)
Description: [blank]
Required: Checked
Type: Text
Suggested Value: Any values
Current value: <Domain>
– Click New to create the User ID parameter using the current value used in the <User ID>
– Repeat creation for the OTP_Token parameter.
Mark Required as Uncheck
Set current value = <Token>.
– Repeat creation for the As_at_date parameter.
Mark Required as Uncheck
Set current value = <As_at_date>. Enter the date in this format: YYYY-MM-DD. E.g. 2017-07-02
– Repeat creation for the Others parameter.
Mark Required as Uncheck
Set current value = None.
Step 2 – Connect to the Repository
– Click on New Source (ribbon)
– Select Other Source > Web. Click Connect.
– In the From Web dialogue, paste <URL> in the URL.
– Click OK. In a moment, the data will be populated,
Step 3 – Transform
– From the menu, select Transform
– In the ribbon, click on “Use first row as header“
Step 4 – Update the source to consume the parameters
– On the menu bar, select View > check on Query setting.
– Change the name to the name of the dataset. E.g. ds1426
– In the Query setting panel on the right, under applied steps, click on Source
– Expand the formula bar with the down arrow (v) to the right if necessary.
– Select the entire content of the formula bar and replace it with <Source>. Change the dataset name (illustrated here as ds1426) as appropriate.
– Save by clicking on the left check box.
Step 5 – Apply the design and export the template
– On the Home menu, select “Close and apply“. The system will load the data.
– The data source is now created. Check by clicking on the Table View on the left. You should see a table with data.
– On the menu, select File > Export
– Select Power BI template
– In the template description, enter. “CS Lucas parameterised template to connect to ds1426”. Change the function name as appropriate.
– Provide the file name based on the function name. Eg ds1426
Using the Power BI template
The Power BI template only has the connectivity configuration but does not save the values of the connection parameters or the data. Others can use it to connect with their credentials.
To connect
- Double-click on ds1426.pbit (the template)
- The system will launch an untitled Power BI file and immediately prompt for the URL, User ID, and either the valid OTP or Token values.
- The system will load the model.
- Save the file.
When you next open the file, it will attempt to connect with the saved parameters.
0 Comments