This discussion is archived
10 Replies Latest reply: Oct 30, 2012 3:16 AM by saranya.a RSS

calculated measures

saranya.a Newbie
Currently Being Moderated
Hi,
i want to crate a calculated measure for the below case.. can anyone pls help me on this??

i have grade as a dimension which will have values :good,poor. and i have cluster as another dimension which will have values:cluster1,cluster2.. now i need the count of good grades across each cluster per month and percentage of good grades across each cluster. is it possible to find this in calculated measure.?? i dont have any stored measure which calculates the count of grades. so is it possible to derive calculated measure for the above case??
  • 1. Re: calculated measures
    DavidGreenfield Expert
    Currently Being Moderated
    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?
  • 2. Re: calculated measures
    saranya.a Newbie
    Currently Being Moderated
    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.
  • 3. Re: calculated measures
    DavidGreenfield Expert
    Currently Being Moderated
    It sounds like you want to calculate COUNT DISTINCT. This is a regular topic on this forum, as shown in these recent posts.

    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:
    CREATE OR REPLACE VIEW call_count_view AS
    SELECT DISTINCT grade, time, cluster, call_id, 1 AS call_count
    FROM fact
    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.
  • 4. Re: calculated measures
    saranya.a Newbie
    Currently Being Moderated
    Hi David,

    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?
  • 5. Re: calculated measures
    DavidGreenfield Expert
    Currently Being Moderated
    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'.
  • 6. Re: calculated measures
    saranya.a Newbie
    Currently Being Moderated
    Hi David

    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
  • 7. Re: calculated measures
    DavidGreenfield Expert
    Currently Being Moderated
    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) 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.
    <HierarchyLevelMap
      Query="SYS.DUAL"
      KeyExpression=" 'ALL_CALLS' ">
    </HierarchyLevelMap>
    Another approach is to define a SQL VIEW on top of your table to be something like this:
    CREATE OR REPLACE ALL_VIEW AS
    SELECT 
      'ALL_CALLS' AS ALL_CALLS,
      t.*
    FROM <your table> t
    Just map the dimension to this view instead of to the base table.
  • 8. Re: calculated measures
    saranya.a Newbie
    Currently Being Moderated
    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??
  • 9. Re: calculated measures
    DavidGreenfield Expert
    Currently Being Moderated
    I would add an 'ALL_GRADES' meber to your GRADE dimension

    ALL_GRADES
    - GOOD
    - POOR

    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.
  • 10. Re: calculated measures
    saranya.a Newbie
    Currently Being Moderated
    Hi David..

    Great!!! Really thanks a lot for ur support. your responses are really helpful

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points