Planning and budgeting calculation examples for BI planning solutions and scenarios

Article
07/22/2014

Applies to: SharePoint Server 2010 Enterprise

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] =

CASE [Metric].[Metrics].[PayGrade]
  WHEN 1 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[1])
  WHEN 2 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[2])
  WHEN 3 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[3])
  WHEN 4 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[4])
  WHEN 5 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[5])
  WHEN 6 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[6])
  WHEN 7 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[7])
  WHEN 8 THEN [Metric].[Metrics].[Hours]/2000 *([Measures].[HR Pay Rates_Value], [PayGrade].[PayGrade].&[8])
  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]

Planning and budgeting calculation examples for BI planning solutions and scenarios