June 2017 - Chris Koester

Process SSAS Tabular Tables and Partitions with TMSL

On: 2017-06-21

This post shows how to process SSAS Tabular tables and partitions with TMSL. TMSL stands for Tabular Model Scripting Language, and it is used for working with SSAS Tabular databases programmatically starting with compatibility level 1200 (SQL Server 2016). Multidimensional models and previous versions of SQL Server all use Analysis Services Scripting Language (ASSL for XMLA). A common use for TMSL is automating the processing of tabular models using the SQL Agent, SSIS, PowerShell, or some other application. One typical processing sequence is a series of Process Data commands followed by a Process Recalc at the end. Here’s what the TMSL code looks like in this scenario:

{
  "sequence": {
    "operations": [
      {
        "refresh": {
          "type": "dataOnly",
          "objects": [
            {
              "database": "DatabaseName",
              "table": "Table1Name",
              "partition": "Table1Partition"
            },
            {
              "database": "DatabaseName",
              "table": "Table2Name",
              "partition": "Table2Partition"
            },
            {
              "database": "DatabaseName",
              "table": "Table3Name"
            },
            {
              "database": "DatabaseName",
              "table": "Table4Name"
            }
          ]
        }
      },
      {
        "refresh": {
          "type": "calculate",
          "objects": [
            {
              "database": "DatabaseName"
            }
          ]
        }
      }
    ]
  }
}

{

"sequence": {

"operations": [

{

"refresh": {

"type": "dataOnly",

"objects": [

{

"database": "DatabaseName",

"table": "Table1Name",

"partition": "Table1Partition"

{

"database": "DatabaseName",

"table": "Table2Name",

"partition": "Table2Partition"

{

"database": "DatabaseName",

"table": "Table3Name"

{

"database": "DatabaseName",

"table": "Table4Name"

}

]

}

{

"refresh": {

"type": "calculate",

"objects": [

{

"database": "DatabaseName"

}

]

}

]

}

Download JSON Data with SSIS

By: Chris Koester

On: 2017-06-06

In: Data Integration

With: 1 Comment

In a post on the Allegient blog, I showed how you can download JSON data with PowerShell. Now we’ll take a look at how to download JSON data with SSIS. As a dedicated data integration tool, SSIS has many advantages over PowerShell for this type of work. SSIS does not have a built in source component for JSON data, so you’ll have to use the Script Component. When you drag the Script Component into a data flow, you’ll be asked how you want to use it. In this case you’ll use it as a source. Define Output Columns Once the Script Component has been defined asRead More →