Working with Large List of Filters in Kyvos

Kyvos now supports a query callback-based mechanism for efficiently applying a large list of filters during query execution. To implement this, Kyvos has introduced a new function in the MDX query named CUSTOMKYVOSFILTER. Inside the CUSTOMKYVOSFILTER function, a GUID (Globally Unique Identifier) is present, which is mapped to the filter values present in the JSON or CSV file. You can execute the MDX query using REST, any external tools, or by utilizing the Query Playground page.

This mechanism allows you to either directly provide filter values in the MDX query or specify a list of filters in a JSON/CSV file by modifying the code and providing the file path. This file, which must be accessible to the JAR on the BI Server node, contains the necessary filter-related details.

A JAR is created in which you can either hardcode filter values or upload filters in a JSON or CSV file by providing its path in the code.

Functions Signature

CUSTOMKYVOSFILTER (fieldName, firstvalue, secondvalue, operator)

Parameter details of CUSTOMKYVOSFILTER

Parameter	Description

Parameter	Description
fieldName	Fully qualified name of Level/Attribute of a Dimension
firstValue	GUID/ID, which will be used to map the actual list of values in the callback code
secondvalue	In case needed based on operator
operator	INLIST/NOTINLIST

The following is a sample MDX query code using the CUSTOMKYVOSFILTER.

SELECT {{[Measures].[MeasureName]}} ON COLUMNS, NON EMPTY HEAD(NONEMPTY(HIERARCHIZE({{ADDCALCULATEDMEMBERS({[Dimension].[Hierarchy].[Level]})}}),{[Measures].[MeasureName]}),10000) ON ROWS FROM (SELECT CUSTOMKYVOSFILTER([Dimension].[Hierarchy].[level],"GUID"," ","AND","INLIST") ON COLUMNS FROM [SModelName])

Steps to implement and setup callback code in Kyvos

Create a JAVA class that should implement in the JAVA callback interface as a part of the callback code.
QueryFilters parameter holds all the filters applied in the query. The callback code iterates over all the filters to identify those that need resolution via an appropriate flag in the filter object. Subsequently, every callback filter is resolved against the received GUID/ID, replacing it with the actual list of values within the same filter object before returning it.
The JAVA class created in Step 1 needs to be packaged in a JAR file. This JAR file needs to be uploaded in Kyvos via Kyvos Manager. Along with jar, you must configure the name of the class (full qualified name including package name) created in step 1 in Kyvos Manager.

Sample Code

package cohortfilters;
 
import com.kyvos.commons.callback.query.IFilterCallback;
import com.kyvos.commons.entity.olap.viewer.Filter;
import com.kyvos.commons.entity.olap.viewer.Filters;
import com.kyvos.commons.queryanalyze.QueryInfoBean;
import java.io.FileReader;
import java.util.Arrays;
import java.util.Iterator;
import java.util.logging.SimpleFormatter;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.json.simple.parser.JSONParser;
 
public class CallBack implements IFilterCallback {

 
   public Filters getResolvedFilters(Filters queryFilters, QueryInfoBean queryInfoBean) {
      Filter filter = (Filter)queryFilters.getFilters().get(0);
      String guid = filter.getFirstValue();
      String userName = queryInfoBean.getUserName();
      String values = getValueByUsernameAndGUID(userName, guid);
      filter.setFirstValue(values);
      return queryFilters;
   }
 
   public static String getValueByUsernameAndGUID(String userName, String guid) {
         String location = "pathToFiltersJson";
         FileReader reader = new FileReader(location);
         JSONParser parser = new JSONParser();
         JSONObject jsonObject = (JSONObject)parser.parse(reader);
         JSONArray adminArray = (JSONArray)jsonObject.get(userName);
         if (adminArray != null) {
            Iterator var8 = adminArray.iterator();
            while(var8.hasNext()) {
               Object adminObj = var8.next();
               JSONObject admin = (JSONObject)adminObj;
               JSONArray guidArray = (JSONArray)admin.get(guid);
               if (guidArray != null && guidArray.size() > 0) {
                  JSONObject valueObj = (JSONObject)guidArray.get(0);
                  return (String)valueObj.get("VALUE");
               }
            }
         }
      return null;
   }
 
}

Sample Filter File

 {
   "userName":[
      {
         "GUID1":[
            {
               "VALUE":"'',''"
            }
         ]
      }
,
      {
         "GUID2":[
            {
               "VALUE":"'',''"
            }
         ]
      }
   ]
}