Community
Participate
Working Groups
Created attachment 85149 [details] report design Description: Attached report includes a crosstab which has two measures, when define a measure filter on QUANTITYORDERED measure, the result is all the QUANTITYORDERED in USA has been filtered. But the other measure's result is hidden. I think we should not hide the USA level, because the other measure has value in this level. Build number: 2.3.0.v20071212-0800 Steps to reproduce: 1, Open the attached design file preview 2, Or New a crosstab with two measures 3, Define a measure filter on one of the measures 4, Preview and check the structure Expected result: USA level should not be hidden. Actual result: USA level is hidden. Error log:
Out current implementation is based on an SQL exprericed user's perspective. We need more time to see if there is enough requirements from users. So I set the status of this to "FUTURE".
measure filter on Crosstab should not change the structure of the Xtab, it should only hide values from the measure cell that doesn't meet the filter criteria and update the aggregations to exclude values from those measure cell. However, no changes should be made to the level/dimension of the Xtab.
I believe the current behavior is correct. Generally, the measure filter will not change the structure of xtab(dimension/level), unless that measure filter leads to a result that in the whole edge no valid measure value can be show. In attached report design, after the measure filter being applied, all the measure values for "USA" dimension would be empty. So we remove it from the xtab output. The following example may help to describe the problem: Say, we have following crosstab: | 1999 | 2000 | | VALUE | VALUE | CHINA | 100 | 120 | UK | 150 | 110 | USA | 300 | 260 | And we want to apply a filter measure["VALUE"] > 250. As we can see, all the USA measures should be filtered out. If we decide not to change the crosstab structure, then after the filtering we got following: | 1999 | 2000 | | VALUE | VALUE | CHINA | 100 | 120 | UK | 150 | 110 | USA | | | A USA row, without any measure values, is still exist in the output. It is hard to say this is what user expected. And it is also not what we expected when initially support measure filter. Our current behavior would provide following: | 1999 | 2000 | | VALUE | VALUE | CHINA | 100 | 120 | UK | 150 | 110 | As we can see, in our current result, the USA row is removed as a whole.This could be the result that expected by most of users. So I think we can still keep the bug to a "future" milestone, until enough community input is received for the request of the features described in the bug report.
Further clarification: There is no such a circumstance that some measure of "USA", say, "QUANTITYORDERED", is filtered out, but the other measure, say "PRICEEACH", remain values. All the cube filters finally work against the fact table, and for all the filters the atomic unit that being impacted would be a fact table row. It is meaningless to have a fact row being filtered out still play a role in the calculation of aggregations.
I think there are three different behavior/features on filtering with measures: 1. conditional formating on measure - measures are hidden based on a condition. Xtab structure is not changed, and hidden measures are still included in the aggregation. 2. filtering of measure - measure M1 in a cube cell (x,y,z,...) is excluded based on a filter condition on the measure M1's value alone. The excluded measure M1 value on cube cell (x,y,z,...) should not be included in aggregations of M1. In this cases, the Xtab structure should not changed. If there is other measure M2 on the same cube cell, M2 should continue to show on the Xtab as well as being included in the aggregation of M2. 3. filtering of cube cell based on measure value with dimension qualifier - the purpose of this filtering is to exclude cube cells (x,y,z,...) in the cube based on the measure value(s) condition for those cells. In Lin's Examples, the condition would be "exclude all cities whose value in all years are greater than 250". In this case, the cube cells that meets the condition shall be removed from the cube, hence both the level/element labels and associated measures shall be removed from the Xtab. Such filtering condition shall also support: (a) dimension qualifier. for example, the condition could be "Exclude cities whose value in year 1999 is greater than 250". (b) expression on mulitple measures on the same cube cell (x,y,z..). for example, the condition could be "Exclude all cities whose sales < expense in all years." (c) expression on a measure among different elements on a level. for example, the condition could be "Exclude all cities whose 2008 sales < 2007 sales". Do we already support feature #1 with conditional formating on measure? Is the current filer on measure feature intended to support feature #2 or feature #3?
Sorry for the late reply. Regarding to Wenfeng's question, we already support #1 (by mapping feature supported by engine) and #3 (by filter on measure). For #2, it is actually not a cube filter behavior. The cube is based on facttable, and in each row of facttable, both measure "M1" and "M2" exist. The cube filter is targeting to filter out facttable rows. So after a filtering apply, for each row of the facttable only following hold: 1. Both M1 and M2 take part in aggregation calculation 2. Neither M1 or M2 take part in aggregation calculation So I would not call #2 a cube filter behavior. However, what described in #2 could be supported by aggregation filter. Specifically, by support using binding reference (data[""]) in the filter definition of aggregation bindings. This is an advanced feature and may be supported in future.
I think we might be using different terminology in the discussion. I see there are two types of filter: level filter and measure filter. 1. Level Filter - User wants exclude certain levels base on a business criteria. Simple level filter's condition might noly involves the level's attributes, for example, city name start with "a". But more advanced filter would uses measures in the filter condition. For example: (a) measure with dimension qualifier: "Exclude cities whose value in year 1999 is greater than 250". (b) filter condiftion with mulitple measures on the same cube cell (x,y,z..). "Exclude all cities whose sales < expense in all years." (c) filter condiftion with a measure among different elements on a level. "Exclude all cities whose 2008 sales < 2007 sales". As you can see the target of filter is the city level, the expression of the filter uses sales measure. The definition of such filter should be associated with the level, since the expression could use mulitple measures. 2. Filter on measures - this type of filter is to exclude certain values in a measure. The usage of such filter is to analyze the impact of exluding certain values. For example, what will the margin of a product in 1999 if we exclude the transactions in 1999 whose amount < 100 dollar. In such case, the filter operation shall only remove measure values that meets the filter condition, AND update the aggregations and calculated columns that are based on this measure. It should not change the Xtab structure. It should not remove other measure that is not the target of this filter.
It is not likely that we are going to support #2 in 2.5.2. I move the bug to future as an hint for further enhancement in cube query.
