This content has been marked as final. Show 10 replies
When you say you need "the count of good grades", do you really mean something like "the count of applicants who got a good grade"? How would you specify this using SQL? Are you looking for a COUNT DISTINCT type of measure?
i want to find the number of calls whose grades are good and number of calls whose grades are bad across each clusters...i have call_id in my fact table.This call_id wil be unique for each call,but it is a randomly generated number(like 230,231,232...etc).so i need to determine the count of call_id where grade = good and count(call_id) where grade = bad.. i couldn't include the call_id as a measure, since its value gets added since it gets aggregated..
is this possible to derive the above in calculated measure.. ??
in short i need to find the count of good grade calls and count of bad grade calls across each cluster.
It sounds like you want to calculate COUNT DISTINCT. This is a regular topic on this forum, as shown in these recent posts.1 person found this helpful
A measure to calculate distinct count
Count of Weeks where a certain measure is > 0
The basic approach is to add a dimension, CALL_ID, that contains all distinct values of your call_id column plus a single ALL_CALLS member. Make this be a dimension of your cube along with GRADE, TIME, CLUSTER and whatever else you need. Create a single measure named 'CALL_COUNT' and map the cube to a new VIEW defined something like this:
Finally, define the aggregation of the cube to use MAX over GRADE, TIME, CLUSTER and then SUM over CALL_ID. (The SUM must be last in the list.) When you query the cube, ask for the ALL_CALLS member and you should get the distinct count of call_id values for the given GRADE, TIME, CLUSTER combination.
CREATE OR REPLACE VIEW call_count_view AS SELECT DISTINCT grade, time, cluster, call_id, 1 AS call_count FROM fact
Thanks a lot for the quick reply..
can u pls tell me how to add the ALL_CALLS member specified by you above.. also to which column in relational table i should map to this all_calls member?
The CALL_ID should have two levels, CALLS and ALL_CALLS say, which you should add to a new level hierarchy in the dimension. Once you have done this you can map the CALLS level to the call_id column in your table and map ALL_CALLS to the text literal 'ALL_CALLS'.
when i mapped the ALL_CALLS to the text literal 'ALL_CALLS' and maintained the cube, i get the below error:
Invalid Metadata Objects:
Invalid Object "CMUSER.CALLID_DM.CH.ALL_CALLS.MAP": "XOQ-01975: The DimensionMap for Dimension "CMUSER.CALLID_DM.CH.ALL_CALLS" must contain either a query or a From clause"
Invalid Object "CMUSER.CALLID_DM.LONG_DESCRIPTION": "XOQ-02517: The data type "NUMBER" of the base attribute or base measure is different from the mapped expression "VARCHAR2 (64) "."
Invalid Object "CMUSER.CALLID_DM.SHORT_DESCRIPTION": "XOQ-02517: The data type "NUMBER" of the base attribute or base measure is different from the mapped expression "VARCHAR2 (64) "."
what am i doing wrong?? pls help
This is an AWM bug, so make sure you have the latest version of AWM to see if that helps. To try and work around the bug I would proceed like this:1 person found this helpful
(1) Clear the mapping completely and press "Apply"
(2) Map both levels to the CALL_ID column and press "Apply". (This should let it derive the "query" mentioned in the error message.)
(3) Change the mapping for the ALL_CALLS level to the literal and "Apply"
If this still doesn't work, then you could fix it up by hand in the XML. Export the XML for the dimension after step (2) above and modify the element named <HierarchyLevelMap> for the ALL_CALLS level. Here is an example of a literal mapping.
Another approach is to define a SQL VIEW on top of your table to be something like this:
<HierarchyLevelMap Query="SYS.DUAL" KeyExpression=" 'ALL_CALLS' "> </HierarchyLevelMap>
Just map the dimension to this view instead of to the base table.
CREATE OR REPLACE ALL_VIEW AS SELECT 'ALL_CALLS' AS ALL_CALLS, t.* FROM <your table> t
Thanks david.. i have one more doubt.. now i need to determine the % of good calls --> ( no of good calls/total calls)*100. i tried applying this in user defined expression in calculated measure. but in that i don't know how to get the count of total calls. i am not clear on defining the expression.. can u pls help me on this??
I would add an 'ALL_GRADES' meber to your GRADE dimension1 person found this helpful
Then define a "share" calculated member (using the templates in AWM) to give you the share of the current grade (e.g. GOOD) to the fixed member ALL_GRADES.
Great!!! Really thanks a lot for ur support. your responses are really helpful