I need to create a considerable number of partitions using XMLA scripts run through SSMS.
Is there a way to combine the creation of multiple partitions into one single script.
In below example, I need to execute the first script. When it finishes, I can open the other script and execute it as well. Very time consuming.
How can I restructure the code to run it in only one execution?
"createOrReplace": {
"object": {
"database": "MYDB",
"table": "MYTABLE1", "partition": "Partition"
},
"partition": {
"name": "Process_OLD", "dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source = #\"mySQL/MY_SCHEMA\",", " MY_SCHEMA= Source{[Schema=\"MY_SCHEMA\"]}[Data],",
" AllData = MY_SCHEMA{[Name=\"MYTABLE1\"]}[Data],", "\t#\"Filtered Rows\" = Table.SelectRows(AllData, each [DATE] < 20170101)",
"in",
" #\"Filtered Rows\""
]
}
}
}
}
"createOrReplace": {
"object": {
"database": "MYDB",
"table": "MYTABLE2", "partition": "Partition"
},
"partition": {
"name": "Process_NEW", "dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source = #\"mySQL/MY_SCHEMA\",", " MY_SCHEMA= Source{[Schema=\"MY_SCHEMA\"]}[Data],",
" AllData = MY_SCHEMA{[Name=\"MYTABLE1\"]}[Data],", "\t#\"Filtered Rows\" = Table.SelectRows(AllData, each [DATE] >= 20170101)",
"in",
" #\"Filtered Rows\""
]
}
}
}
}
You can put a sequence
command around it:
{
"sequence": {
"operations": [
{
"createOrReplace": {
"object": {
"database": "MYDB",
"table": "MYTABLE1",
"partition": "Partition"
},
"partition": {
"name": "Process_OLD",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source = #\"mySQL/MY_SCHEMA\",",
" MY_SCHEMA= Source{[Schema=\"MY_SCHEMA\"]}[Data],",
" AllData = MY_SCHEMA{[Name=\"MYTABLE1\"]}[Data],",
"\t#\"Filtered Rows\" = Table.SelectRows(AllData, each [DATE] < 20170101)",
"in",
" #\"Filtered Rows\""
]
}
}
}
},
{
"createOrReplace": {
"object": {
"database": "MYDB",
"table": "MYTABLE2",
"partition": "Partition"
},
"partition": {
"name": "Process_NEW",
"dataView": "full",
"source": {
"type": "m",
"expression": [
"let",
" Source = #\"mySQL/MY_SCHEMA\",",
" MY_SCHEMA= Source{[Schema=\"MY_SCHEMA\"]}[Data],",
" AllData = MY_SCHEMA{[Name=\"MYTABLE1\"]}[Data],",
"\t#\"Filtered Rows\" = Table.SelectRows(AllData, each [DATE] >= 20170101)",
"in",
" #\"Filtered Rows\""
]
}
}
}
}
]
}
}