Skip to main content

PowerBI Integration With Csv Files

Description​

Produces a Power BI Workspace using data aquired from SkillsWorkflow.
Data is extracted from a Query installed in the Tenant. The Analytics service runs de Query and sends it to a CSV file. The CSV File is then added as a Data Source in the Power BI Workspace.

Configuration​

Skills Workflow​

  • Go into the tenant in Maintenance and create Query.
    • Name the Query with the prefix "Power BI - " (e.g., Power BI - Active Jobs)
    • Set the Categoty to "Power BI"
  • Create a new Schedule to run the Query using the Configuration.
    • Set the running times of the File generation based on the Query that is run
    • Set the name of the File to generate. No spaces are allowed (e.g., PowerBI-ActiveJobs)
  • The Schedule will generate an URL for the File that is generated.
  • Running
    • The Query can be forced to be run manually.
    • Go into the Configuration and run the specified Schedule.
    • This will execute the Query and generate the File.

Workspaces​

The Workspaces in Power BI will have Data Sources pointing to the URL of the Files.
The URL of the Files is shown in the Schedules that are created in Configuration.
The data refresh (i.e., when Power BI reads the data from the Files) can be set to a minimum of every 30 minutes.

Setup​
  • In Power BI, create a Workspace.
  • Go into the Data Source section and add a new Web data source.
  • Get the URL for the generated file from the Schedule generation and paste it here.
  • The data will be available in the Report pages for use.

Schedule updates on Power BI​

After publishing your Power BI dashboard you need to schedule the updates in the Office365 environment. You have to ask Skills Workflow team to send the credentials for each datasource. Once you get it, in the Power BI portal go to desired Dataset and click on dataset credentials.

img-box-shadow

Update Every Data Source

img-box-shadow

Change the Privacy configuration to Private, Authentication mode SAS and add the token provided.

After changing all datasets setup, go to Scheduled Update:

  • Choose Activate and set the time that you want to run the update
  • Click Apply

img-box-shadow

Defining the schedule.

Power Bi Integration with API​

Overview​

There was a need to export job data from the system to Power Bi

Data Exchange​

The scope of this article includes an explanation of how to get data from the system.
Skills Workflow has available specific APIs to extract information prepared to be used in Power BI dashboards, so you can use it to get the data out from the system. You can get the data using REST calls.
Regarding Power BI, you should request to the team the corresponding URL to export data, ie:

Configuration​

There are 2 ways to use Skills APIs in PowerBI, both you need to ask Skills’ team the credentials to access the data.
Skills Team will send you the information bellow:

  • URL parts
  • HTTP request headers parameters
  • X-AppTenant
  • X-AppId
  • X-AppSecret

If you’re using REST API Endpoint (GET):

img-box-shadow

Aquire from web Endpoint

img-box-shadow

Advanced Form

The APIv2 Apis are normally created with a POST. In this scenario open a blank query and add the commands bellow to get the data:

let
body = "{}",
Data= Web.Contents("URL Provided", [Content=Text.ToBinary(body)
in
#"Data"

Once you have the data extracted you have to transform it to create your dashboard.

Transform Your Data​

To be able to manage your data in Power Bi, you will need to follow some steps:

  • Transform column into json:

img-box-shadow

Transform column into json

  • Transform list into json

img-box-shadow

Transform list into json

  • Convert data into table

img-box-shadow

Convert data into table

  • Set configuration to open as Json
  • Split data into columns

img-box-shadow

Split data into columns

  • The data will be in columns

img-box-shadow

The data will be in columns