Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Olap

  1. #1
    Join Date
    Nov 2011
    Posts
    9

    Default Olap

    All,

    I was hoping for a quick explanation.

    I've worked throgh various chapters within the Pentaho Kettle book, including the creation of the star schema for a fictious dvd rental company.

    I have a question. What is the fundemental difference between an OLAP cube and a relational star schema with dimensions and fact tables (which are potentailly summary tables). The example shows rentals basically being marked as a fact for the event, rather then building up a summary of rentals per period (which I believe is what summary tables are focussed on)

    Does this give you similar results? Is this actually an OLAP cube? I understand that OLAP has MDX langauage with it as well.

    Looking for some insight. Basically I read about OLAP and just think this is classic star/snowflake schema design.

    Please help the newbie!

  2. #2
    Join Date
    Jul 2009
    Posts
    476

    Default

    Skynet5,

    The Mondrian documentation, at http://mondrian.pentaho.com/documentation/, has a good explanation for the way Mondrian does OLAP. Briefly, here's how it works:

    (1) You load your data into a relational db with a star schema design.
    (2) You create a Mondrian schema, which is just an XML file, that defines your MDX cube model on top of the relational database.
    (3) You create MDX queries and reports that call Mondrian, which translates your queries into SQL for the relational database, pulls the data, and then translates the results back to you as MDX.

    If you're new to MDX, the link I gave has some information about it as well. For me, the nicest feature of MDX is its ability to group data in pretty much any way that you want. One example is comparing this year's YTD sales to the YTD sales one year ago.

    Rob

  3. #3
    Join Date
    Nov 2011
    Posts
    9

    Default

    Thanks Rob,

    So am I right in thinking that the fact table would normally contain a record per event?

    e.g. I have a client_dim and a product_dim with a fact table containing the price measure in the middle.

    As an order came in for bolts from clientA I would create a record in the fact table for the price linked to the product and client dimensions, then as another order (for same product from same client) came in I would create another fact record for that event.


    Then build the Modrian cube over the top of this star schema.


    Would you ever consider putting aggregated values into the fact table? So in the scenario above simply update the fact table, increasing the price to represent the total price for that client and product?

    As you can guess...this is all new to me!

  4. #4
    Join Date
    Jul 2009
    Posts
    476

    Default

    Skynet5,

    You build a fact table at the lowest level of granularity that you want to see. If you have an orders fact table, and you want to see how much each client ordered of each product per day, you would have three dimensions: client, product and date. If a client makes multiple orders for a single product on a single day, but all you care about is their total number of orders, then this would be OK. If, however, you need to distinguish each order, even when the client, product and date are the same, then you need another dimension such as order #.

    You can define aggregate tables in your Mondrian schema to improve query speed. The link I gave you earlier has a section that describes this. You are responsible for creating and populating the relational tables that provide the aggregates, i.e. your ETL needs to load the aggregate tables when it loads the fact tables, unless the DBMS can do that for you automatically via materialized views. Mondrian is smart enough to know when it can use an aggregate table to handle an MDX query, and it will prefer to use one when possible. You'll need to play around with it a bit to become familiar with how it works.

    Rob

  5. #5
    Join Date
    Nov 2011
    Posts
    9

    Default

    Thanks robj. Reading the docs now with a much better understanding.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.