Supress null values for 13 Week date using dimensional fn, cube as a datasource

[xyz]

Hi All,

I have a requirement to display data for few measures for current selected week and 13 weeks including the current week. Basically data in two or three measures have null data. I developed two separate reports one report for current selected week and other report will display data for 13 week. I was able to suppress null values for current selected week, but I could not able to do suppress of null values for 13 week. As my data source is cube, I should not use detail filters, which will degrade the performance. Please let me know, how will I achieve suppression of null values for 13 week. On top of it, on one measure I have to do calculation to find the average. Please find my expressions below, which I have used for suppressing nulls.

Actually in the list report, I am displaying Employee details along with different measure combinations.

Measures are: [Absent], [Late Exception], [Break Exception]

[Employee]  set

Actual List for selected week.

Employee    Absent
A                1
B                2
C                 
D                1

Current Week data item expression below.

[Cube].[Date].[Date-Hierarchy].[Fiscal Week] -> ?p_date?

To suppress null values, I tried below expression, which didn't suppress the null values

filter([Employee], [Absent] is not null)

Then I tried the below expression, which uses tuple function

filter([Employee], tuple([Current Week],[Absent]) > 0)


After applying filter expression.

Employee    Absent
A                1
B                2
D                1

13 Weeks data list .

Actual List for 13 week preiod.

Employee    Absent
A                5
B                9
C                 
D                1

13 week date data item expression below.

lastperiods(13, [Cube].[Date].[Date-Hierarchy].[Fiscal Week] -> ?p_date?)

To suppress null values, I tried below expression, which didn't suppress the null values

filter([Employee], [Absent] is not null)

How will I suppress the Employee set for 13 week date, I can't use tuple function for it. Otherwise I have to use detail filter, which against OLAP data source as well as it degrade's performance.

Can some one please help me on this issue? Your help will be much apperciated :)

Thanks & Regards,
XYZ

[xyz]

Hi,

Can someone please help me on post?


Your help is much appreciated :)

Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone, please update with some suggestions or idea's on the same, your help is much appreciated :)


Thanks & Regards,
XYZ

[xyz]

Hi All,

Can someone please share some suggestions on this post?


Your help is much appreciatred!!!

Thanks & Regards,
XYZ

[CognosPaul]

You have a set returning the 13 weeks. You can use that to filter your employees. There are actually a number of ways, so let's try the easiest first.

filter([Employees],total([Absent] within set [13 week date ]) >0)

[xyz]

Hi Paul,

Thank you very much for the reply, when I tried your suggestion, I am getting the below error.

XQE-GEN-0010

      Found an internal error: '!mapSuccess - reportName=Employee Performance - edgeName=3 - edgeOrdinal=0 - tuple=(AMEDY, BROOKE M, XQE_FT_CM2).'.

Basically I am displaying two measure side by side on the list report along with Employee dimension. Actually there are some nulls and 0 values in both the measure, how will I suppress nulls in this case. Can't I suppress nulls or 0 in both the measures in the same query? Please find the sample below of the list report. Absent % is the calculated measure for 13 weeks, where as Labor Hours is total for 13 weeks?

List:

Employee    Labor Hours      Absent %
A                8                      1
B                0                       
C                3                      0

Actually I am doing a calculation to find the average of [Absent %], the calculation is like below.

13 Week Avg Absent % expression in the data item below.

total([Absent] within set [13 week date ])/
total(
if(
tuple(CurrentMember([Cube].[Date].[Date-Hierarchy].[Fscl Yr]),[Absent ])>0) then (1) else (0)

within set [13 Weeks Date])


Labor data item expression below.

total([Labor Hours] within set [13 week date ])

Can you please help me on this, even I am getting a performance hit as there are more than 300 records of employee in the cube database?

Your help is much appreciated?

Thanks & Regards,
XYZ

[CognosPaul]

Check the currentMember function, that should be referencing a hierarchy, it looks like you're referencing a level:


total([Absent] within set [13 week date ])/
total(
if(
tuple(currentMember([Cube].[Date].[Date-Hierarchy]),[Absent ])>0) then (1) else (0)

within set [13 Weeks Date])


Try doing this in a crosstab instead of a list. Lists on cubes can cause problems.

The filter function simply filters a set by one or more criteria.

So you could do something like:
filter([Employees],[Labor Hours] >0 and [Absent %] is not null)

[xyz]

Hi Paul,

Thanks for the reply, yes for currentMember function I used hierarchy but while writing the expression in this post, I mistakenly put the level.

On the list, when I try to apply the filter, I was getting the same XQE-GEN-0010 error, but when I converted it to crosstab to test, I didn't get any error. Still I am facing the same performance issue, do you have any suggestions, converting the prompt parameters to prompt macro's will it improve the performance? I tried the options like changing query execution type all options, but none of them helped in improving performance.
What do you suggest, pushing the 13 week calculation to cube, I am hoping would improve the performance .

Your help is really appreciated :)

[CognosPaul]

It's weird that it would be taking so long. Let's try this one step at a time.

Create a new crosstab and pull in employees to the rows. To the columns, add
total([Absent] within set [13 week date ])
and
total([Labor Hours] within set [13 week date ])

Does it run fast or slow?
If it's fast, add
total(if(tuple(currentMember([Cube].[Date].[Date-Hierarchy]),[Absent ])>0) then (1) else (0) within set [13 Weeks Date])


Also, what type of cube is this? PowerCube? SSAS? EssBase?

[xyz]

Hi Paul,

Thank you very much for the reply, my cube is SSAS cube. when I tried to use the step mentioned in the email below

Create a new crosstab and pull in employees to the rows. To the columns, add
total([Absent] within set [13 week date ])
and
total([Labor Hours] within set [13 week date ])

Then report was running same as early around 50 seconds to 1 minute, but it was better when compared with the list report, list report was taking 1 minute 30 seconds like that.

One thing is, eventually I am using the report in the Congos workspace, when I tried to pull the report in the workspace then report was running for 40 to 50 sec for the first time, then after that, when ever I try to run the dashboard then cross tab report was running fast.

Can you please suggest ?


Thanks & Regards,
XYZ

[CognosPaul]

Let's try something different.

Rows:
  Employees

Columns:
  Absent
  Labor Hours

Slice:
  total(currentMeasure within set [13 week date])



50 seconds is excessive for this. Can you also post the expression used for Absent and Labor Hours?

[xyz]

Hi Paul,

Thanks for the reply.

As you mentioned in crosstab Rows, I have added Employees set and in columns I have added Absent and Labor Hours measures. Please find the expressions for Absent and Labor Hours below.

Absent data item expression below:

total([Absent] within set [13 week date ])/
total(
if(
tuple(currentMember([Cube].[Date].[Date-Hierarchy]),[Absent ])>0) then (1) else (0)

within set [13 Weeks Date])

Labor Hours data item expression below:
total([Labor Hours] within set [13 week date ])

Actually report is running in two seconds with above expression and without the Employee data set, the moment I drag and drop the Employee dimension in the query, report performance is going for toss, taking almost 1 minute to 1 minute 10 seconds approx.

When I tried to add the slice filter total(currentMeasure within set [13 week date]), I am getting the below error.

XQE-PLN-0247

      A value expression may not be used in a slicer or context filter, unless wrapped in a Member function.

As a reference, please find the attached xml of cognos report studio report.

Can you please help me on this, your help is much appreciated.

Thanks & Regards,
XYZ

[xyz]

Hi Paul,

Sorry I forgot to mention that, I am doing sorting descending on 'Absent %'  column as this is the requirement of the client. I am not quite sure, this is also causing for performance bottle necks.


Thanks & Regards,
XYZ

[CognosPaul]

Sorry for the delay in replying - lots of holidays for me in October.

The absent function is a bit complex. How many employees are there? It could be that the it's choking on the 13 Week Avg AB function.

Maybe try changing
filter([Employee], [13 Week Avg AB] is not null and [Hours Worked] > 0 )

to

filter(filter([Employee], [Hours Worked] > 0 ),[13 Week Avg AB] is not null)


that way it's testing the complex expression on a smaller sample of employees.

For the slicer, try changing total(currentMeasure within set [13 week date])
to
member(total(currentMeasure within set [13 week date]),'13week','13week',[Labor].[Date].[Hierarchy - Fiscal Year])

[xyz]

Hi Paul,

Thank you very much for the reply. I know you were busy the whole last week attending some conferences.

There are about 400 employees are there, I knew the percentage calculation I am doing for 13 Week Avg AB function is causing issue.

I tried the filter suggestion as suggested by you, to use filter over filter. There is no much of performance improvement.

I am not using any slicer except having a slicer on Organization hierarchy.

When I tried adding one more slicer in the report as suggested by you then, I am getting the below error.

XQE-PLN-0246

      The slicer '[Labor].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree].[(All)]->:[N1].[Org Hierarchy].[Hierarchy - Org Hierarchy Tree].[All]member(total(value(tuple(currentMeasure)); within set(lastPeriods(13; null))); '13week'; '13week'; [Labor].[Date].[Hierarchy - Fiscal Year])' in query 'Q_LE_Emp_13Weeks_AB.1' is not supported. It contains a user defined calculation which references the hierarchy '[Labor].[Date].[Hierarchy - Fiscal Year]'. This hierarchy can not be referenced elsewhere in the report.

     Details
RSV-SRV-0042 Trace back:RSReportService.cpp(747): XQEException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(258): XQEException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(848): XQEException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(305): XQEException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(904): XQEException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestExecution/RSRenderExecution.cpp(587): XQEException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(323): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): XQEException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(178): XQEException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): XQEException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): XQEException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): XQEException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableAssembly.cpp(119): XQEException: CCL_RETHROW: RSTableAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableRowAssembly.cpp(177): XQEException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): XQEException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(417): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableAssembly.cpp(119): XQEException: CCL_RETHROW: RSTableAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSTableRowAssembly.cpp(177): XQEException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(367): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): XQEException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(417): XQEException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSSingletonAssembly.cpp(126): XQEException: CCL_RETHROW: RSSingletonAssembly::assembleAssembly/RSAssembly.cpp(677): XQEException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(519): XQEException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(586): XQEException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(678): XQEException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(279): XQEException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(170): XQEException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1153): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1151): XQEException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1108): XQEException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): XQEException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): XQEException: CCL_RETHROW: QFSQuery::Execute v2XQEConnector.cpp(289): XQEException: CCL_THROW: XQEConnector::send

Can you please suggest on the same ?

One more thing, we were planning to change the measure because of the performance hit.

Thanks & Regards,
XYZ

[CognosPaul]

Are you using Hierarchy - Org Hierarchy Tree anywhere in the report itself? If so, make sure that data item has the expression instead of using a slicer.

[xyz]

Hi Paul,

Thank you very much. Even removing the slicer doesn't affected much on the performance side.

Finally we have decided to use other measure, which doesn't have any averages simple sum.


Thanks & Regards,
XYZ