1

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://learn.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

Federico Lavatori
  • 21
  • 5

1 Answers1

1

Is there a middle layer between ADLS Gen2 delta tables and Power BI for a scalable and efficient tabular model?

If you want to build Power BI Import Models from Delta tables without routing through Databricks SQL or Spark, you can look into the new Delta Sharing Connector for Power BI. Or run a Spark job to export the model data to a Data Lake format that Power BI/AAS can read directly.

If you want DirectQuery models, Synapse SQL Pool or Synapse Serverless would be the path, as these expose the data as SQL Server endpoints, for which Power BI and AAS support DirectQuery.

How to define measures, calculated tables, manage relationships efficiently without the hassle of doing this from scratch in every single .pbix?

Define them in an AAS Tabular Model or a Power BI Shared Data Set.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • Thanks for your quick comment. I am ok with using Databricks SQL as well as the Power BI connector. What's the way to go to have both a middle layer tabular model and Databricks SQL? Thanks in advance – Federico Lavatori Feb 17 '22 at 11:15