Calculated Dimension Filtering

[ttgchicago]

Alex -

Is there any way to allow users to create their own calculated *filters* on the dimension items. Users want the ability to do something like "show me only the top 10 customers from my sales cube." The actual uses for this would be endless.

I thought something similar to the caluculated measure editor in the dimention manager could allow for this:

User clicks on a "Calculated Dimension Filter" button and then in a similar manner as the calculated measure manager, they could build something like this in their dimension filters:

begin
Result = ([Customer][Sales][RANK] >= 10)
end

The internal code would then use the above formula to do something like what you have in the demo code for "Hideing All Rows w/Less than 3% of Total" programatically.

Can this be done now (aside from filtering the SQL)? Do you think something like this could be added in the future?

Thoughts?

[AlexZencovich]

Actually I think there is wrong point to view of customers (I know they mostly should be right, but...)

What mean top 10 sales? If you have only one dimension you can easy get your 10 top. but if you split that by months for exampel - you will have own top10 for each month and it will not always the same top10..it really will top30 may be. Then, if you will deep to Ware...there will own leaders - top10 for each ware etc.

The way you describe can be easy implemented with VCL - just look how split dimensions for Pareto/ABC analysis.

If you may suupose some like-universal formula to describe that..I think it will not too complex to implement.

[ttgchicago]

YOUR customers are brilliant ... and extremely good looking as well!

The filters would be just on the dimension itself, not on the splits. In your example, if there was a hierarchy CUSTOMER/MONTH/WARHOUSE and the filter was [CUSTOMER][SALES][RANK] <= 10, then we would only see the 10 customers that fit that criterial, but then all MONTHS and ALL WAREHOUSES beneath that.

It would act no differently than if you went into the dimension editor and click off all CUSTOMERS other than the top 10 manually, except for the fact that it wouldnt actually turn off those values, but it would never even bring them into the dimension in the first place.

You would then be able to save this "calculated dimension filter" in a map and it would always just show you the cube, filtered by the top 10 customers in sales (or whatever the calculated filter would be).

Obviously, you could then also mix and match these custom filters (top 10 customers in sales, top 5 warehouses where inventory > 100000 units)

Does this make sence?

[AlexZencovich]

No, no, you just mix the points.

If we talk about static report - you are right, it can be easy done just by code like

for i := 0 to Mpa.RowCellsCount - 1 do
begin
PivotCube.Intf._RowTotalRank(PivotMap1,i,f);
if f > 10 then
PivotMap1.RowCells[i].Visible := False
end;

but problem happened if we have another dimension placed in columns.
In that case we colud not go by RowTotalRanks because 11 plase in row total ranks may be 1 place in selected item on columns.

but we cannot hide row for selectd column only - it will hided for all columns.

So really problem is - how determine criteria which will used for filtering.

IF we talk about dimension-based filter it mean we talk about Row/Column total ranks(according to example, but may be values too).
But this is just a case, not common way. Common way for OLAP, I think is several dimensions on rows and several - on columns at one time.

Hide data by 1 dimension - you will get wrong(false) results for another. But if this is required by customer - it is easy fro implement in code like above.

[ttgchicago]

Here's how I would hope it would work (Im open to suggestions as to what may or may not be possible). The "Calculated Dimension Filters" will be made up of 5 parts [DIMENSION][MEASURE][VIEW] operator <value>. The [VIEW] would be any view available in the measure.

Lets assume we have a simple cube with 2 dimensions:

1) CUSTOMER: Alex, Bob, Charlie, Dave, Ed
2) PART : Part1, Part2, Part3, Part4, Part5

We have 2 measures: Sales ($), Sales (Units)

A user adds a "calculated dimension filter": [CUSTOMER][Sales ($)][RANK] <= 3

Assuming that the rank of the customers by Sales ($) is Alex=1, Bob=2, etc., As soon as the user saves their "calculated dimension filter", all we see in the customer dimension is [Alex, Bob, Charlie]. Dave and Ed no longer appear as they dont meet the filter criteria.

The user could then add another "calculated dimension filter" [PART][Sales (Units)][VALUE] > 100. Now maybe only Part2 & Part5 show up as valid dimension records because they are the only 2 parts that qualify.

It wont matter how the dimensions / rows are created, because the records are filtered out of the dimension.

[AlexZencovich]

ok. I understood what you mean, althrough I still could not understand how it handel situation if
Alex has rank 1 for part3,4
and Ed has rnak 1 for part 1

but after your filter Ed will disappeared at all even if difference between totals of Alex and Ed (in absolute values) near ~1%


But I will wait for other suggestions and I will think on your proposal. It is interesting feature probably, I just would like make it really useful for customers and not just "yet one cool feature"

[ttgchicago]

Alex -

Have you had any additional thoughts on this topic? 90% of the requests Im getting from my customers at this point are things like "How can I show the top 20 items by customer for the top 10 customers."

As of right now there is no way to show them this report without coding for it (or is there?)

[AlexZencovich]

As you can see there are no other suggestions here. I think I will implement that way as you suggest (by total value) in nearest future

[ttgchicago]

>>I think I will implement that way as you suggest (by total value) in nearest future<<

Thats wonderful news.

Thanks for the great customer support!

[ttgchicago]

Any movement here? If there's anything I can do to help let me know.