Analysis Services models in Azure Fabric -- METADATA$ROW_ID gotcha
I've been working in and around SQL for over 3 decades.
I am currently working on making our business intelligence systems self-driven by using an event-based architecture. This involves providing curated data in some of the largest SQL Azure Analysis Services cubes ever built (over 6 billion records in 1 table alone).
I've developed several APIs leveraging a serverless Azure architecture to provide real-time data to various internal processes and projects.
Currently working on Project Dormouse; Durable select messaging queue that enables polling processes to extract specific messages.
For those of you entering the Microsoft Fabric foray, there is a big caveat you must be aware of.
If you are using Fabric Mirroring or Delta Lake tables you will see an additional column in your data source: METADATA$ROW_ID. This column is added automatically by Fabric to manage versioning, change data capture, and transactional consistency. What exactly is this column, and you need to care.
The column is, for all intents and purposes, a SQL GUID, which is a 40-character unique identifier and because it is unique it is uncompressible, and this is the problem. Analysis Services models within Fabric are limited by your SKU, so space is at a premium. We are running an F64, so that limits our model size to 25GB.
When you create a model, it does a select * from <source>, which was never an issue until we started using a Mirrored source. That METADATA$ROW_ID snuck into our model and caused the size to explode. Our complete 5-year model normally takes up about 6GB of space, but this would blow out around 18 months. Digging into the models from both Azure Analysis Services and Fabric Analysis Services we found that sneaky ID.
The correction is easy, now that we found the issue, so I am passing it on here in hopes of preventing others the headaches that come from details buried in specific implementations.
Go into your semantic model through DAX Studio and manually remove the METADATA$ROW_ID column. Do this after you have created your model as it is simpler to remove the column than to modify the build process to exclude it.
