Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2007
    Posts
    97

    How to implement time dimension

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    between functions eh...

    What's wrong with using:

    [2] > [1] AND [2] < [3
    Basically:
    [3] > [2] > [1]
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2007
    Posts
    97
    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".

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  5. #5
    Join Date
    Mar 2007
    Posts
    97
    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.

  6. #6
    Join Date
    Mar 2007
    Posts
    97
    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?

  7. #7
    Join Date
    Mar 2007
    Posts
    97
    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
    Last edited by silas; 03-28-07 at 10:26.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  9. #9
    Join Date
    Mar 2007
    Posts
    97
    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
    Attached Thumbnails Attached Thumbnails timeline.png  

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •