Optimizing Dimension and Cube Processing - SQL Server Analysis Services 2012 Cube Development - page 175

Using out-of-line bindings for

dimension processing

You could use out-of-line bindings for incrementally processing very large dimensions
without having to read the full dataset. In this context, the term binding applies to the
dimension table or view. The sample database only has a handful of records for each
dimension table, but in a realistic scenario you could have a client, customer, or even
product dimension with millions of members. On a normal day, you could expect only
a small number (compared to the total number) of dimension members to be added.
Keep in mind that the ProcessAdd option reads the entire dataset in order to de-
termine which rows must be added to the SSAS dimension. Running a query against
a multimillion row dimension table could add an undue burden to the relational data-
base engine and cause processing to be unnecessarily lengthy. Fortunately, there is
a better option: out-of-line bindings.

How to do it...

Let's pretend for a few minutes that Adventure Works has grown by leaps and bounds
to become a multibillion dollar enterprise, which sells products to millions of custom-
ers. We try to process data many times per day, but can't afford to query the gigant-
ic customer dimension each time. Instead you have built a SQL Server view named
NewCustomers , which dynamically determines only those records which haven't
been added to the Analysis Services dimension (perhaps by checking the Create
Date column, and comparing it to the last time the dimension was successfully pro-
cessed). You will use this view to add new customers throughout the week by per-
forming the following steps. Over the weekend, you can run ProcessUpdate against
the full customer dataset to prune any obsolete records as well as refresh/update ex-
isting records that might have changed.

1. Connect to the SSAS instance with Adventure Works DW sample database
using SSMS, expand the Dimensions folder, right-click on the Customer di-
mension, and chose Process .
2. Change the processing option to Process Update , choose the option to ig-
nore duplicate key errors (as shown earlier in this chapter), and select Script
Action to New Query Window . Once the statement is scripted, click on