Microsoft Business Intelligence for Teradata. General Manager SQL Server BI

Microsoft Business Intelligence for

Teradata

Tom Casey

Todd Walter

Microsoft

Teradata R&D CTO

General Manager

SQL Server BI

Microsoft/Teradata Collaboration

•

Microsoft and Teradata are collaborating to

deliver business intelligence solutions

•

New Microsoft BI Applications accessing Teradata

Leave the data where it rests

•

Let Teradata do the heavy lifting

•

Customers benefit from the partnership

•

Teradata will extend the Active Enterprise reach and

access by injecting intelligence in all decisions

•

Microsoft will deliver the new

BI applications to all employees

in People Ready enterprise





Complete and integrated BI and 

Complete

and

integrated

BI

and

Performance

Management

offering

Performance Management offering 





Widespread delivery of business intelligence 

Widespread

delivery

of

business

intelligence

through

Microsoft

Office

through Microsoft Office 



Real

‐

time

insight

Familiar

tools

Decisions

at

all

levels

Secure

Reliable

Manageable

Embedded

BI

Common

Tools

Consistent

practices

END USER TOOLS & PERFORMANCE MANAGEMENT APPS

Excel

Excel

PerformancePoint

PerformancePoint

Server

Server

BI PLATFORM SQL Server SQL Server Reporting Services Reporting Services SQL Server SQL Server Analysis Services Analysis Services SQL Server DBMS SQL Server DBMS

SQL Server Integration Services

SQL Server Integration Services

SharePoint Server

SharePoint Server

Reports

Reports DashboardsDashboards Excel Excel

Workbooks

END USER TOOLS & PERFORMANCE MANAGEMENT APPS

Excel

Excel

PerformancePoint

PerformancePoint

Server

Server

BI PLATFORM SQL Server SQL Server Reporting Services Reporting Services SQL Server SQL Server Analysis Services Analysis Services SQL Server DBMS SQL Server DBMS

SQL Server Integration Services

SQL Server Integration Services

SharePoint Server

SharePoint Server

Reports

Reports DashboardsDashboards Excel Excel

Workbooks

o

Extend

the

reach

of

Teradata

solutions

to

more

users

through

collaboration

on

optimized

SQL

Server

Analysis

Services

adapter

and

cartridge

for Teradata.

PERFORMANCE MANAGEMENT & COLLABORATION

SharePoint

SharePointServerServer

CLIENT

Reports

Reports DashboardsDashboards_{WorkbooksWorkbooks}Excel Excel AnalyticAnalytic_ViewsViews ScorecardsScorecards PlansPlans

PerformancePoint PerformancePoint Server Server SQL Server SQL Server Analysis Services Analysis Services • ADO.NET or OLE‐DB Provider for Teradata • SQL Server Analysis Services cartridge for  Teradata helps translate MDX expressions  for MOLAP and ROLAP access to Teradata •SQL Server Analysis Services  enables direct  and seamless integration for content delivery  and navigation using familiar tools •Make use of Reporting Services and Excel via  Analysis Services integration

Analyze information with powerful and familiar tools

– New Office Excel Pivot Tables for analyzing information using business terms

– Tight integration between Office Excel and Microsoft SQL Server Analysis Services – Data Mining add-ins for advanced analysis and new visualization capabilities

Enabling end-users with ad hoc reporting capabilities

– Report Builder for ad hoc reporting of relational or OLAP data – Microsoft SQL Server Reporting Services for personalized

views of management data

Share reports and analysis over the web

– Excel Services for web based viewing of analysis – Scorecards and dashboards for quickly

disseminating information

Conditional

Conditional

Formatting

Formatting

Spot trends and

Spot trends and

exceptions in exceptions in your data to your data to better inform better inform your decisions your decisions Data Data Connection Connection Library Library Quickly access Quickly access the most the most common data common data sources in your sources in your enterprise for enterprise for data analysis data analysis Rich Rich Interactivity Interactivity Analyze data Analyze data with new with new PivotTable tools PivotTable tools and enhanced and enhanced support for SQL support for SQL Server Analysis Server Analysis Services Services Excel Excel Services Services Enhanced Enhanced Sorting and Sorting and Filtering Filtering

Sort by cell color

Sort by cell color

or filter data by

or filter data by

quarter to work

quarter to work

with only the

with only the

data you need

Report Report Authoring Authoring Visually design Visually design reports & reports &

layout via the

layout via the

Report

Report

Designer

Designer

End user report

End user report

creation

creation

Enable end users

Enable end users

to create their

to create their

own Ad

own Ad--hoc hoc

reports via the

reports via the

Report Builder Report Builder Report Report Management Management Manage report Manage report categorization, categorization, data sources, data sources, security, security, subscriptions subscriptions

via the Report

via the Report

Manager Manager Model design Model design Design Design semantic semantic models that models that map to real map to real business business

entities via the

entities via the

Model

Model

Designer

SQL Server 2005

SQL Server 2005

Analysis Services

Analysis Services

Multi-dimensional (MDX) EngineMulti

Multi--dimensional (MDX) Enginedimensional (MDX) Engine

Data Mining (DMX) EngineData Mining (DMX) Engine

Data Mining (DMX) Engine

Storage EngineStorage Engine

Storage Engine KPI Engine KPI Engine KPI Engine MOLAP MOLAP MOLAP Analysis S e rvices M a nagem ent O b je c ts Analysis S e rvices M a nagem ent O b je c ts XML for An alysis (XML/A) XML for An alysis (XML/A)

UNIFIEDUNIFIED  DIMENSIONALDIMENSIONAL  MODELMODEL

UNIFIED DIMENSIONAL MODEL

ROLAP

ROLAP

ROLAP HOLAPHOLAPHOLAP

Local  cube Local  Local  cube cube

Teradata .NET Provider

CartridgeCartridge Cartridge CartridgeCartridge Cartridge CartridgeCartridge Cartridge Cartridge DriverDriver XML/A || O D BO XML/A || O D BO DDL _XML/A (MDX)

Microsoft

SQL

Server

Client

applications

Developer

&

Admin

Tools

Improve corporate performance

– Scorecards to orient measurement to corporate goals

– Single model to align planning and analysis to the business process – Span the organization with planning and measurement capabilities

Accelerate management decision making

– Office Performance Point Server to integrate scorecarding, analysis and planning – Integrated workflow for simpler consolidation and reporting

– Common model for planning used in analysis

Enable collaborative analysis

– Integration with Office Excel for broad deployment of analysis capabilities

– Personalized scorecards and KPIs for individual views – Collaborative environment with context for analysis

Planning & Planning & Budgeting Budgeting Plan, budget, Plan, budget, forecast at forecast at corporate, corporate, divisional, and divisional, and operational operational

levels with visual

levels with visual

modeling, modeling, Scorecards Scorecards Cascading Cascading scorecards with scorecards with interactive charts interactive charts

and data from

and data from

multiple sources multiple sources Analytics Analytics Multi‐dimensional  slice and dice for  advanced analytics  including  decomposition tree,  performance map,  and perspective view Consolidation Consolidation & Reporting & Reporting Multiple GL data Multiple GL data integration including integration including pre

pre--built integration built integration

with Dynamics AX,

with Dynamics AX,

intercompany intercompany eliminations , eliminations , reconciliations, reconciliations, multiple currency multiple currency translations, and translations, and

o

Cost

effective

and

efficient

integration

for

TERADATA

via

high

performance

load/unload

through

collaboration

on

optimized

SQL

Server

Integration

Services

Adapter

for

Teradata

PERFORMANCE MANAGEMENT & COLLABORATION

SharePoint

SharePointServerServer

CLIENT

Reports

Reports DashboardsDashboards_{WorkbooksWorkbooks}Excel Excel AnalyticAnalytic_ViewsViews ScorecardsScorecards PlansPlans

PerformancePoint PerformancePoint Server Server SQL Server SQL Server Analysis Services Analysis Services • Availability & release vehicle TBD • New provider using Teradata’s high  performance interfaces for use with SQL Server  Integration Services and Teradata • Support for Load and Unload using TPT •SSIS (ETL) tools integration to make  connectivity easier, lower cost of integration

New SSIS Adapter

SQL Server SQL Server Reporting Services Reporting Services

o

Extend

the

reach

of

Teradata

solutions

to

more

users

through

collaboration

on

optimized

SQL

Server

Reporting

Services

provider

for

Teradata.

PERFORMANCE MANAGEMENT & COLLABORATION

SharePoint

SharePointServerServer

CLIENT

Reports

Reports DashboardsDashboards_{WorkbooksWorkbooks}Excel Excel AnalyticAnalytic_ViewsViews ScorecardsScorecards PlansPlans

PerformancePoint PerformancePoint Server Server SQL Server SQL Server Analysis Services Analysis Services • Availability & release vehicle TBD • SQL Server Reporting Services options to  connect to and translate semantics for query  access to Teradata •SQL Server Reporting Services  enables direct  and seamless integration for content delivery  and collaboration using familiar tools

.NET Provider for Teradata

SQL Server SQL Server Reporting Services Reporting Services

Real

‐

time

insight

Familiar

tools

Decisions

at

all

levels

Secure

Available

Manageable

Embedded

BI

Common

Tools

Consistent

practices

•

History

of

innovation

&

partnership

•

Shared

goals

to

bring

insights

to

all

employees

Teradata Optimized Implementation

For Microsoft BI Solutions

Business Case

•

Scenario:

•

Customer has some type of OLAP environment

Export data from Teradata (i.e. MOLAP)

•

Requirements:

•

Continue to leverage current investment in Microsoft

More ADW focus (i.e. reduce data latency)

•

Deeper Analysis (i.e. more dimensions and history)

•

Challenges:

•

Long cube builds

•

Limited depth of analysis

Week/Month old data

OLAP Implementation Techniques

Executive Power Users Analyst

Physical Cube (MOLAP) Multi-Dimensional View

• View data from multiple

dimensions

• Summarized and detailed

view of the data

OLAP Implementations

• Data

• Aggregation

Teradata Database

• Detailed data to build

physical cubes

• Parallel engine to power

Virtual Cube (ROLAP) Hybrid Approach (HOLAP) CUSTOMER CUSTOMER NUMBER CUSTOMER NAME CUSTOMER CITY CUSTOMER POST CUSTOMER ST CUSTOMER ADDR CUSTOMER PHONE CUSTOMER FAX ORDER ORDER NUMBER ORDER DATE STATUS

ORDER ITEM BACKORDERED QUANTITY

ITEM ITEM NUMBER QUANTITY ORDER ITEM SHIPPED

QUANTITY SHIP DATE

MOLAP vs. ROLAP

+ Sub-second

- Scalability

- Data latency

- Cost

+ Sub-second

- Scalability

- Data latency

- Cost

MOLAP

MOLAP

+ Scalability

+ No data loading

+ Drill down/through

- Sub-second

+ Scalability

+ No data loading

+ Drill down/through

Solution

•

Implement a ROLAP solution with Teradata

•

Use Microsoft Business Intelligence Development

Studio modeling tool to design Analysis Services

analytic

•

Theoretically, no change in what is being analyzed, just where

and when it is being done.

•

Teradata Aggregate Join Index (AJI) Feature

•

Customer understands the query performance trade-off that

comes with ROLAP (versus MOLAP) storage. Hence, to

assist in attaining the best possible performance, we will use

Teradata’s Aggregate Join Index (AJI) feature.

Environment Considerations

•

EDW Design

•

Adhere to the practices and methodologies to best support an

enterprise data warehousing environment for the customer.

•

Independent of any tool or analytic requirement in delivering

BI content.

•

Semantic Layer

•

To date, customers are creating one database to hold their

analytic specific data content in a star/snowflake schema in

which Teradata AJIs are implemented

•

Second view database to expose to the tool and users.

•

Proposed AJI

•

When evaluating an AJI for a ROLAP solution, we are

proposing to create a ‘broad’ AJI that references the

dimensions and levels except the very lowest level(s).

Dimension Map and AJI Strategy

Time Product Brands Business Channel Org

Year Product Category Brand Category Business Type Channel Type Business Unit Quarter Product Brand Division

Month Area Day Sales Center

•

Dimension

Map

•

Aggregate

Join Index

Database Considerations (1 of 2)

Physical database design to be implemented in a semantic layer database (i.e. analytic specific) as discussed in the previous slide MUST adhere to the following stipulations:

• Snowflake/Star model is currently recommended.

• All primary and foreign keys are defined as not nullable.

• All primary and foreign keys are not compressable.

• All dimension table primary keys are defined as unique utilizing the UNIQUE constraint or the primary key is defined as an

UNIQUE PRIMARY INDEX.

• Ensure there is a 1-M relationship between tables or levels that define your dimension.

Database Considerations (2 of 2)

• 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.

• 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 / join relationship columns.

• Implement referential integrity on the primary key/ foreign key

columns. The referential integrity can be defined with the check (i.e. hard RI) or no check option (i.e. soft RI).

Aggregate Join Index Definition

CREATE JOIN INDEX AJI_Example ,NO FALLBACK ,CHECKSUM = DEFAULT AS SELECT COUNT(*)(FLOAT, NAMED CountStar ),

ae.Brand_Category_Id , ac.Product_Category_Id , ad.Business_Type_Id , ad.Channel_Id , ak.Area_Id , al.Year , al.Quarter , al.Month ,

SUM(ad.Sales )(FLOAT, NAMED SALES ) FROM Product ac , Fact ad , Brand ae , Sales_Center ak , Time al WHERE

(((ad.product_id = ac.product_id ) AND (ad.brand_id = ae.brand_id )) AND

(ad.sale_center_id = ak.sale_center_id )) AND (ad.day = al.day )

GROUP BY ae.Brand_Category_Id, ac.Product_Category_Id, ad.Business_Type_Id, ad.Channel_Id, ak.Area_Id, al.Year, al.Quarter, al.Month

PRIMARY INDEX ( Brand_Category_Id, Product_Category_Id, Business_Type_Id, Channel_Id, Area_Id, Year, Quarter, Month );

Conclusion

•

Summary

• Aggregate Join indexes can greatly improve query performance

• Easy to define

• Network traffic reduced

• Cube build times minimized

• Built in parallel

• Requires Teradata Database V2R5.1

•

Benefits

• Indexes created can be relatively small structures

• Dependent upon number of demographics rather than number of rows in

Fact/base table(s)

• More in line with an Active Data Warehouse as opposed to MOLAP

• AJIs can be shared by multiple cube definitions

• Can create broader and deeper cubes

• More dimensions, more categories, more member

Next Steps

To address lowest (Fact) level/columns in your OLAP designs, consider the following:

• Secondary Indexes (SI) – Secondary indexes provide faster set selection. Secondary indexes are frequently selected by the

Optimizer when a search condition cannot be satisfied with a primary index retrieval. The Optimizer also selects secondary indexes for query plans when they completely or partially cover a query.

• Partitioned Primary Index (PPI) on date column – Are designed to optimize range queries while also providing efficient primary index join strategies. Analyze your range query optimization needs carefully

because there are performance tradeoffs between specific range query enhancements and possible decrements for primary index

accesses and joins and aggregations on the primary index that occur as a function of the number of active partitions.

SQL Server Analysis Services to Teradata

.NET Data Provider or OLE-DB SQL Server 2005 Analysis Services Teradata Cartridge SQL Server 2005 Reporting Services CRM Customer Supplier Business Intelligence Teradata Cartridge requires .NET Data Provider

Microsoft/Teradata Roadmap

SQL Server 2005

Integration Services TPT

Data Provider for Teradata

OLE-DB or .NET Data Provider ETL SQL Server 2005 Analysis Services Teradata Cartridge * SQL Server 2005 Reporting Services CRM Customer Supplier Business Intelligence Metadata √ √ 08H1 TBD

Joint Solution Components

• Teradata

• Teradata database (V2R6.0.1 or higher)

• OLE-DB Provider for Teradata (1.3 or higher) – included w/TTU

• Only available for 32-bit environments

• .NET Data Provider for Teradata (1.1 or higher) – included w/TTU

• Available for both 32-bit and 64-bit environments

• This is the recommended connectivity interface instead of OLE-DB

• Microsoft

• Microsoft SQL Server 2005 Enterprise Edition (Service Pack 2)

• SQL Server 2005 Analysis Services (SSAS) – OLAP implementation • SQL Server 2005 Reporting Services (SSRS) – Reporting

• SQL Server 2005 Integration Services (SSIS) – ETL

• Microsoft Visual Studio 2005 – development tool

• Microsoft Business Intelligence Development Studio (BIDS) - additional

Partnership Benefits

•

The joint Microsoft and Teradata BI solutions will

enable customers to:

•

Leave the data where it resides

•

Allow Teradata to do the "heavy lifting“

•

Deploy Microsoft BI solutions for the masses

•

Key benefits for the joint solution :

•

Increasing the breadth and depth of data access

Improving analytics response times

•

Reducing cost & data latency with fast, more frequent

loads

•

Eliminating cube scalability limitations

Lowering Total Cost of Ownership