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 <[EMAIL PROTECTED]>
<Rajendra.Jamadagn cc:
[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 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 !
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
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: [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).
