If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > create dimension

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,375
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,375
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #7 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,375
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On