Business Intelligence – Oracle

Hyperion Essbase 11.1.1.0 – Using Java APIs – Part 2 – XMLA and Normal MDX Outputs

Posted by Venkatakrishnan J on January 13, 2009

Yesterday i had given an example of the Java API which can be used for displaying the dimension members from an Essbase Outline. Today, lets test out another example which would basically fire MDX Queries onto the Essbase Server. One of the main reasons for me to try this example is to figure out how MDX is handled by Essbase and in turn how it is handled by BI EE. For this we would be testing 2 different scenarios i.e 2 different MDX queries

1. An MDX query which produces same results using Essbase specifications as well as XMLA specifications.
2. An MDX query which does not produce the same results using Essbase specifications and XMLA specifications.

First let us try to understand the difference between the above 2 types of queries. As an example we shall start with the MDX query given below

With
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate1] as '{[Market].Generations(1).DIMENSION.MEMBERS }'
select { [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

       

The above MDX query produces the same results as per XMLA specs as well as Essbase specs. This is because the Generation standards for both XMLA and Essbase remain the same. But Essbase Level specifications are slightly different from the XMLA specifications. In XMLA specifications, Levels(0) denotes the dimension itself. But in the case of Essbase, Levels(0) denotes the leaf nodes. This is the key differentiator which clearly lets us know the BI EE actually uses the Essbase specifications instead of the XMLA specifications. Another example is provided below which shows an MDX query that produces different results in XMLA specs and Essbase Specs.

SELECT
CrossJoin([Measures].CHILDREN , [Market].CHILDREN) on columns,
[Product].Levels(0).Members
DIMENSION PROPERTIES [Product].[Intro Date] on rows
from Sample.Basic

       

Now, as part of our JAPI testing, lets use the XMLA/Essbase compliant MDX query The java class code to test this out is given below

package SampleBIEE;

import com.essbase.api.base.*;
import com.essbase.api.metadata.IEssMemberSelection;
import com.essbase.api.session.*;
//import com.essbase.api.datasource.*;
import com.essbase.api.dataquery.*;
//import com.essbase.api.metadata.*;
import com.essbase.api.domain.*;

public class MDXQueryJAPI {

private static String s_userName = “admin”;

private static String s_password = “password”;

private static String s_olapSvrName = “localhost”;

private static String s_provider = “http://localhost:13080/aps/JAPI”;

private static String s_appName = “Demo”;

private static String s_cubeName = “Basic”;

private static final int FAILURE_CODE = 1;

public static void main(String[] args) {
int statusCode = 0;
IEssbase ess = null;
IEssCubeView cv = null;
byte ch[] = new byte[2];

try {

acceptArgs(args);
// Create JAPI instance.
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

// Sign On to the Provider
IEssDomain dom = ess.signOn(s_userName, s_password, false, null,
s_provider);

try {
cv = dom.openCubeView(“Mdx Query Example”, s_olapSvrName,
s_appName, s_cubeName);
performMdxQuery(cv);
} catch (Exception x) {
System.out.println(“Error: ” + x.getMessage());
x.printStackTrace();
statusCode = FAILURE_CODE;
} finally {
// Close cube view.
try {
if (cv != null)
cv.close();
} catch (EssException x) {
System.err.println(“Error: ” + x.getMessage());
}
}

if (ess != null && ess.isSignedOn() == true)
ess.signOff();
} catch (EssException x) {
System.err.println(“Error: ” + x.getMessage());
}

if (statusCode == FAILURE_CODE) System.exit(FAILURE_CODE);
}

private static void performMdxQuery(IEssCubeView cv) throws Exception {
boolean bDataLess = false;
boolean bNeedCellAttributes = false;
boolean bHideData = true;

String mdxquery = null;

mdxquery = “With ” +
“set [Evaluate0] as ‘{[Product].Generations(2).DIMENSION.MEMBERS }’ ” +
“set [Evaluate1] as ‘{[Market].Generations(1).DIMENSION.MEMBERS }’ ” +
“select ” +
“{ [Scenario].[Actual] } on columns, ” +
“NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows ” +
“from ” +
“[Demo.Basic] “;

IEssOpMdxQuery op = cv.createIEssOpMdxQuery();

op.setQuery(bDataLess, bHideData, mdxquery, bNeedCellAttributes,
IEssOpMdxQuery.EEssMemberIdentifierType.NAME);
op.setXMLAMode(false);

op.setNeedFormattedCellValue(true);
op.setNeedSmartlistName(true);
op.setNeedFormatString(false);
op.setNeedFormattedMissingCells(true);

op.setNeedMeaninglessCells(false);

cv.performOperation(op);

IEssMdDataSet mddata = cv.getMdDataSet();

printMdDataSet(mddata, op);
System.out
.println(“\n—————— MDX Result in Grid Format —————-“);
printMdDataSetInGridForm(mddata);
System.out
.println(“—————— ————————- —————-“);
}

private static void printMdDataSet(IEssMdDataSet mddata, IEssOpMdxQuery op) throws Exception {

IEssMdAxis[] axes = mddata.getAllAxes();

int nAxes = axes.length;

System.out.println(“Number of axes: ” + nAxes);

for (int axisInd = 0; axisInd < nAxes; axisInd++) { if (axes[axisInd].isSlicerAxis()) System.out.print("Slicer "); System.out.println("Axis " + axisInd + " Number Of Tuples : " + axes[axisInd].getTupleCount() + " Number Of Dimensions : " + axes[axisInd].getDimensionCount()); } for (int axisInd = 0; axisInd < nAxes; axisInd++) { IEssMdAxis axis = axes[axisInd]; int nTuples = axis.getTupleCount(); // Get all the dimensions and their info in this axis System.out.println("\nGetting dimensions in axis : " + axisInd); IEssMdMember[] dims = axis.getAllDimensions(); for (int axisDimInd = 0; axisDimInd < dims.length; axisDimInd++) { IEssMdMember dim = dims[axisDimInd]; int propscnt = dim.getCountProperties(); System.out.println("Dim " + axisDimInd + " dim name : " + dim.getName() + " #props " + propscnt); for (int propInd = 0; propInd < propscnt; propInd++) { System.out.println("Property " + propInd + " Name : " + dim.getPropertyName(propInd) + ", Type : " + dim.getPropertyDataType(propInd)); } } System.out.println("\nGetting members in all the tuples of axis : " + axisInd); for (int tupleInd = 0; tupleInd < nTuples; tupleInd++) { System.out.println("\nTuple : " + tupleInd); IEssMdMember[] mbrs = axis.getAllTupleMembers(tupleInd); printMemberInfo(mbrs); } } System.out.println("\n\n\nPrinting results through clusters ....."); for (int axisInd = 0; axisInd < nAxes; axisInd++) { IEssMdAxis axis = axes[axisInd]; // Get all the clusters in this axis IEssMdCluster[] clusters = axis.getAllClusters(); long nClusters = axis.getClusterCount(); System.out.println("\nAxis " + axisInd + " Num clusters " + nClusters); for (int clusterInd = 0; clusterInd < nClusters; clusterInd++) { IEssMdCluster cluster = clusters[clusterInd]; int clusterTupleCount = cluster.getTupleCount(); // Get the members based on the dimension index System.out.println("\nCluster " + clusterInd + " Size " + clusterTupleCount); for (int dimInd = 0; dimInd < cluster.getDimensionCount(); dimInd++) { IEssMdMember[] mbrs = cluster .getAllDimensionMembers(dimInd); System.out.println("Cluster Dim " + dimInd + " Number Of Members : " + mbrs.length); } // Get the members based on tuple index System.out .println("\nGetting members in all the tuples of this cluster..."); for (int tupleInd = 0; tupleInd < clusterTupleCount; tupleInd++) { System.out.println("\nTuple : " + tupleInd); printMemberInfo(cluster.getAllTupleMembers(tupleInd)); } } } System.out.println("\n\n\nGetting all the cells..."); System.out.println("Number of cells : " + mddata.getCellCount()); for (int ulCellOrdinal = 0; ulCellOrdinal < mddata.getCellCount(); ulCellOrdinal++) { if (!op.isNeedFormattedMissingCells() && mddata.isMissingCell(ulCellOrdinal)) { System.out.println("Cell[" + ulCellOrdinal + "] = #Missing"); } else if (mddata.isNoAccessCell(ulCellOrdinal)) { System.out.println("Cell[" + ulCellOrdinal + "] = #NoAccess"); } else { printCellValue(mddata, ulCellOrdinal, op); } } } private static void printCellValue(IEssMdDataSet mddata, int cellOrdinal, IEssOpMdxQuery op) throws EssException { String actualVal = (mddata.isMissingCell(cellOrdinal)) ? "#Missing" : "" + mddata.getCellValue(cellOrdinal); String fmtdCellTxtVal = mddata.getFormattedValue(cellOrdinal); int cellType = mddata.getCellType(cellOrdinal); if (fmtdCellTxtVal != null && fmtdCellTxtVal.length() > 0) {
String printSmlistName = (cellType == IEssMdDataSet.CELLTYPE_SMARTLIST)
? (“, SmartlistName = ” + mddata.getSmartListName(cellOrdinal))
: “”;
String printNumericValue = (!mddata.isMissingCell(cellOrdinal))
? (“, NumericValue = ” + actualVal)
: “”;
System.out.print(“Cell[” + cellOrdinal + “] = ” + fmtdCellTxtVal
+ ” {CellType = ” + cellType + printNumericValue
+ printSmlistName
+ “}”);
} else {
System.out.print(“Cell[” + cellOrdinal + “] = ” + actualVal);
}

if (op.isNeedFormatString()) {
if (cellType == IEssMdDataSet.CELLTYPE_DATE
|| cellType == IEssMdDataSet.CELLTYPE_DOUBLE) {
System.out.println(” [FormatString = ”
+ mddata.getFormatString(cellOrdinal) + “]”);
} else {
System.out.println();
}
}
}

private static void printMdDataSetInGridForm(IEssMdDataSet mddata)
throws Exception {
IEssMdAxis[] axis = mddata.getAllAxes();

int cols = axis[1].getTupleCount();
int rows = axis[2].getTupleCount();
IEssMdMember[] mem = axis[2].getAllTupleMembers(0);
for (int i = 0; i < mem.length; i++) System.out.print("\t"); for (int j = 0; j < cols; j++) { mem = axis[1].getAllTupleMembers(j); printTuple(mem); } System.out.println(); int k = 0; for (int i = 0; i < rows; i++) { mem = axis[2].getAllTupleMembers(i); printTuple(mem); for (int l = 0; l < mem.length; l++) { int propcnt = mem[l].getCountProperties(); for (int propInd = 0; propInd < propcnt; propInd++) { IEssValueAny propval = mem[l].getPropertyValueAny(propInd); System.out.print(mem[l].getPropertyName(propInd) + ", " + propval + " "); } } for (int j = 0; j < cols; j++) { if (mddata.isMissingCell(k)) { System.out.print("#Missing\t"); } else if (mddata.isNoAccessCell(k)) { System.out.print("#NoAccess\t"); } else { String fmtdCellTxtVal = mddata.getFormattedValue(k); if (fmtdCellTxtVal != null && fmtdCellTxtVal.length() > 0) {
System.out.print(fmtdCellTxtVal + “\t”);
} else {
double val = mddata.getCellValue(k);
System.out.print(val + “\t”);
}
}
k++;
}
System.out.println();
}
}

private static void printTuple(IEssMdMember[] mem) throws EssException {
System.out.print(“(“);
for (int i = 0; i < mem.length - 1; i++) System.out.print(mem[i].getName() + ", "); System.out.print(mem[mem.length - 1].getName() + ")\t"); } private static void printMemberInfo(IEssMdMember[] members) throws EssException { for (int mbrInd = 0; mbrInd < members.length; mbrInd++) { IEssMdMember member = members[mbrInd]; System.out.println("Mbr " + mbrInd + " identifier " + member.getName()); int propcnt = member.getCountProperties(); for (int propInd = 0; propInd < propcnt; propInd++) { IEssValueAny propval = member.getPropertyValueAny(propInd); System.out.println("Property " + propInd + " Name : " + member.getPropertyName(propInd) + ", Type : " + member.getPropertyDataType(propInd) + ", Value : " + propval); } } } static void acceptArgs(String[] args) throws EssException { s_userName = "admin"; s_password = "password"; s_olapSvrName = "localhost"; s_provider = "http://localhost:13080/aps/JAPI"; } } [/sourcecode] If you look at the code above there is a specific setting called [sourcecode language='cpp'] op.setXMLAMode(false); [/sourcecode] So what this mode basically does is, it returns the data in XMLA compliant format or in the default Essbase format. Now, lets compile and run this from JDeveloper with the XMLA mode turned off.        

As you see, since this Query itself is XMLA compliant the output of the query itself returns the data in same format as well. The only difference is that there are certain properties which are not returned as part of the output. So, we shall see the output of the class with the XMLA setting turned on.

       

Not much of a difference here apart from the properties. Of course, the printing of properties is controlled through a seperate method, but the output data in itself is dictated by the MDX and the XMLA settings. Now, let us test the non XMLA compliant MDX query above from the same JAPI with the XMLA setting turned on.

       

As you see, since the XMLA mode is turned on, the Levels(0) in the query is treated as topmost Node (which is the product itself) and displays data only for the product member. Now, lets switch this XMLA setting to off and execute the same class.

       

The above clearly demostrates the fact that BI EE does not follow the XMLA specifications. Of course, this can change in the coming releases, but this should serve as a testing mechanism to understand how various Reporting Tools connect to Essbase/MSAS connectivity.

One Response to “Hyperion Essbase 11.1.1.0 – Using Java APIs – Part 2 – XMLA and Normal MDX Outputs”

  1. shyam said

    Do this work with mondrian?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: