Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Can't map agg table to hierarchy level

  1. #1

    Default Can't map agg table to hierarchy level

    Dear all,

    I have a sample schema in which i need to use one detailed and one manually created agg table (for level "Country").
    I have:
    - one measure - number of customers (Q Customers)
    - one dimension - Region -> Country -> City

    The point is that my schema ignores agg table (agg_customer_country) when I display data by Country and uses values directly from detailed table (ADDRESSES).

    Here are my schema:
    Code:
    <Schema name="New Schema2" description="A. Belo****ski ">
      <Dimension type="StandardDimension" highCardinality="false" name="Geography">
        <Hierarchy hasAll="true" allMemberName="Total" primaryKey="ID" primaryKeyTable="CITIES">
          <Join leftKey="COUNTRY_ISO_CODE" rightKey="ISO_CODE">
            <Table name="CITIES" schema="BISE1_SALES"/>
            <Join leftKey="REGION_ID" rightKey="ID">
              <Table name="COUNTRIES" schema="BISE1_SALES"/>
              <Table name="REGIONS" schema="BISE1_SALES"/>
            </Join>
          </Join>
          <Level name="Region" table="REGIONS" column="REGION_ID" nameColumn="NAME" type="Numeric" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
          <Level name="Country" table="COUNTRIES" column="ISO_CODE" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
          <Level name="City" table="CITIES" column="ID" nameColumn="NAME" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
        </Hierarchy>
      </Dimension>
      <Cube name="Customers_F">
        <Table name="ADDRESSES" schema="BISE1_SALES">
          <AggName name="agg_customer_country">
            <AggFactCount column="FACT_COUNT"/>
            <AggForeignKey factColumn="COUNTRY_ISO_CODE" aggColumn="COUNTRY_ISO_CODE"/>
            <AggMeasure column="Q_CUSTOMER" name="[Measures].[Q Customers]"/>
            <AggLevel column="COUNTRY_ISO_CODE" name="[Geography].[Country]"/>
          </AggName>
        </Table>
        <DimensionUsage name="Geography" source="Geography" foreignKey="CITY_ID"/>
        <Measure name="Q Customers" column="ID" aggregator="distinct count"/>
      </Cube>
    </Schema>
    Maybe someone can help me to understand what is wrong in my scheme and why it ignores the agg table.


    Thank you so much,
    Alex.
    Last edited by whitish; 08-26-2010 at 09:11 AM.

  2. #2

    Default

    Hi Alex,

    Are you tried the Pentaho Aggregation Designer to generate table?

    Can you try the automatic detection of aggregate tables with the name paterns in your exemple the Aggregate table name must be agg_c_addresses.

    Regards
    Enric
    Enric Biosca
    Project Director at ICA

  3. #3

    Default

    I created my agg table manually. I now changed properties in mondrian.properties file, and my agg table is used, but for wrong level. Mondrian uses agg data for "Total" level instead of "Country". And I can't understand why. Below is my updated scheme. Maybe you may help me what is wrong now?
    Code:
    <Schema name="New Schema2">
      <Cube name="Customers_F" cache="true" enabled="true">
        <Table name="ADDRESSES" schema="BISE1_SALES">
          <AggName name="agg_customer_country" ignorecase="true">
            <AggFactCount column="FACT_COUNT"/>
            <AggMeasure column="Q_CUSTOMER" name="[Measures].[Q Customers]"/>
            <AggLevel column="COUNTRY_ISO_CODE" name="[Geography].[Country]"/>
          </AggName>
        </Table>
        <Dimension foreignKey="CITY_ID" highCardinality="false" name="Geography">
          <Hierarchy hasAll="true" allMemberName="Total" primaryKey="ID" primaryKeyTable="CITIES">
            <Join leftKey="COUNTRY_ISO_CODE" rightKey="ISO_CODE">
              <Table name="CITIES" schema="BISE1_SALES"/>
              <Join leftKey="REGION_ID" rightKey="ID">
                <Table name="COUNTRIES" schema="BISE1_SALES"/>
                <Table name="REGIONS" schema="BISE1_SALES"/>
              </Join>
            </Join>
            <Level name="Region" table="REGIONS" column="REGION_ID" nameColumn="NAME" type="Numeric" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
            <Level name="Country" table="COUNTRIES" column="ISO_CODE" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
            <Level name="City" table="CITIES" column="ID" nameColumn="NAME" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"/>
          </Hierarchy>
        </Dimension>
        <Measure name="Q Customers" column="CUSTOMER_ID" aggregator="distinct count"/>
      </Cube>
    </Schema>

  4. #4

    Default

    Hi Alex,

    Can you attach the DDL of your tables (Facts table and aggregate table) ?
    As far I can see (I Know well mondrian but i'm not an expert) no errors in your aggregate definition.

    Are you tried with the automatic tables recognition of mondrian with patterns?? Can you see this at following link

    http://mondrian.pentaho.com/document...nizing_default

    Regards
    Enric Biosca
    Project Director at ICA

  5. #5

    Default

    Sure, Enric,
    here is DDL scripts of detailed fact and agg tables:

    FACT TABLE
    Code:
    CREATE TABLE BISE1_SALES.ADDRESSES
    (
      ID                   NUMBER                   NOT NULL,
      CUSTOMER_ID          NUMBER                   NOT NULL,
      PHONE_NUMBER         VARCHAR2(25 BYTE),
      CUST_STREET_ADDRESS  VARCHAR2(40 BYTE),
      CUST_POSTAL_CODE     VARCHAR2(10 BYTE),
      CITY_ID              NUMBER,
      USER_CREATED         VARCHAR2(30 BYTE)        NOT NULL,
      DATE_CREATED         DATE                     NOT NULL,
      USER_MODIFIED        VARCHAR2(30 BYTE)        NOT NULL,
      DATE_MODIFIED        DATE                     NOT NULL,
      COUNTRY_ISO_CODE     CHAR(2 BYTE)
    )
    AGG TABLE
    Code:
    CREATE TABLE BISE1_SALES."agg_customer_country"
    (
      COUNTRY_ISO_CODE  CHAR(2 BYTE),
      Q_CUSTOMER        NUMBER                      NOT NULL,
      FACT_COUNT        NUMBER
    )

    Regarding using automatic agg recongition, i have not yet tried id.
    Last edited by whitish; 08-27-2010 at 08:43 AM.

  6. #6

    Default

    Hi Alex,

    As far as I know the aggregate definition is correct with this tables and schemas.

    I tell you one more time Are you tried with the automatic tables recognition of mondrian with patterns??

    You can activate the option showSQL in mondrian.properties to debug your problem.

    Regards and good luck!
    Enric Biosca
    Project Director at ICA

  7. #7

    Default

    Thnx, Enric,

    well, i ll try automatic agg recognition and debug.

    Best Regards,
    Alex.

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.