Hitachi Vantara Pentaho Community Forums
Results 1 to 1 of 1

Thread: Bug? PeriodsToDate only works on the first VirtualCubeMeasure and YTD does not work o

  1. #1
    Join Date
    Nov 2015
    Posts
    20

    Exclamation Bug? PeriodsToDate only works on the first VirtualCubeMeasure and YTD does not work o

    Hi,

    I have spent a lot of time with this issue. Here I'm presenting the problem as simple as I could after trying to isolate the problem. This problem happens at least on pentaho biserver 6.1.0.1-196 and 7.0.0.0-25.

    I have a schema with 2 cubes and 1 virtual cube that joins the 2 cubes. When I use YTD on one of the cubes the result is as expected, but on the virtualcube when using YTD (shortcut of PeriodsToDate) it results in nothing, but using PeriodsToDate it works but only with the first virtual cube measure. When used with the second virtual measure it seems that it doesn't filter anything.
    Please see the attached images:

    Using the Cube Budget. Result ok:
    Name:  Budget (Budget cube).jpg
Views: 163
Size:  18.1 KB

    Using the VirtualCube Sales with the Budget measure defined before the Invoiced measure. The Budget amount is ok:
    Name:  Budget (Sales cube) - order Budget Invoiced.jpg
Views: 146
Size:  17.0 KB

    Using the VirtualCube Sales with the Invoiced measure defined before the Budget measure. The Budget amount seems not to be filtered and represents the sum of many years in the fact table:
    Name:  Budget (Sales cube) - order Invoiced Budget.jpg
Views: 145
Size:  17.3 KB

    Using the Cube Invoiced. Result ok:
    Name:  Invoiced (Invoiced cube).jpg
Views: 137
Size:  16.9 KB

    Using the VirtualCube Sales with the Budget measure defined before the Invoiced measure, Trying the same but with the Invoiced measure shows the same problem. This time the Invoiced measure doesn't return the expected result because it is defined after the Budget measure in the schema:
    Name:  Invoiced (Sales cube) - order Budget Invoiced.jpg
Views: 147
Size:  17.1 KB

    And here is the schema:
    Code:
    <Schema name="DatamartPostgresql_Test">
      <Dimension type="TimeDimension" name="Date">
        <Hierarchy name="by Months" hasAll="true" primaryKey="datekey">
          <Table name="date" schema="public" alias="datebymonths"/>
          <Level name="Years" column="year_number" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
          <Level name="Months" column="month_number_of_year" type="Numeric" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
          <Level name="Days" column="day_number_of_month" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
        </Hierarchy>
      </Dimension>
    
      <Cube name="Budget">
        <Table name="view_fact_item_budget" schema="public" alias="view_fact_item_budget"/>
        <DimensionUsage name="Budget Date" caption="Budget Date" source="Date" foreignKey="date_key" highCardinality="false" />
    
        <Measure name="Budget Amount" column="sales_amount" datatype="Numeric" aggregator="sum"/>
    
        <CalculatedMember name="Budget Amount YTD" dimension="Measures">
          <!-- The shortcut YTD seems to work on normal cubes <Formula>
            <=!=[=C=D=A=T=A=[sum(PeriodsToDate([Budget Date.by Months].[Years], [Budget Date.by Months].CurrentMember), [Measures].[Budget Amount])]=]=>
          </Formula>-->
          <Formula>
            <=!=[=C=D=A=T=A=[sum(YTD([Budget Date.by Months].CurrentMember), [Measures].[Budget Amount])]=]=>
          </Formula>
        </CalculatedMember>
      </Cube>
      
      <Cube name="Invoiced">
        <Table name="fact_item_sales" schema="public" alias="fact_item_sales"/>
        <DimensionUsage name="Posting Date" caption="Posting Date" source="Date" foreignKey="posting_date_key"/>
        
        <Measure name="Invoiced Amount" column="sales_amount_actual" datatype="Numeric" aggregator="sum"/>
    
        <CalculatedMember name="Invoiced Amount YTD" dimension="Measures">
          <!-- The shortcut YTD seems to work on normal cubes <Formula>
            <=!=[=C=D=A=T=A=[sum(PeriodsToDate([Posting Date.by Months].[Years], [Posting Date.by Months].CurrentMember), [Measures].[Invoiced Amount])]=]=>
          </Formula>-->
          <Formula>
            <=!=[=C=D=A=T=A=[sum(YTD([Posting Date.by Months].CurrentMember), [Measures].[Invoiced Amount])]=]=>
          </Formula>
        </CalculatedMember>
      </Cube>
      
      <VirtualCube name="Sales">
        <CubeUsages>
          <CubeUsage cubeName="Budget" ignoreUnrelatedDimensions="true"/>
          <CubeUsage cubeName="Invoiced" ignoreUnrelatedDimensions="true"/>
        </CubeUsages>
        <VirtualCubeDimension name="Budget Date" cubeName="Budget"/>
        <VirtualCubeDimension name="Posting Date" cubeName="Invoiced"/>
    
       <!-- The [Budget Amount YTD (Sales)] calculated measure works because [Budget Amount], that it depends on is defined the first -->
        <VirtualCubeMeasure name="[Measures].[Budget Amount]" cubeName="Budget" visible="false"/>
        <VirtualCubeMeasure name="[Measures].[Invoiced Amount]" cubeName="Invoiced" visible="false"/>
    
       <!-- The [Invoiced Amount YTD (Sales)] calculated measure works because [Invoiced Amount], that it depends on is defined the first
        <VirtualCubeMeasure name="[Measures].[Invoiced Amount]" cubeName="Invoiced" visible="false"/>
        <VirtualCubeMeasure name="[Measures].[Budget Amount]" cubeName="Budget" visible="false"/>
    -->
    
        <CalculatedMember name="Invoiced Amount (Sales)" dimension="Measures" visible="true">
          <Formula>
            <=!=[=C=D=A=T=A=[ValidMeasure([Measures].[Invoiced Amount])]=]=>
          </Formula>
        </CalculatedMember>
        
        <CalculatedMember name="Invoiced Amount YTD (Sales)" dimension="Measures">
          <!-- YTD does not work on Virtualcubes <Formula>
            <=!=[=C=D=A=T=A=[sum(YTD([Posting Date.by Months].CurrentMember), ValidMeasure([Measures].[Invoiced Amount]))]=]=>
          </Formula>-->
          <Formula>
            <=!=[=C=D=A=T=A=[sum(PeriodsToDate([Posting Date.by Months].[Years], [Posting Date.by Months].CurrentMember), ValidMeasure([Measures].[Invoiced Amount]))]=]=>
          </Formula>
        </CalculatedMember>
    
        <CalculatedMember name="Budget Amount (Sales)" dimension="Measures">
          <Formula>
            <=!=[=C=D=A=T=A=[ValidMeasure([Measures].[Budget Amount])]=]=>
          </Formula>
        </CalculatedMember>
        
        <CalculatedMember name="Budget Amount YTD (Sales)" dimension="Measures">
          <!-- YTD does not work on VirtualCubes <Formula>
            <=!=[=C=D=A=T=A=[sum(YTD([Budget Date.by Months].CurrentMember), ValidMeasure([Measures].[Budget Amount]))]=]=>
          </Formula>-->
          <Formula>
            <=!=[=C=D=A=T=A=[sum(PeriodsToDate([Budget Date.by Months].[Years], [Budget Date.by Months].CurrentMember), ValidMeasure([Measures].[Budget Amount]))]=]=>
          </Formula>
        </CalculatedMember>
        
      </VirtualCube>
    
    </Schema>
    Last edited by oggers; 03-24-2017 at 01:24 PM. Reason: Forgot to mention the biserver version

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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.