Can an sales app use a data warehouse / lakehouse as data source?

[franklloydteh]

Context: 

We have a sales report that is used by management through PowerBI. The data comes from multiple SaaS applications and internal microservices. Management wants to give each sales agent a view on their own sales stats through the sales agent app.

The IT team wants to use the same tables as the management report for the individual stats. The thinking is,  if we rebuild how the fetching of the stats through some APIs of the SaaS and microservice, then there is a risk that there would be a different in logic and timing. The sales report is only refreshed every 24 hours. 

Note that sales agents are not full time - they are freelance agents. They do not have a MS 365 accounts. We have our custom built user authentication for this application and we do not wish to give them access to PowerBI. There are also plans to disable access to Fabric resources over the internet. See private links . 

Currently: 

We have a web application (Angular) and backend microservices (Java Spring) deployed in Azure (Azure Kubernetes). I am exploring how a microservice can authenticate to a fabric warehouse / lakehouse so the app can run a sales agent specifc query to retrieve individual stats. 

Microsoft Entra authentication in Synapse Data Warehouse - Microsoft Fabric | Microsoft Learn 

Concerns:

Can a fabric handle hundreds of concurrent queries? 

Concurrent users are estimated to be around 50-100. There's actually thousands of sales agents, but it's not as if they will be viewing their apps all at the same time. We will be caching individual query results of course. 

How does this affect usage of Capacity Units? 

Would it be better to also just copy the table into a different dedicated Azure Postgres database? Or would this approach be unecessary? 

[v-jingzhan-msft · Accepted solution]

Hi @franklloydteh 

It sounds like you have a complex setup. Let's address your concerns one by one. 

 

Using a Data Warehouse/Lakehouse as a Data Source

Yes, a sales app can use a data warehouse or lakehouse as a data source. For connecting to a data warehouse or a SQL analytics endpoint of a lakehouse, please learn more from Connectivity to data warehousing - Microsoft Fabric | Microsoft Learn. Please note that for building the connection from the app, you need to authenticate using Microsoft Entra ID. 

 

Row-Level Security (RLS) and Dynamic data masking

As your management wants to give each sales agent a view on their own sales stats through the sales agent app, it is something like Row-Level Security. It is possible to implement Row-Level Security (RLS) in Microsoft Fabric. RLS enables you to control access to rows in a database table based on group membership or execution context. Additionally Fabric supports Dynamic data masking for data security. You may refer to below documentations to explore the possibility to build a combination of them for the data security. 

Row-level security in Fabric data warehousing - Microsoft Fabric | Microsoft Learn

Dynamic data masking in Synapse Data Warehouse - Microsoft Fabric | Microsoft Learn

Custom Authentication for RLS in Data Warehouse

While Microsoft Fabric supports RLS, using custom authentication for RLS instead of Microsoft Entra ID is more challenging. In the Row-level security documentation mentioned in the previous point, it uses a function USER_NAME() in the sample security function. USER_NAME() function is associated with database users. As your app uses custom authentication, the user information is probably maintained in a table of the database, rather than database users managed in the system. I'm afraid it might not work here. Therefore, you might need to explore alternative methods. 

You might get the login user id or name within the app, then set up filter conditions for login user in the SQL queries. Then send these SQL queries to the warehouse to query data for a specific user. 

RLS for Power BI report

Not sure if you are to build the report by using Power BI? If you are to build a Power BI report and embed it in your app (Embed for Customers scenario), there is a possibility to set up RLS in the semantic model for the report. RLS in Power BI semantic models is different from RLS in data warehouse or SQL Server. If your current Power BI report for management is using the Embed for Customers scenario, this approach could be an alternative. You can embed a Power BI report in your custom app and use the app's custom authentication. The app needs to send a request to Power BI REST API to get embed token for a specific login user, then uses the embed token to access the data in Power BI that this user has access. Enforce data permissions for Power BI embedded analytics - Training | Microsoft Learn

Can a fabric handle hundreds of concurrent queries?

Microsoft Fabric is designed to handle high concurrency. Given your estimate of 50-100 concurrent users, Fabric should be able to handle this load efficiently. But if you encounter degraded performance or failure, you might need to resize or scale your Fabric capacity.  

Plan your capacity size - Microsoft Fabric | Microsoft Learn

Scale your Fabric capacity - Microsoft Fabric | Microsoft Learn

Copying Tables to Azure Postgres Database

Copying tables into a different dedicated Azure Postgres database is an option, but it might be unnecessary if your current setup with Microsoft Fabric meets your needs. This approach could add complexity and potential data synchronization issues. However, if you find that Fabric's capabilities are insufficient for your specific requirements, this could be a viable alternative.

I hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[franklloydteh]

Hi Jing, 

This was very helpful. We do plan to authenticate the app (server side) using service principals to the fabric warehouse as mentioned here - https://learn.microsoft.com/en-us/fabric/data-warehouse/connectivity 

PowerBI report for management already exists and does contain sales agent ids. 

Regards, 

Frank