Get started with TMDL

Applies to: 👁 Image
SQL Server 2016 and later Analysis Services 👁 Image
Azure Analysis Services 👁 Image
Fabric/Power BI Premium

Before getting started with this article, be sure to thoroughly understand concepts described in Tabular Model Definition Language (TMDL) overview.

The easiest way to explore TMDL is to reference the Analysis Services Management Objects (AMO) Nuget package and use the TMDL API methods to serialize and deserialize to and from TMDL.

Get the Nuget packages

Get a TMDL model representation

The following code example shows how to get a TMDL model representation of a semantic model in a Power BI Premium workspace:

var workspaceXmla = " <Workspace XMLA address>";
var datasetName = "<dataset name>";
var outputPath = System.Environment.CurrentDirectory;

using (var server = new Microsoft.AnalysisServices.Tabular.Server())
{
 server.Connect(workspaceXmla);

 var database = server.Databases.GetByName(datasetName);

 var destinationFolder = $"{outputPath}\\{database.Name}-tmdl";

 Microsoft.AnalysisServices.Tabular.TmdlSerializer.SerializeDatabaseToFolder(database.Model, destinationFolder);

}

The output is a folder with a TMDL representation of the model, like this:

👁 Folder with a TMDL representation of a model

After serialization into a folder, use a text-editor to edit the TMDL files. For example, by using Visual Studio Code we can add a new measure, [Sales Amount (Computers)]:

/// Sales data for year over year analysis
table Sales 

 partition 'Sales-Part1' = m
 mode: Import 
 source =
 let
 …
 in
 #"Filtered Rows1"

 measure 'Sales Amount' = SUMX('Sales', [Quantity] * [Net Price])
 formatString: $ #,##0

 measure 'Sales Amount (Computers)' = CALCULATE([Sales Amount], 'Product'[Category] = "Computers")
 formatString: $ #,##0

For a better experience you can install Visual Studio Code TMDL language extension.

Deploy a TMDL model representation

The following code example shows how to deploy a TMDL model representation of the model to a Power BI Premium workspace:

var xmlaServer = "<Workspace XMLA address>";

var tmdlFolderPath = $"{System.Environment.CurrentDirectory}\\Contoso-tmdl";

var model = Microsoft.AnalysisServices.Tabular.TmdlSerializer.DeserializeModelFromFolder(tmdlFolderPath); 

using (var server = new Microsoft.AnalysisServices.Tabular.Server())
{
 server.Connect(xmlaServer);

 using (var remoteDatabase = server.Databases[model.Database.ID])
 {
 model.CopyTo(remoteDatabase.Model);

 remoteDatabase.Model.SaveChanges();
 } 
}

When executed, the new measure is deployed to the model.

👁 Sales Amount (Computers) measure in dataset

Handling TMDL serialization errors

When an error is detected in TMDL serialization methods, besides throwing a few common .NET exceptions like ArgumentException and InvalidOperationException, TMDL-specific exceptions are also returned.

• TmdlFormatException is thrown if the TMDL text is not a valid syntax. For example, invalid keyword or indentation.

• TmdlSerializationException is thrown if the TMDL text is valid, but violates the TOM metadata logic. For example, type of value does not match the expected type.

In addition to exception details, the following is included:

• document path: Path to the TMDL file with errors.
• line number: line number with errors.
• line text: line text with errors.

Code example handling TmdlFormatException:

try
{
 var tmdlPath = "<TMDL Folder Path>";

 var model = Microsoft.AnalysisServices.Tabular.TmdlSerializer.DeserializeDatabaseFromFolder(tmdlPath);
}
catch (Microsoft.AnalysisServices.Tabular.Tmdl.TmdlFormatException ex)
{
 Console.WriteLine($"Error on Deserializing TMDL '{ex.Message}', document path: '{ex.Document}' line number: '{ex.Line}', line text: '{ex.LineText}'");

 throw;
} 

Object text serialization

The following code example shows how to serialize a column into TMDL:

var output = Microsoft.AnalysisServices.Tabular.TmdlSerializer.SerializeObject(model.Tables["Product"].Columns["ProductKey"], qualifyObject: true);

Console.WriteLine(output);

Output:

ref table Product

 column ProductKey
 dataType: int64
 isKey
 formatString: 0
 isAvailableInMdx: false
 lineageTag: 4184d53e-cd2d-4cbe-b8cb-04c72a750bc4
 summarizeBy: none
 sourceColumn: ProductKey

 annotation SummarizationSetBy = Automatic

Stream serialization

The following code example shows how to serialize a semantic model to a single text variable:

var output = new StringBuilder();

foreach (Microsoft.AnalysisServices.Tabular.Serialization.MetadataDocument document in model.ToTmdl())
{
 using (TextWriter writer = new StringWriter(output))
 {
 document.WriteTo(writer);
 }
}

Console.WriteLine(output.ToString());

The following code example shows how to deserialize from TMDL, excluding the roles:

var context = Microsoft.AnalysisServices.Tabular.Serialization.MetadataSerializationContext.Create(MetadataSerializationStyle.Tmdl);

var files = Directory.GetFiles("[TMDL Directory Path]", "*.tmdl", SearchOption.AllDirectories);

foreach (var file in files)
{
 if (file.Contains("/roles/"))
 continue;

 using (TextReader reader = File.OpenText(file))
 { 
 context.ReadFromDocument(file, reader);
 }
}

var model = context.ToModel();