Avatar of barkome
barkome
Flag for Canada asked on

GL Trial Balance Data Model

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?
Microsoft SQL Server

Avatar of undefined
Last Comment
barkome

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
srikanthreddyn143

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
David Todd

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
barkome

ASKER
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.
David Todd

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
barkome

ASKER
Thanks David

There is actually an indicator (I,E,A) on the Chart of Account Table....
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
srikanthreddyn143

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
barkome

ASKER
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?
srikanthreddyn143

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
barkome

ASKER
Apologies for my questions, but Im a little lost here, what about historical opening balances on a daily basis?
srikanthreddyn143

Opening balance will be previous day's closing balance.
barkome

ASKER
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?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck