Chapter 10. Troubleshooting 277
By reviewing the MDX trace in the query window further, you can see that Order Method data
item is a member set, restricted by a set filter or a data item with a filter expression:
FILTER(
[Order method].[Order method].[Order method].MEMBERS,
NOT (
IIF(VBA!INSTR([Order method].[Order
Method].CURRENTMEMBER.PROPERTIES("description"), "EMAIL" )<>0,
VBA!MID([Order method].[Order Method].CURRENTMEMBER.PROPERTIES( "description" ),
VBA!INSTR([Order method].[Order method].CURRENTMEMBER.PROPERTIES("description"),
"EMAIL" ), VBA!LEN("EMAIL"))= "EMAIL" , 0)
)
)
The set filter is intended to remove the email channel from the set of sales channels for which
profitability is being assessed. However, the filter expression does not match the member
description value for the email element, and the email sales channel remains in the report
output. The filter expression must be adjusted if the email element is to be restricted.
A further filter is defined to restrict the branch-based employees to be included on the report,
to employees who have recorded sales figures. The MDX trace in the Query view shows the
report is including the filter expression:
FILTER(
[Employee by region].[Employee by region].[Employee].MEMBERS,
([Measures].[Gross profit],
[Time].[Time].DEFAULTMEMBER,
[Order method].[Order method].DEFAULTMEMBER,
[Branch].[Branch].DEFAULTMEMBER)<= 0))
However the filter expression (<= 0) is restricting the list of employees to be included in the
report, to employees who have no profit values recorded for them (unless a loss is recorded).
The report output is therefore not yielding the anticipated results.
To restrict the report to only those employees with sales (profit) values, change the filter
expression to greater than or equal to zero (>= 0).
10.4 Virtual cube query execution tracing
For a virtual cube, the XQueryStrategy and child nodes represent the total time spent to
satisfy the MDX data request from the underlying cubes from which it is comprised. The
property values represent the cumulative totals across the dependent cubes.
Correspondingly, the virtual cube will not display any subquery nodes that represent the SQL
queries that are passed to the underlying relational databases.
Query execution tracing on the dynamic cubes that comprise the virtual cube should be
enabled to explore the query execution behavior for requests that are satisfied for the virtual
cube.
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.