Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Filling fact table using a map dimension table

2 posters

Go down

Filling fact table using a map dimension table Empty Filling fact table using a map dimension table

Post  uzielbueno 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

Back to top Go down

Filling fact table using a map dimension table Empty Re: Filling fact table using a map dimension table

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

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

http://aginity.com

Back to top Go down

Filling fact table using a map dimension table Empty Re: Filling fact table using a map dimension table

Post  uzielbueno 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

Back to top Go down

Filling fact table using a map dimension table Empty Re: Filling fact table using a map dimension table

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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