Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective) - Page 5
November 18, 2009
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.
1. Click the Partitions tab atop the Cube Designer, as we did earlier.
The Partitions tab loads, once again.
2. On the Partitions tab, ensure that the Internet Sales measure group (the top group in a list of eleven groups) is open, once again, by clicking the two upward pointing carets, as we did earlier, if necessary.
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
3. Click the top cell in the Source column, to give it the focus and enable the ellipses (...) button on its right, as shown in Illustration 24.
4. Click the ellipses (...) button.
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.
5. Click OK to close the Partition Source Internet_Sales_2001 dialog.
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, lets take the following steps from the Partitions tab.
1. Click the Internet_Sales_2003 partition, within the Internet Sales measure group pane, to select it.
2. Click the Storage Settings link, just underneath the Internet Sales measure group pane, as shown in Illustration 26.
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.
3. Click the radio button to the immediate left of the Custom setting label in the lower portion of the Partition Storage Settings Internet_Sales_2003 dialog.
4. Click the Options button to the right of the Custom setting radio button, as shown in Illustration 28.
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.
5. Click the Notifications tab on the Storage Options dialog box.
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.
6. Click OK to return to the Partition Storage Settings Internet_Sales_2003 dialog.
7. Click Cancel to close the Partition Storage Settings Internet_Sales_2003 dialog without saving any changes, and to return to the Partitions tab of the Cube Designer.