Business Central 2023w2 Public Preview, the new Data Stack is revealed on Docker
The public preview of Business Central 2023Wave2 has just been released which includes several new features (which we will see in a subsequent post); in this post I want to focus on what I consider to be the most important innovation related to the data stack and extension management.
The most important feature (my opinion) available in the BC2023W12 September preview is: “Data loads faster in the server”
| Feature | Enabled for | Public preview | |
|---|---|---|---|
| Data loads faster in the server | Admins, makers, marketers, or analysts, automatically | Sep 2023 |
“Data loads faster in the server” – Feature details from Microsoft
Business value
Improving the data loading speed on the server benefits all usage scenarios, including web service integrations, background jobs, and interactive sessions. “This release focuses on optimizing an essential part of the data stack in the Business Central server: The data model for table extensions.
Feature details
In previous releases, when a developer extended a table, the fields from the table extension were stored in a separate table called a companion table in the database. This design was chosen to make the app lifecycle (install, update, uninstall) nondisruptive to normal usage of the base table. Unfortunately, it also had impact on the runtime performance on the system because the Business Central server needed to join data from table extensions when reading data from a table. As an example, for a table with 7 table extensions, the server might need to do a 7-way join.
In this release, we change the data model for table extensions so that added fields from all extensions to a table are now stored in the same companion table. In this new model, the server will never need to do more than a single join of the base table to its companion table. We expect this to drastically reduce the performance impact of table extensions to base tables.”
The New Data Stack & Extension Model
- Today.. a lot of table extensions, each with its own data.
Extension model schema v2.0
Sql Data Structure
- One Table for each extension
The new extension model schema v3.0
Data Structure Example:
Sql Data Structure
- A single Companion table with all fields from All extensions
*** TESTING ON BC2023W2 SEPTEMBER PREVIEW ***
Pull the Docker Preview from Powershell and install a single instance BC Preview release
Ps Code
# Install\Update Last Container Helper Module
install-module BCContainerHelper -force
#GetBCPreviewArtifact
$BCPublicPreviewArtifactUrl = Get-BCArtifactUrl -storageAccount BcPublicPreview –country us –type Sandbox -select Latest #GET LAST US Sandbox Preview
write $BCPublicPreviewArtifactUrl
# PREVIEW ARTIFACT AVAILABLE
https://BcPublicPreview.azureedge.net/sandbox/23.0.12034.12313/us
It runs!
The New Data Stack is revealed On SQL
We can see on the SQL installed on the docker how the data structure is created with the v3.0 extension model; in practice there is one table for the Microsoft APP base and a single table for the extensions, containing all the fields coming from the various apps installed.
SQL Server on Docker
Table extension : “$Ext” 🡪 with All published fields
NavAppextschemaSnapshot
Al Code Scenario
In this scenario, we create a new APP which extends the “Bank Account” table of the App base (which is already extended by another Microsoft App); we will see that there will be 2 active extensions that add fields to the standard app base but which will reside on only one extension table.
A new Page with standard and new fields
After publishing the extensions..
It’s possible to see how the structure is managed on the extensions table (the one Companion table); all fields are visible in the same extension.
The “Bank Account table” is extended just once.
Great!
