Business Objects and role playing Dimension (Date)

Hi All

We have a Fact that refers to the Date Dimension various times. It is for a Payment Fact, so it has a Payment Date, Effective Date, Accounting Date and so on. When we create a query, the SQL generated by Business Objects wants ALL the date foreign keys on the Fact to match the Date Dimension key, i.e.
select fact.field1, dimension.field2 from fact, dimension
where fact.datekey1 = dim.primary_key
and fact.datekey2 = dim.primary_key
and fact.datekey3 = dim.primary_key

The dates could be different, so it would filter out those not-equal-date records in the query, which is not a desirable situation.

I've read elsewhere on this forum that it is not recommended to use aliases for this specific purpose, but what should we do instead?

Thanks,

Jeremy

Use an alias. Why would you not?

If users are hand-coding SQL, some designers like to define views for each role, so it is clearer to the user. But, since you are using BOBJ, there is no need for that.

Hi Nick

Thanks for your reply. The reason we're hesitating to use aliases is in a previous response of yours to this post about the subject: "unless you want to present the user with a universe containing multiple folders for the same dimension." We don't *really* want to do that, but we're not dogmatic, so we may still go that way.

You provided this alternative: "An alternate way to do this is to provide a set of filters that reference the fact tables and label them in such a manner that allows the user to specify the context." However, I'm not sure I understand how to do that, or if it will even solve the problem. Our problem is with multiple FKs on a single Fact to the same Dimension.

Jeremy

I believe that was using the same role in different facts. But, if a single fact table has 3 roles for the same dimension, you must define aliases and present separate sets of attributes for each role. Once you have established payment date role attributes (payment date, payment month, payment period, etc...), for example, you can use that same collection of attributes with other facts within separate contexts.

Hi Nick

Cool, thanks; that's the way we'll go.

Jeremy