Hi all,

This is something I have been trying to do for a long time with little
success.

I have an OLAP client that sits on MSAS. I want to access a VLDB environment
that could have billions of rows of data. Hence, I do not want to pull the
data into MSAS to construct partially aggregated cubes.

I have tried 0% aggregation MOLAP in MSAS 2000. However, if you browse the
data in MSAS you get *ERR*. On looking at the SQL query passed to the VLDB
engine, it is obvious that it passed a query to pull every row from the
database and aggregate it within the MSAS engine - not something you want to
do with billions of rows of data!

Has anyone had any success of running MSAS against a VLDB environment? Will
real time ROLAP design in SQL 2005 solve the problem?

Regards,
John

well...
I want to confirm... you have a big volume of data, but you don't want to
aggregate it???
Why do you want to use an olap cube if its NOT for preaggregating data???


what's appends when you open the cube? the system must execute a select
count(*) from MYBIGTABLE (or sums or anything else) ; when the user drill
down, then another big query is executed and again for the next drill
down...
so you kill your database because you'll execute a lot of big queries
against the database. instead-of reading 1 time only and keep the aggregated
data into a MOLAP partition.

so you are unhappy because the system do what you ask it to do: executing a
lot of queries instead-of doing 1 reading against the database.

what is recommended:
* create multi partitions to load and process only required partition
instead of read the entire database
* historical partition could be ROLAP based because they are less accessed
then current values
* "current year" or "current month" aggregation should be MOLAP aggregated
to provides the higher performance

Microsoft has allready published a project called T3 project with billions
of rows. (in 2001)
the link I have is no longer available, but I think there is an article
anywhere around this.

http://www.windowsitpro.com/Articles...layTab=Article

the "current" version is based on AS2005:
http://www.microsoft.com/sql/solutio...ojectreal.mspx



"JC" <JC (AT) discussions (DOT) microsoft.com> wrote

Thanks for the reply.

There are reasons why I don't want to aggregate this in MSAS.

Firstly, i am dealing with the vendor of the VLDB environment and they would
not really take kindly to extracting data from their database environment.
Secondly, the volumes of data they deal with is way beyond T3 (actually, this
referenced 1.2 billion rows of data) - as a standard application they are
dealing with telecomm call data. This can amount to billions of rows a month
- not something for the faint hearted. Lastly, the data, whilst not updating
in real time, is added too by large volumes regularly - too much to aggregate
within MSAS.

For these reasons, we decided to allow the VLDB environment to do the heavy
duty processing (they quote a consistent sub 5 second response time for all
data volumes up to and including tens of billions). So MSAS would have to
pass a query at a summary level rather than detailed.

I am hoping that designing storage as real time ROLAP in SQL 2005 will
enable this to happen.

Regards,
JC

"Jéjé" wrote:

realtime cubes are available in AS2000 too.
create a realtime rolap partition with 0% of aggregation, but AS will
continue to execute big queries

does your cube is based on a detailed or a summary table (or view)?


"JC" <JC (AT) discussions (DOT) microsoft.com> wrote

The VLDB environment will be very detailed transactions.

"Jéjé" wrote:

but does your cube access needs are based on this detail? or a summary is
enough?
I mean, if your cube access the source thourgh a view like:
select col1, col2, ... sum(a), sum(b)
from mybigtable
group by col1, col2

then AS queries will access this view and this insure that you'll always go
through a view which can be optimized. and also insure that you'll never
retrieve too many rows from the system.
if you don't tell AS to access summary tabbles or views, then AS can't use
these summaries!

you can also create a set of cubes 1 by summary view and play with
calculated measures to "redirect" the user to the right cube regarding which
data are asked.
for example, if you have a cube based on the Year and the Store only, when
the user ask your total sales measure for a year and/or a store, you'll call
the total sales measure from the CubeA, else you call the total sales from
the CubeB which contain everyhing but a slower cube.

if you don't use aggregation, you have to work hard to "train" AS how to use
"preaggregated" or "quicker" data.

"JC" <JC (AT) discussions (DOT) microsoft.com> wrote

look at this show:
http://www.microsoft.com/emea/itssho...spx?videoid=80

this should solve your issue ;-)

"Jéjé" <willgart_A_ (AT) hotmail_A_ (DOT) com> wrote