Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Oracle OLAP 10G questions

 
   Database Forums (Home) -> OLAP RSS
Next:  Perm Job Opportunity: Oracle BI Dataware House De..  
Author Message
gjvdkamp

External


Since: Oct 23, 2005
Posts: 4



(Msg. 1) Posted: Fri Jul 07, 2006 11:11 am
Post subject: Oracle OLAP 10G questions
Archived from groups: comp>databases>olap (more info?)

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

 >> Stay informed about: Oracle OLAP 10G questions 
Back to top
Login to vote
Kevin Lancaster

External


Since: Sep 06, 2005
Posts: 2



(Msg. 2) Posted: Thu Jul 20, 2006 12:20 am
Post subject: Re: Oracle OLAP 10G questions [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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.

wrote:
> 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
>

 >> Stay informed about: Oracle OLAP 10G questions 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
OLAP newbie - I've been given the task of designing and building a database system for a DVD rental store and the spec includes the need for management reporting for business analysis purposes. I am unfamiliar with OLAP but know enough to understand I have to..

Sr. OLAP/BI Architect - We are seeking a Sr. member for our BI Performance Management team in either Dallas, Atlanta or S. California. Qaulified candidates will have a passion for solving business problems and leading experts in BI and Data Warehousing. We were awarded a..

OLAP Beginner - I would really like to learn more about OLAP and how to use it. Can someone please help me? I do software testing and this is one of my test cases that I have to create. I would really appriecate it. Thanks.

Acronyms for OLAP, and DW - What follows are some acronyms intended to be humorous; I had only posted the one called junk before. junk Corporate Reporting, Analysis, and Planning s**t Storing Historical Information Technology FART Fast Analysis & Reporting Technology I ho...

Papers on OLAP - Earlier I had posted to the newsgroup that I was writing some papers on OLAP, and was going to put them on a web site. Due to lack of interest I have decided not to open a professional web site. I have only written 3 papers, and have used a free web..
   Database Forums (Home) -> OLAP All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]