Reg: How to model Union in RPD

User_JZKI6

Hello All,

Good evening !!!

We have 10 tables in RPD and there was around 25 common fields in each table we would like to create master table on top that for all the common fields that allows OBIEE to generate a UNION sql between all the tables.

Can anyone suggest how to model UNION in RPD level version we are using is 12.2.1.4.0.

Thanks in Advance

[Deleted User]

Why are there 10 tables and how are they split?

User_JZKI6

Hi Berg,

Thanks for your reply.

All 10 tables are fact tables joined with diff dim tables, we need to bring common fields of all fact tables into 1 table. Suppose column ABC exist in 7 fact tables then it as to make union of all those 7 fact tables and show the results.

Hope I made some sense.  

Thanks,

Joel

I would strongly recommend that you push this logic to the data source. You can use ETL (worst case scenario, a view) to implement your logic and OBIEE just pulls data from one object.

[Deleted User]

Apart from what Joel said - while it makes some kind of sense it doesn't answer my question at all.

WHY are there 10 tables and HOW are they split?

Is that 10 years worth of data? Does it represent 10 business unit? Is it split into 3-letter groups using the dwarf alphabet? 10 random buckets?

Vipulbhatia29

Hello

You can lookup fragmentation. Usually, we use fragmentation to implement UNION ALL in the right business scenarios. However, the grandmasters above have already asked you the right questions. These are the questions that you should always ask before implementing complex scenarios such as above. I don't believe fragmentation is going to be the solution. You should look at Christian Berg's question again and also if possible provide us with a clear cut example.

Thanks

[Deleted User]

My point exactly. Fragmentation has to follow a logic. And if one isn't capable of expressing such a logic in words one will definitely not be capable of expressing and implementing it technically.