Early in 2022, two Microsoft Cloud Solution Architects introduced a new pilot approach for exporting Dynamics 365 Business Central data for analysis using Azure Data Lake and Azure Synapse Analytics. The team has now updated this method to take advantage of Synapse capabilities that allow direct SQL queries from Power BI against Spark tables.

The new model, provided to the community as a solution accelerator in the BC2ADLS project, relies on serverless SQL from Synapse, which enables running ad hoc SQL queries without the overhead of a traditional SQL database in terms of resources and cost. The new method creates a transformation pipeline with the resulting external tables in Spark.

The resulting Spark notebook in Synapse creates an external table with SQL query capabilities. But the Spark table is really an external reference to the Parquet files in the date lake storage that result from the Business Central data export and transformation, explained co-author, Henri Schulte. And while that method may feel like jumping through hoops, it enables Power BI to use DirectQuery with Serverless SQL, meaning it is possible to use joins and other types of aggregations without having to do them locally while still maintaining the data types of the source system.

Schulte explained one case that would see a measurable performance improvement with this approach: