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.
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.