How to implement time dimension

silas · #1 (**permalink**) 03-24-07, 13:30

Hi there,
first of all, english is not my native language, so please excuse for possible mistakes.

I'm not so experienced in DB designing and have a question regarding how to implement a time dimension in a proper way.

Starting point is just one table:

Name: Sold_Objects

ID (as int)| Start_Date (as date)| End_Date (as date)| Price (as int)

My goal is a Pivot table (or something similar) in Excel with number of sold Objects in e.g. First Quarter 2006, Ferbruary to March 2005 or just 2006. Drilldown is most desired.
I do have start and end dates, because it's kind of auction. For a better understanding, I kept things quiete simple.

What would be a good proceeding? Dimension tables for Year,Quarter,Month?
I think a big Problem is that I have a timeline and pivot table in Excel doesn't give much options. especially no "in between" function.
I'm really stuck and would be very happy, if someone can give me a hint.

I use MS SQL Server 2000 (2005 at home for testing). Excel is connected via Analysis Server (Olap Cube).

Thanks a lot!!

Silas

gvee · #2 (**permalink**) 03-24-07, 20:45

between functions eh...

What's wrong with using:

[2] > [1] AND [2] < [3
Basically:
[3] > [2] > [1]

silas · #3 (**permalink**) 03-25-07, 03:39

Hi,

this looks like an SQL stement to me. But I can't use SQL in Excel pivot table. All I can do is to drag a dimension which will be concatenated automatically with "AND".

Pat Phelan · #4 (**permalink**) 03-25-07, 11:41

I'm a purist on analysis issues, so there might be simpler solutions than what I'm proposing, this is easy to do and almost infinitely flexible.

I would create a time dimension table. For each unit of time that interests you (typically a day), you'll want to include a row. The structure that I'd start with would be something like:

Code:

CREATE TABLE time_dimension (
   unit_begin		DATETIME		NOT NULL	-- Unit starts
,  unit_end		DATETIME		NOT NULL	-- Unit ends
,  display_as		DATETIME		NOT NULL	-- How do we display this unit
,  calendar_week	DATETIME		NOT NULL	-- Calendar breakdowns for this unit begin
,  calendar_month	DATETIME		NOT NULL
,  calendar_quarter	DATETIME		NOT NULL
,  calendar_year	DATETIME		NOT NULL
,  fiscal_week		DATETIME		NOT NULL	-- Fiscal breakdowns for this unit begin
,  fiscal_month		DATETIME		NOT NULL
,  fiscal_quarter	DATETIME		NOT NULL
,  fiscal_year		DATETIME		NOT NULL
   )

This allows you to break time into whatever units you see as interesting. For example, a tourist business might consider days important during their "busy" season, but only months important during their "off" season.

You can then join this dimension table against your fact table or tables to divide time up in whatever ways you find interesting. The outputs are always DATETIME values, but this table allows you to easily map incoming facts into aggregate levels that suit you. You can manipulate the resulting date values in the aggregate rows with almost any tool.

-PatP

silas · #5 (**permalink**) 03-26-07, 14:56

Hi PatP,

thanks for your reply. I'm not sure, if I really got you. The content of Time Table, would it look like this? :

Code:

Y_BEGIN;Q_BEGIN;Y_END;Q_END; YEAR; QUARTER
1999;1;1999;1;1999;1
1999;1;1999;2;1999;1
1999;1;1999;2;1999;2
1999;1;1999;3;1999;1
1999;1;1999;3;1999;2
1999;1;1999;3;1999;3
1999;1;1999;4;1999;1
1999;1;1999;4;1999;2
1999;1;1999;4;1999;3
1999;1;1999;4;1999;4
...
1999;1;2000;1;1999;1
1999;1;2000;1;1999;2
1999;1;2000;1;1999;3
1999;1;2000;1;1999;4
...

That would mean a lot of entries.

silas · #6 (**permalink**) 03-27-07, 10:10

I just found out how to fire up custom MDX strings in Excel. I'm not sure, if this is the best choice, but I'm in dire need of some results.
Do you know how to write such a restriction in MDX?

silas · #7 (**permalink**) 03-28-07, 09:10

Ok, I hope this is not going to be a monologue :-)

The following Query gives all active Objects in YEAR and QUARTER. Is it possible to transfer this behaviour to OLAP?

Code:

ALTER  Procedure [dbo].[test_procedure] (@YEAR char(4),@QUARTER char(1)) as
(
select * 
from dbo.objects  as o
where 
(o.year_start<@YEAR and o.year_end>@YEAR )
or 
(o.year_start<@YEAR and o.year_end= @YEAR and o.quarter_end>=@QUARTER )
or
(o.year_start=@YEAR and o.quarter_start<=@QUARTER and o.year_end>@YEAR)
or
(o.year_start=@YEAR and o.year_end=@YEAR and o.quarter_start<=@QUARTER and o.quarter_end>=@QUARTER )

)

TABLE Objects

Code:

ID   YEAR_START     QUARTER_START   YEAR_END     QUARTER_END      NAME
-----------------------------------------------------------------------------
1    1999              2             2000             1            A
2    1999              2             1999             3            B
3    1999              1             1999             4            C
4    1999              4             2000             3            E
5    2000              3             2005             4            F

Output from test_procedure(1999,4) is:

Code:

ID   YEAR_START     QUARTER_START   YEAR_END     QUARTER_END      NAME
-----------------------------------------------------------------------------
1    1999             2              2000             1            A
3    1999             1              1999             4            C
4    1999             4              2000             3            E

Pat Phelan · #8 (**permalink**) 03-28-07, 09:30

Sorry, I'm only getting on DBForums once or twice a day in my old age.

You are correct, there will be one row for every unit of time that you find "interesting" for your queries. It generates a lot of rows, but disk is cheap compared to sweating for hours, and queries actually run faster this way for us.

This sample is untested, composed on a tiny screen (less than QVGA), but it should give you the rough idea of what I'm suggesting:

Code:

DECLARE @d		DATETIME

SET @d = '1999-01-01'

WHILE @d < '2020-01-01'
   BEGIN
      INSERT INTO time_dimension (
         unit_begin, unit_end, display_as
,        calendar_week, calendar_month, calendar_quarter, calendar_year
,        fiscal_week, fiscal_month, fiscal_quarter, fiscal_year
         ) SELECT
         @d, DateAdd(ms, 86399997, @d), @d
,        Convert(CHAR(10), DateAdd(day, 1 - DatePart(dw, @d), @d), 121)
,        Convert(Char(8), Getdate(), 121) + '01'
,        Convert(CHAR(5), @d, 121) + CASE DatePart(quarter, @d) 
            WHEN 1 THEN '01-01' WHEN 2 THEN '04-01' WHEN 3 THEN '07-01' ELSE '10-01' END
,        Convert(CHAR(5), @d, 121) + '01-01'
,        Convert(CHAR(10), DateAdd(day, 1 - DatePart(dw, @d), @d), 121)
,        Convert(Char(8), Getdate(), 121) + '01'
,        Convert(CHAR(5), @d, 121) + CASE DatePart(quarter, @d) 
            WHEN 1 THEN '01-01' WHEN 2 THEN '04-01' WHEN 3 THEN '07-01' ELSE '10-01' END
,        Convert(CHAR(5), @d, 121) + '01-01'
      SET @d = DateAdd(day, 1, @d)
   END

-PatP

silas · #9 (**permalink**) 03-28-07, 13:20

Hi Pat,

I'm not sure we are not talking at cross purposes. Could you please take a look at the attachment.
There you see the lifetime of four objects. Each has a start and an enddate. The last Row shows the number of active Objects.
What I need is a way to get all active Objects from a specified Date.
If user selects Year 1999 and Q4, he gets Object Yellow, Red and Green.
For Year 1999 he gets Yellow,red,Blue and Green etc.

Do you think, there's a way to realize this in a Cube? What do you think of a view for every quarter, that includes all active objects? Same for years.

Silas

Pat Phelan · #10 (**permalink**) 03-29-07, 00:30

Ok, when you think of the cube, you have to realize that there will be N different sums for the aggregates: daily, weekly, monthly, quarterly, annually possibly doing them by both calendar and fiscal rollups. There isn't just one "sum" stored in the cube. Depending on what kind of questions you want to answer, you might have all N sums for every unit of "interesting" time. You can definitely end up with a LOT of numbers in a cube, but that is what makes it useful for analysis!!!

You can create multiple cubes, with different levels of aggregation if that is what you are trying to reach, then you need to decide where the objects "exist" in those cubes and write appropriate queries to aggregate them.

I'm awfully tired, so I'm going to stop babbeling for now.

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech