Using SSIS and PowerShell to Manage Power BI Workspaces

The following blog comes from Addison Sand, Data & Analytics Consultant at Superior Consulting Services.

SQL Server Integration Services (SSIS) is a great tool for managing data-driven processes.  We can use SSIS to load in data, and then use that data to kick off processes that help us manage data environments. 

Power BI is one such environment that can benefit from this data management. Power BI requires management of workspaces access which can be a time-consuming task for  IT teams. 

In this blog, I’ll introduce a scenario where we can leverage a data flow to create, update and delete of users from a Power BI workspace.

Using SSIS and PowerShell to Manage Power BI Workspaces

To illustrate how we can use SSIS and PowerShell to manage workspaces in Power BI, I’m going to walk through an example application. This is just one scenario, but this same structure can be used for other use cases.

Scenario

We have an HR system that stores employee information.  At the end of each week, a file is exported out of that system to be loaded into SQL Server.  

Goal

We want a process that can load in the HR data, add new employees to their departments' Power BI Workspaces, update employees with workspaces who have been promoted and send a report to IT that contains the delta of the new employee file and confirmation.

Design

We can use SSIS dataflows to ingest in the HR employee data export.  Our employee table in our SQL Server database would allow us to track changes over time and when a change is detected: new employee, separation, department or title change.  

Those changes (or delta) would be picked up by SSIS along with a mapping of which job titles and departments belong to what Power BI Workspaces. 

This delta and mapping is then sent to a PowerShell script.  PowerShell will then complete the delta using the mapping information and by connecting to our Power BI workspace. 

Next, our SSIS package emails the Employee Delta report to IT.  This report communicates to the team that a new HR file has been loaded, what employee changes have occurred in the HR system, and that employees have had workspace changes.  

This also gives IT a log that they can use for validation as well as the knowledge that the process is running smoothly.

This is just one example of how a company could use SSIS, PowerShell and Power BI together to update data easily. For more application ideas, contact a data analyst at SCS to discuss your project. 

Superior Consulting Services (SCS) is a Microsoft-centric technology firm providing innovative solutions that enable our clients to solve business problems. We offer full-scale data modeling, analytics, and custom app development.