OBIEE Joins

Dennis Hancy

Table "A" and Table "B" are joined at the physical layer by an ID field.

Table "A" is the primary table and has a larger population of ID values.

Table "B" is a secondary table, and contains a subset of the ID values.

In the Business Layer, these tables are connected via a Logical Join as follows:

- The diagram looks like this: Table "B" --> "Table "A"

- Properties: Table "A" on the left and Table "B" on the right

- Driving Table: None

- Type: Left Outer

- Cardinality: "0,1" for table "A", and "0,1" for table "B"

When we run this, we are only seeing records where the ID fields match on both tables.  We need to see all records in table "A" even if they do not match anything in table "B".

A few thoughts about this...

- We've tried multiple combinations of changing properties and Join Types and such, but we're still experiencing the same issue.

- Also, when we run this SQL directly on the database, we are not seeing anything in the where clause that would indicate a "left outer" join.  If we modify that on the database side to include a left outer join, it all works as expected.

- After much trial and error (mostly "error" at this point), can anyone spot what we're doing wrong?

- Also, can anyone suggest a good resource (online or even a hard copy reference manual) that explains some of this theory? 

- One of our questions is, "What is meant by '0,1' cardinality?"  This is one of many questions that has come up since we started researching this issue.  Basically we're just looking for a guide to take some of the mystery out of this, and eliminate a lot of the guess work we've been doing.

Thanks!

Dennis

Robert Angel

Hi,

in business terms what are A and B; -

a. Both dimension tables in a snowflake relationship

b. Both fact tables

c. Fact B and Dimension A

d. Fact A and Dimension B

Also, is this the entire picture, you are testing this without filters and without joins to other tables?

Finally, you say; -

Table "B" --> "Table "A"

For me this implies B is either the Fact or B is the inner part of a snowflake (closest table to the fact table), can you confirm?

Dennis Hancy

I'll try to answer this as best I can.

Table "A" is a dimension table.

Table "B" appears to be a dimension table as well (given there is no # symbol next to it),  It contains additional information that we want to look up through the ID field.

I am still wrestling with some of these basic concepts such as dimension and fact tables, so that is certainly contributing to my lack of understanding how all this works.

When I look at the Business Model Diagram, there is one more table (a Fact table) that is also directly joined to Table "A".

Maybe this will shed some more light on the setup.

Table "B" --> Table "A" (Dimension) <-- Table "A"(Fact)

Hopefully this answers your questions.  If not, let me know, and I'll do some more research.

Thanks!

Dennis

Robert Angel

Okay,

two ways to deal with this.

1. Use the table as a lookup table, and model it as a sparse relationship, see ->

https://www.rittmanmead.com/blog/2010/08/oracle-bi-ee-11g-lookup-tables-sparse-and-dense-lookups/

2. Model the snowflake structure by joining the additional table to the main table by dragging it onto the logical table source driving table and setting the outer join relationship

https://gerardnico.com/olap/dimensional_modeling/snowflake

The best way would be to revisit your ETL and add the data into a single dimension table so the relationship is just fact to dimension.

Robert Angel

Sorry, overlooked your arrows, this is wrong!!!

Table "B" --> Table "A" (Dimension) <-- Table "A"(Fact)

It should be Fact -> A -> B in the physical

And Fact -> A (with B subsumed via the logical join - see my 2 above) in the business model layer

Dennis Hancy

Did you mean to say "Fact -> A -> B in the logical"?  This could be just my ignorance showing again, but we don't have any fact tables in the physical layer.  So far, we've only seen fact table at the logical layers.

Anyway, if you did mean "logical", we did have (Fact -> A -> originally.  However, as we continue our trial and error approach, we switched the arrows this morning.  Unfortunately, we still saw the same results.

I appreciate the time you've been taking to help explain all this to me

Thanks.

Dennis

Thomas Dodds

The short of it is:

FACT -> A -> B is physical snowflake

OBIEE requires a logical star  lFACT -> lDim (A +

Robert Angel

If you have only seen facts in the logical layer then it sounds like you are modelling 3NF.

In the case of modelling 3NF the rule is that you model aliased tables to represent the physical tables, joining to represent the nature of the joins in the physical.

When you model in the logical layer you turn the messy world of 3NF into the simple world of star schemas, using the techniques I described to flatten snowflakes into star structures.

Typically I would do this by creating a container for the fact first, dragging the values that are to be measures from the physical to logical and then adding the dimension tables one at a time in a similar fashion, modelling the logical joins as previously described.

As a newbie validate and save often is the extra rule of the day!

And no, I did mean physical, OBIEE is best suited to modelled star schema / DW so I had assumed that was your starting point in the absence of any other information.

Hope this clarifies?

I would recommend working your way through some of the 'by example' materials as a minimum, or better yet a training course from a reputable Oracle Partner, like Peak Indicators.