MDX TopCount with specific where clause doesnt work

sanjayraja commented 9 years ago

Version using : 3.7.0.0-752 When using TopCount with MDX Query as

With 
Set [Set1] As TopCount([Company].Children,5,[Measures].[Premium]) 
Select { [Measures].[Premium] } on Columns
,  [Set1] on Rows
 From [insuranceCube]
 Where { {[Date].[Month].[July 2014],[Date].[Month].[August 2014],[Date].[Month].[September 2014]}      }

The result set that is retrieved is invalid as the SQL Statments that are run against the database are

 select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" = 'September 2014' group by "insurance"."Company", "dim_date"."Month"

and then

select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."Company" in ('Auto-Owners', 'Donegal', 'Economical', 'Intact', 'Wynward') and "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" in ('August 2014', 'July 2014') group by "insurance"."Company", "dim_date"."Month"

If you see the Month in Where clause 3 months are present in MDX but only the last month is used to find the TopCount and while retrieving the values, it is being retrieving for the first two months,

This issue was also logged here @ http://jira.pentaho.com/browse/MONDRIAN-814 and was resolved stating fixed in version 3.7. Please help resolve this.

lucboudreau commented 9 years ago

Hi,

The comment in MONDRIAN-814 does mention 3.7, but that's the Pentaho Platform version, which corresponds to Mondrian version 3.2.1.

Concerning the bug itself. I ran a couple tests on my end. This is what I did:

public void testSomething() throws Exception {
    final String mdx =
        "With \n"
        + "Set [Set1] As 'TopCount([Store].[Store City].Members,5,[Measures].[Unit Sales])' \n"
        + "Select { [Measures].[Unit Sales] } on 0,\n"
        + "[Set1] on 1\n"
        + " From [Sales]\n"
        + " Where { [Time].[Month].[2],[Time].[Month].[5]} \n";
    assertQueryReturns(
        mdx,
        "Axis #0:\n"
        + "{[Time].[1997].[Q1].[2]}\n"
        + "{[Time].[1997].[Q2].[5]}\n"
        + "Axis #1:\n"
        + "{[Measures].[Unit Sales]}\n"
        + "Axis #2:\n"
        + "{[Store].[USA].[OR].[Salem]}\n"
        + "{[Store].[USA].[WA].[Tacoma]}\n"
        + "{[Store].[USA].[OR].[Portland]}\n"
        + "{[Store].[USA].[CA].[Los Angeles]}\n"
        + "{[Store].[USA].[WA].[Seattle]}\n"
        + "Row #0: 5,878\n"
        + "Row #1: 5,136\n"
        + "Row #2: 4,846\n"
        + "Row #3: 4,249\n"
        + "Row #4: 4,141\n");
}

It returns the correct numbers. My guess is that in your case, one of the months has no data. Can you copy your full SQL logs? You can also try running the queries and inspect what came back.

sanjayraja commented 9 years ago

Hi Luc, Thanks for the prompt response and apologies for the version confusion.

We are using Mondrian with MS SQL Server and with SSASnaming set to true. On SSAS the same Dataset gives the right results.

So please let me know if there something wrong in what we have done wrt to Mondrian setup/config

The MDX Query

With 
Set [Set1] As TopCount([Company].Children,5,[Measures].[Premium]) 
Select { [Measures].[Premium] } on Columns,
[Set1] on Rows
From [insuranceCube]
Where { {[Date].[Month].[July 2014],[Date].[Month].[August 2014],[Date].[Month].[September 2014]}  }

The 1st SQL from the trace file - This returns insurance.company values only for September 2014 ?

select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" = 'September 2014' group by "insurance"."Company", "dim_date"."Month"

The 2nd SQL from the trace file - This returns values only for July 2014 & August 2014\ & uses Company names the Top5 based on order from the previous query in the IN clause

select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."Company" in ('Auto-Owners', 'Donegal', 'Economical', 'Intact', 'Wynward') and "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" in ('August 2014', 'July 2014') group by "insurance"."Company", "dim_date"."Month"

Here is the complete Trace XML Output from SQL

    <?xml version="1.0" encoding="utf-16"?>
<TraceData xmlns="http://tempuri.org/TracePersistence.xsd">
  <Header>
    <TraceProvider name="Microsoft SQL Server" MajorVersion="11" MinorVersion="0" BuildNumber="2218" />
    <ServerInformation name="(local)" />
    <ProfilerUI>
      <OrderedColumns>
        <ID>27</ID>
        <ID>1</ID>
        <ID>10</ID>
        <ID>6</ID>
        <ID>11</ID>
        <ID>18</ID>
        <ID>16</ID>
        <ID>17</ID>
        <ID>13</ID>
        <ID>9</ID>
        <ID>12</ID>
        <ID>14</ID>
        <ID>15</ID>
      </OrderedColumns>
      <TracedEvents>
        <Event id="12">
          <EventColumn id="1" />
          <EventColumn id="9" />
          <EventColumn id="11" />
          <EventColumn id="6" />
          <EventColumn id="10" />
          <EventColumn id="12" />
          <EventColumn id="13" />
          <EventColumn id="14" />
          <EventColumn id="15" />
          <EventColumn id="16" />
          <EventColumn id="17" />
          <EventColumn id="18" />
        </Event>
        <Event id="13">
          <EventColumn id="1" />
          <EventColumn id="9" />
          <EventColumn id="11" />
          <EventColumn id="6" />
          <EventColumn id="10" />
          <EventColumn id="12" />
          <EventColumn id="14" />
        </Event>
      </TracedEvents>
    </ProfilerUI>
  </Header>
  <Events>
    <Event id="65529" name="Trace Pause">
      <Column id="14" name="StartTime">2014-09-16T22:08:48.323+05:30</Column>
    </Event>
    <Event id="65534" name="Trace Start">
      <Column id="14" name="StartTime">2014-09-16T22:09:14.783+05:30</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData"> set transaction isolation level  read committed  set implicit_transactions off </Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:09:43.94+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData"> set transaction isolation level  read committed  set implicit_transactions off </Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">112</Column>
      <Column id="14" name="StartTime">2014-09-16T22:09:43.94+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:09:43.94+05:30</Column>
      <Column id="16" name="Reads">0</Column>
      <Column id="18" name="CPU">0</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select version()</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:09:43.993+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select version()</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">178</Column>
      <Column id="14" name="StartTime">2014-09-16T22:09:43.993+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:09:43.993+05:30</Column>
      <Column id="16" name="Reads">0</Column>
      <Column id="18" name="CPU">0</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" where UPPER("dim_date"."Month") = UPPER('July 2014') group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.41+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" where UPPER("dim_date"."Month") = UPPER('July 2014') group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">3472</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.41+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.413+05:30</Column>
      <Column id="16" name="Reads">142</Column>
      <Column id="18" name="CPU">0</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" where UPPER("dim_date"."Month") = UPPER('August 2014') group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.43+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" where UPPER("dim_date"."Month") = UPPER('August 2014') group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">3774</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.43+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.433+05:30</Column>
      <Column id="16" name="Reads">142</Column>
      <Column id="18" name="CPU">15</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" where UPPER("dim_date"."Month") = UPPER('September 2014') group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.437+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" where UPPER("dim_date"."Month") = UPPER('September 2014') group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">3160</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.437+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.44+05:30</Column>
      <Column id="16" name="Reads">142</Column>
      <Column id="18" name="CPU">0</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select "insurance"."Company" as "c0" from "insurance" as "insurance" group by "insurance"."Company" order by CASE WHEN "insurance"."Company" IS NULL THEN 1 ELSE 0 END, "insurance"."Company" ASC</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.53+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select "insurance"."Company" as "c0" from "insurance" as "insurance" group by "insurance"."Company" order by CASE WHEN "insurance"."Company" IS NULL THEN 1 ELSE 0 END, "insurance"."Company" ASC</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">62532</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.53+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.593+05:30</Column>
      <Column id="16" name="Reads">1314</Column>
      <Column id="18" name="CPU">63</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select count(distinct "Company") from "insurance"</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.637+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select count(distinct "Company") from "insurance"</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">53437</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.637+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.69+05:30</Column>
      <Column id="16" name="Reads">1314</Column>
      <Column id="18" name="CPU">47</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select count(distinct "Month") from "dim_date"</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.693+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select count(distinct "Month") from "dim_date"</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">6037</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.693+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.7+05:30</Column>
      <Column id="16" name="Reads">142</Column>
      <Column id="18" name="CPU">15</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" = 'September 2014' group by "insurance"."Company", "dim_date"."Month"</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.773+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" = 'September 2014' group by "insurance"."Company", "dim_date"."Month"</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">17564</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.773+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.79+05:30</Column>
      <Column id="16" name="Reads">1460</Column>
      <Column id="18" name="CPU">16</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.83+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select "dim_date"."Month" as "c0", "dim_date"."Month" as "c1" from "dim_date" as "dim_date" group by "dim_date"."Month" order by CASE WHEN "dim_date"."Month" IS NULL THEN 1 ELSE 0 END, "dim_date"."Month" ASC</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">5918</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.83+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.837+05:30</Column>
      <Column id="16" name="Reads">142</Column>
      <Column id="18" name="CPU">0</Column>
    </Event>
    <Event id="13" name="SQL:BatchStarting">
      <Column id="1" name="TextData">select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."Company" in ('Auto-Owners', 'Donegal', 'Economical', 'Intact', 'Wynward') and "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" in ('August 2014', 'July 2014') group by "insurance"."Company", "dim_date"."Month"</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.857+05:30</Column>
    </Event>
    <Event id="12" name="SQL:BatchCompleted">
      <Column id="1" name="TextData">select "insurance"."Company" as "c0", "dim_date"."Month" as "c1", sum("insurance"."Premium") as "m0" from "insurance" as "insurance", "dim_date" as "dim_date" where "insurance"."Company" in ('Auto-Owners', 'Donegal', 'Economical', 'Intact', 'Wynward') and "insurance"."EffectiveDate_date_dim" = "dim_date"."date_key" and "dim_date"."Month" in ('August 2014', 'July 2014') group by "insurance"."Company", "dim_date"."Month"</Column>
      <Column id="17" name="Writes">0</Column>
      <Column id="10" name="ApplicationName">Microsoft SQL Server JDBC Driver</Column>
      <Column id="11" name="LoginName">infoveave</Column>
      <Column id="12" name="SPID">60</Column>
      <Column id="13" name="Duration">54002</Column>
      <Column id="14" name="StartTime">2014-09-16T22:10:17.857+05:30</Column>
      <Column id="15" name="EndTime">2014-09-16T22:10:17.91+05:30</Column>
      <Column id="16" name="Reads">1460</Column>
      <Column id="18" name="CPU">47</Column>
    </Event>
    <Event id="65529" name="Trace Pause">
      <Column id="14" name="StartTime">2014-09-16T22:10:33.39+05:30</Column>
    </Event>
  </Events>
</TraceData>

sanjayraja commented 9 years ago

Hi Luc,

The following query returns the Top5 Companies (Premium wise) for each the months of July,August & September 2014

With Set [Set0] As {[Date].[Month].[July 2014],[Date].[Month].[August 2014],[Date].[Month].[September 2014]} Set [Set1] As Generate([Set0],NonEmptyCrossjoin([Set0].Current,TopCount([Company].Children,5,[Measures].[Premium]))) Select { [Measures].[Premium] } on Columns,  [Set1] on Rows From [insuranceCube]

And the result is as expected and correct.

The query below is supposed to return the Top5 Companies with highest aggregated Premium across July,August & September 2014

With 
Set [Set1] As TopCount([Company].Children,5,[Measures].[Premium]) 
Select { [Measures].[Premium] } on Columns,
[Set1] on Rows
From [insuranceCube]
Where { {[Date].[Month].[July 2014],[Date].[Month].[August 2014],[Date].[Month].[September 2014]}

Here it takes just the last member instead of all of them, and this applicable not just for Dates.

I have tested this with MYSQL,SQL both with the XmlaOlap4jDriver and MondrianOlap4jDriver. And also analysed the queries both on SQL & MYSQL (percona) (assuming that there could be a driver issue). Native TopCount is enabled.

I am more and more inclined to mark this as a bug ? Can someone please help !!

Thanks Sanjay

mkambol commented 9 years ago

I think this is a bug, too. The problem appears to be that Mondrian evaluates the named set prior to executing the slicer, which means the compound slicer handling has not yet happened at the point TopCount is evaluated, and the context only contains one of the date members.

Luc, I can reproduce the error using your test by disabling native topcount. Even though Sanjay has native topcount enabled, the fact that the MDX has [Company].Children in place prevents native topcount from kicking in (CrossJoinArgFactory doesn't know what to do with Dimension.children).

sanjayraja commented 9 years ago

Hi,

Is there a workaround in the interim ? I have tried the above in all possible combinations (native on/off), (members instead of children), switched off SSasnaming and used the default, but I am unable to get the desired result. My mondrian.properties is in webinf/classes. So if there is better way to get the desired result please do let me know.

Thanks Sanjay

mkambol commented 9 years ago

Have you tried inlining the named set directly on the axis? I.e.

Select { [Measures].[Premium] } on Columns, TopCount([Company].Children,5,[Measures].[Premium]) on Rows From [insuranceCube] Where { {[Date].[Month].[July 2014],[Date].[Month].[August 2014],[Date].[Month].[September 2014]}

sanjayraja commented 9 years ago

Works perfectly !! - Except for the parse because of the extra '{' above. I am not sure I can "generate this" dynamically in my application - but this works. So some thinking for me to do , (while this bug ? gets fixed.) Thanks Mark !

mkambol commented 9 years ago

For reference, this bug was already logged: MONDRIAN-1654

Another workaround that might be easier to generate is to not use a compound slicer. So instead of putting the date set in the slicer, put it in a calculated member that aggregates the set, and then put that single calculated member in the slicer.

hbfernandes commented 9 years ago

I've found this issue also in a different MDX:

WITH SET [date] AS {LastNonEmpty([date.YEAR].[YEAR].Members, [Measures].[measure_SUM])}

SELECT NON EMPTY {[Measures].[measure_SUM]} ON COLUMNS, NON EMPTY {[date]} ON ROWS

FROM [cube] WHERE {[product].[product].[a], [product].[product].[c]}

and the query returns nothing because apparently the set is only evaluated for the last member on the slicers that has no value for the measure.

I've added more information on the issue MONDRIAN-1654.

pentaho / mondrian

MDX TopCount with specific where clause doesnt work #383