Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

Overview

1. Specify required imports

2. Prepare the request

3. Call the method executeOlapQuery

4. Use the response

Example

1. Specify required imports.

   import org.olap4j.Cell; import org.olap4j.CellSet; import org.olap4j.OlapConnection; import org.olap4j.OlapStatement; import org.olap4j.Position;

2. Prepare the request.

   Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver"); /* Append “xmlaKyvos” to your Kyvos URL in below statement. Catalog is the folder name where the semantic model is saved to run MDX query else give [Root] here. Specify “KYVOS” under DataSource.*/      Connection connection = DriverManager.getConnection(“jdbc:xmla:Server=http://localhost:8080/kyvos/xmlaKyvos;User=Admin;Password=Admin;Catalog=FlightShopDetails;DataSource=KYVOS"); OlapConnection olapConnection = connection.unwrap(OlapConnection.class); OlapStatement statement = olapConnection.createStatement(); String mdx = "SELECT [Measures].[Miles Traveled] DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON COLUMNS,NON EMPTY CROSSJOIN( [Travel Agent].[Hierarchy].[Travel Agent].AllMembers, [Departure Airport].[Hierarchy].[Departure Country].AllMembers) DIMENSION PROPERTIES [MEMBER_UNIQUE_NAME],[MEMBER_CAPTION] ON ROWS FROM [Flight Shop Analysis]";

3. Call the method to execute OlapQuery.

   CellSet cellSet = statement.executeOlapQuery(String mdx, boolean getMemberDetails); /*Pass getMemberDetails as false to avoid sending further request for each member to fetch extra information like member’s parent, child count, etc. This would ensure a faster response.*/

4. Method to execute MDX and return generate response in a file.

   /**  Method to execute mdx and return generate response in a file.  @param query              MDX Query  @param localProp        fileLocation, fileLocation  @param queryProperties  @throws OlapException  */  public void executeOlapTabularQuery(String mdx, Map localProp, Map queryProperties) throws OlapException 

   
   Sample

   package org.olap4j; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.HashMap; public class Olap4jExecutor { //Location where user want to generate their query response file static String outputFileLocation = ""; //Name of file in which query response will be writte. Ex: <queryResponse> static String outputFileName = ""; //true/false if user requires the response in zipped format static String zipped = ""; //CSV/JSON format in which query response needs to be generated static String outputFormat = ""; //Below parameters can be used for defining custom separator for response static String fieldSeparator = ""; static String lineSeparator = ""; static String enclosure = ""; static String columnHeaderSeparator = ""; //true/false if following details are required in response static String includeHeader = ""; static String keepMeasureFormatting = ""; //HIERARCHICAL/CLASSICAL type of response user want to be generated. Default response is of Hierarchical type static String tabularViewType = ""; //Integer value, If user want the the limited records in the response file static String maxRow = ""; static String maxColumn = ""; static HashMap<String, String> localProps = new HashMap<>(); static HashMap<String, String> serverProps = new HashMap<>(); static String connectionURL = "jdbc:xmla:Server=" + "http://<WebPortalIP>:<WebPortalPort>/kyvos/xmlaKyvos" + ";User=" + "Admin" + ";Password=" + "Admin" + ";Catalog=" + "<QuerySModelName>" + ";DataSource=Kyvos"; public static void main(String[] args) throws ClassNotFoundException, SQLException { try { Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver"); String connectionURL = "jdbc:xmla:Server=" + "http://<WebPortalIP>:<WebPortalPort>/kyvos/xmlaKyvos" + ";User=" + "Admin" + ";Password=" + "Admin" + ";Catalog=" + "<QuerySModelName>" + ";DataSource=Kyvos"; try (Connection connection = DriverManager.getConnection(connectionURL); OlapConnection olapConnection = connection.unwrap(OlapConnection.class); OlapStatement statement = olapConnection.createStatement()) { queryParam(); String mdxQuery = ""; queryExecutor(mdxQuery, statement); } catch (Exception e) { // Handle the exception appropriately, log or display a user-friendly message e.printStackTrace(); } } catch (ClassNotFoundException e) { // Handle class not found exception e.printStackTrace(); } } private static void queryExecutor(String query, OlapStatement statement) throws OlapException { statement.executeOlapTabularQuery(query, localProps, serverProps); System.out.println("Query Execution completed!!! Please find result at following location: " + outputFileLocation); } private static void queryParam() { serverProps.put(Enums.RequestType.RequestProperties.OUTPUT_FORMAT, outputFormat); serverProps.put(Enums.RequestType.RequestProperties.ZIPPED, zipped); serverProps.put(Enums.RequestType.RequestProperties.INCLUDE_HEADER, includeHeader); serverProps.put(Enums.RequestType.RequestProperties.KEEP_MEASURE_FORMATTING, keepMeasureFormatting); serverProps.put(Enums.RequestType.RequestProperties.ENCLOSURE, enclosure); serverProps.put(Enums.RequestType.RequestProperties.OUTPUT_FILE_NAME, outputFileName); serverProps.put(Enums.RequestType.RequestProperties.COLUMN_HEADER_SEPARATOR, columnHeaderSeparator); serverProps.put(Enums.RequestType.RequestProperties.FIELD_SEPARATOR, fieldSeparator); serverProps.put(Enums.RequestType.RequestProperties.LINE_SEPARATOR, lineSeparator); serverProps.put(Enums.RequestType.RequestProperties.MAX_COLUMN, maxColumn); serverProps.put(Enums.RequestType.RequestProperties.MAX_ROW, maxRow); serverProps.put(Enums.RequestType.RequestProperties.TABULAR_VIEW_TYPE, tabularViewType); outputFileName = outputFileName + (zipped.equalsIgnoreCase("true") ? ".zip" : outputFormat); localProps.put(Enums.RequestType.RequestProperties.OUTPUT_FILE_LOCATION, outputFileLocation); localProps.put(Enums.RequestType.RequestProperties.OUTPUT_FILE_NAME, outputFileName); } }

5. Use the response.

   private static void getResponse(CellSet cellSet){             List<CellSetAxis> cellSetAxes = cellSet.getAxes();             for (Position row : cellSetAxes.get(0))             {                   if(cellSetAxes.size() > 1)                   {                         for (Position column : cellSetAxes.get(1))                         {                               for (Member member : row.getMembers())                               {                                   System.out.print(member.getName() + ":" + " ");                               }                               for (Member member : column.getMembers())                               {                                   System.out.println(member.getName() + "=" + " ");                               }                               final Cell cell = cellSet.getCell(row, column);                               System.out.println(cell.getValue());                         }                   }                   else                   {                         for (Member member : row.getMembers())                         {                               System.out.println(member.getName());                         }                         final Cell cell = cellSet.getCell(row);                         System.out.println(cell.getValue());                         System.out.println();                   }                         }

Token based SSO

In the above example, basic authentication is used with a connection URL. But sometimes, users do not want to use their credentials in some scenarios. To deal with such scenarios, Kyvos provides another authentication mechanism which is token-based SSO. Users can invoke Kyvos Login REST API to get a token and then use that token with a connection URL. Users will have to perform the following steps to use token-based SSO.

1. Get a token using passwordless authentication in login REST API.
   To get the token using passwordless authentication, make the following configuration changes:

   1. Set the value for the authentication provider as HOST_APP in the config.json file.

   2. Provide the value for the userAuthenticationVerificationUrl property in the config.json. This property is used for password less SSO, details are mentioned in below section.

   3. Provide value for EXT_AUTH_HEADER_ID property in the properties file. EXT_AUTH_HEADER_ID defines parameter name(request parameter name) against which user token would be provided.

   4. Get token with username and token (password less authentication)

      In this authentication mode (password less), Kyvos relies on the verification server for user authentication. Below is the flow diagram and its description: 
      

      
      
      

      • To get a session ID from Kyvos, the client application will have to call Kyvos login REST API by passing the following parameters:
        username, bearer token (GUID), mode=INTEGRATION, and optional custom data.
        http:<hostname>:<port>/kyvos/rest/login?username=&token=&mode=INTEGRATION&customData= 
        
        

      • Kyvos validates parameter values received in the login REST by making the REST call to the verification server on userAuthenticationVerificationUrl with username, token, and custom data.

      • The verification server validates the data, ensures that given parameters are valid, and returns true or false based on successful validation or failure.

      • If this verification REST call returns true as the HTTP response, then the SSO is successful, and the user session is created in Kyvos. Kyvos login REST call would return the successful response for this user session. For example, on successful authentication, REST API produces the response as:
        
        

        {  "RESPONSE": { "SUCCESS": "90DB354A-EE34-6827-B3E2-2BAAECE5C2E2" } }

      • In the above response, the value of the SUCCESS key is the token. Users can use this token for authentication with the connection URL as mentioned in the below example.

      • If the verification REST call returns a false response, the Kyvos login REST call will not issue any success response and SSO will fail.
        
        

2. Using Olap4j API with a token-based authentication (password less authentication)

Users can obtain a token from login REST API and can use the same for the authentication in Olap4j API. The user has to pass this token as a query parameter in the connection URL.
The parameter name should be the value of EXT_AUTH_HEADER_ID provided in the kyvosclient.properties file.
The connection URL should be in the below-mentioned format:
http://www.kyvosinsights.com:8081/kyvos/xmlaKyvos?<EXT_AUTH_HEADER_ID_VALUE>=<token returned by login REST API>

Example:
If EXT_AUTH_HEADER_ID=username and token=4EB72FAD-B899-D331-BB86-A8895883A870
then XMLA connection URL will be: http://www.kyvosinsights.com:8081/kyvos/xmlaKyvos?username=4EB72FAD-B899-D331-BB86-A8895883A870

You can use this connection URL in the code provided in the examples while using the token, do not provide any username and password.

Examples

Code to get the token from REST API.

Use the value of the SUCCESS key from the response as a token in the connection URL.

Using token in connection URL.

if EXT_AUTH_HEADER_ID_VALUE=username and token=49CD3E8C-1D9F-1025-5B5D-19393E8DEBA3
then XMLA connection URL will be:
<Kyvos_URL>/xmlaKyvos?username=49CD3E8C-1D9F-1025-5B5D-19393E8DEBA3

This connection URL can be used with the sample code (Prepare the request) provided in the above example, and there is no need to provide User=Admin;Password=Admin; in this case.