Monthly Archives: October 2016

Evolving Data Integration with Oracle Planning and Budgeting Cloud Service (PBCS)

October 26, 2016

Author: Rich Pallotta, Performance Architects

Oracle’s Planning and Budgeting Cloud Service (PBCS) is a significant advancement in the Oracle EPM (Hyperion) product suite’s steady evolution and, like biological evolution itself, it seems we’re in one of those “fits-and-starts” phases. In the big picture, PBCS is doing great, but now that we’ve made that leap to the cloud, you may have noticed the ecosystem surrounding the solution has a bit of catching up to do. If you’ve taken a look at moving to PBCS, you’ve probably also had to consider what to do about data integration.

I it could just be Oracle’s way of throwing spaghetti at the wall and seeing what sticks, but Oracle has many data integration choices with applications in the EPM stack: from Oracle Data Integrator (ODI), to Financial Data Management Enterprise Edition (FDMEE), Data Relationship Management (DRM), all the way to MySQL; each fulfills a useful niche in the new world…but it’s helpful to know where we’ve come from, what we have now, and where we’re going.

Many organizations have a significant investment in data integration people and technologies they rightly want to leverage but, right now, PBCS has seemingly has few hooks to utilize these capabilities. Oracle Hyperion Financial Data Quality Management (FDMEE) is baked into the platform but none of the other Oracle tools we’ve come to know and love are there. We have customers asking if this is the end for them.

There’s always that sunk cost, economic rabbit-hole discussion we can go down, or we can just consider the big picture again. If we think of information systems like plumbing systems, data comes from over there, it goes round-and-around and comes out here, at the end of that tortured metaphor is your financial plan.

Now it’s a matter of considering where the “round-and-around” will happen and if it will work in your particular situation. But every enterprise is very different, so back to the big picture again for some basic understanding; we still need to get PBCS the right data at the right time. If you already have on-premise data integration (ETL or ELT) tools hooked up to your ERP system or data warehouse, all you have to do is tweak your current data transformations and toss them across the valley to the opening arms of PBCS.

I know, “easier said than done,” but PBCS is no different than the on-premise version of Oracle Hyperion Planning in that regard: in the end they both need transformed data and metadata.  So on this side of the valley, you’ve already got ODI and DRM and all the other nice tools in place to transform your data. Good, keep using them! EPM Automate is a great tool to pick up files and get it over to PBCS, and FDMEE on the cloud is a very capable tool in its own right to further transform data, if necessary.

Synchronization, timing and frequency, o-demand requirements, etc., are all going to come into play and you’ll need to consider what your community’s tolerance-level may be for having more or less of those, or whether it’s really worth having them at all. When considering going to the cloud it may also be a good time for a thorough spring cleaning of your data integration processes.

Ultimately, it’s a matter of where the majority of your data transformation and integration will be done: on-premise or in the cloud. If you have demanding requirements, on-premise is probably still the way to go. I have no inside information to share but if I had to guess, Oracle’s PBCS product roadmap likely includes robust cloud data integration options as a pretty high priority in the future.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

The Oracle NodeDB Connector – A Useful Tool for BI and EPM Pros

October 19, 2016

Author: Anthony Tauro, Performance Architects

Since 2009 when it was first written, Node.js has grown to become a colossus in the open-source software community. Initially Linux-only, today it is a cross-platform JavaScript runtime environment for building a variety of tools and applications.

While it does not have much of a direct impact for those of us working on Oracle’s Business Intelligence and Enterprise Performance Management toolsets, its versatility means it is a tool worth having or at least knowing about for BI and EPM pros.

Node.js is based on the Google Chrome V8 JavaScript engine. This is the engine that helps the Chrome browser enable smooth application performance for Gmail and Google Docs. Though many people mistakenly assume JavaScript is a “mere scripting” language and thus inferior to a “full” language like Java or C#, the reality is that without the power of JavaScript, modern web applications which do much of their processing in the browser would have been impossible. Unlike the “full” languages which have a significant “build” phase, JavaScript applications (essentially web pages) need to be built during that tiny time interval between the web page loading in the browser, and it being shown to the user. And everybody wants that time to be as short as possible. The V8 engine (for a while at least) did this better than anyone else. So in 2009, Ryan Dahl took this engine and built it into a platform for building server applications.

But what truly makes node.js great happened the next year when Isaac Z. Schlueter built the Node Package Manager – or “npm” for short. With npm, people could write node modules and publish them online. Other developers could then (with a short, single command) import these modules into their code. This has spawned an ecosystem of thousands of free and open-source plugins, many of which are enterprise-grade and some of which are used in the biggest web applications in the world today. For example, Express.js is a very popular web framework, and Sails.js is an MVC framework for building enterprise-grade applications.

Oracle provides the Node-oracledb driver, which lets Node applications access the Oracle database (not the only way to connect Node to Oracle). This means you can create a node.js application that connects to an Oracle database, and then you can use a wide variety of other plug-ins to do stuff like expose that database using a web framework, or connect to another non-Oracle data repository like SharePoint or a REST API to supplement/complement the database.

Though the possibilities are literally limitless, in our BI and EPM projects we use this connector in only small ways. Here are some examples:

  • For a reporting application, we needed to compare the results of our reporting queries with some Excel reports (which we were working to replicate, albeit from a different source). The comparisons needed to be automated as the queries were being constantly tweaked, and source data also regularly reloaded. The workbook had several worksheets, the data was formatted into a pivot-table structure and column structure was a little flexible. We wrote a node application that read all the files in a nested folder structure, identified the right data, and imported it into a table in a flattened format. Then PL/SQL queries took over from there.
  • For a geomapping dashboard we needed to get latitude-longitude information for a list of customer addresses. We wrote a quick node application to get the coordinates from the Google Geomapping API, which has a relatively generous (but not unlimited) free quota, and plugged that back into the Oracle table.
  • To build a simple dashboard to track incoming data files, we used a node application that read the ETL application logs from a table, along with file details from an FTP folder, and presented a consolidated picture in an easily navigable web page.
  • EPM (while very intuitive for us pros), presents some challenges for more casual users. Fortunately, Oracle’s team has been giving it decent REST API capabilities, especially for PBCS. This allows a node.js application to connect to PBCS and present a much simplified interface for its users as required. While the Oracle JET (JavaScript Extension Toolkit) provides a good toolset for UI development, customers can also use whatever toolset they prefer.

This post is an introduction to Node.js for BI and EPM consultants. In subsequent posts we will look at some features of the oracle-nodedb connector, installing Node and the connector, and creating a simple application to view and navigate a table.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Enhancing Oracle BI Cloud Service (BICS) Visualization Projects with Text Boxes and Images

October 12, 2016

Author: Doug Ross, Performance Architects

Oracle’s flagship data visualization tool, BI Cloud Service (BICS) Visual Analyzer, is a feature-rich product for performing interactive analytics and data storytelling. With its extensive library of visualizations, users can analyze and display data in a variety of formats with interactivity to assist further insights.

There are also occasions where an analysis might prove valuable to share with others and to annotate the analysis either with images or text boxes to enhance the presentation of the data. This post will describe how to add text boxes and images to the Visual Analyzer canvas.  Note: while this post references the Visual Analyzer component of BICS, the functionality is also available in Oracle’s Data Visualization Cloud Service (DVCS), Data Visualization Desktop, and Oracle Business Intelligence Enterprise Edition (OBIEE) 12c offerings.

We’ll start with a basic scatter chart showing two metrics: “Profit Ratio%” and “# of Customers by Product” to provide insight into which products are either highly profitable and selling well or not very profitable despite having a large customer base.

doug1

There might be some outliers in the analysis that are worth highlighting

One way to call out or annotate an analysis is by using the “Text Box” visualization. It can be accessed from the Visualization selection box on the left panel and then scrolling to the bottom of the list.

doug2

Adding a text box to the canvas opens up a window that allows for entering the text, formatting that text, and optionally adding a URL link that can be opened when the viewer clicks on it. The options are shown below with the link option signified by the “chain” icon on the right.

doug3

The best way to take advantage of highlighting a particular section of the analysis is to change the canvas properties to allow for freeform movement of the visualizations. This allows you to drag a text box directly onto another panel and place it exactly where it belongs. Click on the “Canvas Properties” gear icon in the upper left and change “Layout” from “Auto Fit” to “Freeform.”

doug4

Adding an image to the canvas is just as simple. Scroll to the bottom of the visualization list and select “Image.” Drag it onto the canvas and a dialog box will be displayed to enter the image details.

doug5

Click on the “Select Image” button and a dialog box will be displayer to upload an image or enter a URL.

doug6 doug7

There are options for the image to change the transparency level. This can be useful if you’d like to display the image as a background on the canvas. Use a higher transparency value to lighten the picture and send it to the back layer of the canvas.

doug8

There are also options to explicitly set the size and aspect ratio of the image.

doug9

You can also include a URL link on the image that will open a new browser tab if the image is clicked.

doug10

Our finished product includes two text boxes and an image overlaid on top of the analysis visualization.

doug11

In summary, Oracle BICS Visual Analyzer offers some useful tools to help annotate and decorate your analyses.


© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.

Tips and Tricks when Moving from Oracle Hyperion Interactive Reporting (BRIO) to Oracle BI

October 4, 2016

Author: Elinor Poole, Performance Architects

It’s hard to think about moving away from a reporting solution that has become part and parcel of your reporting and analysis world to a new reporting tool, but when you take a step back and look at the advantages that come with moving to a solution like Oracle BI (either the on-premise Oracle Business Intelligence Enterprise Edition version or the Oracle BI Cloud Service software-as-a-service version), the benefits quickly surpass the challenges.

Rather than the “siloed modeling” architecture of Hyperion Interactive Reporting (IR), Oracle BI allows for an enterprise approach to reporting and analysis while still allowing end users the agility they have come to rely on with Hyperion IR.  In an enterprise approach, you build the intelligence in the models (a single version of the truth), which allows for all of the reports and dashboards to be shared and reused across the organization.  This resolves many of the siloed, BQY-centric limitations IR faces.  No more redoing work!

The story doesn’t stop there, because Data Visualization (DV) end users can leverage the shared data model in Oracle BI, and add additional data to meet their own reporting needs without going through IT.  Data Visualization is an add-on module to Oracle BI (either in the cloud or on-premise) that provides visual analysis and self-service discovery.  An enterprise-wide solution and agility…what a great idea.

Moving from IR to Oracle BI and DV, users can now create highly visual and interactive reports and dashboards that can be shared with other members of the organization and secured based on an individual’s role.  Data models are created centrally to allow for consistent data definitions, calculations, and metrics.  You model once and deploy everywhere.

Here’s a look at the two environments side by side; as you can see, Oracle BI offers a much better user interface with a much wider range of data visualization and data mashup capabilities:

elinor-1 elinor-2

elinor-3 elinor-4

The repository layer (or RPD) in Oracle BI allows you to connect to multiple data sources in your organization; to define physical and logical models; define hierarchies; create calculations; define roles/group; set up folder structures; and share this with the entire organization, as opposed to creating single models to address a single report:

elinor-5 elinor-6

I’ve also put together a few tips/tricks you might find helpful as you think about your own migration plan to Oracle BI from Hyperion Intelligence:

  • Don’t recreate all BQYs. Leverage the built-in capabilities of Oracle BI to reduce the number of reports needed.  Features like “prompt,” “filters,” and “column selectors” make reports very interactive, reducing the need to build similar reports that are just limited differently.
  • Think beyond the “table/pivot table” view. Oracle BI and DV offers dozens of data visualizations that can be used to create reports and dashboards for all levels of an organization.
  • Move manually. Although some tools are offered to help with the migration, it often just as quick to assess and rebuild rather than move with a tool and try to remediate.
  • Don’t build “super queries.” There is no need to create queries that contain all levels data you think you will ever need.  Use the hierarchy capabilities of the tool!
  • Don’t build something just because you can. Just because you can create some ETL-like functions in the repository (RPD) of Oracle Business Intelligence Enterprise Edition (OBIEE) doesn’t mean you should!  That work needs to be done using an actual ETL tool.
  • Save yourself some time. Don’t convert reports that haven’t been used in a long time.  Also, look for places where reports can be consolidated. Reports are often almost exactly the same with the exception of a filter or a column.
  • Create shared calculations. Create calculations in your central model so that they can be used across all reports.
  • Don’t use custom code. Don’t build out a bunch of custom code. Take advantage of all the built in capabilities like prompts/filters, conditional formatting, zoom, slide, 3d, animation, drill, master-detail linking, etc.
  • Use data visualization to do data validation. Since data visualization allows end users an easy way to import data themselves without going through IT, they can now pull in data and determine if it has value before putting it through a formal ETL process or modeling it.  This saves a lot of unnecessary work of modeling data that no one will ever use.

© Performance Architects, Inc. and Performance Architects Blog, 2006 - present. Unauthorized use and/or duplication of this material without express and written permission from this blog's author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Performance Architects, Inc. and Performance Architects Blog with appropriate and specific direction to the original content.