How do you solve business questions without the support of the OLAP Command Processor?

[jens_hertweck]

It's relatively easy to create specific OLAP queries with the support of the BI Java SDK command processor (see Moya Watson's Command Interfaces: Keys to Simplicity in the BI Java SDK). But what can you do if the command processor does not support something you want to do?
In this weblog, I'll introduce how a simple, familiar query is easily created using the command processor. Then I'll introduce added complexity into the query, and I'll tell you how to go beyond the command processor into the query model to solve your needs.

The command processor is designed to cover certain specific features, and is limited in that sense. The underlying query model is much more powerful and therefore more complex in its configuration. BTW, I am not sure if I have mentioned it already, but the BI Java SDK is incredibly powerful and flexible and can help solve problems in myriads of ways.

A simple Top N query based on one dimension can be easily created using the command processor. The steps and source code depicted below show one way to configure such a query.

1. Create a default query and retrieve the command processor.
2. Select two measures of the measures dimension, which is moved to the columns axis by default.
3. Move the soldToParty dimension to the rows and select all members of the second level.
4. Finally, create a top 10 filter based on the quantity measure.

IBIQuery query = olap.createQuery(sampleMetaData.cube); IBICommandProcessor commandProcessor = query.getCommandProcessor(); commandProcessor.addMember(sampleMetaData.measuresQuantityMember); commandProcessor.addMember(sampleMetaData.measuresCostMember); commandProcessor.moveDimensionToRows( sampleMetaData.soldToPartyDimension); commandProcessor.addLevelMembers(soldToParty2ndLevel); commandProcessor.createTopCountFilter( sampleMetaData.soldToPartyDimension, 10, sampleMetaData.measuresQuantityMember);

Now, if you want to create a Top N query based on a crossjoin of two dimensions on the rows, you have to go beyond the command processor. The command processor only offers commands to create ranking filters for a single dimension, which is the first parameter of the method. Next we look at how to work around that and explore some of the myriads of solutions to it.

We'll start by configuring the query differently. We specify a crossjoin of two dimensions for the rows axis. Everything else is done as in our first example. The question now is how to create the top count filter based on the crossjoin of the two dimensions. See the sample code up to that point below:

... commandProcessor.moveDimensionToRows( sampleMetaData.soldToPartyDimension); commandProcessor.addLevelMembers(soldToParty2ndLevel); commandProcessor.moveDimensionToRows( sampleMetaData.distributionChannelDimension); commandProcessor.addLevelMembers(distributionChannel2ndLevel); commandProcessor.createTopCountFilter ???

One way to create the filter is to manually create all the objects necessary to specify such a ranking filter. This is shown in the steps and code below:

1. To create various query objects retrieve the query object factory.
2. Create literal reference, which is needed as an input for the Top N filter to specify the N.
3. The ranking filter uses a numeric expression to determine the Top N members. Create a reference to the quantity measure.
4. The method to create the ranking filter takes the two objects created in the previous steps and the rank type TOPCOUNT and the join type INITIAL. Pass those objects to the createRankingFilter method.
5. Finally, add the ranking filter we created in the previous step to the query specification. The point is that the ranking filter has to be applied on the axis and not on the axis dimension. That's why it is added to the tuple set of the rows axis. The call to retrieve the list of tuple expressions of the rows axis is a bit lengthy, because there is no direct support in the command processor (hm, I might add this in the future ;-).

IBIQueryFactory queryFactory = olap.getQueryFactory(); IBILiteralReference literalReference = queryFactory.getInputFactory().createLiteralReference( new Integer(10)); IBIMemberReference numericExpression = queryFactory.getInputFactory().createMemberReference( sampleMetaData.measuresQuantityMember); IBIRankingFilter rankingFilter = queryFactory.getMemberSetFactory().createRankingFilter( IBIRankTypeEnum.TOPCOUNT, literalReference, numericExpression, IBIJoinTypeEnum.INITIAL); ((IBIAxis) query.getAxis().get(IBICommandProcessor.ROWS_AXIS_INDEX)) .getTupleSet().getTupleSetExpression().add(rankingFilter);

An alternative for creating the ranking filter is to use the command processor. Now, you might be confused, because I told you that there is no support for doing this. But the point is that there is no difference in the ranking filter based on an axis dimension compared to one based on an axis. Consider the call to create and add a top count filter for the soldToParty dimension at the very beginning of this weblog. It creates and adds a ranking filter to the dimension's member set. That's the only reason the call takes the dimension as a parameter.

What we can do to make use of the command processor is to create a ranking filter based on one dimension, remove it from the dimension's member set, and place it on the axis tuple set.

We'll show how to do this below, but first note two interesting points:

• To call the method to create the ranking filter, a dimension has to be provided. Which of the query's dimensions we provide here is not relevant, because we will remove the ranking filter from the dimension's member set anyway.
• The call to remove the ranking filter is also very interesting. From the filter, we navigate back to the member set (its parent object), there we get the list of all member set expressions in which the ranking filter is contained and remove it from the list.

See the sample code below:

IBIRankingFilter rankingFilter = commandProcessor.createTopCountFilter( sampleMetaData.soldToPartyDimension, 10, sampleMetaData.measuresQuantityMember); rankingFilter.getMemberSet().getMemberSetExpression().remove(rankingFilter); ((IBIAxis) query.getAxis().get(IBICommandProcessor.ROWS_AXIS_INDEX)) .getTupleSet().getTupleSetExpression().add(rankingFilter);

Finally, let's look at a larger piece of code that creates Top N and Bottom N count and percent queries in general and could be considered a template for these types of business questions.
The method to create these queries has several parameters, that I'd like to explain a bit:

• olap is the olap interface previously retrieved from the connection
• cube is the cube upon which to base the query
• rankingMeasure is used as the ranking criteria
• displayMeasures are the measures to select on the columns axis
• levels specify the levels of the dimensions of which members are selected and crossjoined on the rows axis. The order of the levels in the list is important for the crossjoin
• topTrueBottomFalse determines wether a top or bottom query is created
• countTruePercentFalse determines wether a top/bottom-count or -percent query is created
• value specifies N for top or bottom query, which is either an integer type in the case of count, or a float type in the case of percent.

With all the explanation so far and the rich documentation of the BI Java SDK you should be able to understand the implementation of that method. Here's your template code to get you started:

public static IBIQuery createTopBottomQuery( IBIOlap olap, Cube cube, IBIMember rankingMeasure, List displayMeasures, List levels, boolean topTrueBottomFalse, boolean countTruePercentFalse, double value) throws BIOlapQueryException { IBIQueryFactory queryFactory = olap.getQueryFactory(); IBIQuery query = olap.createQuery(cube); IBICommandProcessor commandProcessor = query.getCommandProcessor(); IBILiteralReference literalReference = null; IBIRankType rankType = null; for (Iterator i = displayMeasures.iterator(); i.hasNext();) { IBIMember measure = (IBIMember) i.next(); commandProcessor.addMember(measure); } for (Iterator i = levels.iterator(); i.hasNext();) { Level level = (Level) i.next(); Dimension dimension = level.getDimension(); commandProcessor.moveDimensionToRows(dimension); commandProcessor.addLevelMembers(level); } if (countTruePercentFalse == true) { literalReference = queryFactory.getInputFactory().createLiteralReference( new Long(Math.round(value))); if (topTrueBottomFalse == true) { rankType = IBIRankTypeEnum.TOPCOUNT; } else { rankType = IBIRankTypeEnum.BOTTOMCOUNT; } } else { literalReference = queryFactory.getInputFactory().createLiteralReference( new Double(value)); if (topTrueBottomFalse == true) { rankType = IBIRankTypeEnum.TOPPERCENT; } else { rankType = IBIRankTypeEnum.BOTTOMPERCENT; } } IBIMemberReference numericExpression = queryFactory.getInputFactory().createMemberReference(rankingMeasure); IBIRankingFilter rankingFilter = queryFactory.getMemberSetFactory().createRankingFilter( rankType, literalReference, numericExpression, IBIJoinTypeEnum.INITIAL); ((IBIAxis) query.getAxis().get(IBICommandProcessor.ROWS_AXIS_INDEX)) .getTupleSet().getTupleSetExpression().add(rankingFilter); return query; }

If you haven't already, download the BI Java SDK from the SDN Downloads page today and get started with your own business question!

Love, Jens.