#odtug

At Kscope 2012 I did a Hands on Lab about Smart View and I wanted to share the content.  In the lab we connected to AWS servers and followed along; the servers are gone now but you can do the lab with just about any sample applications as I kept it pretty generic.

Tons of great stuff will be shown at ODTUG Kscope12 on Smart View.  Here are my presentations:

Deeper Dive into Smart View's New Features:

Monday, 4:15 PM - 5:15 PM

http://caat.odtug.com/odtug_caat/caat_abstract_reports.display_presenter_abstract?conference_id=90&presenter_id=1002&abstract_id=334498

Exploring the New Features of Smart View Thursday, 10:30 AM - 12:45 PM

In this hands on lab we will explore key new features related to the latest version of the Smart View client.  Features will include new additions and enhancements to ad-hoc analysis including Essbase parity, improved formatting options, enhanced connection management, the Smart View panel, and more.  This lab will show new features for key providers like HFM and Planning but many of the ad-hoc features will focus on Essbase.  We will also explore how to leverage functions and VBA to enhance the end user experience in Smart View.

  There are many other Smart View Sessions that look terrific just check out kscope12.com or do a quick search:

If you attended my session at ODTUG Kaleidoscope '10 you got some great information on one of the latest in Smart View features; Smart View Extensions.  If you missed I will provided a summarized version here.

Smart View extensions are a framework for creating applications or add-ons that run within Smart View.   In Smart View 11.1.2 these extensions are already in use by Oracle product teams (Disclosure Management) and some additional free extensions are available for anyone to download.

Here are some cool extensions that you can download and use with Smart View 11.1.2. http://www.oracle.com/technology/software/products/bi/performance-management/plugins/index.html

New Link: http://www.oracle.com/technetwork/middleware/smart-view-for-office/overview/index.html

Options Tree - The Power Pack Quick Options extension allows users to view and change common options without having to launch the options dialog. To access this panel, from the Smart View Panel Home dropdown, select ‘Quick Options’.

Substitution Variables - The Power Pack Substitution Variables extension allows users to view and use Essbase Substitution Variables in there queries. To access this panel, from the Smart View Panel Home dropdown, select ‘Substitution Variables’.

Table of Contents - The Power Pack Table of Contents extension allows users to see what workbooks are open and if they are connected or not. From the Smart View Panel Home dropdown select ‘Table of Contents’ and a panel will be displayed that allows you to see all of your connected worksheets and workbooks. In the panel the sheets can be sorted by connection, sheet name or book name.

MDX Script Library - The Power Pack MDX Script Library extension allows users to view and launch MDX scripts stored on their local machine. The script results can be used as a starting point for new Essbase queries. To access this panel, from the Smart View Panel Home dropdown, select ‘MDX Script Library’.

OK I am totally bias but here are some key sessions that you will want to check out at ODTUG Kaleidoscope '10.

06/29/2010 3:30 PM-4:30 PM Washington 3 Oracle® Hyperion Smart View for Office, Fusion Edition Toufic Wakim

For those working with a financial application, Oracle Essbase or Oracle Business Intelligence Suite Enterprise Edition Plus, Oracle Hyperion Smart View for Office brings access to data and report templates and the ability to author reports in the Microsoft Office framework. This session gives an overview of this product and demonstrates new features. It also shows how to use it with Oracle Hyperion Planning, Oracle Hyperion Financial Management, Oracle Business Intelligence Suite Enterprise Edition Plus, and Oracle Essbase.

07/01/2010 8:30 AM-9:30 AM Washington 6 First Look - Smart View Extensions Matt Milella

This session will cover the Smart View extension framework and some of the new extensions that will be available for Smart View. Participants will get a look at extensions that add many ease-of-use features like Essbase calculations, substitution variables, quick options, and will also see an extension for advanced MDX style queries. This session will also cover the extension architecture and information on installing and deploying an extension.

07/01/2010 11:00 AM-12:00 PM Washington 6 Convert Legacy Add-in Solutions in VBA to Smart View VBA or .Net Matt Milella

Moving custom spreadsheet solutions built with the legacy (Essbase) add-in can be intimidating. This session will get you comfortable with the idea of moving these solutions to Smart View. This session will focus on the following: VBA equivalents in Smart View; new options in Smart View VBA, not in the classic add-in; creating .NET add-ins with VSTO (Visual Studio Tools for Office); and a development approach for successful conversions.

I know it has been while since the show but I wanted to make sure this got posted.  The plan for this lab was to build on S655 and S654 and for the end result to be a useable Essbase utility written with the Java API in JDeveloper. 

The lab was focused on the Outline API and was centered around the idea of creating an Outline Extractor.  The lab can be downloaded here.  This lab will get you started but here is a more complete code sample with XML output.

package com.oracle.essbase.japi;

  import com.essbase.api.base.EssException;

import com.essbase.api.base.IEssIterator;

import com.essbase.api.datasource.IEssCube;

import com.essbase.api.datasource.IEssOlapServer;

import com.essbase.api.domain.IEssDomain;

import com.essbase.api.metadata.IEssCubeOutline;

import com.essbase.api.metadata.IEssDimension;

import com.essbase.api.metadata.IEssGeneration;

import com.essbase.api.metadata.IEssLevel;

import com.essbase.api.metadata.IEssMember;

import com.essbase.api.session.IEssbase;

 import java.util.ResourceBundle;

 import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

 import javax.xml.parsers.ParserConfigurationException;

 import javax.xml.transform.OutputKeys;

import javax.xml.transform.Transformer;

import javax.xml.transform.TransformerException;

import javax.xml.transform.TransformerFactory;

import javax.xml.transform.dom.DOMSource;

 import javax.xml.transform.stream.StreamResult;

 import org.w3c.dom.DOMImplementation;

import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;

 public class OutlineExtractor {

private static String s_userName = "";

private static String s_password = "";

private static String s_olapSvrName = "";

private static String s_provider = "";

private IEssOlapServer olapSvr = null;

private IEssbase ess = null;

private Document doc;

public OutlineExtractor() {

olapSvr = Connect();

}

  public static void main(String[] args) {

OutlineExtractor outlineExtractor = new OutlineExtractor();

outlineExtractor.ReadOutline("Sample","Basic", true);

outlineExtractor.Disconnect();

}

private IEssOlapServer Connect() {

readProperties();

try {

// Create JAPI instance.

ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);

// Sign On to the Provider

IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);

IEssOlapServer olapSvr = dom.getOlapServer(s_olapSvrName);

olapSvr.connect();

return olapSvr;

}

catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

Disconnect();

return null;

}

}

private void Disconnect(){

//Try to sign off

try {

if (olapSvr != null && olapSvr.isConnected() == true){

olapSvr.disconnect();

}

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

try {

if (ess != null && ess.isSignedOn() == true){

ess.signOff();

}

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

}

private static void readProperties() {

ResourceBundle settings = ResourceBundle.getBundle("Essbase");

  s_userName=settings.getString("Common.UserName");

s_password=settings.getString("Common.Password");

s_olapSvrName = settings.getString("Common.ServerName");

s_provider = settings.getString("Common.Provider");

}

public void ReadOutline(String ApplicationName, String DatabaseName, Boolean IncludeAttributes){

IEssCube cube;

try {

cube = olapSvr.getApplication(ApplicationName).getCube(DatabaseName);

readOutline(cube,DatabaseName,IncludeAttributes);

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

}

private void readOutline(IEssCube cube, String DatabaseName, Boolean IncludeAttributes) throws EssException {

IEssCubeOutline otl = null;

try {

otl = cube.openOutline();

IEssIterator dims = otl.getDimensions();

DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

DocumentBuilder builder = factory.newDocumentBuilder();

DOMImplementation impl = builder.getDOMImplementation();

  doc = impl.createDocument(null,null,null);

Element elementCube = doc.createElement("Cube");

elementCube.setAttribute("ApplicationName", cube.getApplicationName());

elementCube.setAttribute("DatabaseName", DatabaseName);

elementCube.setAttribute("CubeType", cube.getCubeType().stringValue());

doc.appendChild(elementCube);

for (int i = 0; i < dims.getCount(); i++) {

IEssDimension dim = (IEssDimension)dims.getAt(i);

if (IncludeAttributes) {

Element elementDim = doc.createElement("Dimension");

elementDim.setAttribute("DimensionName", dim.getName());

elementDim.setAttribute("Category", dim.getCategory().stringValue());

elementDim.setAttribute("StorageType", dim.getStorageType().stringValue());

elementCube.appendChild(elementDim);

getMembersIterator(dim.getDimensionRootMember(),elementDim);

}

else{

if(dim.getCategory() != IEssDimension.EEssDimensionCategory.ATTRIBUTE && dim.getCategory() != IEssDimension.EEssDimensionCategory.ATTRIBUTE_CALC){

Element elementDim = doc.createElement("Dimension");

elementDim.setAttribute("DimensionName", dim.getName());

elementDim.setAttribute("Category", dim.getCategory().stringValue());

elementDim.setAttribute("StorageType", dim.getStorageType().stringValue());

elementCube.appendChild(elementDim);

getMembersIterator(dim.getDimensionRootMember(),elementDim);

}

}

}

otl.close();

otl = null;

// transform the Document into a String

DOMSource domSource = new DOMSource(doc);

TransformerFactory tf = TransformerFactory.newInstance();

Transformer transformer = tf.newTransformer();

//transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");

transformer.setOutputProperty(OutputKeys.METHOD, "xml");

transformer.setOutputProperty(OutputKeys.ENCODING,"ISO-8859-1");

transformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");

transformer.setOutputProperty(OutputKeys.INDENT, "yes");

java.io.StringWriter sw = new java.io.StringWriter();

StreamResult sr = new StreamResult(sw);

transformer.transform(domSource, sr);

String xml = sw.toString();

System.out.println(xml);

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

catch (ParserConfigurationException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

catch (TransformerException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

finally {

if (otl != null) {

try {

otl.close();

} catch (EssException e) {

System.out.println(e.getMessage());

e.printStackTrace(System.out);

}

}

}

}

  private void getMembersIterator(IEssMember mbr,Element ParentElement) throws EssException{

Element elementMember = doc.createElement("Member");

elementMember.setAttribute("MemberName", mbr.getName());

elementMember.setAttribute("UniqueName", mbr.getUniqueName());

elementMember.setAttribute("MemberFormula", mbr.getFormula());

elementMember.setAttribute("LevelNumber", String.valueOf(mbr.getLevelNumber()));

elementMember.setAttribute("GenerationNumber", String.valueOf(mbr.getGenerationNumber()));

elementMember.setAttribute("ChildCount", String.valueOf(mbr.getChildCount()));

ParentElement.appendChild(elementMember);

IEssIterator mbrs = mbr.getChildMembers();

for (int i = 0; i < mbrs.getCount(); i++) {

getMembersIterator((IEssMember)mbrs.getAt(i),elementMember);

}

}

 }

Here is what my properties file looks like.

Common.UserName=admin Common.Password=password Common.ServerName=localhost Common.Provider=Embedded

The goal of this lab was to introduce the to Essbase Java API and teach the basics.  It walked through the following:

Setting up J-Developer to work with the Essbase Java API (check out my earlier blog on this topic)

Connecting to Essbase

Creating a grid view (grid API, similar to what the classic add-in uses)

Filling in and querying the grid view

Basic output of the results

The Essbase Java API objects covered in this lab:

IEssbase - The Essbase API instance object.

IEssOlapServer - The connection to the Essbase server.

IEssCube - The Essbase cube object.

IEssCubeView & IEssGridView - The Essbase grid API.

If you did not get a chance to sign up for this hands on lab or just want to try it you can get the lab here.

Please note that this lab was based on a pre-built image so some of the directories, URL's, username,etc. will vary.

Here is the complete code for the lab:

package myproject; import com.essbase.api.base.*; import com.essbase.api.dataquery.*; import com.essbase.api.session.*; import com.essbase.api.datasource.*; public class Essbase { private static String username = "admin"; private static String password = "password"; private static String essbaseServer = "localhost"; private static String provider = "http://localhost:13080/aps/JAPI"; private static String application = "Sample"; private static String database = "Basic"; public Essbase() { } public static void main(String[] args) { IEssbase essbase = null; try { essbase = IEssbase.Home.create(IEssbase.JAPI_VERSION); IEssOlapServer olapServer = essbase.signOn(username,password, false,null,provider,essbaseServer); IEssCube cube = olapServer.getApplication(application).getCube(database); IEssCubeView cubeView = cube.openCubeView("default"); cubeView.setAliasNames(true); cubeView.updatePropertyValues(); IEssGridView gridView = cubeView.getGridView(); gridView.setSize(3,5); gridView.setValue(0,2,"Product"); gridView.setValue(0,3,"Market"); gridView.setValue(1, 2, "Jan"); gridView.setValue(1, 3, "Feb"); gridView.setValue(1, 4, "Mar"); gridView.setValue(2, 0, "Actual"); gridView.setValue(2, 1, "Sales"); IEssOperation essOp = cubeView.createIEssOpRetrieve(); cubeView.performOperation(essOp); int rowCount = gridView.getCountRows(); int colCount = gridView.getCountColumns(); for (int i = 0; i < rowCount; i++) { for (int j = 0; j < colCount; j++) { System.out.print(gridView.getStringValue(i, j) + "\t"); } System.out.print("\n"); } } catch(EssException e) { System.out.println("Error: " + e.getMessage()); } finally { try { if (essbase != null && essbase.isSignedOn() == true) essbase.signOff(); } catch (EssException e1) { System.err.println("Error: " + e1.getMessage()); } } } }