Filling fact table using a map dimension table

View previous topic View next topic Go down

Filling fact table using a map dimension table

Post  uzielbueno on Wed Dec 18, 2013 8:35 pm

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.

uzielbueno

Posts : 2
Join date : 2013-12-18

View user profile

Back to top Go down

Re: Filling fact table using a map dimension table

Post  ngalemmo on Thu Dec 19, 2013 1:23 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Filling fact table using a map dimension table

Post  uzielbueno on Thu Dec 19, 2013 2:19 am

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.

uzielbueno

Posts : 2
Join date : 2013-12-18

View user profile

Back to top Go down

Re: Filling fact table using a map dimension table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum