10 Replies Latest reply: Oct 30, 2012 5:16 AM by saranya.a RSS

    calculated measures

    saranya.a
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            Hi David..

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