/*
Copyright (c) 2012 Marcin Stepien, http://smart.biz.pl/
Permission is hereby granted, free of charge, to any person obtaining
a copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:
The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
*/
package biz.smart.mdx;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import biz.smart.mdx.modeltomdx.Interval;
import biz.smart.mdx.olap.data.BracketName;
import biz.smart.mdx.olap.data.MdxSet;
import biz.smart.mdx.olap.data.MdxSetOrdered;
import biz.smart.mdx.olap.data.MdxValueExpression;
import biz.smart.mdx.olap.data.Member;
import biz.smart.mdx.olap.data.Tuple;
import biz.smart.mdx.olap.function.Crossjoin;
import biz.smart.mdx.olap.function.Head;
import biz.smart.mdx.olap.function.Order;
import biz.smart.mdx.olap.function.Rank;
import biz.smart.mdx.olap.function.Sum;
import biz.smart.mdx.olap.model.Hierarchy;
import biz.smart.mdx.olap.model.Measure;
import biz.smart.mdx.olap.model.RangeResolvableHierarchy;
/**
* util class designed for building common mdx queries
*
*/
public class MdxQueries {
/**doc: #12.0.1
* @param mdxQuery
* @param sortMeasure
* @param mdxSetToRank - set to make rank on
* @param topCount - rank count
* @param direction - rank sorting direction
* @param axis - GroupOrientation (COLUMNS | ROWS) of set elements
* @return
*/
public static MdxQuery setRankOnMeasure(
MdxQuery mdxQuery,
Measure sortMeasure,
MdxSet<?> mdxSetToRank,
int topCount,
Order.SortDirection direction,
Axis axis){
if(mdxQuery==null) throw new NullPointerException("No mdxQuery");
if(mdxSetToRank==null) throw new NullPointerException("No mdxSet");
if(!(topCount>0)) throw new IllegalArgumentException("topCount should be > 0");
// * SET [OrderedSet] AS
// * ORDER([Store].[Store Name].[Store Name].MEMBERS, --Order function
// * [Measures].[Sales Value],
// * BDESC)
MdxSetOrdered<Member> orderedSetToRank = new Order(mdxSetToRank, sortMeasure, direction).getReturn();
orderedSetToRank = new Head(orderedSetToRank, topCount).getReturn();
if(orderedSetToRank.getName()==null)
orderedSetToRank.setName(new BracketName("[OrderedSet]"));
// * MEMBER [Measures].[Rank] AS
// RANK([Hierarchy].CurrentMember,
// [OrderedSet])
// {HEAD([OrderedSet], 10)} ON 1
MdxValueExpression rankingMeasure =
new Rank(
orderedSetToRank.getMembersHierarchy().getCurrentMember(),
// orderedSetToRank.getName())
orderedSetToRank)
.getReturn();
//rankName
//[Measures].[Ranking] !
// BracketName rankingName = Measures.getInstance().getMemberExpression("Ranking").getDefinition();
BracketName rankingName = mdxQuery.getMeasure().getMemberExpression("Ranking").getDefinition();
//SELECT
// {[Measures].[Ranking], [Measures].[MeasureSum]} ON 0,
mdxQuery.addSelection(orderedSetToRank, axis);
Member rankingMember= new Member(orderedSetToRank.getMembersHierarchy(),rankingMeasure);
rankingMember.setName(rankingName);
mdxQuery.addSelection(rankingMember,axis.getSecondAxis());
return mdxQuery;
}
/*
public static MdxSet<?> getRankedSet(
MdxValueExpression measuere,
MdxSet<?> mdxSetToRank,
int topCount,
Order.OrderDirection direction){
MdxSetOrdered<Member> orderedSetToRank = new Order(mdxSetToRank, measuere, direction).getReturn();
orderedSetToRank = new Head(orderedSetToRank, topCount).getReturn();
if(orderedSetToRank.getName()==null){
String rankCaption =
orderedSetToRank.getHierarchies().get(0)!=null ?
orderedSetToRank.getHierarchies().get(0).getCaption()+" - " : "";
;
rankCaption+="ranking";
orderedSetToRank.setName(new BracketName("[OrderedSet]"));
}
return null;
}
*/
/**
* @return named set at WITH for given range values using RangeResovler - checking value existence
*/
public static MdxQuery setRange(MdxQuery mdxQuery, RangeResolvableHierarchy hierarchy, Interval bounds, Axis orientation, RangeResolver rangeResolver){
if(mdxQuery==null) throw new NullPointerException("No mdxQuery");
if(bounds==null) throw new NullPointerException("No bounds");
RangeBounds computedBounds = rangeResolver.getRange(bounds, hierarchy.getRangeResolveTableName(), hierarchy.getRangeResolveColumnName());
MdxSet<Member<?>> rangeSet = hierarchy.getHierarchyRangeMembers(
hierarchy.getMemberExpression(computedBounds.getStringFrom()),
hierarchy.getMemberExpression(computedBounds.getStringTo()));
String rangeSetlabel = "["+bounds.getFrom().toString() + " - " + bounds.getTo().toString()+"]";
rangeSet.setName(new BracketName(rangeSetlabel));
mdxQuery.addSelection(rangeSet, orientation);
return mdxQuery;
}
/**
* @param mdxQuery
* @param setLabel - non bracket set label
* @param hierarchy
* @param bounds
* @param orientation
* @param rangeResolver
* @return
*/
public static MdxQuery setRangeSum(MdxQuery mdxQuery, String setLabel, RangeResolvableHierarchy hierarchy, Interval bounds, Axis orientation, RangeResolver rangeResolver){
if(mdxQuery==null) throw new NullPointerException("No mdxQuery");
if(bounds==null) throw new NullPointerException("No bounds");
RangeBounds computedBounds = rangeResolver.getRange(bounds, hierarchy.getRangeResolveTableName(), hierarchy.getRangeResolveColumnName());
MdxSet<Member<?>> rangeSet = hierarchy.getHierarchyRangeMembers(
hierarchy.getMemberExpression(computedBounds.getStringFrom()),
hierarchy.getMemberExpression(computedBounds.getStringTo()));
BracketName sumName = new BracketName(hierarchy.getMemberExpression(setLabel));
// BracketName alteredSetName = mdxQuery.addSectionWithMember(alteredSetName, new Sum(rangeSet).getReturn());
// mdxQuery.addSectionSelectRow(alteredSetName.toString(), orientation);
MdxValueExpression sumExperssion = new Sum(rangeSet).getReturn(); //.setName(setName)
Member sumMember = new Member(rangeSet.getMembersHierarchy(),sumExperssion);
sumMember.setName(sumName);
mdxQuery.addSelection(sumMember, orientation);
return mdxQuery;
}
/**
*
* @param mdxQuery
* @param mdxSetUpper
* @param mdxSetLower
* @param elementsOrientation
* @return
*/
public static <H1 extends Hierarchy, H2 extends Hierarchy> MdxQuery setCrossJoin(
MdxQuery mdxQuery,
MdxSet<?> mdxSetUpper,
MdxSet<?> mdxSetLower,
Axis elementsOrientation){
// if(mdxSetLower==null) throw new NullPointerException("mdxSetLower is Null");
// if(mdxSetUpper==null) throw new NullPointerException("mdxSetUpper is Null");
if(mdxSetLower==null & mdxSetUpper!=null){
mdxQuery.addSelection(mdxSetUpper, elementsOrientation);
return mdxQuery;
}else if(mdxSetLower!=null & mdxSetUpper==null){
mdxQuery.addSelection(mdxSetLower, elementsOrientation);
return mdxQuery;
}else if(mdxSetLower==null & mdxSetUpper==null){
return mdxQuery;
}
mdxQuery.addSelection(new Crossjoin(mdxSetUpper, mdxSetLower).getReturn(), elementsOrientation);
return mdxQuery;
}
public static MdxQuery setCrossJoins(MdxQuery mdxQuery, List<MdxSet<?>>valueSets, Axis elementsOrientation){
if(valueSets==null) throw new NullPointerException("valueSets is Null");
if(valueSets.size()>0)
mdxQuery.addSelection(new Crossjoin(valueSets).getReturn(), elementsOrientation);
return mdxQuery;
}
/**
*
*
* #4.6.5
with
member [Measures].[Market Share] as '([Measures].[Main Measure]
/ ([Hierarchy1].[All Hierarchy1s], [Hierarchy2].[All Hierarchy2s],...))',
FORMAT_STRING = "Percent"
select
{[Measures].[Market Share]}
ON COLUMNS,
{
Crossjoin({[Hierarchy1].[Member]},
Order(
{[Hierarchy2].[Member1], [Hierarchy2].[Member2], [Hierarchy2].[Member3]},
[Measures].[Market Share], ASC
)
)
}
ON ROWS
from [Cube]
*
*
* @param mdxQuery
* @param shareMeasureOrientation - axis for measure, second axis members will be taken for measure calculation
* @return tuple to use in crossjoin
*/
public static MdxValueExpression getMarketShareExpression(
MdxQuery mdxQuery, String label, Axis shareMeasureOrientation)
throws NoHierarchyToAnalyzeShare{
LinkedHashMap<Hierarchy, Member> tupleMembers = new LinkedHashMap<Hierarchy,Member>();
List<Hierarchy> analyzedHierarchies = mdxQuery.getHierarchies(shareMeasureOrientation.getSecondAxis());
if(analyzedHierarchies.isEmpty())
throw new NoHierarchyToAnalyzeShare(shareMeasureOrientation.getSecondAxis());
for(Hierarchy hierarchy : analyzedHierarchies){
tupleMembers.put(hierarchy, hierarchy.getHierarchyAllAsMember());
}
Tuple tuple = new Tuple(tupleMembers);
MdxValueExpression secWithExpr =
new MdxValueExpression("'( "+mdxQuery.getMeasure()+"/"+tuple.toString()+")',FORMAT_STRING = \"Percent\"",
mdxQuery.getMeasure().getMemberExpression(label).getDefinition(),
tuple.getHierarchies());
return secWithExpr;
}
public static MdxQuery setMarketShareMeasureTest(MdxQuery mdxQuery, String label, Axis measureOrientation){
MdxValueExpression expr = null;
try {
expr = getMarketShareExpression(mdxQuery, label, measureOrientation);
} catch (NoHierarchyToAnalyzeShare e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
mdxQuery.addSelection(expr, measureOrientation);
return mdxQuery;
}
/**
*
*
* <p>Shared group value generator - on base of second axis content.
* If second axis content is empty (default measure) return null</p>
*
*
* #4.6.4
with
member [Measures].[Market Share] as '([Measures].[Main Measure]
/ ([Hierarchy1].CurrentMember.Parent, [Measures].[Main Measure]))',
FORMAT_STRING = "Percent"
select
{[Measures].[Market Share]}
ON COLUMNS,
{
Crossjoin({[Hierarchy1].[Member]},
Order(
{[Hierarchy2].[Member1], [Hierarchy2].[Member2], [Hierarchy2].[Member3]},
[Measures].[Market Share], ASC
)
)
}
ON ROWS
from [Cube]
*
*
* @param mdxQuery
* @param shareMeasureOrientation - axis for measure, second axis deepest hierarchy will be taken for measure calculation
* @return tuple to use in crossjoin
*/
public static MdxValueExpression getParentShareExpression(
MdxQuery mdxQuery, String label, Axis shareMeasureOrientation)
throws NoHierarchyToAnalyzeShare {
Hierarchy analyzedHierarchy = mdxQuery.getHierarchyDeepest(shareMeasureOrientation.getSecondAxis());
if(analyzedHierarchy==null)
throw new NoHierarchyToAnalyzeShare(shareMeasureOrientation.getSecondAxis());
List<Hierarchy> analyzedHierarchyList = new ArrayList<Hierarchy>();
analyzedHierarchyList.add(analyzedHierarchy);
MdxValueExpression secWithExpr =
new MdxValueExpression("'( "+analyzedHierarchy.getCurrentMember()+"/"+analyzedHierarchy.getHierarchyAll()+")',FORMAT_STRING = \"Percent\"",
mdxQuery.getMeasure().getMemberExpression(label).getDefinition(),
analyzedHierarchyList);
return secWithExpr;
}
public static MdxQuery setParentShareMeasureTest(MdxQuery mdxQuery, String label, Axis measureOrientation){
MdxValueExpression expr = null;
try {
expr = getParentShareExpression(mdxQuery, label, measureOrientation);
} catch (NoHierarchyToAnalyzeShare e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
mdxQuery.addSelection(expr, measureOrientation);
return mdxQuery;
}
/**
* @param <H> - Hierarchy of members to compare
* @param name - label for expression name
* @param member1
* @param member2
* @return percent value of change for given members with sign
*/
public static <H extends Hierarchy> MdxValueExpression getChange(BracketName name, Member<H> member1, Member<H> member2){
MdxValueExpression secWithExpr =
new MdxValueExpression("'"
+member1+"/"+member2+"-1',FORMAT_STRING = \"Percent\"",
name,
member1.getHierarchy());
return secWithExpr;
}
/**
* Simply sets set to one of axis (and erases any existing selection)
*
* @param mdxQuery
* @param mdxSet
* @param elementsOrientation
* @return
*/
public static MdxQuery setSet(MdxQuery mdxQuery, MdxSet<Member>mdxSet, Axis elementsOrientation){
if(mdxSet==null) throw new NullPointerException("mdxSet is Null");
mdxQuery.addSelection(mdxSet, elementsOrientation);
return mdxQuery;
}
}