i haven't used calculated measures in AWM so far. i have duration as stored measure. Now i need to derive average duration per month as a calculated measure. when i selected create new calculated measure, i find these calculation types: Simple math,sharing and ranking,user defined expression and olap dml expression. i dont know which calculation type to use for my case and how to use it..??
can anyone pls help me on this????
Lets assume that:
(1). your TIME dimension levels are DAY -> MONTH -> QTR -> YEAR, using standard Gregorian calendar.
(2). your cube name is CUBE1, and stored measure name is DURATION
(3). measure DURATION is loaded at DAY level and its aggregation is SUM
(4). TIME dimension has an attribute TIME_SPAN
Create the calculated measure AVG_DURATION using OLAP Expression syntax, as: CUBE1.DURATION/"TIME".TIME_SPAN
So when you will look at DAY level data, then AVG_DURATION will be equal to DURATION/1
and when you will query (lets say) JUNE level data, then AVG_DURATION will automatically be DURATION/30
and for Q2 level data, then AVG_DURATION will be DURATION/91
You can add CASE statement, if you only want to show data at MONTH level and NA for other levels.
thanks for the reply. i tried as specified by you below,but am not successful. when i defined the calculated measure as
"clustercube.DURATION/"datetimedn".TIME_SPAN " in OLAP DML expression. clustsercube is the cube name and datetimedn is the dimenison name of time dimension.
when i select create, i get the below error:
"Invalid Metadata Objects:
Invalid Object "CMUSER.CLUSTERCUBE.AVGDUR": "ORA-34492: Analytic workspace object CLUSTERCUBE.DURATION does not exist." Am i missing something?? pls help..
i just want average duration of clusters on a monthly basis. i also tried the the other options like period to date,moving average.... but am not successful..pls guide.
(1). Select "*OLAP Expression Syntax*" as Calculation Type in AWM, when creating calculated measures. You are saying that you selected "OLAP DML Expression", which is a different syntax.
(2). Use uppercase in your syntax. Guidelines for specifying object names in OLAP Expression syntax is at http://download.oracle.com/docs/cd/E11882_01/olap.112/e17122/dml_commands_1004.htm#BGECJJGA
(3). OLAP Expression syntax documentation is at http://download.oracle.com/docs/cd/E11882_01/olap.112/e23381/toc.htm
(4). Explanation of Calculation templates in AWM is at http://download.oracle.com/docs/cd/E11882_01/olap.112/e17123/calculations.htm#CIHJGEJA
(5). If still having problem then paste the output of the following three queries: select owner||'.'||cube_name||'.'||measure_name "MEASR" from all_cube_measures where measure_name like '%DURATION%'
*;* select owner||'.'||AW_NAME||'.'||DIMENSION_NAME "DIM" from all_cube_dimensions where dimension_type = 'TIME'
*;* select owner||'.'||DIMENSION_NAME||'.'||ATTRIBUTE_NAME "ATTR" from all_cube_attributes where DIMENSION_NAME like '%DATETIMEDN%'
Thanks Nasar.. It worked. Now i have another doubt..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. 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??