Semantic Model Definition Language (Text Format)

The Semantic Model Definition Language (SMDL) is a JSON-based format used to define tabular models in SQL Server Analysis Services (SSAS) and Azure Analysis Services (AAS). This text-based format allows for efficient management, version control, and programmatic manipulation of your tabular models.

Overview

SMDL represents the entire structure of a tabular model, including tables, columns, relationships, measures, calculated columns, roles, and other model objects. It adheres to a well-defined schema, ensuring consistency and enabling tooling to parse and generate model definitions.

The primary benefits of using SMDL include:

Version Control: Easily track changes to your model definition using source control systems like Git.
Automation: Programmatically generate, modify, and deploy models.
Collaboration: Facilitate team collaboration on model development.
Portability: Move model definitions between different environments and versions of Analysis Services.

Key Concepts

A typical SMDL file is a JSON object representing the entire model. Here are some of the core elements:

Model Structure

The root of the SMDL document defines the model itself, including its name, culture, and other global properties. It then contains arrays for various model objects.

Tables

Each table in the model is represented as an object within the tables array. A table object includes its name, columns, and potentially other properties like partitions or calculated columns.

Columns

Columns are defined within each table's columns array. Each column has properties such as its name, data type, whether it's a key, and its format string.

Relationships

Relationships between tables are defined in the relationships array. Each relationship specifies the source table and column, and the target table and column.

Measures

Measures are calculations defined using DAX. They are typically stored in the measures array, associated with a specific table.

Roles

Security roles for the model are defined in the roles array, specifying user memberships and row-level security filters.

Example SMDL Structure

Below is a simplified example illustrating the basic structure of an SMDL file:

JSON


{
  "model": {
    "name": "SalesModel",
    "culture": "en-US",
    "tables": [
      {
        "name": "DimProduct",
        "columns": [
          {
            "name": "ProductKey",
            "dataType": "int",
            "isKey": true
          },
          {
            "name": "ProductName",
            "dataType": "string"
          }
        ]
      },
      {
        "name": "FactSales",
        "columns": [
          {
            "name": "SalesKey",
            "dataType": "int",
            "isKey": true
          },
          {
            "name": "ProductKey",
            "dataType": "int"
          },
          {
            "name": "SalesAmount",
            "dataType": "decimal"
          }
        ],
        "measures": [
          {
            "name": "Total Sales",
            "expression": "SUM(FactSales[SalesAmount])"
          }
        ]
      }
    ],
    "relationships": [
      {
        "name": "ProductSales",
        "fromTable": "DimProduct",
        "fromColumn": "ProductKey",
        "toTable": "FactSales",
        "toColumn": "ProductKey"
      }
    ],
    "roles": [
      {
        "name": "Sales Managers",
        "members": [],
        "rules": []
      }
    ]
  }
}

Working with SMDL

You can interact with SMDL through various means:

SQL Server Management Studio (SSMS): SSMS provides tools to view and edit tabular models, often exporting them to or importing from SMDL.
Visual Studio (with Analysis Services Projects): The Analysis Services projects extension in Visual Studio allows for model development and can export/import models in SMDL format.
Tabular Editor: A popular third-party tool that offers advanced capabilities for editing and managing tabular models, extensively using SMDL.
Power BI Desktop: While Power BI Desktop uses its own internal model format, it can be integrated with Analysis Services for deployment, and the underlying concepts are related.
Programmatic Access: Using Analysis Services Management Objects (AMO) or TOM (Tabular Object Model) libraries in .NET, you can read, write, and manipulate SMDL files programmatically.

Important

Always refer to the official Microsoft documentation for the most up-to-date schema definition and best practices for SMDL.

Schema Reference

For a complete understanding of the SMDL schema, consult the official schema definition documents. These documents detail all available properties, data types, and constraints for each object within the model definition.

Link to Official SMDL Schema Definition (Note: This is a placeholder link)