Excel VB MDX to 9.1SP3x64 - Very,very slow...

[Alain]

Hi every one,
In XL, I have VB codes that connect to TM1 and pass an MDX view...
In TM1 8.44 that was the fast way to built my XL reports.
We are upgrading to 9.1 SP3 x64 and the MDXs stall.
No one had the same experience? Where should I look to try to fix the (big) issue? Is it to do with looking...?

Thanks,
Alain

[Steve Rowe]

Alain.
I'm no MDX expert so I can't offer you any direct advice. A couple of things you can do though.
1. Make sure you raise it with Cognos support so they can fix the issue in future releases.
2. Post your MDX in case one of the experts can suggest a more efficient approach.

Were you on 64 bit 844 as well? How much has your model changed? Just wondering if the performance is due to the change to 64 bit (unlikely unless you have very slow processors I guess) or 9.1?

Cheers,

[Alain]

Thank Steve for your reply,

The MDX was talking to TM1 8.44 on a slow server with 3 GB RAM before we upgrade to 9.1 SP3 x64 run on a Intel Quad-Core Xeon X5355 / 2.66 GHz processor with 16 GB RAM, The cube is the same as will as the VB codes and I have a few VB codes running MDX queries. They all working very slowly or not at all.

So I do not know what is affecting the performace...

Alain

[David Usherwood]

If you can, suggest you try 9.0SP3U4 or U7. 9.1+ seems to be a giant step backwards all round.

[David Usherwood]

(Not quite talking to myself) Further to your posts, I'm putting together a demo for a customer using TM1 9.1 and EV9.1. EV is normally very fast. This isn't.

[David Usherwood]

Scratch that comment - EV Explorer is slow, EV client is fast - which is what matters.

[Alain]

Tanks for your reply David,
I am not sure I understand EV Explorer...
In XL, programmatically I connect to TM1 trough the "Applix TM1 OLE DB MD Provider (TM1OLAP), then run my MDX then return the data in and VB array to populate my XL report... Could that be that the TM1OLAP connection is on a 32 bits box and try to talk to TM1 x64. Note in the setup of the TM1 server 9.1 sp3 x64 we manage to get the AxNet running on the server box rather that the 32 bits box...

Alain

[David Usherwood]

(EVExplorer is the admin/build tool for EV.)
My understanding is that AXNET is only relevant for TM1Web at present (at least for Wintel). If you are using 64bit TM1 and need to use ODBC via 32bit drivers then you need to put AXNET on a 32bit server and tweak the CFG file. This seems the other way round from the setup you describe.
That does make me wonder though - the ODBO drivers are probably 32bit. But I would have thought they wouldn't work at all rather than work slowly.
What does Support say?

[jim wood]

You may want move to 9.1 SP3 U2. Cgnos are saying this is going fix several issues within 9.1. You may have to speak to Congos to get access to it as I doubt it will be posted on their webs ite. They onyl seems to post version releases not updates,

Jim.

[Alain]

Thanks for all the answers,

I am lucky to ave a test environement with a virtual x64 and x86 systems. So I removed the parameters for the gateway (AxNet) and stop the AxNet service so I could focus on the MDX issue. That did not make any difference.
I'll see how I can get some info from Cognos... and will give feedback.

[jkassier]

Hi Alain

DId you ever receive any feedback on this issue? How did you resolve the issue around the extremely slow MDX query on TM1 v9.1? Would really appreciate any feedback on this issue. Thanks

[David Usherwood]

Number of suggestions:

a Try 9.4. Only out a few weeks, but it's looking promising. (Rebecca H, if you are reading this, we ain't finished testing yet....)
b What was in your MDX which made it better to use than a simple Excel slice? Could you post it or a sanitised version?

[jkassier]

Thanks David. We are currently still reluctant to upgrade to 9.4 as this release is still quite new.

Re the MDX, we run it from an Excel front end - certain users hence don't even need to look at the TM1 backend. The code is effectively as follows:
WITH
SET [D1] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM1].[All DIM1]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D2] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM2].[All DIM2]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D3] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM3].[SYSTEM QUERY]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D4] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM4].[SYSTEM QUERY]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D5] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM5].[All DIM5]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D6] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM6].[All DIM6]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D7] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM7].[All DIM7]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D8] AS '{TM1SORT( {TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {[DIM8].[All DIM8]}, ALL, RECURSIVE )}, 0)}, ASC)}'
SET [D9] AS '{ [Year].[2007/08],[Year].[2008/09],[Year].[2009/10],[Year].[2010/11] }'
SET [D10] AS '{ [Month].[July] }'
SELECT NON EMPTY {[D1] * [D2] * [D3] * [D4] * [D5] * [D6] * [D7] * [D8]} on rows,
{ [D9] * [D10] } on columns
FROM [CUBENAME] WHERE ( [Scenario].[BUDGET], [DIM12].[Unit Price] )

where the DIM? refers to the various dimensions, and the All DIM? refers to various consolidated elements within such dimension. SYSTEM Query refers to a subset that was updated previously in the VBA code.

Any suggestions?

[rmackenzie]

Hi jkassier,

The problem lies with CROSSJOINs, or in the case of your query, the {[dim] * [dim] * [dim] etc etc} clauses.

I am reliably informed that this is a known issue and will be fixed in the next 9.1 service pack release. I suggest you contact your local Cognos rep for details as they may be able to supply you with a hotfix prior to that.

Edit - my problem was with 9.1SP3U2x86 but I assume the problem is with x64 as well.

Robin