msmdsrv using 50% cpu for minutes

msmdsrv using 50% cpu for minutes

• Ralf van der Zanden

  hi,

  thanks for the additional details. i believe that we've seen a similar issue before - i will work on getting the information for you.

  thank you

  mauli

  
  

• saabdude

  Hi Paso,

  Is this still happening for you If so, let me know. We can try to see what diagnostics to run on your system so we can figure out the problem.

  Mauli

  
  

• Kai123

  Hi Mauli,

  Yes, reports using Date dimension and CumulativeCount are slow and are using a lot of cpu power. When I remove all columns (Year and Date) except for the Week-column from the detail pane, then report generation speeds up. So I guess calculating the cumulativecount on a daily basis just takes a long time. Maybe I need to run cumulativeCount reports over night and just publish the result in stead of allowing them to be interactive.

  However the Week part of the Date dimension returns a string in the form of "Week of Septermber 17".  I can not use this in a filter to compare it to a given date (Report parameter of type DateTime) and I could not find a way to convert the Week-string value in a DateTime value. Any idea how to do this

   

  
  

• Francesco De Vittori

  Hi Jim,

  When executing this query I get the following error:

  The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
  Parameter name: mdx (MDXQueryGenerator)
  

  With trial and error I made following changes and this seems to give the wanted result.

  WITH

  Member [Week Date] as [Date].[Year Week Date].CurrentMember.Member_Key

  SELECT

  Non Empty {

  [Measures].[Cumulative Count],

  [Measures].[Week Date]

  } on columns,

  Non Empty {

  ([Date].[Year Week Date].[Week].ALLMEMBERS)

  } on rows

  From [Team System]

  Do you agree there is something wrong with the query you provided or am I missing something

  Thanks.

  
  

• jerryf65

  You are right, my version of the query works in SQL Management Studio but I didn't test it in the report designer tool.  You are seeing the error because the [Date].[Week] dimension attribute is a hidden attribute.  [Date].[Year Week Date] is the right one to use.

  The key is "[Date].[Year Week Date].CurrentMember.Member_Key" would give you what you need, which is the date for the week.  You can use this expression to create a calculated member in the Query Designer so you don't have to write the whole query out in MDX.

  Thx,

  Jim

  
  

• Andreas Jaeger

  Hi Paso,

  The [Cumulative Count] measure is inherently expensive. Another way to speed up the report is to limit the date range on the report. You can take a look at how we do that using the start date and end data report parameters in the out of the box reports.

  To answer your question, you can create a calculation to return the key of the week, which is a date. Here's an MDX example:

  WITH

  Member [Week Date] as [Date].[Year Week Date].CurrentMember.Member_Key

  SELECT

  Non Empty {

  [Measures].[Cumulative Count],

  [Week Date]

  } on columns,

  Non Empty {

  [Date].[Week].[Week]

  } on rows

  From [Team System]

  Hope this helps!

  -Jim

  
  
• msmdsrv using 50% cpu for minutes