Solved

Database design on an accounting system

Posted on 2008-10-26
13
581 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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