Pages

Monday, July 22, 2013

P&L Statement in Business Intelligence

P&L Statement in BI – Part 1
P&L Statements in BI tools are not as simple as they sound, since in most of the BI tools hierarchies are rendered in a way that children are below the parent and not up-side down. This can be resolved by creating intermediate bridge table, basically a many-many cardinality table.
Example –
Hierarchies Displayed in BI tools –
·        
·         EBIT(Sum of Net Income + Net Costs)
o   Net Income
o   Net Costs
o   ….
§  ….
Structure of Standard P&L Statement
·        
·         Net Income
·         Net Costs
·         EBIT (Sum of Net Income + Net Costs)
·         …..
 
Below is the solution which can be used to create a P&L report in any BI tools, which involves creating two additional tables in the existing data warehouse or data mart to be used for reporting.
Objects to be created –
P&L Dimension – This table basically holds the Id’s of the account groups which are to be included in the P&L Statement. There can be a additional field for setting up the sequence of lines in P&L report.
Account Bridge – This is very crucial and key table since this table will contain all the leaf accounts for account group included in P&L Dimension. So as an example if there are total 200 accounts in General Ledger then the top line group of P&L will contain individual lines for all leaf accounts.  


 
 Example –
Here, for simplicity of understanding only few accounts are included, in real world scenarios, there can be more than 2000 leaf accounts. So, as shown in the example the leaf accounts are 101, 102, 201 and 202.
Now the bridge table for the above example will look like –
Account Group
Account Id
EBIT
101
EBIT
102
EBIT
201
EBIT
202
Net Income
101
Net Income
102
Net Costs
201
Net Costs
202
….
 
 
 
And the P&L Dimension will be basically just the list of account groups in P&L report. So the overall structure will look as below –














And here goes the SQL to create a raw P&L report –
SELECT
 P&L.Account_Group_Id
,SUM(L.Amount) as Amount
FROM
Ledger L
inner join AccountBridge A on L.Account_Id = A.Account_Id
inner join Dim_P&L_Report  P&L on P&L.Account_Group_Id = A.Account_Group_Id
group by P&L.Account_Group_Id
order by P&L.Sort_Sequence
In part 2, I will describe in detail how the bridge table and P&L dimensions can be created in the cubes in SSAS.