A reader, September 01, 2002 - 1:59 pm UTC
Hi Tom,
Could you please,please explian DIMENSION with simple words by giving Simple example.
When even I see Fine grain topic(FGAC) OR dimension, I get confued.
I know dimension is used with materialized view.
I will appreciate your response.
Thanks,
querying wuth time intervals
A reader, November 25, 2002 - 4:23 am UTC
hi
I was wondering if we could dividde a resultset into time intervals without using dimensions
For example my emp table with hiredate and I want to find out how many employees I hired per week and month in last year how could we do it without using dimensions in a SQL query (not using PL/SQL)?
normally I would do
select count(*) from emp where hiredate between x and y
in my case x and y would be dynamic (from day 1 to day 365 in a year)
cheers
November 25, 2002 - 7:32 am UTC
sure. first, you could run two queries
select trunc(hiredate,'mm'), count(*) from emp group by trunc(hiredate,'mm');
would give you counts by month. change 'mm' to 'w' and you have by week.
In 8iR2 and up, you can use group by cube to get the same in a single result:
scott@ORA920.US.ORACLE.COM> select trunc(hiredate,'mm') month, trunc(hiredate,'w') week, count(*)
2 from emp
3 group by cube(trunc(hiredate,'mm'), trunc(hiredate,'w'))
4 having (grouping( trunc(hiredate,'mm') ) = 1 and grouping( trunc(hiredate,'w') ) = 0) OR
5 (grouping( trunc(hiredate,'mm') ) = 0 and grouping( trunc(hiredate,'w') ) = 1)
6 /
MONTH WEEK COUNT(*)
--------- --------- ----------
15-DEC-80 1
15-FEB-81 1
22-FEB-81 1
01-APR-81 1
01-MAY-81 1
08-JUN-81 1
08-SEP-81 1
22-SEP-81 1
15-NOV-81 1
01-DEC-81 2
22-JAN-82 1
15-APR-87 1
22-MAY-87 1
01-DEC-80 1
01-FEB-81 2
01-APR-81 1
01-MAY-81 1
01-JUN-81 1
01-SEP-81 2
01-NOV-81 1
01-DEC-81 2
01-JAN-82 1
01-APR-87 1
01-MAY-87 1
24 rows selected.
And in 9iR1 and up, you can use grouping sets to get the same but more efficiently (it doesn't build the entire CUBE and then just select the rows you want as above -- rather it only builds the two combinations we are interested in)
scott@ORA920.US.ORACLE.COM>
scott@ORA920.US.ORACLE.COM> select trunc(hiredate,'mm') month, trunc(hiredate,'w') week, count(*)
2 from emp
3 group by grouping sets(trunc(hiredate,'mm'), trunc(hiredate,'w'))
4 /
MONTH WEEK COUNT(*)
--------- --------- ----------
01-DEC-80 1
01-FEB-81 2
01-APR-81 1
01-MAY-81 1
01-JUN-81 1
01-SEP-81 2
01-NOV-81 1
01-DEC-81 2
01-JAN-82 1
01-APR-87 1
01-MAY-87 1
15-DEC-80 1
15-FEB-81 1
22-FEB-81 1
01-APR-81 1
01-MAY-81 1
08-JUN-81 1
08-SEP-81 1
22-SEP-81 1
15-NOV-81 1
01-DEC-81 2
22-JAN-82 1
15-APR-87 1
22-MAY-87 1
24 rows selected.
Used this
A reader, November 25, 2002 - 12:04 pm UTC
Hi
I ended up using following query (groupping by to_char(f_apertura, 'IYYY')||to_char(f_apertura, 'IW')), thank you very much for the reply
select 'Semana entre '||primer_dia_semana||' y '||ultimo_dia_semana semana,
cola_ultima, num_dias_revisada,
count(dias_revisada) numero_casos
from (select f_apertura, COLA_ULTIMA, trunc(f_apertura, 'iw') primer_dia_semana,
trunc(f_apertura, 'iw')+6 ultimo_dia_semana,
case
when F_ULTIMA - F_APERTURA > 20 then 'Mas de 20 dias'
when F_ULTIMA - F_APERTURA > 10 then 'Entre 10 dias y 20 dias'
when F_ULTIMA - F_APERTURA > 5 then 'Entre 5 dias y 10 dias'
when F_ULTIMA - F_APERTURA > 1 then 'Entre 1 dia y 5 dias'
when F_ULTIMA - F_APERTURA > 1/12 then 'Entre 2 horas y 24 horas'
when F_ULTIMA - F_APERTURA > 1/24 then 'Entre 1 hora y 2 horas'
when F_ULTIMA - F_APERTURA > 0 then 'Menos de 1 hora'
when F_ULTIMA - F_APERTURA = 0 then 'En el momento'
else to_char(F_ULTIMA - F_APERTURA)
end num_dias_revisada,
F_ULTIMA - F_APERTURA dias_revisada
from clarify.CASOS_COLA_TIEMPO_CAUP)
group by cola_ultima, num_dias_revisada, to_char(f_apertura, 'IYYY')||to_char(f_apertura, 'IW'),
primer_dia_semana, ultimo_dia_semana
order by primer_dia_semana;
dimensions vs dimension tables
A reader, November 19, 2003 - 11:04 am UTC
Hi
We can do
create dimension in oracle to add a time dimension
we can also create a plain table for time dimension
Which is more flexible? Performance wise?
Also is Oracle dimensions same concept as Datawarehouse dimensions? (Star Model)
Tom please provide simple exmaple to understand concept of dimensions ( practical example)
Nonu, May 18, 2004 - 2:54 am UTC
Dear Tom
Can you please provide an example to exact use/concept of dimensions?
May 18, 2004 - 12:08 pm UTC
search for
'create dimension' materialized
on this site. or if you have "Expert One on One Oracle" -- i have the multi-page example in there as well.
Dimension changing in time.-
Mariano, May 16, 2005 - 2:46 pm UTC
Hi Tom.
I have a fact table where I collect the detail of some transactions by user.
I'm building a mv in order to retrieve faster the statistics of every user performance per day.
This mv, of course, will contain only aggregate information.
Because the information will be use to retrieve results aggregated by day, week or month I'm adding a time dimension in order to take advantage of query rewrite.
The doubt I have is the following:
Every user in this fact table has a supervisor and every supervisor has a manager. They asked me they want to have information not only by time interval but as "hierachical level" as well.
I'm thinking to use another dimension with the following relations: user rolls up to supervisor and supervisor rolls up to manager.
However these relations can change from now to then and not always an user will have the same supervisor.
In the table where I store this relations I have two date columns with the start date and end date of the relations. The thing here is that I don't know how to write the dimension for it to realize this changing relation and aggregate the info using the right level on behalf of the period is being query.
How can I achieve this?
As always, any advice is thankfully welcome.
Regards
May 16, 2005 - 4:59 pm UTC
so it is user+date rolls to super+date rolls to manager+date
when you sum up the details, you'll register this information as well.
easier than start/end dates wouldn't it be?
Dimension changing in time.-
Mariano, May 16, 2005 - 5:26 pm UTC
Totally.
But...
I was trying to avoid the need for updating extra columns with the relations just leaving the user_id value in the vm and getting the relations from the dimension table. Since someone can change a relation now that happened months ago I thought that being user_id the only value that will not change I can use dimensions to rollup aggregates with the rest of the employees.
Anyway, I have and additional date column that indicates when the modification was made so I guess I'll have to deal with it.
By the way, one of your coworkers in Oracle was writing a book on "Oracle Linux Administration", I saw it in Amazon but the book was not release on that time and I totally forgot his name (dificult surname, seems dutch to me) and cannot find it now. Any chance you know who I'm talking about?
Just can't wait to own and read Export 1 on 1 second edition.
Regards
May 16, 2005 - 5:48 pm UTC
dimension tables vs oracle dimensions
A reader, June 07, 2005 - 12:46 pm UTC
Hi
I would like to know what is better, Oracle Dimensions (create dimension) or a plain table with calendar dates? Considering I am not using Materialized Views.
June 07, 2005 - 1:17 pm UTC
well, you need a table to have an "oracle" dimension, they are not "replacements" for eachother.
An Oracle dimension is just more meta-data.
arent oracle dimension and dimension table the same?
A reader, June 07, 2005 - 2:34 pm UTC
hi
arent oracle dimension and dimension table the same?
June 07, 2005 - 2:40 pm UTC
click on some of the links above and read about them.
Dimension's levels
Franco Perego, November 29, 2005 - 12:09 pm UTC
Hi Tom,
I would like to develop a Dimension based on a "tree" table,
I can query the table using the "connect by prior ... start with", which means that the level of aggregation can vary from 1 for a specific record to more than 10 for another. Is it possible to create a dimension for these data?
Thanks in advance,
Franco
November 30, 2005 - 10:49 am UTC
not that I am aware of, no. dimensions are sort of "flat" structures.