Article

Model architecture for Business Central data export to ADLS adds direct SQL queries

by Jason Gumpert
Editor, MSDynamicsWorld.com

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:

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

Get full access
Automated Data Collection BI and Reporting Business Central Data Archiving Data Management Databases Power BI Storage
About Jason Gumpert

As the editor of MSDynamicsWorld.com, Jason oversees all editorial content on the site and at our events, as well as providing site management and strategy. He can be reached at jgumpert@msdynamicsworld.com.

Prior to co-founding MSDynamicsWorld.com, Jason was a Principal Software Consultant at Parametric Technology Corporation (PTC), where he implemented solutions, trained customers, managed software development, and spent some time in the pre-sales engineering organization. He has also held consulting positions at CSC Consulting and Monitor Group.

More about Jason Gumpert