saiku+kettle整合（十五）常用date range-CSDN博客

title: saiku+kettle整合（十五）常用date range tags:

olap
saiku categories: saiku date: 2016-10-25 18:18:54

常用date range

关于很常用的参数使用range saiku ce居然不支持！！！如果想要传入多个条件只能通过,分割，比如2016-10-01，2016-10-02…… 如果需要传入一个月或者一年的日期会导致url长度超长。

收费版本提供了dateFilter详细见 Advanced Date Filtering

没关系，穷人有穷人的做法！

首先研究mdx参数化的代码 saiku 存在两种参数化行为

saiku在层次上使用include或者exclude将指定参数传入

       public class QueryLevel extends AbstractQuerySet implements Named {
           private final QueryHierarchy hierarchy;
           private final Level level;
           private List<Member> inclusions = new ArrayList();
           private List<Member> exclusions = new ArrayList();
           private Member rangeStart = null;
           private Member rangeEnd = null;
           private String rangeStartExpr = null;
           private String rangeEndExpr = null;
           private String rangeStartSyn;
           private String rangeEndSyn;
           private String parameterName = null;
           private SelectionType parameterSelectionType;
        
           public QueryLevel(QueryHierarchy hierarchy, Level level) {
               this.parameterSelectionType = SelectionType.INCLUSION;
               this.hierarchy = hierarchy;
               this.level = level;
           }
        
           public QueryHierarchy getQueryHierarchy() {
               return this.hierarchy;
           }
        
           public String getName() {
               return this.level.getName();
           }
        
           public String getUniqueName() {
               return this.level.getUniqueName();
           }
        
           public String getCaption() {
               return this.level.getCaption();
           }
        
           public boolean isSimple() {
               return super.isSimple() && (this.level.getLevelType().equals(Type.ALL) || this.inclusions.isEmpty() && this.exclusions.isEmpty() && this.rangeStart == null && this.rangeEnd == null && this.rangeStartExpr == null && this.rangeEndExpr == null) && (!this.hasParameter() || this.hierarchy.getQuery().getParameter(this.getParameterName()) == null);
           }
        
           public boolean isRange() {
               return this.rangeStart != null && this.rangeEnd != null || this.rangeStartExpr != null || this.rangeEndExpr != null;
           }
        
           public Level getLevel() {
               return this.level;
           }
        
           public List<Member> getInclusions() {
               return this.inclusions;
           }
        
           public List<Member> getExclusions() {
               return this.exclusions;
           }
        
           public Member getRangeStart() {
               return this.rangeStart;
           }
        
           public Member getRangeEnd() {
               return this.rangeEnd;
           }
        
           public String getRangeStartExpr() {
               return this.rangeStartExpr;
           }
        
           public String getRangeEndExpr() {
               return this.rangeEndExpr;
           }
        
           public String getRangeStartSyn() {
               return this.rangeStartSyn;
           }
        
           public String getRangeEndSyn() {
               return this.rangeEndSyn;
           }
        
           protected void clearSelections() {
               this.inclusions.clear();
               this.exclusions.clear();
               this.rangeStart = null;
               this.rangeStartExpr = null;
               this.rangeStartSyn = null;
               this.rangeEnd = null;
               this.rangeEndExpr = null;
               this.rangeEndSyn = null;
           }
        
           protected void include(Member m) {
               if(!this.inclusions.contains(m)) {
                   this.inclusions.add(m);
               }
        
           }
        
           protected void exclude(Member m) {
               if(this.inclusions.contains(m)) {
                   this.inclusions.remove(m);
               }
        
               if(!this.exclusions.contains(m)) {
                   this.exclusions.add(m);
               }
        
           }
        
           protected void setRange(Member start, Member end) {
               this.rangeStart = start;
               this.rangeEnd = end;
           }
复制代码

 
复制代码

通常在ui上直接将层次选中后出现的参数就是该处使用的，一般可以选择include或者exclude，在不修改java代码的前提下很难使用到range
saiku可以提供了一次整体替换参数的机会

       query = updateQuery(query);
        
       try {
           String mdx = query.getParameterResolvedMdx();
           log.info(runId + "\tType:" + query.getType() + ":\n" + mdx);
        
           CellSet cs = stmt.executeOlapQuery(mdx);
           queryContext.store(ObjectKey.RESULT, cs);
           if (query != null) {
               queryContext.store(ObjectKey.QUERY, query);
           }
           return cs;
       } finally {
           stmt.close();
           queryContext.remove(ObjectKey.STATEMENT);
       }
复制代码

其中188行处即第一次替换 191行处提供了批量替换参数的机会  
但是此处代码不能帮我们自动生成层次的parent名称
复制代码

public static String replaceParameters(String query, Map<String,String> parameters) {
     if (parameters != null) {
        for (String parameter : parameters.keySet()) {
           String value = parameters.get(parameter);
           if (value == null)
              value = "";
           query = query.replaceAll("(?i)\\$\\{" + parameter + "\\}", value);
        }
     }
     return query;
  }
复制代码

代码很简单，就是使用${var} 作为参数做一次正则替换

因此可以修改生成的mdx为

    WITH
    SET [~ROWS] AS
        {[BillDate].[default].${start}:[BillDate].[default].${end}}
    SELECT
    NON EMPTY {[Measures].[amount]} ON COLUMNS,
    NON EMPTY [~ROWS] ON ROWS
    FROM [maintain analysis]
复制代码

访问该画面传入参数的姿势和原来相同

http://localhost:8081/?plugin=true&paramstart=[2015].[4].[十月].[2015-10-01]&paramend=[2015].[4].[十月].[2015-10-29]&mode=table#query/open//homes/ddd.saiku

可以参照saiku+kettle整合（九）saiku参数化页面

    WITH
    SET [~ROWS] AS
    Filter([BillDate].[default].[date].Members, [BillDate].[default].[date].CurrentMember.NAME >= ${start} AND [BillDate].[default].[date].CurrentMember.NAME <= ${end})
    SELECT
    NON EMPTY {[Measures].[count]} ON COLUMNS,
    NON EMPTY [~ROWS] ON ROWS
    
    FROM [maintain analysis]
复制代码

访问该画面传入参数的姿势和原来相同
http://localhost:8081/?plugin=true&paramstart=2015-10-01&paramend=2015-10-29&mode=table#query/open//homes/ddd.saiku