This weekend, I had the honor of presenting my Power BI precon for SQL Saturday Cleveland. I’ll be giving the same presentation March 16th in Cincinnati.
Inevitably, there are always some questions that I don’t have an answer for. What I like to do is circle back and try to get some answers for the people who attended.
Do clustered data gateways provide load balancing?
Back in November 2017, support was added to cluster On-premises Data Gateways. This is great because it used to be that the data gateway was a single point of failure and there wasn’t a great way around that.
The question that came up was does a cluster split up the workload or does it just provide failover capabilities? It turns out it just provides failover capabilities. From the Microsoft documentation:
New requests for scheduled refresh or DirectQuery operations will be routed to the primary instance in the cluster if this instance is online; if not available, the request will be routed to another instance registered in the cluster.
Can you use 3 part naming with Directquery?
The question was, Can I use 3-part naming to get around the single database limitation?” So to test this, the first thing I did was simply select tables from multiple databases. The UI doesn’t stop you at all. But when you try to load the data, you get this error:
That being said, I created a view in the main database pointing to a different database and there wasn’t any issue. Going further, I decided to test out picking on database and hand typing a query pointing to a different database.
And it works! It’s very interesting, I wonder where the limitations comes from if it’s so easy to get around.
What’s the best way to connect to a Web API application?
One attendee said they use ASP.net Web API as middleware for a large number of databases and tables. So what is the best way to connect to Web API for Power BI?
If the API is complex and OData is not an option, custom data connectors are worth looking into. You’ll be writing a lot of M code, but it can be a good way to encapsulate that complexity.
Does Power BI support SAP Universe?
So the situation for SAP Universe is a bit weird. Back in 2014 they added support for SAP Business Objects.
But then later they removed it because of licensing concerns? It’s not entirely clear to me. That being said, there is a request for support to be added back.
Digging a bit deeper, it sounds like there might be a workaround using the SAP OData API, but that’s not the ideal solution.
What are the best options for sharing reports with external customers?
A question I here a lot is how do you share with customers and deal with multi-tenant databases.
Well very recently, back in November 2017, Power BI added support for external users with Azure B2B. This includes support for row-level security, which means you can have all your data in a central database and limit a customer to just their own data. This is very exciting.
There is a whitepaper if you want to learn more.