Re: Using dimensions
Title: Using dimensions Query rewriting to use materialized views requires dimensions to be defined. Tanel. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, September 29, 2003 7:14 PM Subject: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scannedTFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Scott Canaan [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AMTo: Multiple recipients of list ORACLE-LSubject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
Re: Using dimensions
Raj, what does TFM STFW mean ?!! let me also get used to the list's acronyms :) Jp. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Using dimensions
google gave me for example such an address http://www.gaarde.org/acronyms/ I'm sure one of hundreds or even thousands -Original Message- From: Prem Khanna J [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 30, 2003 12:09 PM To: Multiple recipients of list ORACLE-L Subject: Re: Using dimensions Raj, what does TFM STFW mean ?!! let me also get used to the list's acronyms :) Jp. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gints Plivna INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Using dimensions
OwwThank God Raj hasn't replied yet :-) Thanx Gints. Jp. 30-09-2003 18:24:28, Gints Plivna [EMAIL PROTECTED] wrote: google gave me for example such an address http://www.gaarde.org/acronyms/ I'm sure one of hundreds or even thousands -Original Message- From: Prem Khanna J [mailto:[EMAIL PROTECTED] Raj, what does TFM STFW mean ?!! let me also get used to the list's acronyms :) Jp. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Prem Khanna J INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Using dimensions
Title: Re: Using dimensions Query rewrite from materialized views does not require dimensions; they are only used in certain fairly obscure situations. on 9/30/03 1:49 AM, Tanel Poder at [EMAIL PROTECTED] wrote: Query rewriting to use materialized views requires dimensions to be defined. Tanel. - Original Message - From: Jamadagni, Rajendra mailto:[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 7:14 PM Subject: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Scott Canaan [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AM To: Multiple recipients of list ORACLE-L Subject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) .. Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: RE: Using dimensions
Title: RE: RE: Using dimensions I am out sick today ... glad you found the acronyms ... the F is left to your own imagination. Raj -Original Message- From: Prem Khanna J [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 30, 2003 5:45 AM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Using dimensions OwwThank God Raj hasn't replied yet :-) Thanx Gints. Jp.
RE: Using dimensions
Title: Using dimensions Got a link for this requirement in the manual? Raj -Original Message-From: Tanel Poder [mailto:[EMAIL PROTECTED]Sent: Tuesday, September 30, 2003 4:50 AMTo: Multiple recipients of list ORACLE-LSubject: Re: Using dimensions Query rewriting to use materialized views requires dimensions to be defined. Tanel. - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, September 29, 2003 7:14 PM Subject: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scannedTFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Scott Canaan [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AMTo: Multiple recipients of list ORACLE-LSubject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Using dimensions
Title: Using dimensions
Hm, I did a brief check and it seems I was a bit
wrong:
Dimensions are requiredonly when you want
query rewrite to use materialized views computed on other
constraintsrelationships than primary/foreign/unique keys.If you want
to use your custom relationships in rewrites, you have to use
dimensions.
That's at least what I understood from
docs:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/qr.htm#721
Check"trusted" parameter under "Accuracy of
query rewrite"
Sorry for misinformation and if you
getdifferent understandingdocs, please post it here :)
Tanel
- Original Message -
From:
Jamadagni, Rajendra
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, October 01, 2003 3:59
AM
Subject: RE: Using dimensions
Got
a link for this requirement in the manual?
Raj
-Original Message-From: Tanel Poder
[mailto:[EMAIL PROTECTED]Sent: Tuesday, September 30, 2003
4:50 AMTo: Multiple recipients of list
ORACLE-LSubject: Re: Using dimensions
Query rewriting to use materialized views
requires dimensions to be defined.
Tanel.
- Original Message -
From:
Jamadagni, Rajendra
To: Multiple recipients of list
ORACLE-L
Sent: Monday, September 29, 2003 7:14
PM
Subject: RE: Using dimensions
Thanks Scott, okay lets forget OLTP .. but I haven't seen any
_actual_ uses of dimensions ... where does one use them? in SQLs?
I have scannedTFM, but haven't STFW'd yet ... scared of too
many hits.
Thanks
Raj
Rajendra dot Jamadagni at nospamespn
dot com All Views expressed in
this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-From: Scott Canaan
[mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 11:55
AMTo: Multiple recipients of list ORACLE-LSubject:
RE: Using dimensions
Dimensions are
data warehouse constructs. They are implemented as tables in the
database, but have the characteristic of a hierarchy that can be
traversed. For example: a time dimension can have the
hierarchy of date, day, week, month, quarter, year, decade,
century. This is used for rollup reporting within the data
mart. I don't see any good use of it in an OLTP environment, but I
may be wrong.
Scott Canaan
([EMAIL PROTECTED])
(585) 475-7886
"Life is like a sewer, what you get
out of it depends on what you put into it." - Tom
Lehrer.
-Original
Message-From:
Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003
10:55 AMTo: Multiple
recipients of list ORACLE-LSubject: Using
dimensions
I have tried, but
haven't found a good example of how to _use_ a dimension in 9ir2. I
defined one, but then sat clueless on what to do with it. Is it any good
in an OLTP environment? (I smell the answer is a NO, but still)
...
Any notes from your
experience?
TIA
Raj
Rajendra dot
Jamadagni at nospamespn dot com All Views expressed
in this email are strictly personal. QOTD: Any clod can
have facts, having an opinion is an art !
RE: Using dimensions
Title: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I dont see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AM To: Multiple recipients of list ORACLE-L Subject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: Using dimensions
Title: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scannedTFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Scott Canaan [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AMTo: Multiple recipients of list ORACLE-LSubject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
Re: RE: Using dimensions
i think there are examplse in the datawarehouse doc on otn. there is an oracle datawarehouse book that has come recommended(havent read it). tim gorman wrote that one right? Id bet its in there too. you use dimensions with star schema's right? From: Jamadagni, Rajendra [EMAIL PROTECTED] Date: 2003/09/29 Mon PM 12:14:39 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- Sent: Monday, September 29, 2003 10:55 AM To: Multiple recipients of list ORACLE-L I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! Title: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scannedTFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Scott Canaan [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AMTo: Multiple recipients of list ORACLE-LSubject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: Using dimensions
Raj - Actually a good place to start is the Oracle9i Data Warehousing Guide http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical. htm#97703 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical .htm#97703 I think it is important for all Oracle DBAs to understand the fundamental concepts behind DW. You never know when you'll get yanked into a meeting where they are discussing it and you don't want to say something Neanderthal like of course we'll fully normalize the schema. Ralph Kimball has some good materials on his web site: http://www.ralphkimball.com http://www.ralphkimball.com Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 11:15 AM To: Multiple recipients of list ORACLE-L Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- Sent: Monday, September 29, 2003 10:55 AM To: Multiple recipients of list ORACLE-L I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Using dimensions
Title: Using dimensions I`ve not used dimensions in Oracle only read about them. But I have designed DW using dimensional modeling. So from what I've read in the doc, Oracle will used the dimensions to be better atquery rewriting because it knows the hierarchy of the data (example : neighbourhood, city, region, province, country) Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]Sent: Monday, September 29, 2003 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: Re: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scannedTFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Scott Canaan [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AMTo: Multiple recipients of list ORACLE-LSubject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) .. Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !
RE: Using dimensions
Title: RE: Using dimensions Thanks Dennis ... We have a group who design their application as if this is a DW environment, so I thought learning this stuff might be beneficial for me. I'll check it out. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] Sent: Monday, September 29, 2003 2:25 PM To: Multiple recipients of list ORACLE-L Subject: RE: Using dimensions Raj - Actually a good place to start is the Oracle9i Data Warehousing Guide http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical. htm#97703 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical .htm#97703 I think it is important for all Oracle DBAs to understand the fundamental concepts behind DW. You never know when you'll get yanked into a meeting where they are discussing it and you don't want to say something Neanderthal like of course we'll fully normalize the schema. Ralph Kimball has some good materials on his web site: http://www.ralphkimball.com http://www.ralphkimball.com Dennis Williams This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: RE: Using dimensions
so basically your saying you use dimensions hand in hand with materialized views. do they have other uses? From: Stephane Paquette [EMAIL PROTECTED] Date: 2003/09/29 Mon PM 02:39:43 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Using dimensions Using dimensionsI`ve not used dimensions in Oracle only read about them. But I have designed DW using dimensional modeling. So from what I've read in the doc, Oracle will used the dimensions to be better at query rewriting because it knows the hierarchy of the data (example : neighbourhood, city, region, province, country) Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Monday, September 29, 2003 2:20 PM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Scott Canaan [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AM To: Multiple recipients of list ORACLE-L Subject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! Title: Using dimensions I`ve not used dimensions in Oracle only read about them. But I have designed DW using dimensional modeling. So from what I've read in the doc, Oracle will used the dimensions to be better atquery rewriting because it knows the hierarchy of the data (example : neighbourhood, city, region, province, country) Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]Sent: Monday, September 29, 2003 2:20 PMTo: Multiple recipients of list ORACLE-LSubject: Re: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scannedTFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Scott Canaan [mailto:[EMAIL PROTECTED]Sent: Monday, September 29, 2003 11:55 AMTo: Multiple recipients of list ORACLE-LSubject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I
RE: Using dimensions
Raj - There is some melding of DW and OLTP environments at some sites today. Most DW's are separate environments with data extracted from the OLTP on a regular batch schedule. But it is possible to have a combined environment that satisfies both requirements, especially if you are starting from scratch and not trying to add DW to an existing system. If your users require real-time DW queries, this may be a better alternative. The biggest problem I've heard is the Oracle buffer cache. After it has been running awhile the OLTP buffers will tend to hold the hot blocks. By definition, a DW doesn't have hot blocks, so if both OLTP and DW share the same instance, the DW will tend to mess up the buffer cache for OLTP, so OLTP performance will suffer. Of course, a combination schema design may satisfy nobody, but we DBAs are used to dealing with unhappy people. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 1:50 PM To: Multiple recipients of list ORACLE-L Thanks Dennis ... We have a group who design their application as if this is a DW environment, so I thought learning this stuff might be beneficial for me. I'll check it out. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- mailto:[EMAIL PROTECTED] ] Sent: Monday, September 29, 2003 2:25 PM To: Multiple recipients of list ORACLE-L Raj - Actually a good place to start is the Oracle9i Data Warehousing Guide http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical . htm#97703 http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/logical .htm#97703 I think it is important for all Oracle DBAs to understand the fundamental concepts behind DW. You never know when you'll get yanked into a meeting where they are discussing it and you don't want to say something Neanderthal like of course we'll fully normalize the schema. Ralph Kimball has some good materials on his web site: http://www.ralphkimball.com http://www.ralphkimball.com http://www.ralphkimball.com http://www.ralphkimball.com Dennis Williams -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: RE: Using dimensions
Ryan, Stephane Here is the way I would put it, based on my admittedly small experience: - The in the star schema design, dimension hierarchy gives users a clearer way to select data. Often in the GUI screen they have pull-down boxes for the hierarchies. - The regularity of the star schema not only allows users to easier access, but for software tools like Oracle to implement performance features aimed at the star schema. For example, there is the STAR_TRANSFORMATION hint. Other Oracle features that are really useful in DW are bitmap indexes, materialized views, and partitioning. You may not use any of these on your DW, but if you have a tough performance problem, these can be really useful. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, September 29, 2003 2:55 PM To: Multiple recipients of list ORACLE-L so basically your saying you use dimensions hand in hand with materialized views. do they have other uses? From: Stephane Paquette [EMAIL PROTECTED] Date: 2003/09/29 Mon PM 02:39:43 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: RE: Using dimensions Using dimensionsI`ve not used dimensions in Oracle only read about them. But I have designed DW using dimensional modeling. So from what I've read in the doc, Oracle will used the dimensions to be better at query rewriting because it knows the hierarchy of the data (example : neighbourhood, city, region, province, country) Stephane Paquette Administrateur de bases de donnees Database Administrator Standard Life www.standardlife.ca Tel. (514) 499-7999 7470 and (514) 925-7187 [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of [EMAIL PROTECTED] Sent: Monday, September 29, 2003 2:20 PM To: Multiple recipients of list ORACLE-L Subject: Re: RE: Using dimensions Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Scott Canaan [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have the hierarchy of date, day, week, month, quarter, year, decade, century. This is used for rollup reporting within the data mart. I don't see any good use of it in an OLTP environment, but I may be wrong. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 10:55 AM To: Multiple recipients of list ORACLE-L Subject: Using dimensions I have tried, but haven't found a good example of how to _use_ a dimension in 9ir2. I defined one, but then sat clueless on what to do with it. Is it any good in an OLTP environment? (I smell the answer is a NO, but still) ... Any notes from your experience? TIA Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Using dimensions
I don't have experience with an Oracle Dimension as such, but plenty of experience with homemade dimensions within an Oracle database - Perhaps Oracle does some stuff for you. To give an example of how we would use them: Assume a time dimension with columns as follows (sample data below): YEARMONTH WEEKDAY DATE 20031 1 1 2003/01/01 20031 1 2 2003/01/02 20034 2 9 2003/04/09 Now if you have a fact table (say ORDERS) then you can join ORDERS.ORDER_DATE to TIME.DATE and restrict on other columns in the TIME table. For example: select year, month sum(order_total) from orders, time where orders.order_date = time.date and time.year = 2003 group by year, month We also used Organisational dimensions. For a company with several thousand cost centres this provides the ability to summarise and aggregate figures across the company at any level. Another dimension that used to cause grief was the Report dimension - the system I worked on had several thousand report lines in their various financial reports (balance sheet, cash flow, etc) and we then mapped around 80,000 financial accounts to these report lines to create an entire report structure. The funny thing - we used a further eight dimensions just to map account codes to the report lines - dimensions within dimensions. Add enough dimensions to a fact table in a star schema and the queries you can answer are enormous. Hopefully this gives you a feel of how to use dimensions. They have few uses except for reporting and maintaining them can be a headache within themself. I used to work a lot with time-variant dimensions where not only could you report on the organisational structure but also map data into the structure at any point in time. For example, we could take financial figures from years past and apply the organisational changes to the figures to report against today's structure. Sorry if I've confused you. Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Rajendra.Jamadagncc: [EMAIL PROTECTED] Subject: RE: Using dimensions Sent by: [EMAIL PROTECTED] com 30/09/2003 02:14 Please respond to ORACLE-L Thanks Scott, okay lets forget OLTP .. but I haven't seen any _actual_ uses of dimensions ... where does one use them? in SQLs? I have scanned TFM, but haven't STFW'd yet ... scared of too many hits. Thanks Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Scott Canaan [mailto:[EMAIL PROTECTED] Sent: Monday, September 29, 2003 11:55 AM To: Multiple recipients of list ORACLE-L Subject: RE: Using dimensions Dimensions are data warehouse constructs. They are implemented as tables in the database, but have the characteristic of a hierarchy that can be traversed. For example: a time dimension can have
