Improve performace in dimensional report

[rteruyas]

One of my latest projects is to build a report that show a measure for a give week and compares it against the average of 13 weeks before the given one.
My problem is that it takes about 5mins to complete from the moment user enters the parameter on screen.

I have a few questions about this and was hoping you could gave me some ideas on how to do it:

1. How do you test how long it takes to run the MDX in the server

2. Is there a way to improve the query that is generated?

I'm not expert in MDX, but when I look at it, I see CROSSJOIN being used several times

Code Select Expand


WITH
  SET XQE_NS7 AS 'ORDER([Abattoir Assigné].[Abattoir Assigné par Regroupement].[Abattoir Assigné].MEMBERS, [Abattoir Assigné].[Abattoir Assigné par Regroupement].CURRENTMEMBER.PROPERTIES("MEMBER_CAPTION"), BASC)'
  SET XQE_NS6 AS 'ORDER([Entente].[Entente par Regroupement Courant].[Entente Courante].MEMBERS, [Entente].[Entente par Regroupement Courant].CURRENTMEMBER.PROPERTIES("MEMBER_CAPTION"), BASC)'
  SET XQE_NS5 AS 'LASTPERIODS(14, XQE_NS1.ITEM(0).ITEM(0))'
  SET XQE_NS4 AS 'CROSSJOIN(XQE_NS6, XQE_NS7)'
  SET XQE_NS3 AS 'ORDER([Producteur].[Producteur par Entreprise].[Entreprise].MEMBERS, [Producteur].[Producteur par Entreprise].CURRENTMEMBER.PROPERTIES("MEMBER_KEY"), ASC)'
  SET XQE_NS2 AS 'CROSSJOIN(XQE_NS3, XQE_NS4)'
  SET XQE_NS1 AS 'FILTER([Date].[Calendrier Opérationnelle].[Semaine Opérationnelle].MEMBERS, [Date].[Calendrier Opérationnelle].CURRENTMEMBER.PROPERTIES("MEMBER_KEY") = ?pSemaineComp?)'
  SET XQE_NS0 AS 'EXCEPT(XQE_NS5, {XQE_NS1.ITEM(0).ITEM(0)})'
  MEMBER [Date].[Calendrier Opérationnelle].[XQE_V5M_13 dernières semaines_CM4] AS 'AGGREGATE(XQE_NS0)', SOLVE_ORDER = 4, MEMBER_CAPTION = '13 dernières semaines'
  MEMBER [Date].[Calendrier Opérationnelle].[XQE_V5M_SemainePivot - Classement 59_CM3] AS '(XQE_NS1.ITEM(0).ITEM(0), [Classement].[Classement].[Tous].[59])', SOLVE_ORDER = 4, MEMBER_CAPTION = 'SemainePivot - Classement 59'
  MEMBER [Producteur].[Producteur par Entreprise].[XQE_V5M__Production_._Producteur_._Producteur par Entreprise_._Entreprise_->:_RO_._Producteur_CM0] AS '([Producteur].[Producteur par Entreprise].[Tous])', SOLVE_ORDER = 3, MEMBER_CAPTION = 'Toutes les Entreprises Autorisé'
SELECT
  NON EMPTY {CROSSJOIN({[Date].[Calendrier Opérationnelle].[XQE_V5M_13 dernières semaines_CM4]}, {[Mesures].[Nbre Porc Livré], [Mesures].[Poids Brut], [Mesures].[Indice de Classement Total]}), CROSSJOIN({[Date].[Calendrier Opérationnelle].[XQE_V5M_SemainePivot - Classement 59_CM3]}, {[Mesures].[Nbre Porc Livré], [Mesures].[Poids Brut], [Mesures].[Indice de Classement Total]}), (XQE_NS1.ITEM(0).ITEM(0), [Mesures].[Poids Net Payé Total])} DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(0),
  NON EMPTY XQE_NS2 DIMENSION PROPERTIES PARENT_LEVEL,  PARENT_UNIQUE_NAME ON AXIS(1)
FROM [Production]  CELL PROPERTIES CELL_ORDINAL,  FORMAT_STRING,  VALUE


3. For the 13 weeks, is there a way to show "From week [1stweek of set] to [13week of set] as label?
I tried using the following expression

Code Select Expand


'From '
+ roleValue ( '_businessKey' ; item([13 Dernières Semaines] ; 0) )
+ ' to '
+ roleValue ( '_businessKey' ; item([13 Dernières Semaines] ; 12) )

But when it's executed, I get an error message XQE-PLN-0020

[CognosPaul]

it's hard to debug the report without seeing it. In general there are a few basic rules you can follow.

1. I see you're stacking item(item([set],0),0) That really doesn't do anything.
2. You're filtering the date by a key. This causes the cube to loop through all of the members in the date dimension. Instead, either use the the date mun directly, or construct it. #'[Cube].[Dim].[Hier].[WeekLevel]->['+prompt('pSemaineComp','token')+']#

Cross joins, while problematic in relational, is absolutely fine in OLAP.

My advice would be to dismantle the report, data item by data item. Rerun the report each time. First check if the Abattoir and week data items run okay. Then start adding the various measures.

For the second problem, it would be better to use report expressions or clever positioning. Will this label be IN the crosstab, or can be it be above it? Because it's using two businessKeys from the same hierarchy, it will be difficult (but not impossible) to get it into a crosstab node.