locked
Table Design RRS feed

  • Question

  • Hi  Guys,

    REQ/.

    we are going to develop a reporting project using below data.

    1. Real time systems(OLTP)

    2. Multiple other sources(History data).

    Please advise.. which data model is good to go either OLAP(dim, Fact) design or OLTP design.

    Also, please advise how to deign the Location Master table with Hierarchical model.

    Regards,

    Wood

    • Moved by Tom PhillipsEditor Monday, July 16, 2012 9:23 PM Design question (From:Transact-SQL)
    Monday, July 16, 2012 9:18 PM

Answers

  • So this is a really wide open question. For reporting the dimensional pattern is often the best, though if you are wanting to do real-time reporting, it is not going to be a simple task to do the ETL. A lot depends on your actual needs, but seperating the OLTP from the reporting data can be great for your performance.

    In terms of hierarchial model, the simple version is a self-referencing table, where you repeat the primary key in the table for the parent, like:

    create table location
    (
             locationId  int primary key,
             parentLocationId int foreign key (parentLocationId) references location(locationId)
    )

    If you have a fairly small number of rows (which could be hundreds or thousands), then this can be a very useful pattern, but depending on your needs, there are quite a few other possible optimizations for how to deal with hierarchies.

    Can you provide more requirements about what you are solving?


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, July 17, 2012 2:33 AM

All replies

  • Hi Wood123456,

    Please check out the differences of the OLAP and OLTP for your Repoting purpose:
    http://www.cbsolution.net/ontarget/olap_vs_oltp_what_makes

    For Table design, Hierarchies, Relationships etc. very good example to start with is analysis of "AdventureWorks" Databases. You can download and install both OLTP and OLAP version databases for your analysis.
    http://msftdbprodsamples.codeplex.com/

    Thanks, Khilit
    http://www.bigator.com

    Tuesday, July 17, 2012 1:25 AM
  • So this is a really wide open question. For reporting the dimensional pattern is often the best, though if you are wanting to do real-time reporting, it is not going to be a simple task to do the ETL. A lot depends on your actual needs, but seperating the OLTP from the reporting data can be great for your performance.

    In terms of hierarchial model, the simple version is a self-referencing table, where you repeat the primary key in the table for the parent, like:

    create table location
    (
             locationId  int primary key,
             parentLocationId int foreign key (parentLocationId) references location(locationId)
    )

    If you have a fairly small number of rows (which could be hundreds or thousands), then this can be a very useful pattern, but depending on your needs, there are quite a few other possible optimizations for how to deal with hierarchies.

    Can you provide more requirements about what you are solving?


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Tuesday, July 17, 2012 2:33 AM
  • Hi

    According to context better not to use OLTP database ,useing OLAP is better.

    You can use distributed query for access OLTP tables and run sql fron OLAP database.

     


    Ahsan Kabir

    Tuesday, July 17, 2012 12:19 PM