January 6, 2012 at 7:43 am
Hi guys
having successfully clean my data and populated my dimensional and fact tables, my question is how can i insert values from the dimensional tables into the fact table
the following are my dimensional tables:
- Dim_Temprequset {RequestId(pk), RequestDate}
- Dim_BankBranch {BranchId(pk), Address, town, county, postcode, telephone, typeofbranch}
- Dim_time{ System_date, Date_in_month, day_text, day_in_week, week_in_month, month_number, month_text, quater, year}
- Dim_session {SessionId(pk), requestId, TempId, sessiondate, sessionstart, sessionend, status, type}
- Fact_sales {Time_key, BranchId, SessionId, temprequestId, county, branchname, town}
can anyone help
🙂
New kid on the block
January 6, 2012 at 7:46 am
Delario (1/6/2012)
having successfully clean my data and populated my dimensional and fact tables, my question is how can i insert values from the dimensional tables into the fact tablethe following are my dimensional tables:
- Dim_Temprequset {RequestId(pk), RequestDate}
- Dim_BankBranch {BranchId(pk), Address, town, county, postcode, telephone, typeofbranch}
- Dim_time{ System_date, Date_in_month, day_text, day_in_week, week_in_month, month_number, month_text, quater, year}
- Dim_session {SessionId(pk), requestId, TempId, sessiondate, sessionstart, sessionend, status, type}
- Fact_sales {Time_key, BranchId, SessionId, temprequestId, county, branchname, town}
Please note there is a "Data Warehouse" section on this forum.
Having said that - you usually do not move data from DIM to FACT, ETL process should populate both DIM and FACT tables using the data extracted from the OLTP source/s.
_____________________________________
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.January 6, 2012 at 7:50 am
just name them like to differentiate the tables, in reality the name convention are different
New kid on the block
January 6, 2012 at 7:52 am
Delario (1/6/2012)
just name them like to differentiate the tables, in reality the name convention are different
:blink: would you rephrase, please?
_____________________________________
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.January 6, 2012 at 7:58 am
ok let put it this way, I will change the name of the table
:alien:
New kid on the block
January 6, 2012 at 8:05 am
Delario (1/6/2012)
ok let put it this way, I will change the name of the table
Well... No matter the actual names of the tables the fact is that in a Dimensional Modeled a.k.a. star-schema Data Warehouse, by definition you end up having FACT and DIM tables and these FACT and DIM tables should be populated by the ETL process using data extracted from the OLTP source - not moving data between them.
_____________________________________
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.January 6, 2012 at 8:08 am
got your point
New kid on the block
January 6, 2012 at 8:13 am
so how can I populate the fact table
New kid on the block
January 6, 2012 at 9:46 am
Delario (1/6/2012)
so how can I populate the fact table
Well... you designed it, don't you?
Each row on the particular FACT table is supposed to describe a factual event on your OLTP system, isn't it? Therefore your ETL process should "E"xtract all new factual events from the OLTP system, "T"ransform them as needed to map the attributes on your FACT table then "L"oad such data into the FACT table.
Quick example:
You have a OLTP system that does billing for your company.
Such a system generates Invoices - the generation of each "Invoice" is a business fact.
On your DWH you design a FACT_INVOICE table.
Your daily ETL process loads into FACT_INVOICE all new "invoices" generated on the OLTP system since the last time ETL ran - some related DIMension tables will get loaded alongside.
No offense but designing a DWH is not something you do like buttering bread, you may consider to either get professional assistance or really (really, realy, really) research a lot more.
_____________________________________
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.January 6, 2012 at 9:51 am
I understand the concept but I am having problem with the implimentation. that said all that you have said have done with clear understanding.
New kid on the block
January 6, 2012 at 10:03 am
Delario (1/6/2012)
I understand the concept but I am having problem with the implimentation. that said all that you have said have done with clear understanding.
take a look here...this may help
http://technet.microsoft.com/en-us/library/ms166552.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
January 7, 2012 at 5:03 am
Guys, it’s an Oracle Data Warehouse and questions are distributed in multiple threads...
http://www.sqlservercentral.com/Forums/Topic1231770-391-1.aspx
http://www.sqlservercentral.com/Forums/Topic1231314-391-1.aspx
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply