locked
dimensional Datadabse and relational database RRS feed

  • Question

  • hi friends, 

    please help me to understand the things on dymantional database.

    is it like when we talk about MOLAP storage mode and create a dymantional database (dataware house) by choosing Facts and Dimentiions from the Relational database it stores the copy of data from relational to modeling database.

    is it right if i says:  once dymantional data creates it gets its individual copy of tables from the relational database ???

    i am confuse enough to fire to put the question properly, in short can  any one please assist me to understand how modeling database is sync or dependent with relational database , does it takes it saperate copy from RDBMS or just use the RDBM itself instead of making its saperate copy.

    please assist me, many many thanks in advance.

     

     


    Regards, Shanu
    Saturday, May 14, 2011 4:28 AM

Answers

  • Hi

     

    If your storage mode is MOLAP then it does create a copy of the data.

     

    If it's ROLAP then it doesn't, and accesses the data at query time from the cache, or from the relational source.

     

    (HOLAP is a Hybrid of the two modes)

     

    Regards,

    Jason


    MCITP BI Developer - MCTS SQL Server
    • Proposed as answer by Jason V Campbell Wednesday, May 18, 2011 4:53 PM
    • Marked as answer by Jerry Nee Tuesday, May 24, 2011 1:02 AM
    Saturday, May 14, 2011 8:55 AM
  • Generally we have the 3 layers in which data is stored. One the OLTP (normalized database) which is transactional system where in apps interact to. Then we have the data warehouse database (de-normalized data) which will have data pulled from OLTP systems (single or multiple). The data is generally pulled by tools like SSIS etc on a nightly basis. Then we have Cube or OLAP which consists of measures and dimensions based on which users can analyze using various analytical tools or SSRS reports. The OLAP is built on top of data warehouse database. When the cube is processed, the data is stored in the way that OLAP requires separately in its own efficient format. Now the cube can exists in various storage mode, MOLAP/ROLAP/HOLAP. The way it stores is as mentioned by Jason. In molap mode.

    you can try processing the cube and then deleting the data warehouse database. You can still query the cube and get the results. That simply implies how it stores. Then when you process again, you need the database as that is the source based on which the cube is built

    Hope it answers most of your question


    vinu
    • Proposed as answer by Jason V Campbell Wednesday, May 18, 2011 4:53 PM
    • Marked as answer by Jerry Nee Tuesday, May 24, 2011 1:02 AM
    Saturday, May 14, 2011 1:05 PM

All replies

  • Hi

     

    If your storage mode is MOLAP then it does create a copy of the data.

     

    If it's ROLAP then it doesn't, and accesses the data at query time from the cache, or from the relational source.

     

    (HOLAP is a Hybrid of the two modes)

     

    Regards,

    Jason


    MCITP BI Developer - MCTS SQL Server
    • Proposed as answer by Jason V Campbell Wednesday, May 18, 2011 4:53 PM
    • Marked as answer by Jerry Nee Tuesday, May 24, 2011 1:02 AM
    Saturday, May 14, 2011 8:55 AM
  • Generally we have the 3 layers in which data is stored. One the OLTP (normalized database) which is transactional system where in apps interact to. Then we have the data warehouse database (de-normalized data) which will have data pulled from OLTP systems (single or multiple). The data is generally pulled by tools like SSIS etc on a nightly basis. Then we have Cube or OLAP which consists of measures and dimensions based on which users can analyze using various analytical tools or SSRS reports. The OLAP is built on top of data warehouse database. When the cube is processed, the data is stored in the way that OLAP requires separately in its own efficient format. Now the cube can exists in various storage mode, MOLAP/ROLAP/HOLAP. The way it stores is as mentioned by Jason. In molap mode.

    you can try processing the cube and then deleting the data warehouse database. You can still query the cube and get the results. That simply implies how it stores. Then when you process again, you need the database as that is the source based on which the cube is built

    Hope it answers most of your question


    vinu
    • Proposed as answer by Jason V Campbell Wednesday, May 18, 2011 4:53 PM
    • Marked as answer by Jerry Nee Tuesday, May 24, 2011 1:02 AM
    Saturday, May 14, 2011 1:05 PM