26014 - How to Create a JSP OLAPTableView That Connects to an OLAP Cube with a Customized Query

Sample 26014: How to Create a JSP OLAPTableView That Connects to an OLAP Cube with a Customized Query

Instructions

Build a new Web application project in SAS AppDev Studio by using the Information Map OLAPTableView Servlet template.
The template will generate a servlet and a JSP file, which, by default, are named InfoMapOLAPTableViewExampleControllerServlet.java and InfoMapOLAPTableViewExampleViewer.jsp. Replace the contents of these files with the code below.
Execute the servlet on the test Tomcat server.

Tip: For help with building a Web application project and testing a Web application, see SAS Note 32218.

Code for InfoMapOLAPTableViewExampleViewer.jsp


<%@ taglib uri="http://www.sas.com/taglib/sas" prefix="sas" %>
<%@ page pageEncoding="UTF-8"%>
<html>
<head>
	<link href="styles/sasComponents.css" rel="STYLESHEET" type="text/css">
</head>
<body>

	<sas:OLAPTableViewComposite id="sas_OLAPTableView1_InfoMapOLAPTableViewExample" model="sas_model_InfoMapOLAPTableViewExample" actionProvider="sas_actionProvider_InfoMapOLAPTableViewExample" scope="session">
		<sas:OLAPTableView/>
	</sas:OLAPTableViewComposite>

</body>
</html>

Code for InfoMapOLAPTableViewExampleControllerServlet.java

Required changes

Edit the value of the MAP_NAME field to point to an information map. The code uses the following as an example:
private static final String MAP_NAME = "SBIP://Foundation/BIP Tree/ReportStudio/Maps/OrionStar Map";
Edit the contents of the rows, columns, and slicers lists to match items in your information map. Add one or more items for rows, and zero or more items for slicer.

rows.add("[Data item name]");
columns.add("[Data item name]");
slicers.add("[Data item name]");

If you did not use the default values during the generation of the project, then you might need to update the following values.
- The package for the servlet.
  package servlets;
- The servlet name.
  public class InfoMapOLAPTableViewExampleControllerServlet ...
- The URL mapping for the action provider, which must match the servlet mapping for the servlet.
  sas_actionProvider.setControllerURL(request.getContextPath()+ "/InfoMapOLAPTableViewExample");


package servlets;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import listeners.ExamplesSessionBindingListener;

import com.sas.actionprovider.HttpActionProvider;
import com.sas.iquery.dataretrieval.QueryConnector;
import com.sas.iquery.metadata.IntelligentQueryMetadataService;
import com.sas.iquery.metadata.IntelligentQueryMetadataServiceFactory;
import com.sas.iquery.metadata.MetadataException;
import com.sas.iquery.metadata.business.BusinessModel;
import com.sas.iquery.metadata.business.BusinessQuery;
import com.sas.iquery.metadata.business.DataItem;
import com.sas.iquery.metadata.business.DataItemReference;
import com.sas.iquery.metadata.business.DataSelection;
import com.sas.iquery.metadata.business.DataSelectionFactory;
import com.sas.iquery.metadata.business.InformationMap;
import com.sas.iquery.metadata.business.Role;
import com.sas.iquery.metadata.business.SelectedItem;
import com.sas.iquery.metadata.business.step.StepInterface;
import com.sas.services.discovery.DiscoveryService;
import com.sas.services.discovery.DiscoveryServiceInterface;
import com.sas.services.discovery.ServiceTemplate;
import com.sas.services.information.metadata.PathUrl;
import com.sas.services.session.SessionContextInterface;
import com.sas.services.session.SessionServiceInterface;
import com.sas.services.user.UserContextInterface;
import com.sas.services.user.UserServiceInterface;
import com.sas.servlet.util.BaseUtil;
import com.sas.storage.iquery.BusinessQueryToOLAPDataSetAdapter;
import com.sas.web.keys.ComponentKeys;

public class InfoMapOLAPTableViewExampleControllerServlet extends
		javax.servlet.http.HttpServlet {
	private static final String MAP_NAME = "SBIP://Foundation/BIP Tree/ReportStudio/Maps/OrionStar Map";
	// Declare a default version ID since parent class implements
	// java.io.Serializable
	private static final long serialVersionUID = 1L;
	// Global webapp Strings
	private static final String ACTION_PROVIDER = "sas_actionProvider_InfoMapOLAPTableViewExample";
	// Global webapp InfoMap variables
	private static final String SAS_MODEL = "sas_model_InfoMapOLAPTableViewExample";

	/*
	 * doPost() Respond to the Post message.
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// Note: Calling doGet to provide same behavior to POST and GET HTTP
		// methods.
		doGet(request, response);
	}

	/*
	 * doGet() Respond to the Get message.
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// Note: Add User DO_GET code here
		HttpSession session = request.getSession();

		// Ensure character set is specified before calling
		// response.getWriter().
		String charset = BaseUtil.getOutputCharacterEncoding(request);
		response.setContentType("text/html; charset=" + charset);
		// Setup the ActionProvider
		HttpActionProvider sas_actionProvider = null;
		synchronized (session) {
			if (session != null) {
				sas_actionProvider = (HttpActionProvider) session
						.getAttribute(ACTION_PROVIDER);
			}
			// if ActionProvider is null, create one and put it on the session
			if (sas_actionProvider == null) {
				sas_actionProvider = new HttpActionProvider();
				sas_actionProvider.setLocale(request.getLocale());
				sas_actionProvider.setControllerURL(request.getContextPath()
						+ "/InfoMapOLAPTableViewExample");
				sas_actionProvider.setName(ACTION_PROVIDER);
				// store object in its scope
				if (session != null)
					session.setAttribute(ACTION_PROVIDER, sas_actionProvider);
			}
			// else execute the ActionProvider command
			else {
				sas_actionProvider.executeCommand(request, response, response
						.getWriter());
			}
		}

		synchronized (session) {
			// Setup the Business Query model adapter
			BusinessQueryToOLAPDataSetAdapter adapter = null;
			if (session != null) {
				adapter = (BusinessQueryToOLAPDataSetAdapter) session
						.getAttribute(SAS_MODEL);
			}
			if (adapter == null) {
				try {
					// Obtain the local discovery service
					DiscoveryServiceInterface discoveryService = DiscoveryService
							.defaultInstance();

					// Use the discovery services to find a user service.
					UserServiceInterface userService = (UserServiceInterface) discoveryService
							.findService(new ServiceTemplate(
									new Class[] { UserServiceInterface.class }));

					// Get a user context from the user service by logging in.
					// The initial parameters are obtained from the web.xml
					// file.
					// Note: A password must be set in the web.xml file.
					ServletConfig sc = getServletConfig();
					String domain = sc.getInitParameter("metadata-domain");
					String username = sc.getInitParameter("metadata-userid");
					String password = sc.getInitParameter("metadata-password");
					UserContextInterface userContext = userService.newUser(
							username, password, domain);
					if (session != null) {
						ExamplesSessionBindingListener.getInstance(session)
								.addUserContext(userContext);
					}

					// Use the discovery services to find a session service.
					SessionServiceInterface sessionService = (SessionServiceInterface) discoveryService
							.findService(new ServiceTemplate(
									new Class[] { SessionServiceInterface.class }));

					// Create a session for this user and bind it to the user
					// context.
					SessionContextInterface sessionContext = sessionService
							.newSessionContext(userContext);
					userContext.setSessionContext(sessionContext);
					if (session != null) {
						ExamplesSessionBindingListener.getInstance(session)
								.addSessionContext(sessionContext,
										"InfoMapOLAPTableViewExample_lock");
					}

					// Get a new query service and information map
					IntelligentQueryMetadataService queryService = (IntelligentQueryMetadataService) IntelligentQueryMetadataServiceFactory
							.newService();
					InformationMap informationMap = queryService
							.getInformationMap(sessionContext, new PathUrl(
									MAP_NAME));
					DataSelection dataSelection = DataSelectionFactory
							.newDataSelection(informationMap);

					// Define the data items that should exist on the query. The
					// value should match the item name in the information map
					List rows = new ArrayList();
					List columns = new ArrayList();
					List slicers = new ArrayList();
					Hashtable filters = new Hashtable();

					// Add items to row, column or slicer. Rows and columns must
					// have one or more items. Slicer can have zero or more
					// items
					rows.add("Year-quarter-month");
					columns.add("Demographics");
					columns.add("Sum of Quantity");
					columns.add("Sum of Total Cost");

					// Initialize the model with the rows/column/slicer and
					// member filter selections
					initializeBusinessQuery(dataSelection, true, rows, columns,
							slicers, filters);

					// Create the model adapter and set it on the session
					adapter = new BusinessQueryToOLAPDataSetAdapter();
					adapter.setModel(dataSelection);
					// store object in its scope
					if (session != null) {
						session.setAttribute(SAS_MODEL, adapter);
						ExamplesSessionBindingListener.getInstance(session)
								.addAdapter(adapter);
					}
				} catch (Exception e) {
					throw new ServletException(e);
				}
			}
		}

		// Forward the request to the JSP for display
		String sas_forwardLocation = request
				.getParameter(ComponentKeys.FORWARD_LOCATION);
		if (sas_forwardLocation == null) {
			sas_forwardLocation = "/InfoMapOLAPTableViewExampleViewer.jsp";
		}
		RequestDispatcher rd = getServletContext().getRequestDispatcher(
				sas_forwardLocation);
		rd.forward(request, response);

	}

	/* Print the mdx equivalent of a BusinessQuery */
	private void printQuery(BusinessQuery ds) {
		QueryConnector qc = new QueryConnector();
		try {
			String mdxQuery = qc.getPhysicalQuery(ds);
			System.out.println(mdxQuery);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	private DataItem getDataItem(BusinessQuery businessQuery, String label) {
		DataItem retval = null;
		BusinessModel bmodel = businessQuery.getBusinessModel();
		while (!(bmodel instanceof InformationMap)) {
			bmodel = bmodel.getParentBusinessModel();
		}
		// get all items in this model
		List dataItemList = bmodel.getObjects(false, DataItem.class);
		Iterator iter = dataItemList.iterator();
		while (iter.hasNext() && retval == null) {
			DataItem dataItem = (DataItem) iter.next();
			if (dataItem.getLabel().equalsIgnoreCase(label)) {
				retval = dataItem;
			}
		}
		return retval;
	}

	/* Remove all items from a BusinessQuery */
	public void clearQuery(BusinessQuery businessQuery) {
		List initiallySelected = businessQuery.getSelectedItems();
		for (int i = 0; i < initiallySelected.size(); i++) {
			SelectedItem selected = (SelectedItem) initiallySelected.get(i);
			try {
				businessQuery.removeSelectedItem(selected);
			} catch (MetadataException e) {
				e.printStackTrace();
			}
		}

	}

	public void initializeBusinessQuery(BusinessQuery businessQuery,
			boolean clearQuery, List rows, List columns, List slicers,
			Hashtable filters) {
		if (clearQuery) {
			clearQuery(businessQuery);
		}
		BusinessModel bmodel = businessQuery.getBusinessModel();
		// make sure we are the root business model
		while (!(bmodel instanceof InformationMap)) {
			bmodel = bmodel.getParentBusinessModel();
		}
		// get all items in this model
		List dataItemList = bmodel.getObjects(false, DataItem.class);

		// add items to rows, columns, and filters
		addItemsToRole(businessQuery, dataItemList, rows, Role.ROW, filters);
		addItemsToRole(businessQuery, dataItemList, columns, Role.COLUMN,
				filters);
		addItemsToRole(businessQuery, dataItemList, slicers, Role.BACKGROUND,
				filters);
	}

	/*
	 * Iterate through a list of items. If the item is found in the business
	 * model, add it to the query, on the role specified
	 */
	private void addItemsToRole(BusinessQuery businessQuery, List dataItemList,
			List items, Role role, Hashtable filters) {
		BusinessModel businessModel = businessQuery.getBusinessModel();
		Iterator i = items.iterator();
		for (; i.hasNext();) {
			String itemName = (String) i.next();
			for (Iterator iter = dataItemList.iterator(); iter.hasNext();) {
				DataItem dataItem = (DataItem) iter.next();
				if (dataItem.getLabel().equalsIgnoreCase(itemName)) {
					try {
						// See if the item already exists in the query
						DataItem newDataItem = lookForDataItemInExistingQuery(
								dataItem, businessQuery);
						// If it does not exist in query, check for an existing
						// reference
						if (null == newDataItem) {
							newDataItem = lookForExistingDataItemReference(
									businessModel, dataItem);
							// if a reference doesn't exist, create it, and add
							// it to the model
							if (null == newDataItem) {
								newDataItem = businessModel
										.newDataItemReference(dataItem);
								businessModel.addBusinessItem(newDataItem);
							}
						}
						// Finally, add the item to the query on the proper role
						businessQuery.addResultItem(newDataItem, role);

						// If the filters HashTable contains a value that
						// matches the item name, apply the filter
						if (filters.keySet().contains(itemName)) {
							StepInterface filter = (StepInterface) filters
									.get(itemName);
							for (; newDataItem.getSteps().size() > 0;) {
								newDataItem.removeStep(0);
							}
							newDataItem.addStep(filter);
						}

					} catch (MetadataException e) {
						e.printStackTrace();
					}
				}
			}
		}

	}

	/*
	 * Search through a BusinessQuery, to see if a data item already exists in
	 * that query
	 */
	private DataItem lookForDataItemInExistingQuery(DataItem dataItem,
			BusinessQuery businessQuery) throws MetadataException {
		DataItem retval = null;
		java.util.List queryItems = businessQuery.getResultItems();
		Iterator qi = queryItems.iterator();
		boolean foundItemInQueryItems = false;
		while (qi.hasNext() && !foundItemInQueryItems) {
			DataItem queryItem = (DataItem) qi.next();
			if (queryItem.getLabel().equalsIgnoreCase(dataItem.getLabel())) {
				retval = queryItem;
				foundItemInQueryItems = true;
			}
		}
		return retval;
	}

	/*
	 * Search through a BusinessQuery, to see if a DataItemReference already
	 * exists in that query
	 */
	private DataItemReference lookForExistingDataItemReference(
			BusinessModel businessModel, DataItem dataItem) {
		DataItemReference retval = null;
		List dataItemList = businessModel.getObjects(false,
				DataItemReference.class);
		String label = dataItem.getLabel();
		for (Iterator iter = dataItemList.iterator(); iter.hasNext();) {
			DataItemReference dataItemRef = (DataItemReference) iter.next();
			if (dataItemRef.getLabel().equalsIgnoreCase(label)) {
				retval = dataItemRef;
				break;
			}
		}
		return retval;
	}

	private void turnOnAllTotals(BusinessQuery ds) {
		try {
			ds.setTotalType(BusinessQuery.ALLTOTAL, Role.ROW);
			ds.setTotalType(BusinessQuery.GRANDTOTAL, Role.ROW);
			ds.setTotalType(BusinessQuery.GRANDTOTAL, Role.COLUMN);
			ds.setTotalType(BusinessQuery.ALLTOTAL, Role.COLUMN);
		} catch (MetadataException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Date Modified:	2008-07-28 09:38:26
Date Created:	2006-01-30 15:43:36

Product Family	Product	Host	Product Release		SAS Release
Product Family	Product	Host	Starting	Ending	Starting	Ending
SAS System	SAS AppDev Studio	Microsoft Windows Server 2003 Enterprise Edition	3.2		9.1 TS1M3 SP4
		Microsoft® Windows® for x64	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	3.2		9.1 TS1M3 SP4
		Microsoft Windows XP Professional	3.2		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	3.2		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	3.2		9.1 TS1M3 SP4
		Microsoft Windows NT Workstation	3.2		9.1 TS1M3 SP4
		Windows Vista	3.2		9.1 TS1M3 SP4

Support

Sample 26014: How to Create a JSP OLAPTableView That Connects to an OLAP Cube with a Customized Query

Overview

Additional Documentation

Instructions

Code for InfoMapOLAPTableViewExampleViewer.jsp

Code for InfoMapOLAPTableViewExampleControllerServlet.java

Required changes

Operating System and Release Information