locked
Different data obtained with PDP (5.2 and 6.3) and MDX sample application RRS feed

  • Question

  • Hi. This is a strange issue that happens "sometimes" and I cannot isolate the cause:

     

    Let's consider a view that returns something like this:

     

    Month                 Amount

    April                        100

    May                        200

    June                       130

    July                        180

     

    Let's call MDX1 to MDX statement for that view (obtained from MDX editor in PDP). If I run this query in the MDX sample application (SSAS 2000) or in the SQL Server Management Studio (SSAS 2005) I obtain the same data. No problem.

     

    Now I add March month to the query, but there are no data for March. So, I should get a view like this:

     

    Month                 Amount

    March

    April                        100

    May                        200

    June                       130

    July                        180

     

    However, I get this view instead:

     

    Month                 Amount

    March

    April

    May

    June

    July

     

    Let's call MDX2 to MDX statement for this report. If I run MDX2 query in the MDX sample application (SSAS 2000) or in the SQL Server Management Studio (SSAS 2005) I obtain the result expected:

     

    Month                 Amount

    March

    April                        100

    May                        200

    June                       130

    July                        180

     

    How is this posible? Why MDX2 works outside PDP but not inside PDP?

     

    Thanks!

    Wednesday, July 16, 2008 11:29 AM

All replies

  • Hi Daniel,

     

    That is a very interesting scenario indeed.  You stated that you are using PDP 6.3 and 5.2.  Can you please upgrade to the lastest 6.3.2215 hotfix level to verify the results when using the latest hotfix?  Also, are you running the query using the same user credentials in all three applications? 

     

    If this query points to an AS 2000 cube, you will want to verify that your client components are up to date (PTS / MDAC) on your client.  Can you also test the results within Excel's Pivot Table Services while logged onto the same machine that has the Professional client loaded? 

     

    Thanks,

    Amanda

     

    Wednesday, July 16, 2008 8:36 PM
  • Hi Amanda. I'm using two ProClarity versions because the issue is involved in two different projects. PAS 6.3 is 6.3.2214.

     

    I'm running the query using the same user credentials.

     

    I have tested the results within Excel's Pivot Table Services and I have no problem: I get the same data within Pivot Tables and within ProClarity Standard. The problem appears with ProClarity Professional.

     

    Thanks for your help. I'll keep on investigating but, as you can see, finding the cause of the issue doesn't seem to be easy...

     

    Regards.

    Thursday, July 17, 2008 7:58 AM
  • Could you please run a profiler trace on Analysis Services and capture the MDX that is coming across in each instance?  Then post the two MDX statements and we'll see if we can determine what is causing the differing results.

     

    Friday, July 18, 2008 11:53 PM
  • Hi. Is it possible to run a Profiler trace on Analysis Services 2000? I can run a SQL Server trace file, but I can't connect to AS 2000 with Profiler tool  :-(

     

    Thanks.

    Monday, July 21, 2008 8:09 AM
  • No, you're right, Profiler for Anlaysis Services is only available as of SSAS 2005.  You'll have to use the ProClarity query log.  See this post for information on how to enable and access the log.  Once you've got the queries being logged, run the two queries in question and send the MDX for each, with a note indicating which one returns which results.

    Wednesday, July 23, 2008 11:45 PM