Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2012
    Posts
    5

    Question create dimension

    hello, i want to do same like oracle "create dimension", exists somethig like that in db2?

    Dimensions

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,452
    A dimension seems to be a logical concept, so it's not immediately related to a database engine, like DB2 or Oracle Database. If you're looking for a tool for designing data warehouses, may be this will help: Design Studio
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Dec 2012
    Posts
    5
    no, is created in the database, for example (the classical example time/date):

    CREATE DIMENSION dwh.date_dimen
    level l_day is dim_date.date
    level l_month is dim_date.keymonth
    level l_year is dim_date.year
    hierarchy date_aux_roll_up(
    l_day child of
    l_month child of
    l_year)
    attribute l_day determines (date, day, dayname)
    attribute l_month determines (keymonth, month, monthname)
    attribute l_year determines (year, relativeyear)


    this create a hierarchy and is useful in a MV (Materialized View) for example:

    the MV has month and you made a select with the year attribute so the database motor with this dimension can see that month is child of year and then take the MV with the month attribute.

    PS: sorry my english

  4. #4
    Join Date
    Dec 2012
    Posts
    5
    ****ORACLE CREATE DIMENSION****
    Purpose

    Use the CREATE DIMENSION statement to create a dimension. A dimension defines a parent-child relationship between pairs of column sets, where all the columns of a column set must come from the same table. However, columns in one column set (called a level) can come from a different table than columns in another set. The optimizer uses these relationships with materialized views to perform query rewrite. The SQLAccess Advisor uses these relationships to recommend creation of specific materialized views.
    *******

    what i really want to do is something that helps the MV to do the query rewrite when I select a different attribute from a dimension that is being used in the MV

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,452
    In DB2 query rewrite happens automatically, if the optimizer find an MQT (materialized query table, same concept as Oracle's MVs) with the definition appropriate for the query against its base table(s). You don't need to create some "dimensions" for that.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Dec 2012
    Posts
    5
    Quote Originally Posted by n_i View Post
    In DB2 query rewrite happens automatically, if the optimizer find an MQT (materialized query table, same concept as Oracle's MVs) with the definition appropriate for the query against its base table(s). You don't need to create some "dimensions" for that.
    you say that DB2 somehow knows the hierarchy between attributes without mapping/dimension?:

    esample:

    1) i make a select with year
    2) the MV have month
    3) the motor have to know month is "child of" year without a dimension/maping defined somewhere


    PS: i know that db2 do something like that but in indexes like bitmap in oracle for example

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,452
    Quote Originally Posted by gangster View Post
    you say that DB2 somehow knows the hierarchy between attributes without mapping/dimension?:
    No, I didn't say that. In my view, the database engine has no business dealing with the _meaning_ of data -- it's the application layer that should be concerned with the model.

    So, no, DB2 does not do "dimensions". There are various applications from IBM and 3d parties that let you define and use metadata.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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