Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Historical Balance ??

 
   Database Help (Home) -> Data Warehouse RSS
Next:  Loading Adventure Works DW  
Author Message
appels77

External


Since: Mar 21, 2008
Posts: 1



(Msg. 1) Posted: Fri Mar 21, 2008 8:50 am
Post subject: Historical Balance ??
Archived from groups: microsoft>public>sqlserver>datawarehouse (more info?)

Hello - I have this requirement that seems like it should be fairly
straight forward to solve - but I can't seem to get it to work quite
right. It's a basic accounting system, with charges/receipts occuring
daily. What I also need to get - is what the current balance of an
account was at any given point in time in history. For example:

John Doe gets an invoice for:
$100 on 01/01/2008

He makes payments of:
$10 on 01/10/08
$10 on 01/15/08
$10 on 02/10/08

Looking at each level of the time hierarchy should look like this:
Date: Q1 2008 Amount Billed: $100 Amount Paid: $30 Amount Outstanding:
$70
Date: Jan 2008 Amount Billed: $100 Amount Paid: $20 Amount
Outstanding: $80
Date: Feb 2008 Amount Billed: $0.00 Amount Paid: $10 Amount
Outstanding: $70
Date: Feb 10, 2008 Amount Billed: $0.00 Amount Paid: $10 Amount
Outstanding: $70
Date: Feb 20, 2008 Amount Billed: $0.00 Amount Paid: $0.00 Amount
Outstanding: $70

I tried to use the Adventure Works example with the DimAccount
dimension, that had an Account Balance parent, and two children of
Charge and Receipt. Charge had a unary operator of '+' and Receipt had
a unary operator of '-'. This sort of worked - except not down to the
day level. For example, on Feb 10, 2008 it would take $0.00 - $10 and
give a balance of -$10.

I then created a calculated measure as:
Aggregate
(
NULL:[Transaction Date].[Year - Quarter - Month -
Date].CurrentMember,
[Measures].[Amount]
)

This seemed to work (keep in mind the Amount measure automatically
subtracts Receipts from Charges because of the unary operator) but I
fear a performance hit on this when the DW gets populated with all the
invoices (roughly 24,000 per year).

The ultimate goal is to be able to get Amount Paid, Amount Billed,
Amount Outstanding and the Avg. Days Outstanding for any point in
time.

Any performance suggestions?
Any links to related articles?
Any experience with accounting related type cubes?

Anything would help - thanks very much!!

Tracie

 >> Stay informed about: Historical Balance ?? 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
SQL Scheduled Jobs - Is there a way to monitor schedule jobs and restart the jobs automatically when it faills and if it fails then notify the responsible person, is there a tool to do this ?

Running total with "treshold value" detection..? - Hi, I have to create a query which evalaute, for each year, the employees who reached a particular number of absences and when this value is reached. I have 1 000 000 records in my absence table. my table is like this: DateID, EmployeeID, ActivityID,...

Problem with multiple instant of SQL Server - I have 2 instant of SQL server on the same machine. 1st instant keep most of the static information - Dimensional data 2nd instant keep most of the transaction information. When I try to use the Analysis Services, I have 2 connection, but they can't ..

Near Real time updating of fact table - -what is the best method that to implement the near real time updating of a fact table without affecting all those users who are reading from it? -fact table in question is only 1.8gb in size, 3.1 million rows, expect near real time updating of approx....

Extraction software - Hi, I need an extraction software that needs to be capable of capturing a table (or tables) displayed on the web and converting it to a file as a list of records with comma separated values. The records will be converted to insert commands after the....
   Database Help (Home) -> Data Warehouse All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]