RPD Data Model Design

manyam16

Hello,

We are using relational tables to create OBIEE reports.  I would like to know the solution how to connect in Physical Layer, BMM for the following situation.

I have 7 tables in my criteria to generate report.  .

Table 1, Table 2, Table 3, Table 4, Table 5, Table 6 and Table 7.

Table 1 is considered as fact and other tables are just dimension tables with relations to other tables.

The joins are not sequential, for example table 1 and table 2 are joined and also table 1 and table 3 are joined and also table 3 and table 5 are joined.

Anybody, please throw some light on this how to join in physical layer and BMM.

Thank you,

Gianni Ceresa

Hi,

Quite vague question ...

The key element is to keep in mind what layers means in OBIEE.

Physical layer represent the reality of your physical sources. In your case there you will have your 7 tables with all the required links. Do not think fact or dimension here, just link things based on what your data require (so table1 to table2, table1 to table3, table3 to table5 etc.).

You can perfectly have a snowflake or any other kind of form here.

The BMM layer is where you define the logical model: forget about your sources, forget about the physical links. Here things must be (not an option) star schema. You can have multiple stars inside a single model (so having conformed dimensions) but everything is a star.

In the BMM you do not care anymore on how things looks like in the physical layer, you just define facts and dimensions. If you have a snowflake (so a dim table joining to a dim table joining to a fact) you can simply add the 2 dim tables into a single logical table source so that you can access any attribute of both tables etc.) OBIEE will then try to be smart enough to find a way to use the physical layer model to generate queries answering the requests based on your logical model.

TL;DR : just define all the links, that's it.

Maybe a screenshot and a more concrete example than table1, table2 etc. will be more helpful if you have a precise question.

Christian Berg-0racle

Also: just draw a picture. An diagram with your entities and their relations will basically tell you all you need to know.

Never underestimate how powerful a sheet of paper and a pencil can be.

manyam16

Thank you Very much for the response and sorry for the delay in getting back to you.  Please find the attached.

I already have other physical tables and data models, but for my requirement, i need to update with the new columns and hence adding new tables.  I have attached the screen shot.  can you please suggest how to build the physical and logical models especially when i have the overlapping joins from one table to other table without that my query does not give me the required results. 

Thank you,

Christian Berg-0racle

that's what aliases are for. physically it may look like this. now it's up to you to nicely put that into a snoflakw

Thomas Dodds

remove the circular joins ...

manyam16

Hello,

I was able to create the above model in the rpd.  All joins are working except the join between table 1 and table 5 is not populating into the query.  all other joins are populating working.  I cleared the cache several times and logged off and logged in the system.  That particular join is not coming in the query and hence my results are OFF.  Can somebody help, appreciate.

Christian Berg-0racle

You created those joins including the circular relationships and didn't resolve things into a nice snowflake? So you did neither of the things we said?

Well guess what.....you should have listened

manyam16

Thank you for the response, I did resolve most of them into a snowflake.  But there is one join i have to do as I said above which I did and did not throw any circular joins error.  I thought it would pick up that join in the report, but it did not.  Hence, my results are not correct.  can you please suggest.

Terna

I think, you need to create 2 logical tables in BMM layer:

1. Fact (table source is Table 1)

2. Dimension (table source is table 2 join table3 join table 4 join table 5 join table 6 join table 7)

Then create link between these tables in business model diagram.

Christian Berg-0racle

manyam16 wrote: can you please suggest.

Yes: post your actual model with the physical entities and joins nad also the BMM ones.