ADVANTA (ADVNA)

i wanted to post a reply I made rerdaging an email question on this post:Question: On #3, could you provide some more cons to reporting against a transactional database?Reply:Good question, there are a few reasons why it's not optimal to report against a transactional database:1.A big reason is performance – reporting uses a lot of memory and CPU, especially, reports that use data cubes or mdx queries.2.Data locks can happen more frequently, when someone is adding data while someone is trying to report against it.3.Database designs for reporting are usually different than that for transaction databases. Transactional database should be normalized, with primary and foreign key relationships to ensure the data integrity and to eliminate duplicate or orphaned records. Reporting data structures are usually not normalized, or flat. Using a star database design, or a “table for each major reporting area” is typical for speed on the queries. Also, the indexes many times would be different. Databases for your applications are usually OLTP (On-Line Transaction Processing) and optimized to store data. Reporting databases are usually OLAP (On-Line Analytic Processing) optimized to retrieve data.4.Also, consider scalability if your reporting against your transactional database. How will it perform in a year from now with growth, more data, more reporting requirements, etc.?