Skip to content

Useful script snippets

Daniel Otykier edited this page Mar 8, 2019 · 25 revisions

Here's a collection of small script snippets to get you started using the Advanced Scripting functionality of Tabular Editor. Many of these scripts are useful to save as Custom Actions, so that you can easily reuse them from the context menu.'

Table of contents


Create measures from columns

// Creates a SUM measure for every currently selected column and hide the column.
foreach(var c in Selected.Columns)
{
    c.Table.AddMeasure(
        "Sum of " + c.Name,                    // Name
        "SUM(" + c.DaxObjectFullName + ")",    // DAX expression
        c.DisplayFolder                        // Display Folder
    );
    c.IsHidden = true;
}

This snippet uses the <Table>.AddMeasure(<name>, <expression>, <displayFolder>) function to create a new measure on the table. We use the DaxObjectFullName property to get the fully qualified name of the column for use in the DAX expression: 'TableName'[ColumnName].


Generate Time Intelligence measures

First, create custom actions for individual Time Intelligence aggregations. For example:

// Creates a TOTALYTD measure for every selected measure.
foreach(var m in Selected.Measures) {
    m.Table.AddMeasure(
        m.Name + " YTD",                                       // Name
        "TOTALYTD(" + m.DaxObjectName + ", 'Date'[Date])",     // DAX expression
        m.DisplayFolder                                        // Display Folder
    );
}

Here, we use the DaxObjectName property, to generate an unqualified reference for use in the DAX expression, as this is a measure: [MeasureName]. Save this as a Custom Action called "Time Intelligence\Create YTD measure" that applies to measures. Create similar actions for MTD, LY, and whatever else you need. Then, create the following as a new action:

// Invoke all Time Intelligence Custom Actions:
CustomAction(@"Time Intelligence\Create YTD measure");
CustomAction(@"Time Intelligence\Create MTD measure");
CustomAction(@"Time Intelligence\Create LY measure");

This illustrates how you can execute one (or more) Custom Actions from within another action (beware of circular references - that will cause Tabular Editor to crash). Save this as a new Custom Action "Time Intelligence\All of the above", and you will have an easy way to generate all your Time Intelligence measures with a single click:

image

Including additional properties

If you want to set additional properties on the newly created measure, the above script can be modified like so:

// Creates a TOTALYTD measure for every selected measure.
foreach(var m in Selected.Measures) {
    var newMeasure = m.Table.AddMeasure(
        m.Name + " YTD",                                       // Name
        "TOTALYTD(" + m.DaxObjectName + ", 'Date'[Date])",     // DAX expression
        m.DisplayFolder                                        // Display Folder
    );
    newMeasure.FormatString = m.FormatString;               // Copy format string from original measure
    foreach(var c in Model.Cultures) {
        newMeasure.TranslatedNames[c] = m.TranslatedNames[c] + " YTD"; // Copy translated names for every culture
        newMeasure.TranslatedDisplayFolders[c] = m.TranslatedDisplayFolders[c]; // Copy translated display folders
    }
}

Handling perspectives

Measures, columns, hierarchies and tables all expose the InPerspective property, which holds a True/False value for every perspective in the model, that indicates if the given object is a member of that perspective or not. So for example:

foreach(var measure in Selected.Measures)
{
    measure.InPerspective["Inventory"] = true;
    measure.InPerspective["Reseller Operation"] = false;
}

The script above ensures that all selected measures are visible in the "Inventory" perspective and hidden in the "Reseller Operation" perspective.

In addition to getting/setting the membership in an individual perspective, the InPerspective property also supports the following methods:

  • <<object>>.InPerspective.None() - removes the object from all perspectives.
  • <<object>>.InPerspective.All() - includes the object in all perspectives.
  • <<object>>.CopyFrom(string[] perspectives) - includes the object in all specified perspectives (array of string containing names of the perspectives).
  • <<object>>.CopyFrom(perspectiveIndexer perspectives) - copies perspective inclusions from another InPerspective property.

The latter may be used to copy perspective memberships from one object to another. For example, say have a base measure [Reseller Total Sales], and you want to make sure that all currently selected measures are visible in the same perspectives as this base measure. The following script does the trick:

var baseMeasure = Model.Tables["Reseller Sales"].Measures["Reseller Total Sales"];

foreach(var measure in Selected.Measures)
{
    /* Uncomment the line below, if you want 'measure' to be hidden
       from perspectives that 'baseMeasure' is hidden in: */
    // measure.InPerspective.None();

    measure.InPerspective.CopyFrom(baseMeasure.InPerspective);
}

This technique can be used also when generating new objects from code. For example, if we want to ensure that auto-generated Time Intelligence measures are only visible in the same perspectives as their base measure, we can extend the script from the previous section as:

// Creates a TOTALYTD measure for every selected measure.
foreach(var m in Selected.Measures) {
    var newMeasure = m.Table.AddMeasure(
        m.Name + " YTD",                                       // Name
        "TOTALYTD(" + m.DaxObjectName + ", 'Date'[Date])",     // DAX expression
        m.DisplayFolder                                        // Display Folder
    );
    newMeasure.InPerspective.CopyFrom(m.InPerspective);        // Apply perspectives from the base measure
}

Export object properties to a file

For some workflows, it may be useful to edit multiple object properties in bulk using Excel. Use the following snippet to export a standard set of properties to a .TSV file, which can then be subsequently imported (see below).

// Export properties for the currently selected objects:
var tsv = ExportProperties(Selected);
SaveFile("Exported Properties 1.tsv", tsv);

The resulting .TSV file looks like this, when opened in Excel: image The contents of the first column (Object) is a reference to the object. If the contents of this column is changed, subsequent import of the properties might not work correctly. To change the name of an object, only change the value in the second column (Name).

By default, the file is saved to the same folder as TabularEditor.exe is located. By default, only the following properties are exported (where applicable, depending on the type of object exported):

  • Name
  • Description
  • SourceColumn
  • Expression
  • FormatString
  • DataType

To export different properties, supply a comma-separated list of property names to be exported as the 2nd argument to ExportProperties:

// Export the names and Detail Rows Expressions for all measures on the currently selected table:
var tsv = ExportProperties(Selected.Table.Measures, "Name,DetailRowsExpression");
SaveFile("Exported Properties 2.tsv", tsv);

The available property names can be found in the TOM API documentation. These are mostly identical to the names shown in the Tabular Editor property grid in CamelCase and with spaces removed (with a few exceptions, for example, the "Hidden" property is called IsHidden in the TOM API).

To import properties, use the following snippet:

// Imports and applies the properties in the specified file:
var tsv = ReadFile("Exported Properties 1.tsv");
ImportProperties(tsv);

Generating documentation

The ExportProperties method shown above, can also be used if you want to document all or parts of your model. The following snippet will extract a set of properties from all visible measures or columns in a Tabular Model, and save it as a TSV file:

// Construct a list of all visible columns and measures:
var objects = Model.AllMeasures.Where(m => !m.IsHidden && !m.Table.IsHidden).Cast<ITabularNamedObject>()
      .Concat(Model.AllColumns.Where(c => !c.IsHidden && !c.Table.IsHidden));

// Get their properties in TSV format (tabulator-separated):
var tsv = ExportProperties(objects,"Name,ObjectType,Parent,Description,FormatString,DataType,Expression");

// (Optional) Output to screen (can then be copy-pasted into Excel):
// tsv.Output();

// ...or save the TSV to a file:
SaveFile("documentation.tsv", tsv);

Creating Data Columns from Partition Source metadata

Note: If you're using version 2.7.2 or newer, make sure to try the new "Import Table..." feature.

If a table uses a Query partition based on an OLE DB provider data source, we can automatically refresh the column metadata of that table by executing the following snippet:

Model.Tables["Reseller Sales"].RefreshDataColumns();

This is useful when adding new tables to a model, to avoid having to create every Data Column on the table manually. The snippet above assumes that the partition source can be accessed locally, using the existing connection string of the Partition Source for the 'Reseller Sales' table. The snippet above will extract the schema from the partition query, and add a Data Column to the table for every column in the source query.

If you need to supply a different connection string for this operation, you can do that in the snippet as well:

var source = Model.DataSources["DWH"] as ProviderDataSource;
var oldConnectionString = source.ConnectionString;
source.ConnectionString = "...";   // Enter the connection string you want to use for metadata refresh
Model.Tables["Reseller Sales"].RefreshDataColumns();
source.ConnectionString = oldConnectionString;

This assumes that the partitions of the 'Reseller Sales' table is using a Provider Data Source with the name "DWH".


Format DAX expressions

Please don't abuse this. Each call to FormatDax will generate one web request to www.daxformatter.com, so please take care not to loop over several hundred measures if you don't need to. daxformatter.com is a free service after all, so let's not DDoS-attack them from Tabular Editor...

// Works in Tabular Editor version 2.7.3 or newer:
Selected.Measures.ForEach(m => m.Expression = FormatDax(m.Expression));

Generate list of source columns for a table

The following script outputs a nicely formatted list of source columns for the currently selected table. This may be useful if you want to replace partition queries that use SELECT * with explicit columns.

string.Join(",\r\n", 
    Selected.Table.DataColumns
        .OrderBy(c => c.SourceColumn)
        .Select(c => "[" + c.SourceColumn + "]")
    ).Output();

Auto-creating relationships

If you’re consistently using a certain set of naming conventions within your team, you’ll quickly find that scripts can be even more powerful.

The following script, when executed on one or more fact tables, will automatically create relationships to all relevant dimension tables, based on column names. The script will search for fact table columns having the name pattern xxxyyyKey where the xxx is an optional qualifier for role-playing use, and the yyy is the dimension table name. On the dimension table, a column named yyyKey must exist and have the same data type as the column on the fact table. For example, a column named “ProductKey” will be related to the “ProductKey” column on the Product table. You can specify a different column name suffix to use in place of "Key".

If a relationship already exists between the fact and dimension table, the script will create the new relationship as inactive.

var keySuffix = "Key";

// Loop through all currently selected tables (assumed to be fact tables):
foreach(var fact in Selected.Tables)
{
    // Loop through all SK columns on the current table:
    foreach(var factColumn in fact.Columns.Where(c => c.Name.EndsWith(keySuffix)))
    {
        // Find the dimension table corresponding to the current SK column:
        var dim = Model.Tables.FirstOrDefault(t => factColumn.Name.EndsWith(t.Name + keySuffix));
        if(dim != null)
        {
            // Find the key column on the dimension table:
            var dimColumn = dim.Columns.FirstOrDefault(c => factColumn.Name.EndsWith(c.Name));
            if(dimColumn != null)
            {
                // Check whether a relationship already exists between the two columns:
                if(!Model.Relationships.Any(r => r.FromColumn == factColumn && r.ToColumn == dimColumn))
                {
                    // If relationships already exists between the two tables, new relationships will be created as inactive:
                    var makeInactive = Model.Relationships.Any(r => r.FromTable == fact && r.ToTable == dim);

                    // Add the new relationship:
                    var rel = Model.AddRelationship();
                    rel.FromColumn = factColumn;
                    rel.ToColumn = dimColumn;
                    factColumn.IsHidden = true;
                    if(makeInactive) rel.IsActive = false;
                }
            }
        }
    }
}

Create DumpFilters measure

Inspired by this article, here's a script that will create a [DumpFilters] measure on the currently selected table:

var dax = "VAR MaxFilters = 3 RETURN ";
var dumpFilterDax = @"IF (
    ISFILTERED ( {0} ), 
    VAR ___f = FILTERS ( {0} )
    VAR ___r = COUNTROWS ( ___f )
    VAR ___t = TOPN ( MaxFilters, ___f, {0} )
    VAR ___d = CONCATENATEX ( ___t, {0}, "", "" )
    VAR ___x = ""{0} = "" & ___d 
        & IF(___r > MaxFilters, "", ... ["" & ___r & "" items selected]"") & "" ""
    RETURN ___x & UNICHAR(13) & UNICHAR(10)
)";

// Loop through all columns of the model to construct the complete DAX expression:
bool first = true;
foreach(var column in Model.AllColumns)
{
    if(!first) dax += " & ";
    dax += string.Format(dumpFilterDax, column.DaxObjectFullName);
    if(first) first = false;
}

// Add the measure to the currently selected table:
Selected.Table.AddMeasure("DumpFilters", dax);

CamelCase to Proper Case

A common naming scheme for columns and tables on a relation database, is CamelCase. That is, names do not contain any spaces and individual words start with a capital letter. In a Tabular model, tables and columns that are not hidden, will be visible to business users, and so it would often be preferable to use a "prettier" naming scheme. The following script will convert CamelCased names to ProperCase. Sequences of uppercase letters are kept as-is (acronyms). For example, the script will convert the following:

  • CustomerWorkZipcode to Customer Work Zipcode
  • CustomerAccountID to Customer Account ID
  • NSASecurityID to NSA Security ID

I highly recommend saving this script as a Custom Action that applies to all object types (except Relationships, KPIs, Table Permissions and Translations, as these do not have an editable "Name" property):

foreach(var obj in Selected.OfType<ITabularNamedObject>()) {
    var oldName = obj.Name;
    var newName = new System.Text.StringBuilder();
    for(int i = 0; i < oldName.Length; i++) {
        // First letter should always be capitalized:
        if(i == 0) newName.Append(Char.ToUpper(oldName[i]));

        // A sequence of two uppercase letters followed by a lowercase letter should have a space inserted
        // after the first letter:
        else if(i + 2 < oldName.Length && char.IsLower(oldName[i + 2]) && char.IsUpper(oldName[i + 1]) && char.IsUpper(oldName[i]))
        {
            newName.Append(oldName[i]);
            newName.Append(" ");
        }

        // All other sequences of a lowercase letter followed by an uppercase letter, should have a space
        // inserted after the first letter:
        else if(i + 1 < oldName.Length && char.IsLower(oldName[i]) && char.IsUpper(oldName[i+1]))
        {
            newName.Append(oldName[i]);
            newName.Append(" ");
        }
        else
        {
            newName.Append(oldName[i]);
        }
    }
    obj.Name = newName.ToString();
}
You can’t perform that action at this time.