X

The Oracle BI Application Blogs provides the latest and tips and tricks on Oracle BI Applications Product (OBIA)

Try Oracle Cloud Platform For Free

BI Warehouse - Analytics enabled by warehouse

Saurabh Verma

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

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content