obiee buenas practicas

8/9/2019 OBIEE buenas practicas

1/50

T : +44 (0) 8446 697 995 E : [email protected] W: www.rittmanmead.com

Data Modeling Techniques Using Oracle BI ServerMark Rittman, Director, Rittman Mead ConsultingOracle Open World 2008, San Francisco. Session #S300202
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]


2/50


3/50


Who Am I?

Oracle BI&W Architecture and Development Specialist

Co-Founder of Rittman Mead Consulting

Oracle BI&W Project Delivery Specialists

10+ years with Discoverer, OWB etc

Oracle ACE Director, ACE of the Year 2005Writer for OTN and Oracle Magazine

Longest-running Oracle blog

http://www.rittmanmead.com/blog

Chair of UKOUG BIRT SIG

Co-Chair of ODTUG BI&DW SIG

Speaker at IOUG and BIWA events
mailto:[email protected]://www.rittmanmead.com/bloghttp://www.rittmanmead.com/bloghttp://www.rittmanmead.com/bloghttp://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]


4/50


Rittman Mead Consulting

Oracle BI&DW Project Specialists

Consulting, Training, Support

Works with you to ensure OBIEE andOracle BI Applications project success

Small, focused team

OWB, Oracle BI, DW technicalspecialists

Clients in the UK, Europe, USA
http://www.rittmanmead.com/http://www.rittmanmead.com/


5/50


What is this Presentation About?

For performance and data integration reasons, most mature OBIEE systems sourcetheir data from one (ormore) data warehouses

For initial implementations though, data is often initially accessed directly

OBIEE has some powerful features for creating integrated virtual logical models

For bringing together data from disparate systems (federation) For integrating historic and real-time data (fragmentation)

For creating calculations and derivations

For handling multi-valued dimensions

OBIEE also requires that any normalized data is converted into a star schema

These data modeling techniques, coupled with more formal data warehouses, can

allow you to deploy very flexible, agile data models to support your reportingThis presentation will take you through some of these data modeling techniques and will

discuss how these initial models are migrated to a more formal data warehouse
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]


6/50


Oracle Business Intelligence Enterprise Edition
http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]


7/50


8/50


Data Access

Reporting data may be sourced frommultiple databases, applications

Oracle Database (including Oracle OLAP)

IBM DB2

Microsoft SQL Server Teradata

Microsoft Analysis Services (OLAP)

SAP BW 'info cubes'

SAP, Peoplesoft, Siebel, E-Business Suite

Oracle BI Server joins this source

data together and presents alogical business model

Facts

Dimensions


9/50


Common Enterprise Information Model

Enables consistency, security, reuse, flexibility

Role-based views of the organization

Single metadata model of the business

Consistent definition of business measures, metrics, calculations

Drill paths, summaries, securityModel once, deploy anywhere

Across any data source

Model First development


10/50


Data Modeling using Oracle BI Server

Design goal for the BI Server metadata layer is to create a Star Schema

Fact tables, Dimension tables

Drill paths

Source data can either be pre-integrated (data mart,

data warehouse) or integrated usingthe BI Server (federated)

Federated can be faster to deliver (report in place)

- Assumes data sources share common key values

Data marts and data warehouses are simpler and canbe faster to query

MDX (OLAP, Multi-dimensional) data is converted intoRDBMS row-sets

In this presentation, we will look at how you can use thedata modeling features of OBIEE to create logical star schemas


11/50


Sample Business Model


12/50


Data Modeling Workflow Steps

1.Define logical model in BI Administrator tool

2. Import physical data sources & define primary and foreign keys

3. Map logical model to physical model

4. Create initial calculations

5. Create dimensions6. Create initial presentation model

7. Create complex calculations, time dimensions etc

8. Add additional columns to presentation model


13/50


Data Modeling Scenario

Global Company has a data warehouse, containing order and customer data

Requirement is to take the existing warehouse (normalized)and turn it into a star schema (denormalized)

For now, OBIEE will be used to map the data


14/50


Step 1: Create Logical Layer

Firstly, create new logical model

Then create logical tables and columns

Either create from scratch, then connect to physical(best practice)

Or define by dragging and dropping physical tablesStart from fact table and work outward to the dimensions

Do one fact/dimension combination first

Then add more facts

... and more dimensions

Add details on datatypes etc later on


15/50


Logical Join Types

Logical tables are by default not joined

Logical layer supports both foreign keyand complex joins

Foreign key joins not recommended

Restrict choices that BI servercan make over join paths

Only there for backward compatibility

Complex joins are preferred

Indicates relationship betweentables

Allows BI Server to determinebest join path

- Useful when more than oneLogical Table Source


16/50


Step 2: Create Physical Layer

Define ODBC connection from client or server to database

ODBC only needed to define initial connection, import metadata

Native connection (OCI etc) takes over from then

Import tables, views, keys etc from

data sourceDefine keys, foreign keys if needed

Update row counts

Define table aliases


17/50


Physical Join Types

If source data has foreign key joins, they will be imported

If not, they can be defined in the physical model

Two types of join

Foreign key joins (e.g. CUSTID = CUSTID)

- 1:N joins onlyComplex joins (TRAN_DATE between

STARTDATEID and ENDDATEID)

- also supports 1:1, O:1, 1:O etc

Important to create physical joinsbefore importing in to logical layer

Logical joins rely on physical joinsto define paths

Makes life a lot simpler

Create joins using Join Manager,


18/50


Step 3: Mapping the Logical Model to the Physical Model(s)

Once you have your logical model and one or more physical models, you can map themtogether

The simpler the mapping, the better

Drag and drop physical columnson to the logical model

Logical model will inheritphysical data types


19/50


Logical Table Sources

Logical tables can have more than one physical source

Can create >1 Logical Table Source

BI Server creates two physical queries

Useful when mapping data from

different levels and when using fragmentation

Each Logical table source can have>1 table mapped

BI server creates one query per LTS, joiningas per physical foreign key joins

Used when you need columns from otherjoined tables in your logical table(join only happens if additionalcolumns requested)


20/50


Consider Where to Join Data

Again, consider where to integrate and join data

Whilst the BI Server can join data, it may be faster to load into a data mart

ETL tools can integrate data at time of load

Denormalized data structures can be faster than joining at report time

Indexing, Summary Management, Query OptimizationCreating a data warehouse still is the optimal solution

BIEE data source joining features are most appropriate when bringingnew data in thats not yet in the data warehouse


21/50


Step 4: Create Initial Calculations

Calculations are created as additional logical columns

Sit alongside existing logical columns derived from physical layer

Calculations can alsobe created within report


22/50


OBIEE SQL and the Expression Editor

Calculations within the repository are defined using the Expression Editor

Uses OBIEE SQL Syntax

Reference functions,expressions, logicalcolumns, constants,variables

Same SQL syntax acrossall supported databases

Ability to pass-throughfunction calls usingEVALUATE function


23/50


Step 5: Create Dimensions and Hierarchies

Dimensions define hierarchical relationships between columns

Also used when defining level-based measuresand summaries

Level-based, starts with Grand Total level

One-way export out to Oracle dimensions (DDL)also possible

Enables hyperlinks on columns withindimension levels

Drill-down through the hierarchy

No drill-up or drill to related (yet)


24/50


Step 6: Create Initial Presentation Models

Business models themselves are not visible to users

Users see presentation models whose columns aredefined in business models.

Appear as subject areasin the Oracle BI Answers

Appear as databases toclient tools using ODBC

Can create multiplePresentation Catalogs fromsingle Logical Model


25/50


Step 7: Creating Time Dimension and Time-Series Calculations

OBIEE 10.1.3.2 supports limited time-series functions

AGO value of a measure x months, quarters, years ago

AGO(Videostore."Sales fact view".Sales, Videostore.TimesDim."Month", 1)

TODATE value of a measure to the end of a month, quarter, year

AGO(Videostore."Sales fact view".Sales, Videostore.TimesDim."Quarter", 1)

Requires at least one time dimension in themetadata layer

Requires at least one or more Chronological keys

Monotonically increasing value (DAY etc)


26/50


DemonstrationCreating the Initial Common Enterprise Information Model


27/50


The Calculation Wizard

Wizard that generates share, percentage change etc calculations

Useful in conjunction withTime Dimension

% Change since last month

Change since last month etc

Index

Percent

Wraps ISNULL around calcs


28/50


Reporting Across Multiple Data Sources

OBIEE can combine and join data from multiple data sources

Data is retrieved in separate SQL, MDX calls and joined using the BI Server

Allows you to perform federated queries

Watch out for performance though

End goal should be to eventually move reporting data to a data warehouse


29/50


Federated Query Scenario

Additional data exists in an Excel spreadsheet

Contains quotas, plus information on products and dates

Quotas are at the product category and month level

Existing data is at the product and day level

Requirement is to join data, but only show quotas the relevant level


30/50


Joining A Second Data Source : Step 1

Import the new schema into the physical model, ensure FK and PKconstraints are set


31/50



Use the Join Manager, or Physical Model Diagrammer, to createjoins between tables in the two schemas


32/50



Add the new dimension tables as additional logical table sourcesto the exisiting logical dimension tables

Set the dimension hierarchy level at which theyare applicable


33/50



Add the additional measures to the fact table

Set the dimension levels at which the measures become relevant


34/50


DemonstrationCreating Federated Data Sources


35/50


Incorporating Real-Time Data Using Fragmentation

Current data source for OBIEE might be a data warehouse

Data loaded once a day, once a week or with some other time lag

You may wish to bring real-time data into the OBIEE environment

One option is to load this data into the warehouse in real-time

Another is to use the fragmentation feature to time-partition your measures


36/50


Fragmentation

Logical tables have >1 Logical Table Source

Logical Table Source 1 = Historical Data

Logical Table Source 2 = Real-Time Data


37/50


Advanced Data Modeling Issues

In real-life implementations, the source data may present some challenges

Physical to Logical Modeling Issues

Circular joins

Fan traps

Dimensional Modeling issues Fact to dimension many-to-many relationships

Ragged and unbalanced hierarchies


38/50


Physical to Logical Modeling Issues : Circular Joins

Data in your physical source system may use circular joins

Valid in an OLTP system, and in tools such as Discoverer

Causes issues in tools such as OBIEE as join path cannot be circular


39/50


Circular Joins Solution : Alias Tables

In this example, dimension information is held in three tables

Employees, departments, regions

Employees doubles-up as a table that provides details on regional managers as well

Solution is to create an alias for the employee table and join this to region instead


40/50


Fan Trap Queries

Caused when one master table (i.e. a dimension) joins to two fact tables (sales, budget)

You would expect the aggregations on each table to be correct, however regularSQL queries can over-count totals due to cartesian joins forming

Known in the Discoverer world as fan trap queries

SQL> select a.acc_name,2 sum(sales),3 sum(budget)4 from fan_trap_accounts a, fan_trap_sales s, fan_trap_budget b5 where a.acc_id = s.acc_id6 and a.acc_id = b.acc_id7 group by a.acc_name8 order by a.acc_name9 /

ACC_NAME SUM(SALES) SUM(BUDGET)-------------------- ---------- ----------- ACCOUNT1 900 1050

ACCOUNT2 130 200 ACCOUNT3 600 750 ACCOUNT4 600 600
mailto:[email protected]://www.rittmanmead.com/mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]


41/50


Automatic Fan Trap Query Resolution

The Oracle BI Server automatically detects fan trap queries, and generates twoseparate SQL queries which it combines at the BI Server level

Solves issue, functionally identical, but not quite as efficient as Discoverer

Two SQL queries vs. one, and join done in BI Server memory

-------------------- Sending query to database named ora11g (id: ):

select T6678.ACC_NAME as c1,sum(T6685.SALES) as c2

fromCUST_ORDER_HISTORY.FAN_TRAP_ACCOUNTS T6678,CUST_ORDER_HISTORY.FAN_TRAP_SALES T6685

where ( T6678.ACC_ID = T6685.ACC_ID )group by T6678.ACC_NAMEorder by c1

+++Administrator:2a0000:2a0001:----2008/08/26 17:24:48

-------------------- Sending query to database named ora11g (id: ):

select T6678.ACC_NAME as c1,sum(T6681.BUDGET) as c2

fromCUST_ORDER_HISTORY.FAN_TRAP_ACCOUNTS T6678,CUST_ORDER_HISTORY.FAN_TRAP_BUDGET T6681

where ( T6678.ACC_ID = T6681.ACC_ID )group by T6678.ACC_NAMEorder by c1


42/50


Bridge Tables

In some cases, there might exist a many-to-many relationship between a fact tableand a dimension table

Patient diagnoses, sales made by salespeople

In entity relationship modeling, you would resolvethis many-to-many with and intersection table

The intersection in dimensional modeling termsis called a bridge table


43/50


Bridge Tables and Dependent Dimensions

Marking a dimension table as a bridge table stops OBIEE thinking of it as a fact table

Any dimensions that join to it will not now join to the main fact table

To avoid metadata consistency issues, add the dimension table to the bridge LTSand then add any dimension columns to the bridge logical table

Remove the dimension table from the logical model, use the bridge table instead

Dimension data can then be accessed as required


44/50


DemonstrationUsing Bridge Tables


45/50


Ragged Hierarchies

Some hierarchies are unbalanced or ragged

Do not have the same number of levels downeach hierarchy path

Typically found in Organization, Sales Team hierarchies

Stored in the source system in a parent-child form

OBIEE requires level-based hierarchies

Three main methods of resolving this

Flatten the hierarchy

Use stack/descendent tables

- For full solution, see the URL below

Use Essbase as a data source

Organization

Mark Jon Pete Stan

James Bill Alex

Arthur

Sarah Phil

Darren

Stacey

Gavin

Sarah

http://www.rittmanmead.com/2007/06/21/obiee-data-modeling-tips-3-ragged-hierarchies/
mailto:[email protected]://www.rittmanmead.com/2007/06/21/obiee-data-modeling-tips-3-ragged-hierarchies/http://www.rittmanmead.com/2007/06/21/obiee-data-modeling-tips-3-ragged-hierarchies/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]


46/50


Flattening Ragged Hierarchies

OBIEE requires (in 10gR3 release) all hierarchies to be level-based

Most common solution then is to take the parent-child hierarchy, determine themaximum depth, and create a flattened version of it

Where a hierarchy path is missing descendants, next highest member is copied down

Works well but needs rebuilding if the hierarchy increases in depth

Essbase physical value-based and ragged hierarchies are supported, but are thenconverted to this flattened format


47/50


Summary

The data modeling features of OBIEE are a useful way to create an on-the-fly logicaldata model for your reporting

It is possible to turn a 3NF model into a star schema

Integrate data from multiple source (federation)

Integrate historic and real-time data (fragmentation)

Create calculations and report drill-paths

This is often a useful first step when prototyping your system

Over time, migrate the data you are reporting on to a formal data warehouse

Use the modeling features of the OBIEE suite to bring in data from all your sources


48/50


Further Reading

Other Rittman Mead sessions

#S300201 : Automating Business Decisions with Oracle Real-Time Decisions

#S300202 : Data Modeling Techniques using Oracle BI Server

#S301063 : Extending and Customizing the Oracle BI Applications Data Warehouse

#S301081 : ODTUG Essbase Symposium Part 3 : Oracle Business IntelligenceEnterprise Edition

Visit our website for presentations, papers,the Rittman Mead Blog

http://www.rittmanmead.com

Contact me at [email protected]

Thank you for attending, please dont forget tocomplete the online evaluation
mailto:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/http://www.rittmanmead.com/mailto:[email protected]:[email protected]


49/50


50/50


Data Modeling Techniques Using Oracle BI ServerMark Rittman, Director, Rittman Mead ConsultingOracle Open World 2008, San Francisco. Session #S300202

obiee buenas practicas

Documents