How do I join a role playing date dimension view with a Fact table in SQL Server?

Hi,
I'm using SQL Server 2008 R2, and have a star schema with a Date Dimension and Fact table (along with other dimensions). I have a role playing date dimension in the form of a View.

I need to join the Primary key of the View date dimension to the Fact table Foreign Key. But I guess you can't create Primary Keys on views in SQL Server.

How can I join this view to my fact table and create a Primary/Foreign key connection?

Thanks in advance!

A primary key is purely a logical concept. In a query you join on columns, not keys. And views are simply pre-defined queries.

Primary and foreign key CONSTRAINTS are physical database concepts and apply to tables. A view would assume any constraints defined on the tables it references.

ngalemmo wrote:A primary key is purely a logical concept. In a query you join on columns, not keys. And views are simply pre-defined queries.

Primary and foreign key CONSTRAINTS are physical database concepts and apply to tables. A view would assume any constraints defined on the tables it references.

Hi ngalemmo,
Thanks for your reply! And thanks for answering several other posts in this forum.

I guess I didn't clearly understand your response. I have a Dim_Date, with a view created on that, called vDim_Vessel_Date. The Primary key of the Dim_Date is Date_Key. The view has the same date key as an alias Vessel_Date_Key.

My Fact Table has a column Vessel_Date_Key. Now I wish to join the Vessel_Date_Key of the Fact table and the View. And I'm not being able to do so.

I guess this could be SQl Server related issue, but can you please tell me how to do that?

What do you mean by 'not able to do so'? The SQL is simple... it is a tool specific issue, such as trying to define a cube in SSAS?

ngalemmo wrote:What do you mean by 'not able to do so'? The SQL is simple... it is a tool specific issue, such as trying to define a cube in SSAS?

I can see that I'm not explaining my problem correctly. Here's another shot at it.

I have 6 Views with the same underlying table. Underlying table is Date table and the 6 views are Arrival_Date, Departure_Date, etc. (these 6 are my role playing date dimensions).

I also have a Fact table with these 6 Foreign Keys that need to reference the 6 Views. I need to create Primary Key and Foreign Key constraints between this Fact table and the 6 Views.

I'm not being able to do so because: 1. I can't define a Primary Key for a view in SQL Server 2008 2. I am not able to define a constraint in a SQL Server view (I use SQL Server Management Studio, and I don't see any way to create a constraint in a View).

Please advise how to create these 6 constraints between the PKs of the views and the Fact Table FKs.

I hope this helps, and sorry to have to explain differently.

Thanks in advance!

You don't define those things on a view. They are defined on the tables. You would define the FK constraint on each fact table key against the PK of the dimension.

The view and queries have no use for such constraints. FK constraints are only used when rows are inserted into the fact table.

ngalemmo wrote:You don't define those things on a view. They are defined on the tables. You would define the FK constraint on each fact table key against the PK of the dimension.

The view and queries have no use for such constraints. FK constraints are only used when rows are inserted into the fact table.

You're right, a normal view is just a saved query, so we cannot have integrity constraints on a view.

So please let me know if this will work.

I have six date field keys in my fact table, e.g., Arrival_Date_Key, Departure_Ship_Date_Key, etc.etc. When a new record comes from my transaction system with all these dates, my ETL process (Using SSIS), uses the Date table to look up the surrogate key for each of these 6 dates. It then inserts those surrogate keys into the 6 dateKeys in my Fact table.

So if I use your suggestion, and place foreign key constraints onto the Date Table, I will have six Foreign keys in my fact table referencing the same Primary key in my same Date Table.
Question 1 - Is it ok to have 6 date key FKs from Fact table reference one Date_Key in the date table it like this?
Questions 2 - When my ETL process runs, it will use Date Table as a Look Up table to find the 6 surrogate keys for the 6 dates coming in. Will that work? (I guess I haven't tried this yet).

Thanks in advance for all your help!

Question 1: Yes
Question 2: It should. Not that familar with SSIS.

ngalemmo wrote:Question 1: Yes
Question 2: It should. Not that familar with SSIS.

Excellent! I will try this then. Thanks for all your help today!