Large SSAS Tabular Table Emptying Old Partitions After Process Clear then Process Full on Latest Partition

Question

Hi there,

I'm reaching out today because I've run into some very strange trouble with SSAS Tabular that I haven't experienced in 2-3 years of using it to date.

Just for some quick infrastructure background, my active model is currently deployed on SQL Server Analysis Services 2019 Enterprise Edition with a Tabular model of compatibility level 1500, and the table size I'm working in is roughly estimated at ~400 MB for about ~200 million rows

Within this particular table, I have several partitions by year of varying size (some as small as 100k, some as large as 50m records) dating back to 2012 or so, and the idea is that 2012 - 2020 are individually processed (process full) only once, and 2021 is refreshed every night to pull the latest 2021 data (process clear and then process full). For anonymity purposes, I've provided my desensitized TMSL scripts below used in my SSIS package on 2 different steps:

{
"refresh": {
"type": "clearValues",
"objects": [
{
"database": "[My DB Name]",
"table": "[My Table]",
"partition": "2021"
}
]
}
}

{
"refresh": {
"type": "full",
"objects": [
{
"database": "[My DB Name]",
"table": "[My Table]",
"partition": "2021"
}
]
}
}

My issue is this --

Whenever I run my SSIS package with just these 2 steps, it does appear to successfully clear and reload 2021's values successfully, but when I go to a Power BI report that uses this model and try and load data for a previous year in the same table, all of the rows/measures for any non-2021 year are now blank! Even though I only Process Clear'd and Process Full'd the 2021 partition

I've never seen anything like this before, and I was wondering if someone might have any ideas? Is it possible the old partition data is getting cleared as well? When I use the SSMS UI to look at the partitions on my table, it still shows the row count for the rows that indeed should exist between 2012 - 2020, but for whatever reason despite showing a row count, no data is appearing on any DAX queries applied against prior year data in Power BI or evaluated in Management Studio

Any help at all would be amazing!

Thank you!

Answer 1

Hi,

Some thoughts besides the issue, that from what I know during process full, the data is cleared from an object if it already has some previously processed data. So I would not think the process clear part in this scene is necessary . Could you try use only process full in the TSML , see if this could work around the issue ?

Regards,
Lukas