Skip to content

Doing a ProcessAdd in AMO

January 23, 2012

You can use ProcessAdd to add more data to an existing partition. There are a few ways to use ProcessAdd:

  • Add a new query binding, then process the query binding
  • Change the partition definition query to reflect the rows you want to add, then do a ProcessAdd on the partition
  • If your partition definition query is based on a view, update the definition of the view in the data source to reflect the rows that you want to add, then do a ProcessAdd on the partition

Unfortunately, unlike multidimensional, the tabular UI does not have a place to define a new query binding for a partition. If you want to use the query binding method, you need to do it using automation or script.

Adding the new query binding and doing the ProcessAdd in AMO is pretty simple. Recall my previous AMO example for creating a new partition. In that example, we created a new partition called “Partition 1800” which added the data for the year 1800. Now imagine we wanted to update this partition and add the data for the year 1801 using a query binding. Here is the code:

//connect to server, substitute the server/instance name with yours as necessary
Server s=new Server();
s.Connect(".\\TABULAR");

//navigate to the partition we want to process
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");
Partition p = mg.Partitions.FindByName("Partition 1800");

//do the ProcessAdd for the year 1801, defining the new query binding inline. then disconnect
p.Process(ProcessType.ProcessAdd, new QueryBinding(dsv.DataSourceID, "SELECT [dbo].[DemographicFacts].*   FROM [dbo].[DemographicFacts]  WHERE ([Year] = 1801)" ));
s.Disconnect();

Once you navigate to the right place, it is one line to do the ProcessAdd, including the definition of the new query binding. This is neater than any solution using XMLA.

One thing to note about this example: you will see that there is no ProcessRecalc after the ProcessAdd. As it turns out, ProcessAdd does the necessary recalculations so the database is queryable after this operation completes. Therefore it is not necessary to wrap this statement in a batch or to do the recalc.

Of course, the neatest solution of all is to update the definition of your view and then do a ProcessAdd on that. That is especially true if you are using Integration Services, as I will demonstrate in a future post.

Advertisements
One Comment
  1. Hi Cathy,
    I have another solution that I use for ProcessAdd – out-of line data source.
    This requires another (dummy) database that will be specified in the out-of line data source, creating a simple synonym there that points to a another view in the main database that is used only to provide new records.
    For example; if my data table is exposed through a view in DW database such as DW.dbo.factData, then I will create another view DW.dbo.factData_ProcessAdd, and a synonym for that view in dummy db eg create synonym DW_Dummy.dbo.factData for DW.dbo.factData_ProcessAdd.

    In my DW.dbo.factData_ProcessAdd view I use a join with a metadata table that keeps track of the new records.

    Regards,
    Hrvoje

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: