October 30, 2008 at 8:59 am
A Really easy one I imagine. Im new to Data warehousing and I cant find anything in my book about this.
If you have a fact table linked to dimensions with 2 questions in it. One question only uses 3 dimensions and question 2 uses 4. Can you have everything in the same fact table, meaning that some of the foreign Keys in the fact table will be null since the dimension isnt used for every FACT.
OR would you have to have 2 seperate data marts for each question, ensuring the foreign keys in each fact table are completely populated?
Thanks in advance for any advice
Debbie
October 30, 2008 at 11:45 am
In a Data Warehouse environment you define FKs just to let the system know there is a relationship but normally you disable them.
The idea is ... you want the system to know about the relationship so a better execute plan would be chosen but you don't really want to enforce the FK constraint.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 31, 2008 at 3:11 am
Hmmmm,
So does that mean you can have NULL Foreign keys?
E.g.
PersonID AttID PupilinSystem PupilswithAttinSystem
1 4 1 1
3 9 1 1
4 NULL 1 NULL
Debbie
October 31, 2008 at 4:03 am
Why don't you test it?
Look...
USE pubs
go
CREATE TABLE dbo.master_table
(
mykey numeric(5) NOT NULL,
mydata varchar(10) NULL
)
go
CREATE TABLE dbo.variable_table
(
vpkkey numeric(5) NOT NULL,
vfkkey numeric(5) NULL,
vmydata varchar(10) NULL
)
go
ALTER TABLE dbo.master_table ADD CONSTRAINT pk_master_table
PRIMARY KEY CLUSTERED (mykey)
go
ALTER TABLE dbo.variable_table ADD CONSTRAINT pk_variable_table
PRIMARY KEY CLUSTERED (vpkkey)
go
ALTER TABLE dbo.variable_table ADD CONSTRAINT fk_variable_table
FOREIGN KEY (vfkkey)
REFERENCES dbo.master_table (mykey)
go
ALTER TABLE dbo.variable_table NOCHECK CONSTRAINT fk_variable_table
go
insert into dbo.master_table values(1,'AAAAA')
insert into dbo.master_table values(2,'BBBBB')
insert into dbo.master_table values(3,'CCCCC')
go
insert into dbo.variable_table values(1,1,'VAR AAAAA 1')
insert into dbo.variable_table values(2,1,'VAR AAAAA 2')
insert into dbo.variable_table values(3,Null,'CCCCC')
go
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply