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.
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.
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.|
Click on button New Dataset.
| In screen Create dataset & tables, enter dataset schema information.
Click on [+] in Group Tables. Define table Departments
Click on [+] in Group Columns. Define following columns.
Click on button Create Dataset & Tables.
|“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.
Download and install SSIS Destination Component PBIPushData.
|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.|
Enter Power BI Credentials on tab Connection Settings.
Enter Client ID obtained in Prerequisties as Client Key.
Click on AutoMap to map source and target columns automatically.
Click on Save & Close. Package is ready to push data.
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.