Solved

Database design on an accounting system

Posted on 2008-10-26
13
572 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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
 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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