Always have a plan - An Oracle BI 11g Backup Plan

What is just as important as architecting the perfect Oracle Business Intelligence integration solution?

Answer: Architecting the perfect backup and recovery strategy process in order not to lose it!

Often so much focus is placed on the “Let’s get there” aspect of a Business Intelligence implementation.  But all too often during the crucial requirements gathering sessions, scope building, and project plan generation there is little talk about backing up the infrastructure that the project seeks to put in place.  Forget about backing up source data for a moment. That mainly will/should be handled the DBA side of the Information Technology department.  Let’s talk Oracle BI metadata and environment configuration for a moment.

As we can see from Oracle BI 11g, there are simply many more moving parts to Oracle’s latest BI tool release than what was contained by its predecessors. The Fusion Middleware layer is quite robust and even experienced Oracle BI aficionados can attest to its complicated directory structures, and their lapse in memory regarding the location of key configuration and log files. So, when it comes to backing up the Oracle BI environment there needs to be a fully vetted plan in place to ensure that your Oracle BI environment’s data, metadata, and configurations are recoverable in case an unpredictable incident occurs.  In Oracle BI 11g we now must be “point-in-time” aware as the MDS and BIPLATFORM database repositories are involved.

Without going into great detail by providing a full Oracle BI backup and recovery plan in this article, let’s just highlight some basic Oracle BI backup and restoration strategy topics.

Backup Level

Full Server Backup

This is often seen as the “killing an ant with a sledgehammer” approach but is effective in that all components of the Oracle BI Server (and everything else on the machine) are saved for storage.  Clearly, there are a multitude of cons in this approach such as the ever increasing need for storage space, the cost of storage, and the larger length of time required to complete a full server backup (which may results in large download terms).  Virtual Machines (VM) make this easier but VMs have their own complications, which we don’t discuss here. 

Precision Backup

Oracle’s recommended approach is to take more of a surgical backup approach, taking only the core elements of the environment into consideration for the backup plan.  This is no longer the handful of core files that is used to be in previous version of Oracle BI.  In fact we now must consider several different areas for the back up:

ORACLE_INSTANCE/bifoundation/OracleBIApplication

ORACLE_INSTANCE/bifoundation/OracleBIClusterControllerComponent

ORACLE_INSTANCE/bifoundation/OracleBIJavaHostComponent

ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent

ORACLE_INSTANCE/bifoundation/OracleBISchedulerComponent

ORACLE_INSTANCE/bifoundation/OracleBIServerComponent

ORACLE_INSTANCE/bifoundation/OracleBIODBCComponent

ORACLE_INSTANCE/config/OracleBIApplication

ORACLE_INSTANCE/config/OracleBIClusterControllerComponent

ORACLE_INSTANCE/config/OracleBIJavaHostComponent

ORACLE_INSTANCE/config/OracleBIPresentationServicesComponent

ORACLE_INSTANCE/config/OracleBISchedulerComponent

ORACLE_INSTANCE/config/OracleBIServerComponent

ORACLE_INSTANCE/config/OracleBIODBCComponent

ORACLE_INSTANCE/diagnostics/logs/OracleBIApplication

ORACLE_INSTANCE/diagnostics/logs/OracleBIClusterControllerComponent

ORACLE_INSTANCE/diagnostics/logs/OracleBIJavaHostComponent

ORACLE_INSTANCE/diagnostics/logs/OracleBIPresentationServicesComponent

ORACLE_INSTANCE/diagnostics/logs/OracleBISchedulerComponent

ORACLE_INSTANCE/diagnostics/logs/OracleBIServerComponent

ORACLE_INSTANCE/diagnostics/logs/OracleBIODBCComponent

ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/comp_instance name/repository/NQSConfig.INI

ORACLE_INSTANCE/bifoundation/OracleBIServerComponent/comp_instance name/repository/*.rpd

ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/comp_instance name/catalog/<catalog-name>

Ultimately this precision backup strategy recommends the backup of the Middleware home, the domain home, and the Oracle instance containing the Oracle BI EE components. In addition, if running a Windows Operating System one must export Oracle BI EE Registry entries.  Typically an additional batch script is created to yank out this information on some frequency to prep it for backup and likewise an additional batch script for automating the environment variables recovery.

Precision backups have much fewer cons than the full server backup strategy.  Though more effort is involved upfront the maintenance of this type of backup plan is substantially less of a headache.

Frequency

Hyperion aficionados, particularly those involved in Essbase infrastructure, know that a backup plan is critical to recovering elements of a Hyperion Essbase system.  Depending on data loads and data vulnerability some Hyperion Essbase implementations can expect backups as frequently as daily and others comfortably monthly.  As it relates to Oracle BI the same considerations of data and metadata vulnerability must take place.  Different components such as configuration files or the web catalog for OBIEE can be surgically backed up on a more frequent basis than the components that might not change as frequently such as the configuration files (instanceconfig.xml, etc.)

Considerations

Multiple Software Lines

Each platform delivered with the Oracle BI 11g Suite has its own backup consideration.  Though OBIEE, OBIP, and ORTD comprise the Oracle Business Intelligence Enterprise Edition software they have some common metadata elements and some which are sole references.  Common elements would include the MDS and BIPLATFORM relational database repositories. Sole software reference elements would be such items as the OBIEE RPD or the RTD schema. Determining if some or all of these items fall under the critical backup and restoration plan is a line item to check off during initial BR planning as ultimately your restoration plan is based on what exactly you’ve chosen to backup.

Maintenance Mode

As the process of creating a backup of an infrastructure usually seeks to create a “point-in-time” instance from which to recover, adding a maintenance mode to the BR plan/strategy is a great idea.  Alerting users of the Oracle BI System that a maintenance mode is planned and before backup occurs is not only a good idea it is common courtesy.  More to the point, Oracle recommends locking the Oracle BI Presentation (Web) Catalog before conducting a backup so that the RPD and Presentation (Web) Catalog stay in sync.  That can be conducted with the following syntax:

./runcat.sh -cmd maintenanceMode -on -online BIP_URL

     -login username -pwd password

Including this into any automation process would be a smart way to approach Maintenance Mode considerations.

Conclusion

Ultimately a formal backup and recovery plan should be created for an implementing organization.  This should be done from an Oracle BI (Fusion Middleware) system perspective and not just at the developer (Just save the RPD and Web Catalog) perspective.  By creating an Oracle BI backup and recovery strategy or enlisting a Systems Integrator to assist with creating one, the Oracle BI system will rank with a higher level of confidence, match corporate BR standards, and provide a structured path to getting an infrastructure back online in the worst-case scenario of loss.

Resources

http://download.oracle.com/docs/cd/E21764_01/core.1111/e10105/br_intro.htm#ASADM11238

New Oracle BI 11g Administration Tool Icons - A Comparison

As a slight departure from the serious rhetoric of Oracle BI 11g enhancements, I thought one fun thing to do would be to compare the new icons that are now in the Oracle BI 11g Administration Tool with the Oracle BI 10g version.  As you can see below in the side-by-side comparison there are ultimately only two net new icons that have been added in Oracle BI 11g.  The other icons have merely been redesigned and some even renamed for Oracle BI 11g.  Finally, these icons are clearly more stylish - as to be expected from a shiny new tool. 

I Only Want to Show Lowest Level of Hierarchy on Chart

I saw a question on Oracle’s BI Forum where the person wanted to only see the lowest level of the hierarchy as they drilled down on their chart.  They were using OBIEE 11g; so, I wanted to look at this a little closer to see what could be done.

Just so we understand the issue involved here, a few screen shots will make the issue clearer. 

So, I start with a simple report like the following:

I create a simple bar graph from this criteria.

and then drill down on the FunPod brand to the next level of the Product hierarchy.

You can see from the highlighted area that when you drill down using the standard hierarchy drill feature, you get the Brand as well as the next level which is LOB.  I also added a filter view to show that now we have redundant information in the compound view; the filer shows the FunPod filtering from our drilling.  This is the issue the person in the forum wanted to overcome.  He/She just wants to see the LOB at this point on the graph.

So, is it possible to achieve the results this person wants?

I made a simple change which I will come to, but let’s first look at the results.

Again we are starting at the brand level and notice there are no filters being applied.

We drill down on FunPod again to get to the next level.

We can now see that I was able to get the desired result on the graph and comparing the LOB members to the graph previously showing the LOB level, the data results are the same.  However, notice there are no filters being shown in this scenario.  Perhaps that is not important to the user.  What is important is that are axis labels are not cluttered.  Think about going down 5 levels in a hierarchy with the standard drill down feature which would try to concatenate each level to the point axis labels are unreadable.

So, how was this bit of magic possible?  Let’s look at the criteria to see.

If you compare the previous criteria screen to this one, you will see that I switched out the Brand column for the Product Hierarchy column.  Using the new hierarchy column type that is part of 11g gives the display results the person is trying to achieve.

This is a simple solution that does not require custom code changes to javascript files, which I have not looked at to see if there is a JS file that could be modified to provide these same results.

BI Publisher Desktop Differences (10g to 11g)

When using BI Publisher Desktop 10g and adding a tag to your report template, the code was accessible by right-clicking the tag and selecting the “properties” option. The code was placed directly in the textbox area of the “Status Bar” tab and if you had additional code then you used the textbox area under the “Help Key” tab as well. Similar to what is should below.

With BI Publisher Desktop 11g, a BI Publisher Properties” option was added to the right-click menu which provides 2 tabs; Properties and Advanced. The Advanced tab contains the code associated to the tag in your report template. This expanded property window alleviates the need to break your code into pieces which needs to be spread across tabs as in 10g. The Word Properties window is accessible by using the lower left button appropriately labeled “Word Properties”.

As you can see, the Word Properties textbox now holds a reference tag to your code in the BI Publisher Properties window.

This presents a couple of potential issues you should be aware of.

1. You cannot see the actual code behind the report template tags unless you first load an xml data sample. Without the data sample loaded, if you open the properties you will go directly to the Word Properties and only see the ref tag information.

2. I have had situations where if a copied a tag to reuse it, it would retain the same reference tag number and the code change would be lost. I have now made it a point to add a new tag when I want different code for a tag to insure I have a unique reference tag number rather than copying a tag as I did in 10g.

3. The reference tag code will not run under a BI Publisher 10g server installation. The 10g instance is not able resolve the reference code to the actual code.

All of these issues are actually minor as long as you are aware of them and compensate for them. The last issue is the most troublesome when you are working a project where the client is still on 10g, but you have loaded the latest version on your computer.

However, all is not lost.

Knowing the differences between 10g and 11g gives you the power to code using the latest version for either environment. You simply need to copy your 11g code from the BI Publisher Properties Advanced tab and place it into the expected textbox areas for 10g to find it overwriting the reference tag; doing this will allow your report template to work in both 11g and 10g environments.

One last gotcha to cover if you need to go this route, do NOT update your code through the BI Publisher Properties window. You need to update your code (should changes be necessary) through the Word Properties window. Your code changes will be reflected back towards the BI Publisher Window. Unfortunately, it is a one way street. This also holds true for any other property changes you may wish to make like formatting or datatype changes.

Integrating with OBIEE has become Easier

Some of you have been in front of clients where they wanted to create their own front-end for OBI or wanted more flexibility in the creation of reports. Also, there are more and more clients asking how to leverage OBIEE reports and dashboards in external portals.  With the release of OBIEE 11.1.1.5, we have seen many enhancements not only to the tool sets but also the architecture. What many may not be aware of is that when OBIEE 11.1.1.5 was released other 11.1.1.5 tool sets were also released which now integrate more effectively with OBIEE; such as, WebCenter, WebPortal, and JDeveloper.  There are some new library wizards (extensions) that were released with JDeveloper 11.1.1.5 that makes leveraging OBIEE simpler than ever before.

Business Intelligence Composer: Provides the ability to create or edit BI report via a BI Report Wizard task flow in an ADF page.

Business Intelligence ADF View Regions: Provides the ability to browse the BI Catalog through the JDeveloper resource catalog and add BI objects to an ADF page.

Business Intelligence ADF Task Flow: Provides the ability to browse the BI Catalog through the WebCenter resource catalog and add BI objects to an WebCenter page.

Business Intelligence Soap Connection: Provides the ability to create a BI Soap Connection.

Business Intelligence Logical SQL View Objects: Provides the ability to create a View Object that can query Oracle BI using logical SQL.

OBIEE 11.1.1.5 New Features Worth Highlighting

The latest release of Oracle BI 11g (aka 11g PS1) is now available.

We’ve been quickly involved with this release and we are currently implementing and upgrading serveral of our clients to this release with much success.  Aside from the many bugs that this release has squashed from OBIEE 11.1.1.3, this micro release brings several new features to software suite that are very welcomed. Simply listing every new feature here would make for a very long list.  Instead, I will quickly highlight a few new features that I believe are paramount to the continued road map for Oracle’s domination in the Business Intelligence realm.  These are listed in no particular order of importance.

• Mobile Business Intelligence (iOS ipad/iphone Application)
• Included support for AIX and Solaris SPARC UNIX OSs
• Additional integration with Real-Time Decisions and Essbase
• Metadata Repository one-click SSO support (via CSS) for Essbase
• Oracle BI Apps 7.9.6.3 fully certified OBI 11g
• Informatica 9.x OEM for Oracle BI Apps
• Further ADF (JDeveloper) Integrations for Third-Party Development
• Metadata Administration SOAP Web Service
• Full-Text Catalog Search fully vetted. 

Other runner-up new features worth mentioning are:

• Beefed up support for XMLA, SAP/BW (Native Connection)
• BI Composer (Although this requires additional installation/configuration)
• New Diagram look n’ feel in the RPD (physical and BBM)

Each of these new features could clearly constitute an individual blog post to highlight the benefits they bring to Oracle’s BI offering.  And, this may be the directive I use going forward for the next few blog entries. 

Hopefully this outlines a few talking points for what some of the new key features of OBIEE 11.1.1.5 are.

OBIEE 11g deployments on 64 bit environments must use "Software-Only" install method

After a couple of failed troubleshooting attempts on an issue with the new graphics engine not rendering charts on some of my client’s dashboards, Oracle Support has made me aware that customers deploying OBIEE 11g on 64 bit environments should use the “Software-only” install method instead of going through with the Enterprise install method.

As you can see in the picture below I myself used the Enterprise install on my Oracle Virtual Box / SUSE Linux virtual machine.

OBIEE 11g deployments on 64 bit environments must use “Software-Only” install method

I will work on a new virtual machine install over the weekend using the software-only method. Look for an update soon, in the mean time check out the release notes with the information about 64 bit support.

http://download.oracle.com/docs/cd/E14571_01/doc.1111/e14770/biee.htm#CHDIHIE

Oracle EPM 11.1.2.1 is now available!

Oracle 11.1.2.1 has finally hit GA. You can now download 11.1.2.1 from https://edelivery.oracle.com/. Let the upgrading begin!

DVT Graphs in OBI EE

Just like in OBI 10g; especially BI Publisher, not all charts available in the code base were made available via the front-end wizards. So, I was curious if there are other charts available in OBI 11g as well. If so, how can I leverage them if at all.

The graphic engine for the BI 11g components including BI Publisher is DVT (Data Visualization Tools).

DVT has available the following graph types:

Area	Bar
Bubble	Combo
Funnel	Horizontal bar
Line	Pareto
Pie	Radar
Scatter	Stock

Of these, only Stock graphs appear to be not available through the OBI EE 11g front-end for analyses.

Examples of additional vertical bar graphs not included in the OBIEE 11g wizard include many of Dual-Y and Split Dual-Y. However, they are included in the BI Publisher wizard as seen from the screen shot here.

The Floating Stacked Bar graph is available in ADF DVT, but is not available in either wizard.  I will state it is possible to create a floating bar graph, but it requires some tricks to be applied in OBI EE.

Let’s continue deeper and look at the sub-types available in DVT for each graph type and identify which can be created in OBI EE 11g.

DVT Graph Type	DVT Sub-Type	Description	In OBI
Area	Vertical ABS	An absolute area graph	Y
	Vertical ABS Split 2Y	An absolute area graph with split dual-Y axis	N
	Vertical Percent	A percent area graph	Y
	Vertical Stack	A stacked area graph	Y
	Vertical Stack Split 2 Y	A stacked area graph with split dual-Y axis	N
Bar Horizontal Bar	Vertical Cluster	Clustered bar graph that has a vertical orientation	Y
Vertical Cluster Split 2Y	Clustered, split dual-Y bar graph that is vertical	N
	Vertical Cluster 2Y	Clustered, dual-Y bar graph that is vertical	N
	Vertical Float Stack	Floating stacked bar graph that is vertical	N
	Vertical Percent	Percent bar graph that is vertical	N
	Vertical Stack	Stacked bar graph that is vertical	Y
	Vertical Stack Split 2Y	Stacked, split dual-Y bar graph that is vertical	N
	Vertical Stack 2Y	Stacked, dual-Y bar graph that is vertical	N
	Horizontal Cluster	Clustered bar graph that has a Horizontal orientation	Y
	Horizontal Cluster Split 2Y	Clustered, split dual-Y bar graph that is Horizontal	N
	Horizontal Cluster 2Y	Clustered, dual-Y bar graph that is Horizontal	N
	Horizontal Percent	Percent bar graph that is Horizontal	N
	Horizontal Stack	Stacked bar graph that is Horizontal	Y
	Horizontal Stack Split 2Y	Stacked, split dual-Y bar graph that is Horizontal	N
	Horizontal Stack 2Y	Stacked, dual-Y bar graph that is Horizontal	N
Bubble	Bubble	Bubble graph	Y
	Bubble 2Y	Dual-Y bubble graph	N
Combo	Combination Vertical ABS	Absolute vertical combination graph	Y
	Combination Vertical ABS 2Y	Absolute 2Y vertical combination graph	Y
Funnel	Funnel	Funnel Chart	Y
Line	Vertical ABS	Absolute line graph	Y
	Vertical ABS 2Y	Absolute, dual-Y line graph	N
	Vertical ABS Split 2Y	Absolute, split dual-Y line graph	N
	Vertical Percent	Percent line graph	N
	Vertical Stack	Stacked line graph	N
	Vertical Stack 2Y	Stacked, dual-Y line graph	N
	Vertical Stack Split 2Y	Stacked, split dual-Y line graph	N
Pareto	Pareto	Pareto graph	Y
Pie	Pie	Pie graph	Y
	Pie Bar	Pie-Bar graph	N
	Pie Multi	Multiple pie graph	Y
	Ring	Ring graph	N
	Ring Bar	Ring-Bar graph	N
	Ring Multi	Multiple Ring graph	N
Radar	Radar Line	Radar graph	Y
Scatter	Polar	Polar graph	N
	Scatter	Scatter graph	Y
	Scatter 2Y	Dual-Y scatter graph	N
Stock	Stock Candle	Candle open-close stock graph	N
	Stock Candle Volume	Candle open-close stock with volume	N
	Stock HILO Close	High-low-close stock graph	N
	Stock HILO Close Volume	High-low-close stock graph with volume	N
	Stock OHLC Candle	Candle open-high-low-close stock graph	N
	Stock OHLC Candle Volume	Candle open-high-low-close stock graph with volume	N
	Stack Open HILO Close	Open-high-low-close stock graph	N
	Stock Volume	Open-high-low-close stock graph with volume	N

You may not find all the sub-types marked with ‘Y’ in the list of graph types through OBI EE as they are listed here. In some cases, you need to modify the properties of the base graph to achieve the desired sub-type.

It makes sense that some of the unavailable sub-types would not exist in an analytical tool like OBI EE, but does that mean I cannot leverage them if I have the data and need? I will look at ways it may be possible to leverage some of these “unavailable” sub-types within OBI EE in future entries.

DVT does include other graphical components besides Graphs which are also leveraged within OBI EE 11g. Some of which are included as other types of analysis views; Gauges, Pivot Tables, Maps. Gantt Charts are also available in DVT, but it kind of makes sense they would not be available in OBI EE.

I am going to close this entry with the following tidbits of information between some differences from 10g to 11g charting.

How Has the Behavior of Charts Created in Previous Releases Changed?

Note the following changes in the behavior of charts (known as graphs in this release) that were created in previous releases:

· In this release for a pareto graph, the vertical axis 2 ranges from 0% to 100%. Therefore, you cannot change the abbreviation that is used for scale and data labels, (for example, you cannot change it to Million(m)). You also cannot override the default numeric format in which data labels are currently displayed.

· In this release for a scatter graph, unlike in previous releases, the scatter graph does not require at least one attribute column on the Group By axis.

· In this release for a stacked vertical bar graph and a stacked horizontal bar graph, the order of stacking is the reverse of the order in previous releases.

· In this release for a gauge, the ranges must be continuous (for example, range1: 1-200, range2: 200-400, and range3: 400-500). In previous releases, ranges that were not continuous were allowed (for example, range1: 0-200, range2: 400-500, and range3: 200-400).

· In this release, the logarithmic scale behaves in the following ways:

o If you specify the scale limits, then the scale limits for the logarithmic scale must be powers of 10 only. If the number that you specify is not an exact power of 10, then the power of 10 closest to the number that you specify is used.

o If you let the system determine the scale, then the lower limit changes dynamically based on the measure used.

In previous releases, the logarithmic scale behaved in the following ways:

o If you specified the scale limits, then the scale limits for the logarithmic scale were numbers fully divisible by 10. If the number that you specified was not divisible by 10, then the number closest to the number that you specified that was fully divisible by 10 was used.

o If you let the system determine the scale, then the lower limit always started at 1 and the upper limit changed based on the measures used.

Upgrading BI Publisher to 11g

Now that more and more customers are starting to use the newest BIEE 11g release a commom question asked is : How do I upgrade my BI Publisher 10g to 11g?.

Sometime ago we made a presentation on BI Publisher 11g and touched this matter. Here we revisit this interesting topic again and present you a summary of the upgrade process.

The upgrade of BI Publisher 10g to 11g is essentially a three step process:

1. Install BIEE 11g

2. Run Upgrade Assistant (to upgrade BI Publisher 10g Repository)

3. Run Upgrade Assistant (to upgrade BI Publisher 10g Scheduler metadata)

In Windows to start the Upgrade Assistant using the GUI change directory to the ORACLE_HOME/Oracle_BI1/bin directory of the Oracle Fusion Middleware installation: ua.bat

Assumptions: Certain considerations should be taken during the process as for example.

1. BI Publisher 10g Repository is file based. If it is XMLDB based then move the directory to a file system before migration. As of release 11.1.0 there is no support for XMLDB repository type (Future will use MDS as repository).
2. The upgrade assistant assumes the BI Publisher 10g repository is accessible in a shared drive.
3. Before upgrade, make sure BI Publisher 11g is set to repository File Type (by default it is BIEE Catalog) because the UA only upgrades to file type repository.
4. After upgrade, BIEE 11g and BI Publisher 11g integration works if security is “Fusion Middleware” and BI Publisher Repository is BIEE Catalog. So use the utility in BI Publisher 11g Administration —> Server Configuration —> Select Catalog Type: BIEE —> set the BIP Repository path —>Upload to BI PS Catalog

References:

1. Oracle BI Publisher 11g Documentation