cube by huangyuarong

VIEWS: 0 PAGES: 25

									Creating Cubes
Overview


  n   Introduction to Cubes

  n   Defining Cubes

  n   Managing Access to Cubes

  n   Storing Cubes

  n   Processing Cubes

  n   Customizing Cubes
u Introduction to Cubes
 Location
            Atlanta                            Product
                                    Grapes
          Denver
        Detroit                     Cherries

                                    Melons
                      Sales
                       Sales        Apples

                                    Pears



               Q1     Q2 Q3    Q4
                      Time
Components of a Cube
 Location
               Atlanta                                                 Product
                                                      Grapes
Member
Member       Denver
           Detroit                                    Cherries
 Dimension
  Dimension
                                                      Melons
                             Cell
                              Cell
                                                      Apples

                                                      Pears

   Day 1 Day 2 ...
   Day 1 Day 2 ...
     Jan. Feb. ...
     Jan. Feb. ...
        1998 1999 ...
        1998 1999 ...                          Time
                                               Time        Start
                                                           Start       End
                                                                       End
                        Q1    Q2 Q3   Q4       Quarter 1
                                               Quarter 1   July 1
                                                           July 1      September 30
                                                                       September 30
                                               Quarter 2
                                               Quarter 2   October 1
                                                           October 1   December 31
                                                                       December 31
              Levels
               Levels         Time         Properties3
                                               Quarter 3
                                               Quarter
                                            Properties     January 1
                                                           January 1   March 31
                                                                       March 31
                                               Quarter 4
                                               Quarter 4   April 1
                                                           April 1     June 30
                                                                       June 30
u Defining Cubes


 n   Identifying a Fact Table
 n   Defining Dimensions
 n   Managing Aggregations
Identifying a Fact Table


  n   Choosing Measures
  n   Identifying Appropriate Measures
      l   Additive columns
      l   Nonadditive columns
  n   Determining Level of Detail
u Defining Dimensions


  n   Private Dimensions
      l   Minimize the processing of multiple cubes
  n   Shared Dimensions
      l   Avoid duplicating private dimensions within each of the
          cubes

      l   Allow standardization of business metrics among cubes
Creating Shared Dimensions


  n   One or Multiple Dimension Tables
  n   Data Source Containing Tables and Columns
  n   Dimension Tables Containing the Columns to Include
  n   Standard or Time Dimension
  n   Levels in the Shared Dimension
Managing Aggregations

  n   Avoid Aggregating Every Possible Combination of Data
      l   Balance between storage requirements and query
          performance
  n   Use the Storage Design Wizard
      l   Finds the “80-20” rule
  n   Control Number of Aggregations
      l   Specify storage space limit
      l   Specify performance gain limit
  n   Example of Data Explosion
Managing Access to Cubes

  n   Verifying Authentication
  n   Assigning Roles
      l   Create roles
      l   Establish permissions
      l   Assign users to roles
  n   Controlling File Level Access
      l   Read
      l   Admin
Demonstration: Using OLAP Manager
u Storing Cubes


  n   Storing in a MOLAP Structure

  n   Storing in a ROLAP Structure

  n   Storing in a HOLAP Structure

  n   Comparing Storage Structures

  n   Storing Partitions
Storing in a MOLAP Structure

    Data Warehouse or
    Data Warehouse or          Cube Database
                               Cube Database
        Data Mart
         Data Mart
                         Copy of the fact table and
                         dimensions are stored in a cube

                         Aggregations are stored in a cube


        SQL Server

                               MOLAP Data

                                 MOLAP
                               Aggregations
Storing in a ROLAP Structure

                Data Warehouse or Data Mart
                Data Warehouse or Data Mart

                                       Base data is retained in
                ROLAP Data
                ROLAP Data             source database
                                       Aggregations are stored in
                  ROLAP Aggregations
                  ROLAP Aggregations   relational tables
   SQL Server                          OLAP Services is used to
                                       manage cube data and
                                       aggregations
Storing in a HOLAP Structure

Data Warehouse or Data Mart
Data Warehouse or Data Mart        Cube Database
                                   Cube Database

   Base data is retained in    Aggregations are calculated and
   source database             stored in a cube


                ROLAP Data
                ROLAP Data


     SQL Server
                                   MOLAP
                                 Aggregations
Comparing Storage Structures

         Storage
         Storage         MOLAP
                         MOLAP      HOLAP
                                    HOLAP        ROLAP
                                                 ROLAP
                                   Relational
                                   Relational   Relational
                                                Relational
 Base data
 Base data               Cube
                         Cube        Table
                                     Table        Table
                                                  Table
                                                Relational
                                                Relational
 Aggregation
 Aggregation             Cube
                         Cube        Cube
                                     Cube         Table
                                                  Table


    Client perspective
    Client perspective   MOLAP
                         MOLAP      HOLAP
                                    HOLAP        ROLAP
                                                 ROLAP

 Query performance
 Query performance       Fastest
                         Fastest    Faster
                                    Faster        Fast
                                                  Fast

 Storage consumption
 Storage consumption      High
                          High      Medium
                                    Medium        Low
                                                  Low

 Cube maintenance
 Cube maintenance         High
                          High      Medium
                                    Medium        Low
                                                  Low
Storing Partitions

  n   One Partition Is Automatically Created to Store Cube
      Data
  n   A Cube Can Be Stored Across Many Partitions
  n   Partitions Have No Effect on the Cube
                          Sales Cube                Partitions
                                                    Partitions

                                                       1997

                       1997
                                                       1998
                       1998

                       1999                            1999
Processing Cubes

  n   Steps in Processing Cubes
       1    Retrieve and store base data
       2
       2    Calculate aggregations based on defined dimensions
       3    Store cube in multidimensional structure or relational
            table
  n   Querying Cubes
      l    Users can continue to query the cube while processing it
uCustomizing Cubes


  n   Creating Calculated Members
  n   Creating Virtual Cubes
uCreating Calculated Members


  n   Calculate Values from Other Members’ Values
  n   Add Members and Measures to a Cube Without
      Increasing the Storage Size
  n   Use Nonadditive Columns as Measures in a Cube
Defining Calculated Members


  n   Choose Parent Dimension or Measures
  n   Choose Parent Member
  n   Specify Name of the Calculated Member
  n   Specify Expression
Creating Virtual Cubes


  n   Why to Create Virtual Cubes
      l   Create combinations and variants of existing cubes
      l   Can implement security functions
  n   Combining Multiple Cubes
  n   Exposing Subsets of a Cube
  n   Building Virtual Cubes
  n   Processing Virtual Cubes
Recommended Practices


  Determine the Lowest Level of Detail Required by Users
  Determine the Lowest Level of Detail Required by Users


  Create Shared Dimensions
  Create Shared Dimensions


  Use the Storage Design Wizard to Minimize Data Explosion
  Use the Storage Design Wizard to Minimize Data Explosion

  Create Virtual Cubes
  Create Virtual Cubes
Lab A: Creating and Processing Cubes
Review


  n   Introduction to Cubes

  n   Defining Cubes

  n   Managing Access to Cubes

  n   Storing Cubes

  n   Processing Cubes

  n   Customizing Cubes

								
To top