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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 18, 2009

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

By William Pearson

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

Source Column Setting, First Partition of the Internet Sales Measure Group
Illustration 23: Source Column Setting, First Partition of the Internet Sales Measure Group

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.

Enabling the Ellipses (“...”) Button
Illustration 24: Enabling the Ellipses (“...”) Button ...

4.  Click the ellipses (“...”) button.

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

The Partition Source – Internet_Sales_2001 Dialog Appears
Illustration 25: The Partition Source – Internet_Sales_2001 Dialog Appears

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, let’s 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.

Click the Storage Settings Link
Illustration 26: Click the Storage Settings Link

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

The Partition Storage Settings – Internet_Sales_2003 Dialog Appears
Illustration 27: The Partition Storage Settings – Internet_Sales_2003 Dialog Appears

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.

Setting

Description

Real-time ROLAP

Select to use the following storage and proactive caching settings:

  • ROLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 0 seconds
  • Brings object online immediately

Real-time HOLAP

Select to use the following storage and proactive caching settings:

  • HOLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 0 seconds
  • Updates cache when data changes, with a silence interval of 0 seconds and no silence override interval
  • Brings object online immediately

Low-latency MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 30 minutes
  • Updates cache when data changes, with a silence interval of 10 seconds and a silence override interval of 10 minutes
  • Brings object online immediately

Medium-latency MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enables proactive caching
  • Drops outdated cache, with a latency period of 4 hours
  • Updates cache when data changes, with a silence interval of 10 seconds and a silence override interval of 10 minutes
  • Brings object online immediately

Automatic MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enables proactive caching
  • Updates cache when data changes, with a silence interval of 0 seconds and no silence override interval

Scheduled MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode
  • Enable proactive caching
  • Updates cache periodically, with a rebuild interval of 1 day

MOLAP

Select to use the following storage and proactive caching settings:

  • MOLAP storage mode

Custom setting

Select to explicitly set storage mode, proactive caching, and notification options.

Table 1: Standard Storage Settings and Descriptions

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.

Click the Options Button
Illustration 26: Click the Options Button ...

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

The Storage Options Dialog  - General Tab Appears
Illustration 29: The Storage Options Dialog - General Tab Appears ...

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.

The Storage Options Dialog – Notifications Tab Appears
Illustration 30: The Storage Options Dialog – Notifications Tab Appears ...

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date