Lift & Shift OBI 11g to Oracle Analytics Cloud: Lessons LearnedBIASCorp
Many enterprise customers have been using OBIEE for over 10 years since 11g was first released and have a large highly trained user base that loves the capability the tool provides, and who have created a large catalog of reports, dashboards, and alerts they use today. This user population, that has often smartly exploited OBIEE’s advanced capabilities, is now very keen on leveraging the modernized user experience offered by the Oracle Analytics Cloud (OAC) without losing the hard work that went into building their large and complex catalog.
In upgrading such highly customized Oracle Business Intelligence (OBI) 11g environments to Oracle Analytics Cloud (OAC), BIAS developed several innovative automations and solutions to streamline this multi-step process. In this blog series, we provide a brief overview of what went well out of the gate and what we had to address, with specific attention to security in the cloud and automation.
Users familiar with OBIEE releases understand that OBIEE went through several major architectural and functionality updates between version 126.96.36.199 and 12.2.x. OBI 188.8.131.52 had new features such has new graphs (Views, Performance Tiles, Waterfall), sizing dashboard layouts, improved Excel import and mobile enhancements. OBI 184.108.40.206 had features such as storage of calculations in Web Catalog, new NQSConfig setting, improved Usage Tracking features, whole RPD checkout option. In the subsequent versions such as OBI 12c even the architecture has changed such as Oracle Process Manager and Notification Server (OPMN) is no longer used, system components are managed by Oracle WebLogic, and metadata is stored in the form of BAR files facilitating easier migration to higher releases such as OAC. Visual Analyzer is also introduced in this version. So, a migration from 11g to OAC is a giant leap that involves many architectural and functionality improvements.
What Went Well
Oracle provides a migration utility to support migrate processes from OBI 220.127.116.11 or later to Oracle Analytics Cloud. If you are on a lower version of OBI, then going to OAC is a two-step upgrade. In our migration, the migration utility did a great job of pulling the catalog and repository. In addition, the security settings that are normally managed in Enterprise Manager (EM) were brought seamlessly into OAC. After the initial migration and before we configured the OBI application roles within Identity Cloud Service (IDCS), we could see in the catalog permissions that there were undefined security groups. When we added the OBI security groups in IDCS and linked them in the OAC Console to those used in the catalog, it immediately picked them up and the permissions worked.
With the Oracle–managed platform, custom image content is handled differently. The recommended approach is to place all the custom images on a web server then add the host name to the safe domain list in the OAC Console.
We observed some interesting things while configuring the Data Gateway (DG). This tool is used to connect to on-premise data sources. The DG is a replacement for the Remote Data Connector (RDC) which originally served this purpose. Two key improvements of the Data Gateway over the RDC are that the DG greatly simplifies the installation and it uses an on-premise pull architecture for increased security. Even with the simpler approach, establishing connections from Data Gateway to the OAC requires careful planning to navigate on-premise network security such as firewall rules and proxy servers.
The diagram below shows both the high-level process flow for the Data Gateway and the need for firewall ports to be opened between the DG (on port 443) to the Oracle Cloud. The list of IP ranges to open for the Oracle Cloud depends on the data center and must be generated for your specific implementation with close consultation with either Oracle or your implementation partner.
While connections from the Data Gateway to on-premise Oracle database went smoothly, connections to other on-premise databases needed some intervention. It is important to ensure the DG is using the correct third-party driver version for your non-Oracle database. A wrinkle specific to Teradata connectivity was the Data Gateway’s lack of support for Teradata LDAP authentication. When configuring the Teradata connection, there is no option to check to ask the driver to use LDAP. Fortunately, we found a workaround by changing the default within the Teradata driver from Native to LDAP. In this way, the Data Gateway can support either Native or LDAP authentication for Teradata. Based on this finding, Oracle also published a note (Doc ID 2601452.1) that discusses a few Teradata connection issues.
We also observed that Dynamic repository variables are not getting refreshed consistently post– upgrade. We found two separate issues were responsible for this behavior. One is documented in existing Oracle Doc ID 2592544.1 and the second relates to setting the refresh interval for updating the Initialization Blocks in minutes rather than hours or days.
Usage Tracking and Map Viewer for Hybrid Architecture
Oracle Data Gateway currently only supports select statements. This is per the OAC Data Gateway FAQ. Since Usage Tracking requires writing to tables it currently cannot be done through the Data Gateway. Even in the 105.4 version, only SELECT statements are possible What this means is that Usage Tracking data must be maintained in a cloud database such as Database as a Service (DBaaS) or on the Oracle Autonomous Data Warehouse (ADW). Similarly, we found that MapViewer functionality did not work through the Data Gateway and, therefore, required a cloud database.
Changes in Generated SQL Languages
During performance testing between OBI and OAC, there were changes in the physical SQL generated between the two. In several cases, this resulted in performance degradation in OAC when compared to OBI. Whenever physical SQL is generated differently for the same logical SQL, the culprit is usually Database Features within the RPD. We confirmed the Database Features settings were identical, specifically that the upgrade process didn’t affect them. It turned out, however, that some of these features had different behavior within OAC so adjustments were required to provide equivalent behavior. After modifying these parameters, the run time of the reports within OAC was in line with the performance within OBI.
Oracle Analytics Cloud uses Oracle Identity Cloud Services (IDCS) Foundation to maintain users and groups, similar to how OBI 11g and above use Security Realms within WebLogic Server. IDCS allows many options for sourcing identity data. If migrating from native OBI security, there is a straightforward approach to migrate those settings to IDCS with CSV files. Replication from a centralized security system, for example, Oracle Identity Manager (OIM), is also available. There is an IDCS Connector for OIM and a flexible SDK based on open standards such as OAuth 2.0 and OpenID Connect 1.0. Upgrading to IDCS Standard provides a bridge for Microsoft Active Directory (AD). A key difference between these options is how they enable using the same password within IDCS as is used elsewhere. Replication requires storing the centralized password within IDCS whereas the AD Bridge allows for using the AD password within IDCS without storing it.
Automating Oracle Business Intelligence functions such as clearing the cache, running agents and invoking actions for integration is quite common. In OBI on-premise this is often accomplished using the built-in nqcmd command which is no longer available in OAC. Fortunately, these types of automation can be accomplished in other ways. OBI and OAC both expose web services which that provide for methods that allows you to automate various functions like what you can do in nqcmd. Another option is based on OBI and OAC support for calling functions through the URL, for example calling a GoURL through curl. Both of these approaches can be called from the command line interface of an on-premise server and added to the native scheduler of that machine.
Authors – Ajay Mittapalli, Saikrishna Vurakaranam, Patrick Abram and Ashish Bokil