| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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):
YEAR MONTH WEEK DAY DATE 2003 1 1 1 2003/01/01 2003 1 1 2 2003/01/02 2003 4 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 <ORACLE-L_at_fatcity.com>
<Rajendra.Jamadagn cc:
i_at_ESPN.COM> Subject: RE: Using dimensions
Sent by:
ml-errors_at_fatcity.
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:srcdco_at_rit.edu]
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 (srcdco_at_rit.edu)
(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:Rajendra.Jamadagni_at_espn.com]
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 !
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the official business of Transurban Infrastructure Developments Limited and CityLink Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au 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: ListGuru_at_fatcity.com (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).Received on Mon Sep 29 2003 - 20:24:35 CDT
 |
 |