Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
intern2424
Yak Posting Veteran
53 Posts |
 Posted - 2009-12-17 : 12:29:07
|
| Hi, I was wondering if anyone can help me create a stored procedure that will insert data into multiply table. I want to insert data in my charter fact table but also enter data into the dimensional table. So I enter in charter fact data. First it will enter the data in that table then it will enter data into the other dimensional table. but dim_aircraft, dim_destination, dim_employee, Dim_date, Dim_model, Dim_customer all have distinct data. I want it to check if the value is in that table if it is not then add that value to the dimensional table.Here are the tables.Relationships Charters_fact_table has many to one relations with all the tables. Create table Dim_Employee(Emp_Num int not null,Emp_Title nvarchar(4) not null, Emp_Lname nvarchar(15) not null,Emp_FName nvarchar(15) not null,Emp_initial nvarchar(1) not null,Emp_DOB datetime not null,Emp_Pil_license nvarchar(25) not null,Emp_Pil_Rating nvarchar(25) not null,Emp_Med_Type nvarchar(1) not null,Emp_Pil_Pt135_Date datetime not null,Emp_Hire_Date datetime not null,Primary key (Emp_Num))create table Dim_Destination(Destination nvarchar(3) not null,Primary key(Destination))Create table Dim_Date(Date datetime not null,primary key (Date))create table Dim_Model(Mod_Code nvarchar(10) not null,Mod_Manufacturer nvarchar(25) not null,Mod_Name nvarchar(20) not null,Mod_Seats nvarchar(4) not null,Mod_Chg_Mile real not null,Mod_Cruise float not null,Mod_Fuel float not null,primary key (Mod_Code))Create Table Dim_Customer(Cus_ID int not null,Cus_lname nvarchar(15) not null,Cus_fname nvarchar(15) not null,Cus_Initial nvarchar(1) not null,Cus_Area_Code nvarchar(3) not null,Cus_Phone nvarchar(8) not null,Cus_Balance real not null,Primary Key (Cus_ID))Create Table Dim_Aircraft(Ac_Number nvarchar(5) not null,Ac_ttaf nvarchar(10) not null,Ac_ttel float not null,Ac_tier float not null,Primary Key (Ac_Number))Create Table Charters_Fact_Table(Cus_ID int not null, Mod_Code nvarchar(10) not null,Emp_Num int not null,Ac_Number nvarchar (5) not null,Date datetime not null,Destination nvarchar(3) not null,Char_trip int not null,Char_pilot int null,Char_Distance nvarchar(3) not null,Char_hours_flown float not null,Char_Hours_wait float not null,Char_fuel_gallons float not null,Char_oil_qts smallint not nullPrimary Key (Cus_ID, Mod_Code, Emp_Num, Ac_Number, Date, Destination),FOREIGN KEY (Cus_ID) REFERENCES Dim_Customer(Cus_ID) ,FOREIGN KEY (Mod_Code) REFERENCES Dim_Model(Mod_Code),FOREIGN KEY (Emp_Num) REFERENCES Dim_Employee(Emp_Num),FOREIGN KEY (Date) REFERENCES Dim_Date(Date),FOREIGN KEY (Destination) REFERENCES Dim_Destination(Destination),Foreign Key (Ac_Number) References Dim_Aircraft(Ac_Number))thanks for any help you can give. |
|
|
Kumar_Anil
Yak Posting Veteran
68 Posts |
Posted - 2009-12-18 : 14:58:32
|
| Intern,I would want to share something that I learnt along the way. In almost all of the places that I saw DW implementations, first they populate all of the dimension tables on a pre-defined schedule time of the day or the week. Then they will load that into the fact table and before they do that they run a check on the fact table & see if the data already exists as generally there are no duplicate entries in fact table whatsoever.This can be done through various means by using a trigger(there are many samples in this forum for it ) or define a job & schedule it to run the insert or update.regards,Anil |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-12-20 : 03:42:24
|
| we generally make use of ssis pakages for this. we create packages to populate the required dimensions before populating the fact table. in fact table we make use of lookup transformations to lookup for dimension records based on business key and then retrive the correponding surrogate key to populate fact. Just in case any dimension package got failed we check if record with business key is not already in dimension when we populate the fact and in such case we insert them as inferred memebers in dimension inside package for fact. the values corresponding to this record will be picked up during next succesful run of dimension package. |
|
|
|
|
|
|
|
|
Need help with a stored procedure