Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective) - Page 5

Partition Source and Fact Table Assignment

Individual partitions can be sourced from different physical locations, as we have already discussed, and as we shall see later in this practice session and elsewhere in this subseries. A planning effort, therefore, might include consideration of partition sources. We have already seen a couple of places where we can examine the partition source(s) for a pre-existing measure group.

Moreover, because any individual partition can be supported by a different fact table (within the same database / schema), fact table assignment can be a valid partition planning criterion.

The Partitions tab loads, once again.

We again see the grid containing the list of partitions that support the selected measure group. Our focus here is the Source column, which, for the first listed partition of the Internet Sales measure group, we see circled in red in Illustration 23

The Partition Source – Internet_Sales_2001 dialog appears, as depicted in Illustration 25.

Here we see the targeted Data source, the Adventure Works DW relational database, and the query (including fact table) that we apply against it to define the first of four partitions. We also note the warning here, that our query needs to exclude any data included in other partitions (to avoid double counting).

When we consider Partition Source as a criterion in partition planning, we should also consider the “filtering” of that same source for various elements. Time is a rather obvious dimensional basis for partitioning, an illustration of which we see in this example (within the WHERE clause of the Data source query), where data with an Order Date earlier or equal to December 31, 2001 is entrained into this partition.

Storage Type Settings

We discussed the three types (MOLAP, HOLAP, and ROLAP) of storage in Cube Storage: Introduction. Storage type represents another criterion upon which to plan partitions, as we can assign any individual partition the type of storage that we deem most appropriate. For example, we might consider assigning a ROLAP storage type to a partition containing rarely queried, historical data (and thus leave the storage burden largely to the underlying relational database); in most cases, by contrast, we would assign the MOLAP storage type to a partition upon which we expected heavy query demands, such as a partition containing current / recent periods’ data.

The Storage Settings link on the Partitions tab allows us to access the storage type settings for whichever partition we have highlighted. To illustrate the context-sensitive nature of the link, and to understand the settings it makes available to us, let’s take the following steps from the Partitions tab.

The Partition Storage Settings – Internet_Sales_2003 dialog appears, as depicted in Illustration 27.

We can use the Storage Settings dialog box in Business Intelligence Development Studio to set the proactive caching, storage, and notification settings for a dimension, cube, measure group, or partition. We can set storage type through the use of the setting slider that we see here. By clicking the radio button labeled Standard setting, we enable the slider with its predefined settings for storage mode and proactive caching features. The predefined settings, along with descriptions, appear in Table 1.

The two-tabbed Storage Options dialog appears, defaulted to the General tab, as depicted in Illustration 29.

We can use the General tab of the Storage Options dialog box in Business Intelligence Development Studio to set the storage mode and proactive caching settings for a dimension, cube, measure group, or partition. We discuss these settings in several independent articles of this series.

The Storage Options dialog appears, with the Notifications tab selected, as shown in Illustration 30.

We can use the Notifications tab of the Storage Options dialog box to set the notification method and related settings for a dimension, cube, measure group, or partition. Again, we discuss these settings in several independent articles of this series.