Article Features Kaleida Health’s Use of OBIEE to Combat the Flu

Kaleida Health’s Flu Monitoring Dashboard was developed quickly using Oracle Business Intelligence Enterprise Edition to provide insightful reporting regarding patients with the flu, including H1N1.

The innovative use of Oracle Business Intelligence was recently detailed in a SearchOracle.com article. Kaleida used their Oracle Business Intelligence Enterprise Edition (OBIEE) dashboard technology for analyzing emergency room visits where people arrived at a clinic with flu-like symptoms, tracking various metrics that included what time of day or night the visit took place. With this information, Kaleida was able to effectively calculate when a spike may occur so they could start diverting patients with flu-like symptoms to another clinic, minimizing the possibility of flu patients infecting others in a crowded waiting room.

Read More...

A Better Average

Past Performance is no guarantee of the Future

How many of you have a metric based on an average over time (i.e. Average Sales for the past 12 months)? Simple mathematical averages are a great tool to quickly compare results to an expected result, based on historical performance. Unfortunately, in their simplicity also lies a key problem: They assume results will be flat, whereas real-world results will often plot onto a curve. Often, changes are not linear either due to seasonality, or an inherent exponential factor underlying a result.


Seasonality

Few, if any, businesses do not have any seasonality. Retail sales are greatest toward the end of the year, tourist destinations have the highest bookings during their respective high season, and real estate transactions occur more frequently in the spring. Activity for these businesses, at least in part, is affected by external factors causing results to fit a curve.


Exponential factors

While not necessarily readily apparent, often activity within an organization will change in an exponential manner.

• A simple example is interest income: Over time interest earned on an investment will exponentially increase due to compounded returns (this assumes no drawdown of the investment, as well as reinvestment of returns). Depending on the size of the investment, over a short period it can be feasible to assume a linear growth, but this will introduce greater and greater variances over time.
• A more complex example can be seen in revenues. Often, a portion of revenues are reinvested into generating greater revenues. Conceptually, this scenario is similar to the compounded interest scenario. This differs in that it is often difficult to determine a rate of growth based on an amount reinvested in a business.

Essbase to the rescue!

Among Essbase's many built-in functions is @TREND. In layman's terms, @TREND calculated a weighted average of a series of values. Options allow for fourdifferent weighting algorithms:

• Linear Regression - Standard linear regression (similar to a typical average), with an option to assign priority to points to adjust the importance of certain events.
• Single Exponential Smoothing - Weighting system giving more importance to earlier values than later values. Allows for an adjustment to how much more weight is applied to earlier values.
• Double Exponential Smoothing - Similar to Single Exponential Smoothing, but includes an additional adjustment to influence the resulting slope, or curve, of the result.
• Triple Exponential Smoothing - Builds on Double Exponential Smoothing with a third influence factor. This algorithm is particularly useful for seasonal values.

Some points to consider:

• #MISSING values: some algorithms remove #MISSINGs from the list of values (i.e. they are not treated as zeros), other algorithms do not allow any values to be #MISSING.
• Usage: The function may only be used in a calculation script; it may not be placed within a member formula. Inside of a calculation script the function must be associated with a member.
• The order of members passed to the function will influence the result, since weights are applied differently depending on where in the list a value appears. Consider carefully the order of members in the outline, whether that order will change, and what impact that will have. It may be useful to utilize the @LIST function to hardcode a specific order.

For more information on @TREND, see the "Trend Calculation Function" in the Essbase Technical Reference (this is available via the Oracle website if it is not installed on your system).

Direct Your Budgeting Process Using Hyperion Planning Task Lists

Ever wished you had more control over the budgeting process? Would you like to provide more direction to your budget managers/analysts? Then Hyperion Planning Task Lists might be just the thing for you.

Planning Task Lists guide end users through the budgeting process by directing them to perform specific tasks in a prioritized order. For example, you could start the process by having users read an instruction page that guides them through the system, details any drivers/assumptions, and provides pertinent milestone dates. After that, they could be directed to enter data for such items as new headcount, new capital, and discretionary expenses by being pointed to the appropriate web data input forms. You can also provide links to run calculations, review specific reports, and promote items up through the workflow process for approval.

Task lists are set up with links to each task and are prioritized as necessary. As part of the prioritization, tasks can also be set to be dependent on other tasks being completed first. So, as an example, prior to a user running a formula that calculates depreciation, they would first need to update a new capital web input form before they could proceed.

Other useful features of Task Lists include:

• Completion due dates and on-screen alerts: green for on schedule, yellow for approaching due date, red for overdue.
• Email alerts for tasks that are overdue. These alerts can be scheduled as frequently as every hour.
• The ability to view task status in summary and generate status reports.

Task Lists can also be viewed in two formats: Basic and Advanced modes. In Basic Mode, users only have the Task List to navigate through in the web interface. In Advanced Mode, users can view a Task List and can navigate through all the other areas in the Planning web interface.

Finally, you can set up different Task Lists for each functional area so that users responsible for sales, for example, will only see sales related tasks and users responsible for manufacturing will only see tasks relevant to them. Another nice feature is that you can set access to a Task List, preventing users from viewing other user’s lists. Task Lists can also be set up by planning process, so you could have a set for the annual budget, another set for the rolling forecast, and another for the long range plan.

Not all Hyperion Planning customers use Task Lists. But, if you are looking to provide your budget managers with more guidance and make the budgeting process more directed, implementing Task Lists might just do the trick.

BI Apps Performance Optimization and Consideration - Part1

This topic may be used at a high level but covers most of the common aspects of the performance tuning and optimization considerations for OBIEE deployments. This topic does not detail much about handling ETL performance tuning. Couple of points are picked from Oracle's recommendations

At a high level the following needs to be reviewed and analyzed

• Data model and custom star schema's
• Physical Layer (including Joins and Keys configured)
• Business Model and Mappings Layer (including Joins and measures defined)
• Presentation Layer Layout
• Application Performance (including Joins, and aggregate tables configured)
• Caching options configured
• Security and Personalization
• Initialization Blocks and Variables configured
• Investigate the use of aggregate tables and mini dimension tables to increase performance
• Define data archive strategy and table partition strategy to manage data sizes in the database
• Database optimization
• Hardware setup

Partitioning

• Consider partitioning large Fact tables having more than 20 million rows
• Identify eligible columns of type DATE for implementing range partitioning
• Connect to the Oracle BI Server repository and check the usage or dependencies on each column in the logical and presentation layers
• Analyze the summarized data distribution in the target table by each potential partitioning key candidate and data volumes per time range, month, quarter or year
• Basing on the compiled data, decide on the appropriate partitioning key and partitioning range for your future partitioned table
• The recommended partitioning range for most implementations is a month, though you can consider a quarter or a year for your partitioning ranges

The following columns may be considered as partitioning keys

W_AP_XACT_F on POSTED_ON_DT_WID
W_GL_ACCOUNT_D may be a Date WID
W_AR_XACT_F on POSTED_ON_DT_WID
W_GL_REVN_F on POSTED_ON_DT_WID
W_GL_COGS_F on POSTED_ON_DT_WID
W_TAX_XACT_F on POSTED_ON_DT_WID
W_GL_OTHER_F on ACCT_PERIOD_END_DT_WID

Storage Considerations for Oracle Business Analytics Warehouse

• Setting excessive parallel query processes
• Running multiple I/O intensive applications, such as databases, on a shared storage
  choosing sub-optimal storage for running BI Applications tiers
• Make sure you carefully plan for storage deployment, configuration and usage in Oracle BI Applications environment
• Avoid sharing the same RAID controller(s) across multiple databases
• Set up periodic monitoring of your I/O system during both ETL and end user queries load for any potential bottlenecks
• Update optimizer statistics
• Consider implementing Oracle RAC with multiple nodes to accommodate large numbers of concurrent users accessing web reports and dashboards
• Check for the database configuration parameters as per the recommendations specified in the install guide

Managing Slow Running Reports

• Analyze the session SQL
• Run Trace or Explain plan on query
• Analyze the logical joins leading to the slow performance
• Analyze the database
• Increase the log level to 5 and analyze NQLQuery.log file and look at Query string,
  Logical request, Physical SQL query, Query outcome status, Physical query response time, Rows returned to client
• Key approach should be to address performance issues as close to the data as is possible, moving processing down the stack to the data

The next part will be about handling performance at metadata, reports & dashboards, presentation catalogs

Optimization of Established Planning Applications – Part 1

A common question that I am often asked when first arriving at a client with established Planning applications is, “How can I improve my applications performance.” Two short–term initiatives that can often provide more robust performance for established Planning environments are the following:
1. Remove unnecessary history
2. Reorder outlines

Remove Unnecessary History

Often an inordinate amount of history maintained within Planning applications to facilitate year over year reporting. Maintaining excessive history in a Planning application creates unneeded blocks, the greater the number of blocks, the greater the processing time for calculations. While year over year analysis capabilities undoubtedly have to be maintained, I often leverage the practice of creating a reporting application to facilitate Planning Optimization.

Native Essbase reporting databases are developed to archive historical data. These new reporting databases are based on the existing databases within the Planning applications. All data not associated with generating future budgets or forecasts from each of the Planning applications is moved to the reporting database through partitioning, xrefs or data extracts and loads. Planning data (i.e. Budgets, Forecasts, and Plans) would then be moved into the reporting databases at scheduled intervals to allow the year over year analysis, in addition to allowing for an optimal configuration of planning.

Basic steps to moving history out of Planning:

1. Create native essbase reporting cubes to archive historical data
2. These new reporting cubes should be based on the existing cubes in the Planning applications
3. Remove all data not associated with generating future budgets or forecasts from each of the Planning applications
4. Load all historical to the new reporting cubes
5. Remove dimension members pertaining to historical data and alternate hierarchies from the Planning applications
6. Integrate current year data from the Planning applications to the reporting cubes
   

Reordering of Outlines

Industry wide standards recommend that outlines be structured in an hourglass shape. From top to bottom; dense dimension members with the most stored members to the dense dimension with the smallest number of stored members. Then sparse dimension members with the least stored members to the sparse dimension with the largest number of stored members.

I acknowledge that this rule doesn’t apply to every model; however I do suggest that application administrators run multiple iterations of a baseline calculation reordering the dimension to ascertain the optimal outline order for calculation processing. Reordering outlines to increase the chance of achieving a single anchoring dimension with multiple bitmaps often result in achieving the optimal calculation performance.

Administrators can simulate calculations using SET MSG ONLY in a calculation script. A simulated calculation produces results that help you analyze the performance of a real calculation that is based on the same data and outline.

By running a simulated calculation with a command like SET NOTICE HIGH, you can mark the relative amount of time each sparse dimension takes to complete. Then, by performing a real calculation on one or more dimensions, you can estimate how long the full calculation will take, because the time a simulated calculation takes to run is proportional to the time that the actual calculation takes to run.

While these steps aren’t the be all, end all to optimization, these initial steps will help get you started.

In the next blog, we’ll address leveraging multiple plan types and calculation running in topdown and serial modes

The Impact of IFRS for EPM Reporting – Part 3

In Part 3, I want to provide more detail on the similarities differences and convergences regarding: Consolidations, Joint Venture Accounting, and Equity Method Investees. The details below were from a presentation I viewed during a company sponsored educational seminar about IFRS.

Similarities

• The basis for determining whether or not subsidiaries are consolidated is based on control, but there are differences in the definition of control. Generally, subsidiaries subject to control by the parent are consolidated.
• Equity investments (referred to as “an associate” in IFRS) in which the investor has “significant influence” (generally 20% or more) but not consolidated is considered an equity method investment

Differences

Consolidation Model

• US GAAP – focus on controlling financial interest (FIN 46)
• IFRS – focus on the concept of the power to control – presumed to exist at 50% voting

Special Purpose Entities

• US GAAP – FIN 46 requires the primary beneficiary (determined based on the consideration of economic risks and rewards) to consolidate
• IFRS – consolidate when the substance of the relationship indicates that an entity controls the SPE – No concept of QSPE

Significant events between reporting dates

• US GAAP – Disclosed in financial statements when different dates are used
• IFRS – adjusted for in the financial statements

Joint Ventures

• US GAAP – generally accounted for using the equity method
• IFRS – either the proportionate consolidation method or the equity method

Convergence

FASB and IASB had a joint project that addressed non-controlling interests, which culminated in the issuance of FAS 160, Non-controlling Interests in the US and a revision of the accounting for non-controlling interests for IFRS.

IASB recently issued an exposure draft that proposes the elimination of proportionate consolidation for joint ventures.

In Part 4, I will provide more detail about our next two topics business combinations and inventory.

BICG in Jan/Feb 2010 Issue of Oracle Magazine

There is an article titled, 'Honoring Excellence' within the most recent issue of Oracle Magazine that provides visibility to the companies Oracle honored with awards in 2009. On page 28 of the printed copy, BICG is listed as the sole winner of the Oracle Business Intelligence/Enterprise Performance Management Implementation Partner of the Year Award.

View online article: http://www.oracle.com/technology/oramag/oracle/10-jan/o10awards.html