Fwd: Issue with result when querying Druid using mondrian

[divye sheth]

Hi,

I have designed a cube with three dimensions and a fact table all belonging
to a single fact table in Druid. I am able to query the data using calcite
and druid's sql functionality. The issue I am facing is this.

The total value for the metric in the cube is *148575*. When I query the
cube using the following MDX:
QUERY 1:
*SELECT ( {[Measures].[Metric1]}) ON COLUMNS, {[Year].Members *
[StoreType].Members} on ROWS from [CUBE1]*

*                    Metric1==== ============== =================2017
ACTIVE STORE             142,745     INACTIVE STORE             5,830*

*Generated SQL:*


*select "StoreCount"."opportunitytype" as "c0", "StoreCount"."year" as
"c2", sum("StoreCount"."storecount") as "m0" from "StoreCount" as
"StoreCount" where  "StoreCount"."year" = '2017' group by
"StoreCount"."opportunitytype", "StoreCount"."year"*

QUERY 2:
*SELECT ( {[Measures].[Metric1]}) ON COLUMNS, {[Year].Members} on ROWS from
[CUBE1]*




*                    Metric1==== =================2017
 142,745Generated SQL:select "StoreCount"."opportunitytype" as "c0",
"StoreCount"."year" as "c2", sum("StoreCount"."storecount") as "m0" from
"StoreCount" as "QuarterlyOpportunityStoreCount" where
"StoreCount"."opportunitytype" = 'ACTIVE STORE' and "StoreCount"."year" =
'2017' group by "StoreCount"."opportunitytype", "StoreCount"."year"*
I get the value from MDX but it is not *148575*. I checked the sql and
found that mondrian generates sql properly but only passes ACTIVE STORE as
a filter value in sql and does not generate an IN query which results into
the value corresponding only to that opportunitytype.

There are no properties of mondrian that are modified.
Calcite version tested on: 1.12.0/1.13.0/1.14.0
Mondrian version: 4.3.0-130
Attached is the xml model

Please help as to what could be the issue.

Thanks
Divye Sheth

<?xml version="1.0" encoding="UTF-8"?>
<Schema caption="bubbles" description="bubbles" metamodelVersion="4.0" name="bubbles">
<PhysicalSchema>
  <Table name="StoreCount"/>
</PhysicalSchema>
<Cube name="CUBE1">
  <Dimensions>
  <Dimension key="StoreSubType" name="StoreTypeNamespaceDimension" table="StoreCount">
  <Attributes>
  <Attribute hasHierarchy="false" name="StoreType">
  <Key>
  <Column name="storetype"/>
  </Key>
  <Name>
  <Column name="storetype"/>
  </Name>
  </Attribute>
  <Attribute hasHierarchy="false" name="StoreSubType">
  <Key>
  <Column name="storetype"/>
  <Column name="storesubtype"/>
  </Key>
  <Name>
  <Column name="storesubtype"/>
  </Name>
  </Attribute>
  </Attributes>
  <Hierarchies>
  <Hierarchy hasAll="false" name="StoreTypeNamespaceHierarchy">
  <Level attribute="StoreType"/>
  <Level attribute="StoreSubType"/>
  </Hierarchy>
  </Hierarchies>
  </Dimension>
  <Dimension key="Date" name="Period" table="StoreCount" type="TIME">
     <Attributes>
        <Attribute hasHierarchy="false" levelType="TimeYears" name="Year" orderByColumn="year">
           <Key>
              <Column name="year" />
           </Key>
            <Name>
              <Column name="year" />
            </Name>
        </Attribute>
        <Attribute hasHierarchy="false" levelType="TimeQuarters" name="Quarter" orderByColumn="quarter">
           <Key>
              <Column name="year" />
              <Column name="quarter" />
           </Key>
           <Name>
              <Column name="quarter" />
           </Name>
        </Attribute>
        <Attribute hasHierarchy="false" levelType="TimeMonths" name="Month" orderByColumn="month_of_year">
           <Key>
              <Column name="year" />
              <Column name="quarter" />
              <Column name="month_of_year" />
           </Key>
           <Name>
              <Column name="month" />
           </Name>
        </Attribute>
        <Attribute hasHierarchy="false" levelType="TimeWeeks" name="Week" orderByColumn="week">
           <Key>
              <Column name="year" />
              <Column name="week" />
           </Key>
           <Name>
              <Column name="week" />
           </Name>
        </Attribute>
        <Attribute hasHierarchy="false" levelType="TimeDays" name="DayOfMonth" orderByColumn="the_date">
           <Key>
              <Column name="the_date" />
           </Key>
           <Name>
              <Column name="dayofmonth" />
           </Name>
        </Attribute>
        <Attribute hasHierarchy="false" name="Date">
           <Key>
              <Column name="the_date" />
           </Key>
        </Attribute>
     </Attributes>
     <Hierarchies>
        <Hierarchy hasAll="false" name="PeriodHierarchy">
           <Level attribute="Year" />
           <Level attribute="Quarter" />
           <Level attribute="Month" />
           <Level attribute="DayOfMonth" />
        </Hierarchy>
        <Hierarchy hasAll="false" name="PeriodWeekHierarchy">
           <Level attribute="Year" />
           <Level attribute="Week" />
           <Level attribute="DayOfMonth" />
        </Hierarchy>
     </Hierarchies>
  </Dimension>
  </Dimensions>
  <MeasureGroups>
    <MeasureGroup name="StoreCount" table="StoreCount">
      <Measures>
        <Measure aggregator="sum" column="storecount" name="Metric1"/>
      </Measures>
    <DimensionLinks>
      <FactLink dimension="StoreTypeNamespaceDimension"/>
      <FactLink dimension="Period"/>
    </DimensionLinks>
    </MeasureGroup>
  </MeasureGroups>
<CalculatedMembers/>
</Cube>
</Schema>