<commit_before><Schema name="Coordination Capacity" metamodelVersion="4.0"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="mondrian.xsd">
    <Annotations>
        <Annotation name="desc">Cube Schema for CCRS - Coordination Capacity</Annotation>
    </Annotations>

    <PhysicalSchema>
        <!-- ## _SHARED_PHYSICAL_SCHEMA_TAG_ ## -->
        
        <!-- ## _CATEGORY_QUERIES_TAG_ ## -->
        
        <Query alias="CP2RcoStaffMemberDetail">
            <ExpressionView>
                <SQL dialect="generic">
                    <![CDATA[
                        select
                            D.ID, D.ANNUAL_SALARY_AMOUNT, D.OFFICE_TYPE_ID, D.CONTRACT_MODALITY_ID, D.STAFF_MEMBER_TITLE_ID, D.LEVEL_ID, D.FUNDING_ORGANIZATION_ID, D.FINANCING_SOURCE_ID,
                            D.WHICH_AGENCY_ID, D.HEADRCO,  
                            CP2.DESIGNATION, CP2.ENTITY_YEAR, CP1.COUNTRY_INCOME_STATUS_ID, CP1.SECONDARY_COUNTRY_INCOME_STATUS_ID,
                            date(d.start_date) as start_date_date,
                            date(d.end_date) as end_date_date,
                            CASE WHEN D.HEADRCO = TRUE THEN 1 ELSE 0 END as HEAD_OF_RCO,
                            CASE WHEN D.COMMUNICATION_SPECIALIST = TRUE THEN 1 ELSE 0 END as COMMUNICATION_SPECIALIST,
                            CASE WHEN cp1.OFFICIAL_REQUEST_DAO_COUNTRY = TRUE THEN 1 ELSE 0 END as OFFICIAL_REQUEST_DAO_COUNTRY
                        FROM CP2RCO_STAFF_MEMBER_DETAIL D,  CP2COORDINATION_CAPACITY CP2, CP1GENERAL_PROFILE CP1, BASE_COUNTRY bc, V_CP_VALIDATED_DESIGNATIONS v
                        WHERE
                            CP2.DESIGNATION = CP1.DESIGNATION AND CP2.ENTITY_YEAR = CP1.ENTITY_YEAR AND
                            CP2.ID = D.PARENT_ID AND
                            CP2.DESIGNATION != 'AQ' AND CP2.DESIGNATION != 'DOCO' AND CP2.DESIGNATION = bc.ISO2 AND
                            CP2.DESIGNATION = v.DESIGNATION AND v.ENTITY_YEAR = CP2.ENTITY_YEAR
                    ]]>
                </SQL>
            </ExpressionView>
        </Query>	

	<InlineTable alias="COMMUNICATION_SPECIALIST">
			<ColumnDefs>
				<ColumnDef name="id" type="Numeric" />
				<ColumnDef name="answer" type="String" />
			</ColumnDefs>
			<Key>
				<Column name="id" />
			</Key>
			<Rows>
				<Row>
					<Value column="id">0</Value>
					<Value column="answer">No</Value>
				</Row>
				<Row>
					<Value column="id">1</Value>
					<Value column="answer">Yes</Value>
				</Row>
			</Rows>
		</InlineTable>

        <Query alias="CP2GeneralCube">
            <ExpressionView>
                <SQL dialect="generic">
                    <![CDATA[
                        select
                            cp2.ID, cp2.DESIGNATION, cp2.ENTITY_YEAR, CP1.COUNTRY_INCOME_STATUS_ID, CP1.SECONDARY_COUNTRY_INCOME_STATUS_ID,
                            CASE WHEN cp2.SUBNATIONAL_OFFICES = TRUE THEN 1 ELSE 0 END as SUBNATIONAL_OFFICES,
                            CASE WHEN cp2.SUBNATIONAL_OFFICES = TRUE THEN cp2.NUMBER_OF_OFFICES ELSE null END as NUMBER_OF_OFFICES,
                            (select count(*) from CP2RCO_STAFF_MEMBER_DETAIL where PARENT_ID = cp2.id) STAFF_MEMEBER_COUNT,
                            CASE WHEN cp1.OFFICIAL_REQUEST_DAO_COUNTRY = TRUE THEN 1 ELSE 0 END as OFFICIAL_REQUEST_DAO_COUNTRY
                        from CP2COORDINATION_CAPACITY cp2, CP1GENERAL_PROFILE cp1, BASE_COUNTRY bc, V_CP_VALIDATED_DESIGNATIONS v
                        WHERE
                            cp2.DESIGNATION = cp1.DESIGNATION AND cp2.ENTITY_YEAR = cp1.ENTITY_YEAR AND
                            cp2.DESIGNATION != 'AQ' AND cp2.DESIGNATION != 'DOCO' AND cp2.DESIGNATION = bc.ISO2 AND
                            cp2.DESIGNATION = v.DESIGNATION AND v.ENTITY_YEAR = cp2.ENTITY_YEAR
                    ]]>
                </SQL>
            </ExpressionView>
        </Query>
    </PhysicalSchema>
    
    <!--*************************************************************************
    ***** Shared Dimensions
    *************************************************************************-->
    <!-- ## _SHARED_DIMENSIONS_TAG_ ## -->

    <!--*************************************************************************
    ***** CP2 RCO Staff Member Details
    *************************************************************************-->
    <Cube name="RCO Staff Member" caption="RCO Staff Member" visible="true" cache="true" enabled="true" defaultMeasure="Count">
        <Dimensions>
            <Dimension source="Region" />
            <Dimension source="Country" />
            <Dimension source="Delivering as One" />
            <Dimension name="Year" caption="Year" table="CP2RcoStaffMemberDetail" visible="true">
                <Attributes>
                    <Attribute name="Year" keyColumn="ENTITY_YEAR" datatype="Integer" />
                </Attributes>
            </Dimension>
            <Dimension source="CountryIncomeStatus" />
            <Dimension source="SecondaryCountryIncomeStatus" />

            <Dimension source="CATEGORY" table="StaffMemberTitle" caption="Staff Member Title" />
            <Dimension table="YesNoTable" name="Head of RCO or Main RCO Contact" caption="Head of RCO or Main RCO Contact." />
            <Dimension source="CATEGORY" table="OfficeType" caption="Office Type" />
            <Dimension source="CATEGORY" table="ContractModality" caption="Contract Modality" />
            <Dimension source="CATEGORY" table="FundingOrganization" caption="Funding Organization" />
            <Dimension source="CATEGORY" table="Level" caption="Post Level" />
            <Dimension source="CATEGORY" table="FinancingSource" caption="Post Financing Source" />
            <Dimension source="CATEGORY" table="UNAgency" caption="UN Agency" />            

            <Dimension name="Start Date" caption="Contract Start Date" table="CP2RcoStaffMemberDetail" key="Dimension Id" type="TIME">
                <Attributes>
                    <Attribute name="Dimension Id" keyColumn="ID"
                               hasHierarchy="false"
                               levelType="Regular" datatype="Integer"/>

                    <Attribute name="Start Date" caption="Contract Start Date" keyColumn="START_DATE_DATE"
                               hierarchyHasAll="true"
                               levelType="TimeYears" datatype="Date"/>
                </Attributes>
            </Dimension>

            <Dimension name="End Date" caption="Contract End Date" table="CP2RcoStaffMemberDetail" key="Dimension Id" type="TIME">
                <Attributes>
                    <Attribute name="Dimension Id" keyColumn="ID"
                               hasHierarchy="false"
                               levelType="Regular" datatype="Integer"/>

                    <Attribute name="End Date" caption="Contract End Date" keyColumn="END_DATE_DATE"
                               hierarchyHasAll="true"
                               levelType="TimeYears" datatype="Date"/>
                </Attributes>
            </Dimension>

            <Dimension name="Are you the main focal point for communications" caption="Are you the main focal point for communications?" table='COMMUNICATION_SPECIALIST' key="Dimension Id">
                <Attributes>
                    <Attribute name="Dimension Id" keyColumn="id"
                               hasHierarchy="false"
                               levelType="Regular" datatype="Integer"/>

                    <Attribute name="Are you the main focal point for communications" caption="Are you the main focal point for communications?" keyColumn="answer"
                               approxRowCount="2" hierarchyHasAll="true"
                               levelType="Regular" datatype="Boolean"/>
                </Attributes>
            </Dimension>
        </Dimensions>

        <MeasureGroups>
            <MeasureGroup name="CP2 RCOStaffMemberDetails Measures" table="CP2RcoStaffMemberDetail">
                <Measures>
                    <Measure name="Count" caption="Number of RCO Staff Members" column="ID" datatype="Integer"
                             aggregator="distinct-count" visible="true" formatString="#,###"/>

                    <Measure name="TotalAnnualNetSalaryAmount" caption="Total Annual Net Salary Amount"
                             column="ANNUAL_SALARY_AMOUNT" datatype="Numeric" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="mainFocalPointForCommunications" caption="Are you the main focal point for communications?'" column="COMMUNICATION_SPECIALIST"<commit_msg>CCR-1210 CCR-1167 use simplified schema definition<commit_after><Schema name="Coordination Capacity" metamodelVersion="4.0"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="mondrian.xsd">
    <Annotations>
        <Annotation name="desc">Cube Schema for CCRS - Coordination Capacity</Annotation>
    </Annotations>

    <PhysicalSchema>
        <!-- ## _SHARED_PHYSICAL_SCHEMA_TAG_ ## -->
        
        <!-- ## _CATEGORY_QUERIES_TAG_ ## -->
        
        <Query alias="CP2RcoStaffMemberDetail">
            <ExpressionView>
                <SQL dialect="generic">
                    <![CDATA[
                        select
                            D.ID, D.ANNUAL_SALARY_AMOUNT, D.OFFICE_TYPE_ID, D.CONTRACT_MODALITY_ID, D.STAFF_MEMBER_TITLE_ID, D.LEVEL_ID, D.FUNDING_ORGANIZATION_ID, D.FINANCING_SOURCE_ID,
                            D.WHICH_AGENCY_ID, D.HEADRCO,  
                            CP2.DESIGNATION, CP2.ENTITY_YEAR, CP1.COUNTRY_INCOME_STATUS_ID, CP1.SECONDARY_COUNTRY_INCOME_STATUS_ID,
                            date(d.start_date) as start_date_date,
                            date(d.end_date) as end_date_date,
                            CASE WHEN D.HEADRCO = TRUE THEN 1 ELSE 0 END as HEAD_OF_RCO,
                            CASE WHEN D.COMMUNICATION_SPECIALIST = TRUE THEN 1 ELSE 0 END as COMMUNICATION_SPECIALIST,
                            CASE WHEN cp1.OFFICIAL_REQUEST_DAO_COUNTRY = TRUE THEN 1 ELSE 0 END as OFFICIAL_REQUEST_DAO_COUNTRY
                        FROM CP2RCO_STAFF_MEMBER_DETAIL D,  CP2COORDINATION_CAPACITY CP2, CP1GENERAL_PROFILE CP1, BASE_COUNTRY bc, V_CP_VALIDATED_DESIGNATIONS v
                        WHERE
                            CP2.DESIGNATION = CP1.DESIGNATION AND CP2.ENTITY_YEAR = CP1.ENTITY_YEAR AND
                            CP2.ID = D.PARENT_ID AND
                            CP2.DESIGNATION != 'AQ' AND CP2.DESIGNATION != 'DOCO' AND CP2.DESIGNATION = bc.ISO2 AND
                            CP2.DESIGNATION = v.DESIGNATION AND v.ENTITY_YEAR = CP2.ENTITY_YEAR
                    ]]>
                </SQL>
            </ExpressionView>
        </Query>

        <Query alias="CP2GeneralCube">
            <ExpressionView>
                <SQL dialect="generic">
                    <![CDATA[
                        select
                            cp2.ID, cp2.DESIGNATION, cp2.ENTITY_YEAR, CP1.COUNTRY_INCOME_STATUS_ID, CP1.SECONDARY_COUNTRY_INCOME_STATUS_ID,
                            CASE WHEN cp2.SUBNATIONAL_OFFICES = TRUE THEN 1 ELSE 0 END as SUBNATIONAL_OFFICES,
                            CASE WHEN cp2.SUBNATIONAL_OFFICES = TRUE THEN cp2.NUMBER_OF_OFFICES ELSE null END as NUMBER_OF_OFFICES,
                            (select count(*) from CP2RCO_STAFF_MEMBER_DETAIL where PARENT_ID = cp2.id) STAFF_MEMEBER_COUNT,
                            CASE WHEN cp1.OFFICIAL_REQUEST_DAO_COUNTRY = TRUE THEN 1 ELSE 0 END as OFFICIAL_REQUEST_DAO_COUNTRY
                        from CP2COORDINATION_CAPACITY cp2, CP1GENERAL_PROFILE cp1, BASE_COUNTRY bc, V_CP_VALIDATED_DESIGNATIONS v
                        WHERE
                            cp2.DESIGNATION = cp1.DESIGNATION AND cp2.ENTITY_YEAR = cp1.ENTITY_YEAR AND
                            cp2.DESIGNATION != 'AQ' AND cp2.DESIGNATION != 'DOCO' AND cp2.DESIGNATION = bc.ISO2 AND
                            cp2.DESIGNATION = v.DESIGNATION AND v.ENTITY_YEAR = cp2.ENTITY_YEAR
                    ]]>
                </SQL>
            </ExpressionView>
        </Query>
    </PhysicalSchema>
    
    <!--*************************************************************************
    ***** Shared Dimensions
    *************************************************************************-->
    <!-- ## _SHARED_DIMENSIONS_TAG_ ## -->

    <!--*************************************************************************
    ***** CP2 RCO Staff Member Details
    *************************************************************************-->
    <Cube name="RCO Staff Member" caption="RCO Staff Member" visible="true" cache="true" enabled="true" defaultMeasure="Count">
        <Dimensions>
            <Dimension source="Region" />
            <Dimension source="Country" />
            <Dimension source="Delivering as One" />
            <Dimension name="Year" caption="Year" table="CP2RcoStaffMemberDetail" visible="true">
                <Attributes>
                    <Attribute name="Year" keyColumn="ENTITY_YEAR" datatype="Integer" />
                </Attributes>
            </Dimension>
            <Dimension source="CountryIncomeStatus" />
            <Dimension source="SecondaryCountryIncomeStatus" />

            <Dimension source="CATEGORY" table="StaffMemberTitle" caption="Staff Member Title" />
            <Dimension table="YesNoTable" name="Head of RCO or Main RCO Contact" caption="Head of RCO or Main RCO Contact." />
            <Dimension source="CATEGORY" table="OfficeType" caption="Office Type" />
            <Dimension source="CATEGORY" table="ContractModality" caption="Contract Modality" />
            <Dimension source="CATEGORY" table="FundingOrganization" caption="Funding Organization" />
            <Dimension source="CATEGORY" table="Level" caption="Post Level" />
            <Dimension source="CATEGORY" table="FinancingSource" caption="Post Financing Source" />
            <Dimension source="CATEGORY" table="UNAgency" caption="UN Agency" />            

            <Dimension name="Start Date" caption="Contract Start Date" table="CP2RcoStaffMemberDetail" key="Dimension Id" type="TIME">
                <Attributes>
                    <Attribute name="Dimension Id" keyColumn="ID"
                               hasHierarchy="false"
                               levelType="Regular" datatype="Integer"/>

                    <Attribute name="Start Date" caption="Contract Start Date" keyColumn="START_DATE_DATE"
                               hierarchyHasAll="true"
                               levelType="TimeYears" datatype="Date"/>
                </Attributes>
            </Dimension>

            <Dimension name="End Date" caption="Contract End Date" table="CP2RcoStaffMemberDetail" key="Dimension Id" type="TIME">
                <Attributes>
                    <Attribute name="Dimension Id" keyColumn="ID"
                               hasHierarchy="false"
                               levelType="Regular" datatype="Integer"/>

                    <Attribute name="End Date" caption="Contract End Date" keyColumn="END_DATE_DATE"
                               hierarchyHasAll="true"
                               levelType="TimeYears" datatype="Date"/>
                </Attributes>
            </Dimension>
            
            <Dimension table="YesNoTable" name="Are you the main focal point for communications" caption="Are you the main focal point for communications?" />
        </Dimensions>

        <MeasureGroups>
            <MeasureGroup name="CP2 RCOStaffMemberDetails Measures" table="CP2RcoStaffMemberDetail">
                <Measures>
                    <Measure name="Count" caption="Number of RCO Staff Members" column="ID" datatype="Integer"
                             aggregator="distinct-count" visible="true" formatString="#,###"/>

                    <Measure name="TotalAnnualNetSalaryAmount" caption="Total Annual Net Salary Amount"
                             column="ANNUAL_SALARY_AMOUNT" datatype="Numeric" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="mainFocalPointForCommunications" caption="Are you the main focal point for communications?'" column="COMMUNICATION_SPECIALIST"
{'fraction_non_alphanumeric': 0.1298970328398221, 'fraction_numerical': 0.009321878998354963, 'mean_word_length': 2.034572009613607, 'pattern_counts': {'":': 0, '<': 163, '<?xml version=': 0, '>': 161, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 2}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2270609', 'n_tokens_mistral': 4704, 'n_tokens_neox': 4356, 'n_words': 909}