Filling fact table using a map dimension table
2 posters
Page 1 of 1
Filling fact table using a map dimension table
Hi everyone, i'm a beginner with DWH, after documenting the requirements of my DWH i'm facing the ETL process.
I need to fill a Dimension table which rows comes from many databases.
Between each of these databases some rows can have the same production key:
Database1
project table
id name
1 project1
Database2
project table
id name
1 project1
When i create the map table to assign a surrogate key to each project
looks like this
DimProjectMap
id Project Key(surrogate)
1 1 ->(this comes from Database1)
1 2 ->(this comes from Database2)
Until here everything is ok
Now i fill my Dimension table
DimProject
Project Key Name
1 Project 1
2 Project 2
After all this looks good, but when i faced the filling of the fact table
i need to relate back to the production id of every project to get the
facts of each.
The problem is that in the map table 2 or even more projects could have
the same production id, so if i relate back to the facts using the map table
i will get the facts of all of the projects for each project, this is not good.
I wonder if there is a pattern or solution to this problem.
Thanks in advance for everybody.
I need to fill a Dimension table which rows comes from many databases.
Between each of these databases some rows can have the same production key:
Database1
project table
id name
1 project1
Database2
project table
id name
1 project1
When i create the map table to assign a surrogate key to each project
looks like this
DimProjectMap
id Project Key(surrogate)
1 1 ->(this comes from Database1)
1 2 ->(this comes from Database2)
Until here everything is ok
Now i fill my Dimension table
DimProject
Project Key Name
1 Project 1
2 Project 2
After all this looks good, but when i faced the filling of the fact table
i need to relate back to the production id of every project to get the
facts of each.
The problem is that in the map table 2 or even more projects could have
the same production id, so if i relate back to the facts using the map table
i will get the facts of all of the projects for each project, this is not good.
I wonder if there is a pattern or solution to this problem.
Thanks in advance for everybody.
uzielbueno- Posts : 2
Join date : 2013-12-18
Re: Filling fact table using a map dimension table
You haven't made it clear if a project with the same ID on different databases is actually the same project or a completely different project.
If they are the same project, you should not be creating multiple rows in the dimension table (unless you are implementing a type 2 dimension). If this is a type 1 dimension there should be one row per natural key. If it is a type 2 dimension, when you are loading facts, you only use the most current row for that particular natural key. The fact gets assigned the key of that row.
If these projects are different projects, then you need to expand your natural key. A common way to do this is to add a source code to the natural key to ensure unique keys for each data source. You do the same with facts from each source to locate the correct project row using the code appropriate for that source.
If they are the same project, you should not be creating multiple rows in the dimension table (unless you are implementing a type 2 dimension). If this is a type 1 dimension there should be one row per natural key. If it is a type 2 dimension, when you are loading facts, you only use the most current row for that particular natural key. The fact gets assigned the key of that row.
If these projects are different projects, then you need to expand your natural key. A common way to do this is to add a source code to the natural key to ensure unique keys for each data source. You do the same with facts from each source to locate the correct project row using the code appropriate for that source.
Re: Filling fact table using a map dimension table
I appreciate all your really good advice!!.
Sorry about for the details I left out.
The projects are actually different on each database.
The source table uses a surrogate key to identify a project, so I don't have a natural key, perhaps I could create a new field on this source table with some identifier to create a composed key.
I was thinking on this after reading your response, reviewing the source table there is no other field(s) I could use to compose this key and i'm sure that making this key a string concatenating some guid is not a good idea cause it can hurt performance, so I think I need to keep this key as an integer number but I can't make my mind how to achieve this, do you have some advice on this?? I'll appreciate it a lot, thank you very much again :D.
Sorry about for the details I left out.
The projects are actually different on each database.
The source table uses a surrogate key to identify a project, so I don't have a natural key, perhaps I could create a new field on this source table with some identifier to create a composed key.
I was thinking on this after reading your response, reviewing the source table there is no other field(s) I could use to compose this key and i'm sure that making this key a string concatenating some guid is not a good idea cause it can hurt performance, so I think I need to keep this key as an integer number but I can't make my mind how to achieve this, do you have some advice on this?? I'll appreciate it a lot, thank you very much again :D.
uzielbueno- Posts : 2
Join date : 2013-12-18
Similar topics
» joining dimension table to dimension and again fact table
» Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table
» Large Dimension table compared to fact table?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
» Joining Type II Dimension Hierarchy directly to Fact versus through a Dimension table
» Large Dimension table compared to fact table?
» Conformed Dimension for Transaction Fact and Accumulating Snapshot Fact Table
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum