Push Data to Power BI Service using SSIS component

With the growing use of Power BI for visualizations, analytics and analysis, we look into the ways of integrating data to Power BI. Though Microsoft provides  API to push data to Power BI Services, it has some restrictions.

This article provides an example of using free SSIS component PBIPushData to push data to Power BI.

PBIPushData is a SSIS Destination Component to load data to tables on Power BI Service. Power BI has a restriction where in data can only be pushed to the tables created by Power BI API. PBIHelper Desktop Application is used to create datasets and tables in Power BI Service via API. The component PBIPushData and PBIHelper application works with both free and Pro Power BI Service.

In this example, dataset Human Resources is created in Power BI Service using Power BI Helper desktop application. Data from source file Departments is pushed to Departments table in Power BI.

Prerequisite

The desktop application and SSIS component connect to Power BI service via API.   In general, to use Power BI APIs, we need to register the application with Power BI service. The application also needs to be authorized by Azure. The post App registration for Power BI API use via Azure Portal explains required steps in detail. The SSIS component requires Client ID from App registration (as Native App) process.

Overview

In this example a free SSIS component Power BI Push Data is used to upload data to Power BI. The component uses Power BI APIs to communicate with Power BI service.

Create datasets in Power Bi service

Download and install PBIHelper application.

PBIHelper application opens up with screen to input Power BI credentials. The credentials will be saved on local machine and will be used to logon to Power BI Service. Along with credentials, Client ID obtained in Step 1 is required. Check “Use Power BI Preview (beta) API”, to define measures and relations. Click here for more info on using Power BI Preview (beta) API.
  • Logon : Provide Power BI credentials and save. 
  • Select the account and click connect.

Click on button New Dataset.

 

 In screen Create dataset & tables, enter dataset schema information.

Name: HR

Click on [+] in Group Tables. Define table Departments

Click on [+] in Group Columns. Define following columns.
Column Name Data Type

==================================
DepartmentID Int64
Name String
GroupName String
ModifiedDate DateTime

Click on button Create Dataset & Tables.
This creates dataset in Power BI service.

“Create dataset & tables…” screen facilitate defining schema for dataset. If Power BI API in beta/preview mode is in use, measures can be defined, as well table relations. Dataset schema definitions can be saved before creating dataset in Power BI service.

Push Data 

Download and install SSIS Destination Component PBIPushData.

(           SQL 2012       SQL 2014       SQL 2016   )

Once installed PBIPushData destination component will be available in Common components section of Data Flow Task.Demo package is a template/skeleton package. The source and destination components need to be configured for your local path and with your Power BI settings. The Client ID obtained in Step 1 is required.
  • Drag & Drop Data Flow Task in your SSIS package.
  • Open DFT.
  • Drag & drop Flat File source component and configure source from downloaded demo file Departments.csv.
  • Drag & drop

  • Open the component PBIPushData component.

Enter Power BI Credentials on tab Connection Settings.
Enter Client ID obtained in Prerequisties as Client Key.

Column Mappings

  • Click on Column mappings tab.
  • Select HR as dataset.
  • Select Table Departments

Click on AutoMap to map source and target columns automatically.

Click on  Save & Close. Package is ready to push data.

Conclusion

In this post, we are looking at pushing data to Power BI services using custom SSIS components. This solution helps on-demand data push to Power BI service. This is very useful for users with the need of pushing data such as On-premise legacy IoT, Incremental Data load, users of free Power BI service to name a few.

About the Author

Leave a Reply

Your email address will not be published. Required fields are marked *