Dimension View
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 35
- Joined: Sun Jan 16, 2005 8:39 am
- Location: US
Dimension View
Hi All,
Can we create a DATABASE VIEW of a dimension table , In my case CALENDAR dimension and then use this view as lookup in fact load.
I have multiple date fields going into fact , at the same time I need to associate these dates with Calendar dimension obviously to get related information.
Is it possible ? Any suggestions would be highly appreciated.
Thanks.
Can we create a DATABASE VIEW of a dimension table , In my case CALENDAR dimension and then use this view as lookup in fact load.
I have multiple date fields going into fact , at the same time I need to associate these dates with Calendar dimension obviously to get related information.
Is it possible ? Any suggestions would be highly appreciated.
Thanks.
Santosh
Do you absolutely have to create a View to do that. No, you can load your dimension table into a lookup dataset and perform a lookup. You can also use Join stage and join the tables directly. I dont see any obvious reason for creating a VIEW.
My 2 cents.
My 2 cents.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
If you have multiple fact tables that refer to the calendar dimension I would suggest loading the calendar dimension into a Lookup File Set.
That's because the "index" (hash table) that the Lookup stage would ordinarily have to build at run time is built and stored in the Lookup File Set when it is populated, obviating the need to do so at run time.
(This is only beneficial if the Lookup File Set needs to be used in more than one job. Otherwise simply feed the reference input with the calendar dimension table itself - the Lookup stage will cause its rows to be loaded into a virtual Data Set and a hash table to be created to index it; lookups are then performed using this structure.)
That's because the "index" (hash table) that the Lookup stage would ordinarily have to build at run time is built and stored in the Lookup File Set when it is populated, obviating the need to do so at run time.
(This is only beneficial if the Lookup File Set needs to be used in more than one job. Otherwise simply feed the reference input with the calendar dimension table itself - the Lookup stage will cause its rows to be loaded into a virtual Data Set and a hash table to be created to index it; lookups are then performed using this structure.)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Charter Member
- Posts: 35
- Joined: Sun Jan 16, 2005 8:39 am
- Location: US
Hi Ray,
I have two DATE fields going into same FACT table. Instead of having the date as it is the in the FACT I want to populate the CALENDAR key. This way I can have all the information ( which a CALENDAR dimension provides) for both the dates.
It should be physical join in the database to access the same dimension.
The only way I can think of is creating a replica table of calendar dimension and then use these replica's (as if they were separete dimensions) and then where ever I have the requirement of associating a date filed with calendar I could just pull the corresponding DW key.
or
I thought I could create a VIEW ( but not sure about it)
Thanks,
Santosh.
I have two DATE fields going into same FACT table. Instead of having the date as it is the in the FACT I want to populate the CALENDAR key. This way I can have all the information ( which a CALENDAR dimension provides) for both the dates.
It should be physical join in the database to access the same dimension.
The only way I can think of is creating a replica table of calendar dimension and then use these replica's (as if they were separete dimensions) and then where ever I have the requirement of associating a date filed with calendar I could just pull the corresponding DW key.
or
I thought I could create a VIEW ( but not sure about it)
Thanks,
Santosh.
ray.wurlod wrote:If you have multiple fact tables that refer to the calendar dimension I would suggest loading the calendar dimension into a Lookup File Set.
That's because the "index" (hash table) that the Lookup stage would ordinarily have to build at run time is built and stored in the Lookup File Set when it is populated, obviating the need to do so at run time.
(This is only beneficial if the Lookup File Set needs to be used in more than one job. Otherwise simply feed the reference input with the calendar dimension table itself - the Lookup stage will cause its rows to be loaded into a virtual Data Set and a hash table to be created to index it; lookups are then performed using this structure.)
Santosh
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 35
- Joined: Sun Jan 16, 2005 8:39 am
- Location: US
Ray,
I ve got your point. I lookup the dimension twice with two different dates (coming from source) and fact can have 2 dw (calendar ) keys for both dates in the same record.
I was just wondering the idea of using Calendar DW key as DATE datatype.
Would that help?
Thanks,
Santosh.
I ve got your point. I lookup the dimension twice with two different dates (coming from source) and fact can have 2 dw (calendar ) keys for both dates in the same record.
I was just wondering the idea of using Calendar DW key as DATE datatype.
Would that help?
Thanks,
Santosh.
ray.wurlod wrote:Or two lookups, against the same Lookup File Set. You will need to finagle the key column names, but that's no big deal - you can do it with Copy stage.
Santosh
-
- Charter Member
- Posts: 35
- Joined: Sun Jan 16, 2005 8:39 am
- Location: US
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: