Dimension View

[santoshkumar]

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.

[DSguru2B]

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.

[ray.wurlod]

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.)

[santoshkumar]

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.

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.)

[narasimha]

You don't have to vreate a view, you can use the same query you use for your view as your source query.
My two cents.

[ray.wurlod]

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.

[santoshkumar]

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.

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.

[santoshkumar]

By the way Thanks for teaching me a new word FINAGLE.

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.

[ray.wurlod]

As a general rule we ALWAYS choose integer for foreign keys in star schemas, because they join the most efficiently.