1
Vote

MDX Performance Tool for SQL 2008 R2

description

Hello:
 
I am running this MDX for SQL Server\Analysis Server 2008 R2 version. When i run MDX query in script window, i am getting no results. i run like 4 to 5 small ones & large ones. same response, no results.
 
I have attached trace file with this, for further investigation.
 
Any help is greatly appreciated, Thanking in advance.
 
Thanks

file attachments

comments

furmangg wrote Sep 24, 2011 at 3:11 PM

Thanks for supplying more information. I see several problems:
  1. I see the following trace event hundreds of thousands of times. I believe that indicates that the cube in question is currently being processed. First, I would run MDX Script Performance against a dev environment that's not currently processing the cube and not currently being queried by other users. It's not that MDX Script Performance will hurt processing or hurt other users... it's that you can't trust the results if other things are happening on that box. And with the cube being processed, depending on how that's done, the cube may not be queryable at that moment. Anyway, the event I mentioned was:
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'PANAMA_NRDB_RISK_FACT', Column: 'ID_VALUE_DATE', Value: '780'. The attribute is 'valueDate'. Errors in the OLAP storage engine: The record was skipped because the attribute key was not found. Attribute: valueDate of Dimension: Value Date from Database: Trade_Global, Cube: Trade_Global, Measure Group: Risk Fact, Partition: APC MX, Record: 1393468.
  1. You are receiving the following error message when you run your query, "The set in the WHERE clause cannot contain multiple measures." I don't see multiple measures in the where clause of your query. The where clause is:
    WHERE ([ProcessSource].[scenarioName].&[EOD], {[Book].[region].[region].&[NA]},{[Product Type].[productGroup].[productGroup].&[CDS],[Product Type].[productGroup].[productGroup].&[INDEX],[Product Type].[productGroup].[productGroup].&[RATES]},{[Book].[bookTrader].[bookTrader].&[JASON WEN]},{[Trade].[ccy].[ccy].&[USD]},{[Trade].[tradeStatusDaily].[tradeStatusDaily].&[CLOSED],[Trade].[tradeStatusDaily].[tradeStatusDaily].&[NEW],[Trade].[tradeStatusDaily].[tradeStatusDaily].&[UPDATED]}, [Value Date].[valueDate].&[777], [Measures].[ChgOnDayUSD])
    In researching this error message, I noticed this KB article: http://support.microsoft.com/kb/947511
    I couldn't reproduce that error against Adventure Works on AS2008 R2 version 10.50.1600. When you connect Object Explorer in SSMS to Analysis Services, what version number does it say on the server node?
    Can you double check that MDX query succeeds when run from Management Studio. If it doesn't even succeed from Management Studio, there's no point using MDX Script Performance. If it does succeed in Management Studio, is there a calculated dimension member in the where clause that's causing the error?
Hopefully the above information helps you troubleshoot what's going on. If all else fails and your query uses a calculated dimension member that's defined near the bottom of the MDX script, you might just have to manually troubleshoot the query by deploying the MDX script to a dev environment with large parts of it commented out.

wrote Sep 26, 2011 at 10:18 PM

aaditya2000 wrote Sep 26, 2011 at 10:18 PM

Hello:

Thank you for your kind help on this issue. i restore database to my test server & run the tool & again, it didn't dispaly any result.
When i run same query in SSMS, it return resultset.

But the problem is with this tool, which doesn't dispaly anything on report. (blank report)

I have attached profiler trace.

Any help will be great,.

thanks
regards

furmangg wrote Sep 27, 2011 at 11:22 PM

You're making progress. It doesn't look like the cube is processing while you're running the test.

But it looks like you're still getting that same error when running the query: The set in the WHERE clause cannot contain multiple measures.

When you run that query from Management Studio do you get the same error?

Again, I would recommend you try commenting out large parts of the MDX script and test the query. If I'm seeing the full MDX script in the profiler trace, your MDX script isn't very long, so it shouldn't be too bad to manually comment out and deploy the MDX script to test.

aaditya2000 wrote Sep 29, 2011 at 4:59 AM

Hello:

The query is running fine when ran in SSMS. it takes 17 mins to complete. but runs with no errors & returns result set with some 2000 rows.

So i am not sure where the problem is. it works fine in SSMS but doesn't works from your tool & give timeout message in less than 1 min.

Thanks
Regards

wrote Feb 14, 2013 at 2:19 AM