Post reply

Transactional database or Datawarehouse?

  • May 19, 2015 at 8:07 am

    #300792

    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

    #1799138

    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.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 19, 2015 at 2:00 pm

    #1799150

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy