Solved

Database design on an accounting system

Posted on 2008-10-26
13
556 Views
Last Modified: 2012-05-05
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?
 





0
Comment
Question by:Torus
  • 6
  • 5
13 Comments
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22806829
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
 
LVL 2

Author Comment

by:Torus
ID: 22806862
Can you describe more about the OLAP cube since I am quite new on it ?
0
 
LVL 2

Accepted Solution

by:
Clausewitz earned 250 total points
ID: 22806907
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
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22806919
and of course there is books-online for more information
http://msdn.microsoft.com/en-us/library/bb522592.aspx
0
 
LVL 2

Author Comment

by:Torus
ID: 22806934
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 2

Expert Comment

by:Clausewitz
ID: 22806979
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
 
LVL 2

Author Comment

by:Torus
ID: 22806990
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
 
LVL 2

Author Comment

by:Torus
ID: 22807030
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
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22807040
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
 
LVL 2

Assisted Solution

by:Clausewitz
Clausewitz earned 250 total points
ID: 22807065
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
 
LVL 2

Author Comment

by:Torus
ID: 22807077
Thanks. I will try to study first.

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

0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now