Teradata Business Intelligence Optimizer by iqtT6177

VIEWS: 157 PAGES: 26

									Teradata Business Intelligence

2011 Spring TUG

Sam Tawfik
Product Marketing Manager
sam.tawfik@teradata.com     @teradata_sam

• Teradata Aggregate Designer
• Teradata OLAP Connector Demo
• Implementation Considerations
    OLAP Environments

Multiple BI                   Marketing      Finance/Planning   Customer Service
•   Cubes everywhere
•   Data replication
•   Long ETL jobs
•   Limited scalability
•   Limited data sets
•   Mid-tier server costs   Physical Cubes     Physical Cubes      Physical Cubes
                               (MOLAP)            (MOLAP)             (MOLAP)
Convert to ROLAP Environments
Eliminate Physical Cubes
              Marketing      Finance/Planning   Customer Service

            Physical Cubes     Physical Cubes      Physical Cubes
               (MOLAP)            (MOLAP)             (MOLAP)
    ROLAP Solution

Multiple BI                   Marketing      Finance/Planning   Customer Service
•   Single View
•   Eliminate delays
•   DW scalability
•   Rich detailed data
•   Reduce/Eliminate need
    for Mid-tier server     Physical Cubes
                               BI Server       Physical Cubes
                                                  BI Server        Physical Cubes
                                                                      BI Server
                               (MOLAP)            (MOLAP)             (MOLAP)
ROLAP Solution

• Convert MOLAP to ROLAP with Teradata Database
  > Let Teradata host and analyze the data
  > Use existing Teradata Indexes to optimize response time
  > Use Teradata Aggregate Join Index (AJI) the Teradata
    built-in OLAP optimization capabilities

• High performance analytics powered by Teradata
• BI solutions benefit from the following:
  >   Increased breadth/depth of business information
  >   Eliminate data latency
  >   Eliminate/reduce need for the mid-tier server
  >   Avoid mid-tier scalability and performance limitations
OLAP Implementations Attributes

Attribute               MOLAP                 HOLAP                     ROLAP

Flexibility      Application Specific   Application Specific      Application Neutral

Query                                   Sub-seconds for cube;
                 Sub-seconds                                      Few seconds
Performance                             few seconds for detail

Breadth of       Limited to Cube on
                                        Almost unlimited          Almost Unlimited
Analytics        server
                 Limited to Cube on
Depth of                                Drill-down for detailed
                 server and drill                                 Detailed analysis
Analytics        through

                 Varies depending on    Combination of cube
Data Freshness   cube refresh           data and current
                                                                  Current data

Cost of
                 High                   Moderate                  Low
      Customer Example

      ROLAP Implementation
      • 38 dimensions and 24 measures with two              • Implemented ROLAP solution using AJIs
        years of history                                        > History expanded from 24 to 38 months
         > Added 39th dimension (Wire Center)                   > # of dimensions expanded from 38 to 50
      • MOLAP solution couldn’t handle the                      > # of measures expanded from 15 to 21
        business change                                     • Results
                                                                > Maintenance: 13 hours to 3 minutes
                                                                > Cube size: 22.4 GB to <10GB
                                                                > Detail: Month to Daily

         Response Comparison:


                                                                                            ROLAP with AJIs


           5 Canned       5 Canned with Wire    6 Interactive    6 Interactive with Wire
                                Center                                   Center
Teradata Aggregate Designer
 Teradata Aggregate Join Index Timeline

V2R3.0.2   AJIs Introduced

                 V2R5.0      Sparse JI/AJI, Single AMP, Group-AMP

                                        V2R6.2    PPI

                                                              12    M-PPI

                      V2R5.1     RI                     PGB, Cost-based re-write 13
                                                         OUTER JOIN, COUNT,
                                                 CUBE, ROLLUP, GROUPING SET,
                                                   Subqueries, Spool derived table
Aggregate Join Index
Pre-aggregated Results and Calculations

• For frequently needed roll-up summaries
  > Sales results, orders, shipments, etc.
  > Bypass join, sort, sum at run time
  > Applies for lower level aggregations (many transactions)
• Good for
  >   Dashboards, portal
  >   Interactive analytics

                                                                 Fast response
                                                                time from using
                                                               aggregated results

ROLAP Implementation with Teradata

1.    Identify the target solution – start with a small cube
2.    Capture the cube schema definition
3.    Validate the BI schema and physical data model are suitable for ROLAP
4.    Perform data quality checks
5.    Create the semantic data model
6.    Select the target database for the ROLAP solution
7.    Select the aggregation levels for each dimension
8.    Create the necessary base and broad AJI(s)
9.    Review the Aggregate Join Index DDL’s and modify as necessary
10.   Validate/Publish the AJIs on the target database
11.   Perform one data loading job from source to the fact table
12.   Test the AJIs to make sure they are being used by the Optimizer
13.   Collect statistics
14.   Tune the AJIs as the BI solution evolves
15.   Gradually add more features to the cube or create new cubes
Teradata Aggregate Designer

  > Desktop visual administrative tool to automate designing,
    building, and managing AJIs
  > Natively import cube definitions from IBM Cognos, Microsoft SQL
    Server Analysis Services, or Teradata Schema Workbench

Release 13.10 Enhancements
  > Native support for IBM Cognos
    Framework Manager
    (8.3, 8.4, and 10)
  > Ease of use and functionality
  > Enhanced database and schema validations
  > Detect and alert Schema changes
  > Support AJI enhancement in 13.10
Teradata Aggregate Designer Features

Database Validation
   > Validates target database schema
     for AJI requirements
   > Recommends possible database
     schema changes
AJI Advisor
   > Uses Teradata-specific heuristics to
     recommend the optimal AJI design
AJI Creation Services
   > Provides AJI storage cost estimates
   > User can manually modify AJI levels
   > Validates the SQL and creates
     Teradata DDL
• Supports PI and PPI definitions
Microsoft BI with Teradata ROLAP
Leveraging Teradata Aggregate Designer

                                                    • Teradata Aggregate Designer automates
                                                      designing, building, and managing AJIs for ROLAP

                     Point Server

   SQL Server


                                                    1. DBA leverages the Teradata Aggregate Designer
                                                       tool to consume cube schema from SQL Server
         Office SharePoint Server                      Analysis Services
                                                    2. DBA creates/modifies necessary AJIs using
                  SQL Server
                                                       Teradata Aggregate Designer
                Analysis Services
         Teradata .NET Data Provider

           Optimized SQL

                 Semantic Layer

                                     AJIs             Teradata
                                    AJIs          Aggregate Designer
IBM Cognos with Teradata ROLAP
Leveraging Teradata Aggregate Designer

                                                        • Teradata Aggregate Designer automates the
                                                          design and deployment of AJIs for BI Reporting,


                                                          Dashboards, ROLAP, and MOLAP solutions

                                                        1. BI Admin creates and manages OLAP schema in
                            Portal                         the Cognos Framework Manager.
                           MetaData                     2. Teradata Aggregate Designer consumes model.xml.
                                                        3. Teradata Aggregate Designer validates database is
                                                           suitable for Aggregate Join Indexes. Recommends
                         Cognos Server
                                                           and creates AJIs.

        Optimized SQL

                                               AJIs       Teradata
                                          AJIs        Aggregate Designer
Teradata Aggregate Designer Demo
Teradata OLAP Connector
Excel to Teradata Connectivity

                                                              Teradata OLAP


1.   Open a new Excel workbook
2.   Establish data connection to Teradata Database via Teradata OLAP Connector
3.   Select the project (catalog), and then select the cube
4.   Use Excel’s PivotTables to navigate/view the data – Teradata OLAP Connector converts
     PivotTable requests into optimized Teradata SQL
Teradata OLAP Connector Demo
Implementation Considerations
Implementation Approach

• Moving from MOLAP to ROLAP requires planning
  and changes to the BI application
• Customers should balance the work to be done
  to migrate from MOLAP against the
  added benefits of ROLAP

• Recommendations
  1. Start with a “ROLAP PoC” to validate the approach
     – Typically the PoC is completed in a short period of time and with a
       small budget so make sure you select an appropriate cube to model
     – The PoC should focus on the ROLAP solution itself and not the BI
     – Consider using Excel PivoTables to demonstrate the navigation and
       reporting of the ROLAP implementation
  2. Once the “ROLAP PoC” has been completed then plan for wider
Data Loading Considerations

• Teradata Multiload does not support AJIs, following are
  the different load scenarios to consider:
  > Multiload into normalized tables, and then build AJIs from
    the normalized tables
  > Teradata Tpump into normalized tables
  > Teradata Fastload into staging tables, and then insert select
    into the base tables
Teradata Database Considerations (1 of 2)

• We recommend you start with star and snowflake models
• All primary and foreign keys are defined as not nullable
• All primary and foreign keys are not compressible.
• All dimension table primary keys are defined as unique
• Ensure all primary and foreign keys are on ID not Name or
  Description columns. This will result in a smaller AJI which
  means faster access.
• Ensure all measures in the Fact table are set with Float
  datatype. Otherwise, overflow may occur on some large
  calculated measures
• Teradata Release 12 or later
Teradata Database Considerations (2 of 2)

• Recommended Fact Table design is a 'wide' (i.e. columns for each
  dimension and measure)
• Single level dimensions need supporting reference/lookup/dimension
  table for optimal performance
• Collect statistics on all primary key/foreign key relationship columns
• Implement Referential Integrity (RI) on the primary key/ foreign key
  columns. RI can be defined with the check (a.k.a., hard RI) or no
  check option (a.k.a., soft RI).
   > ORGS: Business Unit -> Division -> Area -> Sales Center
   > ALTER TABLE FACT ADD foreign key (SALES, CENTER_ID) references with
     no check option SALE_CENTER (SALES_CENTER_ID)
• To address the lowest levels in your analytics (i.e. your FACT) that
  are not in your AJI, consider the following:
   > Secondary Indexes
   > Partition Primary Index
   Performance and Capacity Assessments
   and Services
• Data Collection and Reporting – Collect
  and analyze key performance metrics
• Workload Management – Deliver maximum
  throughput of mixed workloads based                         Data
  on query mix and business priorities
• Application Performance – Identify target                Management
  applications for performance improvements and
  tune those applications
• Performance Assessment – Quick hit service desinged to analyze and
  identify opportunities for performance improvements
• Capacity Management – Understanding growth requirements and
  future needs based on application growth, performance requirements,
  and user sophistication
• Viewpoint Advisor Service – Best practices implementation of
  Viewpoint Administration and Portlet use
• Performance Management Foundation – Foundational Service
  Designed around Making the Customer’s Performance Team self
  sufficient in all these areas

To top