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 static void main1(String[] args) throws ClassNotFoundException, SQLException {
OlapConnection conn = getConnection(url);
CellSet cs = getResultSet(mdx, conn);
// CellSetAxis c;
int count = 0;
if (cs.getAxes().size() > 1) {
for (Position row : cs.getAxes().get(1)) {
for (Position column : cs.getAxes().get(0)) {
for (Member member : row.getMembers()) {
System.out.println("rows:" + member.getUniqueName());
}
for (Member member : column.getMembers()) {
System.out.println("columns:" + member.getUniqueName());
}
final Cell cell = cs.getCell(column, row);
System.out.println("values:" + cell.getValue());
Position[] positions = new Position[2];
positions[0] = column;
positions[1] = row;
OlapCell oalpCell = new OlapCell(positions, cell.getValue());
System.out.println("****" + oalpCell.toString());
System.out.println(count++);
}
}
}
}
@Test
public void testSaveModelSettings() throws ConfigurationException, IOException {
PivotModel model = getPivotModel();
model.setMdx(getTestQuery());
model.initialize();
model.setSorting(true);
model.setTopBottomCount(3);
model.setSortCriteria(SortCriteria.BOTTOMCOUNT);
CellSet cellSet = model.getCellSet();
CellSetAxis axis = cellSet.getAxes().get(Axis.COLUMNS.axisOrdinal());
model.sort(axis, axis.getPositions().get(0));
String mdx = model.getCurrentMdx();
XMLConfiguration configuration = new XMLConfiguration();
configuration.setDelimiterParsingDisabled(true);
model.saveSettings(configuration);
Logger logger = LoggerFactory.getLogger(getClass());
if (logger.isDebugEnabled()) {
StringWriter writer = new StringWriter();
configuration.save(writer);
writer.flush();
writer.close();
logger.debug("Loading report content :" + System.getProperty("line.separator"));
logger.debug(writer.getBuffer().toString());
}
PivotModel newModel = new PivotModelImpl(getDataSource());
newModel.restoreSettings(configuration);
newModel.getCellSet();
String newMdx = newModel.getCurrentMdx();
if (newMdx != null) {
// Currently the parser treats every number as double value.
// It's inevitable now and does not impact the result.
newMdx = newMdx.replaceAll("3\\.0", "3");
}
assertEquals("MDX has been changed after the state restoration", mdx, newMdx);
assertTrue(
"Property 'sorting' has been changed after the state restoration", newModel.isSorting());
assertEquals(
"Property 'topBottomCount' has been changed after the state restoration",
3,
newModel.getTopBottomCount());
assertEquals(
"Property 'sortMode' has been changed after the state restoration",
SortCriteria.BOTTOMCOUNT,
newModel.getSortCriteria());
}
public static void main(String[] args) throws Exception {
OlapConnection conn = getConnection(url);
CellSet cs = getResultSet(mdx, conn);
List<Position> coordinates = new ArrayList<Position>();
List<OlapCell> results = new ArrayList<OlapCell>();
explore(cs.getAxes(), coordinates, cs, results);
Gson gson = new Gson();
System.out.println(gson.toJson(results));
}
public ResultSet drillthrough(
String queryName, List<Integer> cellPosition, Integer maxrows, String returns) {
OlapStatement stmt = null;
try {
IQuery query = getIQuery(queryName);
CellSet cs = query.getCellset();
SaikuCube cube = getQuery(queryName).getCube();
final OlapConnection con = olapDiscoverService.getNativeConnection(cube.getConnectionName());
stmt = con.createStatement();
String select = null;
StringBuffer buf = new StringBuffer();
buf.append("SELECT (");
for (int i = 0; i < cellPosition.size(); i++) {
List<Member> members =
cs.getAxes().get(i).getPositions().get(cellPosition.get(i)).getMembers();
for (int k = 0; k < members.size(); k++) {
Member m = members.get(k);
if (k > 0 || i > 0) {
buf.append(", ");
}
buf.append(m.getUniqueName());
}
}
buf.append(") ON COLUMNS \r\n");
buf.append("FROM " + cube.getCubeName() + "\r\n");
SelectNode sn = (new DefaultMdxParserImpl().parseSelect(getMDXQuery(queryName)));
final Writer writer = new StringWriter();
sn.getFilterAxis().unparse(new ParseTreeWriter(new PrintWriter(writer)));
if (StringUtils.isNotBlank(writer.toString())) {
buf.append("WHERE " + writer.toString());
}
select = buf.toString();
if (maxrows > 0) {
select = "DRILLTHROUGH MAXROWS " + maxrows + " " + select + "\r\n";
} else {
select = "DRILLTHROUGH " + select + "\r\n";
}
if (StringUtils.isNotBlank(returns)) {
select += "\r\n RETURN " + returns;
}
log.debug("Drill Through for query (" + queryName + ") : \r\n" + select);
ResultSet rs = stmt.executeQuery(select);
return rs;
} catch (Exception e) {
throw new SaikuServiceException("Error DRILLTHROUGH: " + queryName, e);
} finally {
try {
if (stmt != null) stmt.close();
} catch (Exception e) {
}
}
}
public List<SaikuMember> getResultMetadataMembers(
String queryName,
boolean preferResult,
String dimensionName,
String hierarchyName,
String levelName) {
IQuery query = getIQuery(queryName);
CellSet cs = query.getCellset();
List<SaikuMember> members = new ArrayList<SaikuMember>();
Set<Level> levels = new HashSet<Level>();
if (cs != null && preferResult) {
for (CellSetAxis axis : cs.getAxes()) {
int posIndex = 0;
for (Hierarchy h : axis.getAxisMetaData().getHierarchies()) {
if (h.getUniqueName().equals(hierarchyName)) {
log.debug("Found hierarchy in the result: " + hierarchyName);
if (h.getLevels().size() == 1) {
break;
}
Set<Member> mset = new HashSet<Member>();
for (Position pos : axis.getPositions()) {
Member m = pos.getMembers().get(posIndex);
if (!m.getLevel().getLevelType().equals(Type.ALL)) {
levels.add(m.getLevel());
}
if (m.getLevel().getUniqueName().equals(levelName)) {
mset.add(m);
}
}
members = ObjectUtil.convertMembers(mset);
Collections.sort(members, new SaikuUniqueNameComparator());
break;
}
posIndex++;
}
}
log.debug("Found members in the result: " + members.size());
}
if (cs == null || !preferResult || members.size() == 0 || levels.size() == 1) {
members =
olapDiscoverService.getLevelMembers(
query.getSaikuCube(), dimensionName, hierarchyName, levelName);
}
return members;
}
private Cell getCellAt(int axisCoord, int perpAxisCoord) {
final Position[] positions =
new Position[] {
dataAxisInfo.fullPositions.get(axisCoord), totalsAxisInfo.fullPositions.get(perpAxisCoord)
};
Cell cell = cellSet.getCell(positions[col], positions[row]);
return cell;
}
@Test
public void testBookmarkModelState() {
PivotModel model = getPivotModel();
model.setMdx(getTestQuery());
model.initialize();
model.setSorting(true);
model.setTopBottomCount(3);
model.setSortCriteria(SortCriteria.BOTTOMCOUNT);
CellSet cellSet = model.getCellSet();
CellSetAxis axis = cellSet.getAxes().get(Axis.COLUMNS.axisOrdinal());
model.sort(axis, axis.getPositions().get(0));
String mdx = model.getCurrentMdx();
Serializable bookmark = model.saveState();
assertNotNull("Bookmarked state should not be null", bookmark);
PivotModel newModel = new PivotModelImpl(getDataSource());
newModel.restoreState(bookmark);
newModel.getCellSet();
String newMdx = newModel.getCurrentMdx();
if (newMdx != null) {
// Currently the parser treats every number as double value.
// It's inevitable now and does not impact the result.
newMdx = newMdx.replaceAll("3\\.0", "3");
}
assertEquals("MDX has been changed after the state restoration", mdx, newMdx);
assertTrue(
"Property 'sorting' has been changed after the state restoration", newModel.isSorting());
assertEquals(
"Property 'topBottomCount' has been changed after the state restoration",
3,
newModel.getTopBottomCount());
assertEquals(
"Property 'sortMode' has been changed after the state restoration",
SortCriteria.BOTTOMCOUNT,
newModel.getSortCriteria());
}
public void setCellValue(
String queryName, List<Integer> position, String value, String allocationPolicy) {
try {
IQuery query = getIQuery(queryName);
OlapConnection con =
olapDiscoverService.getNativeConnection(query.getSaikuCube().getConnectionName());
Scenario s;
if (query.getScenario() == null) {
s = con.createScenario();
query.setScenario(s);
con.setScenario(s);
System.out.println("Created scenario:" + s + " : cell:" + position + " value" + value);
} else {
s = query.getScenario();
con.setScenario(s);
System.out.println("Using scenario:" + s + " : cell:" + position + " value" + value);
}
CellSet cs1 = query.execute();
query.storeCellset(cs1);
Object v = null;
try {
v = Integer.parseInt(value);
} catch (Exception e) {
v = Double.parseDouble(value);
}
if (v == null) {
throw new SaikuServiceException("Error setting value of query " + queryName + " to:" + v);
}
allocationPolicy = AllocationPolicy.EQUAL_ALLOCATION.toString();
AllocationPolicy ap = AllocationPolicy.valueOf(allocationPolicy);
CellSet cs = query.getCellset();
cs.getCell(position).setValue(v, ap);
con.setScenario(null);
} catch (Exception e) {
throw new SaikuServiceException("Error setting value: " + queryName, e);
}
}
/**
* Prints a cell set.
*
* @param cellSet Cell set
* @param pw Writer
*/
private static void print(CellSet cellSet, PrintWriter pw) {
pw.println("Axis #0:");
printAxis(pw, cellSet.getFilterAxis());
final List<CellSetAxis> axes = cellSet.getAxes();
final int axisCount = axes.size();
for (int i = 0; i < axisCount; i++) {
CellSetAxis axis = axes.get(i);
pw.println("Axis #" + (i + 1) + ":");
printAxis(pw, axis);
}
// Usually there are 3 axes: {filter, columns, rows}. Position is a
// {column, row} pair. We call printRows with axis=2. When it
// recurses to axis=-1, it prints.
List<Integer> pos = new ArrayList<Integer>(axisCount);
for (int i = 0; i < axisCount; i++) {
pos.add(-1);
}
if (axisCount == 0) {
printCell(cellSet, pw, pos);
} else {
printRows(cellSet, pw, axisCount - 1, pos);
}
}
public SaikuTag createTag(String queryName, String tagName, List<List<Integer>> cellPositions) {
try {
IQuery query = getIQuery(queryName);
CellSet cs = query.getCellset();
List<SaikuTuple> tuples = new ArrayList<SaikuTuple>();
List<SimpleCubeElement> dimensions = new ArrayList<SimpleCubeElement>();
for (List<Integer> cellPosition : cellPositions) {
List<Member> members = new ArrayList<Member>();
for (int i = 0; i < cellPosition.size(); i++) {
members.addAll(cs.getAxes().get(i).getPositions().get(cellPosition.get(i)).getMembers());
}
List<SaikuMember> sm = ObjectUtil.convertMembers(members);
SaikuTuple tuple = new SaikuTuple(sm);
tuples.add(tuple);
if (dimensions.size() == 0) {
for (Member m : members) {
SimpleCubeElement sd =
new SimpleCubeElement(
m.getDimension().getName(),
m.getDimension().getUniqueName(),
m.getDimension().getCaption());
if (!dimensions.contains(sd)) {
dimensions.add(sd);
}
}
}
}
List<SaikuDimensionSelection> filterSelections = getAxisSelection(queryName, "FILTER");
SaikuTag t = new SaikuTag(tagName, dimensions, tuples, filterSelections);
return t;
} catch (Exception e) {
throw new SaikuServiceException("Error addTag:" + tagName + " for query: " + queryName, e);
}
}
/**
* Prints the rows of cell set.
*
* @param cellSet Cell set
* @param pw Writer
* @param axis Axis ordinal
* @param pos Partial coordinate
*/
private static void printRows(CellSet cellSet, PrintWriter pw, int axis, List<Integer> pos) {
final CellSetAxis _axis = cellSet.getAxes().get(axis);
final List<Position> positions = _axis.getPositions();
final int positionCount = positions.size();
for (int i = 0; i < positionCount; i++) {
pos.set(axis, i);
if (axis == 0) {
int row = axis + 1 < pos.size() ? pos.get(axis + 1) : 0;
pw.print("Row #" + row + ": ");
printCell(cellSet, pw, pos);
pw.println();
} else {
printRows(cellSet, pw, axis - 1, pos);
}
}
}
public Matrix format(final CellSet cellSet) {
// Compute how many rows are required to display the columns axis.
final CellSetAxis columnsAxis;
if (cellSet.getAxes().size() > 0) {
columnsAxis = cellSet.getAxes().get(0);
} else {
columnsAxis = null;
}
final AxisInfo columnsAxisInfo = computeAxisInfo(columnsAxis);
// Compute how many columns are required to display the rows axis.
final CellSetAxis rowsAxis;
if (cellSet.getAxes().size() > 1) {
rowsAxis = cellSet.getAxes().get(1);
} else {
rowsAxis = null;
}
final AxisInfo rowsAxisInfo = computeAxisInfo(rowsAxis);
if (cellSet.getAxes().size() > 2) {
final int[] dimensions = new int[cellSet.getAxes().size() - 2];
for (int i = 2; i < cellSet.getAxes().size(); i++) {
final CellSetAxis cellSetAxis = cellSet.getAxes().get(i);
dimensions[i - 2] = cellSetAxis.getPositions().size();
}
for (final int[] pageCoords : CoordinateIterator.iterate(dimensions)) {
matrix =
formatPage(cellSet, pageCoords, columnsAxis, columnsAxisInfo, rowsAxis, rowsAxisInfo);
}
} else {
matrix =
formatPage(cellSet, new int[] {}, columnsAxis, columnsAxisInfo, rowsAxis, rowsAxisInfo);
}
return matrix;
}
// coordinates.size should be 0 at very first
public static void explore(
List<CellSetAxis> axes, List<Position> coordinates, CellSet cs, List<OlapCell> cellList) {
int level = coordinates.size();
// System.out.println(level + " " + axes.size());
if (level < axes.size()) {
for (Position p : axes.get(level).getPositions()) {
coordinates.add(p);
explore(axes, coordinates, cs, cellList);
}
if (level > 0) {
coordinates.remove(level - 1);
}
} else {
Position[] positions = new Position[coordinates.size()];
positions = coordinates.toArray(positions);
Cell cell = cs.getCell(positions);
OlapCell olapCell = new OlapCell(positions, cell.getValue());
cellList.add(olapCell);
// System.out.println((++count) + " " + olapCell.toString());
coordinates.remove(level - 1);
}
}
public TotalsListsBuilder(
Measure[] selectedMeasures,
TotalAggregator[] aggrTempl,
CellSet cellSet,
AxisInfo totalsAxisInfo,
AxisInfo dataAxisInfo)
throws Exception {
Cube cube;
try {
cube = cellSet.getMetaData().getCube();
} catch (OlapException e) {
throw new RuntimeException(e);
}
uniqueToSelected = new HashMap<String, Integer>();
if (selectedMeasures.length > 0) {
valueFormats = new Format[selectedMeasures.length];
measures = selectedMeasures;
for (int i = 0; i < valueFormats.length; i++) {
valueFormats[i] = getMeasureFormat(selectedMeasures[i]);
uniqueToSelected.put(selectedMeasures[i].getUniqueName(), i);
}
} else {
Measure defaultMeasure = cube.getMeasures().get(0);
if (cube.getDimensions().get("Measures") != null) {
Member ms = cube.getDimensions().get("Measures").getDefaultHierarchy().getDefaultMember();
if (ms instanceof Measure) {
defaultMeasure = (Measure) ms;
}
}
measures = new Measure[] {defaultMeasure};
valueFormats = new Format[] {getMeasureFormat(defaultMeasure)};
}
this.cellSet = cellSet;
this.dataAxisInfo = dataAxisInfo;
this.totalsAxisInfo = totalsAxisInfo;
final int maxDepth = dataAxisInfo.maxDepth + 1;
boolean hasMeasuresOnDataAxis = false;
int measuresAt = 0;
int measuresMember = 0;
final List<Member> members =
dataAxisInfo.axis.getPositionCount() > 0
? dataAxisInfo.axis.getPositions().get(0).getMembers()
: Collections.<Member>emptyList();
for (; measuresMember < members.size(); measuresMember++) {
Member m = members.get(measuresMember);
if ("Measures".equals(m.getDimension().getName())) {
hasMeasuresOnDataAxis = true;
break;
}
measuresAt += dataAxisInfo.levels[measuresMember].size();
}
if (hasMeasuresOnDataAxis) {
this.measuresAt = measuresAt;
measuresCaptions = new String[selectedMeasures.length];
for (int i = 0; i < measuresCaptions.length; i++) {
measuresCaptions[i] = selectedMeasures[i].getCaption();
}
} else {
this.measuresAt = Integer.MIN_VALUE;
measuresCaptions = null;
}
totalBranch = new TotalNode[maxDepth];
TotalNode rootNode =
new TotalNode(
measuresCaptions, measures, aggrTempl[0], this, totalsAxisInfo.fullPositions.size());
col = Axis.ROWS.equals(dataAxisInfo.axis.getAxisOrdinal()) ? 1 : 0;
row = (col + 1) & 1;
this.aggrTempl = aggrTempl;
totalBranch[0] = rootNode;
totalsLists = new List[maxDepth];
for (int i = 0; i < totalsLists.length; i++) {
totalsLists[i] = new ArrayList<TotalNode>();
}
totalsLists[0].add(rootNode);
memberBranch = new Member[dataAxisInfo.maxDepth + 1];
}
/**
* Prints the formatted value of a Cell at a given position.
*
* @param cellSet Cell set
* @param pw Print writer
* @param pos Cell coordinates
*/
private static void printCell(CellSet cellSet, PrintWriter pw, List<Integer> pos) {
Cell cell = cellSet.getCell(pos);
pw.print(cell.getFormattedValue());
}
