1 out of 1 rated this helpful - Rate this topic

Planning and budgeting calculation examples for BI planning solutions and scenarios

SharePoint 2010

Published: January 27, 2011

Planning and budgeting calculation examples

Cube calculations

The following is an example of how we can do some simple calculations in MdxScript for driving the HR Budget model.

Base pay calculation

Calculate the base pay for hourly employees based on the number of hours worked and the hourly wage rate as determined by the pay grade.

```// All calculations on HR done at leaf level
SCOPE
(
[Employee].[All_Employee].members
, DESCENDANTS([Geography].[Geographies].[All], 1000, LEAVES)
, [Measures].[Value]
);

[Metric].[Metrics].[Base] =

ELSE NULL
END;

END SCOPE;
```

Benefit calculation

Calculation to determine the estimated the benefits dollars based on the base pay.

```// All calculations on HR done at leaf level
SCOPE
(
[Employee].[All_Employee].members
, DESCENDANTS([Geography].[Geographies].[All], 1000, LEAVES)
, [Measures].[Value]
);
// Benefit averaged out to 25% of base
[Metric].[Metrics].[Benefit] = [Metric].[Metrics].[Base] * 0.25;

END SCOPE;

```

Total compensation calculation

Total compensation calculation based on base compensation and benefit dollars.

```// All calculations on HR done at leaf level
SCOPE
(
[Employee].[All_Employee].members
, DESCENDANTS([Geography].[Geographies].[All], 1000, LEAVES)
, [Measures].[Value]
);

// Total = base + benefits
[Metric].[Metrics].[Total] = [Metric].[Metrics].[Base] + [Metric].[Metrics].[Benefit];

END SCOPE;

```

Stored procedure calculations

Many planning solutions require a currency translation rule that converts financial data into multiple currencies. Here, we will explore an example of a currency conversion rule implemented by a stored procedure. To perform currency translation, we need the following:

• Exchange Rate table holding to the conversion rates from a source currency to destination currency and by time period.

• A fact table that holds all the values that require translation.

Because we have a model designed for storing exchange rates, we can use its fact table as the exchange rate table.

T-SQL for currency translation

```SELECT
a.MemberName [Account]
,t.MemberId [Time]
,s.MemberName [Scenario]
,g.MemberName [Geography]
,c.MemberName [CurrencyType]
,g.[Input Currency]
,g.[Reporting Currency]
,Fact.[Value]
,ExchangeRate.Value [Exchange Rate]
,Fact.[Value]*ExchangeRate.Value [Calculated]
FROM [dbo].[F_Financial Consolidation_CoreMG_Writeback] Fact
INNER JOIN D_Account a
ON Fact.AccountID = a.MemberId
INNER JOIN D_Time t
ON Fact.TimeID = t.memberid
INNER JOIN D_Scenario s
ON Fact.GeographyID = s.MemberId
INNER JOIN D_Geography g
ON Fact.ScenarioID = g.MemberId
INNER JOIN d_currencyType c
ON Fact.currencyTypeID = c.MemberId
---
--- Currency Join
---
INNER JOIN
(SELECT
sc.MemberName [Source]
,dc.MemberName [Destinatation]
,t.MemberId [Time]
,[Value]
FROM [F_Exchange Rates_CoreMG_Writeback] ef
INNER JOIN D_SourceCurrency sc
ON sc.MemberId = ef.SourceCurrencyID
INNER JOIN D_DestinationCurrency dc
ON dc.MemberId = ef.DestinationCurrencyID
INNER JOIN D_Time t
ON t.MemberId = ef.TimeID
WHERE sc.MemberId <> dc.MemberId) ExchangeRate
ON ExchangeRate.Source = g.[Input Currency]
AND ExchangeRate.Destinatation = g.[Reporting Currency]
```

Change History

Date Description

January 27, 2011

Initial publication