rendered paste body<?xml version="1.0"?><Schema name="Payback"><!-- Shared dimensions --> <Dimension name="RadioElement"> <Hierarchy hasAll="true" primaryKey="id" primaryKeyTable="bus_sector"> <Table name="bus_sector"/> <Level name="Region" column="region" /> <Level name="Zone" column="zone" hideMemberIf="IfBlankName" /> <Level name="Territory" column="territory" hideMemberIf="IfBlankName" /> <Level name="Site" column="site_code" > <NameExpression> <SQL dialect="mysql"> CONCAT(`bus_sector`.`site_code`, ' ', LEFT(`bus_sector`.`site_name`,15)) </SQL> </NameExpression> </Level> <Level name="Sector" column="sector_number"> <Property name="Rural/Urbana" column="city_municipality" /> <Property name="Air_date" column="on_air" /> </Level> </Hierarchy> </Dimension> <Dimension name="CoverageType"> <Hierarchy hasAll="true" primaryKey="id"> <Table name="bus_coverageType"/> <Level name="Plotter" column="coverageName" hideMemberIf="IfBlankName" /> </Hierarchy> </Dimension> <Dimension name="Responsible"> <Hierarchy hasAll="true" primaryKey="id"> <Table name="bus_responsible"/> <Level name="Name" column="name" /> </Hierarchy> </Dimension> <Dimension name="Time" type="TimeDimension"> <Hierarchy hasAll="true" primaryKey="id"> <Table name="bus_date"/> <Level name="Year" column="x_year" type="Numeric" uniqueMembers="true" levelType="TimeYears"/> <Level name="Quarter" column="quarter" uniqueMembers="false" levelType="TimeQuarters"/> <Level name="Month" column="month_of_year" uniqueMembers="false" type="Numeric" levelType="TimeMonths"/> <Level name="Day" column="day_of_month" uniqueMembers="false" type="Numeric" levelType="TimeDays"/> </Hierarchy> </Dimension> <!-- pyb_mss_target --><Cube name="Target" defaultMeasure="target"> <Table name="pyb_mss_target"> </Table> <DimensionUsage name="Time" source="Time" foreignKey="date_key"/> <DimensionUsage name="RadioElement" source="RadioElement" level="Region" foreignKey="region"/> <DimensionUsage name="CoverageType" source="CoverageType" foreignKey="coverage_key"/> <Measure name="Target" column="target" aggregator="avg" formatString="Standard" visible="true"/></Cube> <!-- pyb_mss_users --><Cube name="Users" defaultMeasure="users"> <Table name="pyb_mss_users"> </Table> <DimensionUsage name="RadioElement" source="RadioElement" level="Region" foreignKey="region"/> <DimensionUsage name="Time" source="Time" foreignKey="date_key"/> <Measure name="Subscriptors" column="users" aggregator="min" formatString="Standard" visible="true"/> <CalculatedMember name="RUsers" dimension="Measures"> <Formula> IIF([Time].CurrentMember.CHILDREN.COUNT=0,[Measures].[Subscriptors],([Measures].[Subscriptors],ClosingPeriod(Month,[Time].CurrentMember) ) ) </Formula> </CalculatedMember></Cube><!-- pyb_mss_capex --><Cube name="Capex" defaultMeasure="capex"> <Table name="pyb_mss_capex"> </Table> <DimensionUsage name="Time" source="Time" foreignKey="date_key"/> <DimensionUsage name="RadioElement" source="RadioElement" foreignKey="cid_key"/> <DimensionUsage name="CoverageType" source="CoverageType" foreignKey="coverage_key"/> <Measure name="Capex" column="capex" aggregator="sum" formatString="US$#,##0.00" visible="false"/> <CalculatedMember name="AggCapex" dimension="Measures" visible="false"> <Formula>Sum(PeriodsToDate([Time].[All Times], [Time].CurrentMember), [Measures].[Capex])</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="US$#,##0.00" /> </CalculatedMember></Cube><!-- Bts --><Cube name="Bts" defaultMeasure="min_mocE"> <Table name="pyb_mss_cell"> </Table> <DimensionUsage name="Time" source="Time" foreignKey="date_key"/> <DimensionUsage name="RadioElement" source="RadioElement" foreignKey="cid_key"/> <DimensionUsage name="CoverageType" source="CoverageType" foreignKey="coverage_key"/> <!--<DimensionUsage name="Responsible" source="Responsible" foreignKey="respoym_key"/>--> <Dimension name="ResponsibleOpt" foreignKey="respopt_key" usagePrefix="opt"> <Hierarchy hasAll="true" primaryKey="id"> <Table name="bus_responsibleopt"/> <Level name="Name" column="name" /> </Hierarchy> </Dimension> <Measure name="Cost" column="costo" aggregator="sum" formatString="US$#,##0"/> <Measure name="Revenue" column="revenue" aggregator="sum" formatString="US$#,##0"/> <Measure name="Min_moc" column="min_mocE" aggregator="sum" formatString="Standard"/> <Measure name="Min_mocTigo" column="min_mocE_tigo" aggregator="sum" formatString="Standard"/> <Measure name="Min_mocComcel" column="min_mocE_comcel" aggregator="sum" formatString="Standard"/> <Measure name="Min_mocMovistar" column="min_mocE_movistar" aggregator="sum" formatString="Standard"/> <Measure name="Min_mocFijo" column="min_mocE_Fijo" aggregator="sum" formatString="Standard"/> <Measure name="Min_mocLdi" column="min_mocE_Ldi" aggregator="sum" formatString="Standard"/> <Measure name="Min_mtc" column="min_mtc" aggregator="sum" formatString="Standard"/> <Measure name="Min_mtcTigo" column="min_mtc_tigo" aggregator="sum" formatString="Standard"/> <Measure name="Min_mtcComcel" column="min_mtc_comcel" aggregator="sum" formatString="Standard"/> <Measure name="Min_mtcMovistar" column="min_mtc_movistar" aggregator="sum" formatString="Standard"/> <Measure name="Min_mtcFijoLdi" column="min_mtc_fijo_ldi" aggregator="sum" formatString="Standard"/> <Measure name="Qt_mocSMS" column="qt_mocSMS" aggregator="sum" formatString="Standard"/> <Measure name="Qt_mtcSMS" column="qt_mtcSMS" aggregator="sum" formatString="Standard"/> <CalculatedMember name="Effective_minutes" dimension="Measures"> <Formula>[Measures].[Min_moc] + [Measures].[Min_mtc] - [Measures].[Min_mtcTigo]</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="Standard" /> </CalculatedMember> <CalculatedMember name="Gross_Margin" dimension="Measures"> <Formula>[Measures].[Revenue] - [Measures].[Cost]</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="US$#,##0"/> </CalculatedMember> <CalculatedMember name="Ebitda" dimension="Measures"> <Formula>[Measures].[Gross_Margin] - ([Measures].[Revenue]*.242) - ([Measures].[Revenue]*.083)</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="US$#,##0"/> </CalculatedMember> <CalculatedMember name="Cash_Flow" dimension="Measures"> <Formula>[Measures].[Ebitda] - ([Measures].[Ebitda]*0.022)</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="US$#,##0"/> </CalculatedMember> <!-- This is for users forecasting --> <CalculatedMember name="CashPerMin" dimension="Measures"> <Formula>[Measures].[Cash_Flow]/[Measures].[Effective_minutes]</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="##.######"/> </CalculatedMember></Cube><VirtualCube name="Payback" defaultMeasure="Payback"> <VirtualCubeDimension cubeName="Bts" name="RadioElement"/> <VirtualCubeDimension cubeName="Bts" name="Time"/> <VirtualCubeDimension cubeName="Bts" name="CoverageType"/> <VirtualCubeDimension cubeName="Target" name="CoverageType"/> <VirtualCubeDimension cubeName="Target" name="RadioElement"/> <VirtualCubeDimension cubeName="Target" name="Time"/> <VirtualCubeDimension cubeName="Capex" name="RadioElement"/> <VirtualCubeDimension cubeName="Capex" name="Time"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Cost]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Revenue]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Gross_Margin]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Ebitda]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Effective_minutes]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Cash_Flow]"/> <VirtualCubeMeasure cubeName="Capex" name="[Measures].[AggCapex]" visible="false"/> <VirtualCubeMeasure cubeName="Target" name="[Measures].[Target]" visible="true"/> <CalculatedMember name="ValidCapex" dimension="Measures"> <Formula>[Measures].[AggCapex]</Formula> </CalculatedMember> <CalculatedMember name="ValidTarget" dimension="Measures"> <Formula>Iif([Measures].[Payback] IS EMPTY,NULL,([Measures].[Target],Ancestor(RadioElement.CurrentMember,Region) ) )</Formula> </CalculatedMember> <CalculatedMember name="Payback" dimension="Measures"> <Formula>IIf([Measures].[Cash_Flow] IS EMPTY,NULL,[Measures].[ValidCapex] / [Measures].[Cash_Flow])</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="###.#"/> </CalculatedMember></VirtualCube> <VirtualCube name="Forecast" defaultMeasure="Payback"> <VirtualCubeDimension cubeName="Bts" name="RadioElement"/> <VirtualCubeDimension cubeName="Bts" name="Time"/> <VirtualCubeDimension cubeName="Bts" name="CoverageType"/> <VirtualCubeDimension cubeName="Target" name="CoverageType"/> <VirtualCubeDimension cubeName="Target" name="RadioElement"/> <VirtualCubeDimension cubeName="Target" name="Time"/> <VirtualCubeDimension cubeName="Capex" name="RadioElement"/> <VirtualCubeDimension cubeName="Capex" name="Time"/> <VirtualCubeDimension cubeName="Users" name="Time"/> <VirtualCubeDimension cubeName="Users" name="RadioElement"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Cost]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Revenue]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Gross_Margin]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Ebitda]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Effective_minutes]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[Cash_Flow]"/> <VirtualCubeMeasure cubeName="Bts" name="[Measures].[CashPerMin]"/> <VirtualCubeMeasure cubeName="Capex" name="[Measures].[AggCapex]" visible="false"/> <VirtualCubeMeasure cubeName="Users" name="[Measures].[RUsers]" visible="true"/> <VirtualCubeMeasure cubeName="Target" name="[Measures].[Target]" visible="true"/> <CalculatedMember name="ValidCapex" dimension="Measures"> <Formula>[Measures].[AggCapex]</Formula> </CalculatedMember> <CalculatedMember name="VUsers" dimension="Measures"> <Formula>Iif([Measures].[Cash_Flow] IS EMPTY,NULL,([Measures].[RUsers],Ancestor(RadioElement.CurrentMember,Region) ) )</Formula> </CalculatedMember> <CalculatedMember name="ValidUsers" dimension="Measures"> <Formula>Iif([Measures].[Cash_Flow] IS EMPTY,NULL,Iif([CoverageType].CurrentMember.CHILDREN.COUNT=0,([Measures].[VUsers],CoverageType.CurrentMember.Parent ),[Measures].[VUsers]))</Formula> </CalculatedMember> <CalculatedMember name="ValidTarget" dimension="Measures"> <Formula>Iif([Measures].[Payback] IS EMPTY,NULL,([Measures].[Target],Ancestor(RadioElement.CurrentMember,Region) ) )</Formula> </CalculatedMember> <CalculatedMember name="Payback" dimension="Measures"> <Formula>IIf([Measures].[Cash_Flow] IS EMPTY,NULL,[Measures].[ValidCapex] / [Measures].[Cash_Flow])</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="###.#"/> </CalculatedMember> <CalculatedMember name="Mou" dimension="Measures"> <Formula>Iif([Measures].[ValidUsers] IS EMPTY, NULL, ([Measures].[Effective_minutes], Ancestor(RadioElement.CurrentMember,Region) )/([Measures].[ValidUsers]))</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="###.#####"/> </CalculatedMember> <CalculatedMember name="TargetCF" dimension="Measures"> <Formula>Iif([Measures].[ValidUsers] IS EMPTY, NULL, [Measures].[ValidCapex]/[Measures].[ValidTarget])</Formula> </CalculatedMember> <CalculatedMember name="TargetUsers" dimension="Measures"> <Formula>Iif([Measures].[TargetCF]-[Measures].[Cash_Flow]>0,([Measures].[TargetCF]-[Measures].[Cash_Flow])/([Measures].[Mou]*[Measures].[CashPerMin]),0)</Formula> <CalculatedMemberProperty name="FORMAT_STRING" value="########"/> </CalculatedMember></VirtualCube></Schema>