Filter function to suppress NULL values.

[vincydza@gmail.com]

Hi Cognos Gurus,

This report is based on a DMR cube and we are using  Cognos 10.2

This report is a rolling 12 months sales  by product line. The format of the report is as follows,   

Revenue                          Jun 2014  May 2014 ................ May 2013
Product Line                      99,999    88,888        .............   77,77777   

Revenue is the default measure of the report.

I have used the following expression in the report to suppress NULL values from the report.

filter([GO SALES].[All Products].[Product Line], tuple([2014],[Revenue]) <> NULL) 

The report runs successful  and NULL values are suppressed from the report.
The limitation with this expression is that the year is hardcoded [2014] in the expression.

To overcome this limitation and have a more generic expression. I have created a  Query expression  to determine the  'current year' using the expression  ancestor ([last_period],2) .
I then replaced the hardcoded year in the above expression with current year

filter([GO SALES].[All Products].[Product Line], tuple([Current Year],[Revenue]) <> NULL)    however this does not suppress  NULL values from the report. 

In query explorer I have observed that properties of [2014] is a member where as the property of 'current year' is member set also when I  drop 'CURRENT YEAR' into the report the values displayed in the report is 2014. The expression for 'CURRENT  YEAR' is accurate.

I have used  expressions  'current  year' = item(ancestor ([last_period],2) ,0) and   'current  year' = member(ancestor ([last_period],2) )  to retrieve the member of the year level  however this does not resolve the issue.

Can you please advise how the generic expression  can be tweaked so that year is not Hardcoded in the expression.

Regards

Vince

[MDXpressor]

Hi Vincy,
Lets park the question of zero suppression for now.  If you're not using the Suppress Zeros feature, I'll guess there's an underlying problem with your query which may or may not be solved by getting your rolling window right.  With that, lets talk about your rolling 12 month window, instead.  It's a long enough conversation...

Is there any intention to automate this report and schedule it to run on a periodic basis?  Or is prompting ok? 

The following prompted solution is my goto for selecting members by user prompting.  I NEVER user filters in my data items, or in the filter UI for an OLAP source. EVER!  This is the first statement I put into my queries, and it's most important piece of OLAP I've learned.  It not only give the ability to filter, but the ability to drive relative postions in the hierarchy and that creates dynamic reports, saving you time and effort by allowing users to leverage whichever parts of the hierarchy are important to them at runtime.  It's a rare occasion that I do use a filter, and never against a dimension with a lot of levels and members. 

Filtering in SQL is good, it reduces the number of records the BI has to process.  Filtering using an OLAP source is bad, since the entire dimension is called, and then the results are filtered by the BI after the data call from source is processed.   (Although you're using a MDR, so you can't win either way :) MDR=Most Difficult Reporting).   In fact, using MDR, it might be even more critical that you isolate members so that the underlying SQL can can be retrieving the fewest possible db records.

Prompted Date Selection
If this report is to be run by a live user, I suggest this approach, it uses a very powerful statement which will allow your users to select any member of a specified:

Hierarchy:

Code Select Expand

[Namespace].[Dimension].[Hierarchy]->?isolated member?
(generates a tree prompt and represents every member in the hierarchy, user drills to select)

Level:

Code Select Expand

[Namespace].[Dimension].[Hierarchy].[Level]->?isolated member?
(generates a value prompt (drop list) populated by the members of the specified level)

In either event above, you can create a multiselect by nesting the statement in a set() function.

Code Select Expand

set(
      [Namespace].[Dimension].[Hierarchy]->?isolated member?
)

This is a member selection, in the end.  Not a filter (though it could and should be nested in your container or added to your slicer to create a filtering effect). The downside is that you now have a required prompt since your prompt is in a data item.  However, its a member, and this can be fed into another calculation for a relative member or set of members. 
A simple way to form a rolling period based on a prompted variable:  nest one of the above statements in a lastPeriods() function.  I'll assume you've used the code above (whichever is more relevant to your task), and created a new data item called [prompted member selection] with the appropriate syntax.

Code Select Expand

lastPeriods( 12, [prompted member selection] )

BTW, if you prompted them for the window size, and used the Hierarchy example above, users could set their own windows, levels, and ending period, saving you having to create multiple reports for basically the same function:

Code Select Expand

lastPeriods( ?window size?, [prompted member selection] )
If the user enters a ?window size? of 3 and selects the 2014 member from the time dimension with the ?isolated member? prompt, they'd get a rolling 3 year window ending with 2014.
If the user enters a ?window size? of 8 and selects 2014Q4 member from the time dimension with the ?isolated member? prompt, they'd get an 8 quarter rolling window ending with 2014Q4.
If the user enters a ?window size? of 12 and selects Jun-2014 member from the time dimension with the ?isolated member? prompt, they'd get a 12 month rolling window.
(Charge them triple for this report)  :)

(note: lastPeriods assumes you're using the member variable as the ending date of the window.   To use the member as a starting date for your rolling window use a negative index.  lastPeriods(-10, [2014]) would give you data from 2014 to 2024.)

Automated or Scheduled Date Selection
Now, if you aren't allowed to prompt the user, (this is generally the case for scheduled reports), then you have a different animal...  Cube structure will be important for that conversation, and can solve your issues in the reporting layer.  Be sure to work with your cube designer to get a cube that is easy for your users to build reports from.  If a rolling 12 month window is common request for reports in your business, add a specific hierarchy to your cube to deal with it.  It will save your users a lot of hassle.

In this case I'll assume your users want full access to the time dimension for drilling, and so  you can't use a nice MTD, QTD, YTD or custom built hierarchy from the cube.  I will also assume that you do not have 'future' members.  i.e. your cube is not populated with members beyond the latest period. Sometimes cubes will populate an entire year or quarter as soon as one member of that level has data.  Lastly, I will assume that the last period is the period from which you will want to build your rolling set.  i.e. Last 12 months from the latest month.

In this case you can isolate your time member by using nested lastChild calls.

lastChild([Root Time Member])
This should produce the [2014] member assuming your not using Fiscal Years. considering the current date is June 2014.

so the last child of the last child is going to likely give you the latest quarter in most time dimensions.  And the last child of the last child of the last child will probably give you latest month.

Code Select Expand

lastChild(
      lastChild(
          lastChild( [Root Time Member] )
      )
)

so if you called that data item [isolated latest month member] then you could create your rolling with:

Code Select Expand

lastPeriods(  12, [isolated latest month member] )


Man, that was an OPUS!  Sorry for all the text, but this thread should be in every OLAP reporter's favorites.  Isolating a member is ALWAYS a cheaper call than using filters.  Isolate your member, then use the power of OLAP to define relative sets from that.  Nest or slice, don't filter.  Filtering is for those relational db losers who are bound to columns and tables!  MDX is a complete shift of paradigm from SQL.  You're working with elegance of hierarchies and intersections now.  Park your SQL queries at the door and enter with a clean slate.

He wandered off mumbling under his breath "I'd rather speak to the ignorant, rather than the misinformed.  At least the ignorant have no preconceived notions that you need to break."

And in case I wasn't clear, the worst habit to bring from SQL to MDX is the notion of filtering. ;)

Let's see you top THAT MFGF!

[vincydza@gmail.com]

Hi MDXpressor,

Appreciate your recommendation however what I have observed is that zero suppression  report  is not efficient when compared to report with filter() function. I had 2 identical report on one report I used zero suppression and on the other report I used filter() function to excluded NULLs from the report.

The Zero supression report took approx 20 - 25 seconds to finish whereas the report with the filter() function took 5 seconds to finish.

What could be the reason?. 


Vince

[MDXpressor]

My suspicion is that there is still something inefficient in the query somewhere, and the zero suppression is bringing that inefficiency to the forefront. I know that answer sucks, but given that your model is MDR, this is going to be tough to isolate.  There are a lot of moving parts in an MDR query.

Comparing zero suppressed to the filtered version seems like an incorrect comparison to me, but I'll admit my skills in MDR are thin at best.  As I understand the mechanics, when you filter(), you are waiting for the return of the entire dimension from the datasource, and then in the BI layer, applying a filter to the results of that data call.  I'm not sure where the suppression rules are applied. 

So as explained to me by one of the RS Product Managers in charge of the OLAP engine (going back 10 yrs), Isolating your members reduces the intersections which are queried at source.  Similar to more records in SQL slowing your query, more intersection in OLAP will slow your query.  While we weren't speaking directly to MDR at the time, we were speaking in terms of query efficiency.  Cognos is lazy.  It only calls the code necessary to display data which is displayed on a page.  My hunch is that MDR works in a similar fashion, it only executes code (including necessary SQL based filtering) to put the requested data on a page.  So, if you have a limited number of members, the SQL code invoked should have a focused set of filters, which in turn, reduces the recordset from your SQL source, and thereby reduces response times.  Or... That's my thought process, anyhow.

Back to the comparison between filter() vs. suppression, in this case, the BI may be able to process the results of your recordset faster with filter() than it can with suppression; perhaps something gets passed directly through to SQL code, I just don't know.  I suspect in both instances, however, you are returning more data than you need to, and that itself is causing some performance issues.  I think I would be interested to see how unsuppressed compares with unfiltered against the entire hierarchy.  I can't put my finger on why, but that seems like a more accurate comparison from a performance perspective.

If you're going to compare filtering techniques, to get the best from OLAP, you need to isolate your members this is how cubes were built to operate, my apparent lack of knowledge toward MDR notwithstanding.

BTW, I'm sure there's a great reason why, but is there a reason a real cube is not being used?  Seems contradictory to be worried about performance on an MDR solution.  The whole tradeoff of in MDR is you trade real-time data access for quick report-time query performance.  I just haven't been exposed to MDR enough, I guess.

[vincydza@gmail.com]

Appreciate your honest feedback,  Thanks Jean.

Regards

Vince

[MDXpressor]

Nimrod is far more comfortable in the MDR space than I am.  There is a similar thread going on in which he comments on filtering in MDR using DQM.  You may find the answers you need in his information...

http://www.cognoise.com/index.php/topic,25169.0.html