Solved

GL Trial Balance Data Model

Posted on 2014-11-27
11
299 Views
Last Modified: 2014-12-04
I am trying to create a data warehouse from scratch starting with Trial Balance reporting in a multidimensional form.

The tables I have in my staging area are:

-General Ledger Accounts  (Account Balances with daily snapshots of closing balances)
-General Trans Accounts    (Detailed transaction linked at the line level indicating debit and credit values)
-Business Entity (Details of each line of business linked to chart of account sub_prod_id)
-Chart of Accounts
-GL Mapping (GL Codes mapped to Chart of Accounts)

Can anyone help?
0
Comment
Question by:barkome
  • 5
  • 4
  • 2
11 Comments
 
LVL 11

Accepted Solution

by:
srikanthreddyn143 earned 250 total points
ID: 40469705
1. GL Account table -->

GL_ACCOUNT

1. GL_ACCOUNT_ID
2. GL_ACCOUNT_NAME
3. GL_ACCOUNT_NUMBER
4. CURRENT_ACCOUNT_BALANCE

2., GL Transaction table -->

1. GL_ACCOUNT_ID (Forieng key to GL_ACCOUNT.GL_ACCOUNT_ID)
2. DEBIT_CREDIT_TRANSACTION (Char(1)) --> indicated D for Debit and C for credit
3. GL_TRANSACTION_AMOUNT
4. TRANSACTION_TYPE --> This identifies different business processes that created transactions in this table

You can add other column to map yourother business identities.

3. END_OF_DAY BALANCE table

1. GL_ACCOUNT_ID
2. END_OF DAY_BALANCE --> calculated balance for the day
3. END_DATE --> Closing Date

You can actually get information of 3 rd table information from Table 2 itself. It is up to you whether to have different table. I would consider different table thinking of data the transaction table will have.

Thanks
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 250 total points
ID: 40469940
Hi,

Pretty much agree with the above.

To take the generalised trial balance and create P&L and Balance Sheet you'll need an indicator as such on the GL_Account table.

One thing I've seen is that the Debit/Credit sign is also on the GL_Account table.

Although harder to read, it can be easier if the D/B is a bit which is then mangled like (2 * sign - 1 ) * amount.

HTH
  David
0
 

Author Comment

by:barkome
ID: 40470044
Thanks Guys

So the GL Transaction will be my detailed fact table(e.g F_GL_TRANS), while GL_ACCOUNT will be DimGL_Acct, and END_OF_DAY BALANCE will be Dim_EOD_Balance?

@David, will the indicator you mentioned be the Dimension_Key?

@Srikanthreddyn143, I agree with the different table for EOD balance, as the current balance in the GL Account table will be the actual opening balance (closing balance for the previous day)

Also, will I need a separate fact table for monthly summaries? There's a requirement to have the capabilities to be able to drill down from account balance summary to view the actual transactions that make up the daily/monthly balance.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 35

Expert Comment

by:David Todd
ID: 40470050
Hi,

I don't see the P&L/Balance Sheet indicator as a dimension key. Maybe a sub-key or property of the account-number dimension.

HTH
  David
0
 

Author Comment

by:barkome
ID: 40470056
Thanks David

There is actually an indicator (I,E,A) on the Chart of Account Table....
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 40470655
Also, will I need a separate fact table for monthly summaries? There's a requirement to have the capabilities to be able to drill down from account balance summary to view the actual transactions that make up the daily/monthly balance.

You can derive from Table 2 or You can populate table 3 everyday through SQL job or any other service and use it
0
 

Author Comment

by:barkome
ID: 40481155
Dears

Im having difficulty, trying to present a solution regarding drill down to transactions from the trial balance report.

Where I am having a problem is how to display the individual opening balance for each line item on the day of either a debit or credit transaction. My GL Ledger table only has the current closing balance, with no historical snapshot of daily opening balances even at the time of the transactions.

Can anyone help?
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 40481205
That's the reason we have 3rd table END_OF_DAY BALANCE table.

This table needs to get updated everyday with cumulative amount of all the transactions happendd for that day plus previous day balance.

Let's take an example.

On December 3rd you have a record in END_OF_DAY Balance...

12/03/2014  $100.00
on Dec 4th you made 2 debit transactions.

1. When first debit of $10 is posted in trans table, you need to update END_OF_DAY balance to $110 i.e., $100( prev day) + $10
2. When second debit of $30 is made, it will be $110 + $30 = $140

That it will be easy to get daily closing balances

Or

Get the total amount of transaction happened in a the date range from GL Trans table and total amount prior to selected date range and you can do math to get specific day closing amount.

I don't prefer this method as there will be huge load running through all transactions.
0
 

Author Comment

by:barkome
ID: 40481318
Apologies for my questions, but Im a little lost here, what about historical opening balances on a daily basis?
0
 
LVL 11

Expert Comment

by:srikanthreddyn143
ID: 40481342
Opening balance will be previous day's closing balance.
0
 

Author Comment

by:barkome
ID: 40481558
I understand the logic on business terms but developing a query to achieve that is proving difficult, and I really need help on this.

The Ldgr acct only has current balance, with a few other attributes relating to chart of accounts and so on. The transaction table has daily debit, credit and effective dates.

Based on this, what syntax can I use to to get opening balance dr, opening balance cr, movement balance dr, movement balance cr, closing balance dr and closing balance cr, with ldgr ID, GL code, chrt ID at both monthly and daily level for drill down analysis or reporting? What will be the fact table and how will this be updated with foreign keys links to dimension tables?
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question