public boolean removeMember(
String queryName, String dimensionName, String uniqueMemberName, String selectionType)
throws SaikuServiceException {
IQuery query = getIQuery(queryName);
removeAllChildren(queryName, dimensionName);
List<IdentifierSegment> memberList =
IdentifierNode.parseIdentifier(uniqueMemberName).getSegmentList();
QueryDimension dimension = query.getDimension(dimensionName);
final Selection.Operator selectionMode = Selection.Operator.valueOf(selectionType);
try {
if (log.isDebugEnabled()) {
log.debug(
"query: "
+ queryName
+ " remove:"
+ selectionMode.toString()
+ " "
+ memberList.size());
}
Selection selection = dimension.createSelection(selectionMode, memberList);
dimension.getInclusions().remove(selection);
return true;
} catch (OlapException e) {
throw new SaikuServiceException(
"Error removing member (" + uniqueMemberName + ") of dimension (" + dimensionName + ")",
e);
}
}
public void resetAxisSelections(QueryAxis axis) {
for (QueryDimension dim : axis.getDimensions()) {
dim.clearInclusions();
dim.clearExclusions();
dim.clearSort();
}
}
public SaikuFilter getFilter(
String queryName,
String filtername,
String dimensionName,
String hierarchyName,
String levelName) {
IQuery query = getIQuery(queryName);
CellSet cs = query.getCellset();
List<SimpleCubeElement> members = new ArrayList<SimpleCubeElement>();
SimpleCubeElement dimension = null;
SimpleCubeElement hierarchy = null;
Set<MetadataElement> mset = new HashSet<MetadataElement>();
if (cs != null) {
List<CellSetAxis> axes = new ArrayList<CellSetAxis>();
axes.addAll(cs.getAxes());
axes.add(cs.getFilterAxis());
for (CellSetAxis axis : axes) {
int posIndex = 0;
for (Hierarchy h : axis.getAxisMetaData().getHierarchies()) {
if (h.getName().equals(hierarchyName)) {
if (hierarchy == null) {
hierarchy = new SimpleCubeElement(h.getName(), h.getUniqueName(), h.getCaption());
Dimension d = h.getDimension();
dimension = new SimpleCubeElement(d.getName(), d.getUniqueName(), d.getCaption());
}
if (h.getLevels().size() == 1) {
break;
}
for (Position pos : axis.getPositions()) {
Member m = pos.getMembers().get(posIndex);
if (m.getLevel().getName().equals(levelName)) {
mset.add(m);
}
}
break;
}
posIndex++;
}
}
if (mset.size() == 0) {
QueryDimension qd = query.getDimension(dimensionName);
if (qd != null && qd.getAxis().getLocation() != null) {
for (Selection sel : qd.getInclusions()) {
if ((sel.getRootElement() instanceof Member)) {
Member m = ((Member) sel.getRootElement());
if (m.getLevel().getName().equals(levelName)) {
mset.add(m);
}
}
}
}
}
members = ObjectUtil.convert2Simple(mset);
Collections.sort(members, new SaikuUniqueNameComparator());
log.debug("Create Filters: Found members in the result or query: " + members.size());
}
return new SaikuFilter(filtername, null, dimension, hierarchy, members);
}
public void moveDimension(QueryDimension dimension, Axis axis, int position) {
dimension.setHierarchizeMode(HierarchizeMode.PRE);
if (dimension.getName() != "Measures") {
dimension.setHierarchyConsistent(true);
}
QueryAxis oldQueryAxis = findAxis(dimension);
QueryAxis newQueryAxis = query.getAxis(axis);
if (oldQueryAxis != null && newQueryAxis != null) {
oldQueryAxis.removeDimension(dimension);
newQueryAxis.addDimension(position, dimension);
}
}
public SaikuQuery removeFilter(String queryname) {
IQuery query = getIQuery(queryname);
if (query != null && query.getFilter() != null) {
SaikuFilter filter = query.getFilter();
QueryDimension qDim = query.getDimension(filter.getDimension().getName());
if (qDim != null) {
qDim.clearInclusions();
query.moveDimension(qDim, null);
}
query.removeFilter();
}
return ObjectUtil.convert(query);
}
public boolean removeLevel(
String queryName, String dimensionName, String uniqueHierarchyName, String uniqueLevelName) {
IQuery query = getIQuery(queryName);
removeAllChildren(queryName, dimensionName);
QueryDimension dimension = query.getDimension(dimensionName);
try {
for (Hierarchy hierarchy : dimension.getDimension().getHierarchies()) {
if (hierarchy.getUniqueName().equals(uniqueHierarchyName)) {
for (Level level : hierarchy.getLevels()) {
if (level.getUniqueName().equals(uniqueLevelName)) {
Selection inclusion = dimension.createSelection(level);
dimension.getInclusions().remove(inclusion);
ArrayList<Selection> removals = new ArrayList<Selection>();
for (Selection sel : dimension.getInclusions()) {
if ((sel.getRootElement() instanceof Member)) {
if (((Member) sel.getRootElement()).getLevel().equals(level)) {
if (dimension.getInclusions().contains(sel)) {
removals.add(sel);
}
}
}
}
dimension.getInclusions().removeAll(removals);
}
}
}
}
} catch (Exception e) {
throw new SaikuServiceException(
"Cannot remove level" + uniqueLevelName + "from dimension " + dimensionName, e);
}
return true;
}
public boolean includeLevel(
String queryName, String dimensionName, String uniqueHierarchyName, String uniqueLevelName) {
IQuery query = getIQuery(queryName);
removeAllChildren(queryName, dimensionName);
QueryDimension dimension = query.getDimension(dimensionName);
for (Hierarchy hierarchy : dimension.getDimension().getHierarchies()) {
if (hierarchy.getUniqueName().equals(uniqueHierarchyName)) {
for (Level level : hierarchy.getLevels()) {
if (level.getUniqueName().equals(uniqueLevelName)) {
Selection sel = dimension.createSelection(level);
if (!dimension.getInclusions().contains(sel)) {
dimension.include(level);
}
return true;
}
}
}
}
return false;
}
public boolean includeChildren(String queryName, String dimensionName, String uniqueMemberName) {
IQuery query = getIQuery(queryName);
List<IdentifierSegment> memberList =
IdentifierNode.parseIdentifier(uniqueMemberName).getSegmentList();
QueryDimension dimension = query.getDimension(dimensionName);
try {
Selection sel = dimension.createSelection(Operator.CHILDREN, memberList);
dimension.getInclusions().add(sel);
return true;
} catch (OlapException e) {
throw new SaikuServiceException(
"Cannot include children query ("
+ queryName
+ ") dimension ("
+ dimensionName
+ ") member ("
+ uniqueMemberName
+ ")",
e);
}
}
public SaikuQuery applyFilter(String queryname, SaikuFilter filter) throws Exception {
IQuery query = getIQuery(queryname);
if (filter != null
&& filter.getName() != null
&& filter.getDimension() != null
&& filter.getMembers() != null) {
query.setFilter(filter);
QueryDimension qDim = query.getDimension(filter.getDimension().getName());
if (qDim != null) {
qDim.clearInclusions();
query.moveDimension(qDim, Axis.FILTER);
for (SimpleCubeElement member : filter.getMembers()) {
List<IdentifierSegment> memberList =
IdentifierNode.parseIdentifier(member.getUniqueName()).getSegmentList();
qDim.include(memberList);
}
}
}
return ObjectUtil.convert(query);
}
public CellSet execute() throws Exception {
if (scenario != null && query.getDimension("Scenario") != null) {
QueryDimension dimension = query.getDimension("Scenario");
moveDimension(dimension, Axis.FILTER);
Selection sel =
dimension.createSelection(
IdentifierParser.parseIdentifier("[Scenario].[" + getScenario().getId() + "]"));
if (!dimension.getInclusions().contains(sel)) {
dimension.getInclusions().add(sel);
}
}
Query mdx = this.query;
mdx.validate();
StringWriter writer = new StringWriter();
mdx.getSelect().unparse(new ParseTreeWriter(new PrintWriter(writer)));
log.debug("Executing query (" + this.getName() + ") :\n" + writer.toString());
CellSet cellSet = mdx.execute();
if (scenario != null && query.getDimension("Scenario") != null) {
QueryDimension dimension = query.getDimension("Scenario");
dimension.getInclusions().clear();
moveDimension(dimension, null);
}
return cellSet;
}
public boolean removeAllChildren(String queryName, String dimensionName) {
IQuery query = getIQuery(queryName);
QueryDimension dimension = query.getDimension(dimensionName);
List<Selection> children = new ArrayList<Selection>();
try {
for (Selection sel : dimension.getInclusions()) {
if (sel.getOperator().equals(Operator.CHILDREN)) {
children.add(sel);
}
}
dimension.getInclusions().removeAll(children);
return true;
} catch (Exception e) {
throw new SaikuServiceException(
"Cannot remove all children for query ("
+ queryName
+ ") dimension ("
+ dimensionName
+ ")",
e);
}
}
public boolean includeMember(
String queryName,
String dimensionName,
String uniqueMemberName,
String selectionType,
int memberposition) {
IQuery query = getIQuery(queryName);
List<IdentifierSegment> memberList =
IdentifierNode.parseIdentifier(uniqueMemberName).getSegmentList();
QueryDimension dimension = query.getDimension(dimensionName);
final Selection.Operator selectionMode = Selection.Operator.valueOf(selectionType);
try {
removeAllChildren(queryName, dimensionName);
Selection sel = dimension.createSelection(selectionMode, memberList);
if (dimension.getInclusions().contains(sel)) {
dimension.getInclusions().remove(sel);
}
if (memberposition < 0) {
memberposition = dimension.getInclusions().size();
}
dimension.getInclusions().add(memberposition, sel);
return true;
} catch (OlapException e) {
throw new SaikuServiceException(
"Cannot include member query ("
+ queryName
+ ") dimension ("
+ dimensionName
+ ") member ("
+ uniqueMemberName
+ ") operator ("
+ selectionType
+ ") position "
+ memberposition,
e);
}
}
public static void main(String[] args) throws Exception {
// Load the driver
Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
// Connect
final Connection connection =
DriverManager.getConnection(
"jdbc:mondrian:" // Driver ident
+ "Jdbc=jdbc:hsqldb:file:foodmart/foodmart;" // Relational DB
+ "Catalog=file:foodmart/FoodMart.xml;"); // Catalog
// We are dealing with an olap connection. we must unwrap it.
final OlapConnection olapConnection = connection.unwrap(OlapConnection.class);
// Get a cube object.
Cube salesCube = olapConnection.getOlapSchema().getCubes().get("Sales");
// Build a query object.
Query myQuery = new Query("myQuery", salesCube);
// Lookup some dimensions
QueryDimension productDim = myQuery.getDimension("Product");
QueryDimension storeDim = myQuery.getDimension("Store");
QueryDimension timeDim = myQuery.getDimension("Time");
// Place dimensions on some axis
myQuery.getAxis(Axis.COLUMNS).addDimension(productDim);
myQuery.getAxis(Axis.ROWS).addDimension(storeDim);
myQuery.getAxis(Axis.FILTER).addDimension(timeDim);
// Including a member by metadata
Member year1997 =
salesCube.lookupMember(IdentifierNode.ofNames("Time", "1997").getSegmentList());
timeDim.include(year1997);
// Including a member by name parts
productDim.include(
Selection.Operator.CHILDREN,
IdentifierNode.ofNames("Product", "Drink", "Beverages").getSegmentList());
// We can also exclude members
productDim.exclude(
IdentifierNode.ofNames("Product", "Drink", "Beverages", "Carbonated Beverages")
.getSegmentList());
// Validate this query
myQuery.validate();
// Print!
System.out.println("/********************* QUERY ***********************/");
System.out.println(myQuery.getSelect().toString());
System.out.println("/********************* QUERY ***********************/");
System.out.println(" ");
System.out.println(" ");
System.out.println(" ");
System.out.println(" ");
System.out.println("/********************* RESULTS ***********************/");
RectangularCellSetFormatter formatter = new RectangularCellSetFormatter(false);
PrintWriter writer = new PrintWriter(System.out);
formatter.format(myQuery.execute(), writer);
writer.flush();
System.out.println("/********************* RESULTS ***********************/");
}
private IQuery applyTag(IQuery query, OlapConnection con, SaikuTag t) throws Exception {
String xml = query.toXml();
QueryDeserializer qd = new QueryDeserializer();
query = qd.unparse(xml, con);
List<SimpleCubeElement> doneDimension = new ArrayList<SimpleCubeElement>();
Map<String, QueryDimension> dimensionMap = new HashMap<String, QueryDimension>();
if (t.getSaikuTupleDimensions() != null) {
for (SimpleCubeElement st : t.getSaikuTupleDimensions()) {
if (!doneDimension.contains(st)) {
QueryDimension dim = query.getDimension(st.getName());
dimensionMap.put(st.getUniqueName(), dim);
dim.clearExclusions();
dim.clearInclusions();
query.moveDimension(dim, null);
doneDimension.add(st);
}
}
if (t.getSaikuTupleDimensions().size() > 0) {
SimpleCubeElement rootDim = t.getSaikuTupleDimensions().get(0);
QueryDimension dim = query.getDimension(rootDim.getName());
query.moveDimension(dim, Axis.COLUMNS);
for (SaikuTuple tuple : t.getSaikuTuples()) {
SaikuMember m = tuple.getSaikuMember(rootDim.getUniqueName());
List<SaikuMember> others = tuple.getOtherSaikuMembers(rootDim.getUniqueName());
Selection sel = dim.createSelection(IdentifierParser.parseIdentifier(m.getUniqueName()));
for (SaikuMember context : others) {
QueryDimension otherDim = dimensionMap.get(context.getDimensionUniqueName());
query.moveDimension(otherDim, Axis.COLUMNS);
Selection ctxSel =
otherDim.createSelection(IdentifierParser.parseIdentifier(context.getUniqueName()));
sel.addContext(ctxSel);
}
dim.getInclusions().add(sel);
}
}
}
if (t.getSaikuDimensionSelections() != null) {
for (SaikuDimensionSelection dimsel : t.getSaikuDimensionSelections()) {
if (!dimsel.getName().equals("Measures")) {
QueryDimension filterDim = query.getDimension(dimsel.getName());
query.moveDimension(filterDim, Axis.FILTER);
filterDim.clearInclusions();
for (SaikuSelection ss : dimsel.getSelections()) {
if (ss.getType() == SaikuSelection.Type.MEMBER) {
Selection sel =
filterDim.createSelection(IdentifierParser.parseIdentifier(ss.getUniqueName()));
if (!filterDim.getInclusions().contains(sel)) {
filterDim.getInclusions().add(sel);
}
}
}
// TODO: Move it to columns since drilling through with 2 filter items of the same
// dimension doesn't work
// if (filterDim.getInclusions().size() > 1) {
// query.moveDimension(filterDim, Axis.COLUMNS);
// }
}
}
}
return query;
}