Hi Dave,

I am running 3 cube partitions in parallel using Parallel Partition Utility.
The Database is DB2 UDB Version 8.2. The MSAS Read time is a concern at this
point. I am not doing any aggregation on the cubes. When I see the generated
logs, MSAS is reading 10,000 rows per second which is 60K per minute. Taking
all 3 partitions into consideration it is reading about 180K rows per minute.

When we execute the same 3 queries in parallel on the DB2 database and spit
out the results to a text file, every thing is done in a matter of 5 to 6
minutes. I am trying to find out the bottle neck and speed up the Read time.
I mentioned our Hardware and Software settings along with the Analysis
Services settings. Please let me know.

Software:
• 64 bit Microsoft Analysis Services 2000 Enterprise Edition
• 64 bit Microsoft Windows Server 2003 Enterprise Edition

Hardware:
• Intel Itanium processor Family
• 1.60 GHz, 8 Way
• 60 GB RAM
• Gigabit Ethernet
• SCSI Disk Device

MSAS Settings:


Performance settings:
• Maximum number of threads: 16
• Large Level defined as: 10,000

Memory settings:
• Minimum allocated memory: 16384 MB
• Memory conservation threshold: 32768 MB

Processing settings:
• Read-ahead buffer size: 32 MB
• Process buffer size: 256 MB

Thanks,

Prasad.

Could it be the provider you are using to connect to DB2? If you are
using the default ODBC driver from MS, it is not all that fast. I have
seen dramatic increases in performance against DB2 from switching to
using a native OLEDB provider.

Have you tried using the text files you dumped out to as source for AS.
You might not want to do this as a long term solution, but it would
remove the DB2 provider from the equation temporarily so that you could
see if it is the issue.

The amount of aggregations will not affect the reading speed as they are
done in a separate step after the data has been read.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <25DD0DB2-3DCA-426A-BF0C-954CFF4A8D31 (AT) microsoft (DOT) com>,
Prasad (AT) discussions (DOT) microsoft.com says...

Yes, try different drivers and connection configuration.
in the past I have seen some options like row batch size in the DB2 drivers.

not all drivers support the fast load retrieval interface.

"Darren Gosbell" <dgosbell_at_yahoo_dot_com> wrote

Could it be the provider you are using to connect to DB2? If you are
using the default ODBC driver from MS, it is not all that fast. I have
seen dramatic increases in performance against DB2 from switching to
using a native OLEDB provider.

Have you tried using the text files you dumped out to as source for AS.
You might not want to do this as a long term solution, but it would
remove the DB2 provider from the equation temporarily so that you could
see if it is the issue.

The amount of aggregations will not affect the reading speed as they are
done in a separate step after the data has been read.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

In article <25DD0DB2-3DCA-426A-BF0C-954CFF4A8D31 (AT) microsoft (DOT) com>,
Prasad (AT) discussions (DOT) microsoft.com says...

Now that is a *nice* machine.

Since this is 64-bit, it means that you are using the native DB2 OLEDB
provider. I would check with them to ensure that you have the latest version
of the software.

To debug this, I think the first thing that I would do is to get the SQL
statements that we are issuing (from the system-wide processing log file,
which if you haven't already done, stop reading this email and do
immediately, then read the rest . . .

(stopping while you turn on the system-wide processing log file)

OK. Now go to that file and look at the SQL statement. Pull it out and
execute it by-hand using the normal DB2 utilities. Sorry, I'm not up on the
latest and greatest from IBM, but I am sure you know it. Execute several
statement in parallel yourself interactively. What throughput are you
getting?

A known problem is if you are table partitioning on the IBM DB2-side and
partitioning on the AS-side. The way that AS passes the data slice to DB2 is
to use ODBC cannonical format for parameters. The SQL looks something like:

SELECT <col list> FROM <table> WHERE <slice field> = ?

And then the ? is replaced with the data slice for the partition. The WHERE
clause is automatically added by AS when you have multi-partitioning. The
issue on the DB2-side is that the DB2 optimizer cannot use table
partitioning across a parameter passed like that. It must be passed as a
constant. Thus on the AS-side, you set an advanced filter for the partition
to be <slice-field> = <value> and then set a registry setting to disable AS
from adding the default WHERE clause for partitioning. The registry setting
is documented here:

http://msdn.microsoft.com/library/de...egsettings.asp

See MAP_NOT_USE_SLICE_FOR_QUERY

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


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

Hi Dave,

Thanks for the reply. Yes, the management approved the hardware that we
requested. Now, we are trying to prove that we can port all our existing
cubes and also build bigger scalable cubes with MSAS 2000 and 64 bit server.

I already have the advanced filter set to each of the partition slices. Also
the DB2 registry setting is done for MAP_NOT_USE_SLICE_FOR_QUERY as per the
article.

I want to correct some of my MSAS Read time stats that I mentioned in my
note earlier. MSAS is reading about 50 k rows per minute. I am running 3 in
parallel with the utility. Therefore I am assuming that it is reading 50k *3
close to 150 K rows per minute from DB2.

Each partition has roughly 8 million rows. The MSAS Read time for 3
partitions in parallel is 15 to 16 minutes.

I took the exact same 3 queries, executed them in parallel on DB2 directly
and also piped the results to a text file. It took little less than 5 minutes
to complete all 3 of them. Pretty amazing!

We need to get the MSAS read time around the same ball park as DB2 to build
all the necessary cubes in our load week end. (Essentially 48 hours) I am
not planning to have any aggregations at all in any of our cubes because of
the number of dimensions.

We are using IBM OLEDB Provider for DB2 shipped with MSAS. I am not sure how
to check the version of the driver. Also, I did not see any parameters like
batch size etc that I can change with the driver. Please let me know if there
are better drivers that I can use to speed up the MSAS read times from the
DB2 database.

Also, I am not sure how are your other clients of this nature are handling
the read times with database in DB2?

Thanks in advance for your help. Please let me know if you would like me to
test any other things.

Prasad.

"Dave Wickert [MSFT]" wrote:

zero aggs?

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


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

Sorry. I mean to say very less percentage of aggregation : 5% (MSAS estimated
around 20 aggs).

Please note that i had most of the dimensions to "Top Level" only except 3
otr 4.

Thanks,

"Dave Wickert [MSFT]" wrote:

Hi Dave,

Can you please comment on the performance and give recommendations?

Regarding aggs in my prior note, Sorry. I mean to say very less percentage
of aggregation : 5% (MSAS estimated around 20 aggs).

Please note that i had most of the dimensions to "Top Level" only except 3
or 4.

Thanks,



"Dave Wickert [MSFT]" wrote: