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

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

Power BI Meets Programmability – TOM, XMLA, and C#

For anyone who read the title of this and immediately thought, “Oh no, I can’t do C#! Since when do I need to be in app dev to do Power BI?!” Never fear, I had the same panic when writing it haha. I recently did another blog post that uses the TMSL to accomplish a similar goal as this blog (TMSL Blog), but there are some added benefits to using TOM and C# instead of TMSL and SQL.

Recently, a client suggested that they would like to update their Power BI model schema through a pipeline triggered by their application. They allow end users to create custom UDFs (user defined fields) on the fly and also delete them. Normally, Power BI developers would have to open the PBIX file in the Power BI Desktop application and refresh the data model there to pull in the new columns. However, we have another option using the XMLA endpoint, TOM, and C#.

To start, let’s define a couple key terms.

TOM = Tabular Object Model. The TOM can be used inside numerous scripting languages to manipulate the data model. In this case, we are going to use C# so that the code can be called by a larger variety of applications.

TMSL = Tabular Model Scripting Language. TMSL can be used inside SSMS, and is very easy to manipulate, but does not lend itself well to C#-based applications and automation.

Limitations: You cannot export the PBIX file from the service once the XMLA updates have been made. For adding columns to the model, that’s not a big problem since those would be added in once you opened the desktop tool again. The problem comes if you create or edit visuals in the online service that you don’t want to overwrite in future iterations.

Tools needed:

Notes:

  • Ensure you have a data source you can add columns to if you are following the example below
  • Save a copy of your PBIX report so you can make visual edits in the future. Once you edit a data model using the XLMA endpoint, you can no longer export it as a PBIX file from the online PBI service

Process:

  1. First, create and publish a Power BI Report to the online service. No need to add any visuals, but make sure you have at least one table you have access to edit the columns in to follow along with this demo. You will need a Power BI Pro license and access to publish to a Premium workspace.
  2. Next, add a column to your data source that does not currently exist in your Power BI report. For example, make a column in Excel or SQL called “New Column Test” with the letter “a” filled in for every row. I will make one called “Description” in my example.
  3. Unfortunately, Power BI does not refresh the schema in the service so it will not pull in the new column unless you open up the report in Power BI Desktop and refresh there then republish. One way around this is using the XMLA endpoint from the premium workspace and add the column into the JSON code using the TOM (Tabular Object Model) in C#. Before we walk through each of those steps, keep in mind that doing this will prevent that Power BI dataset from being downloaded as a PBIX file ever again. So, it’s best to keep a local copy of that PBIX file for any visual updates that need to be made, or simply use this dataset as a certified dataset to be used in multiple reports.
  4. Open the premium workspace, select settings, and go to the “Premium” tab to copy the workspace connection.

5. Here comes the scary part, but hey it’s October, the month for tackling our fears! So here we go. Time to make a basic C# application. Open up a file in Visual Studio (ensure you have .Net 5.0 and .Net Core installed as well) and navigate to File –> New Project, choose the Console Application template (should be top one), pick any name you’d like (aka PowerBI_TOM_Testing), select .NET 5.0 for your framework, then hit create. Phewf, you have your app, yay! Under the view tab, go ahead and select Solution Explorer
and you should see it pop open on the right side of your screen.

6. Double-click on “Program.cs” to open your project. Now, go under the Tools tab to NuGet Package Manager then to Manage NuGet Packages for Solution. This is where we get to inform our application of the packages of code we want to use.

7. Go to Browse and search Microsoft.AnalysisServices.NetCore.retail.amd64 and two options should pop up. Go ahead and hit “install” for each of them. Once you’re done, double-check the install by hopping over to the Installed app and make sure they are both there (be sure to clear your search first).

8. Go ahead and close this window and go back to the Program.cs tab and let’s try out using a script using our XMLA endpoint! Swap out PowerBI_TOM_Testing with whatever you named your project in step 5. And Swap out the powerbi://api.powerbi.com/v1.0/myorg/POC with the link you copied in step 4. You should see zero errors show up on the bottom. If not, double check that you have all of the brackets and semi-colons.

using System;
using Microsoft.AnalysisServices.Tabular;

namespace PowerBI_TOM_Testing
{
    class Program
    {
        static void Main(string[] args)
        {

            // create the connect string
            string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/POC";
            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(database.Name);
            }
        }
    }
}

9. To run it and get back the datasets in your workspace, simply hit the green arrow at the top. It will pop open with a sign in option, so sign into your Power BI account and watch it go! To see your output, wait for the debugging window to finish running and you should see a list of all the datasets in your workspace!

10. Okay time to add a column into the data model!

For this section, I am going to add some conditional logic so that the script knows what to do if the column already exists. Now fair warning, there’s also a bit of script that adds a measure for you as well. You can delete that section of code, or use it as a template for adding measures into your data model. For more example code, please check out the Power BI Development Camp (https://github.com/PowerBiDevCamp/Tabular-Object-Model-Tutorial/blob/main/Demos/Learning-TOM/Learning-TOM/DatasetManager.cs).

Notes are in green.

Important note, you have to have the SaveChanges() command AFTER the refresh request. If you put the refresh after the save changes, you will have a column with zero data in it.

Here’s the full script, including the script for adding a measure. Please feel free to utilize the additional resources for more examples and assistance. Paste pieces of the code below into your visual studio and enjoy watching your data magically appear into your data model.

using System;
using Microsoft.AnalysisServices.Tabular;


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

            // create the connect string
            string workspaceConnection = "powerbi://api.powerbi.com/v1.0/myorg/POC";
            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}");
            }
            // enumerate through tables in one database (use the database ID from previous step)
            Model model = server.Databases["bb44a290-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}");
            }
           
            // 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}");
             }

            // 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");
                table_product.Columns.Remove("Testing"); //if the column exists, this will remove it
                Console.WriteLine($"Column Deleted");
                Column column_testing = new DataColumn() //this will add back the deleted column
                {
                    Name = "Testing",
                    DataType = DataType.String,
                    SourceColumn = "Description"
                };
                table_product.Columns.Add(column_testing);
                Console.WriteLine($"Column Created!");
            }
            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 = "Description" //this must match the name of the column your source 
                };
                table_product.Columns.Add(column_testing);
                Console.WriteLine($"Column Created!");
            }




            // 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}");
            }



            // Add a measure if it doesn't already exist in a specified table called product
            if (table_product.Measures.ContainsName("VS Test Measure"))
            {
                Measure measure = table_product.Measures["VS Test Measure"];
                measure.Expression = "\"Hello Again World\""; //you can update an existing measure using this script
                Console.WriteLine($"Measure Exists");
            }
            else
            {
                Measure measure = new Measure() 
                {
                    Name = "VS Test Measure",
                    Expression = "\"Hello World\"" //you can also use DAX here
                };
                table_product.Measures.Add(measure);
                Console.WriteLine($"Measure Added");
            }


 
            table_product.RequestRefresh(RefreshType.Full);
            model.RequestRefresh(RefreshType.Full);
            model.SaveChanges();



        }
    }
}


Additional Resources:

Power BI: Adding Columns to a Published Data Model using the XMLA Endpoint & TMSL

Goal of this demo: Update a Power BI model schema by adding a column to the data model without opening a PBIX file and ensure the scheduled refresh still works.

Why would this be useful? Updating the schema in the desktop tool requires an entire refresh of the data model which can take a while if your model is large. Also, app developers could systematically add new elements to existing data models using a formulaic XMLA script through SSMS, saving your report designers time when new fields need to be added.

Limitations: You cannot export the PBIX file from the service once the XMLA updates have been made. For adding columns to the model, that’s not a big problem since those would be added in once you opened the desktop tool again. The problem comes if you create or edit visuals in the online service that you don’t want to overwrite in future iterations.

Tools needed:

  • A data source you can edit (Excel will work, this demo uses a SQL view)
  • Power BI Desktop
  • Power BI Premium Workspace (Premium Per User should also work)
  • Power BI Pro License
  • SSMS (SQL Server Management Studio)

Notes:

  • Ensure you have a data source you can add columns to if you are following the example below
  • Save a copy of your PBIX report so you can make visual edits in the future. Once you edit a data model using the XLMA endpoint, you can no longer export it as a PBIX file from the online PBI service

Process:

  1. First, create and publish a Power BI Report to the online service. No need to add any visuals, but make sure you have at least one table you have access to edit the columns in to follow along with this demo. You will need a Power BI Pro license and access to publish to a Premium workspace.
  2. Next, add a column to your data source that does not currently exist in your Power BI report. For example, make a column in Excel or SQL called “Testing” with the number 1 filled in for every row.
  3. Unfortunately, Power BI does not refresh the schema in the service so it will not pull in the new column unless you open up the report in Power BI Desktop and refresh there then republish. One way around this is using the XMLA endpoint from the premium workspace and add the column into the JSON code using the TMSL scripting in SSMS. Before we walk through each of those steps, keep in mind that doing this will prevent that Power BI dataset from being downloaded as a PBIX file ever again. So, it’s best to keep a local copy of that PBIX file for any visual updates that need to be made, or simply use this dataset as a certified dataset to be used in multiple reports.
  4. Open the premium workspace, select settings, and go to the “Premium” tab to copy the workspace connection.

5. Open SSMS and select “Analysis Services” for your server type. In the server name, paste in the workspace connection string that you copied in step 4. Authentication will be Azure Active Directory with MFA. The user name and password will be the same email and password you use to access Power BI.

6. Under databases, you’ll see all the datasets present in that workspace. Expand the database with the name of your dataset and expand the tables. Note, there will be tables present that you don’t recognize. For every date table in your data model, Power BI builds a table behind the scenes that will now be exposed. Navigate to the table you want to add the column to and right click it.

7. Navigate through by hovering over “Script Table as” then “CREATE OR REPLACE To” then select “New Query Editor Window”. This will open a script to adjust the data model of that table in TMSL (tabular model scripting language).

8. Now here’s the tricky part. It’s best if you already have a column in your data model that is the same data type as the one you want to add so you can just copy/paste the JSON object from the existing script. My example is for an integer column, but you can do this for any data type. Scroll down in the code until you start to see your column names. In my dataset, I have a column named “Custom1” that is the same type as my “Testing” column. All you have to do is copy and paste the code of your sample column then swap out any place where it says “Custom1” (aka whatever your sample column name is) with the name of your new column.

Sample Column
New column

9. Delete the line of code that says “lineageTag” from your new section of code. The lineage tag only matters if you are editing an existing column, Power BI will generate a new lineage tag once this column is officially added to the schema.

10. Hit “Execute” or F5 to push the schema change to the data model in the service. The message at the bottom will run through a few items, but the final response should look like the one in the image below.

11. The final step is to refresh your data model in the Power BI service on demand or by using the script below. To run this script, you’ll need to select your main table then select “New Query”. You should see your measures and metadata populate as the analysis service cube is exposed. Once you see that, you can copy/paste the code below to refresh the table (this is also how you can refresh one table at a time if needed, hint hint). Execute and your new column will now have data in it, yay!

{
“refresh”: {
“type”: “automatic”,
"objects": [
{
"database": "YOUR DATASET NAME HERE",
"table": "YOUR TABLE NAME HERE"
}
]
}
}

12. Test it out! Go into your report in the service, hit the edit button and update your report with the your new column! But remember, you no longer have the option to download the PBIX file. So any changes that need to be made to the data model (i.e. new measures) need to be done through the XMLA end point, and any visual changes must be done in the online service.

Additional Resources:

X-XMLA: vii. Partition Management with XMLA

image_thumb_thumb_thumbIn the 7th article of this series, I will discuss how to manage your partitions with XMLA.   We will be focused on the partition structures as opposed to partition processing which was covered in the previous post.

Creating or altering a partition

I have already discussed object creation with XMLA in part 3 of this series.  Remember that when altering the partition you need to have all of the partition definition you wish to keep as well as what you are changing in the XMLA.  If you have a subset of the definition, SSAS will assume you want to remove the missing parts.

Adding a partition to an existing measure group

One area where a partition is different from other objects is that you can add a partition to an existing measure group.  This is often done to improve processing performance or to match the underlying data architecture (especially if table partitioning is already in place for the source tables).  Often the partitions are based on date and correspond to the date the data is loaded.  For instance, you may have separate partitions for each year.  So at the beginning of the next year you need to add a partition.  In this case, you create a new partition with a specific data source definition for that partition.

If the measure group only has one partition that is based on a single table, you may need to change the source definition so you can properly add a new partition.  There are two common ways to manage the data sources to support partitions.  The first is to create views that map to the partition structure in the cube and then the data source for each partition can point directly to a particular table or view.  The second way is to specify a query definition that usually points at one table and filters the data based on one or more keys.

In either case, the XMLA is constructed as a Create command with the ParentObject specifying the measure group that the partition will be added to.  The Object definition contains the ID, Name, Source and other properties that make up the partition.  The example below shows the definition for adding a partition with that covers the month of January using a filter in the QueryDefinition for the Source.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>
        <CubeID>Adventure Works DW</CubeID>
        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    </ParentObject>
    <ObjectDefinition>
        <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
            <ID>Internet_Sales_Jan2011</ID>
            <Name>Internet_Sales_Jan2011</Name>
            <Source xsi:type="QueryBinding">
                <DataSourceID>Adventure Works DW</DataSourceID>
                <QueryDefinition>SELECT * 
                        FROM dbo.FactInternetSales 
                        WHERE OrderDateKey BETWEEN 20110101 AND 20110131</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <AggregationStorage>MolapOnly</AggregationStorage>
                <Source xsi:type="ProactiveCachingInheritedBinding">
                    <NotificationTechnique>Server</NotificationTechnique>
                </Source>
            </ProactiveCaching>
            <EstimatedRows>60398</EstimatedRows>
            <AggregationDesignID>Internet Sales</AggregationDesignID>
        </Partition>
    </ObjectDefinition>
</Create>

Merging partitions

In some cases, you will find the need to merge partitions.  In our example above we added a partition for the month of January.  However, as the your partition count becomes larger there is benefit to merging partitions into historical or archive partitions.    Or you may even decide that merging will help with maintenance or processing.  In either case, the logic is fairly simple.  You have one or more source partitions being merged into target partition.  The XMLA for this is pretty straightforward.

There are a couple of nuances to be aware of including the fact that the structure and the aggregation designs need to be the same to support merging.  If you plan to merge, you should take this into account when building partitions and applying aggregation designs.  Furthermore, if you use partition slices you will likely need to modify the target’s slice after the merge is account for the new data.

Here is the code required to merge two source partitions into a target partition.  The result will be one partition.

<MergePartitions xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Sources>
    <Source>
      <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
      <PartitionID>Internet_Sales_Jan2011</PartitionID>
    </Source>
    <Source>
        <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
        <CubeID>Adventure Works DW</CubeID>
        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
        <PartitionID>Internet_Sales_Feb2011</PartitionID>
    </Source>
  </Sources>
  <Target>
    <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    <PartitionID>Internet_Sales</PartitionID>
  </Target>
</MergePartitions>

As you can see in the example above, all that you need to specify is the full address for each partition and identify which are the sources and which one is the target.

Setting the Slice Property

Before digging into the XMLA a couple of notes on the data slice in cubes.  First, slices are not required for MOLAP partitions, however, they can help with query performance.  While in current versions of SSAS (2005 and later), the system will determine the slice based on the data, it is not always accurate.  Keep in mind that a slice does not affect processing or the source data for a partition.  That is determined by the Source attribute and related definition.

Refer to the following blog posts Jesse Orosz and Mosha Pasumanky for more information as the details around the value of assigning scope which is beyond the scope of this post:

http://jesseorosz.wordpress.com/?s=slices

http://sqlblog.com/blogs/mosha/archive/2008/10/14/get-most-out-of-partition-slices.aspx

Assuming you need to set the slice or if you need to change it due to a merge here is the XMLA to alter a partition’s slice property.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    <PartitionID>Internet_Sales_Feb2011</PartitionID>
  </Object>
  <ObjectDefinition>
    <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <ID>Internet_Sales_Feb2011</ID>
      <Name>Internet_Sales_Feb2011</Name>
      <Source xsi:type="QueryBinding">
        <DataSourceID>Adventure Works DW</DataSourceID>
        <QueryDefinition>SELECT * 
                        FROM dbo.FactInternetSales 
                        WHERE OrderDateKey BETWEEN 20110102 AND 20110228</QueryDefinition>
      </Source>
      <StorageMode>Molap</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <Slice>{[Date].[Calendar].[Month].&amp;[2011]&amp;[2]}</Slice>
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <AggregationStorage>MolapOnly</AggregationStorage>
        <Source xsi:type="ProactiveCachingInheritedBinding">
          <NotificationTechnique>Server</NotificationTechnique>
        </Source>
      </ProactiveCaching>
      <EstimatedRows>60398</EstimatedRows>
      <AggregationDesignID>Internet Sales</AggregationDesignID>
    </Partition>
  </ObjectDefinition>
</Alter>

If we were to merge the January and February partitions we created previously, we could change the slice definition to include both months.

<Slice>{[Date].[Calendar].[Month].&amp;[2011]&amp;[1],[Date].[Calendar].[Month].&amp;[2011]&amp;[2]}</Slice>

Setting the Storage Location

Partitions also support the ability to change the storage location for each partition further capitalizing on hardware investments such as SSDs and low cost disks.  This is set in the XMLA using the StorageLocation element with a valid path.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    <PartitionID>Internet_Sales_Jan2011</PartitionID>
  </Object>
  <ObjectDefinition>
    <Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <ID>Internet_Sales_Jan2011</ID>
      <Name>Internet_Sales_Jan2011</Name>
      <Source xsi:type="QueryBinding">
        <DataSourceID>Adventure Works DW</DataSourceID>
        <QueryDefinition>SELECT * 
                        FROM dbo.FactInternetSales 
                        WHERE OrderDateKey BETWEEN 20110101 AND 20110131</QueryDefinition>
      </Source>
      <StorageMode>Molap</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <StorageLocation>C:\Data\SSASOptimized</StorageLocation>
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <AggregationStorage>MolapOnly</AggregationStorage>
        <Source xsi:type="ProactiveCachingInheritedBinding">
          <NotificationTechnique>Server</NotificationTechnique>
        </Source>
      </ProactiveCaching>
      <EstimatedRows>60398</EstimatedRows>
      <AggregationDesignID>Internet Sales</AggregationDesignID>
    </Partition>
  </ObjectDefinition>
</Alter>

As you can see, there are many partition management operations that can be done using XMLA.  As you work to develop a management strategy keep in mind that these operations can be scripted for automated execution when managing your Analysis Services solution.

X-XMLA: vi. Processing and Out-of-Line Bindings in XMLA

image_thumb_thumbIn this post, I will progressively go through processing a partition from full process, to incremental process, and finally to incremental process with out-of-line bindings.  This will provide you with additional samples to examine the results of partition processing with XMLA.

Note: these examples use the Adventure Works DW 2008R2 SE sample database available on CodePlex (http://msftdbprodsamples.codeplex.com/).

Processing with XMLA

Processing is a command that can be executed in a batch and in parallel with similar tasks.  This is particularly useful when processing multiple objects that are not dependent on each other, such as dimensions.  The basic structure is as follows:

<Batch>
<Parallel>
<Process>
<Object>
~object definition~
      <Type>
<ErrorConfiguration>
<WriteBackTableCreation>

Processing Type Options

This is a list of the most commonly used types when processing SSAS objects.

  • ProcessDefault:  This will determine the best option based on the condition of the object.  Be aware that if SSAS determines that a full process is required, it will do that. I would not recommend using this process type in most production situations.
  • ProcessFull: This option completely rebuilds the dimension, partition, cube or even database by removing existing data and reloading the data with aggregations and indexes.  In the case of cubes, measure groups and partitions, this is the equivalent of ProcessData + ProcessIndex.
  • ProcessClear: This option drops the data and related structures such as indexes and aggregations from the object.  Once this run, no data is available in the object.
  • ProcessData:  This option drops the data and reloads it, but does not rebuild the aggregations or indexes. It works with cubes, measure groups, partitions, and dimensions.
  • ProcessAdd: This option adds new data to a partition or dimension.  It is not available as an option when processing dimensions from SQL Server Management Studio, but is available in XMLA scripts.
  • ProcessUpdate:  This options will reread the data and updates the dimension attributes.  It is only available for dimensions and will drop flexible aggregations and indexes on related partitions.
  • ProcessIndex: This option rebuilds indexes and aggregations for partitions.  It requires that the partition has already been loaded with data (ProcessData).

Of these options, the most commonly used when working with XMLA in my experience have been, ProcessFull, ProcessData, ProcessAdd and ProcessIndex.  I will use each throughout the examples below and describe the cases where they have applied in my projects over the years.

Processing with XMLA

The process command in XMLA let’s you define the object to be processed, the type of processing to implement, and how to handle any writeback partitions.  (NOTE: the ErrorConfiguration options is optional.) Overall the structure is fairly simple.  Here is a simple example of XMLA will fully process the Fact Internet Sales measure group.

   1: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   2:   <Parallel>

   3:     <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

   4:       <Object>

   5:         <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

   6:         <CubeID>Adventure Works DW</CubeID>

   7:         <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

   8:       </Object>

   9:       <Type>ProcessFull</Type>

  10:       <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  11:     </Process>

  12:   </Parallel>

  13: </Batch>

The syntax is identical for processing dimensions.  The <Object> element identifies the object to be processed starting with the database.  Next the <Type> element specifies the processing type using the options from the list above.  Finally, the <WriteBackTableCreation> element instructs Analysis Services on how to handle a write back partition that has not been fully processed.  The default is UseExisting which will use existing table or create a new table if needed.  This is only relevant if your cube supports writeback.

Processing Affected Objects

This is a setting that is part of the Process command element.  By default it is set to True as in the example above.  This means that any objects that are affected will be processed as well.  The best example is if you process a dimension and it ends up being fully processed, this will cause all related measure groups to be fully processed as well, keeping the data in a consistent usable state.  However, if you are tightly managing your processing jobs, you may need to set this to False to reduce unexpected and blocking processing jobs.

Using Out-of-Line Binding with Incremental Processing

A great feature of XMLA is the ability to dynamically bind data and even connections when processing SSAS objects.  This is called “Out-of-Line” binding (http://msdn.microsoft.com/en-us/library/ms128523).   One of the core concepts behind out-of-line bindings is that you can specify the connection and the data source query or table at run time.  This is particularly useful when you want to do incremental processing.

This first example illustrates how to use a query to change the data being added to the partition.

   1: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   2:   <Parallel>

   3:     <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

   4:       <Object>

   5:         <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

   6:         <CubeID>Adventure Works DW</CubeID>

   7:         <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

   8:         <PartitionID>Internet_Sales</PartitionID>

   9:       </Object>

  10:       <Type>ProcessAdd</Type>

  11:       <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  12:     </Process>

  13:   </Parallel>

  14:   <Bindings>

  15:     <Binding>

  16:       <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

  17:       <CubeID>Adventure Works DW</CubeID>

  18:       <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

  19:       <PartitionID>Internet_Sales</PartitionID>

  20:       <Source xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xsi:type="QueryBinding">

  21:         <DataSourceID>Adventure Works DW</DataSourceID>

  22:         <QueryDefinition>Select 1 from SomeTable</QueryDefinition>

  23:       </Source>

  24:     </Binding>

  25:   </Bindings>

  26: </Batch>

The second example shows the syntax for changing a table or view in the bindings.

   1: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   2:   <Parallel>

   3:     <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

   4:       <Object>

   5:         <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

   6:         <CubeID>Adventure Works DW</CubeID>

   7:         <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

   8:         <PartitionID>Internet_Sales</PartitionID>

   9:       </Object>

  10:       <Type>ProcessAdd</Type>

  11:       <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  12:     </Process>

  13:   </Parallel>

  14:   <Bindings>

  15:     <Binding>

  16:       <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

  17:       <CubeID>Adventure Works DW</CubeID>

  18:       <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

  19:       <PartitionID>Internet_Sales</PartitionID>

  20:       <Source xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xsi:type="TableBinding">

  21:         <DataSourceID>Adventure Works DW</DataSourceID>

  22:         <DbTableName>Fact_InternetSales</DbTableName>

  23:       </Source>

  24:     </Binding>

  25:   </Bindings>

  26: </Batch>

In the end, by using the Bindings definitions you can choose what data is getting processed into the SSAS object.  Here are some ways that I have used this technique to support my projects.

  • Changed the datasource used with the object.  I have used this to point to a different data source to test whether it works with the cube without overwriting the existing datassource in the cube definition.
  • Changed the table to a view for processing.  Because I use XMLA when scheduling most of SSAS process jobs, I can dynamically set the table or view as needed.
                        • Changed the query definition to limit the data to new rows only. I have used this the most.  I have changed the query definition to reflect the latest data for both dimensions and partitions.  More importantly, I was able to leverage this in environments where I have not had control over the data source itself, specifically Oracle data warehouses.

Out-of-line bindings are very powerful when controlling your SSAS process options and allow you to maximize the efficiency of your jobs.