Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question Unanswered: OLAP time dimension

    Hello people,

    I would like to know how to create a dimension for time. I need to dimension my tables by year,quarter,month,week but not sure how to do it (conceptually)...
    Do I need to build a set of hierarchical tables to represent the calendar:
    tblYears
    |-> tblQuarters
    |-> tblMonths
    |-> tblWeeks

    ???? I'm confused as to how this works ??? Surely Oracle has some way of declaring a time related dimension without having to build this structure...

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    First, let us know WHY you want to create a dimension, what is the purporse you're seeking with this.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    I want Oracle to QueryRewrite to the correct materialized view when the sql is run that requests a sum/count for a period.

    My understanding is that using the dimension will tell oracle that even though I am grouping by trunc(txdate, 'year'), the precomputed monthly dates exist in materialized vew monthly_figures_mv and the query should use the data there to sum up to year level rather than going to the base tables. I suppose it's a performance issue at the end of the day.

    The questions always asked are how much monthly, quarterly, anually per location/department/product/status etc....

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Thanks for that.

    You can find an excellent example of exactly what you're trying to achieve on the link below.

    http://asktom.oracle.com/pls/asktom/...47464735113741

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Cool! Thanks... that is a perfect example of what I am trying to accomplish. Thanks

  6. #6
    Join Date
    Sep 2009
    Posts
    2

    Time dimension

    Hi,
    you need to define a function for getting the value of year month and quarter.Maybe you can use a tool like profobi,You can build your time dimension only by draging.
    ProfoBI - a simple and powerful data analysis tool - OLAP,Local Cube,BI,PivotTable,Dashboard,DW

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I doubt OP has been waiting for THREE years for your response.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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