US and Worldwide: +1 (866) 660-7555
Results 1 to 2 of 2

Thread: Agg table do not match( I have used many ways to solve it but still do not work)

  1. #1

    Question Agg table do not match( I have used many ways to solve it but still do not work)

    Hi, all.
    I use saiku webapp plugin on BI server.
    I have a problem, my agg table do not match!!!!
    I follow different ways to configure my environment for using agg table.
    I have enabled agg table in mondrian.properties( ..\system\mondrian\ and ..\tomcat\webapps\saiku-webapp-2.2\WEB-INF\classes\)

    # Allow the use of aggregates
    mondrian.rolap.aggregates.Use=true
    mondrian.rolap.aggregates.Read=true

    I also copy this mondrian.properties into JVM file( I think its useless).

    My fact_table:
    CREATE TABLE fact_orders
    (
    customer_key integer NOT NULL DEFAULT 0,
    website_key integer NOT NULL DEFAULT 0,
    dvd_release_key integer NOT NULL DEFAULT 0,
    promotion_key integer NOT NULL DEFAULT 0,
    local_order_date_key integer NOT NULL DEFAULT 29991231,
    local_order_time_key integer NOT NULL DEFAULT 0,
    demography_key integer NOT NULL DEFAULT 0,
    customer_order_id integer NOT NULL DEFAULT 0,
    from_warehouse_key integer NOT NULL DEFAULT 0,
    to_warehouse_key integer NOT NULL DEFAULT 0,
    utc_order_date_key integer NOT NULL DEFAULT 29991231,
    utc_order_time_key integer NOT NULL DEFAULT 0,
    shipping_date_key integer NOT NULL DEFAULT 29991231,
    delivery_due_date_key integer NOT NULL DEFAULT 29991231,
    delivery_date_key integer NOT NULL DEFAULT 29991231,
    return_due_date_key integer NOT NULL DEFAULT 29991231,
    return_date_key integer NOT NULL DEFAULT 29991231,
    order_status_key integer NOT NULL DEFAULT 0,
    revenue numeric(6,2) DEFAULT NULL::numeric,
    quantity integer,
    shipping_cost numeric(6,2) DEFAULT NULL::numeric,
    order_to_delivery_lag integer NOT NULL DEFAULT 0,
    rental_duration integer NOT NULL DEFAULT 0,
    CONSTRAINT fact_orders_pkey PRIMARY KEY (customer_key , dvd_release_key , local_order_date_key , customer_order_id )
    )

    my agg table:
    CREATE TABLE agg_sales_fact_orders
    (
    dim_date_year text,
    dim_date_quarter text,
    dim_date_month text,
    dim_date_day integer,
    fact_orders_revenue double precision DEFAULT NULL::numeric,
    fact_orders_quantity integer,
    fact_orders_fact_count integer
    )


    My schema for agg table:

    <Cube name="wcm_orders" visible="true" description="World Class Movies Sales" cache="true" enabled="true">
    <Table name="fact_orders" schema="public">
    <AggName name="agg_sales_fact_orders" ignorecase="true">
    <AggFactCount column="fact_orders_fact_count">
    </AggFactCount>
    <AggMeasure column="fact_orders_Revenue" name="[Measures].[Revenue]">
    </AggMeasure>
    <AggMeasure column="fact_orders_Quantity" name="[Measures].[Quantity]">
    </AggMeasure>
    <AggLevel column="dim_date_Year" name="[Local Order Date].[Year]">
    </AggLevel>
    <AggLevel column="dim_date_Quarter" name="[Local Order Date].[Quarter]">
    </AggLevel>
    <AggLevel column="dim_date_Month" name="[Local Order Date].[Month]">
    </AggLevel>
    <AggLevel column="dim_date_Day" name="[Local Order Date].[Day]">
    </AggLevel>
    </AggName>
    </Table>


    The mondrian.log shows:
    I think the important parts are:

    2012-05-02 11:26:22,636 DEBUG [mondrian.rolap.FastBatchingCellReader] FastBatchingCellReader: bitkey=0x0000000000000000000000000000000000000000000000000000000000001111
    Year (0): "dim_date"."year4"
    Quarter (1): "dim_date"."quarter_number"
    Month (2): "dim_date"."month_number"
    Day (3): "dim_date"."day_in_month"


    and

    2012-05-02 11:26:22,710 DEBUG [mondrian.rolap.agg.AggregationManager] NO MATCH : fact_orders
    Foreign columns bit key=0x0000000000000000000000000000000000000000000000000000000000001111
    Measure bit key= 0x0000000000000000000000000000000000000000000000000010000000000000
    Agg Stars=[
    AggStar:agg_sales_fact_orders
    bk=0x0000000000000000000000000000000000000000000000000110000000000000
    fbk=0x0000000000000000000000000000000000000000000000000000000000000000
    mbk=0x0000000000000000000000000000000000000000000000000110000000000000
    has foreign key=false
    Revenue (13): sum("agg_sales_fact_orders"."fact_orders_revenue")
    Quantity (14): sum("agg_sales_fact_orders"."fact_orders_quantity")
    Table:
    name=agg_sales_fact_orders
    relation=public.agg_sales_fact_orders
    numberofrows=2972
    FactCount:
    Fact Count (-1): "agg_sales_fact_orders"."fact_orders_fact_count"
    Measures:
    Revenue (13): sum("agg_sales_fact_orders"."fact_orders_revenue")
    Quantity (14): sum("agg_sales_fact_orders"."fact_orders_quantity")
    Levels:

    ]

    The entail log is:

    2012-05-02 11:26:15,196 DEBUG [mondrian.olap.Util] Util.lookupCompound: parent.name=wcm_orders, category=member, names=[Measures].[Revenue]
    2012-05-02 11:26:15,196 DEBUG [mondrian.rolap.RolapCube] RolapCube.lookupChild: name=wcm_orders, childname=[Measures] returning elementname=Measures
    2012-05-02 11:26:15,197 DEBUG [mondrian.rolap.RolapHierarchy] HierarchyBase.lookupChild: name=Measures, childname=[Revenue] returning elementname=Revenue
    2012-05-02 11:26:15,197 DEBUG [mondrian.olap.Util] Util.lookupCompound: found child.name=Revenue, child.class=mondrian.rolap.RolapBaseCubeMeasure
    2012-05-02 11:26:22,466 DEBUG [mondrian.server.monitor] StatementStartEvent(51)
    2012-05-02 11:26:22,466 DEBUG [mondrian.rolap.RolapConnection]
    SELECT
    NON EMPTY {Hierarchize({[Measures].[Revenue]})} ON COLUMNS,
    NON EMPTY {Hierarchize({[Local Order Date.Months hierarchy].[Day].Members})} ON ROWS
    FROM [wcm_orders]
    2012-05-02 11:26:22,467 DEBUG [mondrian.olap.Util] Util.lookupCompound: parent.name=wcm_orders, category=unknown, names=[Measures].[Revenue]
    2012-05-02 11:26:22,468 DEBUG [mondrian.rolap.RolapCube] RolapCube.lookupChild: name=wcm_orders, childname=[Measures] returning elementname=Measures
    2012-05-02 11:26:22,468 DEBUG [mondrian.rolap.RolapHierarchy] HierarchyBase.lookupChild: name=Measures, childname=[Revenue] returning elementname=Revenue
    2012-05-02 11:26:22,468 DEBUG [mondrian.olap.Util] Util.lookupCompound: found child.name=Revenue, child.class=mondrian.rolap.RolapBaseCubeMeasure
    2012-05-02 11:26:22,469 DEBUG [mondrian.olap.Util] Util.lookupCompound: parent.name=wcm_orders, category=unknown, names=[Local Order Date.Months hierarchy].[Day]
    2012-05-02 11:26:22,469 DEBUG [mondrian.rolap.RolapHierarchy] HierarchyBase.lookupChild: name=Measures, childname=[Local Order Date.Months hierarchy] returning null
    2012-05-02 11:26:22,469 DEBUG [mondrian.rolap.RolapCube] RolapCube.lookupChild: name=wcm_orders, childname=[Local Order Date.Months hierarchy], status=hierUsage == shared returning elementname=Local Order Date.Months hierarchy
    2012-05-02 11:26:22,469 DEBUG [mondrian.rolap.RolapHierarchy] HierarchyBase.lookupChild: name=Local Order Date.Months hierarchy, childname=[Day] returning elementname=Day
    2012-05-02 11:26:22,469 DEBUG [mondrian.olap.Util] Util.lookupCompound: found child.name=Day, child.class=mondrian.rolap.RolapCubeLevel
    2012-05-02 11:26:22,470 DEBUG [mondrian.rolap.RolapCube] RolapCube.getUsages: name=Measures
    2012-05-02 11:26:22,471 DEBUG [mondrian.rolap.RolapConnection] select NON EMPTY {Hierarchize({[Measures].[Revenue]})} ON COLUMNS,
    NON EMPTY {Hierarchize({[Local Order Date.Months hierarchy].[Day].Members})} ON ROWS
    from [wcm_orders]
    2012-05-02 11:26:22,472 DEBUG [mondrian.mdx] 852: select NON EMPTY {Hierarchize({[Measures].[Revenue]})} ON COLUMNS,
    NON EMPTY {Hierarchize({[Local Order Date.Months hierarchy].[Day].Members})} ON ROWS
    from [wcm_orders]
    2012-05-02 11:26:22,473 DEBUG [mondrian.server.monitor] ExecutionStartEvent(852)
    2012-05-02 11:26:22,473 DEBUG [mondrian.rolap.RolapCube] RolapCube.getUsages: name=Measures
    2012-05-02 11:26:22,474 DEBUG [mondrian.sql] 22: SqlTupleReader.readTuples [[Date.Months hierarchy].[Day]]: executing sql [
    select
    "dim_date"."year4" as "c0",
    "dim_date"."quarter_number" as "c1",
    "dim_date"."quarter_name" as "c2",
    "dim_date"."month_number" as "c3",
    "dim_date"."month_abbreviation" as "c4",
    "dim_date"."day_in_month" as "c5"
    from
    "public"."dim_date" as "dim_date"
    group by
    "dim_date"."year4",
    "dim_date"."quarter_number",
    "dim_date"."quarter_name",
    "dim_date"."month_number",
    "dim_date"."month_abbreviation",
    "dim_date"."day_in_month"
    order by
    "dim_date"."year4" ASC NULLS LAST,
    "dim_date"."quarter_number" ASC NULLS LAST,
    "dim_date"."month_number" ASC NULLS LAST,
    "dim_date"."day_in_month" ASC NULLS LAST]
    2012-05-02 11:26:22,474 DEBUG [mondrian.server.monitor] SqlStatementStartEvent(22)
    2012-05-02 11:26:22,528 DEBUG [mondrian.server.monitor] SqlStatementExecuteEvent(22)
    2012-05-02 11:26:22,532 DEBUG [mondrian.sql] 22: , exec 53 ms
    2012-05-02 11:26:22,532 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Date.Months hierarchy].[Day]]: executing sql [select
    "dim_date"."year4" as "c0",
    "dim_date"."quarter_number" as "c1",
    "dim_date"."quarter_name" as "c2",
    "dim_date"."month_number" as "c3",
    "dim_date"."month_abbreviation" as "c4",
    "dim_date"."day_in_month" as "c5"
    from
    "public"."dim_date" as "dim_date"
    group by
    "dim_date"."year4",
    "dim_date"."quarter_number",
    "dim_date"."quarter_name",
    "dim_date"."month_number",
    "dim_date"."month_abbreviation",
    "dim_date"."day_in_month"
    order by
    "dim_date"."year4" ASC NULLS LAST,
    "dim_date"."quarter_number" ASC NULLS LAST,
    "dim_date"."month_number" ASC NULLS LAST,
    "dim_date"."day_in_month" ASC NULLS LAST], exec 53 ms
    2012-05-02 11:26:22,590 DEBUG [mondrian.sql] 22: , exec+fetch 116 ms, 3660 rows
    2012-05-02 11:26:22,590 DEBUG [mondrian.rolap.RolapUtil] SqlTupleReader.readTuples [[Date.Months hierarchy].[Day]]: done executing sql [select
    "dim_date"."year4" as "c0",
    "dim_date"."quarter_number" as "c1",
    "dim_date"."quarter_name" as "c2",
    "dim_date"."month_number" as "c3",
    "dim_date"."month_abbreviation" as "c4",
    "dim_date"."day_in_month" as "c5"
    from
    "public"."dim_date" as "dim_date"
    group by
    "dim_date"."year4",
    "dim_date"."quarter_number",
    "dim_date"."quarter_name",
    "dim_date"."month_number",
    "dim_date"."month_abbreviation",
    "dim_date"."day_in_month"
    order by
    "dim_date"."year4" ASC NULLS LAST,
    "dim_date"."quarter_number" ASC NULLS LAST,
    "dim_date"."month_number" ASC NULLS LAST,
    "dim_date"."day_in_month" ASC NULLS LAST], exec+fetch 116 ms, 3660 rows
    2012-05-02 11:26:22,591 DEBUG [mondrian.server.monitor] SqlStatementInfo{sqlStatementId=22}
    2012-05-02 11:26:22,591 DEBUG [mondrian.server.monitor] SqlStatementEndEvent(22)
    2012-05-02 11:26:22,636 DEBUG [mondrian.rolap.FastBatchingCellReader] FastBatchingCellReader: bitkey=0x0000000000000000000000000000000000000000000000000000000000001111
    Year (0): "dim_date"."year4"
    Quarter (1): "dim_date"."quarter_number"
    Month (2): "dim_date"."month_number"
    Day (3): "dim_date"."day_in_month"

    2012-05-02 11:26:22,637 DEBUG [mondrian.rolap.RolapCube] RolapCube.getUsages: name=Measures
    2012-05-02 11:26:22,704 DEBUG [mondrian.sql] 23: RolapStar.Column.getCardinality: executing sql [
    select
    count(distinct "dim_date"."day_in_month") as "c0"
    from
    "public"."dim_date" as "dim_date"]
    2012-05-02 11:26:22,705 DEBUG [mondrian.server.monitor] ExecutionPhaseEvent(852, 0)
    2012-05-02 11:26:22,705 DEBUG [mondrian.server.monitor] SqlStatementStartEvent(23)
    2012-05-02 11:26:22,708 DEBUG [mondrian.server.monitor] SqlStatementExecuteEvent(23)
    2012-05-02 11:26:22,708 DEBUG [mondrian.sql] 23: , exec 4 ms
    2012-05-02 11:26:22,709 DEBUG [mondrian.rolap.RolapUtil] RolapStar.Column.getCardinality: executing sql [select
    count(distinct "dim_date"."day_in_month") as "c0"
    from
    "public"."dim_date" as "dim_date"], exec 4 ms
    2012-05-02 11:26:22,709 DEBUG [mondrian.sql] 23: , exec+fetch 5 ms, 1 rows
    2012-05-02 11:26:22,710 DEBUG [mondrian.rolap.RolapUtil] RolapStar.Column.getCardinality: done executing sql [select
    count(distinct "dim_date"."day_in_month") as "c0"
    from
    "public"."dim_date" as "dim_date"], exec+fetch 5 ms, 1 rows
    2012-05-02 11:26:22,710 DEBUG [mondrian.server.monitor] SqlStatementInfo{sqlStatementId=23}
    2012-05-02 11:26:22,710 DEBUG [mondrian.server.monitor] SqlStatementEndEvent(23)
    2012-05-02 11:26:22,710 DEBUG [mondrian.rolap.agg.AggregationManager] NO MATCH : fact_orders
    Foreign columns bit key=0x0000000000000000000000000000000000000000000000000000000000001111
    Measure bit key= 0x0000000000000000000000000000000000000000000000000010000000000000
    Agg Stars=[
    AggStar:agg_sales_fact_orders
    bk=0x0000000000000000000000000000000000000000000000000110000000000000
    fbk=0x0000000000000000000000000000000000000000000000000000000000000000
    mbk=0x0000000000000000000000000000000000000000000000000110000000000000
    has foreign key=false
    Revenue (13): sum("agg_sales_fact_orders"."fact_orders_revenue")
    Quantity (14): sum("agg_sales_fact_orders"."fact_orders_quantity")
    Table:
    name=agg_sales_fact_orders
    relation=public.agg_sales_fact_orders
    numberofrows=2972
    FactCount:
    Fact Count (-1): "agg_sales_fact_orders"."fact_orders_fact_count"
    Measures:
    Revenue (13): sum("agg_sales_fact_orders"."fact_orders_revenue")
    Quantity (14): sum("agg_sales_fact_orders"."fact_orders_quantity")
    Levels:

    ]
    2012-05-02 11:26:22,710 DEBUG [mondrian.rolap.agg.AggregationManager] generateSqlQuery: sql=select
    "dim_date"."year4" as "c0",
    "dim_date"."quarter_number" as "c1",
    "dim_date"."month_number" as "c2",
    "dim_date"."day_in_month" as "c3",
    sum("fact_orders"."revenue") as "m0"
    from
    "public"."dim_date" as "dim_date",
    "public"."fact_orders" as "fact_orders"
    where
    "fact_orders"."local_order_date_key" = "dim_date"."date_key"
    group by
    "dim_date"."year4",
    "dim_date"."quarter_number",
    "dim_date"."month_number",
    "dim_date"."day_in_month"
    2012-05-02 11:26:22,711 DEBUG [mondrian.sql] 24: Segment.load: executing sql [
    select
    "dim_date"."year4" as "c0",
    "dim_date"."quarter_number" as "c1",
    "dim_date"."month_number" as "c2",
    "dim_date"."day_in_month" as "c3",
    sum("fact_orders"."revenue") as "m0"
    from
    "public"."dim_date" as "dim_date",
    "public"."fact_orders" as "fact_orders"
    where
    "fact_orders"."local_order_date_key" = "dim_date"."date_key"
    group by
    "dim_date"."year4",
    "dim_date"."quarter_number",
    "dim_date"."month_number",
    "dim_date"."day_in_month"]
    2012-05-02 11:26:22,711 DEBUG [mondrian.server.monitor] SqlStatementStartEvent(24)
    2012-05-02 11:26:23,434 DEBUG [mondrian.server.monitor] SqlStatementExecuteEvent(24)
    2012-05-02 11:26:23,438 DEBUG [mondrian.sql] 24: , exec 723 ms
    2012-05-02 11:26:23,439 DEBUG [mondrian.rolap.RolapUtil] Segment.load: executing sql [select
    "dim_date"."year4" as "c0",
    "dim_date"."quarter_number" as "c1",
    "dim_date"."month_number" as "c2",
    "dim_date"."day_in_month" as "c3",
    sum("fact_orders"."revenue") as "m0"
    from
    "public"."dim_date" as "dim_date",
    "public"."fact_orders" as "fact_orders"
    where
    "fact_orders"."local_order_date_key" = "dim_date"."date_key"
    group by
    "dim_date"."year4",
    "dim_date"."quarter_number",
    "dim_date"."month_number",
    "dim_date"."day_in_month"], exec 723 ms
    2012-05-02 11:26:23,498 DEBUG [mondrian.sql] 24: , exec+fetch 787 ms, 2972 rows
    2012-05-02 11:26:23,498 DEBUG [mondrian.rolap.RolapUtil] Segment.load: done executing sql [select
    "dim_date"."year4" as "c0",
    "dim_date"."quarter_number" as "c1",
    "dim_date"."month_number" as "c2",
    "dim_date"."day_in_month" as "c3",
    sum("fact_orders"."revenue") as "m0"
    from
    "public"."dim_date" as "dim_date",
    "public"."fact_orders" as "fact_orders"
    where
    "fact_orders"."local_order_date_key" = "dim_date"."date_key"
    group by
    "dim_date"."year4",
    "dim_date"."quarter_number",
    "dim_date"."month_number",
    "dim_date"."day_in_month"], exec+fetch 787 ms, 2972 rows
    2012-05-02 11:26:23,498 DEBUG [mondrian.server.monitor] SqlStatementInfo{sqlStatementId=24}
    2012-05-02 11:26:23,498 DEBUG [mondrian.rolap.FastBatchingCellReader$Batch] Batch.loadAggregation (millis) 794
    2012-05-02 11:26:23,499 DEBUG [mondrian.server.monitor] SqlStatementEndEvent(24)
    2012-05-02 11:26:23,499 DEBUG [mondrian.rolap.FastBatchingCellReader] loadAggregation (millis): 795
    2012-05-02 11:26:23,546 DEBUG [mondrian.olap.ResultBase] RolapResult<init>: FREE_MEMORY: 149640kb 57.49%
    2012-05-02 11:26:23,556 DEBUG [mondrian.server.monitor] ExecutionEndEvent(852)
    2012-05-02 11:26:23,556 DEBUG [mondrian.mdx] 852: exec: 1083 ms

    I have spent 4 days on this problem. I am very appreciate your help!!!!
    Thank you so much!!!

    Johnson Zeng

  2. #2

    Default

    Hi,

    Could you please also post the "dimension section from the mondrian schema", for the date dimension??? Also, which version of Mondrian are you using????

    Thanks,
    Sujen

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •