Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 9, 2005

Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS - Page 4

By William Pearson

We note that the radio buttons appearing within the Select a processing option section (to the right of the Select the object to process pane) change to reflect the Full process option only (the others are disabled). In like manner, the options change depending upon our selections, in a context-sensitive way.

14.  Expand the Foodmart 2000 Analysis Services database.

The underlying Cubes, Dimensions, and Mining Models folders appear.

15.  Click the Dimensions folder to highlight it.

The processing options again change, to reflect those that are available for the cube dimensions as a group. Our options now include Rebuild the dimension structure and Incremental update, as we noted in the tables above, and as is depicted in Illustration 7.

Click for larger image

Illustration 7: Options for Processing the Dimensions Folder ...

16.  Expand the Cubes folder.

The underlying Cubes for the FoodMart 2000 database appear.

17.  Click the Warehouse cube to highlight it.

In addition to the corresponding change in the possible options (Full process, Refresh data, and Incrementally update dimensions) within the Select a processing option section, we note the appearance of a new object: the Cube Settings button appears as shown in Illustration 8.

Illustration 8: The Cube Settings Button Appears

18.  Click the Cube Settings button.

The Cube Processing Settings dialog opens, revealing several options whereby we can establish recurring settings for cube processing. Our settings will be enforced anytime we process the cube or its partition(s). In effect, this is the same set of settings that we encounter from the Settings button in the standard Process a Cube dialog box, when we are manually processing a cube, as we have done in many articles of the Introduction to MSSQL Server Analysis Services series. Note that any settings we make here are stored, and override any previous processing settings made elsewhere.

The Cube Processing Settings dialog includes the setting options detailed in Table 3.


Use This Setting to:

Processing Optimization Mode:

- After all aggregations are calculated

(Default) Make data available for browsing only after all aggregations have been accomplished.

- Immediately after data is loaded

Make data available for browsing after it has been loaded, but before all aggregations have been accomplished. (New data is available sooner for querying; a potential cost is lessened responsiveness to query execution from the Analysis server.

Missing Dimension Key Settings:

- Stop processing after encountering missing dimension key errors

Stop processing if dimension key errors are encountered.

Processing will stop after [Number] errors

(Default= zero)

Here we can specify the number of errors after which processing is to stop.

- Ignore all missing dimension key errors

Continue processing, regardless of dimension key errors.

Log dimension key errors to a file

Affords a means of specifying a path to a text file.

-     File path and name

Specify where file will reside, along with its name

-     Browse

Click to navigate to a local or network location, for easy selection of a home for the file.

NOTE: Best practices dictate that the path observe the Universal Naming Convention (UNC), as is the case in similar scenarios.

Table 3: Selection Options within the Cube Processing Settings Dialog

The Cube Processing Settings dialog appears (with default settings) as depicted in Illustration 9.

Illustration 9: The Cube Processing Settings Dialog, with Defaults

19.  Click OK to close the Cube Processing Settings dialog, without making any changes.

We return to the Analysis Services Processing task dialog. We will leave the settings at default, which means Full process, among other default settings.

NOTE: Had we chosen Incremental update for a given partition of the Warehouse cube (it is a two-partition cube, as we have discussed in other articles of the series), we would have been able to change the data source and fact table under consideration. We would also have been offered an opportunity to establish a filter limiting fact table records to be included in the incremental update.

For an introduction to partitions within Analysis Services cubes, see my Database Journal article Partitioning a Cube in Analysis Services - An Introduction. For more information regarding incremental updates, see my article Performing Incremental Cube Updates - An Introduction.

20.  Click OK on the Analysis Services Processing task dialog, to finish creating the new DTS task.

The Analysis Services Processing task dialog closes.

21.  Select Package --> Save on the Package menu, as shown in Illustration 10.

Illustration 10: Saving the New Package, Containing Our Analysis Services Processing Task

The Save DTS package dialog appears.

22.  Type the following into the Package name box of the Save DTS package dialog:


23.  Leaving the other settings in the dialog at default, input the correct authentication information for your local environment.

The Save DTS package dialog appears, using my own local settings as an example, similar to those depicted in Illustration 11.

Illustration 11: Save DTS Package Dialog, with My Local Settings

24.  Click OK to save the package and to close the Save DTS package dialog.

We are returned to the design sheet.

25.  Select Close, under the "hammer and table" icon, as shown in Illustration 12.

Illustration 12: Select Close to leave the DTS Design Environment

We are returned to Enterprise Manager. From this point, we will execute the DTS package that contains our new Analysis Services Processing task.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM