You can use any valid SQL statement here that will produce the same columns required by the partition that you are going to incrementally update.
The QueryDefinition element contains the query that will be used by the ProcessAdd operation. In order to find the right values you have to investigate the object properties in SQL Server Management Studio, otherwise you have to use the AMO or PowerShell approach that are described later in this article, which automatically find the right name starting from the display name of the object. The names that you can see in MeasureGroupID, PartitionID and DataSourceID elements contain a GUID in the name that has been automatically generated by the editor when you created the data model. SELECT * FROM FactInternetSales WHERE OrderDateKey >= 20120215 The Batch element contains a Process command, which specify the target partition / measure group / cube / database, followed by a Bindings element that replaces the existing query binding on the partition with a different query, which will be used just for this process command. In order to process a table partition in Tabular, you have to issue a process command to a partition of a measure group in the corresponding Multidimensional model that Analysis Services publish in order to make it queryable by any existing OLAP client tool.
#Process recalc ssas tabular how to#
In the following sections you will see how to execute and automate ProcessAdd by using different tools.
You can use this same approach by using separate operations, but ProcessAdd can be more optimized for this specific activity. Technically, when a ProcessAdd runs internally Analysis Services creates a new partition, process the whole partition and then merge it to the target partition (the one on which ProcessAdd command has been executed). There is no automatic detection of duplicates and if the table does not have unique columns you would obtain row duplicates in your table as a result, otherwise the process operation will stop with an error if a unique condition for a column is violated by loading new data. Remember that it is your responsibility avoiding duplicate rows in the destination table. It is out of scope of this article describing how you should define the SQL command that only returns the new rows to be added to the table in a Tabular model. A XMLA script command is required and you will see how to programmatically obtain it by using AMO and PowerShell.
Thus, a programmatic approach is required. However, this is not a big issue: in fact, if you need to use ProcessAdd, probably you need to automate that command in a batch process. The ProcessAdd command available in Process Partition(s) dialog box in SQL Server Management Studio (SSMS) does not allow you to specify a custom query for the process operation, in order to filter only new rows that have to be added to the partition. The ProcessAdd command allows you to add only a few rows to an existing partition and to do that you have to specify the query that reads data from the data source, applying the necessary WHERE condition in a SQL query or by using any other SQL statement to this purpose.
#Process recalc ssas tabular update#
UPDATE : If you use tabular models in compatibility levels 1200 or higher, ignore this article and read Using Process Add in tabular models, which shows techniques available with new AMO/TOM libraries and TMSL scripts.
0 kommentar(er)
