saiku执行速度优化二

上一篇文章介绍了添加filter可以加快查询速度。下面继续分析：

下面这个MDX语句：

WITH
SET [~FILTER] AS
    {[create_date].[create_date].[2013-01-01]}
SET [~ROWS_dimPartner_dimPartner] AS
    {[dimPartner].[dimPartner].[name].Members}
SET [~ROWS_sight_sight] AS
    Hierarchize({{[sight].[sight].[country].Members}, {[sight].[sight].[area].Members}})
SELECT
NON EMPTY {[Measures].[money], [Measures].[quantity], [Measures].[qunar_income], [Measures].[order_num]} ON COLUMNS,
NON EMPTY Order(NonEmptyCrossJoin([~ROWS_dimPartner_dimPartner], [~ROWS_sight_sight]), [Measures].[money], BDESC) ON ROWS
FROM [com_order_detail_cube]
WHERE [~FILTER]
2016-04-15 17:56:05,176 DEBUG [org.saiku.service.olap.ThinQueryService] Query End
2016-04-15 17:56:05,177 INFO  [org.saiku.service.olap.ThinQueryService] RUN#:94 Size: 7/8       Execute:        444501ms        Format: 1ms     Totals: 0ms      Total: 444502ms

查看数据查询语句，本琢磨这应该很快，执行一个sql即可

select "com_order_detail_view"."create_date" as "c0", "dim_partner"."name" as "c1", "com_order_detail_view"."area" as "c2", sum("com_order_detail_view"."money") as "m0", sum("com_order_detail_view"."quantity") as "m1", sum("com_order_detail_view"."qunar_income") as "m2", count(distinct "com_order_detail_view"."display_id") as "m3" from "com_order_detail_view" as "com_order_detail_view", "dim_partner" as "dim_partner" where "com_order_detail_view"."create_date" = DATE '2013-01-01' and "com_order_detail_view"."partner" = "dim_partner"."code" group by "com_order_detail_view"."create_date", "dim_partner"."name", "com_order_detail_view"."area";

结果发现在这之前，还需要执行这条语句，且执行时间最长：

select count(distinct "area") from "com_order_detail_view"

意淫原因应该是saiku对执行的结果进行format，所以才执行这条语句，而这个字段应该是维度表，所以猜想使用维度表或者可以提升效率。经过测试，发现确实如此，所以尽量使用纬度表优化数据的使用。