Monday Apr 22, 2013

BI Warehouse - Analytics enabled by warehouse

A lot of times, a question comes up as to what value a warehouse based solution brings. Below are a few use cases that exemplify the need for a persistent storage. Both real time and warehouse based analytics are complementary to each other and serve different use cases. In a follow up post we will also cover the use cases best served by real time and not warehouse based solution.

Feature

Description

Sample Use Case

Currency Conversion

4 corporate level and functional currency available in warehouse

Show my worldwide bookings for last week in US dollars

e.g. Bookings / Billings / Backlog report

UOM conversion

Common UOM reporting

Show me units shipped worldwide in Sales UOM for a product

Snapshots / Trend Reports

Balances / Snapshot tables providing trend reporting

Inventory Balance, Headcount Balance, Opportunity Revenue trend views

“What changed” report

Time Series Calculations

Enable usage of time series calculations

Year Ago, Qtr Ago, Month Ago

YTD, QTD, MTD etc, % Growth metrics

Event Interpretation

Assignment record having multiple transitions

# of hires, # of transfers, # of promotions, # of resignations require event interpretation and comparison to prior records

Hierarchies / SCD / As Is vs As Was reports

Report on various versions of hierarchies / historical views

Show me revenue numbers with current sales org over last 3 years

Also, performance is one of the key value of the warehouse which is due to the following transformations or services provided by the warehouse.

Feature

Description

Comments

Denormalization

Most Entities are denormalized in the warehouse

2 – 3 x lesser joins in queries

Symmetrical

All queries have same shape.

One pattern to tune

Normalized schemas result in unpredictable query shapes requiring case by case tuning

Indexing

All columns are (bitmap) indexed

Can be freely used in prompts, filters, group by etc

Outer Joins

OLTP tables have optional relationships

Large number of outer joins causing tuning and modeling issues

Class tables

Overloaded Tables containing multiple entities

Sometimes cause query plan distortions

Transpositions

Use column A or column B depending on transaction type

Require a case statement

Cannot be indexed / used in filters etc

Date arithmetic

Timestamp join to date joins require date functions

Suppress index usage

Aggregates

No aggregates in OLTP

Enable exalytics usage / other performance tweaks

About

Phil Wang-Oracle

Search

Categories
Archives
« April 2013 »
SunMonTueWedThuFriSat
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
23
24
25
26
27
28
29
30
    
       
Today