| Author |
Topic |
|
satya068
Posting Yak Master
233 Posts |
 Posted - 2012-06-20 : 12:01:43
|
| Hi,i have denormalised the snow flake schema to star schema and in start schema diagram the fact table is not a proper fact table though it is a main table becausePK from my Fact table is pointing to FK in the Dimension table,but a proper fact table definition is Foreign keys in the fact table should refer to primary key in the dimension table.'Referral' is the fact table with below given columnsReferral ID PKCRNReferral Date---------all my dimension tables hold Referral ID as FK and the PK in my dimension tables does not exist in 'Referral' table.My question is 1)Can i create a surrogate key in dimension table and add in the Referral table for making Referral table as a proper fact table? 2)Without fact table can i create a datawarehouse tables,in my chart Referral is the main table but its not the fact table. 3)How to convert the Referral table into fact table?Thanks and Regards |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-06-20 : 21:55:30
|
| can you explain what this Referral table is holding and why dimensions are referring it to by means of fk?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-21 : 04:09:32
|
| >> PK from my Fact table is pointing to FK in the Dimension table,but a proper fact table definition is Foreign keys in the fact table should refer to primary key in the dimension table.Not really - if the dimension table has a unique covering index (which it should do) then you have a virtual table which is the same as the dimension you are describing. It is logically no different - yoou could put a view on it to make it a named derived table if it makes you happier. Only issue might be the datatype of the join column and how flexible it is. I take it this is a type 1 dimension.1) Don't see why not2) Yes - but yoou will need to generate a fact table in the cube build I think3) Depends on nwhat you need. Just look at measures and attributes. Put attributes into dimensions and anything that can be summed into the fact table. The structure should be built from what the user wants to see.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
satya068
Posting Yak Master
233 Posts |
Posted - 2012-06-21 : 05:24:02
|
| Thanks for you reply,I amanged to design a dataware house tables by creating a new fact table with all the ID and their fk pointing to Dimension table,Now my concern is the relation ship betewwn fact and dimension is 1 to N ,can you please give an idea on how to handle this 1 to n relationship. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-21 : 06:02:24
|
| It can't be 1-n - hence my comment about the unique index.How do you decide which value in the dimension to link to?I would create a new table for the link and extract unique data from your current dimension.You can link from the current dimension to this so that yoou don't duplicate data if you wish.You could also add a flag to your current table and set it for only one of each row to make it unique.You could think of that as a snowflake or you could think of it as a physical schema for a logical snowflake. The Kimball methodology doesn't really understand the difference in these so ignore what it says.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
satya068
Posting Yak Master
233 Posts |
Posted - 2012-06-21 : 07:11:46
|
| Hi Thanks for ur prompt reply.That is what i am thinking too,creating a new fact table with all id and getting unique data.can i use surrogate key in the new fact table for getting unique data? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-21 : 08:03:11
|
| Yes you probably should allocate a new surrogate key for the new table.You can also add that to your current dimension to link the two if you wish.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
satya068
Posting Yak Master
233 Posts |
Posted - 2012-06-21 : 08:39:13
|
| i think surrogate key needed to my dimension tables as well,how should i link my fact table and dimension?can i link with Sk inthe fact table to SK in the dimension table? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-21 : 08:42:26
|
| You add a column to the fact table (if one isn't already there) and populate it with the SK to the dimension row.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
satya068
Posting Yak Master
233 Posts |
Posted - 2012-06-21 : 09:05:36
|
| Hi, I added a Sk column in the fact table,now should i link Sk column from fact to Sk in the dimension?is this what r u trying to tell?Thanks |
|
|
|
satya068
Posting Yak Master
233 Posts |
Posted - 2012-06-21 : 11:11:58
|
| Hi,i managed to link Fact and Dimension tables,as i previously said its 1 to n Relation.MY dimension Tables are 'Diagnosis','TreatmentSummary' and 'Appointments' linking to 'Referral' Fact table.My question is How can i identify that how many Diagosis,How many appointments, and How many Treatmentsummary for each referralID in the referral fact table?Thanks |
|
|
|
|
Fact table query