Thank you to everyone who made it out to PASS Data Community Summit! I have greatly enjoyed meeting so many fellow data nerds and have learned so much from all of you. Speaking in person is always a joy, but this crowd was one of the best. Thank you for laughing with me and asking great engaging questions. If you haven’t already, please connect with me on LinkedIN and reach out with any additional questions or fun stories as you begin to scale your Power BI! If you have seen this presentation before, please check out the section where we use actual DAX inside a calculated column and measure instead of just text. It was an adventure to update the code the day of presenting, but well worth it to add a little more flair to the presentation.
As promised, below is the code used in my presentation as well as a link to my GitHub with the presentation and zip file to run the code on your own laptop. Happy coding everyone!
Abstract
Tune in to learn how to programmatically add columns and measures to Power BI data models using TOM, XMLA, and C#! It is rare to find a Power BI developer who has a background in C#, but C# and other programming languages offer a lot of automation and scalability that is lacking in Power BI development. The XMLA endpoint is a powerful tool available in the online Power BI service that allows report developers to connect to their data model and adjust a variety of entities outside the Power BI Desktop application. For example, the XMLA endpoints can be used within a pipeline triggered by an application to update a Power BI model schema. This allows end users to create custom UDFs (user defined fields) on the fly and delete them. Similarly, developers can create and use translations for customer specific column renames without worrying about breaking visuals and complicated data models.
Links
- LinkedIN – https://www.linkedin.com/in/kristyna-hughes-dataonwheels/
- GitHub – https://github.com/Anytsirk12/DataOnWheels/tree/main/PASS%20Data%20Community%20Summit%202022
- Analysis Services for .NET Microsoft Documentation – https://learn.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.measure?view=analysisservices-dotnet
- Tabular Object Model Intro – https://learn.microsoft.com/en-us/analysis-services/tom/introduction-to-the-tabular-object-model-tom-in-analysis-services-amo?view=asallproducts-allversions
Final C# Code (now with DAX calculated columns!)
using System;
using Microsoft.AnalysisServices.Tabular;
namespace TOM_Testing_Live
{
internal class Program
{
static void Main(string[] args)
{
//-------------- Connect to PBI Premium Workspace ---------------- //
// create the connect string - powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE_NAME
string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/Happy%20Coding";
string connectString = $"DataSource={workspaceConnection};";
// connect to the Power BI workspace referenced in connect string
Server server = new Server();
server.Connect(connectString);
//---------------- List out current state before we make changes -------------------//
// enumerate through datasets in workspace to display their names
foreach (Database database in server.Databases)
{
Console.WriteLine($"ID : {database.ID}, Name : {database.Name}, CompatibilityLevel: {database.CompatibilityLevel}, Last Updated : {database.LastSchemaUpdate}");
}
// enumerate through tables in one database (use the database ID from previous step)
Model model = server.Databases["5d161e6b-697a-49b6-a3e6-7d19b940a8fd"].Model;
//if you don't specify a database, it will only grab models from the first database in the list
foreach (Table table in model.Tables)
{
Console.WriteLine($"Table : {table.Name} IsHidden? : {table.IsHidden}");
}
// Specify a single table in the dataset
Table table_product = model.Tables["Product"];
// Specify a single table in the dataset (our measure table)
Table table_measures = model.Tables["_Measures"];
// List out the columns in the product table
foreach (Column column in table_product.Columns)
{
Console.WriteLine($"Columns: {column.Name}");
}
// List out the measures in the product table
foreach (Measure productmeasures in table_product.Measures)
{
Console.WriteLine($"Measures: {productmeasures.Name}");
}
// List of translations on the model
foreach (Culture culture in model.Cultures)
{
Console.WriteLine($"Existing Culture: {culture.Name}");
}
// List out the hierarchies in the product table
foreach (Hierarchy hierarchy in table_product.Hierarchies)
{
Console.WriteLine($"Hierarchies: {hierarchy.Name}, Lineage Tag = {hierarchy.LineageTag}");
}
// List out the levels in our category hierarchy
if (table_product.Hierarchies.ContainsName("Category Hierarchy"))
{
Hierarchy hierarchy_category_ = table_product.Hierarchies["Category Hierarchy"];
foreach (Level level_categoryhierarchy in hierarchy_category_.Levels)
{
Console.WriteLine($"Category Hierarchy Level {level_categoryhierarchy.Ordinal}: {level_categoryhierarchy.Name} Lineage Tag: {level_categoryhierarchy.LineageTag} from {level_categoryhierarchy.Column.Name}");
}
}
else
{
Console.WriteLine($"No Category Hierarchy");
}
//----------------Deleting columns and measures ----------------//
/*
// Deleting a column if it exists
if (table_product.Columns.ContainsName("Testing"))
//this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Column Exists");
table_product.Columns.Remove("Testing"); //if the column exists, this will remove it
Console.WriteLine($"Column Deleted");
}
else
{
Console.WriteLine($"Column Does Not Exist");
}
// Deleting a measure if it exists
if (table_measures.Measures.ContainsName("Test Measure"))
//this looks to see if there is a measure already named "Test Measure"
{
Console.WriteLine($"Measure Exists");
table_measures.Measures.Remove("Test Measure"); //if the measure exists, this will remove it
Console.WriteLine($"Measure Deleted");
}
else
{
Console.WriteLine($"Measure Does Not Exist");
}
*///--------------- Adding columns and measures --------------------//
// Adding our column if it doesn't already exist
if (table_product.Columns.ContainsName("Testing")) //this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Column Exists");
}
else
{
Column column_testing = new DataColumn() //this will add the column
{
Name = "Testing", //name your column for Power BI
DataType = DataType.String, //set the data type
SourceColumn = "Product", //this must match the name of the column your source
LineageTag = "product-testing-column"
};
table_product.Columns.Add(column_testing);
Console.WriteLine($"Column Created!");
}
//Get the partition sources for each table
foreach (DataSource datasource in model.DataSources)
{
Console.WriteLine($"Datasource : {datasource.Name}");
};
// Add a measure if it doesn't already exist in a specified table
if (table_measures.Measures.ContainsName("Test Measure"))
{
Measure measure = table_measures.Measures["Test Measure"];
measure.Expression = ""Hello there""; //you can update an existing measure using this script
Console.WriteLine($"Measure Exists");
}
else
{
Measure measure = new Measure()
{
Name = "Test Measure",
Expression = ""Hello World"", //you can also use DAX here
LineageTag = "test-measure"
};
table_measures.Measures.Add(measure);
Console.WriteLine($"Measure Added");
}
//--------------- Adding columns and measures with DAX functions! --------------------//
// Adding our column if it doesn't already exist
if (table_product.Columns.ContainsName("List Price w Tax")) //this looks to see if there is a column already named "Testing"
{
Console.WriteLine($"Calculated Column Exists");
}
else
{
Column column_testing = new CalculatedColumn() //this will add the column
{
Name = "List Price w Tax", //name your column for Power BI
DataType = DataType.Decimal, //set the data type
Expression = "'Product'[List Price] * 1.05", //this is the DAX used to create your new column!
LineageTag = "testing_w_dax-column"
};
table_product.Columns.Add(column_testing);
Console.WriteLine($"Calculated Column Created!");
}
// Add a measure if it doesn't already exist
if (table_measures.Measures.ContainsName("DAX Measure Example"))
{
Measure measure = table_measures.Measures["DAX Measure Example"];
measure.Expression = "SUM(Sales[Total Product Cost])+100"; //you can update an existing measure using this script
Console.WriteLine($"DAX Measure Exists");
}
else
{
Measure measure = new Measure()
{
Name = "DAX Measure Example",
Expression = "SUM(Sales[Total Product Cost])",
LineageTag = "test-measure-with-dax"
};
table_measures.Measures.Add(measure);
Console.WriteLine($"DAX Measure Added");
}
//------------------- Manipulating Hierarchies ---------------------//
//Editing an existing hierarchy originally called Category Hierarchy
//Once you rename the hierarchy, you'll have to adjust this code to the renamed Hierarchy
Hierarchy hierarchy_category = table_product.Hierarchies["Category Hierarchy Rename"];
{
hierarchy_category.Name = "Category Hierarchy Rename"; //this renames the hierarchy, note the lineage tag will remain unchanged
Console.WriteLine($"Category Hierarchy Renamed");
}
//Editing an existing hierarchy level
Level level_Category = hierarchy_category.Levels.FindByLineageTag("ca792793-d3c3-4b5d-9bee-2b46c01833bb");
Level level_Subcategory = hierarchy_category.Levels.FindByLineageTag("4304e645-bb8f-4d7e-b25c-f629be2110d8");
{
level_Category.Name = "Category";
level_Category.Ordinal = 1;
level_Subcategory.Name = "Subcategory";
level_Subcategory.Ordinal = 0;
Console.WriteLine($"Category Hierarchy Levels Renamed & Reordered");
}
//Adding a new level to the hierarchy if it doesn't already exist
if (hierarchy_category.Levels.ContainsName("Model"))
{
Console.WriteLine($"Hierarchy Level Exists");
}
else
{
Level level_Model = new Level()
{
Name = "Model",
Ordinal = 2,
Column = table_product.Columns.Find("Model"),
LineageTag = "hierarchy-model-column"
};
hierarchy_category.Levels.Add(level_Model);
Console.WriteLine($"Hierarchy Level Added");
}
//Add a new hierarchy if it doesn't already exist
if (table_product.Hierarchies.ContainsName("New Hierarchy"))
{
Console.WriteLine($"New Hierarchy Exists");
}
else
{
Hierarchy hiearchy_new = new Hierarchy()
{
Name = "New Hierarchy",
LineageTag = "new-hierarchy-lineage-tag"
};
table_product.Hierarchies.Add(hiearchy_new);
Console.WriteLine($"Hierarchy Added");
//Creating levels to add to the new hierarchy
Level level_one = new Level()
{
Name = "Model",
Ordinal = 0,
Column = table_product.Columns.Find("Model"),
LineageTag = "product-model-column"
};
Level level_two = new Level()
{
Name = "Product",
Ordinal = 1,
Column = table_product.Columns.Find("Product"),
LineageTag = "product-product-column"
};
hiearchy_new.Levels.Add(level_one);
hiearchy_new.Levels.Add(level_two);
Console.WriteLine($"Levels added to new hiearchy");
};
//-------------------------- Translations ------------------------------//
//Translations can be used to rename existing columns without rebuilding the model. This also updates any visuals that use that column.
// List of translations on the model
foreach (Culture culture in model.Cultures)
{
Console.WriteLine($"Existing Culture: {culture.Name}");
}
// Let's get a list of the existing translations within the en_US culture
Culture enUsCulture = model.Cultures.Find("en-US");
foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations)
{
Console.WriteLine($"Translated Object: {objectTranslation.Value}");
}
// Narrow down what column within this culture/language you would like to add the translation to
MetadataObject dataColumn = table_product.Columns.Find("SKU"); //this needs to always be the original column name within the data model.
ObjectTranslation proposedTranslation = enUsCulture.ObjectTranslations[dataColumn, TranslatedProperty.Caption];
// Only one translation per entity per culture.
if (proposedTranslation != null)
{
Console.WriteLine($"Translation Exists for this Culture & Column combo");
enUsCulture.ObjectTranslations.Remove(proposedTranslation); //need to remove the existing translation to overwrite it
ObjectTranslation overwriteTranslation = new ObjectTranslation()
{
Object = dataColumn,
Property = TranslatedProperty.Caption,
Value = "Cool Stuff"
};
enUsCulture.ObjectTranslations.Add(overwriteTranslation);
}
else
{
ObjectTranslation newTranslation = new ObjectTranslation()
{
Object = dataColumn,
Property = TranslatedProperty.Caption,
Value = "Total Rad"
};
enUsCulture.ObjectTranslations.Add(newTranslation);
}
// List out the translations to see what they are now that we have run the script
foreach (ObjectTranslation objectTranslation in enUsCulture.ObjectTranslations)
{
Console.WriteLine($"Final Translated Object: {objectTranslation.Value}");
}
//------------------- List out end state --------------------------------//
// List out the columns in the product table one more time to make sure our column is added
foreach (Column column in table_product.Columns)
{
Console.WriteLine($"Columns: {column.Name}");
}
// List out the measures in the table one more time to make sure our measure is added
foreach (Measure productmeasures in table_measures.Measures)
{
Console.WriteLine($"Measures: {productmeasures.Name}");
}
// List out the hierarchies in the product table
foreach (Hierarchy hierarchy in table_product.Hierarchies)
{
Console.WriteLine($"Hierarchies: {hierarchy.Name}, Lineage Tag: {hierarchy.LineageTag}");
foreach (Level level_hierarchy in hierarchy.Levels)
{
Console.WriteLine($"Level {level_hierarchy.Ordinal}: {level_hierarchy.Name}, Lineage Tag: {level_hierarchy.LineageTag} from {level_hierarchy.Column.Name}");
}
}
//-------------- Refresh our version of the data model then push/save changes back to the model in the PBI service -------------//
table_product.RequestRefresh(RefreshType.Full);
table_measures.RequestRefresh(RefreshType.Full);
model.RequestRefresh(RefreshType.Full);
model.SaveChanges();
//make sure this is the last line! Note, this line will not work unless your dataset has proper data source credentials connected in the service
Console.WriteLine($"Script Complete!");
}
}
}