is there a way to process a tabular model's tables in sequence?
-
Hi, i'm reading the posts at https://stackoverflow.com/questions/77508309/how-to-process-full-tables-in-tabular-ssas-cube-using-xmla-file and https://learn.microsoft.com/en-us/analysis-services/tmsl/sequence-command-tmsl?view=asallproducts-allversions and thinking "maybe not". My hope was to script some tmsl in one step in my sql agent job that would process one table at a time probably dims first. There is only one fact table.
The reason i want these things single threaded (sequenced) is that we are getting a memory error on processing one particular db full. My concern is that if too much parallelism occurs processing individual tables in that db, i'll have the same memory problem all over. It looks to me like the sequence command cant be used with the refresh . But I'm not sure.
I want to do something like this perhaps with the sequence command...i'm dreading having to make 11 steps for all 11 tables. And am already wondering if ssas might get confused anyway if some prerequisite isnt met on one of the tables.
{
"refresh": {
"type": "full",
"objects": [
{
"database": "db",
"table":"table1"
},
{
"database": "db",
"table":"table2"
},
{
"database": "db",
"table":"table3"
}
]
}
}
-
You can get SSMS to do all of the table-by-table scripting for you, no need for any dread!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
my thought was that a "thread" would lessen the chance of a memory error. And that that was one of the tricks out there for getting around some memory issues. I split the script (was full on the db) into 2 steps in my sql agent job. The first refreshes dims (about 10 of them) at the table level but doesn't try to sequence them so i guess i got some parallelism. The second, refreshes the only fact table of 50+ million rows. The first step worked early this morning. The second went down on memory.
-
You can avoid all parallelism by using the Sequence command in your JSON and setting maxParallelism to 1.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
thanks Phil. I couldnt see how to author the sequence command along with the refresh format i showed in the original post. I think MS is telling us that only certain things are compatible with sequence, and refresh isnt one of them. And my thanks for reminding me that this is a post about sequence, not memory issues. I'm going to move the memory challenge over to a new post.
- stan wrote:
I think MS is telling us that only certain things are compatible with sequence, and refresh isnt one of them.
You are correct, my apologies!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
no problem. I moved the memory part of the discussion to a different thread. And i'll mark your post here as an answer.
-
hold the phone. I havent tried it yet but here is a link that explains how to mix sequence and refresh. I have to stare at the curly braces to see if it does what it claims to.
https://stackoverflow.com/questions/65620288/how-to-clear-then-refresh-database-in-one-json-block
-
Not a lot of upvotes against that post!
Please post back with any findings.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
it appears to run.
{
"sequence": {
"maxParallelism": 1,
"operations": [
{
"refresh": {
"type": "clearValues",
"objects": [
{
"database": "MyDB"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table1"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table2"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table3"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table4"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table5"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table6"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table7"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table8"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table9"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table10"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"Table11"
}
]
}
},
{
"refresh": {
"type": "full",
"objects": [
{
"database": "MyDB",
"table":"FactTable"
}
]
}
}
]
}
}
-
Well done!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply