Challenges in Modeling 3NF Sources in the RPD

[Mark Rittman]

Hi All

OK, here's another one. I'm pulling together a conference presentation
on the challenges in modeling sources that aren't in a star schema,
i.e. 3NF sources.

From my perspective, some of these challenges include:

- source tables may contain both fact and dimension data (solution -
alias the tables in the physical layer, or map them in twice in the
BMM)
- source tables may contain self-joins, i.e. a hierarchy (solution -
either alias the table to create a second one, to join to, or if it's
a hierarchy, consider using a value-based hierarchy if on 11g)
- source data may be in several tables, rather than the one
denormalized table (solution - map in additional physical tables into
the LTS, using FK relationships)
- transaction data may not be neatly stored all at the same grain
(solution - use a transaction hierarchy, with transaction line and
transaction header as levels)
- transaction data may be fast-moving, stopping us using caching

Has anyone else hit any other specific RPD modeling issues, and come
up with solutions, for dealing with source data that's in a
transactional, 3NF schema? If so, share them with the group.

Mark

[Robert Tooker]

The only thing I'd add is that my preferred way for point 1 is to map twice in the BMM layer, since aliasing in the physical layer will guarantee you a self join which is likely to be very costly on a transactional table. Regards,

Robert

--
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to
obiee-enterprise-met...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en

All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/).  Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

[Gerard Braat]

Hi Mark, In my opinion 3NF transactional models and dimensional models are not that far apart.
It is a matter of being able to understand the 3NF model first and look for the transactional entities which are the candidates for sourcing the Logical Fact.
The tables joining directly to transactional entities are called component entities and form candidate dimensions. All other tables are classification entities.

Then the final step to wards modeling is the ability to look to the model and collect all tables which can be joined together safely without hitting pitfalls like fantraps, chasm traps. This set of tables is what I call a join-path-context and has been discussed in one my blogposts on ittoolbox on understanding the BI Server.

This concept to be able to read a 3NF as a dimensional model is scientific and has been written down in two papers from Moody and Kortink (From ER to Dimensional Models). THis is exactly what we are trying to achieve in OBIEE.

The challenge is of course still how to model this correctly in OBIEE.

I would encourage everybody to read the two papers I mentioned.

http://www.google.com/url?sa=t&source=web&cd=1&ved=0CCAQFjAA&url=http%3A%2F%2Fciteseerx.ist.psu.edu%2Fviewdoc%2Fdownload%3Fdoi%3D10.1.1.33.511%26rep%3Drep1%26type%3Dpdf&rct=j&q=moody%20kortink&ei=CjZ6TuLFLcyr-gbr8IFR&usg=AFQjCNHah6izZXVxXr_gAGXA-DR-mSNszA&sig2=tPzHwK7-5w_8CLTR5645WA&cad=rja

http://www.google.com/url?sa=t&source=web&cd=3&ved=0CDMQFjAC&url=http%3A%2F%2Fwww.business-intelligence-quotient.com%2Fwp-content%2Fuploads%2F2010%2F10%2FFrom-ER-Models-to-Dimensional-Models-Part-II.pdf&rct=j&q=moody%20kortink&ei=CjZ6TuLFLcyr-gbr8IFR&usg=AFQjCNGXCYXtcHiBZuKhfoY5hRepRXj1CA&sig2=apfEO8xXbU6VgbvY4BAIiQ&cad=rja

Gerard

[Jit Dutta]

Have worked with a custom DW that was really 3NF. Most of the issues mentioned before were encountered with messy joins involving a bunch of normalized tables in the LTS. It worked but with large data volume OBIEE 10g dashboard performance became an issue. Had to build additional MVs that really were more of a star schema format, remodel the LTS and that resolved most of the performance issues.

Thanks.

> Date: Wed, 21 Sep 2011 08:08:17 -0700
> Subject: [OBIEE EMG] Challenges in Modeling 3NF Sources in the RPD
> From: mark.r...@rittmanmead.com
> To: obiee-enterpri...@googlegroups.com

[Richard Chan]

Hi Mark,

I've taken the approach of performance tuning and modeling at the same time by creating SQL views in the Physical layer, this is only really apporopriate where the reporting requirements are clearly defined with no requirement for ad-hoc analysis as some of the SQL code combined a number of tables that a number of reports used, no point in using this for a report or analysis that only used one of the tables due to the join overhead

 

Richard

[andrea.carme]

Other modeling challenges could be:
- Many-to-many relationships between tables which will act as
dimension (solution: model the dimension LTS with the 2 tables and the
bridge table).
- Transactional fact table with no numerical columns (solution: create
measures with count or count distinct aggregation)
- Transactional fact tables with one-to-one relationship (solution:
map the two tables in the same fact LTS)

Andrea

On 21 Set, 22:56, Jit Dutta <jdutta...@hotmail.com> wrote:
> Have worked with a custom DW that was really 3NF. Most of the issues mentioned before were encountered with messy joins involving a bunch of normalized tables in the LTS. It worked but with large data volume OBIEE 10g dashboard performance became an issue. Had to build additional MVs that really were more of a star schema format, remodel the LTS and that resolved most of the performance issues.
>
> Thanks.
>
>
>
>
>
>
>
> > Date: Wed, 21 Sep 2011 08:08:17 -0700
> > Subject: [OBIEE EMG] Challenges in Modeling 3NF Sources in the RPD

> > From: mark.ritt...@rittmanmead.com

[Michael Wilcke]

Mark,

I Know a (Teradata) case, where Aggregate-Rows were included in the
(one and only) fact-table. Of course, there was one column indicating
on which Level in "hierarchy" that Row was calculated. Use the where-
clause in LTS content tab to Filter detail and Aggregate fact
table(s).

Regs - Michael

[Sachin Jain, CPHIMS]

One should stay away from creating/defining  views in physical layer. This is OK for Proof-Of-Concept modeling, but eventually you have to move these cubes down to the Database. What we have seen in the past is

• Difficulty debugging the resulting query
• Difficulty optimizing the reports (DBA's see the inline queries generated by OBIEE and freak out)

Sachin Jain
214 697 2238