Query regarding physical joins

souvik88_570 · May 31, 2017 2:29AM

Hi,

In my RPD I have two physical tables - Tab1 and Tab2. Tab1 has two alias - AliasTab1A and AliasTab1B and these two alias are joined with AliasTab2, an alias of Tab2.

So, it is like - AliasTab1A --------------- AliasTab2 ---------------- AliasTab1B.

Now, there is a requirement to introduce a new table, e.g., AliasTab3, between AliasTab1A and AliasTab1B, without touching the existing joins with AliasTab2 as there are existing reports which use this path.

Is it a good practice to create the new joins with AliasTab3 using the existing alias of Tab1? If yes then is there a way to enforce OBIEE to follow the newly created path based on certain condition?

Or shall I create new set of alias for Tab1 and create join with AliasTab3 using them?

Please suggest.

[Deleted User] · May 31, 2017 2:47AM

Three words: Entity Relationship Diagram

a) You're not stating from where to where the joins are supposed to go

b) You're not stating cardianlities

c) You're not indicating what any of the three entities is supposed to be

d) You're not stating what the new one is supposed to be

Your question is too vague to be answered. You could be (currently) in a pure snowflake, a fact+ +snowflake or 2facts + dimension scenario and with the change you could be in a multi-fact+dim, multi-fact+snowflake, single-fact + multi-dim, single-fact + snowflake or pure snowflake scenario.

Way too many options to give a valid answer. We could randomly throw stones at the issue and see what sticks....which is probably what will happen in a short while but if you want a precise and most importantly pertinent answer: Be more precise!

souvik88_570 · May 31, 2017 3:48AM

Hi Christian

Thanks for your reply.

This is a pure snowflake scenario where all the tables are dimension tables.

The join path will be like - AliasTab1A ---------------> AliasTab2 ----------------> AliasTab1B. After the introduction of new table, which is also a dimension table, it will provide a second path to traverse from AliasTab1A to AliasTab1B.

All the joins are complex joins where the cardinality is unknown.

My question is whether it is advisable to create the new joins using the existing alias of Tab1 or shall i create newset of alias?

[Deleted User] · May 31, 2017 3:51AM

Souvik Manna wrote:The join path will be like -  AliasTab1A ---------------> AliasTab2 ----------------> AliasTab1B. After the introduction of new table, which is also a dimension table, it will provide a second path to traverse from AliasTab1A to AliasTab1B.

"a second path" as in "an alternative path"?

So you'd have 2 different and distinct flakes?

1.) AliasTab1A -> AliasTab2 -> AliasTab1B

and

2.) AliasTab1A -> AliasTab3 -> AliasTab1B

Is that correct?

souvik88_570 · May 31, 2017 3:55AM

Yes correct.

But, there already exist reports that use the path AliasTab1A -> AliasTab2 -> AliasTab1B and i cannot alter that (perhaps by changing that LTS priority?).

But then, I have new requirement of reports which should use AliasTab3 as the bridge between AliasTab1A and AliasTab1B.

[Deleted User] · May 31, 2017 3:59AM

Better go for something like

AliasTab1A -> AliasTab3 -> AliasTab1*C*

This will ensure that the instantiation of Tab1 happens in a nicely separated way.

souvik88_570 · May 31, 2017 4:25AM

Thanks for the solution.

I have a separate path created using two new alias of Tab1, i.e., AliasTab1C -> AliasTab3 -> AliasTab1D which works as expected.

But i was wondering if there is a way to leverage the existing set of alias to create this alternate path and then enforce OBIEE to follow this path based on certain condition without jeopardizing the old joins?

That way I can reduce the number of alias for the same table.

Gianni Ceresa · May 31, 2017 5:30AM

AliasTab1C is useless, that's why Christian only introduced this new alias as the right-side table at the end of the new path.

There could be ways to use your current 2 alias of Tab1, but to be sure the existing one keep using the old route you better add a new alias. This way is safer. (Because in the end OBIEE generates the queries and so you never know what changes on that side can happen at some point)

[Deleted User] · May 31, 2017 5:59AM

Gianni beat me to it but that ("leverage the existing set of alias") is precisely why I said AliasTab1*A* -> AliasTab3 -> AliasTab1*C*

The starting point for the two flake outliers can be the same if you for example use them as alternative hierarchies or the likes. If you have two distinct starting points you can (and probably should) model two completely distinct snowflakes as two distinct logical dimensions.

But again: ENTITY RELATIONSHIP DIAGRAM

You still have not said a word about WHAT that stuff actually is and believe me or not - I guess you don't believe me - it DOES make a difference what the things are supposed to be because

"Private Customer's Contact Team Address" as a snowflake of Customer -> Contacting Entity -> Contacting Entity Address

is NOT the same as

"Institutional Customer's Direct Contact Team Address" as a snowflake of Customer -> Contacting Entity -> Contacting Entity Address

souvik88_570 · May 31, 2017 6:58AM

Hi Christian

I am actually trying to build a report to show "Supplier Bank Branch" information (Bank Number, Bank Name, Branch Number, Branch Name).

I have below tables to get this information -

HZ_ORGANIZATION_PROFILES_BANK and HZ_ORGANIZATION_PROFILES_BRANCH - Both alias of HZ_ORGANIZATION_PROFILES to get Bank/Branch Number.

HZ_PARTIES_BANK and HZ_PARTIES_BRANCH - Both alias of HZ_PARTIES to get Bank/Branch Name.

IBY_EXT_BANK_ACCOUNTS_SUPPLIER - Alias of IBY_EXT_BANK_ACCOUNTS

In the current model, the joins are like - HZ_ORGANIZATION_PROFILES_BANK --> HZ_PARTIES_BANK --> IBY_EXT_BANK_ACCOUNTS_SUPPLIER --> HZ_PARTIES_BRANCH --> HZ_ORGANIZATION_PROFILES_BRANCH.

But, with the joins with IBY_EXT_BANK_ACCOUNTS table, report does not give desired result. So, I am trying to introduce HZ_RELATIONSHIPS table in place of IBY_EXT_BANK_ACCOUNTS, without distorting the existing joins.

Hope this makes my scenario clear.

[Deleted User] · May 31, 2017 7:16AM

If you draw it as a picture and it makes sense with regards to your business requirements then go for it.

Technically it will obviously work fine since it's extremely standard modeling

MFA for Oracle Community

Oracle Analytics Cloud and Server

Categories

Query regarding physical joins

Answers