Multi-Dimensional Ranking Table Examples

1)    The following coding example sets a rank filter based on the business query.  The rank filter determines the top count in the cross joining of both month (from the DTE dimension) and car (from the CARS dimension) based on the values in [Measures][Sales_Sum].

/**

*

*

* @return modified  data selection

* @throws Exception

*/

private  com.sas.iquery.execution2.ResultSetInterface RankOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception

{      

// Create connection and assign roles to data items.

int val = 2;

Integer vall = new Integer(val);

Object value = (Object) vall;

 

 

             // Create connection and assign roles to data items.

             java.util.Map results =null;

 

com.sas.iquery.dataretrieval.QueryConnector m_queryConnector = new com.sas.iquery.dataretrieval.QueryConnector();

for(int i = 0; i < dsDataItems.size(); i++)

{            com.sas.iquery.metadata.business.DataItem tdi = (com.sas.iquery.metadata.business.DataItem)dsDataItems.get(i);

String label = tdi.getLabel();

 

// Assign Column to Sales_sum measure, all other dataitem will be assigned a role of ROW

if ( label.equalsIgnoreCase("SALES_SUM") )

{            ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

 

            //Define TopCount Step using SALES_SUM measure.            

java.util.List byRank = new ArrayList();

byRank.add(tdi);

com.sas.iquery.metadata.business.step.olap.BusinessQueryRankFilter step = new com.sas.iquery.metadata.business.step.olap.BusinessQueryRankFilter(BusinessQueryRankFilter.TOP, value, byRank ,   com.sas.iquery.metadata.business.BusinessQueryActionType.COUNT);

}

else

{         ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

}

}

// Assign BusinessQueryRankFilter step to dataselection based on the column axis.

ds.addStep(step, Role.COLUMN);

 

// Perform query and get results.

java.util.List selections = new java.util.ArrayList();

selections.add( ds );

ds.setID("RankBusinessQueryOLAPTable");

results = m_queryConnector.retrieveQueryResultMap( selections );

 

// Get Results interface to display table if desired.

com.sas.iquery.execution2.ResultSetInterface resultSet = (com.sas.iquery.execution2.ResultSetInterface)results.get( ds.getID() );

return resultSet;

}

2)    The following coding example sets a ranking on a specific dataItem.  This rank determines the top 2 percent in the product line based on the value of the measure.  This is accomplished by applying a rank step on the dataItem – "Product" and passing into the step define a value of 2.  

/**

*

*

* @return modified  data selection

* @throws Exception

*/

private  com.sas.iquery.execution2.ResultSetInterface RankOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception      

{            

// Create connection and assign roles to data items.

int val = 2;

Integer vall = new Integer(val);

Object value = (Object) vall;

// Create connection and assign roles to data items.

             java.util.Map results =null;

com.sas.iquery.dataretrieval.QueryConnector m_queryConnector = new com.sas.iquery.dataretrieval.QueryConnector();

for(int i = 0; i < dsDataItems.size(); i++)

{            com.sas.iquery.metadata.business.DataItem tdi = (com.sas.iquery.metadata.business.DataItem)dsDataItems.get(i);

String label = tdi.getLabel();

 

// Assign Column to Sales_sum measure and DTE dimension.

if ( label.equalsIgnoreCase("SALES_SUM") )

{            ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

}

else

if ( label.equalsIgnoreCase("DTE") )

{             ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

}

else

{        

com.sas.iquery.metadata.business.step.olap.DataItemRankFilter step = new com.sas.iquery.metadata.business.step.olap.DataItemRankFilter(com.sas.iquery.metadata.business.step.olap.DataItemRankFilter.TOP, value, com.sas.iquery.metadata.business.step.olap.DataItemRankFilter.PERCENT);

tdi.addStep(step); 

ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

}

}

 

// Perform query and get results.

java.util.List selections = new java.util.ArrayList();

selections.add( ds );

ds.setID("RankDataItemOLAPTable");

results = m_queryConnector.retrieveQueryResultMap( selections );

 

// Get Results interface to display table if desired.

com.sas.iquery.execution2.ResultSetInterface resultSet = (com.sas.iquery.execution2.ResultSetInterface)results.get( ds.getID() );

return resultSet;

}

3)    The following coding example sets ranking on a particular dataitem based on the value in a measure. This ranking determines those cars that make up 2 percent of Actual Sales.  This is accomplished by applying a rank step to dataitem - "product" and passing in 2 as the value and [Measures].[Sales_Sum] as the ranking value.

/**

*

*

* @return modified  data selection

* @throws Exception

*/

private  com.sas.iquery.execution2.ResultSetInterface TotalOLAPTableDisplay(java.util.List dsDataItems, com.sas.iquery.metadata.business.DataSelection ds) throws Exception      

{           // Create connection and assign roles to data items.

int val = 2;

Integer vall = new Integer(val);

Object value = (Object) vall;

             

// Create connection and assign roles to data items.

             java.util.Map results =null;

com.sas.iquery.dataretrieval.QueryConnector m_queryConnector = new com.sas.iquery.dataretrieval.QueryConnector();

for(int i = 0; i < dsDataItems.size(); i++)

{           com.sas.iquery.metadata.business.DataItem tdi = (com.sas.iquery.metadata.business.DataItem)dsDataItems.get(i);

String label = tdi.getLabel();

 

// Assign Column to Sales_sum measure and DTE dimension.  Assigned a role of ROW and a step to CARS dimension.

if ( label.equalsIgnoreCase("SALES_SUM") )

{      ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

        java.util.List byRank = new ArrayList();

        byRank.add(tdi);

 

}

else

if ( label.equalsIgnoreCase("DTE") )

{             ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.COLUMN);

}

 

else

if ( label.equalsIgnoreCase("CARS") )

{     com.sas.iquery.metadata.business.step.olap.DataItemRankFilter step = new com.sas.iquery.metadata.business.step.olap.DataItemRankFilter     (com.sas.iquery.metadata.business.step.olap.DataItemRankFilter .TOP, value, byRank , com.sas.iquery.metadata.business.step.olap.DataItemRankFilter .PERCENT);

        tdi.addStep(step); 

       ds.addResultItem(tdi, com.sas.iquery.metadata.business.Role.ROW);

}

}

 

// Perform query and get results.

java.util.List selections = new java.util.ArrayList();

selections.add( ds );

ds.setID("DataItemRankByMeasureOLAPTable");

results = m_queryConnector.retrieveQueryResultMap( selections );

 

// Get Results interface to display table if desired.

com.sas.iquery.execution2.ResultSetInterface resultSet = (com.sas.iquery.execution2.ResultSetInterface)results.get( ds.getID() );

return resultSet;

}