Loading data into Time Dimension

[ketfos]

Hi,

I have Oracle database.

How do I load data into time dimension.
The measures are day, month, qtr, year.
We have datastage 7.5



Thks
Ketfos

[DSguru2B]

Very vague question. Need requirements. What goes into what column and in what format.

[ketfos]

Hi,

I have transaction table with Policy Number, Location, Type, Premium Amount and Payment Date.
I want to create FACT and DIMESNION table.
I want to create a Fact Table which will have premium amount and corresponding Time Dimension table to analyze this fact on daily, monthly,quaterly and yearly basis.

Thanks
Ketfos

[narasimha]

Generation of the Time generation is very vital part in your Schema,
There are multiple ways to go ahead with that.
I would prefer doing all the calculations in an excel and loading into the table, rather than building a job to do that.
But if you want to go the datastage path -
Refer to the popular "TwoTechTips" documents, forgot which of the premium posters had compiled that.

[ketfos]

Hi,

What calculations I need to build into the Excel for time dimesnion .
Loading from Excel to table is not a problem.

ketfos

[us1aslam1us]

First decide the range of Dates you would like to have their and consider that as a Date_Key then easily you can break that into day,month,year and quarter using formulas.

Anyway Narasimha has been referring to this post
viewtopic.php?t=101775&highlight=twotechtips

[narasimha]

This depends on what level of granularity you want in your Time Dimension.
I suggest you look into all possibilities before hand, as a lot of your tables can have keys referring to it.

[narasimha]

First decide the range of Dates you would like to have their and consider that as a Date_Key then easily you can break that into day,month,year and quarter using formulas.

Anyway Narasimha has been referring to this post
viewtopic.php?t=101775&highlight=twotechtips

Right Sam,
How can I forget, It was courtesy, Craig and Kim. Thanks!

[I_Server_Whale]

As an example of the structure of a time dimension. This is the one that is being used at my current site.

Code: Select all

Date_SK (PK, int, not null)
Date (varchar(8), null)
Day_Month (varchar(10), null)
Day_Number_Of_Month (int, null)
Is_Holiday (varchar(1), null)
Is_Weekend (varchar(1), null)
Long_Date (varchar(20), null)
Month_Begin_Date (datetime, null)
Month_End_Date (datetime, null)
Month_Long_Name (varchar(10), null)
Month_Number (int, null)
Month_Short_Name (varchar(5), null)
Quarter (int, null)
Quarter_Begin_Date (datetime, null)
Quarter_End_Date (datetime, null)
Short_Date (datetime, null)
Week_Begin_Date (datetime, null)
Week_End_Date (datetime, null)
Week_Number_Of_Month (smallint, null)
Week_Number_Of_Year (int, null)
Weekday_Long_Name (varchar(10), null)
Weekday_Number (int, null)
Weekday_Short_Name (varchar(5), null)
Year (int, null)
Year_Begin_Date (datetime, null)
Year_End_Date (datetime, null)
Year_Month_Name (varchar(10), null)
Year_Month_Number (varchar(10), null)
Year_Quarter (varchar(10), null)
Same_Day_Last_Year (datetime, null)
Same_Day_Next_Year (datetime, null)
Year_Week_Number (varchar(10), null)
Year_Week_Quarter (varchar(10), null)

It can very well vary depending on your needs.

[ray.wurlod]

And ALL of those fields except IsHoliday can be generated using DataStage functions - specifically Oconv(). You can even use a before-job subroutine to generate internal format dates in your desired range (job parameters) into a file, then process that file as your source of dates.
Holidays, of course, vary year by year; you can hard code the constant ones, but would need to invent some manual process for getting the variable ones into your system.

[ketfos]

Hi,

Thanks, I have loaded data into the time dimension table (
Date_SK (PK, int, not null)
Date (varchar(8), null)
Month_Number(int,null)
Qtr_Number(int, null)
Year_Number(int,null)

I have transaction table Policy with following fields PolicyNumber, Premium_Amount, Region, payment_Date.

How do I load data into the PremiumFact table
(Date_SK (FK, int, not null)
Premium_Amount)

from transaction table Policy

The end result is to get premium_amount by day, month,qtr,year.

Ketfos

[I_Server_Whale]

Code: Select all

                                           time
                                            |
                                            |
                                            |
                                            |(Do a lookup on Policy.payment_Date = Time.Date and pull the Date_SK into PremiumFact )
                                            |
                                            |
                                            |
                                            |
           Policy------------------->Transformer---------------->PremiumFact table

[DSguru2B]

Basically do a lookup on natural key to get the primary key which in your case is the surrogate key.
Speaking of natural key, how come it is specified as nullable, you sure the Date column is Nullable

[kduke]

I would make your surrogate key a smart key like YYYYMMDD. This will save you a lot of work on lookups.

I wrote one of these in BASIC for Michael Hester. Maybe he will post it or allow me to post it if I can find it.

You usually have a couple of choices whn doing this in a routine. You can write it to a hashed file or sequential file. Next you need a job to push it to your table.

[ray.wurlod]

You can load the hashed file from your Time dimension table, and create the smart key (if required) during that process. Basically, however, all you need is a two-column hashed file with the date as its key and the corresponding Time dimension surrogate key value as its other column.