Build a SQL connected Power BI Dashboard as External B2B Guest
Recently, I had a client who wanted me to build a Power BI report with a connection to their SQL database. This story is not intended to represent the best way to do this, but one of the many options out there. Given the requirements and current limitations, which I will explain soon, this was the best way I could think of moving through this new project.
As a consultant, there are hurtles to jump over in almost every project. This gives good reason to plan each project carefully and methodically. After the initial meeting with my new client, I was able to jot down the following:
The data is stored in a SQL DB, which is refreshed daily, hence, the report should be refreshed daily as well.
We will need to create multiple versions of this dashboard for each internal project.
We will have a different tab in Teams for each internal project so we can collaborate separately on each project while reviewing the PBI dashboard.
We need the consultant to have admin privileges to the PBI workspace for any required changes, publishing reports, applying best practices, etc.
I would need to access the SQL database, which I knew could be a little tricky depending on their environment and security restrictions. After having a conversation with their head IT/Security person, I was able to note the following security requirements:
There will be no holes poked through the firewall.
TeamViewer can be used to remotely access a machine connected to the SQL Server.
Now it was time for me to sit down and really plan how I was going to build this Power BI solution and maneuver through these requirements. There had been similar projects in the past, like connecting to an external SQL Server and accessing an external Power BI workspace. But there were things that made this project uniquely different. I knew the following:
I cannot connect PBI Desktop to the SQL DB from my computer using the IP address and Port number method, because that would require poking holes into the firewall - a company security policy that must be respected.
I would walk IT through the process of adding me to their tenant as a guest.
After connecting SQL to PBI Desktop, Power Query transformations cannot be done on my computer. This part would still need to be done on the remote desktop.
Below, I explain how I moved forward with the important details we have established.
An Externally Shared Workspace
First, we created a Power BI workspace in the client’s PBI environment. This workspace would be used to test and publish reports for this project. The next logical step was to add me as an Admin to that workspace so I can publish and manage it. This required adding me as a guest in their Azure Admin Portal, then in their Power BI Admin Portal, then the PBI workspace. The process is explained as explained in this MS Doc, Distribute Power BI content to external guest users with Azure AD B2B.
Remember, we cannot use the IP + Port # in PBI's SQL Server connector, which would allow us to connect directly to SQL Server from our computer. Instead, TeamViewer was set up so I could remotely access a machine that was connected to the SQL Server. This allowed me to finally connect Power BI to the SQL Server, make all the transformations needed in Power Query, build the appropriate data model, and publish the .pbix file to the shared workspace. All from the remote desktop.
Next, on my computer, I went to the shared workspace and downloaded the .pbix file, which only had the data model and a blank canvas – let the artistry begin! I created table relationships, DAX measures, and visualizations. Now, publish...?
As a guest administrator, you can publish a report to the external shared workspace...but it’s done a little differently. If you click the Publish button in PBI Desktop, you will not see the external workspace as on option. Instead, you will need to save the .pbix file, go to the shared workspace in your browser, click on Get Data, and upload the report file.
Scheduled Refresh
As many of you know, you need a Power BI Gateway to refresh datasets in the Power BI service (web) that have a SQL Server data source. You will want your client to add you as a gateway admin as well. This will allow you to add/edit SQL Server data sources to the gateway. Once the gateway and data sources were properly set up and added on the PBI service, I was to refresh within the service and to turn on the scheduled refresh.
Report Optimization with Dataflows
A Dataflow can be very useful in many situations. In this example, from my computer I created one dataset that everyone can use as a single source of truth to build ad hoc reports. I was the owner of this Dataflow. However, I could not connect PBI Desktop to that Dataflow. Just like the Publish issue from PBI Desktop, the list of Dataflows to choose from will not contain the external Dataflow. That part had to be done in the remote desktop via TeamViewer. Really, PBI Desktop needs to be logged into an account that has access to the workspace and is part of the same tenant.
Once I replaced the dataset in Power BI Desktop with the Dataflow (from the remote desktop), I published it to the workspace. To create a separate report for each internal project, I went to the PBI workspace from my computer. Then I saved a copy of the report I had just published (makes a copy in the workspace). In the workspace, I edit the new report and set the appropriate report level filter (e.g., Project = ABC). Each PBI report was using the same dataset, all from that one Dataflow, only with a different filter.
Using Teams to Collaborate on PBI Reports
Once the PBI reports for each project were up and running in my client’s PBI workspace, we were able to add the reports to the Teams channel as separate PBI tabs. A client can also create a separate channel or even a separate team for each project - depends on their preference. Team members can view the dashboard in Teams and comment as they please. In this case, every morning team members viewed the dashboard for the projects they're a part of. They would collaborate about the project, comment on trends, and anything that popped out to them. There was a Teams discussion for each project. Below is an example of one PBI tab I created in Teams within my environment. The process to add Power BI to Teams is explained in this MS Doc, Embed Power BI content in Microsoft Teams.
Conclusion
Each project is different and presents its unique challenges. Microsoft’s products have come a long way, especially the Power Platform with its monthly updates. There are still areas of improvement that will hopefully be addressed over time. As mentioned earlier, this is not the perfect solution, but it's the best workflow I could think of given the situation.
Listed below are some things to know and limitations of adding Azure AD B2B Guests for Power BI consultants to help clients manage their Power BI environments.
You cannot find the shared workspace within your own PBI environment. You will need to save the URL to that shared workspace when you visit it.
A shared workspace in a different tenant will also create a personal workspace in that tenant – wasn't important in this discussion, just FYI.
You cannot publish directly from Power BI Desktop. You need to use Get Data > Files > Local File within the workspace (PBI service) to publish.
You can create Dataflows in the shared workspace but cannot connect to them using from PBI Desktop using the Dataflows connector, even as the Dataflow owner.
Your Power BI Pro license can be used in your client’s environment.