dcsimg

Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction - Page 4

October 11, 2004

Procedure

We can begin partitioning of our new cube with a few basic steps.

1.  Expand the new DBJ_Budget cube.

2.  Right-click the Partitions folder that appears within the Cube object.

The context menu appears.

3.  Click New Partition, as shown in Illustration 4.

Click for larger image

Illustration 4: Select New Partition

The Welcome dialog appears, as depicted in Illustration 5.

Click for larger image

Illustration 5: Welcome Dialog - Partition Wizard

4.  Click Next.

The Specify the data source and fact table dialog appears, as shown in Illustration 6.


Illustration 6: Specify the Data Source and Fact Table Dialog - Partition Wizard

We will leave this dialog at default, although we might have used it to specify a different data source to be used to define our partition. We will take advantage of this option in partitioning, and in an identical option for performing incremental updates, in subsequent articles. For now, let's continue with our exploration of the basic partitioning process.

5.  Click Next.

The Select the data slice dialog appears. Here we can designate the portion of our fact table upon which our partition is based. This dialog makes specification of the slice easy, by offering a selection of the dimensional members (the slice can be based upon a member, from any level of any available dimension) that are available within our cube's structure. A filter is created, based upon our selection in this dialog, which restricts the rows in the data source that Analysis Services retrieves during cube builds. We can also remove slices here, to modify an existing arrangement we have in place, as the need arises.

6.  Click Time in the Dimensions box (left side of the dialog).

The Members of the Time dimension appear in the Members box, to the right of the dialog.

7.  Click 1997 to select it.

NOTE: We see the Quarter levels if we expand 1997 with the "+" sign to the left, although we wish only to select 1997 at present.

The Select the data slice dialog appears, with our selections, as shown in Illustration 7.


Illustration 7: Select the Data Slice Dialog - Partition Wizard (Year 1997 Expanded)

8.  Click Next.

The Specify the partition type dialog appears. Here we can choose between a local (exists on the same server as the parent cube) or a remote partition type. This opportunity to specify the physical placement of the partition, to which we alluded in our introductory comments, can only be set at its creation. For purposes of our exercise, we will leave the dialog at the default selection of Local.

The Specify the partition type dialog appears as depicted in Illustration 8.


Illustration 8: Specify the Partition Type Dialog - Partition Wizard

9.  Click Next.

The Finish the Partition Wizard dialog appears. Here we can name the partition, and can make provision for its storage design. In our example, where we are creating partitions to segregate years, it is likely that, in the future (say, in 2000), our partitioning strategy might include the merging of years beyond the current and its immediate predecessor, which are by far the most frequently accessed for financial reporting purposes.

10.  Type the following into the Partition name box:

Budget_1997

Merging 1997 and 1998 (the reciprocal partition) together requires that the storage design for both be identical. Another convenience that is provided in the Finish the Partition Wizard dialog is the opportunity to design storage for the new partition in the image of the existing partition (remember that all cubes have at least one), through the use of the Copy the aggregation design from an existing partition radio button.

11.  Select the radio button to the immediate left of Copy the aggregation design from an existing partition, in the What do you want to do? section of the dialog.

The Copy from selector that accompanies the Copy the aggregation design from an existing partition option becomes enabled.

12.  Ensure that DBJ_Budget appears in the Copy from selector.

13.  Click the Process the partition when finished check box.

Another option we have at this stage is accessed via the Advanced button, appearing in the lower right of the dialog. Here, we can use the WHERE clause of an SQL statement to further filter our partition. We will revisit this option in subsequent articles.

The Finish the Partition Wizard dialog appears as depicted in Illustration 9.


Illustration 9: The Finish the Partition Wizard Dialog (with Our Settings Circled)

14.  Click Finish.

Processing begins, and we are soon greeted with indication of successful completion via the Process log viewer. The log viewer appears as shown in Illustration 10.


Illustration 10: The Process Log Viewer Indicates Successful Completion

15.  Click Close to close the Process log viewer.

The new partition, together with the pre-existing one, appears in the cube tree, as depicted in Illustration 11.


Illustration 11: The New Partition Appears ...

16.  Discard the DBJ_Budget cube, as desired.

(Simply right-click, and select the Delete option from the context menu that appears, to delete the cube clone.)

17.  Select File -` Exit, when ready to close Analysis Manager.

We have thus practiced the basic steps of using another useful performance tuning tool, the Partition Wizard. As more data becomes available for analysis, partitions (for quarters or months, in our simple scenario) could potentially be created to meet the needs of the organization in an optimal way. Those requirements, and the frequency with which they would need to be met, would dictate the best strategy for storage and processing optimization through the use of partitions. Once the partitions were created, aggregations and other considerations could be designed for each, with the only limitation being mandated by the likelihood of future merges, as we suggested in our practice exercise.

Summary...

In this lesson we introduced basic partitioning, another tool in our MSAS cube design and optimization toolset. As its name implies, the Partition Wizard provides us an easy means of creating and modifying partitions within our cubes. We first discussed partitioning as a concept, within the context of MSAS cubes, and then we began a hands-on exercise where we created a partition upon a copy of the Budget sample cube. As we proceeded through the guided steps of the Partition Wizard, we examined the options that are available to us, commenting upon general properties and optimization considerations along the way.

» See All Articles by Columnist William E. Pearson, III

Discuss this article in the MSSQL Server 2000 Analysis Services and MDX Topics Forum.

Introduction to MSSQL Server Analysis Services Series
Introduction to Security in Analysis Services
Cube Storage: Planning Partitions from a SQL Server Management Studio Perspective
Cube Storage: Planning Partitions (Business Intelligence Development Studio Perspective)
Cube Storage: Introduction to Partitions
Introduction to Cube Storage
Attribute Discretization: Customize Grouping Names
Attribute Discretization: Using the "Clusters" Method
Attribute Discretization: Using the "Equal Areas" Method
Attribute Discretization: Using the Automatic Method
Introduction to Attribute Discretization
More Exposure to Settings and Properties in Analysis Services Attribute Relationships
Attribute Relationships: Settings and Properties
Introduction to Attribute Relationships in MSSQL Server Analysis Services
Attribute Member Values in Analysis Services
MSSQL Analysis Services - Attribute Member Names
Attribute Member Keys - Pt II: Composite Keys
Attribute Member Keys - Pt 1: Introduction and Simple Keys
Dimension Attributes: Introduction and Overview, Part V
Dimension Attributes: Introduction and Overview, Part IV
Dimension Attributes: Introduction and Overview, Part III
Dimension Attributes: Introduction and Overview, Part II
Dimension Attributes: Introduction and Overview, Part I
Dimensional Model Components: Dimensions Part II
Dimensional Model Components: Dimensions Part I
Manage Unknown Members in Analysis Services 2005, Part II
Manage Unknown Members in Analysis Services 2005, Part I
Alternatively Sorting Attribute Members in Analysis Services 2005
Introduction to Linked Objects in Analysis Services 2005
Distinct Counts in Analysis Services 2005
Positing the Intelligence: Conditional Formatting in the Analysis Services Layer
Administration and Optimization: SQL Server Profiler for Analysis Services Queries
Mastering Enterprise BI: Time Intelligence Pt. II
Mastering Enterprise BI: Time Intelligence Pt. I
Design and Documentation: Introducing the Visio 2007 PivotDiagram
Actions in Analysis Services 2005: The URL Action
Actions in Analysis Services 2005: The Drillthrough Action
Mastering Enterprise BI: Introducing Actions in Analysis Services 2005
Mastering Enterprise BI: Introduction to Translations
Mastering Enterprise BI: Introduction to Perspectives
Introduction to the Analysis Services 2005 Query Log
Mastering Enterprise BI: Working with Measure Groups
Mastering Enterprise BI: Introduction to Key Performance Indicators
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. II
Mastering Enterprise BI: Extend the Data Source with Named Calculations, Pt. I
Process Analysis Services Objects with Integration Services
Usage-Based Optimization in Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Named Sets Revisited
Introduction to MSSQL Server Analysis Services: Migrating an Analysis Services 2000 Database to Analysis Services 2005
Introduction to MSSQL Server Analysis Services: Introducing Data Source Views
Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ...
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Create Aging "Buckets" in a Cube
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube, Part II
Introduction to MSSQL Server Analysis Services: Mastering Enterprise BI: Relative Time Periods in an Analysis Services Cube
Introduction to MSSQL Server Analysis Services: Process Analysis Services Cubes with DTS
Introduction to MSSQL Server Analysis Services: Presentation Nuances: CrossTab View - Same Dimension
Introduction to MSSQL Server Analysis Services: Point-and-Click Cube Schema Simplification
Introduction to MSSQL Server 2000 Analysis Services: Manage Distinct Count with a Virtual Cube
Introduction to MSSQL Server 2000 Analysis Services: Distinct Count Basics: Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Semi-Additive Measures and Periodic Balances
Introduction to MSSQL Server 2000 Analysis Services: Performing Incremental Cube Updates - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Partitioning a Cube in Analysis Services - An Introduction
Introduction to MSSQL Server 2000 Analysis Services: Basic Storage Design
Introduction to MSSQL Server 2000 Analysis Services: Derived Measures vs. Calculated Measures
Introduction to MSSQL Server 2000 Analysis Services: Creating a Dynamic Default Member
Introduction to MSSQL Server 2000 Analysis Services: Another Approach to Local Cube Design and Creation
Introduction to MSSQL Server 2000 Analysis Services: Introduction to Local Cubes
Introduction to MSSQL Server 2000 Analysis Services: Actions in Virtual Cubes
Introduction to MSSQL Server 2000 Analysis Services: Putting Actions to Work in Regular Cubes
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Part II
Introduction to MSSQL Server 2000 Analysis Services: Reporting Options for Analysis Services Cubes: ProClarity Professional, Part I
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services , Part II
Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Toward More Sophisticated Analysis
Introduction to MSSQL Server 2000 Analysis Services: MSAS Administration and Optimization: Simple Cube Usage Analysis
Introduction to MSSQL Server 2000 Analysis Services: Build a Web Site Traffic Analysis Cube: Part II
Build a Web Site Traffic Analysis Cube: Part I
Reporting Options for Analysis Services Cubes: Cognos PowerPlay
Reporting Options for Analysis Services Cubes: MS FrontPage 2002
Reporting Options for Analysis Services Cubes: MS Excel 2002
Introduction to MSSQL Server 2000 Analysis Services: Drilling Through to Details: From Two Perspectives
Introduction to MSSQL Server 2000 Analysis Services: Custom Cubes: Financial Reporting - Part II
Introduction to MSSQL Server 2000 Analysis Services Custom Cubes: Financial Reporting (Part I)
Introduction to SQL Server 2000 Analysis Services: Exploring Virtual Cubes
Introduction to SQL Server 2000 Analysis Services: Working with the Cube Editor
Introduction to SQL Server 2000 Analysis Services: Parent-Child Dimensions
Introduction to SQL Server 2000 Analysis Services: Handling Time Dimensions
Introduction to SQL Server 2000 Analysis Services: Working with Dimensions
Introduction to SQL Server 2000 Analysis Services: Creating Our First Cube








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers