Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: View Join or NameExpression: which has better performances?

  1. #1
    Join Date
    Dec 2007
    Posts
    23

    Default View Join or NameExpression: which has better performances?

    Hi,
    I have dimensions which are not pre-defined. They are based only on the fact table. Example:

    I have a SALES table with (among others) has the fields TYPE, THICK, QUALITY. These 3 fields forms a hierarchy "dinamically" (it means I do not have a pre-define hierarchy which defines all possible combinations). My dimension looks like:

    Code:
    <Table name="SALES"/>
    
    <Dimension name=MaterialType" >
        <Hierarchy hasAll="true" >
          <Level name="Type" column="TYPE"/>
          <Level name="Thickness" column="THICK" />
          <Level name="Quality" column="QUALITY" />
        </Hierarchy>
    </Dimension>
    Now, TYPE and QUALITY has a key and a description. I want the latter to appear to the users.

    As far as I have understood I can find more solutions:

    1) I create a <View> with an SQL statements which joins all my definition tables and then defining:

    Code:
    <View alias="SALES"...SQL....INNER JOIN...with all def tables>
    
    <Dimension name=MaterialType" >
        <Hierarchy hasAll="true" >
          <Level name="Type" column="TYPE_DESCRIPTION"/>
          <Level name="Thickness" column="THICK" />
          <Level name="Quality" column="QUALITY_DESCRIPTION" />
        </Hierarchy>
    </Dimension>
    2) I still use the fact table and I join the hierarchy, like:
    Code:
    <Table name="SALES"/>
    
    <Dimension name=MaterialType" >
        <Hierarchy hasAll="true" >
         <Join type_def table...
         <Join quality_def table.....
          <Level name="Type" column="TYPE_DEFINITION"/>
          <Level name="Thickness" column="THICK" />
          <Level name="Quality" column="QUALITY_DEFINITION" />
        </Hierarchy>
    </Dimension>
    3) In most cases I can use a CASE statement to add a name to the level like:
    Code:
    <Table name="SALES"/>
    
    <Dimension name=MaterialType" >
        <Hierarchy hasAll="true" >
          <Level name="Type" column="TYPE">
         <NameExpression><SQL>
         <![CDATA[ CASE WHEN TYPE='AB' THEN 'Medium Quality' ....END>
         </Level>
          <Level name="Thickness" column="THICK" />
          <Level name="Quality" column="QUALITY" />
          ...
        </Hierarchy>
    </Dimension>
    The question is: which one is better for performances? And for caching?

    Many thanks for any advice!
    Lorenzo

  2. #2
    Join Date
    Nov 1999
    Posts
    1,618

    Default

    In general, the join will work better. It allows mondrian to decompose the query and join in just the tables it wants.

    If your database is smart enough, it may be able to drop tables from the FROM clause of a query if they are not needed. That's asking a lot of the database, and you will probably need to help its optimizer by adding primary and foreign keys.

    Best thing is to follow the standard tuning process. Create a data set which is large enough to cause problems. Choose a representative set of MDX queries. Run mondrian with tracing turned on. See which SQL statements are taking a significant amount of time. Tune those statements; don't waste time on statements which are executing fast enough. Tuning includes figuring out whether mondrian is generating a smart query, and looking at the plan to figure out whether the DBMS is executing the query efficiently. Make changes to mondrian's schema or the organization of the database (e.g. create indexes, primary or foreign keys) to allow mondrian and the DBMS to do the smart thing.

    Basing your mondrian schema on SQL expressions can have an impact. Your database may not be able to use indexes as an access path to those expressions (depends on your DBMS of course). It's generally better if you compute all expressions during the ETL process and store them in plain old columns, which you can index if necessary.

    Julian

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.