How to role playing dimensions in PostgreSQL

View previous topic View next topic Go down

How to role playing dimensions in PostgreSQL

Post  cmg on Tue Dec 07, 2010 4:46 pm

I am new in the DW/BI world but as far as I've learned, the Kimball approach is the best. I need to make use of "role playing dimensions", more specifically I need more than one date in my fact table. I've read in the Kimball's books that it can be done creating views of the dimension table and pointing my foreign keys to them. I've also read in this forum that it is not necessary, that it can be achieved with aliases. Here comes my problem, I am using PostgreSQL and I'm unable to point my foreign keys to the views that I've created, just to tables. I've tried the aliases alternative but I've only found a way for creating aliases: in the SELECT clause. Any solution that can work in PostgreSQL?

cmg

Posts : 1
Join date : 2010-12-07

View user profile

Back to top Go down

Re: How to role playing dimensions in PostgreSQL

Post  ngalemmo on Tue Dec 07, 2010 6:34 pm

No relational database has foreign key relationships to views. Views are simply logical representations of sets. And anyway, where the foreign key points in the constraint definition has nothing to do with constructing a query.

What is the issue is how do you present role playing dimensions to users so it is clear to them what joins to what. The view suggestion is simply if you have a column called "ship_date_key" in the fact table, you have a corresponding view called "ship_date_dim" with a PK named "ship_date_key" rather than just "date_dim" and a PK named "date_key".

Thing is, users don't write SQL anymore, they use query tools which makes the issue moot, and those users who are technical enough to write SQL can understand that "ship_date_key" is a role reference to the date_dim table without the need for views or synonyms.
avatar
ngalemmo

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

View user profile http://aginity.com

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