tag:blogger.com,1999:blog-69572354126886286382024-03-18T21:15:25.234-07:00BI Reporting with Mehul ThakkarAnonymoushttp://www.blogger.com/profile/14471930143496937490noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-6957235412688628638.post-92220829623919786192013-07-22T08:05:00.001-07:002013-07-22T12:18:28.977-07:00P&L Statement in Business Intelligence<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b style="mso-bidi-font-weight: normal;"><span style="font-size: 16pt; line-height: 115%;"><span style="font-family: Calibri;">P&L Statement in BI – Part 1<o:p></o:p></span></span></b></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">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.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;"><strong>Example – </strong></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Hierarchies Displayed
in BI tools –<o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">…<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">EBIT(Sum of Net Income + Net Costs)<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 72pt; mso-add-space: auto; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Net Income<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 72pt; mso-add-space: auto; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Net Costs<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 72pt; mso-add-space: auto; mso-list: l0 level2 lfo1; text-indent: -18pt;">
<span style="font-family: "Courier New"; mso-fareast-font-family: "Courier New";"><span style="mso-list: Ignore;">o<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">….<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="margin: 0cm 0cm 10pt 108pt; mso-add-space: auto; mso-list: l0 level3 lfo1; text-indent: -18pt;">
<span style="font-family: Wingdings; mso-bidi-font-family: Wingdings; mso-fareast-font-family: Wingdings;"><span style="mso-list: Ignore;">§<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">….<o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Structure of Standard
P&L Statement <o:p></o:p></span></b></div>
<div class="MsoListParagraphCxSpFirst" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">…<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Net Income<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">Net Costs<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle" style="margin: 0cm 0cm 0pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">EBIT (Sum of Net Income + Net Costs)<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast" style="margin: 0cm 0cm 10pt 36pt; mso-list: l0 level1 lfo1; text-indent: -18pt;">
<span style="font-family: Symbol; mso-bidi-font-family: Symbol; mso-fareast-font-family: Symbol;"><span style="mso-list: Ignore;">·<span style="font-size-adjust: none; font-stretch: normal; font: 7pt/normal "Times New Roman";">
</span></span></span><span style="font-family: Calibri;">…..<o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">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.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Objects to be created –<o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">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. <o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">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.</span><o:p><span style="font-family: Calibri;"> </span></o:p><o:p><span style="font-family: Calibri;"> </span></o:p><span style="font-family: Calibri;"></span></div>
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><div class="separator" style="clear: both; text-align: center;">
<span style="font-family: Calibri;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqq81BpXZ7uR0H4AzXtI3GvvKKmBlSJ6qobu9hhbj1l_q8MlLCnkh6Snq0fV0MSttI9LLOwSU3ae-OCEfbdltwOOUMS5vMDGwmTWTG7F_SF_LHDpyAzu1bJ_r1i9Q1FAiKIIHoQJTIqLja/s1600/Untitled.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="220" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqq81BpXZ7uR0H4AzXtI3GvvKKmBlSJ6qobu9hhbj1l_q8MlLCnkh6Snq0fV0MSttI9LLOwSU3ae-OCEfbdltwOOUMS5vMDGwmTWTG7F_SF_LHDpyAzu1bJ_r1i9Q1FAiKIIHoQJTIqLja/s320/Untitled.png" width="320" /></a></span></div>
<span style="font-family: Calibri;">
</span>
<br />
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p><b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Example – <o:p></o:p></span></b></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="mso-no-proof: yes;"><v:shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" o:preferrelative="t" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f"><span style="font-family: Calibri;">
<v:stroke joinstyle="miter">
<v:formulas>
<v:f eqn="if lineDrawn pixelLineWidth 0">
<v:f eqn="sum @0 1 0">
<v:f eqn="sum 0 0 @1">
<v:f eqn="prod @2 1 2">
<v:f eqn="prod @3 21600 pixelWidth">
<v:f eqn="prod @3 21600 pixelHeight">
<v:f eqn="sum @0 0 1">
<v:f eqn="prod @6 1 2">
<v:f eqn="prod @7 21600 pixelWidth">
<v:f eqn="sum @8 21600 0">
<v:f eqn="prod @7 21600 pixelHeight">
<v:f eqn="sum @10 21600 0">
</v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas>
<v:path gradientshapeok="t" o:connecttype="rect" o:extrusionok="f">
<o:lock aspectratio="t" v:ext="edit">
</o:lock></v:path></v:stroke></span></v:shapetype></span><o:p></o:p></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">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. <o:p></o:p></span></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">Now the bridge table for the above example will look like –<o:p></o:p></span></div>
<table border="1" cellpadding="0" cellspacing="0" class="MsoTableGrid" style="border-collapse: collapse; border: currentColor; mso-border-alt: solid windowtext .5pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
<tbody>
<tr style="mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td style="background-color: transparent; border: 1pt solid windowtext; mso-border-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Account Group<o:p></o:p></span></b></div>
</td>
<td style="background-color: transparent; border-color: windowtext windowtext windowtext rgb(0, 0, 0); border-style: solid solid solid none; border-width: 1pt 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<b style="mso-bidi-font-weight: normal;"><span style="font-family: Calibri;">Account Id<o:p></o:p></span></b></div>
</td>
</tr>
<tr style="mso-yfti-irow: 1;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">EBIT<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">101<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 2;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">EBIT<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">102<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 3;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">EBIT<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">201<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 4;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">EBIT<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">202<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 5;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">Net Income<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">101<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 6;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">Net Income<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">102<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 7;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">Net Costs<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">201<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 8;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">Net Costs<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">202<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 9;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">….<o:p></o:p></span></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<span style="font-family: Calibri;">…<o:p></o:p></span></div>
</td>
</tr>
<tr style="mso-yfti-irow: 10; mso-yfti-lastrow: yes;">
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext; border-style: none solid solid; border-width: 0px 1pt 1pt; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
</td>
<td style="background-color: transparent; border-color: rgb(0, 0, 0) windowtext windowtext rgb(0, 0, 0); border-style: none solid solid none; border-width: 0px 1pt 1pt 0px; mso-border-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 240.55pt;" valign="top" width="321"><div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
</td>
</tr>
</tbody></table>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<o:p><span style="font-family: Calibri;"> </span></o:p></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;">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 –<o:p></o:p></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8vUJReOf5i3cxsUVYtvq0qQzp36ZgN4pRbtLcvQmG2S5mBa6RIj3SgtPe3dngVIXloicTF9Cfl1DE145mc6uIH3qeM6pGVdQRB48ADXAE5DDjimrXCnh5AgniRHhakuR3mZ0n7kiRWg_v/s1600/Untitled2.png" imageanchor="1" style="clear: left; float: left; height: 202px; margin-bottom: 1em; margin-right: 1em; width: 641px;"><img border="0" height="158" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8vUJReOf5i3cxsUVYtvq0qQzp36ZgN4pRbtLcvQmG2S5mBa6RIj3SgtPe3dngVIXloicTF9Cfl1DE145mc6uIH3qeM6pGVdQRB48ADXAE5DDjimrXCnh5AgniRHhakuR3mZ0n7kiRWg_v/s320/Untitled2.png" width="320" /></a></div>
<div class="MsoNormal" style="margin: 0cm 0cm 10pt;">
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<span style="font-family: Calibri;"></span><br />
<br />
<span style="font-family: Calibri;">And here goes the SQL to create a raw P&L report –</span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: blue; font-family: Consolas; font-size: 9.5pt;"><span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">SELECT</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="font-family: Consolas; font-size: 9.5pt;"><span style="mso-spacerun: yes;"><span style="color: black;"> </span></span><span style="color: teal;">P</span><span style="color: grey;">&</span><span style="color: teal;">L</span><span style="color: grey;">.</span><span style="color: teal;">Account_Group_Id</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: grey; font-family: Consolas; font-size: 9.5pt;">,</span><span style="color: magenta; font-family: Consolas; font-size: 9.5pt;">SUM</span><span style="color: grey; font-family: Consolas; font-size: 9.5pt;">(</span><span style="color: teal; font-family: Consolas; font-size: 9.5pt;">L</span><span style="color: grey; font-family: Consolas; font-size: 9.5pt;">.</span><span style="color: teal; font-family: Consolas; font-size: 9.5pt;">Amount</span><span style="color: grey; font-family: Consolas; font-size: 9.5pt;">)</span><span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: black;"> </span><span style="color: blue;">as</span><span style="color: black;"> </span><span style="color: teal;">Amount</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">FROM</span><span style="font-family: Consolas; font-size: 9.5pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: teal; font-family: Consolas; font-size: 9.5pt;">Ledger</span><span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: black;"> </span><span style="color: teal;">L</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: grey; font-family: Consolas; font-size: 9.5pt;">inner</span><span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: black;"> </span><span style="color: grey;">join</span><span style="color: black;"> </span><span style="color: teal;">AccountBridge</span><span style="color: black;"> </span><span style="color: teal;">A</span><span style="color: black;"> </span><span style="color: blue;">on</span><span style="color: black;"> L</span><span style="color: grey;">.</span><span style="color: teal;">Account_Id</span><span style="color: black;"> </span><span style="color: grey;">=</span><span style="color: black;"> </span><span style="color: teal;">A</span><span style="color: grey;">.</span><span style="color: teal;">Account_Id</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: grey; font-family: Consolas; font-size: 9.5pt;">inner</span><span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: black;"> </span><span style="color: grey;">join</span><span style="color: black;"> </span><span style="color: teal;">Dim_P</span><span style="color: grey;">&</span><span style="color: teal;">L_Report</span><span style="mso-spacerun: yes;"><span style="color: black;"> </span></span><span style="color: teal;">P</span><span style="color: grey;">&</span><span style="color: teal;">L</span><span style="color: black;"> </span><span style="color: blue;">on</span><span style="color: black;"> </span><span style="color: teal;">P</span><span style="color: grey;">&</span><span style="color: teal;">L</span><span style="color: grey;">.</span><span style="color: teal;">Account_Group_Id</span><span style="color: black;"> </span><span style="color: grey;">=</span><span style="color: black;"> </span><span style="color: teal;">A</span><span style="color: grey;">.</span><span style="color: teal;">Account_Group_Id</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">group</span><span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: black;"> </span><span style="color: blue;">by</span><span style="color: black;"> </span><span style="color: teal;">P</span><span style="color: grey;">&</span><span style="color: teal;">L</span><span style="color: grey;">.</span><span style="color: teal;">Account_Group_Id</span><o:p></o:p></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="color: blue; font-family: Consolas; font-size: 9.5pt;">order</span><span style="font-family: Consolas; font-size: 9.5pt;"><span style="color: black;"> </span><span style="color: blue;">by</span><span style="color: black;"> </span><span style="color: teal;">P</span><span style="color: grey;">&</span><span style="color: teal;">L</span><span style="color: grey;">.</span><span style="color: teal;">Sort_Sequence<o:p></o:p></span></span></div>
<div class="MsoNormal" style="line-height: normal; margin: 0cm 0cm 0pt; mso-layout-grid-align: none;">
<span style="color: black; font-family: Times New Roman; font-size: small;">
</span><span style="font-family: Calibri;">In part 2, I will describe in detail how the bridge table
and P&L dimensions can be created in the cubes in SSAS.<o:p></o:p></span></div>
</div>
Anonymoushttp://www.blogger.com/profile/14471930143496937490noreply@blogger.com0