Hi,
For the MDX Below
With
Member [Measures].[0] as IIF([Measures].[No Of Sessions]>0,1,0)
Member [Measures].[1] as Aggregate({[Session
Date].[Day].[2016-12-10].Lag(4):[Session
Date].[Day].[2016-12-10]},[Measures].[0])
Member [Measures].[Repeat Users] as
COUNT(FILTER([User].[User].Children,[Measures].[1]>1))
Select { [Measures].[Repeat Users] } on Columns From [Session Date Cube] Where
{ { [Session Date].[Day].[2016-12-10] } }
Setup Scenario 1
I have created a Table in Druid called DimDate which basically holds the Date
Dimension.
The resultant Query and Expected result is fine – however
Mondrian generates the following query which Caclite Executes
select "dimdate"."QuarterName" as "c0", "dimdate"."Date" as "c1" from "dimdate"
as "dimdate" group by "dimdate"."QuarterName", "dimdate"."Date" order by CASE
WHEN "dimdate"."QuarterName" IS NULL THEN 0 ELSE 1 END, "dimdate"."QuarterName"
ASC;
This basically times out against DRUID.
{"queryType":"groupBy","dataSource":"DimDate","granularity":"all","dimensions":["QuarterName","Date"],"limitSpec":{"type":"default"},"aggregations":[{"type":"longSum","name":"dummy_agg","fieldName":"dummy_agg"}],"intervals":["2000-01-01T00:00:00.000Z/2021-01-01T00:00:00.000Z"]}
1) Why does Sqlline give back an empty result for this.
Scenario 2
I tried creating an in-memory Table for the Date Dimension using a
CustomTableFactory. Here the DimDate values are returned immediately, however
the inner join Query Fails.
The generated query is
select "datasource"."username_0" as "c0", "dimdate"."Date" as "c1",
count("datasource"."mobilenumber_2") as "m0" from "datasource" as "datasource",
"dimdate" as "dimdate" where "dimdate"."Date" in ('20161208', '20161209',
'20161210', '20161211') and "datasource"."sessiondate_3_DateDim" =
"dimdate"."Date" group by "datasource"."username_0", "dimdate"."Date"
The Error Is
[mondrian.rolap.agg.SegmentCacheManager$sqlExecutor_1] DEBUG mondrian.sql - 6:
, failed (java.sql.SQLException: Error while executing SQL "select
"datasource"."username_0" as "c0", "dimdate"."Date" as "c1",
count("datasource"."mobilenumber_2") as "m0" from "datasource" as "datasource",
"dimdate" as "dimdate" where "dimdate"."Date" in ('20161208', '20161209',
'20161210', '20161211') and "datasource"."sessiondate_3_DateDim" =
"dimdate"."Date" group by "datasource"."username_0", "dimdate"."Date"":
org.apache.calcite.interpreter.Bindables$BindableJoin cannot be cast to
org.apache.calcite.adapter.enumerable.EnumerableRel)
56092
Can someone please help with this
Regards,
Sanjay
