Scalable Power BI data model on delta lake ADLS Gen2

Question

Our current architecture for reporting and dashboarding is similar to the following:

[Sql Azure] <-> [Azure Analysis Services (AAS)] <-> [Power BI]

We have almost 30 Power BI Pro Licenses (no Premium Tier)

As we migrate our on-premise data feeds to ADLS Gen2 with Data Factory and Databricks (in the long run, we will dismiss SQL Azure DBs), we investigate how to connect Power BI to the delta tables.

Several approaches suggest using SQL Databricks endpoints for this purpose:

https://www.youtube.com/watch?v=lkI4QZ6FKbI&t=604s

IMHO this is nice as long as you have a few reports. What if you have, say, 20-30? Is there a middle layer between ADLS Gen2 delta tables and Power BI for a scalable and efficient tabular model? How to define measures, calculated tables, manage relationships efficiently without the hassle of doing this from scratch in every single .pbix?

[ADLS Gen2] <-> [?] <-> [Power BI]

As far as I can tell, no AAS Direct Query is allowed in this scenario: https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource

Is there a workaround to avoid the use of Azure Synapse Analytics? We are not using it, and I am afraid we will not include it in the roadmap.

Thanks in advance for your invaluable piece of advice

Answer 1

You may use the new Delta Sharing Connector for Power BI to create Power BI Import Models from Delta tables without having to go through Databricks SQL or Spark. Alternatively, you can use a Spark job to export the model data to a Data Lake format that Power BI/AAS can read.

If you require DirectQuery models, you should use Synapse SQL Pool or Synapse Serverless, as these expose the data as SQL Server endpoints, which Power BI and AAS support.

You can define measures, calculated tables, manage relationships in an AAS Tabular Model or a Power BI Shared Data Set.

Unlock the Power of Data Visualization with Our Comprehensive Power BI Course!