Database design on an accounting system

The accouting system is used MSSQL 2008 and one of the requirement from the user is to enquiry the history aging how customers owe the company money as at the date input.  The system has over than 10 million records. e.g. There is a ledger or voucher table to save all the money flow transactons  

positive mean customers owe money, negative value mean customers pay the money

Customer A           2008-7-30        1000
Customer A           2008-9-30        1000
Customer A           2008-10-30       -500

According to the customer requirement,  the 2008-10-30 will pay for the first record, i.e  2008-7-30. If the customer inputs 2008-10-30, the report will like this

owe < 30 days            30 - 60        90-180              > 180
    $0                               $1000          $500                 $0

If customer inputs 2008-9-30, the report will like this
owe < 30 days            30 - 60        90-180              > 180
    $1000                        $0               $1000                 $0

It is no problem to calculate the value on the fly. However, the ledger table cantains over 10M records and has many customers. It seems that it is quite time-consuming to calculate all the customers aging history on the fly. Is there any best way in the database design to accomodate this situation?
 





LVL 2
TorusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ClausewitzCommented:
This type of situation is usually solved with an OLAP cube in SQL Server Analysis Services.
You will have a far better performance with an OLAP cube.
0
TorusAuthor Commented:
Can you describe more about the OLAP cube since I am quite new on it ?
0
ClausewitzCommented:
An OLAP cube is a special type of database. So you first need to transfer your data from your current database to the cube and then you can run queries against your cube. Due to the different structure in a cube and some precalculations the query will be much faster than inside a relational database.

For more information you can look at:
http://en.wikipedia.org/wiki/Olap
http://www.microsoft.com/bi/bicapabilities/olap.aspx

so the pros of an OLAP cube are:
- much faster (due to a time dimension and preaggregations)
- you already have a license for it when you've bought sql server 2008

cons:
- additional work to transfer the data from your current productive database to the cube
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

ClausewitzCommented:
and of course there is books-online for more information
http://msdn.microsoft.com/en-us/library/bb522592.aspx
0
TorusAuthor Commented:
But if my production database continues to grow, is it ok to use OLAP cube?  i.e the cube will be updated simultanousely once the production database is updated? If not, it seems that the solution does not work. It is just for DSS only then.
0
ClausewitzCommented:
no the cube will not be automatically updated. But you can use automated tasks to update the cube every day at a specific time for example.

Another approach to make your current query faster might be using indexes. Is the customers ID an indexed field? Does your query/queries always target a specific customer or does it contain a list of all customers?
0
TorusAuthor Commented:
I am not sure the time to update the cube every day for so much records. If it takes a lot of time,  it seems not worth to do every day since the system also has a lot of house keeping or batch job at nite.

The fields should be supposed indexing properly. Actually, for now, because it is in design phase,  I can't imagine how slow/fast if the customers query a list of all customers.  This function should support ad-hoc query for a individual customer ID or a list of all customers to provide aging history.


0
TorusAuthor Commented:
Are you sure the OLAP cube in SQL server 2008 is not updated automatically once the production is updated. I asked my friend that it seems it will.
0
ClausewitzCommented:
well, once you have build a cube you can update it with only new rows (e.g. from the last 24 hours). This should not be very time consuming.

If you want a list from all customers, SQL server will have to go through all 10M rows. There is no way around it. Another possible solution might be to create a new table which will contain the result of the time consuming query. So you can run the time consuming query e.g. every night and write the results into the new table. You/ your clients then can query the result table (which will not contain the latest data).
0
ClausewitzCommented:
well, there is a new feature in 2008 which is called "Change Data Capture", but i'm not sure if it automatically updates the cube. As i read it this feature allows you to update your cube in a simplified way, once you "told" the cube to update.
0
TorusAuthor Commented:
Thanks. I will try to study first.

Get back to you later if I have more questions or I will give you marks.

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.