Optimizing Microsoft SQL Server Analysis Services: Optimization Tools: Incremental Update Wizard
13. Select Process from the context menu that appears, as partially shown in Figure 4.
Figure 4: Select Process from the Context Menu (Partial View)
The Process a Cube — Select the processing method dialog appears, as depicted in Figure 5.
Figure 5: The Select the Processing Method Dialog
Full Processing is the default (the other options are disabled), as the IncrUpdate cube has never been processed, at least from the perspective of history (none for the clone).
14. Leaving settings at default, click OK.
Processing begins, and runs rapidly, as evidenced by the Process viewer’s presentation of processing log events in real time. Processing ends rather quickly, and the success of the evolution is indicated by the appearance of the Processing Completed Successfully message (in green letters) at the bottom of the viewer, as shown in Figure 6.
Figure 6: Indication of Successful Processing
15. Click Close.
We are returned to Analysis Manager, having prepared the cube for an Incremental Update, based upon the fact that it was a clone with no history.
Next, let’s do some preparation to make the process work as it would in a real Incremental Update. For this to be simulated, we will need to build a small set of data to treat as the “add’ source of the Incremental Update.
Create Sample Data to Act as the “Add Target” in the Incremental Database
We will “go to the source,” (for our sample cubes, at least), the FoodMart 2000 MS Access database, for the purposes of creating a basic table, from which we can illustrate the operation of an Incremental Update using the Incremental Update Wizard in MSAS. While we could certainly source the existing fact table (with a filter to restrict all except the correct records into our Incremental Update process) for the “new” information we wished to add to our existing cube, we will create a simple additional table for that purpose, mainly to prevent causing any harm to the existing FoodMart 2000 schema.
1. Navigate to the FoodMart 2000.mdb file, either via Windows Explorer, MS Access, or other means desired.
The FoodMart 2000 database is typically installed in the Program directory for MSAS, in the Samples folder. An example path, assuming installation of MSAS on the C: drive, would appear as follows:
C:Program FilesMicrosoft Analysis ServicesSamples
2. Open the FoodMart 2000.mdb in MS Access (I’m using Access 2003 in my illustrations, but Access 2000 forward will work, and will be very similar in operations).
3. By whatever means is convenient, get to the Database View, which will appear similar to that shown in Figure 7.
Figure 7: Database View – FoodMart 2000.mdb
4. Right-click the expense_fact table.
5. Select Copy from the context menu that appears, as shown in Figure 8.
Figure 8: Select Copy on the Context Menu