none
Creating an OLAP model from OLTP

    Question

  • 1.Please Guide on creating an OLAP model from and OLTP schema. Are there any tools that can aid in the creating of an OLAP model? I have created a denormalized structure of the OLTP,  i need help.

    2.Please guide or refer articles/books.

    3.Can someone please explain a straight forward methodology on how to go about this? Please explain the steps involved. 

    Thanks

    Thursday, October 07, 2010 2:12 AM

Answers

  • Datawarehouse ToolKit is a good book that you can refer.

    You can use the SSAS provided by microsoft to create a SSAS Cube.

    1. Create Data Source

    2. Create a Data Source view with the available OLTP schema

    3. Create the dimension and then the create a cube which would contain various measures.

    4. Dimension contains data that use to slice on. Mostly the Master tables like Product, Employee etc

    5. Fact tables would contains data that would aggregated or measurable entities like sales amount quantity etc

    6. The one can write an MDX query on the cube.


    vinu
    Thursday, October 07, 2010 2:25 AM
  • Hi,

    You can have a look here for good books about the design and metodology: http://www.kimballgroup.com/html/books.html

    This Wiki is also a good start: http://en.wikipedia.org/wiki/Data_warehouse

    And I also recommend this site: http://www.sqlbi.com/Default.aspx?tabid=88

    Generall you build this from the OLTP to the OLAP part. Your mai tool will be Integarion Services in the MS BI stack.

    1.  Data staging area

    2. A normalized data warehouse

    3. Data marts for each cube.

    4. The OLAP cube

    HTH

    Thomas Ivarsson

    Thursday, October 07, 2010 5:14 AM
    Moderator

All replies

  • Datawarehouse ToolKit is a good book that you can refer.

    You can use the SSAS provided by microsoft to create a SSAS Cube.

    1. Create Data Source

    2. Create a Data Source view with the available OLTP schema

    3. Create the dimension and then the create a cube which would contain various measures.

    4. Dimension contains data that use to slice on. Mostly the Master tables like Product, Employee etc

    5. Fact tables would contains data that would aggregated or measurable entities like sales amount quantity etc

    6. The one can write an MDX query on the cube.


    vinu
    Thursday, October 07, 2010 2:25 AM
  • Hi,

    You can have a look here for good books about the design and metodology: http://www.kimballgroup.com/html/books.html

    This Wiki is also a good start: http://en.wikipedia.org/wiki/Data_warehouse

    And I also recommend this site: http://www.sqlbi.com/Default.aspx?tabid=88

    Generall you build this from the OLTP to the OLAP part. Your mai tool will be Integarion Services in the MS BI stack.

    1.  Data staging area

    2. A normalized data warehouse

    3. Data marts for each cube.

    4. The OLAP cube

    HTH

    Thomas Ivarsson

    Thursday, October 07, 2010 5:14 AM
    Moderator
  • http://www.accelebrate.com/sql_training/ssas_tutorial.htm

    This is a fantastic resource. Lots of examples cited as well.

    Thursday, October 07, 2010 6:02 AM
  • Thanks for the reply Tomas. Please can you flesh out the different steps in simpler terms? Thanks
    Friday, October 08, 2010 12:05 AM
  • The basic structure of an OLAP model is a Cube. Now a cube consists of Dimensions and measure groups. Dimensions comprise various attributes against which the data get stored in your fact table. Then you have measures, which are numeric data, for eg. sales volume, which you would like to 'measure' the 'dimensions'. For eg., time is a dimension. Dimensions are composed of attributes and hierarchies. A time dimension will have 'year','quarter','month' and 'week'. These can be organized as a hierarchy as well. So you can get the tables/views from OLTP  using the data source viewer in OLAP. From these, you can form dimensions and measures, and create cubes. You can do all this in SSAS. There are loads of resources online and you will have to read and experiment.
    Friday, October 08, 2010 8:06 AM
  • additionally to your question,

    OLTP contains table structure that is normalized and the application runs on top of it.

    OLAP is build on a table structure that is de-normalized and mostly only reporting tool consumes this via OLAP or SQL. We try to achieve a star schema or a snow flake.

    So in simple terms, this could be the architecture

    App1 -> Database1 (OLTP: Normalized) -> Database2 (DateWarehouse:Denormalized) -> OLAP -> Reports


    vinu
    Saturday, October 09, 2010 4:49 AM