Search code examples
ssmsssaspartitioningssas-tabular

SSMS Tabular Model: create multiple partition via one single xmla script


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?

Script 1:

  "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\""
        ]
      }
    }
  }
}

Script 2:

  "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\""
        ]
      }
    }
  }
}

Solution

  • 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\""
                  ]
                }
              }
            }
          }
        ]
      }
    }