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 6

By William Pearson

Storage Location Assignment

Whether we choose remote or local partitioning, location of the partition storage is yet another criterion upon which to plan our partitions. We might, for example, plan to create a partition with local storage for current data that we might expect to query often; in contrast, we might plan to create a remote partition for the historical data of previous years, upon which we might expect only minimal need to perform queries.

We noted earlier that we can design storage for a cube on the Partitions tab in Cube Designer. Moreover, we noted that we can configure storage and design aggregations separately for each partition in a measure group. We can also set default storage settings for any measure group. We can see sample Storage Location settings for a partition of the Internet Sales measure group by taking the following steps.

1.  Right-click the Storage Location setting for the Internet_Sales_2003 partition (currently indicating Default).

2.  Select Properties from the context menu that appears, as depicted in Illustration 31.

Selecting Properties for the Internet_Sales_2003 Partition
Illustration 31: Selecting Properties for the Internet_Sales_2003 Partition

The Properties settings for the Internet_Sales_2003 partition appear underneath the Object Explorer, by default, in the lower right corner of the development environment, as shown in Illustration 32.

Properties Settings for the Internet_Sales_2003 Partition
Illustration 32: Properties Settings for the Internet_Sales_2003 Partition

3.  Scroll down in the Properties settings to the StorageLocation setting.

4.  Click the label of the StorageLocation setting, to enable the ellipses (“...”) button on its immediate right, as depicted in Illustration 33.

Enable the Ellipses (“...”) Button
Illustration 33: Enable the Ellipses (“...”) Button ...

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

The Partition Type Selection dialog for the Internet_Sales_2003 partition appears, as shown in Illustration 34.

Partition Type Selection Dialog for the Internet_Sales_2003 Partition
Illustration 34: Partition Type Selection Dialog for the Internet_Sales_2003 Partition

We typically specify the Microsoft SQL Server Analysis Services instance of the cube that owns the partition, as well as the Analysis Services instance that stores the data for the partition on the Processing and Storage Locations page of the Partition Wizard. (We get hands-on exposure to the Partition Wizard in independent articles of this subseries, where we see that we can define a partition as a remote partition by specifying either a remote Analysis Services instance or a storage location other than the default storage location, among other possibilities.)

The Processing and Storage Location settings on the Partition Type Selection dialog can serve as potential bases for partition planning, in general. The settings, along with a description of each, appear in Table 2.

Setting

Description

 

 

Processing Location Options

 

Current server instance

Makes the current Analysis Services instance responsible for processing the partition.

Remote Analysis Services data source

Makes a remote Analysis Services instance responsible for processing this partition.

(From the dropdown list, we can select the data source representing the remote Analysis Services instance that will be responsible for processing the partition.)

New

Creates a new data source representing the remote Analysis Services instance responsible for processing the partition.

Storage Location Options

Default server location

Makes the data folder of the current Analysis Services instance the storage location of the aggregation and indexing data for the partition.

Specified folder

Specifies the storage location of the aggregation and indexing data for the partition.

The ellipses button (“...”) to the right of this setting displays the Browse for Remote Folder dialog box in which we can select a folder for Specified folder.

Table 2: Processing and Storage Locations Settings and Descriptions

6.  Click Cancel to close the Partition Type Selection dialog without saving any changes, and to return, once again, 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