Essbase Labs

Smart View Command Line Install

2011-12-15T07:42:00.001-08:00

Though this is in the documentation I thought I would push this out with a few more details as we get a good amount of requests on this topic. Most are looking for two things:

Install SV silently (no prompts)
Install SV and pass in the Shared Connections URL

The Smart View installer is built with Install Shield and therefore it supports most Install Shield command line switches along with standard windows installer switches. To properly understand how these switches work you should understand the basics of the Smart View install along with most windows installers. There are really two main parts:

Setup.exe this is a bootstrapper that does thing like handles languages and dependencies and also contains and launches an MSI.
Smart View MSI file is the windows installer file that controls the rest of the setup like laying down files and writing to the registry among other things.

Each part of the installation accepts various command line switches:

Switch	Description	Applies To
/s	Runs in silent mode	Setup.exe
/v	Passes command line parameters to the MSI file	Setup.exe
/a	Runs as an administrator	Setup.exe
/qn	Runs in silent mode	MSI File
URL=	Shared Connections URL	MSI File

Note: Any thing you need to pass to the MSI must be passed from setup.exe using /v and must be in quotes.

Examples:

Run in silent mode:

SmartView.exe /s /v"/qn"

Run in silent mode with the Shared Connection URL:

SmartView.exe /s /v"/qn URL=http://server:port/workspace/SmartViewProviders"

Run in normal mode with the Shared Connection URL:

SmartView.exe /v" URL=http://server:port/workspace/SmartViewProviders"

You can also find this documentation in the Oracle Hyperion Enterprise Performance Management System Installation and Configuration Guide:

http://docs.oracle.com/cd/E17236_01/epm.1112/epm_install_11121/frameset.htm?ch03s07s01.html

Using Fiddler to Debug Smart View Issues

2011-12-12T11:21:00.001-08:00

When we get customer issues with Smart View often the first thing we ask for is a “Fiddler” log. The first response is often “What is Fiddler?” so I thought I would take some time to go over what fiddler is and how it can be used to debug a Smart View issue.

What is Fiddler?

http://www.fiddler2.com/fiddler2/

From the fiddler web site: “Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect traffic, set breakpoints, and "fiddle" with incoming or outgoing data.”

Simply put for Smart View; it is a tool that can be used to monitor the HTTP/XML traffic that Smart View uses to communicate with most of it’s providers.

Example Request/Response for Smart View:

How To Use Fiddler with Smart View

Download and install fiddler on the client where you are experiencing the Smart View issue. http://www.fiddler2.com/fiddler2/ – Remember this is a client tool and needs to be installed on the same machine as the Smart View add-in. There is no need to install fiddler on any server (provider).
Launch Fiddler and make sure you are capturing traffic (File | Capture Traffic)
Recreate the Smart View issue while fiddler is capturing traffic. When doing this is best if other applications are closed as many application use http requests and these will also be captured. Things like email, iTunes, and any browser based application. This will not be an issue but when sharing logs with support you do not want to share traffic dealing with emails or other personal items. Plus it will be a lot easier to read if the only traffic is related to Smart View.
The last step is to share these logs with Smart View support or development. It is always best to have this along with a SR logged. This way the issue can be properly tracked.

How to Read the Fiddler Log

There are tons of options in Fiddler but there are a few things we are looking for and a few areas in Fiddler where we find this information.

Statistics:

Inspectors:

Inspectors has many sections be we are usually looking at the XML sections and seeing what is happening with each request/response to narrow down the issue.

Fiddler is a great tool for debugging but be sure to have a support case opened so that support can help guide you and interpret what you are seeing. For additional details you can review a short tutorial on this same topic. http://www.oracle.com/webfolder/technetwork/tutorials/demos/hyp/CollectingLogs/CollectingLogs.htm

Smart View R&D–Clearly Not Supported Yet…

2011-11-22T10:24:00.001-08:00

Doing a little R&D and thought I would share that I am having some good success with Smart View on Windows 8 Developer Preview (64-Bit) running in Office 2010 (64-Bit).

We all know this is not supported but it is nice to see that on the surface it works.

Don’t even think about calling support on this…

CDF Sample Code

2011-11-16T15:34:00.001-08:00

Recently the Essbase CDF sample code was moved and I thought it would be a good idea to let every one know where the new code can be found.

http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html

download.oracle.com

Click Sample Code Index on the left

Click on Essbase

Add-in Load Times

2011-10-28T12:22:00.000-07:00

Recently we found a setting that improves add-in load time drastically, this setting applies to any Office add-in but Smart View will gladly benefit.

In Internet Explorer options uncheck the option “Check for publisher's certificate revocation". Doing this should noticeably speed up the time for add-in loading. We have only done informal tests but the results have been positive. If you want to check out some of the boring details you can read this http://office.microsoft.com/en-us/office-2003-resource-kit/how-office-performs-certificate-revocation-HA001140308.aspx or you can just uncheck it and see if it loads faster…

My Excel now loads fully in less than 3 seconds (2.66s)!

Update:

Please work with IT departments and understand the security implications prior to making any change like this. This TechNet article describes why the setting was put in place for Windows Vista and above. http://technet.microsoft.com/en-us/library/ee619730(WS.10).aspx

Smart View is 2 words…

2011-09-30T07:52:00.001-07:00

OK I wanted to do a mindless post so I selected a stupid topic. Just so everyone knows it is 2 words; “Smart View”. Not SmartView, I see this a lot (also 2 words) and wanted folks to know.

I suck at grammar so feel free to rip me in the comments.

PSU 102 New Features Tutorial

2011-09-22T08:48:00.001-07:00

Some folks have told me that are interested in the new features in Smart View 11.1.2.1.102 but are not ready to upgrade. If you want to see the features prior to doing an upgrade check out this awesome tutorial.

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

Smart View had emerged from the chrysalis…

2011-09-16T07:48:00.001-07:00

OK that is a bad butterfly reference but I want to point out one of the new Smart View 11.1.2.1.102 features. Butterfly reports are reports that have metadata in the middle of 2 data columns. With Smart View they can now be created, formatted, and interacted with! You can also use multi-range grids to have many butterfly reports on one sheet from various Essbase sources.

If you do this at work you may get fired but it is sure pretty! And yes this is refreshable and more…

If you like the Classic Add-in, check this out…

2011-09-15T08:27:00.001-07:00

Smart View 11.1.2.1.102 is the highly anticipated release with a number of new features for improved interaction with Essbase, making Smart View compelling for the Essbase Addin Community.

Customers can now take advantage of a new forward looking architecture, along with the features they enjoy in the Essbase Addin.

For current Smart View users, the new features provides a rich user experience over the current functionality.

What’s New – Smart View 11.1.2.1.102
• Member Name and Alias on Rows
• Formula Preservation
• Formula Fill
• Sheet Level Options
• New Zoom Options
• Range-Based operations
• Sheet level Options
• Linked Reporting Objects

You can download this patch from Oracle Support.

Check out the following link to sign up for a webcast on the new features for Smart View 11.1.2.1.102 https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1356368.1

Short Cut Keys and Smart View

2011-09-01T09:44:00.001-07:00

I hear a lot of discussion and sometime complaints about what shortcut keys do or do not exist for Smart View and being a ‘mouse’ centric guy I really never paid much attention. Recently I saw a demo of the Ribbon interface where someone explained the model Office used for Keyboard shortcuts and I took notice. I took notice because basically you can do anything you want in Smart View with keyboard shortcuts and you can customize some of your favorites to be really quick and easy.

To get really familiar with keyboard shortcut you need to start by understanding what Office give us for free. Frankly Smart View does not do anything special here, we are simply leveraging the ribbon and just get this stuff.

http://office2010.microsoft.com/en-us/excel-help/keyboard-shortcuts-in-excel-2010-HP010342494.aspx

This applies to Office 2007 and Office 2010 because this is where the ribbon is available. It will also apply better to more recent releases of Smart View where we have heavily integrated with the Office ribbon.

The magic key to remember is the ‘ALT’ key. Open Office and press the ‘ALT’ and all the shortcuts in Office will be revealed even for Smart View.

ALT-S activates the Smart View ribbon and once I have a specific query in play I can also activate my provider specific ribbons. ALT-Y-AZ-N does a zoom in on an Essbase Ad-Hoc query.

I know that is a lot of key strokes! And the key to making this useful is the Office Quick Access toolbar. Any item you place in this toolbar get a simple shortcut key. I added zoom-in an refresh among other items to this toolbar and in the picture below you see the much simpler shortcut keys.

ALT-7 for Refresh and ALT-8 for Zoom-In.

I hope this helps all you keyboard junkies out there! I, however easy this is, will stick with the mouse…

Smart View 11.1.2.1 – Some Cool New Features

2011-04-12T15:21:00.001-07:00

Smart View 11.1.2.1 has many new features but I thought I would point out a few that I found to be very cool.

1 – Office 2010 support – this is not really a feature but it is something many folks have been looking for so I thought I would point it out. Keep in mind this is only the 32 bit version of Office 2010 and not the 64 bit version. For the 64 bit version there seems to be a lot to think about aside from Smart View support so I wonder how many are considering moving. Here is an article on that very topic. http://office.microsoft.com/en-us/word-help/choose-the-32-bit-or-64-bit-version-of-microsoft-office-HA010369476.aspx

2 – Type in the POV toolbar – When doing an ad hoc analysis users can not type member names in the POV toolbar. If you know your dimension member name than this will make your life much easier. In the screen capture below I am almost done typing in Diet Cola.

3 – Docking and Hiding the POV toolbar – The POV toolbar can now be vertically or horizontally docked and the selections will re-orient to maximize screen real estate based on how they are docked. Docking works for all connection types (Essbase, Planning, HFM, etc.) and analysis types (ad hoc or forms). Also you can now show and hide the POV toolbar and your preference will be remembered. The days of the toolbar re-appearing on refresh after you close it are gone.

4 – Use Excel Formatting – Now this option does exactly what it says, it will leave your Excel formatting alone in all cases. This is good and bad, but mostly good. If I format the cell ‘b4’ red then cell ‘b4’ stays red, even when a user pivots. Some users might expect the formatting to shift on the pivot but this does not happen; likely there is no way to predict what the user may want in all cases so it is just left alone. Even with this ‘hazard’ I still love this one. It is nice to have refreshable drillable reports like the one below.

There are a bunch more but I like to keep these short. This should be enough to get most excited and in the coming days I will highlight more features.

Broken Links: A workaround for Smart View cell functions

2011-03-02T14:32:00.000-08:00

One issue found by those who use Smart View cell functions (i.e. HsGetValue) is that the functions can get "disoriented" when distributed to other users or used in a Citrix environment. The result of this disorientation is that the functions in the cells get preceeded by by the full path of the source system (C:\Oracle\SmartView\bin\HsTbar.xla!HsGetValue....) and no longer are valid.

There is a workaround that has proven successful for those who are confronted with this problem. It is simply to add a BAS file to a working Smart View workbook and in this BAS file put the following function declarations and conversion routine:

' Function Smartview VBA Declaration.
Declare Function HsGetValue Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
Declare Function HsSetValue Lib "HsAddin" (ByVal Value As Variant, ParamArray MemberList() As Variant) As Variant
Declare Function HsGetText Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
Declare Function HsSetText Lib "HsAddin" (ByVal text As Variant, ParamArray MemberList() As Variant) As Variant
Declare Function HsLabel Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
Declare Function HsDescription Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant
Declare Function HsCurrency Lib "HsAddin" (ParamArray MemberList() As Variant) As Variant

'**************************************************************************
' For converting existing XLA cell functions to use a local function reference
'**************************************************************************

Sub ConvertFunctionsToLocal()

ActiveWorkbook.ChangeLink "HsTbar.xla", ThisWorkbook.FullName, XlLinkType.xlLinkTypeExcelLinks

End Sub

Running the conversion routine will change all of the current cell functions to use the functions declared in the workbook instead of the functions declared in HsTbar.xla.

And that's it. The workbook will be a self-contained solution that can be passed around or placed in a Citrix environment without the worry of broken links.

Use compression to boost performance in Smart View

2010-11-22T07:21:00.000-08:00

We have been working on the performance of Smart View in recent months. One of the areas of improvement that has been identified is the communication between the server and the client.

The good news is that all of the main providers (APS, Planning and HFM) have the ability to use GZIP compression when sending data back and forth with the Smart View client. As you can expect, the performance gains can be significant with large queries.

The bad news is that APS and Planning do not turn on this compression by default. You must enable this feature.

In APS, there is a setting in the essbase.properies file which must be set to false:

smartview.webservice.gzip.compression.disable=false

In Planning, a SYSTEM PROPERTY must be set (with the lowest value = 1):

SMARTVIEW_COMPRESSION_THRESHOLD

Experiment with the settings, you might be pleasantly surprised with the results. I know I have been.

Converting Legacy Add-in VBA to Smart View Add-in VBA

2010-07-12T15:06:00.001-07:00

If you attended my session at ODTUG Kaleidoscope '10 you found out that if you want to convert legacy (classic) add-in VBA solutions to Smart View that there will be some work. In the session I broke the conversion process into 3 steps; Evaluate, Redesign, and Rewrite.

Evaluate :

Are there new Smart View features that user want or need to leverage?

Maybe the custom code has been replaced with a standard Smart View feature.

Does my current VBA application use the Essbase Visual Basic API?

If yes conversion will take longer or may not be possible.

Are there calls I am making that are deprecated or no longer valid with Smart View? For Example:

EssVGetHctxFromSheet
EssVGetDataPoint
EssMenuLock

Perform a code review and understand the scope of the changes you will need to make.

This is key, make sure you treat the conversion like a ‘real’ development effort!

Determine if the application can be done with Smart View.

There will be some cases where conversion is not possible; YET. This will likely be the case for solutions that heavily leverage the VB API for Essbase.

Does it make sense to convert.

What is the size of the applications user base and how long will it be in service. Maybe the conversion effort is just not worth it and another solution will be better.

Is there another application that will do the job?

Maybe your organization has outgrown planning in Spreadsheet and looking at a the Planning product might be a good idea.

Answering these questions and reviewing these points will help you determine when to convert and how large the conversion effort will be.

Redesign:

Consider what Smart View features can be used to redesign the application. There maybe new features that will influence how your application works. For Example:

POV Toolbar
Reporting Objects
Smart Slices

Understanding the improved Smart View architecture may also guide the redesign.

TCP/IP vs HTTP(s)
New Connectivity Model (providers and connections)
Single Sign On (SSO)

All of these items and more will likely make so that a redesign to some degree is needed and wanted.

Rewtrite:

This is the one that most people hate to hear about. You cannot do a find and replace and make this work!

Be sure to back up all your code.

Do not rewrite on the fly in the project files.
Use some kind of source control if possible.

Treat this like a new project and do not roll it out before it is ready.

Run a test and beta cycle to make sure all the bugs are flushed out prior to releasing your custom application.

Get a partner involved if you do not have the skill sets in house.

During the session I demo’ed a conversion utility that will assist during the Evaluate stage of this process and it is going to be posted on OTN shortly so be on the look out. I will be sure to do a blog post when the utility is available.

Smart View Extensions

2010-07-02T09:29:00.001-07:00

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’.

Adjust Data - The Power Pack Adjust Data extension allows users to quickly make adjustment to data in a cell or range of cells. This panel will allow you to work within the sheet while making simple 1 click adjustments using common math operations. To access this panel, from the Smart View Panel Home dropdown, select ‘Adjust Data’.

Smart View at ODTUG Kaleidoscope '10

2010-06-25T06:07:00.001-07:00

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.

If you have not seen all the new features in 11.1.2 or you want to know more about Smart View you will want to check out these sessions.

Cell Functions: Smart View 11.1.2 vs. Classic Add-In

2010-05-11T06:36:00.000-07:00

The HsGetVal function has been improved significantly in Smart View over the classic add-in EssCell function. The difference in performance is attributed to Smart View HsGetVal using a parallel batch process approach to retrieving the data versus the single cell, serial approach of EssCell.

Here are some statistics for refreshing an Excel 2007 worksheet containing 7,225 cell functions:

Classic Essbase Add-In (EssCell) = 724 seconds to complete

Smart View 11.1.2 (HsGetVal using Function Builder) = 20 seconds to complete

Smart View 11.1.2 (HsGetVal using Copy DataPoints) = 7 seconds to complete

For those wondering, the "Copy DataPoints" based HsGetVal function achieves better performance than the regular HsGetVal function by using a special POV link:

HsGetValue("", "Measures#Profit %", "Market#New York", "Year#Jan", "SVLink12605407100EOL", "Row1", "Col15")

This breakthrough in cell function performance in Smart View should be welcome news to the thousands of classic Essbase add-in users who love the control of querying data using cell functions but were not happy with the performance.

Smart View Extensions and Utilities Page

2010-04-30T08:55:00.001-07:00

Today the Smart View Extensions and Utilities page was launched on OTN. http://www.oracle.com/technetwork/middleware/smart-view-for-office/overview/index.html

One of the non-visible changes to Smart View 11.1.2 was a framework for extensibility. For now this framework is internal Oracle only but it will allow us to put out extensions for end users and quickly support new providers. We have 5 extensions and 1 utility (xlam file that works with any 11.x version) posted and we will be adding more shortly. I will do some posts on specific extension soon and if you want to know more about this framework and the extensions then sign up for my session at Kaleidoscope (First Look - Smart View Extensions).

If you have idea for new extensions or questions or comments on the ones out there than let us know!

Smart View 11.1.2 – Ribbons

2010-04-23T07:51:00.001-07:00

One of the major changes you will notice with Smart View 11.1.2 is context specific ribbons and menus. So now when you use Smart View you will no longer have to browse through a list of grayed out items on a huge ribbon or menu.

When you are in Smart View doing various things (ad-hoc analysis, form data entry, etc.) you see ribbons and items that are specific to the task. In Office you get this same effect when you insert a chart or a specific object. When the object is active specific menus and ribbons are available.

The ribbon and menu items have also been revised to make sure they are organized logically and the things you do most are easy to access.

Even context menus are specific, down to the cell level, and excel right click options can be shown or hidden based on a user preference. Also for forms we have introduced a ‘Mini Toolbar’. The new "Mini Toolbar" is a type of context menu that is automatically shown (by default) when a cell is selected. The purpose of this feature is to provide easy access to the most-used commands without requiring a right-mouse-button click. This works a lot like the Office 2007 formatting toolbar.

I am sure users will find this much easier to work with!

Smart View 11.1.2 - Connections

2010-04-21T14:49:00.001-07:00

Now that Smart View 11.1.2 is out I can share some key changes that I think most users will like. In this update I will focus on connections and the revised Smart View Panel. But before I get to that I want to mention that you can download the Smart View client from edelivery without downloading the entire foundation. A mere 80 megabytes!

Now back to connections. Connections in this release have been revised quite a bit and things should be much simpler, no more APS vs. independent vs. favorites. In this release there are 2 types of connections, shared and private.

Shared Connections - Connections that are defined by an administrator and all users can see them.

Private Connections - Locally stored connections or URL based connections.

Most of the time users will find all of their data sources listed under shared connections.

If a data source is not listed under shared connections (ex. a development server) the end user can use the ‘quick connect’ feature in private connections and just type a URL in the drop down and click the ‘go’ button.

If you have a connection that you use all the time simply add it to the list of private connections and it will always be there in the dropdown. Plus URL’s you type will also be remembered. Also note that when you click on a connection you get a context sensitive list of key ‘actions’ that can be performed on that connection.

Once you use a connection a few times you will be able to access that connection from the home panel by simply clicking it’s MRU (Most Recently Used) item. MRU’s are a great for single click access to your most common tasks like starting an ad-hoc analysis or opening a form. MRU’s can be ‘pinned’ so they are always where you expect them to be.

Alright, it is that simple. I will be sharing more new features and more on connections in the next few days and weeks so stay tuned!

Smart View Timeout Settings

2010-02-22T13:32:00.000-08:00

For those Smart View users who perform large Essbase queries or have to suffer with a slow Internet connection, during Smart View operations you may have experienced an error message that says "The request timed out. Contact administrator to increase NetRetryCount and NetRetryInterval." Unfortunately, this error message can be a little misleading in how to remedy the situation. There are two areas to adjust and they have nothing to do with NetRetry.

First, within the Smart View options under the Provider tab. Select Provider Services from the drop down and increase the Session Timeout setting. I set mine to 600 seconds.

Second, there need to be 3 DWord items added to the registry under

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings

ReceiveTimeout 900000
KeepAliveTimeout 900000
ServerInfoTimeout 900000

These values are in milliseconds. So, 900000 is the equivalent of 15 minutes.

After making these adjustments, I was able to query almost a million cells using Office 2007 (via Internet Explorer 8 HTTP) without a timeout error.

Smart View Features You Never Knew Existed – NumericZero.

2010-02-01T09:38:00.001-08:00

In previous versions of Smart View you could put the number 0 in as a missing label and instead of queries showing #missing they would show 0. At some point this went away for various reasons but many do not know that this functionality still exists. You just have to use the replacement term #NumericZero to get the same effect.

This option change will result in a grid with 0’s instead of text…

Smart View Features You Never Knew Existed – POV Printing.

2010-01-26T20:54:00.001-08:00

So ever wish you could print the Point of View with your Smart View reports in excel? Maybe you insert a row, or a textbox, or use some VBA trickery to do this. Well there is actually a built in way to do this.

Just use the standard dialog to modify the Excel header or footer and add the following text: POV:{}

Smart View translate this to the POV when the sheet is printed or print preview is launched.

There are no options with this so you get the POV as displayed in the screenshot above.

I am hopeful that this will become a multi-part series as I find more things that I think are useful but not everyone know about.

Sparklines with Smart View

2010-01-20T14:31:00.001-08:00

I was checking out Office 2010 and noticed a new feature called Sparklines. Sparklines are a new feature that let you essentially chart data within a cell. Here is a link to a blog that describes the feature in detail. http://blogs.msdn.com/excel/archive/2009/07/17/sparklines-in-excel.aspx This seemed really cool, so I thought it would be even more powerful if I could put this feature into action with live, refreshable Essbase data so I installed the latest 11.x version of Smart View and got it working.

Here is a screen shot, if anything this will make for some cool demos.

Please note that Office 2010 is not supported and I do not recommend trying to install Smart View with it as it will likely have issues and you will not be able to get assistance from support.

More CYA on this one:

Opinions expressed by the authors of this blog are entirely their own and do not reflect the position of Oracle or any other corporation. Any advice or recommendations discussed on these sites (or sites they link to) are not validated by Oracle.

Performance Survey…

2009-12-15T10:53:00.001-08:00

To help use understand the scale of queries that are in use please answer this short 4 question survey. This is regardless of if you are using the Legacy Add-In or Smart View.

http://surveys.polldaddy.com/s/4081F5643A88381C/

Thanks!

Leveraging Excel 2007 features in Smart View

2009-11-25T09:16:00.001-08:00

So my days of living in a spreadsheet are behind me and I really do not get a chance to use all the spreadsheet has to offer. Because of this I took some time to re-familiarize myself with some of the new features in Excel 2007. A bunch of these items may be in 2003 I just did not try it and know that some are 2007 specific. I found many of these features very cool but combined with live data from Smart View (Essbase) they were way cooler!

Ok to set the stage I am not going to provide every step just the major ones and hopefully you can figure this out as I did. For this I am leveraging a feature in Smart View call function grids. A function grid is simply a series off cells with cell functions in them. This eliminates the issue of formatting and Excel features going away when the data is refreshed. Function grids can be added in a number of ways:

Copy and Paste Data options on the Hyperion ribbon.
Reporting objects created using a Smart Slice.
Using the function builder.

For what I am doing I am going to use option 1. So to get started I am going to drill around an ad hoc grid until i have the data I want to use in play. I select that data and click Copy Data from the Hyperion ribbon. I go to a new worksheet or workbook and click Paste Data from the ribbon. This gets me a refreshable function grid.

Once I have the data in this format I can apply Excel formats with out worry.

Format as Table (On Home tab in the Styles group)

Table formats allow the user to easily sort, rank and filter. This is really powerful when the data is static and even better when the data is dynamic and can be refreshed. In the screenshot I sorted on spending for 2007 and filtered on the top 15. Looks like FL and CA are the big spenders…

Conditional Formatting (On Home tab in the Styles group)

Conditional Formats can help you to build cool reports that are great for users who need visual cues as to where issue are. In this example I changed the POV for the data grid to % Change in spending from the previous year and creates a threshold table so used can dynamically change the tolerances. Wow in most states spending is growing by 5% or more…

Data Grouping (On Data tab in the Outline group)

This is a nice way to organize large data sets for users and keep a large report from getting out of control. In this example I group states based on their spending growth and kept the conditional formatting from above.

Data Validation (On Data tab in the Data Tool group)

Nice way to see if data rules are being enforced also can be used to see if amount are beyond thresholds set. In this work book I have asked that all states that spent more than $100,000,000,000 in a year get circled.

Combined Features

Ok this last sample is just crazy but you get the point. Again all of these are just Excel features but with the Function Grid in Smart View they are even more powerful because of live, refreshable data.

Oh one more thing, the screenshots are of actual US government spending data. Government Transparency + Mike’s Essbase skills = Cool Sample Application! Thanks Mike.

What office product do you use to access Oracle BI/EPM data sources?

2009-11-17T13:25:00.001-08:00

Lately we have been doing a lot of work with Smart View and are trying to get some feedback from users about the product. So we decided to try polls. Here is our first question and based on the answers we will have more polls and surveys to follow. All with the goal of helping make the product better.

More Sample Code…

2009-09-02T15:00:00.001-07:00

OK Mike is on a roll posting CDF samples. Check out the latest list:

https://codesamples.samplecode.oracle.com/servlets/tracking/action/ExecuteQuery?query=essbase1

Keep checking for updates, as we get time we will continue to post more.

Sample Code

2009-07-27T11:29:00.001-07:00

At the last ODTUG conference and over the course of writing on this blog we have had a bunch of requests to get some of our code posted. For the most part we handled this on a case by case basis with email, ftp and other means…

Well the code sharing problem is officially solved thanks to samplecode.oracle.com. Here is the description from the site.

Welcome to samplecode.oracle.com, where employees and Oracle Technology Network members collaboratively build and share sample applications, code snippets, skins and templates, and more.

We are just starting to post some of the CDF that are requested most often; we only have a few up there but we will be posting more over the next few weeks.

We also plan to share other stuff like Java API code, MaxL scripts, Calculation Scripts, and maybe some spreadsheet toolkit stuff. We will also get this linked from the Essbase OTN page but until then here is the direct link.

https://codesamples.samplecode.oracle.com/servlets/tracking/action/ExecuteQuery?query=essbase1

Kaleidoscope 2009 Hands on Lab - Oracle Essbase Java API - Building Your First Application (S656)

2009-07-07T09:07:00.001-07:00

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

If you complete this lab you may run into a bug with connections. Tim Tow has documented this fix very well… http://timtows-hyperion-blog.blogspot.com/2007/12/essbase-api-error-fix-geeky.html

Kaleidoscope 2009 Hands on Lab - Oracle Essbase Java API - The Basics (S655)

2009-06-26T09:48:00.001-07:00

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()); 
            } 
        }
    }

}

You can download the lab here.

Kaleidoscope 2009 Hands on Lab - Introducing J-Developer (S654)

2009-06-26T08:27:00.001-07:00

If you did not get a chance to sign up for the hands on lab I wanted to give you a chance to get some of the content. This lab is intended to be a intro to J-Developer so that you can begin using J-Developer. If you follow this online tutorial you will get all the same content and more. If you are interested in using J-Developer with the Essbase Java API check out my earlier blog on this topic.

Kaleidoscope 2009 Top 5 Essbase CDF's (S605)

2009-06-22T18:16:00.001-07:00

I am about to present at Kaleidoscope 2009 and thought I should do a blog about the session so that if anyone missed the session or fell asleep they could get the information they need. Before I start I need to give credit to Mike and Toufic for sharing their code with my and thanks to Mike for helping me get up to speed on CDF's

So What is a CDF?

A CDF or custom defined function is any java class written so that is can interact with the Essbase calculator.

Data, metadata or both can be passed from the calculator to the CDF.
Data or metadata be returned to the calculator.
The CDF can call any outside Java process.

Basically if it can be done it Java it can be run in an Essbase Calculation.

What are the Ways I can run a CDF?

Register a Function.

Static methods can be registered with Essbase so they can be run in a calculation script as if they were a native function. You also want to do this is the person creating the calculation script will not be the same person who developed the function. The registered function is then run in a calculation script like any other Essbase calculator function. Here are some docs on registering functions http://download.oracle.com/docs/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/ddl/cdf/examples/register.htm

RUNJAVA Command

This command will run a ‘Main’ method in a java class by calling it in a calculation script like this RUNJAVA somejavaclass “Parameters String []";

Most CDF’s should and can be written to support both methods and in many cases you may need both to support the function. For instance if you have a system you want to log into and then pass a bunch of data or Essbase and then log out. You might use RUNJAVA at the top of the script to do the login and then use a function to get the data and then use RUNJAVA to logout at the end. This way you do not have to log into the system over and over again as you pull in data. I show the use of both methods in my blog entry on integrating Twitter with Essbase. http://essbaselabs.blogspot.com/2009/05/ashton-kutcher-can-now-get-my-essbase.html

How do I install a CDF?

Create the JAR file and make sure it does what you want.
Copy the JAR file or files and any related files like properties files to the Essbase server in the directory $HYPERION_HOME /products/Essbase/EssbaseServer/java/udf For new installations this folder may not exist and you may need to create it.
Modify the file udf.policy and grant the appropriate permissions if needed $HYPERION_HOME /products/Essbase/EssbaseServer/java/udf.policy For Example:

grant codeBase "file:${essbase.java.home}/essbase.jar" {
permission java.security.AllPermission;
};

Register the function if needed.
You should follow the same rules and syntax as a standard Java Policy file and always error on the side of more secure.
CDF are loaded when the server starts and changes to the JAR file will require that you stop and start the server.
You can view an application log to see the your function loaded correctly.
[Thu May 07 12:54:02 2009]Local/Sample///Info(1200445)
External [GLOBAL] function [@JechoString] registered OK

Top 5 CDF's

Beyond all the geeky coding stuff is some cool and really functional CDF's and instead of doing a top 5 specific list I have broken them down into the top 5 CDF categories and many of the categories have more than 1 useful function.

Data Functions - Functions like these allow a developer to get at data not normally accessible to Essbase. In the example I provided (see below) I go after stock data from a free Yahoo http quote service. This concept can be applied to any number of web service or internal systems.
Essbase Java API based functions - Ever wanted at add a member while doing a calculation, well you can you just need to put the correct files in place and call the Essbase JAVA API in your CDF. The sky is the limit with the JAPI and I include moving data from one cube to another and creating substitution variables but if you can do it in the JAPI you can do it in a CDF.
J-Alert - Ever wanted to know the progress of your calculation with out having to check the logs? If so this function is perfect! It can send emails with various content at any point in the process.
String Functions are ideal for debugging your calculation scripts and enhancing. Things like string concatenation that are difficult in the Essbase are a breeze with these functions.
J-Export - This has got the be the most asked about CDF in the field and among internal teams. This allows you to do exports of targeted data to various output types and it does it all screaming fast!

Sample Files

Speeding Up ASO Extracts

2009-06-22T12:20:00.000-07:00

I recently had the opportunity to work on coming up with the fastest way to extract data from an ASO database. The result of this extract was to be used in another application so the format of the data was important. This requirement ruled out using the Export feature of ASO since the format of the data from an Export is optimized for loading back into an Essbase database and cannot be configured. However, if you do not care about the format of the output, this is the fastest way to export all level zero data from an ASO database.

That left me with 2 options: MDX script and Report Writer script.

MDX really is not meant to be a bulk extractor of data. It has a number of drawbacks. One is that it has set size limitations (2^32) when performing crossjoins. One can work around this limitation (to a point) by pre-processing the sets using filtered named sets and then using the named sets in the crossjoin. Even when the MDX ran successfully, it was still slower than using the Report Writer.

The Report Writer method of extraction turns out to be the fastest IF the report script is written in the correct manner. The key to using the Report Writer with an ASO database is to order the dimensions from the smallest dimension to the largest dimension in the ROW command. Using this proper order will speed up a report writer query in some cases by a factor of 10 or more!

Finally, I found that a properly ordered Report Writer script does not benefit by increasing the Retrieval Buffer. Leave it at the default setting.

Sample extract report writer and MDX scripts can be found here.

CDF: Logging Functions

2009-06-22T06:55:00.000-07:00

There are three different ways to perform logging using a CDF. The first is using the JAPI logging capabilities. The second is using a third party logging package like “Log4J”. Finally, the Essbase calculator provides for a way to use Essbase logging. I will be discussing the latter two methods in this blog.

The Essbase calculator allows for logging within the Essbase application log itself through the use of the Essbase context. Using the logging CDF, this can only be accessed using the RUNJAVA command in a calc script. An example of this would be as follows:

RUNJAVA com.hyperion.essbase.cdf.LogFunctions "The calc script has finished";

Using a third party logger like Log4J, there are dependency files that must be included in the UDF directory along with the CDF jar file. In this case, the log4j-1.2.8.jar file (which can be found under the Hyperion/Common directory structure) and the log4j.properties file. The properties file will need to be modified to direct the log output file to the appropriate location on your machine. There are two functions in the logging CDF:

@JlogDebug (string, string array, double array)

Logs a debug message to the Log4J log file.

@JlogDebug("On Budget" ,@LIST(@NAME(@CURRMBR(Year)) ,@NAME(@CURRMBR(Market)) ,@NAME(@CURRMBR(Measures)) ,@NAME(@CURRMBR(Product)) ,@NAME(Actual) ,@NAME(Budget)) ,@LIST(Actual,Budget));

“2009-04-21 15:49:41,875 DEBUG On Budget :: Jan New York Profit 100-10 Actual Budget 0.0 -2.0E-15”

@JlogError (string, string array, double array)

Logs an error message to the Log4J log file.

@JlogError("Over Budget" ,@LIST(@NAME(@CURRMBR(Year)) ,@NAME(@CURRMBR(Market)) ,@NAME(@CURRMBR(Measures)) ,@NAME(@CURRMBR(Product)) ,@NAME(Actual) ,@NAME(Budget)) ,@LIST(Actual,Budget));@JlogError("Over Budget" ,@LIST(@NAME(@CURRMBR(Year)) ,@NAME(@CURRMBR(Market)) ,@NAME(@CURRMBR(Measures)) ,@NAME(@CURRMBR(Product)) ,@NAME(Actual) ,@NAME(Budget)) ,@LIST(Actual,Budget));

“2009-04-21 15:49:41,875 ERROR Over Budget :: Dec New York Profit 100-10 Actual Budget 51.0 40.0”

Source for these functions can be found here.

Kaleidoscope 2009

2009-06-19T07:36:00.001-07:00

Check out my sessions at Kaleidoscope 2009:

http://www.odtugkaleidoscope.com/abstracts.html#Milella

Top Five Essbase CDF's
In this session we will demonstrate how to install and use a CDF (Custom Defined Function) in Essbase. We will also walk through some of the most popular CDF categories including an overview of J-Export. CDF categories include; exporting data (J-Export), importing data, accessing Web content (stock quotes, other Web services), outline changes with the Essbase JAPI, and general utility functions for tuning and debugging Essbase calculations.

Bonus CDF - Integrate Essbase with Twitter http://essbaselabs.blogspot.com/2009/05/ashton-kutcher-can-now-get-my-essbase.html

Hands on Labs

I will also be joining Tim Tow in some hands on labs:

Introducing JDeveloper
In this session, you will learn how to download, install and perform the basic configuration, with a focus on the Essbase Java API, of the Oracle JDeveloper Java Integrated Development Environment. You will also learn basic Java concepts including how to create a new Java application and project and how to create a new Java class by creating the classic Java ‘Hello World’ application.

Java API: The Basics
In this session, you will learn the basic structure of the Essbase Java API, how to use it to connect to a given Essbase server, application and database, how to layout a data retrieval grid and how to retrieve data into the grid.

Java API: Building Your First App
In this session, you will learn how to use the Essbase Java API to write an application, we will focus our efforts on building an outline extractor.

Using the Essbase Java API with JDeveloper

2009-05-20T11:37:00.001-07:00

Not sure if this is covered in any document or on any other blog but I could not find it so I thought I would share the steps I took to do this.

Pre Requites:

Essbase is installed and working and is accessible from the machine where you plan to do your development work.
APS (Analytic Provider Services) is installed, this does not have to be running to use the API but you will need files from this installation.
JDeveloper is installed. I am using JDeveloper 11.1.1.0.1 but I am sure earlier versions will also work.

In JDeveloper create a new 'Generic Application' by selecting File New...

Give your application a name (i.e. Essbase Java API) and click finish.

With the new application selected create a new 'Generic Project' by selecting the File New menu or right clicking on the application in the application navigator.

Give the new project a name and select Java for it's technology.

Click Next and enter a default package name (i.e. com.oracle.essbase.japi) and click finish.

On the server where you have installed APS and Essbase copy the following files and put them in a directory that will be accessible from the JDeveloper project:

%HYPERION_HOME%\products\Essbase\aps\lib\cpld14.jar
%HYPERION_HOME%\products\Essbase\aps\lib\ess_es_server.jar
%HYPERION_HOME%\products\Essbase\aps\lib\ess_japi.jar
%HYPERION_HOME%\common\loggers\Log4j\1.2.8\lib\log4j-1.2.8.jar
%HYPERION_HOME%\products\Essbase\aps\bin\essbase.properties

When copying these files to your machine make sure you have following directory structure:

Local Folder\lib - This folder has all the jar files.
Local Folder\bin - This folder has properties file.
Local Folder\data - This folder is empty and when you run your first application a file will be created there for you called domain.db.

In JDeveloper right click on the project created above and select 'Project Properties'.

Select the section named 'Libraries and Classpath' and add a reference to the 4 jar files from above.

Select the section ' Run/Debug/Profile' and edit the default profile.

In the Java Options for the client JVM add the following parameter:

-DESS_ES_HOME=<Path to the files from above or the APS_HOME directory> (i.e. -DESS_ES_HOME=C:\JDeveloper\mywork\EssbaseJAPI\japi)

Now you are ready to program using the JAPI to do this you will need a Java class. Right click on the project and select New...

Select New Java Class and click OK.

Give the class a name and select any of the options you want.

You are now ready to start writing code against Essbase using the JAPI in JDeveloper. You can find code samples in the directory %HYPERION_HOME%\products\Essbase\aps\samples\japi

Ashton Kutcher can now get my Essbase calculation updates...

2009-05-07T14:09:00.001-07:00

So for an upcoming ODTUG presentation I needed to create some basic CDF's to show as examples; so I decide I had to do something more fun than just logging data or running an executable. As I was updating my Twitter status I realized there may be an actual business case for having a Twitter account and my CDF example was born... The use case is to follow an Essbase server on Twitter and get updates as calculations are run on the server.

Here is what I did:

Set up a new Twitter account (did not want to mess with my own account) http://twitter.com/essbaseserver
Using JDeveloper I created a new CDF that sends string updates to twitter. I did this using JTwitter an open source Java library for access to twitter.

Registered the CDF and created a calculation script to run while sending the Twitter account status updates.

RUNJAVA com.oracle.essbase.cdf.EssbaseTweet "Running a Calculaiton on the Cube Sample.Basic";Calc ALL;fix("Market","Sales","Jan",@LEVMBRS ("Product", 0))"Actual"(@EssbaseTweet (@CONCATENATE (    @CONCATENATE (                @CONCATENATE ("Sales for ", @AlIAS(@CURRMBR("Product")))                                , " are "), @JgetStringFromDouble (@CURRMBR ("Product"),@_false,@_false)));)endfix  RUNJAVA com.oracle.essbase.cdf.EssbaseTweet "Calculation Complete on the Cube Sample.Basic"; 

This runs a calc all and then updates the Twitter account status with sales for level 0 products. If you follow this twitter http://twitter.com/essbaseserver account you will get updates for all of my testing. Here is the source.

Organizing MaxL Scripts

2009-04-21T12:19:00.001-07:00

If you have been using Essbase for any amount of time then you surely have found yourself writing a number of MaxL scripts. Often times these scripts can become as complex as writing code using an API but the scripts have no real development environment and quickly get out of control. I am going to discuss one technique I use to organize MaxL scripts.

If you have never used MaxL or you use Notepad to write MaxL scripts then you may want to read my post on working with Essbase Files before continuing with this post.

The key for script organization is to break the scripts down in to 'mini' scripts in separate files and then have a calling script to run all the functional mini scripts. For example I always have a connect script, a disconnect script and a properties script. The properties script is just a list of all my set commands (variables).

set ApplicationName = 'Sample';
set DatabaseName = 'Basic';
set SpoolFile = '../logs/MAXL_Build_Process.log';
set ServerName = 'SomeServerName';
set UserName = 'SomeEssbaseUser';
set Password = 'Password';

You will want to include anything in here that might vary, including username and password. Be careful about saving user information in clear text!!

The connect script is simply the login command that accepts the variables from the

properties script.

LOGIN $UserName $Password ON $ServerName;

The disconnect script is even easier as it is just the logout and exit commands.

LOGOUT;
EXIT;

Now that you have 3 script files you can pull them together and call them from one file and run the entire script.

msh Properties.msh;
msh Connect.msh;
msh Disconnect.msh;

This file simply calls the other files in the specified order. This allows you to be flexible and easily edit what parts of the script are run. Also over time you can accumulate a library of mini scripts that can be used to create multiple scripts to automate almost any process.

I know this example does nothing functional (login then logout) but it should help you to understand how to break a script down for organizational purposes.

CDF: Miscellaneous Functions

2009-04-20T08:54:00.000-07:00

Over the years, we have created a variety of custom functions to solve particular business problems. The following list is a variety that one can only categorize as miscellaneous.

@JgetConcatenate (string array)

Unlike the native function @CONCAT which will combine only two strings into a single string, @JgetConcatenate is a very useful function for combining many strings into one string.

@JgetConcatenate(@LIST("More","-","than","-","two","-","strings."))

@Jequals (string) and @JequalsIgnoreCase (string)

@ Jequals compares two strings and returns a Boolean. @ JequalsIgnoreCase compares two strings regardless of their case and returns a Boolean.

@Jequals(@ALIAS(@CURRMBR("Product")),@SUBSTRING("GoodColas",4))

@JequalsIgnoreCase(@ALIAS(@CURRMBR("Product")),@SUBSTRING("goodcolas",4))

@JcompareStringToDouble (string, double)

@ JcompareStringToDouble compares a string with a double value and returns a Boolean.

@JcompareStringToDouble(@NAME(@CURRMBR("Product")),100)

@JgetString (double)

@ JgetString compares a string with a double value and returns a Boolean.

@Jequals(@NAME(@CURRMBR("Product")),@JgetString(100))

@JgetDouble (string)

@ JgetDouble returns the double of a string.

@JgetDouble(@NAME(“2004”))

@JgetDoubleQuote (string)

@ JgetDoubleQuote returns a string enclosed within double quotes.

@JgetDoubleQuote(“20040927”)

@JsortStrings (string array, boolean)

@ JsortStrings returns a sorted array of strings. The boolean parameter determines ascending or descending ordering.

@JsortStrings(@LIST("c","a","b"),@_False)

Update: Source for these functions can be found here.

CDF: @JEcho

2009-04-16T07:58:00.000-07:00

One of the most useful functions that we have ever written is @Jecho. This function allows for sending any output to the Essbase database window during the execution of a calc script. Have you ever wondered to yourself “This calc script is taking too long…what the heck is it doing?” Well, with @Jecho you can insert this function into areas of the calculation script and very quickly see what is being calculated, both data and metadata, and in what order. This function has been a life saver for countless Essbase calc performance optimizations over the years because it can identify inefficiencies in a calc script such as calculating un-intended members or calculating empty blocks when they do not need to be calculated.

There are three variations to this function: @JechoString, @JechoDouble and @JechoBoth. @JechoString allows for the output of an array of strings. @JechoDouble allows for the output of an array of double values. @JechoBoth allows for the output of an array of strings and an array of double values. The output is tab delimited.

Note: In Essbase, the native function @LIST will create an array of items. The native function @NAME will return the string name of an Essbase member. The native function @ALIAS will return the string alias of an Essbase member.

Update: Source code for these functions can be found here.

CDF: JExport and Dynamic Members

2008-10-15T07:54:00.001-07:00

One of the most frequently asked questions by people who use the JExport function is “How do I output the dynamically calculated members along with the stored members in a cube?” By default, the Essbase calculator will skip over the dynamically calculated members for performance reasons. It is only until you explicitly make the calculator evaluate a dynamic calculation that it will include dynamic calculations in its output.

There are 2 ways to make this happen. For both solutions, it is required that the formula be based upon a DENSE member. In my examples to follow, the formula is for the member “Jan” which is from the Time dimension in the Sample|Basic cube. The Time dimension is a dense dimension.

The first way to get all dynamic members to be output by the JExport function is to include an evaluation statement in the script that uses a dynamic member in the evaluation. In the example script, the evaluation statement is “IF(“Variance” >=0 OR “Variance” <= 0)”. The member “Variance” is a dynamically calculated member. Note: The IF statement has been purposefully written so that it always evaluates to True.

The second way to get all dynamic members to be output by the JExport function is similar to the first but instead of introducing an evaluation statement into the script you include a dynamic member as an output member in the JExport function. In this example, the “Variance” member is being output along with the “Actual” and “Budget” members.

Remember, extracting dynamic members using JExport will degrade the performance of your script since the calculator has to perform the calculations before it can output the data.

Custom Defined Functions

2008-10-03T08:37:00.001-07:00

I am a huge fan of the Essbase Custom Defined Function (CDF). My colleague Toufic Wakim and I have built a library of CDFs over the years to do a variety of things that extend the capabilities of Essbase. Probably the most famous CDF we created is JExport. JExport is a function that uses the powerful Essbase calculator to extract data from Essbase into a file or SQL database. The main benefits of JExport are that it is very fast and allows for conditional extracts.

Anyway, I will be blogging about the various functions within the CDF library for the next few months. By doing so, I am hoping that those new to Essbase will discover this powerful extensibility feature of Essbase and those veteran Essbase designers will get new inspiration for how to use Essbase to solve new business problems.

PAGELESS CUBES

2008-10-03T08:35:00.001-07:00

Did you know that it is possible to create a cube that is completely real-time? Where there is NO data stored in the cube or any other cube for that matter. How can this be, you ask yourself? It can’t be through partitioning because there is not any data stored in any cube. It can’t be through the use of an XREF function for the same reason. The answer is through the use of dynamically calculated members and customer defined functions (CDF).

I have always wanted to build my own personal Essbase stock market database that would give me investment information. I wanted it to use freely available market information and to have real-time stock prices. I found my source of this data on Yahoo Finance. You pass in a URL with parameters to Yahoo and it returns comma separated data.

My plan was to create a simple CDF that would generate the Yahoo URL and insert the necessary parameters. This would return the stock price for any stock symbol in real-time. I would use this CDF function in the calculation of a dynamically calculated member. When the dynamically calculated member is queried, it launches the URL and retrieves the data just as if it existed in the cube.

I was able to achieve my dream cube and it works great. This technique of using a CDF within a dynamically calculated member works for other data sources as well. I have CDF functions that retrieve data from a flat file, SQL database and an in-memory variable.

One caveat to this solution: the query performance is not sub-second. But for me, I can live with slower performance to have my dream cube.

Essbase in the Clouds

2008-09-30T12:26:00.001-07:00

So after the Oracle’s announcement about putting the database and other tools in the cloud I thought is would be timely to do a post on some work being done by a partner, Full360, with Essbase and the Amazon Cloud. Here is a blog from Full360 that gives you step by step instructions on getting a copy of their Essbase image up in the Amazon Cloud.

http://www.full360.com/content/using-cubeflex-basic-edition-ami-start-using-essbase-amazon-ec2

This is a real cutting edge thing and it is nice to see Essbase at the front end of it. The possibilities are endless with Essbase and the vast amounts scale the cloud can provide.

To learn more about cloud computing check out this link. http://www.google.com/search?hl=en&q=cloud+computing

Database Settings with MaxL

2008-09-15T07:54:00.001-07:00

For a project I wrote a script that changes key database settings with MaxL. Nothing too special here just thought it would be a nice one to share. Here is a link to download the files. Once extracted use the file properties.msh to edit the settings and enter key fields like username and password.

Working with Essbase Files

2008-08-26T08:16:00.001-07:00

Working on a project I found myself writing a bunch of MaxL scripts. So for this I started using Administrative Service and as the scripts got bigger I realized I needed to start using my favorite text editor, TextPad. If you don’t have it already it is really a fine text editor and I have been using it for years, but working with MaxL I was missing the syntax highlighting and function lists that I had in Administrative Services. I remembered that TextPad has features that allow you to define things like highlighting and function lists, you just need to build the definition files for this stuff. Here are the steps I took to get my MaxL files working the way I wanted.

Set up TextPad to run MaxL scripts and capture the output.

Open TextPad.
Select Configure | Preferences.
Select the ‘Tools’ item.
On the screen click the ‘Add’ button and choose ‘Program’.
Browse to the location of the MaxL executable and select it. (useally - %HYPERION_HOME%\products\Essbase\EssbaseClient\bin\essmsh.exe).

Once in the tools list you can select it and make edits if needed.
Now you can run MaxL scripts in TextPad. Select Tools | External Tools and select the item you want to run.
Repeat for things like Esscmd and anything else you might want to run from TextPad.

Set up Syntax Highlighting

Open TextPad.
Select Configure | New Document Class.
Type the name you want for the new document type (MaxL or Essbase MaxL).
Select the class members or file extensions that will identify the file (*.msh, *.mxl).
Enable syntax highlighting and select the file to be used. The files need to be located in the TextPad ‘samples’ directory. (C:\Program Files\TextPad 5\Samples) Files are posted below.
Click ‘Next’ and then ‘Finish’.
You now have a new document class that understands syntax highlighting; repeat this for other file types like MDX, Essbase Calculation Scripts, Etc.

Set up clip Libraries.

Make sure the clip library files are located in the TextPad ‘samples’ directory. (C:\Program Files\TextPad 5\Samples) Files are posted below.
Open TextPad.
Select View | Clip Library.
Select the clip library you want.

Now TextPad it set up to handle all the Essbase Script development you would ever want. You can also set up things like ‘File Name Filters’ and ‘Associated Files’ to make things even easier.

Thanks to Dave Roberts for providing the initial TextPad files used for syntax highlighting and clip libraries. Please note the some of the files need updating for new features in Essbase but all are pretty close…

Essbase Studio First Impressions (Part 2)

2008-08-18T07:42:00.001-07:00

So let me start out with a quick review of part 1. In part one I set out to rebuild sample basic with Essbase Studio. I extracted data and the outline, loaded it into Oracle XE and created all the connections from XE to Studio. I left off with a mini schema built and I stated that I was ready to create metadata.

In Studio metadata is considered things like dimensions, dimension elements, measures, cube schemas, drill through reports, etc. and it all lives in a free form panel in studio. This panel is called the ‘Metadata Navigator’ and it is the place for all the ‘pieces’ that you will need to build the cubes you want. For example you may have a hierarchy stored here that is used in many cube schemas. I mention that it is free form only because it is a good idea to come up with a system to organize this section. You can create folders and put new objects in the folders but you cannot move anything. Once it is there it stays unless you delete it and when building a cube or series of cubes you will have lots of metadata!

The first bit of metadata I wanted to create was my hierarchies. In my mini schema tables I had all of my dimensions laid out with a parent column and a child column. So when creating the hierarchies I simply dragged the parent column and to the new hierarchy window and then dragged the child right below the parent.

For each new hierarchy this process would create 3 new pieces of metadata. A dimension element for the parent column, a dimension element for the child column and the hierarchy itself. I repeated this process for all my main dimensions, market, product, scenario, and year. This was a breeze.

Next I had to create the measures dimension and for this I used the columns in the fact and dragged them to the measures hierarchy window. For now I left the measures dimension flat. We will get to the measure hierarchy in another post.

Creating hierarchies was simple and easy once I had the proper mini schema set up. In the next blog on this topic I will address creating the cube schema and some to the key Essbase properties available.

Not your fathers Essbase

2008-08-12T14:26:00.001-07:00

I have spent some time lately installing and using Essbase 11.x and I have started to notice more and more that this is not the same Essbase of years back. This has been happening over the years but I guess I have really taken note as of late. I will start by saying that change can be scary but it that does not mean it is wrong or bad. So this morning I did a fresh install of Essbase and it’s client tools, and I figured I would point out some of the changes that you fellow old timers might encounter.

Gone are the days of 1 floppy or even 1 cd. For this stack you will need a DVD or just a big enough hard drive. The installer is way bigger (many gigs) and this is because you are getting much more than just Essbase you are also getting a number of various foundation components that all you you to do a bunch of great things. You have to think of Essbase as part of an enterprise stack and things like authentication, scaling, administration, etc. additional software is needed. I do wish they would package the client stuff in a small simple windows installer so in the rare case you are installing on a windows client you don’t have to lug down such a big installer.
Native security is being used less and less, and in fact with this installation you have to check a box if you want Essbase to run in native mode. At first glance this seemed like a bummer but if you want to hook Essbase to a LDAP source or Active Directory you have to do this. Plus I bet there are a bunch of Essbase installations with a native admin user name and password of Admin/Password or Essbase/Password.
Running Essbase in the foreground is not so easy… Used to be that you would do the install then launch cmd prompt or your shell and type ‘essbase’ to get things rolling. Now by default there is a service that you start and stop. The service has been there for a long time it is just that now it the default. You just have to trust that Essbase is running and use the logs to see messages, like any other piece of enterprise software. If you really want to start Essbase in the foreground there is a command file for it $HYPERION_HOME/products/Essbase/EssbaseServer/bin/startEssbase.cmd
Speaking of logs they are no longer in the application directory. They are all in a common directory. ($HYPERION_HOME/logs) You see most customer don’t just have Essbase any more they now have many products (see point 1) so it is nice that the logs are all in one place. The old school folks will harp about this when looking for logs on their laptop but it is good for enterprise customers.
Directory structure has changed. I know all the Essbase’ers had the old structure memorized, but don’t worry, it is about the same once you get down to the Essbase directory. $HYPERION_HOME/products/Essbase/EssbaseServer This is to accommodate the many products in the EPM stack you may install on the same machine and I think a little organization is a good thing.

So these are some of the key things that hit me this AM when I installed Essbase. I guess I still thought of Essbase as this cool little OLAP engine when it has grown up to be part an EPM system. For the old timers who are now freaking out (Mike), don’t worry there is still an essbase.cfg!

Essbase Studio First Impressions (Part 1)

2008-08-06T08:07:00.001-07:00

So last week I started using Essbase Studio and figured I would start to share some of my first impressions. When I installed Studio I had planned on working on a simple test case so that I could learn the product. The plan was to build a version of sample basic but because I don’t have a relational schema with the sample basic content I had to do some prep work…

Step 1: Extract the data from sample basic in some form that resembles a fact table.

Step 2: Extract the dimension structures.

Step 3: Load the extracts into a relational database and build a star-schema.

For step 1 I used report scripts and found them to work really well except for the fact that I had to re-learn them. Here is the script I ended up with to get my rough fact table:

{ROWREPEAT}
{DECIMAL 2}
{SUPMISSINGROWS}
{SUPFEED}
{SUPFORMATS}
{SUPPAGEHEADING}
{SUPCOMMAS}
{TABDELIMIT}
{MISSINGTEXT ""}
<SUPSHARE
<ROW (Year, Product, Market, Scenario)
<COLUMN (Measures)
<DIMBOTTOM Year
<DIMBOTTOM Measures
<DIMBOTTOM Product
<DIMBOTTOM Market
Actual
Budget
!

This gave me a file with all my dimensions in rows and all my measures in columns. No keys or indexes but good enough for a quick fact table…

On step 2 I totally cheated, for this step Mike gave me some old dimension extracts he had with parent/child columns. Not sure where he got them but they were perfect so I did not care.

Step 3 was a step I was hoping to avoid, Studio has a cool new feature for supporting text files as tables. Not knowing the limitations I spent some time trying to build a “mini schema” with the text files. Eventually I would find that text files have too many limitations in studio to use them for an entire model. They would be great for some ancillary data but not the main fact and dimensions. The main issue is that you cannot join 2 text files together in a “mini schema” like you can join 2 tables. So I downloaded Oracle Express Edition, installed it and had the files imported in about 30 minutes. Oracle XE is not supported by Studio and is not meant for any sort of enterprise deployment but it worked for what I needed and is quite a nice entry level database.

Once I finished my setup up I was ready to work with Studio to build my model. The first thing to do is to create the data sources. Studio has a nice wizard to walk you through this and it was really easy.

The product supports a number of relational source and some other sources that might be handy like OBI. It also lists Essbase as a source but don’t be fooled this, it is only so you can set up a target for your cubes to be deployed to later. Right away I set up 2 sources one to my XE database and the other to the Essbase server where I was going to deploy my cube.

Once I had my source all set up I was able to create my Mini Schema and define all the relationships between my tables. This can be done in the wizard for creating the data source, manually after the source is created, or skipped altogether if the joins are already defined in the relational database. For this there is also a wizard to get you started:

One you have the Mini Schema created you can begin to define joins, this is as simple as drag and drop.

For each dimension table I needed to create 2 joins. The first join was to the fact table; and this the normal join I would expect. The second join was to join my parent column to my child column in each of the dimension tables. If you have your dimension tables laid out like I did with a parent child relationship this has to be there for hierarchies to work properly later. I’ll attempt to explain this better…

Each of my dimension tables have a column for Parent and a column for child and the hierarchy could be ragged.

ex:

Parent Child

Market East

Market West

East New York

East Florida

West Utah

This is opposed to having a column for each level in the dimension. With the parent and child columns joined you will get the following resulting dimension; and this is what you want.

Without the join you will get the following hierarchy and your build will fail with duplicate member names later in the process.

Once you get the mini schema just right you are ready to begin creating metadata. The next part of this post will dive into this topic so stay tuned!

Running Essbase Clients on Windows Vista

2008-08-04T12:57:00.001-07:00

I recently installed the stack of Essbase clients on my Windows vista machine and found that some the the products needed some tweaks to work. The products in question were Essbase Studio (new tool for building cubes) and the Administrative Services Console. These products need administrative right and with UAC (user access control) turned on in Essbase Studio they will not run properly.

Essbase Studio would not launch at all for this I had to create a new shortcut to the EssbaseStudio.exe file and not use the batch file. This can be found in:

%Hyperion_Home%\products\Essbase\EssbaseStudio\Console\

After creating the shortcut go to the compatibility tab and check the box ‘Run this program as an administrator’. This will cause UAC to prompt you to elevate privileges when you launch the shortcut.

For Administrative Services Console the shortcut seems to work fine and the client launches but when you try to open a calc, or outline or just about anything nothing happens. To fix this just modify the existing shortcut and also check the box ‘Run this program as an administrator’ on the compatibility tab.

Here are some screen shots:

Here is an example of the UAC prompt you will get each time you run these.

You will just have to deal with this unless you turn UAC off and that is a pretty bad idea…

Essbase Studio as a Windows Service – Defining the DataFile Directory

2008-07-30T10:34:00.001-07:00

After working with Essbase Studio as a Windows Service for a couple of days, I discovered that the ability to use text files as a data source was not working properly. The fix is to add this line to the server.properties file in the Studio server directory to point Studio to the desired location for the text files (must use double backslashes):

server.datafile.dir=F:\\Hyperion\\products\\Essbase\\EssbaseStudio\\Server\\datafiles

Thanks go to Aneel on the Essbase product management team for this fix.

Essbase Studio as a Windows Service

2008-07-28T13:15:00.001-07:00

Essbase Studio is a new application that is included with the latest release of the Essbase family of products. The Essbase Studio server is a pure Java application, not deployed to an application server like many of the other server products. Therefore, it is not possible to make the Essbase Studio server a Windows service without a third-party product.

As our group uses many “lights out” servers for our project work, we always run our server products as Windows services. Luckily, we were exposed to an application that takes a Java application and allows you to run it as a Windows service during our last project called JavaService (http://sourceforge.net/projects/wrapper/). We used JavaService to run an OBIEE server using OC4J as a Windows Service. It worked well for our needs.

The key to using JavaService is knowing the correct parameters to use when defining the Windows service. Refer to the link above for the documentation.

Here are the parameters for OC4J:

javaservice -install "Oracle BI: OC4J Service" "F:\jdk1.6.0_06\jre\bin\client\jvm.dll" -XX:MaxPermSize=128m -Xmx512m "-Djava.class.path=D:\OracleBI\oc4j_bi\j2ee\home\oc4j.jar" -start oracle.oc4j.loader.boot.BootStrap -description "Oracle BI Oc4J Service"

Here are the parameters for Essbase Studio server:

javaservice -install "Hyperion Essbase Studio" F:\Hyperion\common\jre\sun\1.5.0\bin\server\jvm.dll -XX:MaxPermSize=128m -Xmx512m -DESS_ES_HOME=F:\Hyperion\products\Essbase\EssbaseStudio\Server\ess_japihome -Djava.class.path=F:\Hyperion\products\Essbase\EssbaseStudio\Server\server.jar -Dpassword=password -Dserver.runInBackground=true -start com.hyperion.cp.server.ServerMain -description "Hyperion Essbase Studio Service"

So, if you are ever in need of running a Java application as a Windows service, we have had good success using JavaService.

Welcome Post

2008-07-23T08:06:00.001-07:00

Welcome to the Essbase Labs blog. On this blog we will discuss anything and everything Essbase. The contributors (still recruiting) have many years of experience with Essbase and have implemented 100’s if not 1000’s of applications for customers.

Parent	Child
Market	East
Market	West
East	New York
East	Florida
West	Utah