Re: MSAS 2000 Read Time (64 bit) ; DB: DB2

zero aggs?

--
Dave Wickert [MSFT]
dwickert@xxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CEC180BE-A645-455D-9459-1C9F740FD98D@xxxxxxxxxxxxxxxx
> 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:
>
>> 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/default.asp?url=/library/en-us/dnsql2k/html/sql2k_anservregsettings.asp
>>
>> See MAP_NOT_USE_SLICE_FOR_QUERY
>>
>> Hope this helps.
>> --
>> Dave Wickert [MSFT]
>> dwickert@xxxxxxxxxxxxxxxxxxxx
>> 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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:25DD0DB2-3DCA-426A-BF0C-954CFF4A8D31@xxxxxxxxxxxxxxxx
>> > 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.
>> >
>> >
>>
>>
>>


.

Relevant Pages

  • Re: MSAS 2000 Read Time (64 bit) ; DB: DB2
    ... 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. ... I took the exact same 3 queries, executed them in parallel on DB2 directly ...
    (microsoft.public.sqlserver.olap)
  • Re: MSAS 2000 Read Time (64 bit) ; DB: DB2
    ... >> I already have the advanced filter set to each of the partition slices. ... >> I want to correct some of my MSAS Read time stats that I mentioned in my ... >> We are using IBM OLEDB Provider for DB2 shipped with MSAS. ... I think the first thing that I would do is to get the SQL ...
    (microsoft.public.sqlserver.olap)
  • Re: COBOL stored procedure for DB2
    ... Regarding how you precompile the app, you must specify target mfcob to db2 prep, rather than target ibmcob. ... Have you also confirmed that, prior to executing the CALL statement, the host variables specified within the client app have the appropriate values? ... MODIFIES SQL DATA ... confirm the cob command used for creating the SP module. ...
    (comp.lang.cobol)
  • Re: SQL Server 2005 Express as a Data Mart environment
    ... I'm not sure if there are any limitations with SQL Express with respect to ... meter with 60-170k rows each with 3.5mil rows total. ... each partition. ... 15MinuteInterval TinyInt ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Merge join performs really slow
    ... You can remove the join from the SQL statement but do so at your own risk. ... or you could load the partition through a view (which is my ... > join hint - with a loop join strategy. ...
    (microsoft.public.sqlserver.olap)