Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

How to role playing dimensions in PostgreSQL

2 posters

Go down

How to role playing dimensions in PostgreSQL Empty How to role playing dimensions in PostgreSQL

Post  cmg 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?


Posts : 1
Join date : 2010-12-07

Back to top Go down

How to role playing dimensions in PostgreSQL Empty Re: How to role playing dimensions in PostgreSQL

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

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

Back to top Go down

Back to top

- Similar topics

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