Creating a tabular partition in code
Following up on my previous post on managing tabular models, here is a small AMO example for adding a partition in code. This will be the first in a series of posts on tabular model automation.
This example will show how to add a partition to the Demographic Facts table of the Hans Rosling Project database. To create that database, simply create a new tabular project by importing from the Hans Rosling PowerPivot workbook, set the name of the database to hans_rosling_project, and deploy.
Notice that the Analysis Services database only has data from 1960 onward. However, the underlying relational data source has data points going back towards 1800. Let’s write a C# program that creates a partition with the data from the year 1800 and processes it fully (we will do more advanced processing techniques in another post).
First, create a C# project (you could use VB, I just don’t). You must add a reference to the Microsoft.AnalysisServices.dll assembly. Since this assembly is not in the GAC and the location of the assembly is on disk is not enumerated by the Add Reference dialog, you must browse to the location of the DLL (by default C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies) to add the reference.
Now you can go ahead and write the code. This example has zero error handling, but gets the point across:
//connect to server, substitute the server/instance name with yours as necessary Server s=new Server(); s.Connect(".\\TABULAR"); //navigate to the Demographic Facts measure group, we must add the partition to the measure group Database db = s.Databases.FindByName("hans_rosling_project"); DataSourceView dsv = db.DataSourceViews.FindByName("Sandbox"); Cube c = db.Cubes.FindByName("Model"); MeasureGroup mg = c.MeasureGroups.FindByName("Demographic Facts"); //define the new partition Partition p = mg.Partitions.Add("Partition 1800"); p.Source = new QueryBinding(dsv.DataSourceID, "SELECT [dbo].[DemographicFacts].* FROM [dbo].[DemographicFacts] WHERE ([Year] = 1800)"); p.StorageMode = StorageMode.InMemory; //update the server with the new partition, process it, and disconnect p.Update(UpdateOptions.ExpandFull); p.Process(ProcessType.ProcessFull); s.Disconnect();
The most interesting part of the code to me is the part where you navigate to the measure group to add the partition. There’s a few things to note about this code:
- There are DataSourceViews, Cubes, and MeasureGroups. These things did not go away with the tabular model, they’re just hidden out of sight in the code of the BIM file or in the script of the database.
- Just as for multidimensional models, partitions are added to measure groups.
- The FindByName method is used liberally throughout this code. Using the ID, while more reliable, is definitely not as user friendly. IDs are of the form prefix+GUID where the prefix is the name of the item when it is first created. The ID is rarely (if ever) displayed in the UI. The ID is never updated, even when the object is renamed. So, if you choose to use IDs in your code, your code will be quite unreadable.
- The DataSourceView is always called Sandbox.This is a hangover from PowerPivot when the DataSourceView was always hidden from view. Now you will be referring to this mysterious Sandbox object from code.
- The Cube name is the same as the name supplied in the tabular project properties, if you do not change the default cube name then use the value “Model” here.
- The Measure Group name is the same as the table name. The Measure Group name is automatically updated whenever the table is renamed, so you can safely use the values you see in the UI for the FindByName call.
The rest of the code is pretty straight forward. Once you have found the place to add the partition, you set its query and storage mode, then update the server and process it. I do have some suggestions for processing strategies in Processing tabular models 101, but more guidance will come on that topic in the future. Stay tuned.
From → BI Semantic Model