Ushering in the New Era of Hyperion Strategic Finance

Welcome to the first installment of our new Hyperion Strategic Finance (HSF) blog series. Edgewater Ranzal’s HSF team has been working closely with Ranzal’s other Hyperion practices (HFM, FDM, Planning/Essbase etc.) to hone in on how HSF can be utilized to its full potential in accordance with the other product offerings.  As part of that process we felt it was important to start a dialogue (blog) to share some of our insights on various topics ranging from new product release info to cutting edge integration best practices.  We are hoping this series will be a good resource for you and your organization on your HSF journey.

Given the major changes to HSF in the 11.1.2.2 release, and the exciting product roadmap ahead of us, I thought it would be good to start the series by discussing “The New Era of Hyperion Strategic Finance.”

So…What’s new?

With the 11.1.2.2 release comes probably the most significant change to the product since it was acquired by Hyperion.  A shift in the user interface from a traditional thick client to an Excel based Smart View Add-in is at the core of the change.  The enhancement enables the end user to perform the majority of HSF modeling activities directly in an Excel workbook.  With this change, the legacy reporting in the HSF client has been REPLACED with Excel reporting via Smart View.  This means that those who choose to implement 11.1.2.2 will be required to use the Smart View based reporting and/or export the data to an external database (i.e. Essbase) to utilize other reporting tools (i.e. Financial Reports, OBIEE etc.).  For any current HSF users looking to upgrade, it is important to note that the process of upgrading to 11.1.2.2 automatically converts existing HSF reports into the Smart View format, however, existing charts/graphs will need to be rebuilt and some formatting issues have been identified which may require some re-work.  While this does introduce a big change for end users, it also presents a great opportunity by opening up native Excel functionality like allowing the use of Excel graphing, conditional formatting, highlight sums, and the group / ungroup data feature etc. To view the full list of new features you can look through the 11.1.2.2 Read me, available here.

Why make the change?

Some existing clients have asked me why this change was made in the first place.  The answer is really two-fold:

1. Tighter Integration with Hyperion Planning: If there has been one consistent theme throughout all of my meetings/conversations with Oracle’s HSF/Planning development team it has been a desire to continuously improve the integration between Hyperion Planning and HSF.  The integration I’m referring to doesn’t stop at data, but also includes seamlessly integrating the end user experience.  Everything from selling the tools as a combined solution (i.e. bundled pricing) to having both HSF and Planning users interact with the tool in a similar manner (i.e. Smart View) have been or will be addressed.  Below I will outline some additional roadmap items that are planned that will continue this theme.

2. Reporting: All legacy HSF users that have spent time creating, formatting, and modifying HSF reports are well aware that reporting has NOT been an area of strength previous to 11.1.2.2.  Both Oracle and our implementation team consistently receive requests to have HSF reporting operate “more like Excel.”  This move is a direct response to those requests, and it is definitely a big step in the right direction.

Current Challenges:

As with any major change / new release there are going to be some growing pains and the 11.1.2.2 release of HSF is no different.  We have been working closely with Oracle over the past couple of months to identify any issues, make recommendations, and test fixes that have been applied.  The main point I want to make clear is that from a functional perspective the tool has the EXACT same capabilities.  You are simply changing the way you do things, not what can be done.  There is a bit of a learning curve to understand the new menu bar, short cut keys etc…but in general it still functions much like its predecessor with the added benefit of the Excel look and feel.

A couple of things to look out for if you will be implementing AND going live prior to the 11.1.2.3 release:

1. Issues with large numbers of active reports:  We have experienced some performance issues when working with a file that has numerous reports (standard or freestyle) open at once.  This includes reduced speed of the check out / in process, flickering upon calc/refresh, and occasional freezing of the application.  Currently, the product seems to work more seamlessly when working with just the accounts tab or 1-2 reports, however, fixing this is at the top of Oracle’s priority list and we expect it to be addressed sooner rather than later.

2. Renaming of Time Periods: You want to avoid renaming the default time periods in the entities (i.e. Changing 2013 to FY13).  In the current release this can cause some calculation issues in system regarding the funding routine.  Again, this has been identified by Oracle and is expected to be resolved in the next patch set or release.

3. Smart View Parity: There are some features that are not currently in the Smart View interface which may require users to revert back to the old HSF client.  Some of these features include the debt/depr schedulers, ECM/ACM etc.  This means that the end user may have to jump between interfaces in certain instances.  The good news is that these features are expected to be added to the Smart View functionality in the next release (11.1.2.3) and can still be utilized in the traditional client if need be.  To see a full list of these items look to the 11.1.2.3 roadmap column below.

4. With the change in the technology, come some changes to the infrastructure component, so it is important to discuss these requirements with your technical team to diagnose hardware and software needs before moving forward. It is also important to note that the upcoming release of 11.1.2.3 will NOT be backward compatible with 11.1.2.2.  So, if you have a multiproduct implementation with integration components you would need to upgrade the entire suite.

Product Direction / Road Map

The above picture is a snap shot of the HSF roadmap given to me by the Oracle product team.  You can see the current release (as of this blog post) on the far left, calendar year 2013’s scheduled release (11.1.2.3) in the middle, and the subsequent releases on the far right.  The focus this year is to truly stabilize the Smart View integration and incorporate all of the standard features of the thick client into the Smart View interface.  The exciting part of this is how the changes in 11.1.2.2 have set up the product for even more advancement in the future.  Post 11.1.2.3, a lot of the heavy lifting for HSF’s major changes will be complete.  This will allow the team to focus on true feature enhancements like adding a monthly depreciation scheduler or addressing the concept of parent level scenario modeling (possibly an idea for a future blog post!).  In addition, if you look at the future direction, in the Enterprise Readiness section, you will see items such as LRP Integration to Planning, Automated Data Loads, DRM support etc…This represents a future state which allows a user to manage both data and metadata in a consistent manner between HSF, Planning, and Essbase.  Imagine a world where you can have one excel worksheet open with your HSF model and another with a LIVE connection to an HSF reporting cube (via Essbase) for Ad-hoc purposes!  Not only that but a world where the Essbase cube is automatically updated as you make changes to the HSF model, meaning no manual metadata management between applications or tedious mappings that need to be maintained. This type of enhancement truly empowers the user to focus on the modeling aspects of HSF while allowing Essbase to shine for management reporting – truly using the right tool for the right job.

Our Recommendation

Overall we are very excited about the direction Oracle is heading with HSF.  The Smart View capability in 11.1.2.2 is really just the first step in what we see as a continued effort to make the product better for its users.  With that in mind, we have begun multiple 11.1.2.2 implementations, however, it is understood in all cases that there will be some issues to work through and there are no pending deadlines where these issues could put the project success at risk.  In fact, the Go Live dates are not expected to occur until a time when we believe 11.1.2.3 and / or an 11.1.2.2 patch set will be available.  Given that, we do recommend that all new HSF clients seriously considering starting with v11.1.2.2 while taking note of the challenges mentioned above.  Initially this approach eliminates the need for your end users to learn two different user interfaces.  Additionally, if you are willing to give feedback, as an 11.1.2.2 client you will definitely have the ear of the Oracle team as it pertains to resolving any existing product issues, as well as requesting new enhancements for the future.  Oracle is very eager to make this release a success and they truly value any input early adopters can provide.  So if you have the patience to work through some bumps in the road, and the time to resolve the issues you may encounter, I would definitely encourage giving the 11.1.2.2 release serious consideration.

I hope you found this information helpful.  We look forward to coming out with many more in the future. In that vein, if you have any ideas / request for blog topics please feel free to leave them in the comments section or reach out to me directly at rmeester@ranzal.com and we will make sure to address them in future posts.

About the Author

Ryan Meester is a Practice Director for the Strategic Planning Practice at Edgewater Ranzal.  His first encounter with HSF dates back to 2004 as a Consultant with Hyperion in the HSF practice.  After three years in that capacity, leading projects and assisting with business development efforts, Ryan co-founded Meridian Consulting International with two of his Hyperion colleagues, Andrew Starks and Ricardo Rasche.  At Meridian, Ryan, Andrew, and Ricardo focused exclusively on HSF implementation services until Meridian was acquired by Edgewater Ranzal in May of 2010.  This was a strategic acquisition for both Meridian and Ranzal. Both organizations were seeing more and more multiproduct implementations which required a broader EPM focus. The acquisition effectively rounded out Ranzal’s EPM service offering by adding HSF expertise to their repertoire.

Using Data Visualization and Usability to enhance end user reporting – Part 4: Tying it all together

Now that the foundations have been set in my last three posts, in this final post I’ll share how we can create reports, leveraging:

• Standard definitions and metrics
• The understanding how the users  will consume data and interact with the system

To effectively create reports, make sure to follow these key best practices:

1. Reduce the data presented by focusing on the important information. For example,  rather than showing two lines for revenue actuals and revenue budget, try showing one for the difference. Users can identify trends much more quickly when there are fewer objects to focus on.

2. Concentrate on important data and consolidate it into chunks. If you have two charts, use the same color for revenue on both of them. This makes it easier to interpret and see trends between them

3. Remove non-data items, especially the images, unnecessary lines and graphics.  This helps the user focus on the actual data, so they can see trends and information rather than clutter.

Here is an example of two reports with the same data. The first provides a table with various colors, bold fonts and line. The second report highlights the important areas/regions. Your eyes are immediately drawn to those areas needing attention. Table two allows the user to draw accurate conclusions more effectively and in a much shorter timeframe.

These are some general practices which can be applied in most cases and will give users a much more positive experience with your reporting system. If you need help making sense of your reporting requirements, creating a coherent reporting strategy or implementing enterprise reporting, please contact us at info@ranzal.com.

Using Data Visualization and Usability to Enhance End User Reporting – Part 3: The Balance between Data and Visual Appeal

In part three of my four part blog series, I’ll provide an overview of the important balance between data and visual appeal when creating reports, including some of the latest research and findings.

Many users believe that once you have the metrics in place and understand what data users want, the next step is to create the reports

In reality, a lot of thought and a careful eye are required when making design considerations to create charts, grids and tables that convey the details in the simplest terms for user understanding. The right design choices enable users to see easily the trend, outliers, or items needing attention.

Many people think that the more data they can cram in, the better. However, studies have shown that the average person can only store 6 chunks of information at a time.  Depending on how flashy and distracting your graphics and marketing logos are, you may have already used up half of your brain’s capacity, without getting to any reports or dashboards.

Graphic overload may make one consider removing all distracting graphics, highlights, bolds and visual clutter to show the data – novel concept right?

But this is not the solution. There has been lots of visualization studies and research done over the past century that have uncovered that eliminating graphics altogether is not the solution to this dilemma.

In fact, there are several leading experts on this topic, including three key people, who are leading the charge against clutter and visual distraction, cheering for more measured and thoughtful chart and dashboard visual design. These individuals are:

·         Edward R. Tufte

·         Colin Ware

·         Stephen Few

All three have published several books explaining how we interpret visual data, including what makes our eyes drawn to color and form, and what aids understanding. It also explains “chart junk” – a term first coined by Tufte in 1983. Tufte defines “chart junk” as simply:

“Conventional graphic paraphernalia routinely added to every display that passes by: over-busy grid lines and excess ticks, redundant representations of the simplest data, the debris of computer plotting, and many of the devices generating design variation.”

The key concept of “chart junk” leads into another of Tufte’s mantras called the “Data Ink” ratio. The idea here is that by minimizing the non-data ink you are maximizing the data ink.  In other words,  that you can achieve the ideal balance of data and design by removing borders, underlines, shading and other ink elements which don’t convey any messages

There are a lot of available resources out there on this topic by these authors and others.

Stay tuned for my final blog post, in which I will demonstrate how to effectively put these concepts  into practice when creating reports.

Ranzal UK are presenting at UKOUG 2012

I’m please to announce that Ranzal UK consultants are presenting papers on both days of the UKOUG Hyperion conference this year.  The conference takes place on October 23rd & 24th at the Park Plaza hotel in central London.

http://hyperion.ukoug.org/

On the Tuesday my colleague Mark Drayton will be talking about a recent implementation of Hyperion Planning and HPCM in the session HPCM and Hyperion Planning: A Cost Allocation Process‘.   This will be especially interesting to those of you who want to see how Mark made use of batch updates to HPCM Stage 1 Drivers and Assignments.

(http://hyperion.ukoug.org/default.asp?p=9676&dlgact=shwprs&prs_prsid=7944&day_dayid=65)

On Wednesday, our Senior Consultant Alecs Mlynarzek will be delivering a presentation titled ‘Planning Integration with Task Lists‘, which describes techniques we have used to enable users to launch integration tasks from the Task List UI within Hyperion Planning, a feature not available out of the box.

(http://hyperion.ukoug.org/default.asp?p=9676&dlgact=shwprs&prs_prsid=7907&day_dayid=66)

Also on the Wednesday, Ranzal’s UK Infrastructure Lead Dave Hogg and I will be presenting various innovative techniques that we have implemented for clients in order to meet technical challenges – this session is called ‘Guide to Using EAS to Maintain Batch Environment Variables‘ (http://hyperion.ukoug.org/default.asp?p=9676&dlgact=shwprs&prs_prsid=7891&day_dayid=66)

We really hope that you can come and attend one of our sessions – please come and say hello and we can tell you more about how we are growing, and the exciting projects that we are currently delivering.

Security configuration in TaskFlows (EPMA 11.1.2) – Cookbook style

TaskFlows in EPMA can be used to sequence any number of EPMA operations such as dimension updates, application deployments, and data synchronisations.  They are also able to execute batch jobs and send emails, all of which makes them potentially useful for automation and integration of EPMA applications.

TaskFlows can be scheduled by a built-in scheduler in TaskFlow Management, but there is also the option to run task flows ‘interactively’ (i.e. on-demand).   For one particular client application, this was an attractive approach to delivering a capability to kick-off processes in-day on an ad-hoc basis, but we needed to ensure that some kind of security could be applied to the different task flows so that ‘run’ permissions could be assigned to the correct people for the correct TaskFlow.

Our client system was based on 11.1.2, and the procedure below is based on 11.1.2.1.  I could not find much documentation around doing this on 11.1.2, and it appears that the controls around TaskFlow security have changed since 11.1.1.3.  Therefore I decided to set up a simple PoC as described below.

The summary approach is as follows:

• Set up several simple TaskFlows
• Set up security roles (Create the aggregated HSS roles)
• Modify the Access Control to the TaskFlows
• Set up several users & provision them to have access to different roles
• Demonstrate the effect of these different levels of access

Set up some simple TaskFlows

TaskFlows can be created to run many different EPM processes such as cube deployment, data import/export, but for our example we are just going to get the TaskFlows to execute a simple batch file, which will write to an output file, which indicates that our TaskFlow has successfully run. 

The administrator should have, as a minimum, the following roles provisioned in Shared Services, to be able to create TaskFlows:

TaskFlow administration is then accessed in EPMA, via ‘Navigate’ > ‘Application Library’, and then choose menu ‘Administration’  > ‘Manage Task Flows’, which will bring up the following screen:

From here, new TaskFlows can be created, and existing ones can be edited, deleted, scheduled and executed.

Below I have set up TaskFlow TF_1 to execute a batch job in Stage1.

This is done by selecting the processing tab & choosing ‘Hub’ from the ‘Application’ drop-down, ‘Execute’ from the ‘Action’ drop-down, and specifying the name of the batch file to execute.

(by default batch files are located in %HYPERION_HOME%\Common\Utilities, and output is routed to Oracle\Middleware\user_projects\domains\EPMSystem folder). 

(Note, the example has Stage2 in it – TaskFlows can contain any number of Stages – this example has 2 stages purely as a result of experimentation – each stage simply executes a basic batch file to create an output file)

For the purposes of  testing access control, I created the following 4 TaskFlows (by using the ‘Save As’ control) :

Now that we have set up several TaskFlows, we will set up some users to demonstrate access control.  The first step is to create ‘aggregated roles’ in Shared Services (HSS) to allow each TaskFlow to have a different level of access, by associating it with different roles.

Set up security roles (Create the aggregated HSS roles)

The pre-defined HSS Administrator roles of ‘Manage TaskFlows’ & ‘Run TaskFlows’ will give default access to a user to run manage / run a new TaskFlow, until the access control of that TaskFlow is edited.

To satisfy our requirement to have differing levels of access we create different  ‘aggregated’ roles in HSS, with different access levels.  There are 2 access levels for TaskFlows

• ‘Manage’ access allows the user to create, delete, schedule & run TaskFlows
• ‘Run’ access allows the user to only run TaskFlows.

(Having no access, simply means that a user will only be able to ‘View’ TaskFlow status.)

For our example, we want to achieve the following access to TaskFlows TF_1 -> TF_4:

This requires us to create the following aggregated roles, which will eventually be associated with the specific TaskFlows :

Creation of aggregated  roles is simple. 

1.   Log into HSS as admin, expand the ‘User Directories’, ‘Native Directories’ tree in the explorer and right-mouse-click on ‘Roles, and choose ‘New’:

2.   This brings up the ‘Create Role’ dialog – we enter a Role name, and choose ‘HUB-11.1.2’ as the product group (each product group has its own list of relevant ‘Available Roles’, but TaskFlow related roles exist in the ‘HUB…’ group):

3.   Select ‘Next’ and from the left hand list, select ‘Manage TaskFlows, and move it to the right hand list, and ‘Save’ :

4.   For the Roles which will have Run access, we simply choose the ‘Run TaskFlows’ role from the left-hand list instead.

5.   When we have finished creating all the new aggregated  roles we should have a list in HSS like this:

The next task is to edit our TaskFlows to utilise these new roles.

Modify the Access Control to the TaskFlows

As admin, log into EPMA & navigate to the ‘Manage TaskFlows’ screen.

Select the TaskFlow TF_1 & choose ‘Access Control’:

Each TaskFlow has the option to set the role allowed for ‘Manage’ access and the role allowed for ‘Run’ access:

The drop-down for the ‘Manage Permission Role’ displays all roles (pre-defined and aggregated) that have ‘Manage TaskFlow’ level access:

The drop-down for the ‘Execute Permission Role’ displays all roles (predefined and aggregated) that have ‘Run TaskFlow’ level access:

You can see the Aggregated roles that we created earlier are available.  The ‘Administrator…….Taskflow’ roles correspond to the predefined ‘Manage..’ and ‘Run..’ roles directly under the Administrator node in HSS:

So we can now associate our task flows with the different aggregated roles available, for both the ‘Manage’ & the ‘Run’ access level.

So for TaskFlow TF_1, we set the roles as follows:

The TaskFlows TF_2, 3 & 4 are configured as per the following table:

Now we need to provision our users to give then the correct level of access.

Set up several users & provision them to have access to different roles

Set up the example users (User1 -> User5) in HSS & provision them in the usual way as follows :

I have found that a user must be provisioned with ‘Create Integrations’ role as well as the ‘Manage’ or “Run’ roles, in order to get access to the ‘Manage TaskFlows’ screen.

(These are the roles required for each user in order to achieve the required access to the TaskFlows – it will be necessary to assign users to additional roles to achieve access rights to other parts of the product !)

Running a ‘Provisioning Report’ from HSS on the 5 users for Shared Services applications, we can see what their provisioning is:

Now we can login to EPMA for each of the 5 users and we can see the combined effect of the role creation, user provisioning & ‘Access  Control’ configuration:

User1:

User2:

User3:

User4:

User5:

Additional Information

The pre-defined Shared Services roles ‘Manage TaskFlows’ and ‘Run TaskFlows’ apply by default to any new TaskFlows that are created, because new TaskFlows have the access control roles of ‘Administrator Manage Taskflow’ and ‘Administrator Execute Taskflow’ set by default.  Any new TaskFlows with these default access control settings will only be available to users provisioned with the pre-defined Shared Services roles ‘Manage TaskFlows’ and ‘Run TaskFlows’.  Conversely, any users provisioned only with these pre-defined Shared Services roles, would have access only to TaskFlows with these default access control settings

The ‘Create Integrations’ role that is required to give any user access to the ‘Manage Taskflows’ screen can be embedded into the aggregated roles, rather than provision the role to the user separately:

I found that If a user has roles with ‘Manage TaskFlow’ access to TaskFlow A, and ‘Run TaskFlow’ to TaskFlow B, when the user logs in they appear to have ‘Manage TaskFlow’ access to both TaskFlow A & B. This means that that user can delete TaskFlows that they were not intended to have ‘Manage TaskFlow’ rights to , so it would be best to avoid this scenario.

Finally I found that logging out of one user session and logging back in as another user, without closing down the browser session,  had the effect that the second user’s privileges appeared to be the same as the first, even if their access was lower.  I worked around this by exiting all browser sessions & clearing the browser cache in between logins.  This could have been a local environment issue.

References

The Oracle library has the following guidance on TaskFlows and security relating to them:

http://docs.oracle.com/cd/E17236_01/epm.1112/hss_admin_1112200.pdf chapter 6 – Managing Roles, chapter 8 -Managing TaskFlows

http://docs.oracle.com/cd/E17236_01/epm.1112/epma_admin.pdf part IV, Using Task Automation

Techniques for Creating, Loading, and Optimizing a Simple Essbase ASO Application

A couple of recent projects have required us to build an Essbase database to provide a subset of upstream system data for downstream consumer systems such as HPCM.  The process included dimension updates, data loads and custom calculations.  ASO was the chosen Essbase technology because we were potentially dealing with large data volumes, relatively simple hierarchy structures, and only a small number of custom calculations that could be easily modeled in MDX within minimal performance impact.

The principle was that an overnight batch would be used to completely rebuild the ASO cube each night, including any metadata restructures that were necessary, followed by a full reload of data.

The high level process is as follows:

The starting point was to use a ‘stub’ application as a template for the metadata rebuild.  This is an ASO Essbase application with all dimension headers present, all POV dimensions present (Years, Periods, Scenarios etc), and all volatile hierarchies represented by the hierarchy headers only.  This ASO application serves as a “poor man’s MDM” which allows us to have application, dimension and hierarchy properties all pre-set.  The main advantage of the stub outline is that it creates a natural defragmentation of the target ASO application which improves query performance, and reduces dimension build times to the minimum. This is analogous to a relational database where you want to ‘truncate’ tables and/or compress, as opposed to deleting and reading all the time – there is a gradual growth.  A good tip is to defragment build dimensions in order from smallest to largest in terms of volumes.

A sample ‘Stub.otl’ outline looks something like the following. In this case, the stub outline is modeled after the new embedded Fusion G/L Essbase cube:

As can be seen, the volatile dimensions (Budget centre, Balancing Entity, Accounts, etc) are each populated with a single hierarchy header (e.g. BE_dummy) whereas the static dimensions (AccountingPeriod, Balance Amount etc) are complete, and will not be the subject of a dimension load in the MaxL.  Static dimensions which contain members with MDX member formula will persist (although the formula will not necessarily validate at this stage as they may depend on members that have not yet been rebuilt).

The first part of the batch process is to use this Stub outline to replace the outline in the ‘user’ ASO cube (i.e. the cube that will be restructured and loaded with data).  The MaxL will clear data & replace the .otl file in the user application with the .otl file from the ‘stub’ application

A simplified version of the MaxL is as follows (normally passwords would be encrypted):

This simply copies the Stub.otl file into the ’user’ ASO cube database folder & names it with the target database name – it will be available as soon as the application is reloaded.

The next section in the MaxL would be a standard dimension build of those volatile dimensions – the primary consideration when building the hierarchies is that the ASO restrictions on hierarchies are met otherwise the outline will not verify.  This is not covered here – we assume that incoming master data is pre-validated to meet these requirements, but the summary of dimension rules for ASO is as follows:

• ASO dimensions can contain hierarchies of 2 types – ‘Stored’ or ‘Dynamic’
• A dimension must be tagged as Multiple Hierarchies Enabled’ or “Dynamic” if it contains two or more hierarchies
• The first hierarchy in a dimension where Multiple Hierarchies enabled is specified  must be defined as a ‘Stored’ hierarchy
• Stored Hierarchies are generally only additive as they only allow the + or ~ consolidation operators
• Dynamic Hierarchies can contain any consolidation operators, and members can contain formulas.
• For alternate hierarchies, where shared members may be required, Stored hierarchies can only contain one instance of a member (to avoid double counting), but subsequent Stored hierarchies can contain members previously defined in previous stored hierarchies

Once metadata has been loaded, the data load can be carried out.

Once this is complete, we have a fully loaded ASO cube, which we can retrieve data against using either SmartView or an Essbase report script (for example, when we are supplying filtered data to our downstream systems).

The example Smart View retrieve template below is a straightforward report with periods as columns and 550 rows of level 0 Budget Centres, with all other dimensions set as filters.

The Essbase application log shows that the above SmartView query took over 16s to execute.  This report layout may or may not be representative of real world queries / reports but the object of the exercise here is to speed this up for in-day usage.

ASO databases do not use calculation scripts to consolidate the data so the traditional BSO approach to consolidation cannot be used.  Instead, ASO will attempt to dynamically calculate upper level intersections, which, while resulting in much faster batch processing times, may result in longer than necessary retrieval times.

What we can do to improve this situation is use the ‘Query Tracking’ facility in ASO to capture the nature of queries run against the ASO cube, and build retrieval statistics against it. These statistics can then be used to build aggregation views tailored to retrieval patterns in the business.

This relies on us having some predefined definitions of the kinds of queries that are likely to be run – SmartView report templates, Web Analysis pages & Financial Reports definitions will all be suitable.

In this example, we use the above SmartView template as a basis for creating an Essbase Report script as follows:

This report mimics the SmartView template, and we use it during the overnight batch to capture the query characteristics using Query Tracking. One of the reasons to use report scripts is that if you use the query designer (or the Spreadsheet Retrieval Wizard if you are using a REALLY old version of the Excel Add In), it can save a report script output. MDX queries will have a similar affect.

The sequence of MaxL steps is as follows:

• Switch on Query Tracking
• Run one or more Essbase Report Script(s)
• Run ‘execute aggregate process’ command to create aggregate views

 The MaxL to accomplish this is as follows:

The ‘execute aggregate process’ command is issued with the ‘ based on query_data’ option to tell Essbase to use query patterns picked up by Query Tracking to build the aggregation views.  Essbase will build as many views as necessary until the ‘total_size’ limit is reached.  This limit may need tweaking so as to give the desired improvement in performance whilst also conserving disk space (which may get swallowed up with larger ASO cubes).  The particular example also runs in a matter of seconds, but the addition of more sample reports needs to be managed to ensure that the batch run time does not exceed its window.  It should be noted that one can process hierarchies without the query tracking, but there are restrictions on what alternate hierarchies get processed, and this is a very good technique when you are trying to improve performance on “alternate rollups”.

When this has been executed, users should see an improvement on query performance.

Our SmartView query was rerun, and the log file demonstrates the reduction in query time to less than 1 second :

This approach lends itself to situations where the ASO outline is likely to change frequently.  Changes in metadata mean that aggregation views created and saved in EAS cannot necessarily be reused – new level 0 members will not necessarily invalidate the aggregate views, but new upper level members, or restructured hierarchies definitely will invalidate these views. The rationale for this is because the ASO aggregation engine constructs multiple “jump” points based on the most recent level hierarchy  –  if I were going to oversimplify what was happening in a BSO world, imagine level zero stored, level one as dynamic calc, level two stored, level three as dynamic calc, and level four stored. In any instance, there would never be more than one level of dynamic calc. I don’t know if this is still the case, but this may be why ASO cubes seem to like symmetrical vs. ragged hierarchies a bit easier – it makes the derivation of what should be calculated vs. dynamic easier.

Using Data Visualization and Usability to Enhance End User Reporting – Part 2: Usability

In this second part of my four-part blog series, I’ll be looking at usability and what it really means for report design.

Usability takes a step back and looks at the interactions users have with reports. This includes how users actually use the reports, what they do next, and where they go. If users refer to another report to compare values or look at trends, they should think about condensing these reports into a single report or even create a dashboard report with key metrics. This way, users have a clear vision of what they need or what Oracle calls “actionable insight”. From there, users can provide other users with guided navigation paths based on where they actually go today.

With improved usability, users can review an initial report and easily pull up additional reports, possibly from a different system or by logging into the general ledger/order entry system to find the detail behind the values/volumes. With careful design, this functionality can be built into reporting and planning applications, to provide a single interface and simplify the user interactions.

Here is a real world example of how improved usability can benefit users on a daily basis: Often a user will open a web browser and an item is highlighted as a clickable link. Normally if you click on the link, it will open up in the same window, causing you to lose the original site that you visited. By clicking the back button, you can also lose the first site that you visited. With improved usability, clicking on a link would result in a new pop-up window, so when finished users are able to choose which windows to close and return to the original window.

The challenge with achieving improved usability, is that many organizations lack visibility into how users actually use reports, especially with users spread all over the world. One possible solution is for organizations to ask users about their daily activities. The issue here is that often users are uncomfortable discussing what they do and where they go online. Companies can overcome this challenge by enforcing sessions where they can ask leading questions including why users feel uncomfortable sharing their daily activities. These types of sessions can help organizations uncover the root causes/issues, giving them the insight to delve deeper to understand what lies behind the report request.

One common scenario where you could apply this approach is when users ask for a full P&L for their business units, so they can compare and ring anyone over budget.  By having a session to understand the users’ specific needs/daily activities, organizations can instead produce a dashboard that highlights the discrepancies by region. With this dashboard, there is no need to compare and analyze; users can open the dashboard and see the indicators with a click of a button. Users can drill down for more information while placing that call!

In conclusion, improved usability means helping users get to the answer quicker, without having to do a lot of unnecessary steps. The old adage is true – KISS – Keep It Simple Stupid!