Hello,

if I store a cube as ROLAP I see the aggregates in my database.

If I store a cube as MOLAP I cannot see them.
I think the data and the aggregates are stored in a new database. (in SQL
Server?)
But I don't see them.

Who can help?

Thanks in advance,

Reiner

Hello,

if you use MOLAP, the data will be store on disk in the directory
...\Microsoft Analysis Services\Data\

Cordially

Patrice
"Reiner Wahnsiedler" <wahnsiedler (AT) t-online (DOT) de> wrote

ROLAP would store the data and aggregations in relational structures in a
relational database (Slower than MOLAP, Less Disk space needed)

MOLAP would store the data and aggregations in multi dimensional structure
generally under \Program Files\Microsoft Analysis Services\Data (Fastest,
More disk space)

HOLAP would store the data in a relational structure and aggregations in
Multi dimensional structures (Faster than ROLAP, Lesser disk space than
MOLAP)

Hope this helps

John

"Reiner Wahnsiedler" <wahnsiedler (AT) t-online (DOT) de> wrote

Yes MOLAP does make a copy of the data so it uses more disk space that
ROLAP, but it is much smaller than you think.
1) only the measure data actually used within the fact table is copied,
typically numeric data. If you have other fields in the table, we don't copy
them.
2) we used very specialized data structures that are bit-encoded, e.g.
dimension members and the ordinal address.
3) within the data, we combine what we call 'duplicate rows'. this means
that two rows in the fact table that have exactly the same lowest
coordinates, e.g. if you are recording point-of-sale data, by customer, by
store, by product, by day -- and if the same customer buys the same product
in the same day at the same store, then its rows in the RDBMS fact table are
combined into a single 'fact' in the MOLAP structures. Depending on the
application and its dimensionality, this can have a HUGE impact on reducing
the data.

Our typical experience is that the same data in your RDBMS, is 1/4 to 1/6
smaller in MOLAP, e.g. if you have 600GB then AS database is about
100-200GB.

With the performance benefits of MOLAP, we strongly recommend it as the
'storage-mechanism-of-choice' for most applications. Only consider ROLAP and
HOLAP if you have a very unique application.

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"John Thomas" <ptjohn (AT) hotmail (DOT) com> wrote

"Nigel Pendse" <nigelp.nospam (AT) compuserve (DOT) com> wrote


I strongly endorse Nigel's explanation that MOLAP takes less storage
than ROLAP.

I have got benchmarks we have done inhouse between SAP Business
Warehouse (BW) running on a multicluster Oracle 817 64 bit database on
IBM SP frames with over 20 GB RAM & 10 CPUs RISC processors
collectively betweeen the two servers. Some of the SAP BW cubes which
are all ROLAP cubes which take more than 20 GB storage on Oracle we
built the same cube on Microsoft 2000 OLAP Server the cube is about
1.5GB I cannot even remotely dream of browsing this cube on SAP BW. I
have sub second response times in browsing this prototype cube on MS
OLAP. I am running MS OLAP on Pentium III 1.5 Ghz with 256 MB RAM with
10GB IDE drive based system which is not even a server. So that must
be enough real proof to prove the storage, scalability and performance
of MOLAP vs ROLAP. SAP BW is a purely ROLAP solution. The comparison
of MS MOLAP to SAP BW was like a David and Goliath comparison. MS
MOLAP beat SAP BW pants down. The storage I have quoted above does not
include the space occupied by SAP BW aggregates which are nothing but
small ROLAP cubes storing the same subset of the data in the base
ROLAP cube redundantly if I had factored BW aggregates the size would
have been much higher. The size of the MS MOLAP cube I have quoted
includes the size of the aggregates with 50% performance optimisation
approximately with about 3500 plus MOLAP aggregates in the MOLAP sizes
I have indicated.

I have documented evidence of other cases a 2.5 GB SAP BW Infocube
when I moved it into MS OLAP cube is less than 450KB we have sent the
cube archive on emails between sites. I could not dream of doing this
on BW.


The business is so excited with this prototype that we are now
planning to pull all data to be archived from our SAP R/3 system and
dump them as MOLAP cubes on MS Analysis Services. We are seriously
looking at need of ROLAP solutions like SAP BW ??

Last but not the least I have a big winge against Microsoft's
marketing which is extremely bad the MS Analysis Services is such a
technology next only to Windows or MS Office or a web browser that we
discovered I feel Microsoft is not doing enough in making its existing
customer base aware of the power and capability of MS Analysis
Services and its power. Every company today as tonnes of data and do
not have access or aware of sensible technologies to process this huge
volume of data this is where we feel MS Analysis Services is a
exceptional resource in looking at this data. We were sold by SAP to
put all our non-SAP data in flat files and even load data in some of
our SQL Server databases into SAP BW and use SAP BW as a reporting
tool and pay them over $3500 per reporting user license a user plus
per year the huge cost of implementing SAP BW. Once again it is
failure of Microsoft to sell such a powerful potent technologies to
its customer base. Practically none of the MS customer base are even
aware of the power of Analysis Service leave alone the data mining and
Reporting services customers are spending millions on inferior
technologies due to lack of product awareness. But what amazes me is
Microsoft is not keen on selling such a powerful technology like
Analysis Services which when combined with reporting services and
Sharepoint is such a potent technology but all Microsoft invites us is
to listen about MS Office and Windows. I think MS needs a radical
marketing initiative to sell products that can save millions to its
customers who are burning millions of dollars on useless technologies
like SAP BW this is the way to win the hearts and minds of its
customers. Not selling the oversold MS Office and MS Windows we all
have them. We have SAP BW we know the pain we are spending millions on
a inflexible technology hiring expensive SAP BW resources who do not
know what they are even talking off and they are asking customers to
put all flat files into SAP BW they are selling SAP BW as the next
best thing after white bread - Thanks to Microsoft's marketing
indifference.

Belinda

Hi Dave,

I have a question about AS storage optimization feature #3 that you have
described below. Does this imply that it is (practically speaking)
futile/misguided to try and reduce AS cube storage by first building a
summarized fact table from detail records, using something like a SQL
grouping?

This has been the stock-in-trade for handling data explosion, that
erstwhile Essbase cube designers tend to transfer to AS. But it sounds
like the OLAP Server will do much the same grouping for you, from the
detail records?

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Suppose you are building a cube of Sales by Customer, by Product, by Store,
by Time (who bought what, where and when).
Also, while your fact table records transactions down to the minute, the
lowest level in your time dimension was day.

Suppose your RDBMS fact table has 3 records:

Cust -- Product -- Store -- Date -- # units -- total price

Tom -- diapers -- Redmond -- 3/3/03 11:23 -- 4 -- $32.00
Tom -- diapers -- Redmond -- 3/3/03 15:33 -- 5 -- $42.00
Tom -- diapers -- Redmond -- 3/3/03 21:22 -- 2 -- $22.00

In our structures, when processed, these what we consider "duplicate rows"
reduce to:

Cust -- Product -- Store -- Date -- # units -- total price

Tom -- diapers -- Redmond -- 3/3/03 -- 11 -- $96.00 <-- this is
what we store in the MOLAP equivalent of the 'fact table"

This is because the grain of the fact table is what is stored -- not
necessarily an exact copy of the fact table.

Thus depending on the grain of your RDBMS fact table -- and the grain of
your lowest dimensionality on the Analysis Services side, you could have a
*tremendous* reduction of the disk space taken by the MOLAP structures. But
then again, you may not see any reduction if you've already pre-processed
your fact table. I just wanted to point out that it can be a contributing
factor of why you can see a reduction on the amount of disk space taken,
even after you perform have aggregation

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Thanks - I think this example confirms that a summarizing SQL query will
not reduce cube size, for a given grain. If the fact table were grouped
in a SQL query, like:

Select Cust, Product, Store, Date, Sum(Units), Sum(Price)
From Facts
Group By Cust, Product, Store, Date

This would reduce the Fact Table to 1 record; but in either
case the OLAP Server would store it as 1 record, because the grain is
the same. However, summarizing would result in loss of the ability to
drill-thru to detail records.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!