SSM 10 - Replace calculations question

[Former Member]

Hello,

We have the following problem: we need to have, as a consolidation formula in a dimension, the MEAN (average)  , not SUM as it automatically creates it.

We've read that this could be accomplished by using the 'Replace Calculations' but we don't know exactly how to use it.

Can you please help us a little bit?

It would save us from trying workarounds whichwould mean days or maybe weeks' work.

Thank you!

Regards,

Andreea D.

[Former Member]

Hi

I'm not familiar with the "Replace Calculations" that you make mention of. We create our KPIs via the BI Connector as some of our data comes from a BW system. When we create a manual KPI, the system has various options for consolidation(with the average being one of them)

[Former Member]

Hi Zimkhita

I don't use cube builder and so was not aware of this option. Cube builder generates PAS code Would you be able to look at the generated code and describe how it achieves an average?

Thanks

Cliff

[Former Member]

Hi Cliff,

This is the code that I can see from the wfmde file...maybe you can make sense of it...but from what I see looks like there' s a 'calcavg_pmdb.pro' that's used.

LSS>
LSS> ... WFMDE_SETUP.PRO FOR ORACLE
LSS>
LSS> output REVIEW_LOAD;ZOSSPBI over
LSS> echo LOADSTARTED
LOADSTARTED
LSS> output off
LSS>
LSS> checkpoint update
LSS> checkpoint freeze
LSS>
LSS> set control WFMDELINK SSM_CB_EA
LSS>
LSS> access lslink
LSLink> connect SSM_CB_EA
LSLink>
LSLink> ... clear the scorecard status table
LSLink> begin
> DELETE FROM CPMS_SCORE_STATUS
> WHERE CPMS_CONTEXT_ID IN (SELECT ID FROM CPMS_CONTEXT
> WHERE CONTEXT_NAME IN (SELECT CONTEXT FROM CPMS_PASUSER
> WHERE UPPER(USEDB) = UPPER('ZOSSPBI')))
> end
LSLink>
LSLink> ... create the LOADALL proc that will run wfmde_load.pro once for each content set
LSLink> begin
> SELECT DISTINCT CPMS_EA_MASTER_ID
> FROM CPMS_EA_DETAIL
> WHERE ID IN
> (
> SELECT DISTINCT CPMS_EA_DETAIL_ID
> FROM CPMS_EA_DATA, CPMS_EA_CUBES
> WHERE CPMS_EA_DATA.CPMS_EA_CUBES_ID = CPMS_EA_CUBES.ID
> AND UPPER(CUBE_NAME) = 'ZOSSPBI'
> )
> end
LSLink>
LSLink> lss create code = "job 'wfmde_load.pro' '" + CPMS_EA_MASTER_ID + "'"
LSLink> output proc LOADALL;PIPADMINDEFAULT
LSLink> peek create nohead nonumb
job 'wfmde_load.pro' '147850914151359'                                                                                                                                                                                                                                             
LSLink> output off
LSLink>
LSLink> ... before loading data for metric sets, create and job proc to remove data for all the vars defined
LSLink> ... in these sets - can't do in metric set load proc because same var can be part of multiple sets
LSLink> begin
> SELECT DISTINCT 'remove data ' || DIM0_MEMBER || ' sure'
> FROM CPMS_EA_DETAIL, CPMS_EA_CUBES
> WHERE CPMS_EA_DETAIL.CPMS_EA_CUBES_ID = CPMS_EA_CUBES.ID
> AND UPPER(CUBE_NAME) = 'ZOSSPBI'
> AND INUSE = 1
> end
LSLink>
LSLink> output proc REMOVEDAT;PIPADMINDEFAULT
LSLink> peek nohead nonumb
remove data BI_TEST_ACT sure                                                                                                                                                                                                                                
LSLink> output off
LSLink>
LSLink> end leave
LSS>
LSS> job REMOVEDAT;PIPADMINDEFAULT
LSS> remove data BI_TEST_ACT sure
Data For 1 Variable Removed
LSS>
LSS>
LSS> ... create table with info from CONSOLINFO doc set - joined w/ E&A tables to create consol proc
LSS> access lslink
LSLink>
LSLink> ... just in case, drop old
LSLink> DROP TABLE CPMS_TMP_ZOSSPBI
ORA-00942: table or view does not exist

LSLink> ... create new
LSLink> begin
> CREATE TABLE CPMS_TMP_ZOSSPBI
> (
> VAR_NAME VARCHAR(100),
> PROC_NAME VARCHAR(50),
> DIMSELECT VARCHAR(20)
> )
> end
LSLink>
LSLink> ... done with access lslink for now
LSLink> end leave
LSS>
LSS> ... use access external to read CONSOLINFO doc set and create code to fill consol table
LSS> access external
EXT> use CONSOLINFO;ZOSSPBI
EXT> description free , decimalpoint . varname text 96 procname text 50 dimselect text 20
EXT> lss create code = "INSERT INTO CPMS_TMP_ZOSSPBI VALUES ('" + varname + "','" + procname + "','" + dimselect + "')"
EXT> output proc FILLCONSOLTBL;PIPADMINDEFAULT
EXT> peek create nohead nonumb
INSERT INTO CPMS_TMP_ZOSSPBI VALUES ('BI_TEST_ACT','calcavg_pmdb.pro','INPUT')                                                                                                                                       
INSERT INTO CPMS_TMP_ZOSSPBI VALUES ('BI_TEST_TAR','calcavg_pmdb.pro','INPUT')                                                                                                                                       
EXT> output off
EXT> end
LSS>
LSS> ... back to access lslink to run the inserts
LSS> access lslink
LSLink> job FILLCONSOLTBL;PIPADMINDEFAULT
LSLink> INSERT INTO CPMS_TMP_ZOSSPBI VALUES ('BI_TEST_ACT','calcavg_pmdb.pro','INPUT')
LSLink> INSERT INTO CPMS_TMP_ZOSSPBI VALUES ('BI_TEST_TAR','calcavg_pmdb.pro','INPUT')
LSLink> end leave
LSS>
LSS> ... done with setup needed to process all metric sets
LSS>
LSS> ... job the procedure that was created above - this will load and consolidate
LSS> ... all metric sets, one at a time
LSS> job LOADALL;PIPADMINDEFAULT
LSS> job 'wfmde_load.pro' '147850914151359'
LSS> ... WFMDE_LOAD.PRO
LSS> ... run once for each content set defined through Metrics Mgr
LSS>
LSS> clear status
LSS>
LSS> access lslink
LSLink>
LSLink> ... set a bunch of control variables for the load - id passed to proc as 147850914151359 -
LSLink> ... some of dim name columns will be empty - this sets them to EXTRAn so there
LSLink> ... is a name to assign to them for the loa
LSLink> output proc SETCONTROL;PIPADMINDEFAULT over
LSLink>
LSLink> begin
> SELECT 'set control PDY ' || PERIODICITY FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT 'set control DIM1 ' || nvl2(DIM1_NAME, DIM1_NAME,'EXTRA1') FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT 'set control DIM2 ' || nvl2(DIM2_NAME, DIM2_NAME, 'EXTRA2') FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT 'set control DIM3 ' || nvl2(DIM3_NAME, DIM3_NAME, 'EXTRA3') FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT 'set control DIM4 ' || nvl2(DIM4_NAME, DIM4_NAME, 'EXTRA4') FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT 'set control DIM5 ' || nvl2(DIM5_NAME, DIM5_NAME, 'EXTRA5') FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> end
LSLink>
LSLink> peek nohead nonumb
set control DIM1 DEPARTMENT_BY_DIRECTORATE                                                                                                                                                                                                                  
set control DIM2 EXTRA2                                                                                                                                                                                                                                     
set control DIM3 EXTRA3                                                                                                                                                                                                                                     
set control DIM4 EXTRA4                                                                                                                                                                                                                                     
set control DIM5 EXTRA5                                                                                                                                                                                                                                     
set control PDY Monthly                                                                                                                                                                                                                                     
LSLink>
LSLink> ... figure out the across/down list for the content set
LSLink> begin
> SELECT ID AS MASTER_ID,
> PERIODICITY,
> nvl2(DIM1_NAME, DIM1_NAME || ',', DIM1_NAME) AS DIM1_NAME,
> nvl2(DIM2_NAME, DIM2_NAME || ',', DIM2_NAME) AS DIM2_NAME,
> nvl2(DIM3_NAME, DIM3_NAME || ',', DIM3_NAME) AS DIM3_NAME,
> nvl2(DIM4_NAME, DIM4_NAME || ',', DIM4_NAME) AS DIM4_NAME,
> nvl2(DIM5_NAME, DIM5_NAME || ',', DIM5_NAME) AS DIM5_NAME
> FROM CPMS_EA_MASTER
> WHERE ID = 147850914151359
> end
LSLink>
LSLink> lss create downlist = 'set control DOWNLIST variables,' + DIM1_NAME + DIM2_NAME + DIM3_NAME + DIM4_NAME + DIM5_NAME + 'TIME'
LSLink> peek create nohead nonumb
set control DOWNLIST variables,DEPARTMENT_BY_DIRECTORATE,TIME                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
LSLink> output off
LSLink>
LSLink> ... build a proc to select dimensions - again, some dim name cols will be empty and this time
LSLink> ... we want to skip them so there are no bad PAS select statements
LSLink> begin
> SELECT DISTINCT 'select var plus ' || DIM0_MEMBER CODE FROM CPMS_EA_DETAIL WHERE CPMS_EA_MASTER_ID = 147850914151359 AND INUSE = 1
> UNION
> SELECT DISTINCT nvl2(DIM1_NAME, 'select ' || DIM1_NAME, DIM1_NAME) CODE FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT DISTINCT nvl2(DIM2_NAME, 'select ' || DIM2_NAME, DIM2_NAME) CODE FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT DISTINCT nvl2(DIM3_NAME, 'select ' || DIM3_NAME, DIM3_NAME) CODE FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT DISTINCT nvl2(DIM4_NAME, 'select ' || DIM4_NAME, DIM4_NAME) CODE FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> UNION
> SELECT DISTINCT nvl2(DIM5_NAME, 'select ' || DIM5_NAME, DIM5_NAME) CODE FROM CPMS_EA_MASTER WHERE ID = 147850914151359
> end
LSLink>
LSLink> output proc SELVARSDIMS;PIPADMINDEFAULT over
LSLink> peek nohead nonumb
select DEPARTMENT_BY_DIRECTORATE                                                                                                                                                                                                                            
select var plus BI_TEST_ACT                                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                            
LSLink> output off
LSLink>
LSLink> ... need to leave access lslsink to clear created field list for next peek create
LSLink> end leave
LSS>
LSS> ... set the begin and end dates for this load - basing end on target data for both loads
LSS> ... but this can be refined to set a different end for actual if necessary
LSS>
LSS> access lslink
LSLink>
LSLink> output proc LOADDATES;PIPADMINDEFAULT over
LSLink>
LSLink> begin
> SELECT MIN(PERIOD) startdate
> FROM CPMS_EA_DATA, CPMS_EA_DETAIL
> WHERE CPMS_EA_DATA.CPMS_EA_DETAIL_ID = CPMS_EA_DETAIL.ID
> AND CPMS_EA_MASTER_ID = 147850914151359
> end
LSLink> lss create code =  'set control LOADBEGIN ' + substr(startdate,1,4) + '/' + substr(startdate,5,2) + '/' + substr(startdate,7,2)
LSLink> peek create nohead nonumb
set control LOADBEGIN 2013/02/01 
LSLink>
LSLink> begin
> SELECT MAX(PERIOD) lastdate
> FROM CPMS_EA_DATA, CPMS_EA_DETAIL
> WHERE CPMS_EA_DATA.CPMS_EA_DETAIL_ID = CPMS_EA_DETAIL.ID
> AND CPMS_EA_MASTER_ID = 147850914151359
> end
LSLink> lss create code =  'set control LOADEND ' + substr(lastdate,1,4) + '/' + substr(lastdate,5,2) + '/' + substr(lastdate,7,2)
LSLink> peek create nohead nonumb
set control LOADEND 2013/02/01 
LSLink>
LSLink> output off
LSLink>
LSLink> ... finished with access lslsink for now
LSLink> end leave
LSS>
LSS> ... job the procedures created
LSS> job SETCONTROL;PIPADMINDEFAULT
LSS> set control DIM1 DEPARTMENT_BY_DIRECTORATE
LSS> set control DIM2 EXTRA2
LSS> set control DIM3 EXTRA3
LSS> set control DIM4 EXTRA4
LSS> set control DIM5 EXTRA5
LSS> set control PDY Monthly
LSS> set control DOWNLIST variables,DEPARTMENT_BY_DIRECTORATE,TIME
LSS>
LSS> job LOADDATES;PIPADMINDEFAULT
LSS> set control LOADBEGIN 2013/02/01
LSS> set control LOADEND 2013/02/01
LSS>
LSS> ... set up the view for the load
LSS> job SELVARSDIMS;PIPADMINDEFAULT
LSS> select DEPARTMENT_BY_DIRECTORATE
807 Members of DEPARTMENT_BY_DIRECTORATE Selected
LSS> select var plus BI_TEST_ACT
1 Variable Currently Selected
LSS>
LSS> across nothing down variables,DEPARTMENT_BY_DIRECTORATE,TIME

Down List:
  VARIABLES                   1  test-Actual
  DEPARTMENT_BY_DIRECTORATE 807  Table View Sports Complex...
  TIME                    

Earliest Date Not Specified
  Latest 31 Jan 2013 (MO)

Attached Databases: ZOSSPBI(Frozen), SMREPORT, CGLIB, APLIB  User: MMADMIN
LSS> set period 2013/02/01 - 2013/02/01
LSS>
LSS> ... load all data for this content set
LSS> access lslink
LSLink>
LSLink> ... load actual
LSLink> begin
> SELECT
> DIM0_MEMBER AS "variables",
> DIM1_MEMBER AS "DEPARTMENT_BY_DIRECTORATE",
> DIM2_MEMBER AS "EXTRA2",
> DIM3_MEMBER AS "EXTRA3",
> DIM4_MEMBER AS "EXTRA4",
> DIM5_MEMBER AS "EXTRA5",
> PERIOD,
> ACTUAL AS "value"
> FROM CPMS_EA_DETAIL, CPMS_EA_DATA
> WHERE CPMS_EA_DETAIL.ID = CPMS_EA_DATA.CPMS_EA_DETAIL_ID
> AND SUBSTR(DIM0_MEMBER, LENGTH(DIM0_MEMBER)-2, 3) LIKE 'ACT'
> AND CPMS_EA_MASTER_ID = 147850914151359
> AND INUSE = 1
> AND READY = 1
> end
LSLink> lss create TIME =  substr(PERIOD,1,4) + '/' + substr(PERIOD,5,2) + '/' + substr(PERIOD,7,2)
LSLink> read
1 Record(s) Read, 0 Record(s) Skipped.
LSLink>
LSLink> ... load target
LSLink> begin
> SELECT
> DIM0_MEMBER AS "variables",
> DIM1_MEMBER AS "DEPARTMENT_BY_DIRECTORATE",
> DIM2_MEMBER AS "EXTRA2",
> DIM3_MEMBER AS "EXTRA3",
> DIM4_MEMBER AS "EXTRA4",
> DIM5_MEMBER AS "EXTRA5",
> PERIOD,
> TARGET AS "value"
> FROM CPMS_EA_DETAIL, CPMS_EA_DATA
> WHERE CPMS_EA_DETAIL.ID = CPMS_EA_DATA.CPMS_EA_DETAIL_ID
> AND SUBSTR(DIM0_MEMBER, LENGTH(DIM0_MEMBER)-2, 3) LIKE 'TAR'
> AND CPMS_EA_MASTER_ID = 147850914151359
> AND INUSE = 1
> end
LSLink> lss create TIME =  substr(PERIOD,1,4) + '/' + substr(PERIOD,5,2) + '/' + substr(PERIOD,7,2)
LSLink> read
0 Record(s) Read, 0 Record(s) Skipped.
LSLink>
LSLink> ... leave access lslink to clear created field list
LSLink> end leave
LSS>
LSS> ... create a proc for consolidating measures in this content set
LSS> access lslink
LSLink>
LSLink> output proc CONSOLVARS;PIPADMINDEFAULT over
LSLink> begin
> SELECT DISTINCT VAR_NAME, PROC_NAME
> FROM CPMS_TMP_ZOSSPBI, CPMS_EA_DETAIL
> WHERE UPPER(PROC_NAME) <> UPPER('NONE')
> AND UPPER(PROC_NAME) <> UPPER('ENTER')
> AND CPMS_EA_MASTER_ID = 147850914151359
> AND UPPER(DIM0_MEMBER) = UPPER(VAR_NAME)
> end
LSLink>
LSLink> lss create code = "job '" + PROC_NAME + "' " + VAR_NAME
LSLink> peek create nohead nonumb
job 'calcavg_pmdb.pro' BI_TEST_ACT                                                                                                                                                                                                                                                                                                                                                                                          
LSLink> output off
LSLink>
LSLink> ... done with access lslink
LSLink> end leave
LSS>
LSS> ... selecting everything before load so should be all set to consolidate
LSS> job CONSOLVARS;PIPADMINDEFAULT
LSS> job 'calcavg_pmdb.pro' BI_TEST_ACT
LSS> .... procedure to average up the dimension
LSS>
LSS> .... first consolidate the var being calc'd
LSS> set var 'BI_TEST_ACT' consol
LSS> rollup 'BI_TEST_ACT'
Using These Dimensions:  DEPARTMENT_BY_DIRECTORATE
ROL> remove everybody
0 Combination(s) Removed
ROL> add everybody
2 Combination(s) Added
ROL> end
LSS> consol 'BI_TEST_ACT'
2 Member Combination(s) Consolidated
LSS> set var 'BI_TEST_ACT' noconsol
LSS>
LSS> .... create var to count dim members for averaging up dimension
LSS>
LSS> .... needs to be re-calc'd for each kpi var so only populated
LSS> .... dimension members are counted
LSS>
LSS> create COUNTVAR temp like 'BI_TEST_ACT'
1 Variable Created
LSS> set var countvar consol
LSS> calc countvar if 'BI_TEST_ACT' is missing = 0 otherwise = 1
0 Member Combination(s) Calculated; 0 Consolidated
LSS> set var countvar noconsol
LSS>
LSS> .... now calc average of original var
LSS> calc 'BI_TEST_ACT' = 'BI_TEST_ACT' / COUNTVAR full
3 Member Combination(s) Calculated; 0 Consolidated
LSS>
LSS> .... all finished with count variable
LSS> remove var COUNTVAR sure
1 Variable Removed
LSS>
LSS> LSS>
LSS> LSS>
LSS> ... back from processing individual metric sets
LSS>
LSS> ... set earliest, latest and create LASTDATE doc set - base latest on actual only
LSS> access lslink
LSLink>
LSLink> output proc SETDATES;PIPADMINDEFAULT over
LSLink>
LSLink> begin
> SELECT MIN(PERIOD) AS startdate
> FROM CPMS_EA_DATA, CPMS_EA_CUBES
> WHERE CPMS_EA_DATA.CPMS_EA_CUBES_ID = CPMS_EA_CUBES.ID
> AND UPPER(CUBE_NAME) = 'ZOSSPBI'
> end
LSLink> lss create code =  'set control BEGINDT ' + substr(startdate,1,4) + '/' + substr(startdate,5,2)
LSLink> peek create nohead nonumb
set control BEGINDT 2013/02 
LSLink>
LSLink> begin
> SELECT MAX(PERIOD) AS lastdate
> FROM CPMS_EA_DATA, CPMS_EA_CUBES
> WHERE CPMS_EA_DATA.CPMS_EA_CUBES_ID = CPMS_EA_CUBES.ID
> AND UPPER(CUBE_NAME) = 'ZOSSPBI'
> AND ACTUAL IS NOT NULL
> end
LSLink> lss create code =  'set control ENDDT ' + substr(lastdate,1,4) + '/' + substr(lastdate,5,2)
LSLink> peek create nohead nonumb
set control ENDDT 2013/02 
LSLink>
LSLink> output off
LSLink>
LSLink> ... while in link sub-system, clean up temp tables created in mm db file
LSLink> DROP TABLE CPMS_TMP_ZOSSPBI
LSLink>
LSLink> end
LSS>
LSS> ... save current state of model because following code will cause rollback if
LSS> ... custom procedure doesn't exist
LSS> checkpoint update
LSS>
LSS> ... "hook" to run a custom procedure for this PAS model if it exists
LSS> set onerror continue
LSS> job 'custom_ZOSSPBI.pro'
ENV081:
File custom_ZOSSPBI.pro Not Found.
LSS>
LSS> output REVIEW_LOAD;ZOSSPBI over
LSS> echo LOADFINISHED-BEFORELATEST
LOADFINISHED-BEFORELATEST
LSS> show latest
  Latest 31 Jan 2013 (MO)

LSS> output off
LSS>
LSS> set onerror clear
LSS>
LSS> ... job the procedure created - will set latest and update LASTDATE doc set based on _ACT vars only
LSS> ... if no actual data then this will fail but previous settings will be intact
LSS> job SETDATES;PIPADMINDEFAULT
LSS> set control BEGINDT 2013/02
LSS> set control ENDDT 2013/02
LSS>
LSS> set earliest 2013/02
LSS> set latest 2013/02
LSS>
LSS> output LASTDATE;ZOSSPBI over
LSS> exh mon per latest '%Y/%2MM/%D'
2013/02/28
LSS> output off
LSS>
LSS> output REVIEW_LOAD;ZOSSPBI over
LSS> echo LOADFINISHED
LOADFINISHED
LSS> show latest
  Latest 28 Feb 2013 (MO)

LSS> output off
LSS>
LSS> checkpoint update
LSS>
LSS> trace both off

[Former Member]

Hi,

REPLACE CALCULATIONS is a option used in the create dimension command to replaces the relationships with new ones.

What you need to do is change the consolidation method for the variable (the default is sum). For example in cube builder there is a option for you to choose what is the consolidation method (sum, average, etc...).

Miguel

[Former Member]

Thanks for that Miguel. You are spot on.

[Former Member]

Thanks Zimkhita,

I will examine that code tonight. This is not my post so I can't award points but as usual you've been very helpful

Cheers

Cliff

[Former Member]

The key section of the code is what I have posted below. Basically it uses the same method I suggest above.

• It creates a var (I used Count_Members and it uses COUNTVAR)
• It then set the value to 1 for all populated dimension members. I did t
• I consolidates the new var whereas they left it set to caclulate which achieved the same thing.
• They calculate the main var as equal itself divided by the new var. I used separate vars for source and target.

The code now follows.

job 'calcavg_pmdb.pro' BI_TEST_ACT
.... procedure to average up the dimension

.... first consolidate the var being calc'd
set var 'BI_TEST_ACT' consol

rollup 'BI_TEST_ACT'
       remove everybody
        add everybody
end

consol 'BI_TEST_ACT'
set var 'BI_TEST_ACT' noconsol

.... create var to count dim members for averaging up dimension
.... needs to be re-calc'd for each kpi var so only populated
.... dimension members are counted

create COUNTVAR temp like 'BI_TEST_ACT'
set var countvar consol
calc countvar if 'BI_TEST_ACT' is missing = 0 otherwise = 1
set var countvar noconsol

.... now calc average of original var
calc 'BI_TEST_ACT' = 'BI_TEST_ACT' / COUNTVAR full…all finished with count variable
remove var COUNTVAR sure

[Former Member]

Hello,

Anyone? Any ideas?

Thanks in advance,

Andreea D.

[Former Member]

I assume you want the averag up the dimension. We are not talking about averaging over time (months to quarters to years)

I am not familiar with replace calculations. However if you have a measure (e.g. average_error_rate.) Create a second measure with the same dimsnionality (e.g. count_members).

Create a third measure with the same dimensionality (e.g. count_errors).

Populate Count_members with a value of 1 for all input members of the dimension(s)

Populate Count_Errors with the value of average_error_rate for all inputmembers of the dimenson(s)

Consolidate Count_Members

Consolidate Count_errors

For all output and result members of the dimension(s) calculate average_error_rate as Count_errors / Count+members remembering to use the FULL keyword.