analytics/sdk/fmdsdk/java/StartToFinish.java

/**
Licensed Materials - Property of IBM

IBM Cognos Products: DOCS

(C) Copyright IBM Corp. 2013

US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP
Schedule Contract with IBM Corp.
*/


import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;

import com.ibm.json.java.JSONArray;
import com.ibm.json.java.JSONObject;

/**
 * This code creates a simple model corresponding to the GOSALES example
 * showing sales by product type / product line.
 */
public class StartToFinish {
	
	/**
	 * URL to the server with the FMD SDK installed.
	 */
	final private static String BI_SERVER_URL = "http://localhost:9300/p2pd/servlet/dispatch/";
	
	/**
	 * The URL part that directs requests to the FMD SDK.
	 */
	final private static String BASE_URL = BI_SERVER_URL + "FmCommand/";
	
	/**
	 * Path to the model location.
	 */
	final private static String OUTPUT_FILE = "c:/gosales_sdk.fmd"; 
	
	/**
	 * POJO code that creates HTTP request passing data in JSON format.
	 * <p>Should the request return a error or a warning, they are written out to the system error stream.</p>
	 * @param keyword The URL path segment used to identify the request
	 * @param method The HTTP method to use ("GET", "POST", "PUT", "DELETE").
	 * @param json The data to be passed with the request, might be <code>null</code>
	 * @param modelObjectId The Id of the model object targeted by this request, may be <code>null</code>
	 * @return The JSON data returned by the SDK. Might be null. 
	 */
	static protected JSONObject callFMD(String keyword, String method, JSONObject json, String modelObjectId) {
		OutputStream os = null;
		BufferedReader br = null;
		HttpURLConnection conn = null;
		try {
			URL url = (modelObjectId == null) ? new URL(BASE_URL + keyword) : new URL(BASE_URL + keyword + "/"+modelObjectId);
			conn = (HttpURLConnection) url.openConnection();
			conn.setDoOutput(true);
			conn.setRequestMethod(method);
			// By default, server only allows "GET" and "POST" requests. Use X-header
			// to pass other verbs.
			if ("GET".equals(method) || "POST".equals(method))
				conn.setRequestMethod(method);
			else if ("PUT".equals(method) || "DELETE".equals(method)) {
				conn.setRequestMethod("POST");
				conn.setRequestProperty("X-HTTP-Method-Override", method);
			}
			conn.setRequestProperty("Content-Type", "application/json; charset=utf-8");
			
			if (json != null) {
				os = conn.getOutputStream();
				os.write(json.serialize().getBytes());
				os.flush();
			}
			
			br = new BufferedReader(new InputStreamReader(conn.getInputStream(), "UTF-8"));
			String output;
			StringBuffer tmp = new StringBuffer();
			while ((output = br.readLine()) != null) {
				tmp.append(output);
			}
			
			JSONObject result = JSONObject.parse(tmp.toString());
			JSONArray errorsArray = (JSONArray) result.get("errors");
			if (errorsArray != null)
				System.err.println("Errors for request \"" + keyword + "\": " + errorsArray.toString());
			JSONArray warningsArray = (JSONArray) result.get("warnings");
			if (warningsArray != null)
				System.err.println("Warnings for request \"" + keyword + "\": " + warningsArray.toString());
			
		    return result;
		} catch (MalformedURLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (json != null)
				json.clear();
			try {
				if (os != null)
					os.close();
				if (br != null)
					br.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			if (conn != null)
				conn.disconnect();
		}
		return null;
	}
	
	/**
	 * Convenience function used to create a model element. See {@link #callFMD(String, String, JSONObject, String)}
	 * for description of arguments.
	 * @param keyword The URL path segment used to identify the request
	 * @param json The data to be passed with the request, might be <code>null</code>
	 * @return The Id of the model object, if returned by the SDK. Might be null. 
	 */
	static protected String fmdCreate(String keyword, JSONObject json) {
		JSONObject result = callFMD(keyword, "POST", json, null);
		return (String) result.get("id");
	}
	
	/**
	 * A convenience function wrapping "save model to a stream" request. 
	 * @param file The local file in which to save the stream contents
	 * @param modelObjectId The Id of the model object targeted by this request
	 * @return <code>true</code> if model was saved successfully
	 */
	static protected boolean saveToFile(File file, String modelObjectId) {
		BufferedReader replyReader = null;
		HttpURLConnection conn = null;
		BufferedWriter fileWriter = null;
		try {
			URL url = new URL(BASE_URL + "model_save_stream/" + modelObjectId);
			conn = (HttpURLConnection) url.openConnection();
			conn.setRequestMethod("POST");
			conn.setUseCaches(false);
			conn.setRequestProperty("Cache-Control", "no-cache");
			conn.setRequestProperty("Connection", "Keep-Alive");
			conn.setRequestProperty("Content-Type", "text/plain");	
			
			replyReader = new BufferedReader(new InputStreamReader(conn.getInputStream(), "UTF-8"));
			String output;
			fileWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file),"UTF-8"));
			while ((output = replyReader.readLine()) != null) {
				fileWriter.write(output);
				fileWriter.newLine();
			}
			fileWriter.flush();
		    return true;
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				if (replyReader != null)
					replyReader.close();
				if (fileWriter != null)
					fileWriter.close();
				if (conn != null)
					conn.disconnect();
			} catch (IOException ex) {
				ex.printStackTrace();
			}
		}
		return false;
	}

	
	/**
	 * Convenience method creating a pair of a text and locale.
	 */
	static protected JSONArray localizedText(String text, String locale) {
		JSONObject tmp = new JSONObject();
		tmp.put("text", text);
		tmp.put("locale", locale);

		JSONArray nameArray = new JSONArray(1);
		nameArray.add(tmp);
		return nameArray;
	}

	public static void main(String[] args) throws IOException {
		// create model in memory
		JSONObject json = new JSONObject();
		json.put("name", "Project");
		json.put("namespace", "Model");
		json.put("locale", "EN");
		String modelId = fmdCreate("model_new", json);
		
		// datasource
		json.put("parent", modelId);
		json.put("name", "great_outdoors_sales");
		json.put("cmDataSource", "great_outdoors_sales");
		json.put("catalog", "GOSALES");
		json.put("schema", "gosales");
		json.put("queryType", "relational");
		json.put("functionSetId", "V_SQLServer");
		json.put("interface", "SS");
		String idDataSource = fmdCreate("datasource", json);
		
		// relational dimension
		json.put("parent", modelId);
		json.put("name", localizedText("Products", "EN"));
		String idDimension = fmdCreate("relational_dimension", json);
		
		// level - Line
		json.put("parent", idDimension);
		json.put("name", localizedText("Line", "EN"));
		String idLevel = fmdCreate("level", json);
		
		// query item - Product Line Code
		json.put("parent", idLevel);
		json.put("name", localizedText("Product Line Code", "EN"));
		json.put("usage", "identifier");
		json.put("datatype", "int32");
		json.put("precision", "10");
		json.put("scale", "0");
		json.put("isLevelKey", "true");
		String idProductLineCode = fmdCreate("query_item", json);

		// query item - Product Line
		json.put("parent", idLevel);
		json.put("name", localizedText("Product Line En", "EN"));
		json.put("usage", "identifier");
		json.put("datatype", "nVarChar");
		json.put("size", "30");
		String idProductLine = fmdCreate("query_item", json);
		
		// role
		json.put("parent", idProductLine);
		json.put("name", localizedText("_memberCaption", "EN"));
		fmdCreate("query_item_role", json);
		
		// level - Type
		json.put("parent", idDimension);
		json.put("name", localizedText("Type", "EN"));
		String idTypeLevel = fmdCreate("level", json);

		// query item - Product Type Code
		json.put("parent", idTypeLevel);
		json.put("name", localizedText("Product Type Code", "EN"));
		json.put("usage", "identifier");
		json.put("datatype", "int32");
		json.put("precision", "10");
		json.put("scale", "0");
		json.put("isLevelKey", "true");
		String idProductTypeCode = fmdCreate("query_item", json);
		
		// query item - Product Type Code
		json.put("parent", idTypeLevel);
		json.put("name", localizedText("Product Type En", "EN"));
		json.put("usage", "identifier");
		json.put("datatype", "nVarChar");
		json.put("size", "40");
		String idProductType = fmdCreate("query_item", json);
		
		// role
		json.put("parent", idProductType);
		json.put("name", localizedText("_memberCaption", "EN"));
		fmdCreate("query_item_role", json);
		
		// create relational hierarchy
		json.put("parent", idDimension);
		json.put("name", localizedText("ByType", "EN"));
		json.put("multiRoot", "false");
		json.put("rootMember", "All");
		json.put("rootCaption", "All");
		json.put("isParentChild", "false");
		json.put("defaultHierarchy", "true");
		
		// have a level reference
		JSONArray jsonLevelRefs = new JSONArray();
		jsonLevelRefs.add(idLevel);
		jsonLevelRefs.add(idTypeLevel);
		json.put("levels", jsonLevelRefs);

		fmdCreate("relational_hierarchy", json);
		
		// physical definitions - table PRODUCT_LINE
		json.put("parent", idDimension);
		json.put("name", "PRODUCT_LINE");
		json.put("datasource", idDataSource);
		String idTableLine = fmdCreate("physical_table", json);
		
		// physical definitions - table PRODUCT_TYPE
		json.put("parent", idDimension);
		json.put("name", "PRODUCT_TYPE");
		json.put("datasource", idDataSource);
		String idTableType = fmdCreate("physical_table", json);
		
		// physical definitions - table PRODUCT
		json.put("parent", idDimension);
		json.put("name", "PRODUCT");
		json.put("datasource", idDataSource);
		String idTableProduct = fmdCreate("physical_table", json);
		
		// create a joint with an association PRODUCT_TYPE <-> PRODUCT_LINE
		json.put("parent", idDimension);
		json.put("name", "FK_PRODUCT_TYPE_PRODUCT_LINE");
		json.put("leftTable", idTableLine);
		json.put("rightTable", idTableType);
		json.put("leftMinCardinality", "one");
		json.put("leftMaxCardinality", "one");
		json.put("rightMinCardinality", "one");
		json.put("rightMaxCardinality", "many");
		String idJoin = fmdCreate("physical_join", json);
		
		json.put("parent", idJoin);
		json.put("leftColumn", "PRODUCT_LINE_CODE");
		json.put("operator", "equals");
		json.put("rightColumn", "PRODUCT_LINE_CODE");
		fmdCreate("physical_association", json);
		
		// create a joint with an association PRODUCT_TYPE <-> PRODUCT
		json.put("parent", idDimension);
		json.put("name", "FK_PRODUCT_PRODUCT_TYPE");
		json.put("leftTable", idTableType);
		json.put("rightTable", idTableProduct);
		json.put("leftMinCardinality", "one");
		json.put("leftMaxCardinality", "one");
		json.put("rightMinCardinality", "one");
		json.put("rightMaxCardinality", "many");
		String idJoinProduct = fmdCreate("physical_join", json);
		
		json.put("parent", idJoinProduct);
		json.put("leftColumn", "PRODUCT_TYPE_CODE");
		json.put("operator", "equals");
		json.put("rightColumn", "PRODUCT_TYPE_CODE");
		fmdCreate("physical_association", json);

		// query items mapping - 1
		json.put("parent", idDimension);
		json.put("columnName", "PRODUCT_LINE_CODE");
		json.put("table", idTableLine);
		json.put("queryItem", idProductLineCode);
		fmdCreate("query_item_mapping", json);
		
		// query items mapping - 2
		json.put("parent", idDimension);
		json.put("columnName", "PRODUCT_LINE_EN");
		json.put("table", idTableLine);
		json.put("queryItem", idProductLine);
		fmdCreate("query_item_mapping", json);
		
		// query items mapping - 3
		json.put("parent", idDimension);
		json.put("columnName", "PRODUCT_TYPE_CODE");
		json.put("table", idTableType);
		json.put("queryItem", idProductTypeCode);
		fmdCreate("query_item_mapping", json);
		
		// query items mapping - 4
		json.put("parent", idDimension);
		json.put("columnName", "PRODUCT_TYPE_EN");
		json.put("table", idTableType);
		json.put("queryItem", idProductType);
		fmdCreate("query_item_mapping", json);
		
		// cube
		json.put("parent", modelId);
		json.put("name", localizedText("ProductSales", "EN"));
		String idCube = fmdCreate("cube", json);
		
		// measure dimension
		json.put("parent", idCube);
		json.put("name", localizedText("Measures", "EN"));
		String idMeasureDimension = fmdCreate("measure_dimension", json);
		
		// measure Quantity
		json.put("parent", idMeasureDimension);
		json.put("name", localizedText("Quantity", "EN"));
		json.put("usage", "fact");
		json.put("datatype", "int32");
		json.put("precision", "10");
		json.put("scale", "0");
		json.put("regularAggregate", "sum");
		String idMeasure = fmdCreate("measure", json);
		
		// set it as a default measure in the measure dimension
		json.put("defaultMeasure", idMeasure);
		callFMD("measure_dimension", "PUT", json, idMeasureDimension);
		
		// physical definition - table ORDER_DETAILS
		JSONObject tableOrderDetails = new JSONObject();
		tableOrderDetails.put("parent", idMeasureDimension);
		tableOrderDetails.put("name", "ORDER_DETAILS");
		tableOrderDetails.put("datasource", idDataSource);
		String idTableOrderDetails = fmdCreate("physical_table", tableOrderDetails);
		
		// physical definition - query item mapping QUANTITY
		json.put("parent", idMeasureDimension);
		json.put("columnName", "QUANTITY");
		json.put("table", idTableOrderDetails);
		json.put("queryItem", idMeasure);
		fmdCreate("query_item_mapping", json);

		// relationship Products<-->Measures
		json.put("parent", idCube);
		json.put("name", "Products-Measures");
		json.put("leftObjectRef", idDimension);
		json.put("leftMinCardinality", "one");
		json.put("leftMaxCardinality", "one");
		json.put("rightObjectRef", idMeasureDimension);
		json.put("rightMinCardinality", "one");
		json.put("rightMaxCardinality", "many");
		
		JSONArray expressionParts = new JSONArray();
		JSONObject part1 = new JSONObject();
		part1.put("columnName", "PRODUCT_NUMBER");
		part1.put("tableName", "PRODUCT");
		part1.put("dataSourceRef", idDataSource);
		part1.put("objectRef", idDimension); // relational dimension
		expressionParts.add(part1);
		
		expressionParts.add("=");
		
		JSONObject part2 = new JSONObject();
		part2.put("columnName", "PRODUCT_NUMBER");
		part2.put("tableName", "ORDER_DETAILS");
		part2.put("dataSourceRef", idDataSource);
		part2.put("objectRef", idMeasureDimension); // measure dimension
		expressionParts.add(part2);
		
		json.put("expression", expressionParts);
		fmdCreate("relationship", json);
		
		// save model
		saveToFile(new File(OUTPUT_FILE), modelId);
		
		// deploy cube
		json.put("contentManagerModelPath", "~/folder[@name='My Folders']");
		json.put("packageName", "FMDSDKdemo");
		json.put("refreshDataSources", "true");
		json.put("cube", idCube);
		callFMD("cube_deploy", "POST", json, null);
		
		// register cube
		json.put("cube", idCube);
		callFMD("cube_register", "POST", json, null);
		
		// start the cube
		json.put("cube", idCube);
		callFMD("cube_start", "POST", json, null);
		
		System.out.println("Cube is ready.");
	}
}