Hi all,

I usually work a lot with MSAS, but i was asked to help out on a DWH
project on Oracle Olap 10g. I've been looking and reading for about a
week or so, but i'm still new at this technology and i could use some
pointers here.

Now, the model i was asked to scrutenize has 6 dimesnions. I've done
some size calculations it (i got that off Rittman's site) and this told
me the cube would be around 35 Billion terabytes big. So we need to cut
down on the number of dims by putting them together.

Let's consider this case:
Two of the characteristics are client industry and client credit
rating. I would like to put these into the same dimension to keep the
cube dense, but i need to be able to create a crosstab report that will
put the credit rating on columns and the industry on rows.

--- credit rating --
--industry-- poor weak good
manufacturing 100 200 300
oil 400 500 600
banking 700 800 900

Will Oracle OLAP let me do that? Discoverer will only let me put 1
hierarchy (not even a level) per dimension into a report. This puts me
in trouble because they are both levels of the same dimension.

I know AS2K won't let me do this straight away, but there i could make
virtual dimensions for industry and credit rating, create an MDX query
that stacks them both on rows.
manufacturing weak 100
manufacturing poor 200
manufacturing good 300 etc etc

Now i could crosstab these in Reporting services to get the right
format.

So my question:
* What front ends to Oracle olap will let me crosstab levels (or even
hierarchies) from the same dimension? Which would let me do this in the
designer, or maybe let me issue a OLAP DML query and then crosstab
them?
* Can i query a Oracle Olap cube by selecting levels or can i only
select hierarcies?

Hope i'm making sense here, if not, please let me know, so i can try
again.

Thanks in advance,

Gert-Jan

Hi Gert-Jan

I am not sure what information you read, but I think it very unlikely
that your cube would be as big as that.

A common mistake is to assume that the size of a cube will be simply the
number of logical cells in the cube (ie the product of all the dimension
intersections) multiplied by the size of each cell in bytes. You have
to consider sparsity. And as the dimensionality of a measure
increases, so does its sparsity. If the engine you are uses deals with
that sparsity well, it doesn't need to be a massive problem. The OLAP
Option of the Oracle 10g Database introduced internationally patented
technology that handles large volumes of sparse data extraordinarily
well. You don't say how many input rows you have, or the size of your
dimensions or the hardware you have, so hard for me to advise here, but
it is unlikely that reducing the number of dimensions to less than 6 is
needed for your analysis need.

My guess is that you either have not considered sparsity, or have not
estimated it/calculated it correctly. Oracle database is renowned for
its ability to handle mult-terabyte DWs, but the largest independently
verified database in the world according to last years Winter Corp
Survey of VLDBs was 'only' 100TB. We probably have a few years to wait
for the thousands of terabytes data warehouse. Thus, I think your
sizing calc is wrong.

Please contact me offline. I will be very happy to put you in touch
with folk in your country (NL?) who could help you exploit the OLAP
Option in your Oracle Data Warehouse.

In the meantime, you should check out SPARSITY_ADVICE_TABLE and ADVISE
_SPARSITY, which are subprograms of the the DBMS_AW package in Oracle
Database 10g. The SPARSITY_ADVICE_TABLE procedure creates a table for
storing the advice generated by the ADVISE_SPARSITY procedure. The
ADVISE_SPARSITY procedure will run a series of queries against your data
and make recommendations about how to configure your aggregation rules
and cube storage settings.

Your other 2 questions :

"What front ends to Oracle olap will let me crosstab levels (or even
hierarchies from the same dimension? Which would let me do this in the
designer, or maybe let me issue a OLAP DML query and then crosstab
them?"
This is pretty straightforward in tools like Oracle BI Enterprise
Edition, and non-Oracle BI tools like Business Objects, plus other SQL
based tools. To do it in Discoverer Plus OLAP you would need to
expose the levels as dimensions, and have a measure or a calculated
measure/formula that would break out the AW data in the way that you
want to visualise it. So front-end tools are fine. The first case
(measure) can be done via point and click in Oracle AW Manager or Oracle
Warehouse Builder 10gR2. The second method (calculated measure) would
require you to write a simple OLAP DML calculation. I'd probably go the
first way for simplicity unless you need to eek out the very best
build/agg performance or you are very constrained on hardware, in which
case leaving the multidimensional engine inside Oracle to do it on the
fly would be worth trying.

"Can i query a Oracle Olap cube by selecting levels or can i only
select hierarcies?"
Yes, ofcourse. If you are using a tool like Oracle Discoverer Plus
OLAP or the Excel Addin, then the Query Builder functionality includes
this on the 'Coinditions' tab for the dimension you are filtering. If
you are accessing the AW via a SQL query, then you can use a WHERE
filter on the level of each dimension.

Hope it helps. Kevin @ Oracle.

gjvdkamp (AT) gmail (DOT) com wrote: