olap watsonrevise

Document Sample
olap watsonrevise Powered By Docstoc
					     On-line Analytical Processing
                OLAP




Analysis is simplifying, breaking down things into parts, picking out
strands and elements. Analysis is comparing unknown things with
things that are known. Analysis also involves picking out relationships
and putting them back together as a whole. – Edward de Bono
The Work Environment Today…



              Bad
            Decisions!
                     What is OLAP ??
On-Line Analytical                         • Multiple views

    Processing                             • Drill-Down

                                           • Slice-n’-Dice
                             Set of
• Natural to users
                        functionalities
• ‘Cubes’ of data        that facilitate
                       multi-dimensional
                         data analysis
                           for faster,
                        more informed
                       decision making
         Drill-Down Through a Dimension
                                                    Sales Volumes
    COLOR




M
O
D
E
L




      Clyde Gleason Carr   Levi   Lucas   Bolton              Gary   St. Louis Chicago   Midwest



                                                    REGION
                                                   DISTRICT
                                                   DEALERSHIP
    “Dicing” - Ranging of Data
               Sales Volumes

                                                 Coupe                  Clyde
    Mini Van
M
O                                                        Blue   White
D    Coupe
E                                                  “Diced” Data
L    Sedan
                                         Carr
                                       Gleason
                                    Clyde
                                                 DEALERSHIP
               Blue   Red   White



                 COLOR
              “Slicing” - Rotation of Data
                       Rotate
             Sales Volumes the data                       Sales Volume
                                         cube by 90°


M Van
 Mini Van
                                                     M Van
                                                      MiniVan
                                                 M   o
o                                                O
d Coupe
     Coupe                                       D   d Coupe
                                                          Coupe
                                                 E   e Sedan
e Sedan                                  Carr
                                                 L                                     Ca
     Sedan                                             Sedan
l
                                       Gleason
                                    Clyde            l
                                                   DEALERSHIP                        Glea
                                                                                  Clyde
             Blue
              Blue   Red
                      Red   White
                            White                          Miller Clyde
                                                            Blue    Red   Smith
                                                                          White

             Color                                        Dealership
                COLOR                                         COLOR
                     What is OLAP ??
On-Line Analytical                         • Multiple views

    Processing                             • Drill-Down

                                           • Slice-n’-Dice
                             Set of
• Natural to users
                        functionalities
• ‘Cubes’ of data        that facilitate
                                                   • Data
                       multi-dimensional
                         data analysis             • Processes
• Detection
                           for faster,             • Relations
• Measurement           more informed
• Comparison
                       decision making             • Concepts
                             OLAP Goals
                                              Faster
                                             Cognition

         DATA
                                              Better
                                           Comprehension
• Intensive

• Multi-Dimensional               OLAP
                                 TOOLS        Better
• Variety of Relationships                 Communication

• Complex Situations

• Context / Focus
                                               Better
                                          Decision-Making
                  OLTP versus OLAP
                            OLTP                            OLAP
User                    Clerk, IT Professional         Knowledge Worker
Function                Day-to-day Operations          Decision Support
Database Design         Application-oriented           Subject-oriented
                         (E-R based)                     (Star, Snowflake)
Data                    Current, Isolated              Historical, Consolidated
View                    Detailed, Flat Relational      Summarized, Multidimensional
Usage                   Structured, Repetitive         Ad-Hoc
Unit of Work            Short, Simple Transaction      Complex Query
Access                  Read / Write                   Read Mostly
Operations              Index/Hash on Prim. Key        Lots of Scans
# Records Accessed      Tens                           Millions
# Users                 Thousands                      Hundreds
Database Size           100s MB-GB                     100s GB-TB
Performance Metric      Transaction Throughput         Query Throughput, Response
    Four Characteristics of OLAP

   Use Multidimensional data analysis
    techniques (Most distinguishing)
       Multidimensional data analysis requires
        multidimensional data representation, which is
        usually provided by RDBMS.
   Provide advanced database support
   Provide easy-to-use end-user interfaces
   Support client/server architecture
     Multidimensional Data Analysis
              Techniques
   Viewing data as part of a multidimensional
    structure
   Multidimensional data analysis techniques are
    augmented by the following
       Advanced data presentation functions
            Pivot tables
            3-D graphics
            Crosstabs
            Data rotation
            3-D cubes
   Advanced data aggregation, consolidation,
    and classification functions to
      create multiple data aggregation levels
      slice and dice data

      Drill-down and roll up

   Advanced Computational functions
      Statistical functions
      Financial/accounting functions

      OR/MS modeling techniques (LP, regression, etc.)
   Analyzing data with pivottable
        What is a Pivot Table?

   A summary table based on multiple
    conditions
   A PivotTable uses two-dimensional data to
    create a three-dimensional table.
         Creating a Pivot Table

   Load dw_p2.xls to the spreadsheet
   From the Data menu, select PivotTable
    and PivotChart Report
   Identify the Data Source (Microsoft Excel)
    and Report (PivotTable), Select Next
   Specify the Data Range
   Determine the Pivot table’s location (new
    worksheet)
Pivot table template
        Laying Out the PivotTable

   The following list describes the four areas
    that are available to apply fields:
       Page. Create a drop-down menu above the
        table
       Row. Summarize data from top down
       Column. Summarize data from left to right
       Data. This area adds, counts, or creates other
        analytical functions.
      Laying Out the PivotTable

   Clicking the Layout button in the Step 3 of
    3 PivotTable Wizard dialog box displays
    the Layout dialog box.
Layout Dialogue box
Draw the 3D Bar Graph
Building a query for a PivotTable
           (Crosstabs)

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:0
posted:10/5/2012
language:English
pages:21