Framework manger join relation

[ravinder]

Hi Everyone,
In Frame work manger I have a fact table and a reference table, at present I have a relation like this
     Fact Table <----> Reference table
         (0..n)                     (1..1)

I have many reference tables like this in relation with the fact table in my model. As there are many outer joins, client asked me to change all these joins to inner joins (1..1 on both sides). Most of my queries will be driven from Fact table to reference tables, in this scenario if i put 1..1 relation on both sides the query would still bring the correct result, but very few queries will be driven from reference table to fact table, in this scenario if I use 1..1 relation i will get the wrong result. So I am planning to use 2 packages, First package with inner join (1..1 both sides) to handle the queries driven from fact table. The second package with outer join between Fact and Reference table (0..n; 1:1) to handle the queries driven from reference table. Is there any better way of doing instead of publishing 2 packages? If I use determinants on reference table can my package can handle the queries driven from both fact and Reference table?

[MFGF]

Hi Everyone,
In Frame work manger I have a fact table and a reference table, at present I have a relation like this
     Fact Table <----> Reference table
         (0..n)                     (1..1)

I have many reference tables like this in relation with the fact table in my model. As there are many outer joins, client asked me to change all these joins to inner joins (1..1 on both sides). Most of my queries will be driven from Fact table to reference tables, in this scenario if i put 1..1 relation on both sides the query would still bring the correct result, but very few queries will be driven from reference table to fact table, in this scenario if I use 1..1 relation i will get the wrong result. So I am planning to use 2 packages, First package with inner join (1..1 both sides) to handle the queries driven from fact table. The second package with outer join between Fact and Reference table (0..n; 1:1) to handle the queries driven from reference table. Is there any better way of doing instead of publishing 2 packages? If I use determinants on reference table can my package can handle the queries driven from both fact and Reference table?

Hi,

I'm not sure you have the right idea here. Converting the cardinality above from outer to inner would simply mean changing the fact table end to be 1..n instead of 0..n.

The implications of doing this will obviously be that you will not see dimension rows which have no facts in your reports. Is there a requirement to do this? If so, the join needs to be 0..n at the fact end (as it is currently).

MF.

[ravinder]

Yes, the original requirement is 0..n on fact side and 1..1 on dimension side. But many users want to see for a given fact row they want to see the corresponding dimension row, in this scenario inner join would make sense, they are not worried much about the dimension rows which have no entries in fact table. And they again want to create a separate package with 0..n on fact side and 1..1 on dimension side to see the dimension rows which have no entries in fact table, very rarely the users will have the need to look at this information, in those cases they will use this outerjoin package and the rest of the times they will use the inner join package.

So i am wondering if there is any way to just use one single package with inner join and to pull all the dimension rows which have no entries in fact table as well. I know this is not possible with inner join but just exploring the various options.