SSAS cube incremental data processing guidelines

SSAS provides Process Incremental/Add as one of the processing options for cubes, measure groups and partitions.

Veerendra Thati has explained nicely in his blog

Let’s look at how to extend Adventure Works sample cube to allow incremental processing of Internet Sales Measure Group

Steps to configure Incremental cube processing for InternetSales measure group

  1. Connect to SQL Server AdventureWorksDW database
    in SSMS and add an identity key to FactInternetSales table using the following
    query.

ALTER TABLE FactInternetSales ADD id INT IDENTITY

This new column can be used to track the rows that have not been loaded into the cube yet

 

 

  1. The following steps need to be done in SSDT on AdventureWorks cube

 

  1. Refresh the Adventure Works DW.dsv in SSDT to
    bring in the new id field that has been added to FactInternetSales table

internetsalesfact

 

  1. Add the id measure to InternetSales Measure group and select the aggregation usage as Maximum as given in the screenshot below

This measure can be used to track the data that has been last loaded into the cube

newmeasureid

  1. Rename the measure as Internet Sales Id
  2. Edit the existing Partitions in InternetSales measure group to include the new column in the query.
    SSDT does not automatically refresh the queries used in partitions and this needs to
    be manually done.

queryforpartition2005

  1. Adventure Works Cube sample has four partitions for InternetSales Measure group partitioned by the Year of the order creation

2005, 2006, 2007 and 2008.

For the purpose of this blog, we will end date the 2008 partition with 20081231 and create a new partition for incremental processing

Edit the 2008 partition to include the following WHERE condition

WHERE OrderDateKey >= ‘20080101’ AND OrderDateKey <= ‘20081231’

 

Create a new open ended partition and name it Internet_Sales_Current with the following query

SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey],[dbo].[FactInternetSales].[CustomerKey],[dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber],CONVERT ( CHAR ( 10 ), SalesOrderNumber ) + ‘Line ‘ +
CONVERT ( CHAR ( 4 ), SalesOrderLineNumber ) AS [SalesOrderDesc],[dbo].[FactInternetSales].[OrderDate],[dbo].[FactInternetSales].[DueDate],[dbo].[FactInternetSales].[ShipDate],[dbo].[FactInternetSales].[id]
FROM [dbo].[FactInternetSales]
WHERE OrderDateKey > ‘20090101’

  1. Deploy the changes to the cube and perform a Process Full to load the data into the cube
  2. Now we are ready to perform the Incremental
    Processing of the new partition that we created.

To test this, we add a new record into
AdventureWorksDW.dbo.FactInternetSales table. This would generate a row with a new id

 

  1. Replace OLAPSERVERNAME with Analysis Services Server name and OLAPDBName with AdventureWorks OLAP database name for your instanceAdd a linked server to Analysis Services Server from SQL Server that hosts the AdventureWorksDW2012 database.
  1. Connect to the AdventureWorks Cube from SSMS
    a. Right click on the partition that we had created
    and click on Process

processnewpartition

  1. Select Process Add from the process options and click on Configure
  2. Specify the source as query and input the following query .

The key is in the where clause and the using the linked server created,

we are finding out the last [id] that was loaded into the Measure group to retrieve the delta records that have to be loaded

SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey],[dbo].[FactInternetSales].[CustomerKey],[dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber],CONVERT ( CHAR ( 10 ), SalesOrderNumber ) + ‘Line ‘ +
CONVERT ( CHAR ( 4 ), SalesOrderLineNumber ) AS [SalesOrderDesc],[dbo].[FactInternetSales].[OrderDate],[dbo].[FactInternetSales].[DueDate],[dbo].[FactInternetSales].[ShipDate],[dbo].[FactInternetSales].[id]
FROM [dbo].[FactInternetSales]
WHERE id > (select * from openquery(AdvWorksOLAP, ‘select [Measures].[InternetSalesId] on columns from [Adventure Works]’))

  1. Click on OK to complete the incremental processing of the cube.
  2. Browse the cube to make sure that the record with new id in Internet Sales has been loaded into the cube

This completes the process of incrementally processing the latest partition of Internet Sales Measure Group.

Advertisements

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