January 6, 2012 at 2:51 am
Hi everyone
I need help to create and populate a time dimension table in data warehouse
can anyone help me please
New kid on the block
January 6, 2012 at 6:43 am
delmensah (1/6/2012)
I need help to create and populate a time dimension table in data warehouse
If you really have nothing link next may set you on the right track: http://msdn.microsoft.com/en-us/library/ms174832.aspx
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 6, 2012 at 6:49 am
You create it the same way you would create a normal table, only you populate it with dates and date parts, depending on your needs.
Do you need to keep week numbers? Or just calendar dates? Do you need quarter numbers and quarter names? What about weekdays or weekday numbers?
Figure that information out first, then create the table based on that information. Also, you need to know when to start the table (what is the first date in the table) and when to end the table (the last date).
Given SQL 2008's new time data types, you might also consider if you truly want a time-only dimension table or a date-time table or a date table. Most people consider a Time table as containing dates. But that may not be the case in your situation.
January 6, 2012 at 7:17 am
so far I have created the table base on your advice, and my time table looks likes this:
CREATE TABLE "DIM_TIME"
(
"TIME_KEY" NUMBER,
"SYSTEM_DATE" DATE,
"DAY_IN_MONTH" NUMBER,
"DAY_TEXT" VARCHAR2(10 BYTE),
"DAY_IN_WEEK" VARCHAR2(1 BYTE),
"WEEK_IN_MONTH" NUMBER(2,0),
"MONTH_NUMBER" NUMBER(2,0),
"MONTH_TEXT" VARCHAR2(12 BYTE),
"QUARTER" NUMBER(1,0),
"YEAR" NUMBER(2,0)
)
is this what it should look like?
New kid on the block
January 6, 2012 at 7:27 am
Delario (1/6/2012)
so far I have created the table base on your advice, and my time table looks likes this:CREATE TABLE "DIM_TIME"
(
"TIME_KEY" NUMBER,
"SYSTEM_DATE" DATE,
"DAY_IN_MONTH" NUMBER,
"DAY_TEXT" VARCHAR2(10 BYTE),
"DAY_IN_WEEK" VARCHAR2(1 BYTE),
"WEEK_IN_MONTH" NUMBER(2,0),
"MONTH_NUMBER" NUMBER(2,0),
"MONTH_TEXT" VARCHAR2(12 BYTE),
"QUARTER" NUMBER(1,0),
"YEAR" NUMBER(2,0)
)
is this what it should look like?
I would say that that's a good start but final design depends on business requirements.
Ask yourself this kind of questions...
- Would business want to slice data by Fiscal Year Week?
- Would business want to compare Current Week with same Week the Year before?
- Would business needs require to know which DOW is the last one of the month?
Depending on needs this dimension may require some extra columns but it all depends on your business requirements.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 6, 2012 at 8:21 am
Delario (1/6/2012)
so far I have created the table base on your advice, and my time table looks likes this:CREATE TABLE "DIM_TIME"
(
"TIME_KEY" NUMBER,
"SYSTEM_DATE" DATE,
"DAY_IN_MONTH" NUMBER,
"DAY_TEXT" VARCHAR2(10 BYTE),
"DAY_IN_WEEK" VARCHAR2(1 BYTE),
"WEEK_IN_MONTH" NUMBER(2,0),
"MONTH_NUMBER" NUMBER(2,0),
"MONTH_TEXT" VARCHAR2(12 BYTE),
"QUARTER" NUMBER(1,0),
"YEAR" NUMBER(2,0)
)
is this what it should look like?
What system are you using to create this table? NUMBER is not a SQL Server data type.
EDIT: And SQL does not use double-quotes like that. In fact, there are no qualifiers for column names.
January 6, 2012 at 8:23 am
Here's a link to Books Online for creating tables in SQL Server:
January 6, 2012 at 8:36 am
sql developer in fact I just copy the syntax, i know that the quataion marks are not allow
New kid on the block
January 6, 2012 at 8:38 am
Function F_TABLE_DATE is a calendar function that returns a table containing a variety of attributes of all dates from @FIRST_DATE through @LAST_DATE. It is meant to be used to populate a calendar table, but can also be used directly in place of a table.
Date Table Function F_TABLE_DATE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Your code indicates that you may not be using Microsoft SQL Server, so if you are using a different RDMS, like Oracle, you should post your question on a forum that answers questions for that RDMS.
January 6, 2012 at 8:58 am
Delario (1/6/2012)
sql developer in fact I just copy the syntax, i know that the quataion marks are not allow
It's more than just the quotations. NUMBER is an invalid data type and BYTE is also an invalid keyword in this context. And if you intended another data type that uses decimal places (which I assume you did given your definition of precision and scale) why would you do that? It's completely unnecessary to have precision and scale to define a month number and a day number.
The syntax you copied is entirely wrong for T-SQL. It doesn't even look right for MS Access. If you're using SQL Server, stop using whatever source you got that code from. They don't know what they're doing... Unless they're using a different system than SQL Server.
January 6, 2012 at 9:05 am
thank for the tips, I am new in this field
New kid on the block
January 6, 2012 at 9:35 am
Brandie Tarvin (1/6/2012)
Delario (1/6/2012)
sql developer in fact I just copy the syntax, i know that the quataion marks are not allowIt's more than just the quotations. NUMBER is an invalid data type and BYTE is also an invalid keyword in this context. And if you intended another data type that uses decimal places (which I assume you did given your definition of precision and scale) why would you do that? It's completely unnecessary to have precision and scale to define a month number and a day number.
The syntax you copied is entirely wrong for T-SQL. It doesn't even look right for MS Access. If you're using SQL Server, stop using whatever source you got that code from. They don't know what they're doing... Unless they're using a different system than SQL Server.
Answering your question... syntax is consistent with either Oracle 10g or Oracle 11g.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 6, 2012 at 11:31 am
PaulB-TheOneAndOnly (1/6/2012)
Brandie Tarvin (1/6/2012)
Delario (1/6/2012)
sql developer in fact I just copy the syntax, i know that the quataion marks are not allowIt's more than just the quotations. NUMBER is an invalid data type and BYTE is also an invalid keyword in this context. And if you intended another data type that uses decimal places (which I assume you did given your definition of precision and scale) why would you do that? It's completely unnecessary to have precision and scale to define a month number and a day number.
The syntax you copied is entirely wrong for T-SQL. It doesn't even look right for MS Access. If you're using SQL Server, stop using whatever source you got that code from. They don't know what they're doing... Unless they're using a different system than SQL Server.
Answering your question... syntax is consistent with either Oracle 10g or Oracle 11g.
Ah. Thank you for the clarification, Paul. I appreciate it.
January 7, 2012 at 4:04 am
Brandie Tarvin (1/6/2012)
PaulB-TheOneAndOnly (1/6/2012)
Brandie Tarvin (1/6/2012)
Delario (1/6/2012)
sql developer in fact I just copy the syntax, i know that the quataion marks are not allowIt's more than just the quotations. NUMBER is an invalid data type and BYTE is also an invalid keyword in this context. And if you intended another data type that uses decimal places (which I assume you did given your definition of precision and scale) why would you do that? It's completely unnecessary to have precision and scale to define a month number and a day number.
The syntax you copied is entirely wrong for T-SQL. It doesn't even look right for MS Access. If you're using SQL Server, stop using whatever source you got that code from. They don't know what they're doing... Unless they're using a different system than SQL Server.
Answering your question... syntax is consistent with either Oracle 10g or Oracle 11g.
Ah. Thank you for the clarification, Paul. I appreciate it.
Even if the syntaxes are aligned to Oracle, Delario’s requirement is technology independent. He is here to buy a concept of ‘Time Dimension’ which Paul has very well served.
@Delario: You were lucky this time but you should take care while questioning (specifically code help) in a forum. It creates unnecessary confusion. It also reduces the probability to get correct response from the volunteers in forums.
January 7, 2012 at 4:07 am
thanks Dev!
New kid on the block
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply