Tag: C#

Power BI Cruise Recap – Deep Data Dives

On By Kristyna FerrisIn C#, TOM, and Power BI, General, Power BI, Power Query, Reporting, Speaking1 Comment

Thank you so much to those of you who were able to attend this three-hour, C# packed session! The Power BI Cruise was an incredible conference, and I cannot recommend it enough if you’re looking for an opportunity to dig deep on Power BI topics with other passionate individuals. From the first ever Power BI bingo to a three hour session on TMDL from Mathias himself, this was a trip that will stick with me for a while. In this post, I’ll include notes from my session and the sessions I attended plus links to the GitHub. Let me know if anything really piques your interest and you’d like to see more detailed blog posts about it!

  1. My Session – Power BI, C#, and TMDL!
  2. TMDL & Source Control – Mathias Thierbach
  3. Hacking the Visuals – Stepan Resl
  4. Ask Me Anything – Jeroen (Jay) ter Heerdt

Huge shout out to the other speakers and of course the incredible organizers who stuck with this amazing vision through the pandemic and made it happen. Thank you all so much for making this event full of passion and unparalleled learning. I don’t know how you all made this happen without sponsors, but it was truly an incredible event. So thank you again Asgeir, Erik, Johan, and Just!

My Session – Power BI, C#, and TMDL!

Now you may have noticed I give this session – Power BI Meets Programmability – quite often. Bringing programmability to the world of Power BI is something I greatly enjoy, but this session was truly unique. Having three hours to take a deep dive into what C# can do for Power BI development was the opportunity of a lifetime. With two more hours than usual, we explored creating calculation groups in this session for the first time as well as creating automatically generated set of measures based on data types. Not only that, but the attendees were good sports and stuck around for an extra half hour to create calculation groups in C# using TMDL! I plan on doing a very detailed blog on this in the future, but the GitHub folder for this conference (link below) contains TMDL code live coded by the creator himself – Mathias Thierbach! The script contains a section that will create a TMDL version of your data model, allow you to interact with TMDL files within your solution, and publish those changes back to the data model. It blew my mind, and I hope it blows yours as well!

Additional Resources

The final code can be found here: https://github.com/Anytsirk12/DataOnWheels/tree/main/Power%20BI%20Cruise.

TMDL & Source Control – Mathias Thierbach

Speaking of TMDL, this conference hosted the incredible Mathias Thierbach for a session on TMDL & Source Control. Below are my notes from that session, but if you ever have a chance to hear him speak on the subject I highly recommend it. The notes below are in bullet point format for now, as I dig further into this language I look forward to writing more detailed blog posts about this incredible language and what we can do with it.

  • Key goals of TMDL: readable, editable, and allows collaboration
  • There will be a VSCode extention for TMDL coming soon (we were given a special preview in the session) that will have syntax highlighting for both DAX and M as well as TMDL
  • Shortcut to get to the TOM docs: goto.pbi.tools/tom-docs or pbi.onl/tools
  • TMDL is completely case insensitive!
    • Default export of .tmd file is camel case
  • Boolean properties: you only have to say the Boolean property to enforce the default value
    • To explicitly set it, syntax is:
      property: true/false
  • No matter how many times you serialize TMDL, the order of the objects will always be the same
    • Tables: partitions, calc groups, columns, hierarchies…
    • You can reorder objects
    • You can reorder objects to your specifications (aka no longer alphabetical)
  • Meta data IsParameterQuery = true makes it a parameter
  • Ordinal is not a table property in TOM, but you can create one in TMDL! It won’t show up in this order in Desktop…yet. But it will allow consistent order. It’s a weak ordinal, in cases of conflict it won’t cause any errors. It should default to alphabetically on conflict.
  • Indentation for expressions is not white space sensitive (DAX and M)
    • It finds a shared whitespace to the left to create indentation in the final model
  • Indentation only matters for nested objects
  • To fold levels in VS Code use Ctrl + k + [level_number]
  • To unfold levels in VS Code use Ctrl + k + j to unfold all levels
  • Unicode is supported
  • Shared expressions = parameters
  • Preview 1 (what we have), perspectives can’t be read back but can create them
    • Will be fixed in Preview 2
  • 4 previews total, goal is for GA at end of the year
  • tableName.columnName etc.
    • If there are spaces, use single quotes as a delimiter
  • Description is added by using /// text on top of the object declaration. Can be multi-line and line break is maintained.
  • No comment support but you can comment within M and DAX
    • Triple ‘’’[formula] ‘’’ will be a safe way to mark M/DAX expression sections. Likely coming in preview 3 or 4 later this year
  • Language spec will be published
  • Default properties are available to find in Rui’s documentation
    • Default property is what is after the = for the object
    • This can be found on the main TMDL overview page

How to create a TMDL folder from Tabular Editor (need 2.18.1 version or later)

Open the folder you’ve created within VS Code.

Hacking the Visuals – Stepan Resl

Hacking = use various available means to get better insights from data.

This session absolutely blew my mind. Stepan does an incredible job showing the art of the possible by hacking into expression properties within tabular editor as well as calculation group formatting. We had an awesome time connecting at this conference and I hope to see him again at future conferences! In the meantime, below is a link to his GitHub as well as a blog post that covers a lot of the same material as his session. Be prepared to have your mind blown on this one.

Stepan’s GitHub for this session:

https://github.com/tirnovar/public-speaking/tree/main/Power%20BI/Power%20BI%20Cruise%20-%202023

Additional blog post by Stepan on this subject: https://datameerkat.com/conditional-formatting-calculation-groups

  • For bar charts – to easily highlight what is over target, change the color within the bar at the target with grey below the target
  • Visual Vocabulary = a great reference for chart types
    • Ft.com/vocabulary
  • / used in a format string will escape the wildcard characters
  • Keep in mind audience, green is not positive in Japan
  • topProductByLocation = TOPN(1, ALLSELECTED(Products[ProductName]),[# Total Quantity])
  • # sold quantity by Top Product = VAR _product = [topProductByLocation] RETURN CALCULATE([# Total Quantity], Products[Product name] = _product
    • This allows us to use a measure as a filter by precalculating it in a variable
  • Best whitespace option is to use UNICHAR 8203, it’s a very very small dot
  • Color picker: Just Color Picker
  • Use smart text in subtitles in charts to tell people when we hit a target or how close we are
  • You can use format to change numbers to text with a thousands delimiter! See the subtitle measure
  • You can use ; within format to do an if statement! BUT don’t forget to escape any special characters using \
    • Example: “Sales target was set to ” & FORMAT(_target,”#,,,,,,,,,,#”)
      & ” and we have sold ” & FORMAT(_sold,”#,,,,,,,,,,#”)
      & ” which means we ” & FORMAT(_sold-_target,”\have;\haven’t”) & ” fulfilled our target.”
    • In this case “h” is a special character that is trying to get an hour value and will show 0 if you don’t escape it
  • If you create conditional formatting in bar charts, the colors will stay if you switch to a line chart even though conditional colors are not available in the UI
    • Same thing happens if you have an x constant line in a line chart and switch to bar chart
  • You can customize spacing below titles and subtitles.

I’ve added the final PBIX from this session to the GitHub, keep in mind a lot of the features that make it possible are only visible through tabular editor: https://github.com/Anytsirk12/DataOnWheels/blob/main/Power%20BI%20Cruise/Hacking%20the%20Visuals.pbix

Ask Me Anything – Jeroen (Jay) ter Heerdt

Meeting Jay and chatting during various free time on the ship was definitely a highlight of the trip. He’s incredibly knowledgeable on Power BI (especially the elusive DAX language), and has a deep understanding of the Microsoft strategy on new feature creation. It was a pleasure getting to laugh and brainstorm together, and I can’t wait to hang out again at future conferences. Below are some questions that were asked during the AMA session as well as answers provided and links I found while Jay was talking.

  • Why can’t we use RLS and USERELATIONSHIP() together?
    • RLS blocks USERELATIONSHIP() since USERELATIONSHIP() can (but doesn’t always) go around the RLS relationship and creates a security risk
  • Different people own different visuals which is why the options and customizations vary so widely
    • Miguel Myers did a session on the future of visuals in Power BI. Huge plans with him on board to own visuals and align everything
    • There is a linked in group called PBI Core Vision, put comments there on what can be improved in the future. Twitter and LinkedIn
  • When we change the hierarchy name or order via XMLA, it now breaks the visual. Can we get this to update in the visual in the service with adjusted level orders
    • SEND TO JAY
    • Also ask him about Top N + Others
  • Tableau is the primary competition for PBI, Looker is up and coming as Google, and third is Qlik because it’s still getting new clients in Europe
    • These are taken into consideration when making a case for new items
    • Cannot blatantly copy items
    • Tableau has specific people in their agreement that cannot even look at Tableau (can only look as a user, not at the debugger nor dll file)
  • In the future, hoping to have functions that could be shared within a community for DAX
  • Could we get Rulers or aligned grid lines in PBI Desktop?
    • Rosie or Ree-ann would own this
    • Can we customize grids?
    • Not part of visual team, it’s placed under on-boarding
  • Advancements to API?
    • Hardening of PBIX will help with getting better Scanner APIs to get deeper API’s (visual level and page level details on what’s available within the tenant)?
  • DataZen got killed and unsure why
  • Jay would investigate how to get rid of filter direction in DAX
  • Why can I not put a measure name in smart narratives?
    • You can do this by creating a shape and using fx to put in the text you want

SQL Saturday Atlanta 2023 Recap

On By Kristyna FerrisIn General, SQL Saturday

This past weekend, I had the pleasure of attending and speaking at SQL Saturday Atlanta! If you’re in the area, I highly recommend connecting with the local user group and getting to know fellow data nerds near you. Thank you to everyone who was able to make it out, it was great to see such a large in person SQL Saturday event post-pandemic! Big shout out to the volunteers, organizers, and sponsors who made it all happen. Thank you all for your time, hard work, and commitment that will lead to rebuilding the data community. This was the first conference I felt confident enough in my presentation to attend a session in every timeslot! Below are some takeaways I took from the sessions I attended, but there were a lot of other incredible sessions and I recommend checking out the schedule for any that interest you and reaching out to the speaker.

Attended Session Takeaways

  • Practical Use Cases for Composite Models (Kevin Arnold)
    • Never thought of using composite models for pulling in an enterprise model with a thin slice specific to the use case of the report. Genius method for maintaining a focused enterprise model while meeting the needs of your end users.
    • Perspectives can be used with personalized visuals instead of hiding columns, so end users are not overwhelmed by column and measure options.
    • Field parameters can also be used/created by end users for a cultivated experience that meets their business needs without impacting the larger audience of your enterprise model. If you haven’t heard of them (I hadn’t), highly recommend checking out this link.
  • Planning Steps for a Power BI Report (Belinda Allen)
    • Always ask stakeholders what their experience is with Power BI, it will help put all their questions and assumptions in context.
    • Ask your stakeholder for the scope of success. If they can’t define what success is for the project, you have the wrong person or the wrong client.
    • Show nothing in a needs gathering session. Listen and take notes. Similar to watching a movie before reading a book, it will severely limit the imagination necessary for an impactful report.
    • Ask who is maintaining the data currently and who will continue to do so.
    • Check out PowerBI.tips Podcast.
    • Ask if they want data access or data analytics. This will let you know if a visual report is a waste of resources for them and/or if paginated report or something similar better fits their needs.
    • Check out Chris Wagner’s blog, he has a great slide deck for a wireframing session with success owner after the needs gathering session.
    • Host office hours or something similar to foster on-going user growth
    • After project, always ask if we achieved defined success benchmarks. Try to give them a concrete ROI (ie x hours saved = x $ saved based on average salary).
    • Linktr.ee/msbelindaallen
  • Introduction to Azure Synapse Studio Development Tools (Russel Loski)
    • Synapse workspace can allow you to create T-SQL and python notebooks off items in Azure Data Lake Storage like csv and parquet files.
    • Notebooks allow markdown to be side-by-side with code
    • ctrl + space will bring up snippets to use within a notebook
    • No indexing since it’s serverless, prepare for some wait time.
    • We can promote column headers using a variable HEADER_ROW = TRUE
  • DataOps 101 – A Better Way to Develop and Deliver Data Analytics (John Kerski)
    • Check out the The DataOps Manifesto – Read The 18 DataOps Principles
    • Principles are repeatable and adaptable to new technologies
    • Make everything reproducible
    • Versioning and automated testing are keys to building sustainable solutions
    • Check out the DataOps Cookbook and pbi-tools
  • Power BI Performance in 6 demos (Patrick LeBlanc & Adam Saxton from Guy in a Cube)
    • To reduce the “other” line item in performance analyzer, limit the number of visual objects on a page.
    • Optimize DAX when the line item is over 120 milliseconds.
    • SLA for page loads is 5 seconds.
    • Using drop downs in your slicer will delay DAX from running for that visual object. That deferred execution aids in speeding up the initial load.
    • Tooltips run DAX behind the scenes on the initial load for visuals (you can see this by copying out the DAX query into the DAX Studio). To delay this execution until it’s needed, use a tooltip page.
    • If the storage engine in DAX Studio is over 20 milliseconds, there’s opportunity to optimize.
    • Variables limit the number of times a sub-measure will be run and speed up DAX queries behind visuals.
    • Keep in mind while performance tuning, Power BI desktop has 3 caches – visual, report, and analysis services engine. You can clear all caches within the desktop tool except visual. To clear that cache, you need to close and reopen the PBIX file.

My Session

I cannot express enough how grateful I am for everyone who was able to make it to my session! To have so many established professionals in the field approach me afterwards telling me how well it went was a dream come true. If you’re interested in reviewing the slides and code, please check out my GitHub folder for all you will need to recreate the demo we went through. Miss it? No worries! I’ll be presenting this topic at SQLBits and the Power BI Cruise, so come join me! I’m also open to presenting at various user groups, feel free to reach out to me at kristyna@dataonwheels.com.

Again, thank you so much to everyone who made this weekend possible and please connect with me on LinkedIN and Twitter! I’d love to stay connected!

2022 PASS Data Community Summit Recap: Power BI Meets Programmability – XMLA, TOM, C#

On By Kristyna FerrisIn C#, TOM, and Power BI, General, PASS Summit, Power BI, Speaking

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

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!");


        }
    }
}

Power BI Adding Translations to Rename Columns – XMLA, TOM, C#

On By Kristyna FerrisIn Analysis Services, C#, TOM, and Power BI, General, Power BI, Power Platform, Reporting, Visual Studio1 Comment

If you are new to using C# and the Tabular Object Model (TOM), please check out the previous blog post (https://dataonwheels.wordpress.com/2021/10/15/power-bi-meets-programmability-tom-xmla-and-c/) for both an introduction to the topic and detailed instructions on getting the demo stood up.

For the TOM and XMLA experts, imagine this. Your customer wants to dynamically rename columns without using the Power BI Desktop and would prefer all existing report visuals not get broken by the new name. Impossible? Not with TOM, XMLA, and translations within Power BI.

If you’ve ever tried to change a column name in a Power BI source, you’ve likely run into this error on any visuals that contained the renamed column. And when you hit that “See Details”, it will tell you the column that you simply renamed is no longer available for your visual.

So how do we get around that? Translations. Translations are typically used to translate report entities to other languages that will change depending on what language the end user has set on their browser. However, we can hijack this functionality to rename columns without having to impact the data model. It is a bit confusing on why this works, but imagine this: you build a Lego pyramid, but learn that one of the blocks needs to be changed from blue to green. Couple of options, you can take apart the entire pyramid (this would be akin to reopening the PBIX in Power BI Desktop and changing all of your visuals) OR you can take a green marker and color that blue brick green (adding a translation from blue to green).

If you don’t need to put this code into C#, the Tabular Editor is an excellent tool for adding translations to your data model (https://tabulareditor.com/creating-multilingual-power-bi-datasets/). However if you would like to programmatically update column names using C#, feel free to use the script below in your solution.

At a high level, here’s the hierarchy of entities used:
Workspace – Dataset – Data Model – Cultures – Object Translations
Workspace – Dataset – Data Model – Table – Column – Translated Properties

Note: There can only be one translated property per culture.

To add translations, we first need to set which culture this translation belongs in. For this example, we will use “en-US” because that is what default browser we want these names applied to. The code snippet below will list out all the cultures (aka website language codes) that are configured in this data model and list out all the translated objects (data columns in this case) that already exist.

After setting the culture/language, narrow down the column that this translation will be applied to and create a variable for the translation object. The translation object consists of two parts, the metadata object (column in this example) and the property of that metadata that we want to translate (caption in this example which is essentially display name).

Once we have these elements, we can check to see if this column already has a translation for this culture. If it does, this script will remove the old translation to allow for overwriting. If it does not, it will add the new translation to the culture within the data model.

And that’s it!

Here’s what it looks like in the service. Don’t forget to refresh your report page if you have it open for the new name to appear. There’s no need to refresh the dataset.

Full C# code:

using System;
using Microsoft.AnalysisServices.Tabular;



namespace PowerBI_TOM_Testing
{
    class Program
    {
        static void Main()
        {

            // create the connect string - powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE_NAME
            string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/YOURWORKSPACE";
            string connectString = $"DataSource={workspaceConnection};";

            // connect to the Power BI workspace referenced in connect string
            Server server = new Server();
            server.Connect(connectString);
            // 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["bb44a298-f82c-4ec3-a510-e9c1a9a28af2"].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"];

            
            
            // List out the columns in the product table
            foreach (Column column in table_product.Columns)
            {
                Console.WriteLine($"Columns: {column.Name}");
             }


            //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("Description"); //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 = "Blue"
                };
                enUsCulture.ObjectTranslations.Add(overwriteTranslation);
            }
            else
            {
                ObjectTranslation newTranslation = new ObjectTranslation()
                {
                    Object = dataColumn,
                    Property = TranslatedProperty.Caption,
                    Value = "Blue"
                };
                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}");
                }
            
model.SaveChanges(); //make sure this is the last line! 
       


        }
    }
}

Additional Resources:

– https://www.kasperonbi.com/setting-up-translations-for-power-bi-premium/
– https://tabulareditor.com/creating-multilingual-power-bi-datasets/
– https://www.sqlbi.com/tools/ssas-tabular-translator/
– https://docs.microsoft.com/en-us/analysis-services/tabular-models/translations-in-tabular-models-analysis-services?view=asallproducts-allversions
– https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.culture?view=analysisservices-dotnet
– https://docs.microsoft.com/en-us/dotnet/api/microsoft.analysisservices.tabular.culture.objecttranslations?view=analysisservices-dotnet#Microsoft_AnalysisServices_Tabular_Culture_ObjectTranslations