May 19, 2015 at 8:07 am
I am designing a database for a Document Management application using asp.net MVC. This application will have a lots of Metrics for Reporting. Because of the metrics and the reporting I am thinking of designing the whole database as a DataWareHouse. I want to find out if this is a good idea or I should design a transactional database for the application and a Datawarehouse separate for the reporting and the metrics?. Any suggestions will be appreciated.
May 19, 2015 at 1:17 pm
Always this:
I want to find out if this is a good idea or I should design a transactional database for the application and a Data warehouse separate for the reporting and the metrics?
It's generally a bad idea to do reporting against your transactional (OLTP) databases'; they are generally optimized for lots of updates, inserts, deletes - things that are slower when you have a number of indexes in place and/or you are dealing with denormalized data structures. A data warehouse, on the other hand, is designed with reporting in mind. You can add more indexes and de-normalize your data with reporting in mind; you can do so without having to worry about the indexes down slowing your data modification tasks.
Even when creating a data warehouse is not an option I like to somehow separate my OLTP and Reporting data. Sometimes this can be as simple implementing snapshot or transactional replication to build and maintain a separate set of tables that are only for reporting.
-- Itzik Ben-Gan 2001
May 19, 2015 at 2:00 pm
Hi Alan
Thanks very much and happy to know from an authoritative source that I am doing the right thing with Data Warehouse. I appreciated your input.
Eb
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply