Laser Tag for Cloud Analytics

A friendly game of laser tag between out-of-shape technology consultants became a small gold mine of analytics simply by combining the power of Essbase and the built-in data visualization features of Oracle Analytics Cloud (OAC)! As a “team building activity,” a group of Edgewater Ranzal consultants recently decided to play a thrilling children’s game of laser tag one evening.  At the finale of the four-game match, we were each handed a score card with individual match results and other details such as who we hit, who hit us, where we got hit, and hit percentage based on shots taken.  Winners gained immediate bragging rights, but for the losers, it served as proof that age really isn’t just a number (my lungs, my poor collapsing lungs).  BUT…we quickly decided that it would be fun to import this data into OAC to gain further insight about what just happened.

Analyzing Results in Essbase

Using Smart View, a comprehensive tool for accessing and integrating EPM and BI content from Microsoft Office products, we sent the data straight to Essbase (included in the OAC platform) from Excel, where we could then apply the power of Essbase to slice the data by dimensions and add calculated metrics. The dimensions selected were:

  • Metrics (e.g. score, hit %)
  • Game (e.g.Game 1, Game 2, Total),
  • Player
  • Player Hit
  • Target (e.g. front, back, shoulder)
  • Bonus (e.g. double points, rapid fire)

With Essbase’s rollup capability, dimensions can be sliced by any one item or at a “Total” level. For example, the Player dimension’s structure looks like this:

  • Players
    • Red Team
      • Red Team Player 1
      • Red Team Player 2
    • Blue Team
      • Blue Team Player 1
      • Blue Team Player 2

This provides instant score results by player, by “Total” team, or by everybody. Combined with another dimension like Player Hit, it’s easy to examine details like number of times an individual player hit another player or another team in total. You can drill in to Red Team Player 1 shot Blue Team or Red Team Player 1 shot Blue Team Player 1 to see how many times a player shot an individual player. A simple Smart View retrieval along the Player dimension shows scores by player and team, but the data is a little raw. On a simple data set such as this, it’s easy to pick out details, but with OAC, there is another way!

Laser Tag 1

Even More Insight with Oracle Analytics Cloud (OAC)

Using the data visualization features of OAC, it’s easy to build queries against the OAC Essbase cube to gain interesting insight into this friendly folly and, more importantly, answer the questions everybody had: what was the rate of friendly fire and who shot who? Building an initial pivot chart by simply dragging and dropping Essbase dimensions onto the canvas including the game number, player, score, and coloring by our Essbase metric “Bad Hits” (a calculated metric built in Essbase to show when a player hit a teammate), we discovered who had poor aim…

Laser Tag 2

Dan from the Blue team immediately stands out as does Kevin and Wayne from the Red team!  This points us in the right direction, but we can easily toggle to another visualization that might offer even more insight into what went on. Using a couple of sunburst type data visualizations, we can quickly tie who was shooting and who was getting hit – filtered by the same team and then weight by the score (and also color code it by team color).

Laser Tag 3

It appears that Wayne and Kevin from the Red Team are pretty good at hitting teammates, but it is also now easy to conclude that Wayne really has it out for Kevin while Kevin is an equal opportunity shoot-you-in-the-back kind of teammate!

Reimagining the data as a scatter plot gives us a better look at the value of a player in relation to friendly fire. By dragging the “Score” Essbase metric into the size field of the chart, correlations are discovered between friendly fire and hits to the other team.  While Wayne might have had the highest number of friendly fire incidents, he also had the second highest score for the Red team.  The data shows visually that Kevin had quite a few friendly fire incidents, but he didn’t score as much (it also shows results that allow one to infer that Seema was probably hiding in a corner throughout the entire game, but that’s a different blog post).

Laser Tag 4

What Can You Imagine with the Data Driving Your Business?

By combining the power of Essbase with the drag-and-drop analytic capabilities of Oracle Analytics Cloud, discovering trends and gaining insight is very easy and intuitive. Even in a simple and fun game of laser tag, results and trends are found that aren’t immediately obvious in Excel alone.  Imagine what it can do with the data that is driving your business!

With Oracle giving credits for a 30-day trial, getting started today with OAC is easy. Contact us for help!

ORACLE HYPERION CALC MANAGER – Part 4 – Creating RuleSets

In Part 1 of this series, we introduced Calc Manager, providing a general overview and explanation of some new terms.  In the second post we walked through the development of a Planning rule that utilized a run time prompt.  Part 3 covered templates available with Calc Manager.

In this, the final post in this series, we’ll step through the creation of a ruleset.  Rulesets are equivalent to Business Rule Sequences in Hyperion Business Rules.

We’ll begin by logging on to Hyperion Workspace and navigating to Calc Manager.  Once in Workspace, the navigation path is:  Navigate -> Administer -> Calculation Manager.

Once in Calc Manager, you’ll land on the System View tab, which appears as follows:

 

Once again, I’ll use my EPMA enabled version of my Planning app based on Sample.Basic.

To create a new ruleset, right click on the “RuleSets” node under your Planning app and select New.  You’ll be prompted to give the ruleset a name.  I’ll name mine Process_Application.  Additionally, you can change the app/database for this ruleset in this dialog box.

After I click OK,  the following screen loads:

You can display the rules available for your rule set by expanding the tree until you see the rules for your database. 

To add rules to the ruleset, simply drag and drop them onto the Ruleset Designer on the right side of the screen.

By default, the rules will run sequentially.  If you wish for rules to execute in parallel, select the RuleSet name within the RuleSet designer.  Check “Enable Parallel Execution” on the Properties tab at the bottom of the screen.

In order to run the script, save, validate, and deploy to your Planning application.

The series of posts that we’ve put together this summer were designed to give a user a basic understanding of how to work with Calculation Manager.  With any new technology, its best to dive in and immerse yourself to speed through the learning curve – Calculation Manager is no different.  Take the opportunity to experiment with the tool.  I feel that you’ll find it easy to learn the basics and before long you’ll be developing your own rules.

If you have any questions about Calc Manager, please leave a comment on any of the posts in this series, or reach out to me via email at jrichardson@ranzal.com.

ORACLE HYPERION CALC MANAGER – Part 3 – Working with Templates

In Part 1 of this series, we introduced Calc Manager, providing a general overview and explanation of some new terms.  In the second post in the series, we walked through the development of a Planning rule that utilized a run time prompt.  In this post, we’ll explore templates provided within Calc Manager.

As with the Rule Designer, which is a great tool to help less experienced developers build rules, templates provide a simple way to develop rules for basic tasks in Planning and Essbase…tasks such as copying, clearing, exporting, allocating, and aggregating data.  In addition, you can design your own templates.

We’ll begin by logging on to Hyperion Workspace and navigating to Calc Manager.  Once in Workspace, the navigation path is:  Navigate -> Administer -> Calculation Manager.

Once in Calc Manager, you’ll land on the System View tab, which appears as follows:

Once again, I’ll use my EPMA enabled version of my Planning app based on Sample.Basic.

To access predefined templates, right click on “Rules”.  Once you give the rule a name, the graphical designer is launched.  In the “Existing Objects” window, you should find a list of the pre-existing templates.  A list of the system templates follows:

CLEAR DATA

In order to use the system template to Clear Data, drag and drop “Clear Data” from the System Templates to the Rule Designer.  This will then invoke a member selection window asking you to specify the data to clear.  Keep in mind that this template generates a calc script utilizing the CLEARBLOCK command as opposed to a CLEARDATA command.

In my sample app, I select “FY11” for the Years dimension and “Final” for the Version dimension.  The dropdown box for “Clearblock Option” can be used to define the blocks to be cleared…”All” is the default.  The code that is generated appears below.

FIX ("FY11","Final")
  CLEARBLOCK ALL;
ENDFIX

COPY DATA

The Copy Data template helps to walk the calc developer through the process of copying data from one slice of the database to another.

In the remainder of the wizard, you select the “Copy From” member and the “Copy To” member.  The calc script generated follows:

FIX (@RELATIVE("Measures" , 0),@RELATIVE("Periods" ,0),@RELATIVE("Product" , 0),@RELATIVE("Market" , 0),@RELATIVE("Years" , 0),"Budget")
DATACOPY "Working" TO "Final";
ENDFIX

AMOUNT-UNIT-RATE

The Amount-Unit-Rate template allows the developer to build a calc script to solve for either an amount, unit, or rate, basically whichever is missing.  I’ve added a couple of measures to my application to facilitate the demo.  Using the member selection wizard, I’ve selected “Sales” as my amount, “Cases” as my unit, and “Revenue per Case”  as my rate.  The script generated by the template follows:

"Sales"(
  IF ("Sales" == #missing and "Cases" != #missing and "Revenue per Case" != #missing)
    "Sales" = "Cases" * "Revenue per Case";
  ELSEIF ("Sales" != #missing and "Cases" == #missing and "Revenue per Case" != #missing)
    "Cases" = "Sales" / "Revenue per Case";
  ELSEIF ("Sales" != #missing and "Cases" != #missing and "Revenue per Case" == #missing)
    "Revenue per Case" = "Sales" / "Cases";
  ELSE
    "Sales" = "Cases" * "Revenue per Case";
  ENDIF
)

ALLOCATIONS

Two types of allocation templates are provided within Calc Manager.  The first template, Allocate Level to Level,  allows you to allocate from one level to another.   In my example with my Planning app, you would use this template to allocate marketing expenses  from product family to product using a driver like revenue.  This approach utilizes @ANCESTVAL to build the script.

The second template, Allocate Simple, allocates values based on a predefined relationship, such as Marketing->Market * Cases/Cases->Market.

Both templates walk the developer through the setup of the allocations, selecting members that are fixed throughout the process, offset members (if any), etc.

AGGREGATION

The aggregation template aids the developer to create a script to aggregate the application.  The first screen of the wizard, pictured below, allows you to select members for the FIX statement in the aggregation – here you would limit the calc to a particular version, scenario, or your non aggregating sparse dimension members.

The next screen prompts for dense dimensions to aggregate.  However, if dynamic calcs are properly utilized, this should not be necessary.

The third screen asks for sparse dimensions for the aggregation.  You should exclude any non aggregating sparse dimensions from this selection.

Next, you’re prompted for partial aggregations of dense dimensions.  Again – if dynamic calcs are used properly, this should not be an issue.

In the final screen of the wizard, the developer selects settings for the script…

The code generated by Calc Manager follows:

SET AGGMISSG ON;
SET FRMLBOTTOMUP ON;
SET CACHE HIGH;
FIX (@RELATIVE("Years" , 0),"Working","Budget")
CALC DIM ("Product");
CALC DIM ("Market");
ENDFIX

Please note that this code is not optimized.  In this example, I would use the following:

AGG (“Product”,”Market”);

The code as generated by Calc Manager will result in an extra pass through the database – the calc can be accomplished with a single pass.  Additionally, AGG can be used in place of CALC DIM if there are no formulas on the dimensions being calculated.  Generally speaking, stored formulas on sparse dimensions should be avoided due to performance issues.

SET Commands

The next template walks the user through setting various SET commands for the calc.  This is a fairly straightforward exercise.

EXPORT DATA

This is another straightforward template that helps create a data export calc script.  You need to define the fixed members for the export,  delimiter, #MISSING value, export type (flat file, relational), etc.

In the final part of this series, due for posting on August 13, we’ll walk through the creation of a ruleset.  If you have any questions before the next post, please leave a comment!

ORACLE HYPERION CALC MANAGER – Part 2 – Creating a Planning Rule

In Part 1 of this series we introduced Calc Manager, providing a general overview and explanation of some new terms.  In this post, we will walk through the development of a rule for Hyperion Planning using the graphical interface within Calc Manager.

Again, in order to access Calc Manager, log on to Hyperion Workspace.  Once in Workspace, the navigation path is:

Navigate->Administer->Calculation Manager.

Once in Calc Manager, you’ll land on the System View tab, which appears as follows:

 

For purposes of this demonstration, I have created an EPMA enabled Planning application from the Sample.Basic application that we all know and love.  When the Planning node is expanded, this is what I see:

First, to help illustrate functionality available in Calc Manager, I’m going to create a script component that contains my standard SET commands for the rule.  In order to create the script component, right click on “Scripts” and click on “New”.  Give your script a name and click on “OK”.  This will launch the Component Designer.

From here, you have two options.  If you know what your SET commands need to be, you’re free to type them in directly.  If you wish to be prompted through the process, click on the  button at the top left corner of the Component Designer window.  This will launch a window with all of the calc functions and SET commands.  The following shot displays the function selection interface for SET commands.

For my purposes, I’m going to directly type my SET commands into the Component Designer.  Once complete, save and validate.

Think of script components as an easy way to reuse code…SET commands, standard cube aggregations and the like. 

Once we have saved the script component with our SET commands, it’s time to develop our rule.  To begin, right click on “Rules” under the database node and select “New”.    Give your rule a name and click on “OK”.  This will launch you into the Rule Designer window.

In this example, I’ll create a rule that aggregates the cube, using a run time prompt for the Version dimension.

We can now begin to develop our rule.  First, we’ll select the script component for our SET commands that we developed earlier.  Simply drag this into the rule designer to the right of “Start”.  The Rule Designer window now looks like this:

Let’s take this opportunity to create our variable for the Version dimension run time prompt.  Go to the “Tools” menu and select “Variables”.  Once the Variable Navigator launches, expand the Planning, application, and database nodes.  I’m going to create a run time prompt variable for the Version dimension.  Right click on your rule name and select “New”.  Once I populate the fields on the “Replacement” tab, my screen looks like this:

When complete, save the variable.  Now, back to our rule…

We’re going to specify members for our “Fix” statement.  To do this, select “Member Range” in the New Objects portion of the Rule Palette.  

To add to the rule, drag and drop to the right of the SET command script.  My screen looks like this:

Next, we’ll populate the members for our Fix statement.  I’ll start with Measures.  For my rule, I want to select all of the level 0 measures.  Once I click on the Value field for the Measures dimension, an Actions box appears. 

I want to select a Function.  This invokes the function selection window that we observed earlier.  I want to select @LEVMBRS from the list, which will then prompt for the dimension and level number.

I select Measures from the drop down box and enter “0” for the level name.  I’m going to repeat this process for all of my dense and non aggregating sparse dimensions, with the exception of the Version dimension.  This will be handled via the run time prompt.  For the Version dimension, select “Variable” in the Actions box.  Change the Category selection to “Rule” and this is what we see.

Highlight the variable and click OK.  My member range box looks like this:

Now, we’ll develop the script component to aggregate the Product and Market dimensions.  I’m going to drag a script from the “New Objects” portion of the Rule Palette into my member range.  The graphical display looks like:

Again, I’m going to select a function (AGG in this case).  I then select Products and Market from the dimension selector. 

Now, save and validate.  To deploy the rule to Planning, select Quick Deploy:

Once deployed, the rule can be run from Planning.

In this post, we’ve provided a walk through on developing a new rule using the graphical designer.  More experienced developers can directly code the calc in script mode.  To convert to script mode, select “Edit” and “Script” from the menu. 

In the next post, due by July 31, we’ll explore templates and ruleset creation.  In the meantime, please leave a comment if you have any questions!

Special uses for Life Cycle Management (LCM)

In my previous post, I showed how to use LCM to back up or copy an entire planning application environment.  Here I’ll expand on that subject a bit by showing some other uses you may find handy.  This is by no means meant to be an exhaustive collection – just a few suggestions you may find useful and which may provoke ideas for other uses.

Copy single dimension from one app to another

This can be done for any dimension, including the standard planning dimensions.  Here, to expand on the subject we are also going to export from the “Organization” dim in one planning app & import to the “Entity” dim in another.

Select the artifacts to export (no harm in copying everything).

Click thru the next screen to this one.

Since we need to change the dimension name, we must export to files, not directly to the other app.

Then click thru the remaining screens to execute the migration.

After the export finishes, go to the \Hyperion\Common\Import_export directory. Under the Username@Directory folder find the files you exported.

In the “info” directory, edit “listing.xml” changing all instances of “Organization” to “Entity”.

Now find the XML file for the dimension to be migrated with name change.

Rename to the target dimension name.

Now edit the file to change “Organization” to “Entity”.

In Shared Services->Application Groups->File System, open the extract and select the (newly renamed) Entity dimension.

Define Migration…

…and click thru the remaining screens to execute the migration.

Lights-out Operation

In Shared Services select the artifacts to be backed up and define migration.

We need to back it up to files so type in a folder name…

…and click thru the remaining screens until you get here.

Now, instead of clicking the Execute button, click “Save Migration Definition.”

You will get this screen…

…click “Save.”

Shared Services wants to save “MigrationDefinition.xml” where you tell it to.

You can name the file any name you want (I suggest using naming conventions to differentiate the operation being saved) and anywhere you want.

After saving the file you will get this…

…click “Close” and the backup definition will be saved.

Now look in the Automation folder where the xml file was saved.

The file has everything Shared Services needs to run the backup from the command line utility except the USERID and PASSWORD.

Edit in TextPad or other text editor and type in a Userid and password.

After running the job the password is automatically encrypted.

The job is run from an Oracle supplied process, “utility.bat.”

…and you pass the path information to the migration definition file you created above.”

You should channel the output to a log file so you will have a record of success or failure.  The following message is an excerpt from that log which, in turn, lists the detailed log location & name and whether the process was a success or failure and it will also tell exactly where any failure occurred in the process.

I hope I’ve shown you enough to get you started using LCM.  It can certainly be a valuable tool, whether you want to do one-time tasks or perform lights-out operations such as regular backups.  The important thing to remember is to test it and see what, if any, problems you will have and either fix those or work around them.

Welcome to Ranzal & Associates’ Blog!

Ranzal specializes in Business Intelligence and Business Performance Management with a concentration in Oracle/Hyperion’s toolkit. Ranzal works closely with corporate executives, line-of-business management, end users, and information systems departments alike to address the business issues and challenges inherent in data gathering, management, and dissemination. Organizations from various industries have engaged Ranzal with outstanding results.

Topics of discussion: