Hitachi Vantara Pentaho Community Forums
Results 1 to 1 of 1

Thread: lost time dimension with weekly aggregate table doesn't summarize correctly

  1. #1
    Join Date
    May 2011
    Posts
    15

    Post lost time dimension with weekly aggregate table doesn't summarize correctly

    Hi,

    I am facing an issue with mondrian aggregate tables where I have created aggregate tables for levels like quarter, month, week, day for lost time dimension. Once I add a level as week_of_month to time dimension and use a aggregate table for weekly summary the result from any of the aggregate table(s) for levels higher than week_of_month are not displayed correctly. In other words if I do not use week_of_month as a level in my time dimension and do not use aggregate table for week_of_month, the summarization for each level i.e. day, month, quarter, year etc is done correctly picking up values from corosponding aggregate table.

    I have created a sample schema having following:
    (file: fact_prod_sales.sql.txt)
    --------------------------
    dimension tables:
    1) dim_time
    2) dim_product
    3) dim_customer

    fact table:
    1)fact_prod_sales

    aggregate tables:
    1) agg_1_01daily_fact_prod_sales
    2) agg_1_01weekly_fact_prod_sales
    3) agg_1_01monthly_fact_prod_sales
    4) agg_1_01quarterly_fact_prod_sales

    mondrian(attached file: week_test.xml)
    ---------------------------------------
    mondrian schema: sch_week_test
    mondrian cube: cube_week_test

    MDX queries and results:
    -------------------------------
    select
    {[Measures].[sum_unit],[Measures].[sum_dollar]}
    on columns
    from [cube_week_test]
    where (
    [Product].[shirt],
    [Customer].[frank],
    [Time].[2010].[4].[10].[1].[3].[7])
    --returns:
    --sum_unit=10
    --sum_dollar=20

    select
    {[Measures].[sum_unit],[Measures].[sum_dollar]}
    on columns
    from [cube_week_test]
    where (
    [Product].[shirt],
    [Customer].[frank],
    [Time].[2010].[4].[10].[1].[3])

    --returns:
    --sum_unit=11
    --sum_dollar=20

    select
    {[Measures].[sum_unit],[Measures].[sum_dollar]}
    on columns
    from [cube_week_test]
    where (
    [Product].[shirt],
    [Customer].[frank],
    [Time].[2010].[4].[10].[1])

    --returns:
    --sum_unit=21
    --sum_dollar=20

    select
    {[Measures].[sum_unit],[Measures].[sum_dollar]}
    on columns
    from [cube_week_test]
    where (
    [Product].[shirt],
    [Customer].[frank],
    [Time].[2010].[4].[10])
    --returns:
    --sum_unit=21
    --sum_dollar=20

    --should return:
    --sum_unit=31
    --sum_dollar=20

    Please help if someone has faced an issue or similar issue with agregate table on week level in time dimension.
    Attached Files Attached Files

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.