| Ranges as dimensions in a crosstab/data cube? [message #247927] |
Sat, 14 July 2007 13:09  |
Eclipse User |
|
|
|
Originally posted by: allevato.vt.edu
Is it possible to use a datetime range (or any other range, really) as
a dimension for a crosstab/data cube?
In terms of the Classic Models sample database, there is a table column
for ORDERDATE. It's easy to create a new group from this field in the
data cube, and then populate a crosstab where each row is an ORDERDATE,
and a detail column is the number of orders on that date.
But is there a way to make each row a -range- of time? For example:
Range Number of orders in that range
------------------------------------------------------------ --------------------------
Jan
1, 00:00 to 11:59 x1
Jan 1, 12:00 to 23:59 x2
Jan 2, 00:00 to 11:59 x3
....
(I'm using a granularity of 12-hour ranges because we need to be able
to specify those instead of just day, month, year, etc.)
It looks like selecting "year", "month", "week-of-month", etc. would be
a good starting point when setting up the group level, but I'm not sure
how to get more flexibility out of that (like "12-hour blocks"). Can
anyone help?
Then, once that crosstab is set up, is it possible to make a chart from
this? It doesn't look like I can use a data cube dimension as an axis
on a chart -- what we would really like to be able to do, for example,
is create a bar chart where each bar is one of those 12-hour ranges,
and the Y-axis is from one of the measures.
We're using a custom data source to access our database, so we've been
fudging it so far by using scripted data sets w/ POJO to access the
data, then preprocess it in the script, but the crosstab support in 2.2
looks like it could free us from this.
Thanks!
- Tony Allevato
|
|
|
| Re: Ranges as dimensions in a crosstab/data cube? [message #247938 is a reply to message #247927] |
Sat, 14 July 2007 19:30  |
| Eclipse User |
|
|
|
Hi Tony,
Fisrt it's sure to use datetime, date and time range as a dimension for
crosstab.
The default type of ORDERDATE is Date, so you should define a computed
column in dataset: expression is row["ORDERDATE"] type is Date Time or
Time. Then using them in your cube.
If you want display like:
Jan 1, 00:00 to 11:59 x1
Jan 1, 12:00 to 23:59 x2
Jan 2, 00:00 to 11:59 x3
you should make sure that the ORDERDATE dimension is a Regular group but
not a Date group. Because the Date group can not set the static level. Do
you know how to set the regular dimension? In cube builder, when you drag
ORDERDATE column into Groups, there will be a selection dialog pops up,
you just chose the regular group is ok. Then you can define your static
levels to display x1, x2 and x3. There is another way but now we didn't
open this feature now, the date level will suppport user to define its
interval(range), but the display is only the number of hour but not 11:59.
see bug
#189340(https://bugs.eclipse.org/bugs/show_bug.cgi?id=189340)
Second, chart can not using the cube's dimension as an axis, chart only
can use the dataset as its data source, please see comments #3 in
bug#192853.
Regards!
|
|
|
Powered by
FUDForum. Page generated in 0.02917 seconds
Home