Standardization of Comparative Analytics in Healthcare

A Comprehensive Solution for Value-Based Care

As healthcare providers are quickly consolidating and purchasing smaller health systems, standardization is paramount to enable comparative reporting across organizations or sites that facilitates changing attitudes, decreased costs, and better, more cost effective care. Provider systems need to operate independently using a standardized enterprise system process to effectively make decisions around costs, health outcomes, and patient satisfaction.  Without standardization, the analysis of metrics can require considerable work and time and create issues when comparing like sites since appropriate metrics can mean totally different things at the underlying base member calculation.

A standardized solution is simple – an enterprise-based model that allows data to be shared across systems and applications to facilitate comparative analytics with data integrity:

MH Image 1

Such a solution offers the ability to compare productivity indices across departments against national standards using a standard calculation approach with federated master data across all toolsets, resulting in comparative analytics to drive efficiencies and value-based care:

MH Image 2

Oracle Business Intelligence Cloud Service (BICS) September Update

The latest upgrade for BICS happened last week and, while there are no new end user features, it is now easier to integrate data. New to this version is the ability to connect to JDBC data sources through the Data Sync tool.  This allows customers to set up automated data pulls from Salesforce, Redshift, and Hive among others.  In addition to these connections, Oracle RightNow CRM customers have the ability to pull directly from RightNow reports using Oracle Data Sync.  Finally, connections to on premise databases and BICS can be secured using Secure Socket Layer (SSL) certifications.

After developing a customer script using API calls to pull data from Salesforce, I am excited about the ability to connect directly to Salesforce with Data Sync. Direct connections to the Salesforce database allows you to search and browse for relevant tables and import the definitions with ease:

blog

Once the definitions have been imported, standard querying clauses can create the ability to include only relevant data, perform incremental ETLs, and further manipulate the data.

While there are no new features for end users, this is a powerful update when it comes to data integration. Using APIs to extract data from Salesforce meant that each extraction query had to be written by hand which was time consuming and prone to error.  With these new data extraction processes, BICS implementations and integrating data becomes much faster, furthering the promise of Oracle Cloud technologies.

A Comparison of Oracle Business Intelligence, Data Visualization, and Visual Analyzer

We recently authored The Role of Oracle Data Visualizer in the Modern Enterprise in which we had referred to both Data Visualization (DV) and Visual Analyzer (VA) as Data Visualizer.  This post addresses readers’ inquiries about the differences between DV and VA as well as a comparison to that of Oracle Business Intelligence (OBI).  The following sections provide details of the solutions for the OBI and DV/VA products as well as a matrix to compare each solution’s capabilities.  Finally, some use cases for DV/VA projects versus OBI will be outlined.

For the purposes of this post, OBI will be considered the parent solution for both on premise Oracle Business Intelligence solutions (including Enterprise Edition (OBIEE), Foundation Services (BIFS), and Standard Edition (OBSE)) as well as Business Intelligence Cloud Service (BICS). OBI is the platform thousands of Oracle customers have become familiar with to provide robust visualizations and dashboard solutions from nearly any data source.  While the on premise solutions are currently the most mature products, at some point in the future, BICS is expected to become the flagship product for Oracle at which time all features are expected to be available.

Likewise, DV/VA will be used to refer collectively to Visual Analyzer packaged with BICS (VA BICS), Visual Analyzer packaged with OBI 12c (VA 12c), Data Visualization Desktop (DVD), and Data Visualization Cloud Service (DVCS). VA was initially introduced as part of the BICS package, but has since become available as part of OBIEE 12c (the latest on premise version).  DVD was released early in 2016 as a stand-alone product that can be downloaded and installed on a local machine.  Recently, DVCS has been released as the cloud-based version of DVD.  All of these products offer similar data visualization capabilities as OBI but feature significant enhancements to the manner in which users interact with their data.  Compared to OBI, the interface is even more simplified and intuitive to use which is an accomplishment for Oracle considering how easy OBI is to use.  Reusable and business process-centric dashboards are available in DV/VA but are referred to as DV or VA Projects.  Perhaps the most powerful feature is the ability for users to mash up data from different sources (including Excel) to quickly gain insight they might have spent days or weeks manually assembling in Excel or Access.  These mashups can be used to create reusable DV/VA Projects that can be refreshed through new data loads in the source system and by uploading updated Excel spreadsheets into DV/VA.

While the six products mentioned can be grouped nicely into two categories, the following matrix outlines the differences between each product. The following sections will provide some commentary to some of the features.

Table 1

Table 1:  Product Capability Matrix

Advanced Analytics provides integrated statistical capabilities based on the R programming language and includes the following functions:

  • Trendline – This function provides a linear or exponential plot through noisy data to indicate a general pattern or direction for time series data. For instance, while there is a noisy fluctuation of revenue over these three years, a slowly increasing general trend can be detected by the Trendline plot:
Figure 1

Figure 1:  Trendline Analysis

 

  • Clusters – This function attempts to classify scattered data into related groups. Users are able to determine the number of clusters and other grouping attributes. For instance, these clusters were generated using Revenue versus Billed Quantity by Month:
Figure 2

Figure 2:  Cluster Analysis

 

  • Outliers – This function detects exceptions in the sample data. For instance, given the previous scatter plot, four outliers can be detected:
Figure 3

Figure 3:  Outlier Analysis

 

  • Regression – This function is similar to the Trendline function but correlates relationships between two measures and does not require a time series. This is often used to help create or determine forecasts. Using the previous Revenue versus Billed Quantity, the following Regression series can be detected:
Figure 4

Figure 4:  Regression Analysis

 

Insights provide users the ability to embed commentary within DV/VA projects (except for VA 12c). Users take a “snapshot” of their data at a certain intersection and make an Insight comment.  These Insights can then be associated with each other to tell a story about the data and then shared with others or assembled into a presentation.  For those readers familiar with the Hyperion Planning capabilities, Insights are analogous to Cell Comments.  OBI 12c (as well as 11g) offers the ability to write comments back to a relational table; however, this capability is not as flexible or robust as Insights and requires intervention by the BI support team to implement.

Figure 5

Figure 5:  Insights Assembled into a Story

 

Direct connections to a Relational Database Management System (RDBMS) such as an enterprise data warehouse are now possible using some of the DV/VA products. (For the purpose of this post, inserting a semantic or logical layer between the database and user is not considered a direct connection).  For the cloud-based versions (VA BICS and DVCS), only connections to other cloud databases are available while DVD allows users to connect to an on premise or cloud database.  This capability will typically be created and configured either by the IT support team or analysts familiar with the data model of the target data source as well as SQL concepts such as creating joins between relational tables.  (Direct connections using OBI are technically possible; however, they require the users to manually write the SQL to extract the data for their analysis).  Once these connections are created and the correct joins are configured between tables, users can further augment their data with data mashups.  VA 12c currently requires a Subject Area connected to a RDBMS to create projects.

Leveraging OLAP data sources such as Essbase is currently only available in OBI 12c (as well as 11g) and VA 12c. These data sources require that the OLAP cube be exposed as a Subject Area in the Presentation layer (in other words, no direct connection to OLAP data sources).  OBI is considered very mature and offers robust mechanisms for interacting with the cube, including the ability to use drillable hierarchical columns in Analysis.  VA 12c currently exposes a flattened list of hierarchical columns without a drillable hierarchical column.  As with direct connections, users are able to mashup their data with the cubes to create custom data models.

While the capabilities of the DV/VA product set are impressive, the solution currently lacks some key capabilities of OBI Analysis and Dashboards. A few of the most noticeable gaps between the capabilities of DV/VA and OBI Dashboards are the inability to:

  • Create the functional equivalent of Action Links which allows users to drill down or across from an Analysis
  • Schedule and/or deliver reports
  • Customize graphs, charts, and other data visualizations to the extent offered by OBI
  • Create Alerts which can perform conditionally-based actions such as pushing information to users
  • Use drillable hierarchical columns

At this time, OBI should continue to be used as the centerpiece for enterprise-wide analytical solutions that require complex dashboards and other capabilities. DV/VA will be more suited for analysts who need to unify discrete data sources in a repeatable and presentation-friendly format using DV/VA Projects.  As mentioned, DV/VA is even easier to use than OBI which makes it ideal for users who wish to have an analytics tool that rapidly allows them to pull together ad hoc analysis.  As was discussed in The Role of Oracle Data Visualizer in the Modern Enterprise, enterprises that are reaching for new game-changing analytic capabilities should give the DV/VA product set a thorough evaluation.  Oracle releases regular upgrades to the entire DV/VA product set, and we anticipate many of the noted gaps will be closed at some point in the future.

The Role of Oracle Data Visualizer in the Modern Enterprise

Chess as a metaphor for strategic competition is not a novel concept, and it remains one of the most respected due to the intellectual and strategic demand it places on competitors. The sheer combination of moves in a chess game (estimated to be more than the number of atoms in the universe) means that it is entirely possible that no two people have unintentionally played the same game.  Of course, many of these combinations result in a draw and many more set a player down the path of an inevitable loss after only a few moves.  It is no surprise that chess has pushed the limits of computational analytics which in turn has pushed the limits of players.  Claude Shannon, the father of information theory, was the first to state the advantages of the human and computer competitor attempting to wrest control of opposing kings from each other:

The computer is:

  1. Very fast at making calculations;
  2. Unable to make mistakes (unless the mistakes are part of the programmatic DNA);
  3. Diligent in fully analyzing a position or all possible moves;
  4. Unemotional in assessing current conditions and unencumbered by prior wins or losses.

The human, on the other hand, is:

  1. Flexible and able to deviate from a given pattern (or code);
  2. Imaginative;
  3. Able to reason;
  4. Able to learn [1].

The application of business analytics is the perfect convergence of this chess metaphor, powerful computations, and the people involved. Of course, the chess metaphor breaks down a bit since we have human and machine working together against competing partnerships of humans and machines (rather than human against machine).

Oracle Business Intelligence (along with implementation partners such as Edgewater Ranzal) has long provided enterprises with the ability to balance this convergence. Regardless of the robustness of the tool, the excellence of the implementation, the expertise of the users, and the responsiveness of the technical support team, there has been one weakness:  No organization can resolve data integration logic mistakes or incorporate new data as quickly as users request changes.  As a result, the second and third computer advantages above are hindered.  Computers making mistakes due to their programmatic DNA will continue to make these mistakes until corrective action can be implemented (which can take days, weeks, or months).  Likewise, all possible positions or moves cannot be analyzed due to missing data elements.  Exacerbating the problem, all of the human advantages stated previously can be handicapped; increasingly so depending on the variability, robustness, and depth of the missing or wrongly calculated data set.

With the introduction of Visual Analyzer (VA) and Data Visualization (DV), Oracle has made enormous strides in overcoming this weakness. Users now have the ability to perform data mashups between local data and centralized repositories of data such as data warehouses/marts and cubes.  No longer does the computer have to make data analysis without the availability of all possible data.  No longer does the user have to make educated guesses about how centralized and localized data sets correlate and how it will affect overall trends or predictions.  Used properly, users and enterprises can leverage VA/DV to iteratively refine and redefine the analytical component that contributes to their strategic goals.  Of course, all new technologies and capabilities come with their own challenges.

The first challenge is how an organization can present these new views of data and compare and contrast them with the organizational “one version of the truth”. Enterprise data repositories are a popular and useful asset because they enable organizations to slice, dice, pivot, and drill down into this centralized data while minimizing subjectivity.  Allowing users to introduce their own data creates a situation where they can increase data subjectivity.  If VA/DV is to be part of your organization’s analytics strategy, processes must be in place to validate the result of these new data models.  The level of effort that should be applied to this validation should increase according to the following factors:

  • The amount of manual manipulation the user performed on the data before performing the mashup with existing data models;
  • The reputability of the data source. Combining data from an internal ERP or CRM system is different from downloading and aligning outside data (e.g. US Census Bureau or Google results);
  • The depth and width of data. In layman’s terms, this corresponds to how many rows and columns (respectively) the data set has;
  • The expertise and experience of the individual performing the data mashup.

If you have an existing centralized data repository, you have probably already gone through data validation exercises. Reexamine and apply the data and a metadata governance processes you went through when the data repository was created (and hopefully maintained and updated).

The next challenge is integrating the data into the data repository. Fortunately, users may have already defined the process of extracting and transforming data when they assembled the VA/DV project.  Evaluating and leveraging the process the user has already defined can shorten the development cycle for enhancing existing data models and the Extract, Transform, and Load (ETL) process.  The data validation factors above can also provide a rough order of magnitude of the level of effort needed to incorporate this data.  The more difficult task may be determining how to prioritize data integration projects within an (often) overburdened IT department.  Time, scope, and cost are familiar benchmarks when determining prioritization, but it is important to take revenue into account.  Organizations that have become analytics savvy and have users demanding VA/DV data mashup capabilities have often moved beyond simple reporting and onto leveraging data to create opportunities.  Are salespeople asking to incorporate external data to gain customer insight?  Are product managers pulling in data from a system the organization never got around to integrating?  Are functional managers manipulating and re-integrating data to cut costs and boost margins?

To round out this chess metaphor, a game that seems to be nearly a draw or a loss can breathe new life by promoting a pawn to a lost queen. Many of your competitors already have a business intelligence solution; your organization can only find data differentiation through the type of data you have and how quickly it can be incorporated at an enterprise level.  Providing VA/DV to the individuals within your organization with a deep knowledge of the data they need, how to get it, and how to deploy it can be the queen that checkmates the king.

[1] Shannon, C. E. (1950). XXII. Programming a computer for playing chess. The London, Edinburgh, and Dublin Philosophical Magazine and Journal of Science, 41(314), 256-275. doi:10.1080/14786445008521796

Default and User Friendly Prompting With BI Publisher

As mentioned in the previous post, Dynamic Report Grouping with Oracle BI Publisher, Edgewater Ranzal is working with a client to convert XML Publisher reports to BI Publisher reports. As part of Ranzal’s initiative, we began looking for opportunities to improve the user interface as well as create a standard methodology that report developers could utilize in the future. One of the initial areas we focused on was to improve the prompting feature. To this effort, we concentrated on:

  • Presenting prompts to the user within the BI Publisher tool
  • The displaying of user-entered prompt values within the report
  • Creating a methodology of implementation for report developers.

As expected, many of the reports had time prompts (date, period, or year), but the existing reports did not have default prompt values.  Although it is not published in any Oracle documentation we have seen, Oracle offers five functions that can be inserted into the Default Value option of the parameter:

{$SYSDATE()$}
{$FIRST_DAY_OF_MONTH()$}
{$LAST_DAY_OF_MONTH()$}
{$FIRST_DAY_OF_YEAR()$}
{$LAST_DAY_OF_YEAR()$}

*Note that you also have to set the Data Type to Date for these parameters. 

Simple numeric mathematical calculations can be performed with these functions to add some flexibility.  For instance, the previous day’s date would be displayed as

{$SYSDATE() – 1$}

By using these functions in conjunction with the Date String Format in the parameter options section, a variety of date value defaults can be displayed in the prompting section of the report. The following table is a sample of the prompts, Default Value, and Date Format Strings that were deployed at the client:

BI Publisher post 2 1

It is very important to understand that, regardless of the Date Format String settings, the actual value used in the date functions is the full date string and an optional numeric number added or subtracted that represents days. For instance, if the Default Value is set as {$FIRST_DAY_OF_YEAR() + 1$} (first day of year plus one) and the Date Format String is set to MM, the user would still see 01 as the default value because the actual value generated (and then converted to the month number) is 20XX-01-02T00:00:00.000+HH:00 (Jan 2, 20XX).

Because the optional numeric value used in the function refers only to days, and no logic can be written into the Default Value function, there is a natural limitation that prohibits generating anything beyond a period and/or year plus or minus one. For instance, if a client wants a prompt default value for two years ago, logic cannot be written to determine if the current year or previous year was a leap year and conclude whether to subtract 365 x 2 = 730 or 366 x 2 = 732 from the first day of year function (or system date function, depending on your preference).

Understandably, this problem would only occur two days every four years (December 31st of both a leap year and the year following a leap year); however, extrapolating from this logic is evidence of the difficultly in going back two or more months from any date function because of the variable numbers in a month. We observed an even more complicated version of this issue when the client wanted to have the default values for a period range equal to the previous period (i.e. during Q3, From Period defaults to 04 and To Period defaults to 06). Depending on the current period, the From Period needs to default from three to five periods ago and the To Period needs to default from one to three periods ago. Further exacerbating this problem was the year prompt that, during Q1, needs a default value of the previous year.

The final piece of the puzzle when using any parameters with the date data type is realizing that the bind value passed to your data model is the full date/time string. Our client exclusively used SQL in their data models; therefore, it was only a matter of using Oracle SQL’s native TO_CHAR function to convert the date/time string to a relationally comparable value as such:

BI Publisher post 2 2

The Ranzal team then looked to streamline and simplify interaction between parameters, parameter input requirement evaluation, and the RTF templates. The client’s reports had up to twelve parameters that required user input, and they used XLST logic to evaluate whether or not users had supplied values. As mentioned in previous posts, XLST is not a robust language as it relates to logical evaluations; after all, XLST was designed to consume XML documents and output new documents (in this case, RTF based reports). Because of these limitations, the initial RTF templates used the following logic (white space added for clarity):

BI Publisher post 2 3

Using this method, each parameter is evaluated until a null value is found, and then the remaining parameters are evaluated for a null value. When the XLST consumes the XML, each required parameter that the user has not entered a value for results in an additional warning line message. From a developer point of view, each additional required parameter requires the creation of additional lines of code. While the example above only has four required parameters, reports with many required parameters become quite convoluted and difficult to maintain.

Ranzal again turned to the logic processing capabilities of Oracle SQL. Within the data model, we created a new data set to create a parameter status (named PARAM_STAT) to look at the bind values passed by the BI Publisher parameters. We came up with the following SQL template to generate a more succinct warning message within the column value PARAM_STAT (note that n denotes the number of required report parameters):

BI Publisher post 2 4

There is an argument for creating a SQL statement that concatenates all missing parameter names with a comma and then uses logic to correct the punctuation; however, we felt that from a reusability standpoint, it would be best to compartmentalize the statement using the WITH TABLE1 statement. Using the above SQL template, report developers merely have to update the following lines:

  • 4 – 7:  Data model parameter names (i.e. :PRMBU) and report parameter names (i.e. Business Unit)
  • 10:  Data model parameter names (i.e. :PRMBU)
  • 15 – 20:  Replace the PARAM_COUNT comparison values (n, n – 1, and n – 2)

Using the example above with the required parameters for year, period, business unit, and ledger, the following SQL statement was generated:

BI Publisher post 2 5

Using this parameter status value results in a much more succinct XLST template that needs only to evaluate whether PARAM_STAT has a value (white space added for clarity):

BI Publisher post 2 6

The client has hundreds of BI Publisher reports and plans to continue to develop additional reports as their Oracle Business Intelligence platform becomes the standard reporting tool. By using the SQL template along with the simplified RTF template, the real work becomes creating the table, pivot table, or chart within the RTF template.  Fortunately, the Ranzal team was able to create an Excel-based VBA macro that automates the generation of the majority of the client’s templates. We will discuss this tool in a later post.

These two examples demonstrate the Ranzal team’s commitment to taking a proactive stance to examining current processes and looking for opportunities for improvement.  As we worked through the technical details of this implementation, we carefully balanced the idea of a user-centered experience against the often competing need for a simplified methodology and process for report developers. To accomplish the latter, we went through several phases of technical refinement, demonstrated the process to developers, and provided thorough documentation. This ensures that when the time comes to turn the maintenance of these reports over to the client, there is a complete knowledge transfer as well.

Dynamic Report Grouping With Oracle BI Publisher

Edgewater Ranzal is working with a client to convert XML Publisher and nVision reports to BI Publisher as part of a larger initiative to consolidate reporting under the Oracle Business Intelligence solution.  The client currently does not use the BI Publisher Layout Editor, but rather relies on RTF templates to display results to the user. One nVision report in particular presented a challenge because of the grouping requirements.

The report requires users to enter an Account range, and the resulting tables need to be grouped by individual accounts. There are also seven optional wildcard prompts, including Product and Department.  If the optional prompts are left blank, the results need only to be grouped by the individual accounts; however, if values are entered for the optional prompts, the subsequent results need to be grouped by all resulting unique combinations for the prompts entered. For instance, if the user enters 10000 to 20000 for the account range and all departments beginning with 10, an example report may be:

BI publisher 1

Sample Data Only

If the user adds the wildcard criteria (%) for the optional prompt Product, the results are further grouped by Product as such:BI publisher 4

Sample Data Only

One approach to meet the client’s needs for this particular nVision report is to use the grouping feature in the Microsoft Word BI Publisher Table Wizard. To fulfill the requirement of grouping only when users enter values in the optional prompts, the XLST statements that create groupings can be wrapped in IF statements that evaluate the prompts for null entries. For example, Department and Product can be evaluated as such (note that all prompts begin with PRM):

BI publisher 2

This approach has two drawbacks: First, after adding the additional evaluations for the other five optional prompts, the XLST within the RTF template becomes quite convoluted. The second, and more limiting factor, is that the IF statements to end the “group by” statements (below the table template) result in either a non-functioning report or incorrect groupings.

 A second and more viable option is to use the Data Model to leverage Oracle SQL which has more robust logic evaluation capabilities than XLST. To meet the client’s needs, an additional column was added to evaluate user prompt values (note that all prompt variables begin with PRM, and CHR(13) returns a carriage return):

BI-publisher-5

This column evaluates each optional prompt for a value and, if the user has made an entry, concatenates the common name (i.e. Department and Product) with the value of the respective row and a carriage return; otherwise, a null value is returned. The Microsoft Word BI Publisher Table Wizard feature is then used to generate the XLST needed to group the table by the new column GRPOPT in a more succinct fashion:

BI publisher 3By leveraging the Data Model SQL rather than XLST within the RTF template, Ranzal recreated this nVision report while maintaining the sort of dynamic capabilities normally seen in Oracle Business Intelligence Answers. The additional SQL statements add no overhead to the BI Publisher report, and the report runs as fast as or faster than, the corresponding nVision report. The delivery of this report opens up new reporting possibilities for the client, and it reinforces Ranzal’s expertise in the Oracle Business Intelligence tool set.

Strategic Finance for Service Lines: Finding Opportunities for Growth

Healthcare providers are always seeking innovations and evaluating strategic alternatives to meet growing demand while healthcare legislation is adding challenges to an already complex industry. As the population continues to age and development increases the demand for high quality healthcare, providers must put themselves in the optimal financial position to deliver the best care to the communities that depend on them.

To do this, many are turning to a service line model so that they can identify profitable areas of their organization that will generate future growth and capture market share.  In order to identify the strategic value of each service line, organizations need to have a long-range planning tool that will enable them to quickly forecast each of their service lines over the next 3-5 years and evaluate growth areas so that investments can be made in the service lines that will generate the greatest long-term economic value for the organization.

Utilizing Oracle’s Hyperion Strategic Finance, Edgewater Ranzal has helped many organizations chart a realistic financial plan to achieve their long-range goals and vision.  Some of the ways that we have helped organizations are as follows:

  • Forecast detailed P&Ls  for each service line using revenue and cost drivers such as number of patients, revenue per procedure, FTE’s, and payer mix to accurately forecast profit levels of each service line.
  • Easily consolidate the forecasted service line P&Ls to view the expected financial results at a care center level or for the Healthcare organization as a whole.
  • Layer into the consolidation structure potential new service lines that are being evaluated to understand the incremental financial impact of adding this new service line.
  • Run scenarios on the key business drivers of each service line to understand how sensitive profitability, EPS, and other key metrics are to changes in variables like number of patients, payer mix, FTE’s and salary levels.
  • Compare multiple scenarios side by side to evaluate the risks and benefits of specific strategies.
  • Evaluate the economic value of large capital projects needed to grow specific service lines beyond their current capacity.  Compare the NPV and IRR of various projects to determine which ones should be funded.
  • Layer into the consolidation structure specific capital projects and view their incremental impact on revenue growth and profitability at the service line level as well as the healthcare organization as a whole.
  • Use the built in funding routine of HSF to allocate cash surpluses to new investments and to analyze at what point in time the organization is going to need to secure more debt financing to fund its operations and its capital investments in specific service lines.

Regardless of where you are in your understanding, analysis, or implementation of service lines, a viable long-term strategy must include a critical evaluation of how will you identify the market drivers for growth, measure sustainable financial success, and adjust to changing economic, regulatory, and financial conditions.