Is there a way to create a calculated member based on a range of values?
For example, I have two measures in my cube: ResponseTime and TransactionCount.
What I want to do is create a calculated member that returns the total number of Transactions where the ResponseTime is between a certain range, i.e. between 1 and 2 seconds.
Here is what I've tried to do:
IIF( [Measures].[ResponseTime] > 1 And [Measures].[ResponseTime] < 2, [Measures].[TransactionCount], 0)
However, when I browse the cube, all the values for this calculated member are blank. Any ideas?
This problem can easily can generalized. My main problem is just finding a way to group values based on a certain range of values.
Thanks!!
Joel,
Given that you want to look at the "response time" for each individual transaction in your fact table and the cube is going to show you values aggregated based on selections that the end user makes, you may want to consider the following solution:
In your data source view:
Add a name calculation to your fact table named "ResponseTimeRangeKey" using a CASE statement:
CASE
WHEN ResponseTime <= 1 THEN 1
WHEN ResponseTime > 1 AND ResponseTime < 2 THEN 2
ELSE 3
END CASE
Next you can create a dimension table that can be used to build a cube dimension for your response time ranges using either a database view or a named query with a simple SQL statement something like this:
SELECT
1 AS ResponseTimeRangeKey,
'1 sec or less' AS ResponseTimeRangeName
UNION
SELECT
2 AS ResponseTimeRangeKey,
'1-2 sec' AS ResponseTimeRangeName
UNION
SELECT
3 AS ResponseTimeRangeKey,
'more than 2 sec' AS ResponseTimeRangeName
You can then relate your fact table to the new dimension in the data source view. Next you can create your response time range dimension and add it to your cube.
This solution has the benefit of allowing users to look at any measure in your cube using the response time ranges and will accurately aggregate your values for all dimensions.
HTH,
Steve
|||
Thanks for the help. I tried it out, and it worked brilliantly.
|||
I have somewhat the same scenario, I need to find a way to group values based on certain range of values. I have a calculated member, YTD investment. From my fact table I created a named calculation field for SegmentID with initial value of -1 to relate it with Segment Dimension. The key -1 has "Unknown" as the caption. This SegmentID is my dimension key on the fact table and the key of Segment Dimension. The Segment Dimension has the Min and Max attributes where the minimum and maximum range of values for a particular dimension member is defined.
Is there a way for me to populate or assign values to the named calculation field SegmentID by getting the dimension key of segment dimension where the YTD Investment falls on segment dim's min and max attributes/range?
Thanks in advance,
May Lanie
|||
May,
This is can be tricky depending on the dimensionality of your cube and how you want the YTD to roll up. I have done this type of "dynamic" bucketing before using a separate measure group that only contains the row count for the fact table. You can then use the SCOPE function to allocate values along a pre-defined range of buckets according to the value you want to use for distribution purposes. Here is an example of what I am talking about:
Scope([Measures].[Customer Count by Bucket]);
([Bucket].[Bucket Key].[< 50K]) =
Filter(
Existing Customer.[Customer Name].[Customer Name].Members,
([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 50000).Count;
([Bucket].[Bucket Key].[50K - 100K]) =
Filter(
Existing Customer.[Customer Name].[Customer Name].Members,
([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) > 50000 AND
([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 100000).Count;
....
End Scope;
The "Measures.[Customer Count by Bucket]" is the fact table row count measure. There is a predefined "Bucket" dimension that has key values indicating a range, but you could just as easily use your "low" and "high" member values. The "Filter" statements are used to count the number of customers that fall into the buckets using "Sales Amt". You would need to substitute your "YTD Amount" calculation.
HTH,
Steve
