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

    <PhysicalSchema>
        <!-- ## _SHARED_PHYSICAL_SCHEMA_TAG_ ## -->
        
        <!-- ## _CATEGORY_QUERIES_TAG_ ## -->
        
        <!-- ## _YESNOTABLE_QUERIES_TAG_ ## -->
        
        <Query alias="CP7GeneralCube">
            <ExpressionView>
                <SQL dialect="generic">
                    <![CDATA[
                        select
                            cp7.ID, cp7.DESIGNATION, cp7.ENTITY_YEAR, CP1.COUNTRY_INCOME_STATUS_ID,
                            COALESCE(CP1.SECONDARY_COUNTRY_INCOME_STATUS_ID, -1) AS SECONDARY_COUNTRY_INCOME_STATUS_ID,
                            cp7.OTHERUNCTJOINTLY_MANAGE_RESOURCES,
                            CASE WHEN cp7.GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK = TRUE THEN 1 ELSE 0 END as GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK_COUNT, 
                            CASE cp7.GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK,
                            CASE WHEN cp7.HAS_REVIEW_OF_FINANCING_LANDSCAPE = TRUE THEN 1 ELSE 0 END as HAS_REVIEW_OF_FINANCING_LANDSCAPE_COUNT,
                            CASE cp7.HAS_REVIEW_OF_FINANCING_LANDSCAPE WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as HAS_REVIEW_OF_FINANCING_LANDSCAPE,
                            CASE WHEN cp7.GOV_HASSDGFINANCING_GAP_STRATEGY = TRUE THEN 1 ELSE 0 END as GOV_HASSDGFINANCING_GAP_STRATEGY_COUNT,
                            CASE cp7.GOV_HASSDGFINANCING_GAP_STRATEGY WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as GOV_HASSDGFINANCING_GAP_STRATEGY,
                            CASE WHEN cp7.UNCT_HASSDGFINANCING_GAP_STRATEGY = TRUE THEN 1 ELSE 0 END as UNCT_HASSDGFINANCING_GAP_STRATEGY_COUNT,
                            CASE cp7.UNCT_HASSDGFINANCING_GAP_STRATEGY WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as UNCT_HASSDGFINANCING_GAP_STRATEGY,
                            CASE WHEN cp7.UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION = TRUE THEN 1 ELSE 0 END as UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION_COUNT,
                            CASE cp7.UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION,
                            CASE cp1.OFFICIAL_REQUEST_DAO_COUNTRY WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as OFFICIAL_REQUEST_DAO_COUNTRY
                        from CP7JOINT_FUNDING_PROFILE cp7, CP1GENERAL_PROFILE cp1, BASE_COUNTRY bc, v_cp_validated_designations v
                        WHERE
                            cp7.DESIGNATION = cp1.DESIGNATION AND cp7.ENTITY_YEAR = cp1.ENTITY_YEAR AND
                            cp7.DESIGNATION != 'AQ' AND cp7.DESIGNATION != 'DOCO' AND cp7.DESIGNATION = bc.iso2 and
                            cp7.designation = v.designation and v.entity_year = cp7.ENTITY_YEAR
                    ]]>
                </SQL>
            </ExpressionView>
        </Query>
    </PhysicalSchema>

    <!--*************************************************************************
    ***** Shared Dimensions
    *************************************************************************-->
    <!-- ## _SHARED_DIMENSIONS_TAG_ ## -->
    
    <!--*************************************************************************
    ***** CP7 General Cube
    *************************************************************************-->
    <Cube name="Joint Funding Profile" caption="Joint Funding Profile" 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="CP7GeneralCube" visible="true">
                <Attributes>
                    <Attribute name="Year" keyColumn="ENTITY_YEAR" datatype="Integer" />
                </Attributes>
            </Dimension>
            <Dimension source="CountryIncomeStatus" />
            <Dimension source="SecondaryCountryIncomeStatus" />

            <Dimension table="YesNoTable" name="Partner government have an integrated national financing framework" caption="Does your partner government have an integrated national financing framework?" />
            <Dimension table="YesNoTable" name="Review of the full financing landscape" caption="Does it include a review of the full financing landscape?" />
            <Dimension table="YesNoTable" name="Partner government developed a strategy to close financing gap" caption="Has your partner government developed a strategy to close the SDG financing gap?" />
            <Dimension table="YesNoTable" name="UNCT strategy to close financing gap" caption="Has the UNCT developed a strategy to contribute to closing the SDG financing gap?" />
            <Dimension table="YesNoTable" name="Does the UNCT jointly manage resource mobilization" caption="Does the UNCT jointly manage resource mobilization?" />

            <Dimension name="Other UNCT resource mobilization" caption="Other UNCT resource mobilization" table="CP7GeneralCube" key="Dimension Id" visible="true">
                <Attributes>
                    <Attribute name="Dimension Id" keyColumn="ID"
                               hasHierarchy="false"
                               levelType="Regular" datatype="Integer"/>
                    <Attribute name="Other UNCT resource mobilization" caption="Other UNCT resource mobilization"
                               keyColumn="OTHERUNCTJOINTLY_MANAGE_RESOURCES" hierarchyAllMemberName="All Other UNCT resource mobilization"
                               hierarchyCaption="All Other UNCT resource mobilization" levelType="Regular"
                               datatype="String" />
                </Attributes>
           </Dimension>
        </Dimensions>

        <MeasureGroups>
            <MeasureGroup name="CP7 General Cube Measures" table="CP7GeneralCube">
                <Measures>
                    <Measure name="Count" caption="Number of Country Records Submitted" column="ID" datatype="Integer"
                             aggregator="distinct-count" visible="true" formatString="#,###"/>

                    <Measure name="partnerGovernmentHaveIntegratedNationalFinancingFramework" caption="Count of 'Does your partner government have an integrated national financing framework?'" column="GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="includeReviewOfFinancingLandscape?" caption="Count of 'Does it include a review of the full financing landscape?'" column="HAS_REVIEW_OF_FINANCING_LANDSCAPE_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="strategyToCloseSdgFinancingGap" caption="Count of 'Has your partner government developed a strategy to close the SDG financing gap?'" column="GOV_HASSDGFINANCING_GAP_STRATEGY_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="hasUnctStrategyToCloseFinancingGap" caption="Count of 'Has the UNCT developed a strategy to contribute to closing the SDG financing gap?'" column="UNCT_HASSDGFINANCING_GAP_STRATEGY_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="doesUnctManageResourceMobilization" caption="Count of 'Does the UNCT jointly manage resource mobilization?'" column="UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>
                </Measures>
                <DimensionLinks>
                    <!-- ## _SHARED_DIMENSIONS_LINKS_TAG_ ## -->
                    <ForeignKeyLink dimension="Partner government have an integrated national financing framework"  foreignKeyColumn="GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK"/>
                    <ForeignKeyLink dimension="Review of the full financing landscape"  foreignKeyColumn="HAS_REVIEW_OF_FINANCING_LANDSCAPE"/>
                    <ForeignKeyLink dimension="Partner government developed a strategy to close financing gap"  foreignKeyColumn="GOV_HASSDGFINANCING_GAP_STRATEGY"/>
                    <ForeignKeyLink dimension="UNCT strategy to close financing gap"  foreignKeyColumn="UNCT_HASSDGFINANCING_GAP_STRATEGY"/>
                    <ForeignKeyLink dimension="Does the UNCT jointly manage resource mobilization"  foreignKeyColumn="UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION"/>
                    <ForeignKeyLink dimension="Other UNCT resource mobilization"  foreignKeyColumn="OTHERUNCTJOINTLY_MANAGE_RESOURCES"/>
                </DimensionLinks>
            </MeasureGroup>
        </MeasureGroups>
    </Cube>
</Schema><commit_msg>CCR-2051: CP7 changes<commit_after><Schema name="Joint Funding and Financing Profile" metamodelVersion="4.0"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="mondrian.xsd">
    <Annotations>
        <Annotation name="desc">Cube Schema for CCRS - Joint Funding and Financing Profile</Annotation>
    </Annotations>

    <PhysicalSchema>
        <!-- ## _SHARED_PHYSICAL_SCHEMA_TAG_ ## -->
        
        <!-- ## _CATEGORY_QUERIES_TAG_ ## -->
        
        <!-- ## _YESNOTABLE_QUERIES_TAG_ ## -->
        
        <Query alias="CP7GeneralCube">
            <ExpressionView>
                <SQL dialect="generic">
                    <![CDATA[
                        select
                            cp7.ID, cp7.DESIGNATION, cp7.ENTITY_YEAR, CP1.COUNTRY_INCOME_STATUS_ID,
                            COALESCE(CP1.SECONDARY_COUNTRY_INCOME_STATUS_ID, -1) AS SECONDARY_COUNTRY_INCOME_STATUS_ID,
                            cp7.OTHERUNCTJOINTLY_MANAGE_RESOURCES,
                            CASE WHEN cp7.GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK = TRUE THEN 1 ELSE 0 END as GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK_COUNT, 
                            CASE cp7.GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK,
                            CASE WHEN cp7.HAS_REVIEW_OF_FINANCING_LANDSCAPE = TRUE THEN 1 ELSE 0 END as HAS_REVIEW_OF_FINANCING_LANDSCAPE_COUNT,
                            CASE cp7.HAS_REVIEW_OF_FINANCING_LANDSCAPE WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as HAS_REVIEW_OF_FINANCING_LANDSCAPE,
                            CASE WHEN cp7.GOV_HASSDGFINANCING_GAP_STRATEGY = TRUE THEN 1 ELSE 0 END as GOV_HASSDGFINANCING_GAP_STRATEGY_COUNT,
                            CASE cp7.GOV_HASSDGFINANCING_GAP_STRATEGY WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as GOV_HASSDGFINANCING_GAP_STRATEGY,
                            CASE WHEN cp7.UNCT_HASSDGFINANCING_GAP_STRATEGY = TRUE THEN 1 ELSE 0 END as UNCT_HASSDGFINANCING_GAP_STRATEGY_COUNT,
                            CASE cp7.UNCT_HASSDGFINANCING_GAP_STRATEGY WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as UNCT_HASSDGFINANCING_GAP_STRATEGY,
                            CASE WHEN cp7.UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION = TRUE THEN 1 ELSE 0 END as UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION_COUNT,
                            CASE cp7.UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION,
                            CASE cp1.OFFICIAL_REQUEST_DAO_COUNTRY WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as OFFICIAL_REQUEST_DAO_COUNTRY,
                            CASE WHEN cp7.has_global_pooled_fund_mechanism = TRUE THEN 1 ELSE 0 END as has_global_pooled_fund_mechanism_count,
                            CASE cp7.has_global_pooled_fund_mechanism WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE -1 END as has_global_pooled_fund_mechanism_dim,
                            COALESCE(cp7.global_pooled_fund_mechanism_id, -1) AS global_pooled_fund_mechanism_id
                        from CP7JOINT_FUNDING_PROFILE cp7, CP1GENERAL_PROFILE cp1, BASE_COUNTRY bc, v_cp_validated_designations v
                        WHERE
                            cp7.DESIGNATION = cp1.DESIGNATION AND cp7.ENTITY_YEAR = cp1.ENTITY_YEAR AND
                            cp7.DESIGNATION != 'AQ' AND cp7.DESIGNATION != 'DOCO' AND cp7.DESIGNATION = bc.iso2 and
                            cp7.designation = v.designation and v.entity_year = cp7.ENTITY_YEAR
                    ]]>
                </SQL>
            </ExpressionView>
        </Query>
    </PhysicalSchema>

    <!--*************************************************************************
    ***** Shared Dimensions
    *************************************************************************-->
    <!-- ## _SHARED_DIMENSIONS_TAG_ ## -->
    
    <!--*************************************************************************
    ***** CP7 General Cube
    *************************************************************************-->
    <Cube name="Joint Funding Profile" caption="Joint Funding Profile" 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="CP7GeneralCube" visible="true">
                <Attributes>
                    <Attribute name="Year" keyColumn="ENTITY_YEAR" datatype="Integer" />
                </Attributes>
            </Dimension>
            <Dimension source="CountryIncomeStatus" />
            <Dimension source="SecondaryCountryIncomeStatus" />

            <Dimension table="YesNoTable" name="Partner government have an integrated national financing framework" caption="Does your partner government have an integrated national financing framework?" />
            <Dimension table="YesNoTable" name="Review of the full financing landscape" caption="Does it include a review of the full financing landscape?" />
            <Dimension table="YesNoTable" name="Partner government developed a strategy to close financing gap" caption="Has your partner government developed a strategy to close the SDG financing gap?" />
            <Dimension table="YesNoTable" name="UNCT strategy to close financing gap" caption="Has the UNCT developed a strategy to contribute to closing the SDG financing gap?" />
            <Dimension table="YesNoTable" name="Does the UNCT jointly manage resource mobilization" caption="Does the UNCT jointly manage resource mobilization?" />

            <Dimension name="Other UNCT resource mobilization" caption="Other UNCT resource mobilization" table="CP7GeneralCube" key="Dimension Id" visible="true">
                <Attributes>
                    <Attribute name="Dimension Id" keyColumn="ID"
                               hasHierarchy="false"
                               levelType="Regular" datatype="Integer"/>
                    <Attribute name="Other UNCT resource mobilization" caption="Other UNCT resource mobilization"
                               keyColumn="OTHERUNCTJOINTLY_MANAGE_RESOURCES" hierarchyAllMemberName="All Other UNCT resource mobilization"
                               hierarchyCaption="All Other UNCT resource mobilization" levelType="Regular"
                               datatype="String" />
                </Attributes>
            </Dimension>

            <Dimension table="YesNoTable" name="hasGlobalPooledFundMechanism" caption="Has the UNCT accessed any global pooled-fund mechanism in the last year?" />

            <Dimension source="CATEGORY" table="GlobalPooledFundMechanism" caption="Which pooled-fund mechanism?" />
        </Dimensions>

        <MeasureGroups>
            <MeasureGroup name="CP7 General Cube Measures" table="CP7GeneralCube">
                <Measures>
                    <Measure name="Count" caption="Number of Country Records Submitted" column="ID" datatype="Integer"
                             aggregator="distinct-count" visible="true" formatString="#,###"/>

                    <Measure name="partnerGovernmentHaveIntegratedNationalFinancingFramework" caption="Count of 'Does your partner government have an integrated national financing framework?'" column="GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="includeReviewOfFinancingLandscape?" caption="Count of 'Does it include a review of the full financing landscape?'" column="HAS_REVIEW_OF_FINANCING_LANDSCAPE_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="strategyToCloseSdgFinancingGap" caption="Count of 'Has your partner government developed a strategy to close the SDG financing gap?'" column="GOV_HASSDGFINANCING_GAP_STRATEGY_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="hasUnctStrategyToCloseFinancingGap" caption="Count of 'Has the UNCT developed a strategy to contribute to closing the SDG financing gap?'" column="UNCT_HASSDGFINANCING_GAP_STRATEGY_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="doesUnctManageResourceMobilization" caption="Count of 'Does the UNCT jointly manage resource mobilization?'" column="UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION_COUNT"
                             datatype="Integer" aggregator="sum" visible="true" formatString="#,###"/>

                    <Measure name="hasGlobalPooledFundMechanism" caption="Count 'Has the UNCT accessed any global pooled-fund mechanism in the last year?'" column="has_global_pooled_fund_mechanism_count" datatype="Integer"
                             aggregator="sum" visible="true" formatString="#,###"/>
                </Measures>
                <DimensionLinks>
                    <!-- ## _SHARED_DIMENSIONS_LINKS_TAG_ ## -->
                    <ForeignKeyLink dimension="Partner government have an integrated national financing framework"  foreignKeyColumn="GOV_HAS_INTEGRATED_NATIONAL_FINANCING_FRAMEWORK"/>
                    <ForeignKeyLink dimension="Review of the full financing landscape"  foreignKeyColumn="HAS_REVIEW_OF_FINANCING_LANDSCAPE"/>
                    <ForeignKeyLink dimension="Partner government developed a strategy to close financing gap"  foreignKeyColumn="GOV_HASSDGFINANCING_GAP_STRATEGY"/>
                    <ForeignKeyLink dimension="UNCT strategy to close financing gap"  foreignKeyColumn="UNCT_HASSDGFINANCING_GAP_STRATEGY"/>
                    <ForeignKeyLink dimension="Does the UNCT jointly manage resource mobilization"  foreignKeyColumn="UNCT_JOINTLY_MANAGE_RESOURCE_MOBILIZATION"/>
                    <ForeignKeyLink dimension="Other UNCT resource mobilization"  foreignKeyColumn="OTHERUNCTJOINTLY_MANAGE_RESOURCES"/>
                    <ForeignKeyLink dimension="hasGlobalPooledFundMechanism" foreignKeyColumn="has_global_pooled_fund_mechanism_dim"/>
                    <ForeignKeyLink dimension="GlobalPooledFundMechanism" foreignKeyColumn="global_pooled_fund_mechanism_id" />
                </DimensionLinks>
            </MeasureGroup>
        </MeasureGroups>
    </Cube>
</Schema>
{'fraction_non_alphanumeric': 0.10664335664335664, 'fraction_numerical': 0.007992007992007992, 'mean_word_length': 2.686429755109556, 'pattern_counts': {'":': 0, '<': 142, '<?xml version=': 0, '>': 142, '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': '4665248', 'n_tokens_mistral': 5726, 'n_tokens_neox': 5208, 'n_words': 1252}