Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Non Collapsed Snowflake Agg joins on wrong column

  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Default Non Collapsed Snowflake Agg joins on wrong column

    Hallo everybody!


    I use mondrian in my java web application. It works fine without aggregate tables. But if I try to introduce an aggregate table in my schema, queer things happen. I' ll try to explain my problem step by step:


    1. I have designed following schema:


    Code:
    <?xml version="1.0" encoding="UTF-8"?>
        <Schema name="_olap_on_urd_5">
            <!--Shared Dimension Definitions-->
            <Dimension name="classes">
                <Hierarchy hasAll="true" primaryKey="id">
                    <Table name="classes_olap_on_urd_5" schema="olap" />
                    <Level name="name" column="name" caption="classes" uniqueMembers="true" />
                </Hierarchy>
            </Dimension>
            <Dimension name="requests">
                <Hierarchy name="plz" hasAll="true" primaryKey="id">
                    <View alias="plz_v">
                        <SQL dialect="generic">
                            SELECT DISTINCT 
                                r.id as id, s.station_name as station_name, p.five_digits as five_digits, p.two_digits as two_digits, p.digit as digit  
                            FROM 
                                olap.requests_olap_on_urd_5 as r, olap.stations_olap_on_urd_5 as s, olap.plz_olap_on_urd_5 as p  
                            WHERE 
                                r.stations_id = s.id and r.plz_id = p.id
                        </SQL>
                    </View>
                    <Level name="digit" column="digit" caption="PLZ" uniqueMembers="true" />
                    <Level name="two_digits" column="two_digits" caption="PLZ" uniqueMembers="true" />
                    <Level name="five_digits" column="five_digits" caption="PLZ" uniqueMembers="true" />
                    <Level name="station_name" column="station_name" caption="stations" uniqueMembers="true" />
                    <Level name="id" column="id" caption="requests" uniqueMembers="true" />
                </Hierarchy>
                <Hierarchy name="gds" hasAll="true" primaryKey="id">
                    <View alias="gds_v">
                        <SQL dialect="generic">
                            SELECT DISTINCT 
                                r.id as id, s.station_name as station_name, g.name as name 
                            FROM 
                                olap.requests_olap_on_urd_5 as r, olap.stations_olap_on_urd_5 as s, olap.gds_olap_on_urd_5 as g 
                            WHERE 
                                r.stations_id = s.id and r.gds_id = g.id
                        </SQL>
                    </View>
                    <Level name="name" column="name" caption="GDS" uniqueMembers="true" />
                    <Level name="station_name" column="station_name" caption="stations" uniqueMembers="true" />
                    <Level name="id" column="id" caption="requests" uniqueMembers="true" />
                </Hierarchy>
                <Hierarchy name="stations" hasAll="true" primaryKey="id">
                    <View alias="stations_v">
                        <SQL dialect="generic">
                            SELECT DISTINCT 
                                r.id as id, s.station_name as station_name, s.station_type as station_type 
                            FROM 
                                olap.stations_olap_on_urd_5 as s, olap.requests_olap_on_urd_5 as r 
                            WHERE 
                                r.stations_id = s.id
                        </SQL>
                    </View>
                    <Level name="station_type" column="station_type" caption="stations" uniqueMembers="true" />
                    <Level name="station_name" column="station_name" caption="stations" uniqueMembers="true" />
                    <Level name="id" column="id" caption="requests" uniqueMembers="true" />
                </Hierarchy>
            </Dimension>
            <!--Cube Definitions-->
            <Cube name="prices" defaultMeasure="price">
                <Table name="prices_olap_on_urd_5" schema="olap">
                    <!--Aggregation Table Definitions-->
                    <AggName name="AGG_ON_STATIONS_olap_on_urd_5">
                        <AggFactCount column="fact_count" />
                        <AggMeasure name="[Measures].[station_count]" column="fact_count" />
                        <AggMeasure name="[Measures].[request_count]" column="request_count_v" />
                        <AggMeasure name="[Measures].[min]" column="min_v" />
                        <AggMeasure name="[Measures].[max]" column="max_v" />
                        <AggMeasure name="[Measures].[avg]" column="avg_v" />
                        <AggMeasure name="[Measures].[day_avg]" column="day_avg_v" />
                        <AggLevel name="[requests.stations].[station_name]" column="station_name" collapsed="false" />
                        <AggLevel name="[classes].[name]" column="class_name" />
                    </AggName>
                    <AggName name="AGG_ON_STATIONS_olap_on_urd_5">
                        <AggFactCount column="fact_count" />
                        <AggMeasure name="[Measures].[station_count]" column="fact_count" />
                        <AggMeasure name="[Measures].[request_count]" column="request_count_v" />
                        <AggMeasure name="[Measures].[min]" column="min_v" />
                        <AggMeasure name="[Measures].[max]" column="max_v" />
                        <AggMeasure name="[Measures].[avg]" column="avg_v" />
                        <AggMeasure name="[Measures].[day_avg]" column="day_avg_v" />
                        <AggLevel name="[requests.gds].[station_name]" column="station_name" collapsed="false" />
                        <AggLevel name="[classes].[name]" column="class_name" />
                    </AggName>
                        <AggName name="AGG_ON_STATIONS_olap_on_urd_5">
                        <AggFactCount column="fact_count" />
                        <AggMeasure name="[Measures].[station_count]" column="fact_count" />
                        <AggMeasure name="[Measures].[request_count]" column="request_count_v" />
                        <AggMeasure name="[Measures].[min]" column="min_v" />
                        <AggMeasure name="[Measures].[max]" column="max_v" />
                        <AggMeasure name="[Measures].[avg]" column="avg_v" />
                        <AggMeasure name="[Measures].[day_avg]" column="day_avg_v" />
                        <AggLevel name="[requests.plz].[station_name]" column="station_name" collapsed="false" />
                        <AggLevel name="[classes].[name]" column="class_name" />
                    </AggName>
                </Table>
                <DimensionUsage name="classes" source="classes" foreignKey="classes_id" />
                <DimensionUsage name="requests" source="requests" foreignKey="requests_id" />
                <!--Measure Definitions-->
                <Measure name="station_count" column="price" aggregator="count" formatString="Standard" />
                <Measure name="request_count" column="price" aggregator="count" formatString="Standard" />
                <Measure name="min" column="price" aggregator="min" formatString="0.00" />
                <Measure name="max" column="price" aggregator="max" formatString="0.00" />
                <Measure name="avg" column="price" aggregator="avg" formatString="0.00" />
                <Measure name="day_avg" column="day_price" aggregator="avg" formatString="0.00" />
                <CalculatedMember name="StdDev" caption="Standard Deviation" formula="MWS_StdDev([Measures].[avg], '_olap_on_urd_5', 'olap', 'XXX', 'XXX', 'XXX', 'XXX', 'XXX')" formatString="0.00" dimension="Measures" visible="true" />
            </Cube>
            <!--User Defiened Function Definitions-->
            <UserDefinedFunction name="MWS_StdDev" className="XXX" />
        </Schema>

    2. My first problem was, that the agg joins were made on wrong column of the aggregation table. So I starded to search and found this fix:


    http://jira.pentaho.com/browse/MONDRIAN-1271


    So I downloaded the gant repository and build a new mondrian jar from the source code, where the fix is already present, as described in http://mondrian.pentaho.com/document...pers_guide.php


    3. I trued to execute two mdx-queries:


    3.1


    Code:
    select {[Measures].[station_count], 
        [Measures].[request_count], 
        [Measures].[min],
        [Measures].[max],
        [Measures].[avg],
        [Measures].[day_avg],
        [Measures].[StdDev]} ON 0,
        {[classes].[name].Members} ON 1
        from [prices]

    and


    3.2


    Code:
    select {[Measures].[station_count],
        [Measures].[request_count],
        [Measures].[min],
        [Measures].[max],
        [Measures].[avg],
        [Measures].[day_avg],
        [Measures].[StdDev]} ON 0,
        {[classes].[name].Members} ON 1,
        {[requests.plz].[digit].Members} ON 2
        from [prices]

    The query 3.1 uses the aggregation table as expected. The query 3.2 does not, but it should.


    So I have three questions:


    1. Am I doing something wrong or do I missing something important?
    2. How can I get the query 3.2 using aggregation table.
    3. Is there a way to talk to developer Luc Boudreau, who provided the fix I mentioned above?


    I would realy appreciated any answers, because the use of aggregation tables is the general topic of my diplom thesis.


    Thanks in advance!

  2. #2
    Join Date
    Mar 2007
    Posts
    142

    Default

    You can't use non-collapsed levels because you are using a single table for your dimension. This feature only works with snowflaked dimensions, or else the join will create a carthesian product.

    Either add new columns to represent the upper levels in your aggregate tables and mark it as non-collapsed, or split your dimension tables into multiple joined tables.
    Luc Boudreau
    aka. Luc le Magnifique
    aka. Monsieur Oui Oui

    Lead Engineer, Pentaho Corporation
    Web: http://devdonkey.blogspot.com
    Twitter: luclemagnifique
    IRC: Monsieur_Oui_Oui@freenode

  3. #3
    Join Date
    Apr 2012
    Posts
    3

    Default

    Thank you for your quick reply, Luc! I think, I will try the first solution, because I'm using MonetDB as underlying database and it does not like too many joins :-)

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.