Business Objects and role playing Dimension (Date)

View previous topic View next topic Go down

Business Objects and role playing Dimension (Date)

Post  Jeremyoos on Tue Jun 26, 2012 4:46 pm

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

Jeremyoos

Posts : 4
Join date : 2009-02-05

View user profile

Back to top Go down

Re: Business Objects and role playing Dimension (Date)

Post  ngalemmo on Tue Jun 26, 2012 10:18 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Business Objects and role playing Dimension (Date)

Post  Jeremyoos on Tue Jun 26, 2012 11:48 pm

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

Jeremyoos

Posts : 4
Join date : 2009-02-05

View user profile

Back to top Go down

Re: Business Objects and role playing Dimension (Date)

Post  ngalemmo on Wed Jun 27, 2012 1:04 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Business Objects and role playing Dimension (Date)

Post  Jeremyoos on Wed Jun 27, 2012 8:25 am

Hi Nick

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

Jeremy

Jeremyoos

Posts : 4
Join date : 2009-02-05

View user profile

Back to top Go down

Re: Business Objects and role playing Dimension (Date)

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum