Tag Archives: Obiee
OBIEE 11g Presentation Server Catalog Replication Across Redundant Servers
The thing about OBIEE 11g, is that there is no single way to get things “up and running”. Over the past year I have performed countless OBIEE installs and almost every one has been different to the last, due to infrastructure challenges, scale requirements or requirements for high-availability and server redundancy.
There is little we can do about infrastructure challenges, typically around policies which are in place across the IT estate and must be adhered to. Scale is simple; more users means more or bigger servers. The last point on the list is HA & server redundancy, and this is arguably the biggest area we need to address before punching in “./runInstaller” at the command-prompt.
Mark Rittman is currently pulling a wider blog posts around options for HA & server redundancy, so I’m not about to get into that in this post. What I will share in my first Rittman Mead blog post is something I have been working on recently, that is sometimes implemented as part of a HA/redundancy solution; BI Presentation Server catalog replication.
BI Presentation Server catalog replication is the Oracle supported method of moving a BI Presentation Server catalog from one running OBIEE instance to another. This is useful is many situations namly:
- Release cycle management when specific aspects of the BI Presentation Server catalog need to be moved from one OBIEE environment to another, such as development to testing.
- Full BI Presentation Server catalog synchronization between OBIEE instances to provide two OBIEE instances with exactly the same BI Presentation Server catalog for DR.
The 2nd point above is the scenario I am looking to cover here – using this utility we can make sure the BI Presentation Server catalog on two distinct OBIEE instances are the same. In a DR event we can either point users at the secondary server or reverse this utility to pump the data from the DR server to the primary server after the fault on the server has been cleared. The RPD and other changes, such as user roles, would be managed as part of the release process and copied to the DR server when they are pushed to the production server. It is only the BI Presentation Server catalog which is constantly changing as the OBIEE instance is used, and therefore only the BI Presentation Server catalog which needs to be constantly replicated.
To set up BI Presentation Server catalog replication, we can use a command-line utility which ships with Oracle Business Intelligence 11g, called either “sawrepaj.bat” or “sawrepaj.sh” depending on your operating system (the two are functionally identical, and differ only in the way they are invoked from the command-line). “sawrepaj” has options to synchronise catalogs between servers, or to simply export a catalog for backups or as part of the release lifecycle; in addition, we can control the grain of what is exported down to an individual catalog folder or file.
The utility runs from the command-line and accepts a number of options, or commands, to specify how the replication takes place, using the following syntax:
sawrepaj.sh [/C path] command [command parameters]
- mark – instruct the OBIEE server to ‘watch’ catalog files and record changes.
- run – run the batch, as defined in the config.xml file.
Before using the utility, we need to create a config.xml file which instructs the OBIEE Presentation Services to record changes, and tells sawrepaj what to do when it executes.
The config.xml file can be placed anywhere in the filesystem and is referenced using the /C switch when calling sawrepaj – more about this /C switch later. The config file tells the sawrepaj utility which Oracle BI Presentation Servers are involved in the replication, and how to access them. The example below includes a source and target Oracle BI Presentation Server – however you could add countless other BI Presentation Servers depending on your environment.
<?xml version="1.0" encoding="UTF-8"?> <!-- Oracle BI Presentation Services Replication Agent Configuration File --> <Config> <General> <ExportDirectory>smb://path/to/directory</ExportDirectory> <LogExpiresHours>48</LogExpiresHours> </General> <Server name=”serverA" user="weblogic" pwd="Password01"> <Property name= "oracle.bi.presentation.url" value ="http://servera:9704/analytics/saw.dll"/> <Property name="oracle.bi.presentation.usesoaptcptransport" value="N"/> </Server> <Server name="serverB" user="weblogic" pwd="Password01"> <Property name= "oracle.bi.presentation.url" value ="http://serverb:9704/analytics/saw.dll"/> <Property name="oracle.bi.presentation.usesoaptcptransport" value="N"/> </Server> <Folderset name="all"> <Folder>/</Folder> </Folderset> <ReplicationTask destination="serverA" source="serverB" folders="all" /> </Config>
The three sections of interest in the above file are Server, FolderSet and ReplicationTask. Each
- Server tag points to an OBIEE Presentation Services instance with credentials for a user with has SOAP access.
- FolderSet tag contains a list of Folder tags which detail the folders within the webcat to replicate.
- ReplicationTask contains the instruction of what to do when we run sawrepaj. For the above example, serverA is the target and ServerB is the source.
Before any replication can begin, we need to enable each BI Presentation Server component for replication. Add the below to the instanceconfig.xml file between the <catalog> tags. This needs to be done for all all Presentation Server components, whether source or target for the replication.
<Replication> <Enabled>true</Enabled> <ReadLogRecordsSinceHoursAgo>120</ReadLogRecordsSinceHoursAgo> <RecordsInFileLimit>4000</RecordsInFileLimit> </Replication>
Now, back to the sawrepaj commands:
mark – The mark command is used to tell tBI Presentation services which webcat files to monitor for changes. We can be as specific as a single file, or as as vague as an entire webcat. You will need to run this on a BI server with the following command:
sawrepaj mark all /
run – The final part of the process, the run command will instruct sawrepaj to run all the tasks in the config.xml file. The below is an example of the command. If your config.xml file is not in the root path of the sawrepaj tool, you will need to specify it’s location with the /C switch. This command can be added to a cronjob, a Windows scheduler or can be ran manually, or as part of another script.
sawrepaj /C /my/config/file/config.xml run
That’s it! Once completed, the above steps will give you all you need to set up full catalog replication between multiple BI servers.
Date formatting in OBIEE 11g – setting the default Locale for users
A short and sweet blog post this one, simply to plug a gap in Google that I found when trying to do achieve this today.
How user’s see things like date formats in OBIEE is determined by their locale, which is usually related to language but not always the same.
The classic example, and the one I was trying to resolve, was that users were seeing dates presenting in the ‘wrong’ format. The users are British and instead of seeing dates in the correct format of DD/MM/YYYY (19/01/2014) they were MM/DD/YYYY (01/19/2014). (side note: this lighthearted article in the Guardian refers to this ridiculous format as “Middle-Endianness”…)
In this example I have got the same date value in two columns. One column I have explicitly formatted (“DATE_VERBOSE”) so there is no ambiguity about the date. The second column (“DATE_LOCALE”) is the same date value, but formatted according to the locale setting. It is this default locale setting that I want to demonstrate how to set – manually changing all date columns to a particular format is not a sustainable way to develop reports…
Oracle’s documentation is not very clear in this respect, and Google is a mishmash of half solutions and rubbish, so I humbly offer this short instruction for how to ensure users have the correct locale applied.
Solution 1 – manually
Each user can configure their chosen locale, from the My Account dialog:
Whenever changing the locale, you need to logout and log back in to OBIEE for it to take effect.
This setting, if they override it from the default, is stored in the Presentation Catalog under the user’s userprefsxmlstore.xml file
<sawprefs:userPreference prefKey="Locale">en-gb</sawprefs:userPreference>
So for a single user, this method is fine – but for more than one existing user, or any new users, it doesn’t fly. We don’t want to go hacking Presentation Catalog files, that is bad, mmm’kay?
Solution 2 – The USERLOCALE System Session Variable
There is a System Session Variable called USERLOCALE which you can set per user, and will define their locale. Easy! All you have to watch out for is that its value (thanks Christian) is case sensitive. This cost me some hours today, so I’ll say it again – the value you specify for USERLOCALE is case sensitive and OBIEE will not help you out if you specify it wrong (it’ll just ignore it).
You can hardcode the value to the same for all users, or if you want to get fancy you could drive it from a database table to make it variable dependent upon the user’s location that you store in a table.
The Session Variable and example Initialisation Block are shown here:
To validate what is going on with the session variable, you can use a Static Text view with the following content to show the values of the relevant session and presentation variables:
[u][b]System Session Variables[/b][/u]
<p align=left>
[b]NQ_SESSION.USERLOCALE: [/b]@{biServer.variables['NQ_SESSION.USERLOCALE']}[br/]
[b]NQ_SESSION.WEBLANGUAGE: [/b] @{biServer.variables['NQ_SESSION.WEBLANGUAGE']}[br/]
</p>
[u][b]Predefined Presentation Variables[/b][/u][br/]
<p align="left">
[b]session.language:[/b] @{session.language}[br/]
[b]session.locale:[/b] @{session.locale}[br/]
</p>
In my test report the Static Text view is to the right of the data table, and I can now see that the USERLOCALE session variable has been populated. Crucially, the session.locale presentation variable is inheriting the correct value, which in turn is driving the desired formatting of the DATE_LOCALE column.
Method 3 – the hack
I’m including this here for completeness, and because you will come across it in plenty of places on the web so it is good to understand why it is not the correct solution.
Within the OBIEE installation folder (FMW_HOME) you will find the ORACLE_HOME folder, Oracle_BI1, in which all the application’s binaries and internal configuration are stored. This is not a “user serviceable” folder, and Oracle are at liberty to change any of the files within it whenever you patch or upgrade the software.
Within Oracle_BI1 there is a file called localemappings.xml and this file sets the default locale used if one is not explicitly configured. If you change the “catch all” line in this XML file to your desired locale, it will set the default.
<when matches="*"><localeDefinition name="en-gb"/></when>
But, this is not the correct way to do it, and there is no need to because the USERLOCALE method above works just fine.
What about AllowedLocales in instanceconfig.xml?
In combing through the documentation (RTFM, after all), you will come across reference to the Localization tags for the Presentation Services configuration file instanceconfig.xml. Within this tag you can specify AllowedLocales. However, all this does is provide a way to restrict the dropdown locale list (shown in method 1 above). So this is a nice thing to do for your user base if there is a finite number of locales they will want to use (save them wading through many options), but it does not influence the default locale – even if you set it to a single value, your desired default.
Summary
If you want to set the default locale, use the system session variable USERLOCALE. Make sure you specify your value in lowercase, otherwise it won’t work.
Automated Regression Testing for OBIEE
In the first article of this series I explored what regression testing is, why it matters, and by breaking down the OBIEE stack into its constituent parts where it is possible to do it for OBIEE. In this posting, I explore some approaches that lend themselves well to automation for testing that existing analyses and dashboards are not affected by RPD changes.
Easy Automated RPD regression testing – it’s all about the numbers
“Bring me solutions not problems” goes the mantra, and in the first article all I did was rain on the parade of the de facto regression testing approach, looking at the front end using functional testing tools such as Selenium. So if not at the front end, then where should we focus our automated regression testing of OBIEE? Answer: the data.
The data should arguably be what is most important to our users. If it’s wrong, that’s bad, and if it’s right, hopefully they’re going to be happy. Obviously, there are other factors in making users happy not least performance and the visual appearance of the data. But a system that gives users wrong data, or no data, is fundamentally a failed one.
Looking at the following diagram of a request/response through the OBIEE stack we can see that so far as data is concerned, it is the BI Server doing all the work, handling both logical and physical SQL and data sets:
The data that it passes back up to Presentation Services for rendering in the user’s web browser is the raw data that feeds into what the user will see. Obviously the data gets processed further in graphs, pivot tables, narrative views, and so on – but the actual filtering, aggregation and calculation applied to data is all complete by the time that it leaves the BI Server.
How the BI Server responds to data requests (in the form of Logical SQL queries) is governed by the RPD, the metadata model that abstracts the physical source(s) into a logical Business model. Because of this abstraction it means that all Logical queries (i.e. analysis/dashboard data requests) are compiled into Physical SQL for sending to the data source(s) at runtime only. Whenever the RPD changes, the way in which any logical query is handled may change.
So if we focus on regression testing the impact that changes to the RPD have on the data alone then the available methods become clearer and easier. We can take the Logical SQL alone that Presentation Services generates for an analysis and sends to the BI Server, and we can run it directly against the BI Server ourselves to get the resulting [logical] dataset. This can be done using any ODBC or JDBC client, such as nqcmd (which is supplied with OBIEE at installation).
Faith in reason
If:
- the Logical SQL remains the same (i.e. the analysis has not changed nor Presentation Services binaries changed – but see caveat below)
- the data returned by the BI Server as a result of the Logical SQL before and after the RPD change is made is the same
Then we can reason (through the above illustration of the OBIEE stack) that
- the resulting report shown to the user will remain the same.
Using this logic we can strip away the top layer of testing (trying to detect if a web page matches another) and test directly against the data without having to actually run the report itself.
In practice
To use this method in practice, the process is as follows:
- Obtain the Logical SQL for each analysis in your chosen dashboards
- Run the Logical SQL through BI Server, save the data
- Make the RPD changes
- Rerun the Logical SQL through BI Server, save the data
- Compare data before & after to detect if any changes occurred
The Logical SQL can be obtained from Usage Tracking, for example:
SELECT QUERY_TEXT
FROM S_NQ_ACCT
WHERE START_TS > SYSDATE - 7
or you can take it directly from the nqquery.log. For more than a few analyses, Usage Tracking is definitely the more practical option.
You can also generate Logical SQL directly from an analysis in the catalog using runcat.sh – see later in this post for details.
If you don’t know which dashboards to take the Logical SQL from, ask yourself which are going to cause the most upset if they stop working, as well as making sure that you have a representative sample across all usage of your RPD’s Subject Areas.
Give the Logical SQL of each analysis an ID and have a log book of which dashboard it is associated with, when it was taken, etc. Then run it through nqcmd (or alternative) to return the first version of the data.
nqcmd -d AnalyticsWeb -u weblogic -p Password01 -s analysis01.lsql -o analysis01.before.csv
where:
-dis the BI Server DSN. For remote testing this is defined as part of the configuration/installation of the client. For testing local to the server it will probably be AnalyticsWeb on Linux and coreapplication_OHxxxxxx on Windows-uand-pare the credentials of the user under whose ID the test should be executed-sspecifies the Logical SQL script to run-othe output file to write the returned data to.
For more information about nqcmd see the manual.
Once you’ve run the initial data sample, make your RPD changes, and then rerun the data collection with the same command as before (except to a different output file). If the nqcmd command fails then it’s an indication that your RPD has failed regression testing already (because it means that the actual analysis will presumably also fail).
An important point here is that if your underlying source data changes or any time-based filter results change then the test results will be invalid. If you are running an analysis looking at “Sales for Yesterday”, and the regression test takes several days then “Yesterday” may change (depending on your init-block approach) and so will the results.
A second important point to note is that you must take into account the BI Server cache. If enabled, either disable it during your testing, or use the DISABLE_CACHE_HIT request variable with your Logical SQL statements.
Having taken the before and after data collections for each analysis, it’s a simple matter of comparing the before/after for each and reporting any differences. Any differences are typically going to mean a regression has occurred, and you can use the before/after data files to identify exactly where. On Linux the diff command works perfectly well for this
In this case we can see that the ‘after’ test failed with a missing table error. If both files are identical (meaning there is no difference in the data before and after the RPD change), there is no output from diff:
Tools like diff are not pretty but in practice you wouldn’t be running all this manually, it would be scripted, reporting on exceptions only.
So a typical regression test suite for an existing RPD would be a set of these nqcmd calls to an indexed list of Logical SQL statements, collecting the results for comparison with the same executions once the RPD changes have been made.
Tips
- Instead of collecting actual data, you could run the results of nqcmd directly through
md5and store just the hash of each resultset, making for faster comparisons. The drawback of this approach would be that to examine any discrepancies you’d need to rerun both the before & after tests. There is also the theoretical risk of a hash collision (where the same hash is generated for two non-matching datasets) to be aware of. diffsets a shell return code depending on whether there is a difference in the data (RC=1) or not (RC=0), which makes it handy for scripting into if/then/else shell script statementsnqcmdusesstdoutandstderr, so instead of specifying-ofor an output file, you can redirect the output of the Logical SQL for each analysis to a results file (file descriptor 1) and an error file (file descriptor 2), making spotting errors easier:nqcmd -d AnalyticsWeb -u weblogic -p Password01 -s analysis01.lsql 1>analysis01.out 2>analysis01.err
Taking it one step further
We can strip away the layers even further, in two additional stages:
- Instead of examining the data returned by a generated physical SQL query, simply compare the generated SQL query itself, before and after a RPD change. If the query is the same, then therefore the data returned will be the same, and therefore the report will be the same.
One thing to watch for is that the Physical query logged in Usage Tracking (although not innqquery.log) has a Session ID embedded at the front of it which will make direct comparison more difficult.
- The Physical SQL is dependant on the RPD; if the RPD changes then the Physical SQL may change. However, if neither the RPD nor inbound Logical SQL has changed and only the underlying data source has changed (for example, a schema modification or database migration) then we can ignore the OBIEE stack itself and simply test the results of the Physical SQL statement(s) associated with the analysis/dashboard in question and make sure that the same data is being returned before and after the change
The fly in the ointment – Logical SQL generation
This may all sound a bit too good to be true; and there is indeed a catch of which you should be aware.
Presentation Services does not save the Logical SQL of an analysis, but rather regenerates it at execution time. The implication of this is that the above nqcmd method could be invalid in certain circumstances where the generated Logical SQL changes even when the analysis and patch level remain unchanged. If an analysis’ Logical SQL changes then we cannot use the same before/after dataset comparison as described above — because the ‘after’ dataset would not actually match what would be returned. In reality, if the Logical SQL changes then the corresponding Logical resultset is also going to be different.
Two factors that will cause Presentation Services to generate different Logical SQL for an analysis without changing the analysis at all are modifications to an RPD Logical Column’s Sort order column or Descriptor ID column configuration.
As an example, consider a standard “Month” column. By default, the column will be sorted alphabetically, so starting with April (not January)
The Logical SQL for this can be seen in the Advanced tab
Now without modifying the analysis at all, we change the RPD to add in a Sort order column:
Reload the RPD in Presentation Services (Reload Files and Metadata) and reload the analysis and examine the Logical SQL. Even though we have not changed the analysis at all, the Logical SQL has changed:
When executed, the analysis results are now sorted according to the Month_YYYYMM column, i.e. chronologically:
The same happens with the Descriptor ID Column setting for a Logical Column – the generated Logical SQL will change if this is modified. Changes to Logical Dimensions can also affect the Logical SQL that is generated if an analysis is using hierarchical columns. For example, if the report has a hierarchical column expanded down one level, and that level is then deleted from the logical dimension in the RPD, the analysis will instead show the next level down when next run.
Regression Testing Logical SQL generation
It is important that Logical SQL is considered as part of regression testing if we are using this targeted approach – it is the price to pay for selectively testing elements of the stack using reasoning to exclude others. In this case, if the Logical SQL changes then we cannot compare datasets (because the source query will have changed when the actual analysis is run). In addition, if the Logical SQL changes then this is a regression in itself. Consider the above Sort order column example – if that were removed from an RPD where it had been present, users would see the effect and quite rightly raise it as a regression.
There are at least two ways to get the Logical SQL for an analysis programatically : the generateReportSQL web service, and the Presentation Services Catalog Manager tool. We will look at the latter option here. The Catalog Manager can be run interactively through a GUI, or from the command line. As a command line utility it offers a rich set of tools for working with objects in the Presentation Catalog, including generating the Logical SQL for a given analysis. The logical outline for using it would be as shown below. If the Logical SQL is not identical, or runcat.sh fails to generate Logical SQL for the analysis after the RPD is changed, then a regression has occurred. If the Logical SQL has remained the same then the testing can proceed to the nqcmd method to compare resulting datasets.
runcat.sh is a powerful utility but a bit of a sensitive soul for syntax. First off, it’s easiest to call it from its home folder:
cd $FMW_HOME/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalogmanager
To see all the things that it can do, run
./runcat.sh -help
Or further information for a particular command (in our case, we’re using the report command):
./runcat.sh -cmd report -help
So to generate the Logical SQL for a given analysis, call it as follows:
./runcat.sh -cmd report -online http://server:port/analytics/saw.dll -credentials creds.txt -forceOutputFile output.lsql -folder "/path/to/analysis" -type "Analysis" "SQL"
Where you need to replace:
- server:port with your BI Server and Managed Server port number (for example, biserver:9704)
- creds.txt is a file with your credentials in, see below for further details
- output.lsql is the name of the file to which the Logical SQL will be written. Remove the ‘force’ prefix if you want runcat.sh to abort if the file exists already rather than overwrite it
- /path/to/analysis is the full path to the analysis (!), which you can get from both OBIEE (Catalog -> Object -> Properties) and from the Catalog Manager in GUI mode (Object -> Properties). In the screenshots here the full path is /shared/Standard/Analyses/Sales Report
Having called runcat.sh once, you then make the RPD change, reload the RPD in Presentation Services, and then call runcat.sh again and compare the generated Logical SQL (e.g. using diff) - if it’s the same then you can be sure that when the analysis runs it is going to do so with the same Logical SQL and thus use the nqcmd method above for comparing before/after datasets.
To call runcat.sh you need the credentials in a flatfile that looks like this:
login=weblogic pwd=Password01
If the plaintext password makes you uneasy then consider the partial workaround that is proposed in a blog post that I wrote last year : Make Use of OBIEE’s Command Line Tools with Reduced Exposure of Plain Text Passwords
Bringing it together
Combining both nqcmd and runcat.sh gives us a logic flow as follows.
- Run Logical SQL through nqcmd to generate initial dataset
- Run analysis to which the Logical SQL corresponds through runcat.sh to generate initial Logical SQL
- Make RPD changes
- Run analysis through runcat.sh again. If it fails, or the Logical SQL doesn’t match the previous, then regression occurred.
- Rerun nqcmd, and compare the before/after datasets. If they don’t match, or nqcmd, fails, then regression occurred.
You may wonder why we have two Logical SQL statements present – that for use with nqcmd, and that from runcat.sh. The Logical SQL for use with nqcmd will typically come from actual analysis execution (nqquery.log / Usage Tracking) with filter values present. To compare generated Logical SQL, the source analysis needs to be used.
Summary
So to summarise, automated regression testing of OBIEE can done using just the tools that are shipped with OBIEE (and a wee bit of scripting to automate them). In this article I’ve demonstrated how automated regression testing of OBIEE can be done, and suggested how it should be done if the changes are just in the RPD. Working directly with the BI Server, Logical SQL and resultset is much more practical and easier to automate at scale. Understanding the caveat to this approach – that it relies on the Logical SQL remaining the same – and understanding in what situations this may apply is important. I demonstrated another automated method that could be used to automatically flag any tests for which the dataset comparison results would be invalid.
Testing the data that analyses request and receive from the BI Server can be done using nqcmd by passing in the raw Logical SQL, and this Logical SQL we can also programatically validate using the Catalog Manager tool in command line mode, runcat.sh.
Looking back at the diagram from the first post in this series, you can see the opportunities for regression testing in the OBIEE stack, with the point being that a clear comprehension of this would allow one to accurately target testing, rather than assuming that it must take place at the front end:
If we now add to this diagram the tools that I have discussed in the article, it looks like this:
I know I’ve not covered Selenium, but I’ve included it in the diagram for completeness. The other tool that I plan to cover in a future posting is the OBIEE Web Services as they also could have a role to play in testing for specific regressions.
Conclusion
Take a step back from the detail, I have shown here a viable and pragmatic approach to regression testing OBIEE in a manner that can actually be implemented and automated at scale. It is important to be aware that this is not 100% test coverage. For example, it omits important considerations such as testing for security regressions (can people now see what they shouldn’t). However, I would argue that some regression testing is better than none, and regression testing “with one’s eyes open” to its limitations that can be addressed manually is a sensible approach.
Regression testing doesn’t have to be automated. A sensible mix of automation and manual checking is a good idea to try and maximise the test coverage, perhaps following an 80/20 rule. The challenges around regression testing the front end mean that it is sensible to explore more focussed testing further down the stack where possible. Just because the front end regression testing can’t be automated, it doesn’t mean that the front end shouldn’t be regression tested – but perhaps it is more viable to spend time visually checking and confirming something than investing orders of magnitude more hours in building an automated solution that will only ever be less accurate and less flexible.
Regression Testing OBIEE is not something that can be solved by throwing software at it. By definition, software must be told what to do and OBIEE is too flexible, too complex, to be able to be constrained in such a manner that a single software solution will be able to accurately detect all regressions.
Successfully planning and executing regression testing for OBIEE is something that needs to be not only part of a project from the outset but is something that the developers must take active responsibility for. They have the user interviews, the functional specs, they know what the system should be doing, and they know what changes they have made to it — and so they should know what needs testing and in what way. A siloed approach to development where regression testing is “someone else’s problem” is never going to be as effectively (in terms of accuracy and time/money) as one in which developers actively participant in the design of regression tests for the specific project and development tasks at hand.
Many thanks to Gianni Ceresa for his thoughts and assistance on this subject.
How-To: File hunt for OBIEE GUI changes
The single most important things when working with anything relating to the GUI is: Firefox with Firebug (or Internet Explorer with F12 Developer Tools)
OBIEE Regression Testing – An Introduction
In this article I’m going to look at ways to test changes that you make to OBIEE to ensure that they don’t break existing functionality. In all but the simplest IT systems it’s common for one (planned) action to inadvertently cause another (unplanned).
What IS Regression Testing?
When we make a change to a system we use functional unit tests to ensure that it does do what it is supposed to do. We should also make sure that the same changes don’t do what they’re not supposed to, that is, cause functionality already existing in the system to change behaviour. If this does happen it is known as a regression and is something we want to ensure doesn’t happen without us knowing. Some examples of regressions seen in standard OBIEE development changes include:
- Reports stop returning data, showing an error instead
- Reports start to show the wrong data
- Some combinations of dimensions and facts to no longer show data, or show an error
- Dashboards that reference a particular analysis stop working
As well as these, less common system changes can also cause regressions, for example:
- An OBIEE version upgrade causes certain types of graph to render in a different way from the previous version
- An OBIEE patch introduces a bug in the front end user interface
What drives Regression Testing?
The requirement for regression testing OBIEE broadly comes from two different types of change:
- New binaries – that is, an upgrade (or patch) of OBIEE
- New “application code” – changes to the RPD, the underlying database schema, and so on.
These two requirements have the same aim – make sure nothing breaks when we make the change – but differ in ways that make how we address them important:
- Frequency : OBIEE may get patched once or twice a year, and upgraded every few years. Compare this to development changes made to the RPD et al, which users would often like to see happening on a frequent basis (sometimes daily at the beginning of an implementation). If these changes are happening with great regularity then (a) we don’t want to be the ones causing the bottleneck because we can’t regression test them and thus (b) we need to find a repeatable way to perform these tests accurately and quickly.
- Delta Visibility : When Oracle change the OBIEE code base, we are blind as to what has gone on under the covers. Sure, we know what’s changed in the documentation, but as a starting point for “what might have broken” we can only assume everything has and test accordingly. Conversely, in a planned development we know exactly what we changed and we can therefore work out the scope of the necessary testing.
The points in bold above are what I aim to address in this article. Regression testing OBIEE doesn’t have to mean one technique alone – it can be refined based on what we know has changed.
Why Regression Test?
If you don’t regression test then you place a wager that you’ll be able to fix any problems that arise. As soon as they arise. In Production. With angry users on the phone. And the project manager screaming blue murder because their change is getting blamed for breaking everything.
This is a recipe for compounding errors upon errors, not a stable system. Testing, in all flavours, is about gaining confidence about the impact of a proposed change to a system. Functional testing reassures us that the change will do what it was designed to do. Performance testing helps us understand how a system behaves from a response time and capacity perspective. Regression testing gives us the confidence that a change, whilst doing what it ought to, isn’t going to affect something else.
The confidence in what is (and isn’t) going to happen when we deploy a change enables us to make these changes more frequently as required by the users. Instead of a long development cycle with a huge number of changes bunched in together, and one big bang test and release, we can take a more rapid, flexible, and responsive approach to development and release because we have the confidence that an individual change is going to work.
In addition to confidence in additional releases to new deployments, a good regression testing framework enables us to have confidence in making changes to long-standing big ball of mud systems. So long as we understand the relevant interfaces points in OBIEE, we can build a pass/fail test framework on top of the most complex RPD/schema.
Targeting Regression Testing Effectively
Regression testing is easy. You pay a troop of monkeys to sit at a set of computers and run every single dashboard, build every permutation of adhoc report, and if you’ve just upgraded or patched OBIEE, go through the user interface with a fine toothed comb. After the appropriate period of several weeks, any differences they find from before your change was made is a regression. Congratulations. All you need to do now is fix the problem – and then of course, regression test your new change. So monkeys are one option, but they’re expensive (you should see the wholesale market peanut price these days), they’re not infallible (monkeys get distracted by YouTube too), and they are slow.
Better than monkeys is automated regression testing, targeted smartly at the area of OBIEE that has been changed. We will now take a look at which changes can cause regressions in which area, and from that derive a list of testing methods appropriate for each type of change made.
Regression testing points in the OBIEE stack
To understand how we can regression test OBIEE, let us look at where a regression can be detected. The following diagram illustrates the request/response flow through the components in the OBIEE stack. We can use it to see where regressions may expose themselves, and thus understand at what points we can consider testing for them.
Starting from the point of view of the actual end user:
- The user interface may regress. They may be actual bugs that weren’t there before, or ‘regressions’ in the sense that functionality or icons/layout have changed. These changes would typically only come about through software changes (patching/upgrades).
Regressions could also occur if you are manipulating the UI through the analysis itself (eg narrative view) and the behaviour changes, but this type of UI modification is less common. - Regressions caused by changes to the underlying data, RPD or analyses are going to manifest themselves through a dashboard. This could be in the data or the presentation of the data (tables, graphs, etc).
- Considering a dashboard by its constituent parts, an individual analysis could exhibit differences in its data or the presentation of the data
Next to consider is that each analysis sends a “Logical” SQL request to the BI Server. It is not common, but it is possible that a change to the binaries (version upgrade/patch) could introduce a regression that caused the Logical SQL to be generated incorrectly. Specific changes to the RPD can also cause the Logical SQL generation to change, potentially erroneously.
The Logical SQL that is generated is executed by the BI Server which in turn returns the requested logical resultset data. This resultset may expose a regression in how the BI Server is handling the logical request. 
A “Logical” SQL request on the BI Server is parsed through the metadata layer, the RPD, and one or more “Physical” SQL statements are sent to the underlying data source(s). An error in the RPD could result in the Physical SQL being generated incorrectly.
Finally, each “Physical” SQL request at the data source returns data back to the BI Server. Any errors in changes to the physical sources will show themselves through the physical query failing, or the results being incorrect. 
Regression testing opportunities
To summarise the previous section, our testing points for regression are as follows.
- The logical query generated by Presentation Services for an analysis
- The physical query/queries generated by the BI Server to retrieve the data from the data source(s)
- The data supplied by the data source to the BI server
- The data supplied by the BI server for an analysis (logical resultset)
- User interface, including the dashboard/analysis, taking into account both rendered data and presentation/UI.
Regression testing is based around comparing one state (before a planned change) to another (after the planned change). In considering how we are going to perform our testing, let’s take a very simplistic view on what we need to test:
- Does it look the same
- Are the numbers the same
Of these two, one is very easy to get a computer to do (and conversely, very laborious to perform manually), and the other is very difficult to explain to a computer (and relatively easy to do manually): -
- Telling a computer to fetch some data twice and compare the first result with the second is bread and butter automation.
- Trying to explain to a computer what a page “looks” like, or what a user interface “does” is extremely time consuming, and inevitably specific to the single item in question. Of course, we can programmatically compare the underlying code for a dashboard before and after a change, but the question I pose is whether we should.
Computers are blind
The user interface for an OBIEE end user is a web browser, and OBIEE builds its web pages through a set of languages and protocols that used to be quaintly referred to as “Web 2.0”. It uses HTML, CSS, XML, and JavaScript, taking plentiful advantage of asynchronous page loading and in-flight modifications to the Document Object Model (DOM) too. AJAX is a term which certainly covers some of the magic that goes on. The resulting user interface is pretty slick with drop down menus, expanding hierarchy trees, and partial dashboard rendering as data is returned rather than waiting for all analyses to complete. All of this omits the knockout blow that is Flash, used for rendering all graph objects in OBIEE and the subject of at notable UI bug in OBIEE 11.1.1.6. The “Developer Tools” option in modern web browsers gives us a glimpse into what is going on under the covers. We can see the number of resources that go into rendering a single page…
…and how many layers there are to the object model:
Getting a computer to interface with all of this, simulating a user interaction and parsing the response is possible with functional testing tools such as Selenium, Oracle Application Testing Suite, and HP’s QuickTest Professional. Each of these tools is capable of simulating a user (often by ‘recording’ a session as the starting point) and parsing the responses from OBIEE.
But, there is a fundamental complication to using these tools. For all the AJAX/CSS/DOM magic to happen, the page that OBIEE generates is littered with element identifiers (so that the JavaScript code can identify the element to manipulate). For example, the following table cell has the ID in this particular execution of e_saw_14485_10_1_0_0:
Some of these IDs may change between report executions or sessions, but either way, cannot be relied on to be consistent. Therefore, getting our testing tool (such as Selenium) to compare the before/after results to detect a regression becomes a whole heap more tricky. It is possible to work out element paths based on their relative position within the page rather than an absolute ID, but that becomes even more page specific and complex to implement. Therefore to compare a before and after page programatically we have to either
- define a particular part of the page alone to check remains the same (and risk chucking the baby out with the bath water, that is, missing other genuine regressions elsewhere on the page)
or we have to
- compile a list of elements that we expect may change but that we don’t count as a regression (i.e. exceptions).
The latter is going to be prone to causing false positives (i.e. failing regression tests that aren’t genuine regressions) because it relies on reverse engineering the full Document Object Model of the OBIEE page. All of this is also without even taking into account software patching and upgrades – so far as Oracle are going to be concerned how a page is rendered is their own business and thus at full liberty to completely change the internal structure of a page as they desire. Given this above complication, it becomes clear that building a test against a single page is time consuming, and it will typically be specific to that page only. This becomes a problem the greater the scale of the deployment you are trying to test. Hardcoding the testing for one specific page might be fine, but given more than a handful of pages you risk ending up with a large inflexible regression test code base (that itself may become error prone and need regression testing when it’s changed…).
Conclusion
So, we come back to not how we test the front end but more should we, in every case? Given a finite amount of time, what are you going to get most benefit from in your regression tests? In the next post I will demonstrate one of the ways you can get the most “bang for your buck” when regression testing OBIEE, by concentrating your automation efforts on the query part of the OBIEE stack, and not the front end. Stay tuned!
Many thanks to Gianni Ceresa for his thoughts and assistance on this subject.
