Impressions of the Oracle BI 11g Launch in New York

Hats off to Oracle for doing a great job on the much anticipated launch of OBIEE 11g. Dennis Busby (BICG) and I were fortunate enough to attend the launch event in New York on July 20 with approximately 300 other people.



The session started with Tony Fernicola kicking off the launch from one of the buildings that make up the Lincoln Center campus. The theater featured an amazing view of Central Park and the Manhattan skyline. Tony set the stage discussing the history of BI at Oracle as well as Oracle’s vision for business intelligence in the future.

The OBIEE 11G release is instrumental to Oracle’s BI vision as it does a great job of integrating OBIEE with additional Oracle technologies including Hyperion Essbase, Oracle Enterprise Manager, Oracle WebCenter and WebLogic (to name a few).

Paul Rodwick and David Granholm then served as co-presenters for the North American release of OBIEE 11g. They grouped the content into 4 different categories that included ROLAP, MOLAP, BI Publishing, and Collaboration. During the presentation, Rodwick and Granholm provided views of the interface design and of the enterprise approach to the architecture. Both presenters did a very nice job showing off Oracle's new business intelligence suite in a way that I felt highlighted the cababilities around OBIEE 11g.



BI Consulting Group has been an integral part of the beta process. BICG's OBIEE 11g Task Force members, consisting of 15 BICG consultants and managers, are working daily on dissecting the OBIEE 11g beta release to ensure we continue to be the most knowledgeable Oracle Business Intelligence experts in the world. Our OBIEE 11g Task Force has been doing a great job on providing beta feedback to Oracle as well as paving the way for BICG to guide and help our customers who plan on embarking on their own OBIEE 11g initiatives.

To ensure we are continually providing proper guidance to our customers and partners around Oracle BI 11g, BICG will be announcing new topics for our BICG Webinar Series that will feature BICG's OBIEE 11g Task Force and focus on the key questions our customers are asking about 11g. Our OBIEE 11g Task Force is also in the process of scheduling Free OBIEE 11g Hands-On Workshops across North America to allow customers and prospects an important hands on experience!

I truly believe the new capabilities of Oracle Business Intelligence 11g will allow organizations to better meet their strategic enterprise business intelligence-related goals . I am most excited to start thinking creatively around best practice usage of collaboration, action framework and the Hyperion Essbase integration as these new features will be game-changers throughout the entire BI space.

Upcoming BICG Webinar Series Events

Essbase Unleashed!

Leveraging XOLAP and Hybrid Analysis for Increase Performance and Real-time Analytics

July 27, 2010, 1pm-2pm ET

Register Now

OBIEE 11g - What's in it for me?

The first chapter of many BICG Webinar Series chapters to focus on Oracle BI 11g. BICG's OBIEE 11g Task Force will simplify the 100's of enhancements and new functionality in the new release.

August 31st, 2010, 1pm-2pm ET

Register Now

BICG at Florida OAUG Summer Meeting

DLT Solutions, Oracle, and BI Consulting Group (BICG), will be holding a half-day workshop for the Florida OAUG on Tuesday, July 20th. In addition to the members of the FLOAUG this event is opened to all State & Local Government agencies as well.

At this event we will focus on the Oracle HR/Payroll modules and Oracle’s BI Analytics platform with an emphasis on HR reports, graphs, dashboards, and KPIs. The discussion will be led by both Oracle and BI Consulting Group with such noted speakers as Ian Sterling (Oracle), Matt Kvancz (BICG), and Todd Wall (Oracle) sharing their extensive knowledge and experience with attendees.

Ian sterling will discuss HCM Roadmap and Vision, Key Features, R12.1.3 Functional Enhancements, and Productive User Interfaces. Todd Wall will discuss the R12 HCM Upgrade Timeline, key R12 milestones, and workarounds. Brad Reinders and BI Consulting Group will conclude the day focusing on the Oracle BI Analytics platform with an emphasis on HR/Payroll metrics, KPI’s, and reporting.

Agenda:

0930 – 1000 Registration
1000 – 1010 Introductions
1010 – 1100 Oracle HCM Roadmap, Key & New features/functionality
1100 – 1110 Break
1110 – 1200 R12 HCM Upgrade Timeline, Key Milestones, Assessments, & work arounds
1200 – 0100 Lunch
0100 – 0250 BI Consulting Group Oracle BI Analytics Overview and HR/Payroll reporting
0250 – 0300 Concluding Remarks

We hope to see you there!


Date:
Tuesday, July 20th
Time:
9:30 a.m. – 3:00 p.m. ET
Location:
DoubleTree Castle Hotel
8629 International Drive
Orlando, FL 32819

Registration Link: http://www.dlt.com/events/Florida_Oracle_Application_Users_Group/2488

Using Selenium for OBIEE Regression Testing

In my last post I introduced Selenium, an open-source testing platform with an easy-to-use Firefox plugin that can be used as a cost-effective tool for regression testing web-based applications like OBIEE. I promised to demonstrate workaround to OBIEE-specific challenges when using Selenium, namely:

• Selenium's problematic handling of daughter or "target" windows, and
• Dynamic element ID generation in OBIEE
  

[Note: All examples given here will be executed against the Paint demo installation using Firefox 3.5.10, which according to Oracle Support is the most recent version of Firefox that is supported in the most recent version of OBIEE (10.1.3.4.1)]

Here's a common scenario impacted by these challenges: When configuring a Dashboard to link to another Dashboard, OBIEE would open the new Dashboard in a "daughter" or "target" window.

Selenium would have trouble scripting this behavior because a) the only way Selenium can effectively identify target windows is by element ID (this is a known issue that doesn't appear to have a resolution from the Selenium project forthcoming anytime soon - though, in the grand tradition of Open Source software, you are welcome to figure out your own fix!) and b) OBIEE generates element ID's dynamically, which means they will change with each instance of the Presentation Server. So any Selenium-generated script would not work against a different Presentation Server, or against the same Presentation Server after a restart.

To illustrate the problem more clearly, I will create links to Dashboard pages in "My Dashboard." In this example I created links to "Brand Analysis", "Regional Analysis" and "Year over Year Analysis".


As you know, clicking into any of these links opens a new window (whose name = "_blank") to display the selected dashboard.

The workaround solution to this problem: 1) identify the URL that is opened in the "_blank" window, then 2) execute a standard Selenium "Open" command on that URL.

The key is the first step: identifying the URL of the "_blank" window. Doing so involves two tricks: a) Identifying the desired <A> element using XPath (which is an open standard that Selenium relies on to identify & interact with page elements); then b) identifying its HREF attribute.

If you're not familiar with XPath, it may be helpful to take a look at the following sites first, each of which offer a concise one-page explanation of XPath basics:

• XPath Syntax
  http://www.w3schools.com/xpath/xpath_syntax.asp
  
  
• How XPath Works
  http://java.sun.com/j2ee/1.4/docs/tutorial/doc/JAXPXSLT3.html
  

Let's take a look at the html for the customized "My Dashbaord" to understand some essential XPath concepts.

<a
   href = "saw.dll?PortalPages[...]Done=Close"
   target = "_blank"
   name = "SectionElements">
   <span
      class="NavLinkCaption">
      Brand Analysis
   </span>
   </a>

The "attributes" of the <a> tag (or "Element") are "href", "target" and "name". Their respective values are 'saw.dll?PortalPages[...]Done=Close', '_blank', and 'SectionElements'.

The "attribute" of the <span> element is "class" and its value is 'NavLinkCaption".

The "child" of the <a> element is the <span> element. Or put another way, the <a> element is the "parent" of the <span> element.

The <span> element content "contains" the text 'Brand Analysis'.

Using XPath there are several ways to identify an element, but not all can be used with web pages created in OBIEE:

• Element ID - Usually the best approach, and is the default behavior in Selenium, however this approach is not possible because OBIEE generates element ID's dynamically
• Ordered location in the document - Not advisable because any change in document content (e.g., adding a new <a> tag before the desired <a> tag) may break the XPath query
• Attribute (href, target, name, etc) - Not possible because OBIEE attributes are not unique; there are several href's whose target = "_blank" or name = "SectionElements"
• Displayed content - The best bet for OBIEE, since displayed content will be unique by definition (how else could the user differentiate between the links?)
  

To identify the desired <a> element via the contents of the <span> element, we can use the XPath "contains()" predicate:

//a/span[contains(.,'Brand Analysis')]

Pseudo-SQL translation: Select span.* from document where span.parent = <a> and span.content like '%Brand Analysis%'

Then the ".." expression (meaning "Parent of") can be used to identify the desired <a> tag, which is the parent of the <span> tag we identified above.

//a/span[contains(.,'Brand Analysis')]/..

Pseudo-SQL translation: Select span.parent from document where span.parent = <a> and span.content like '%Brand Analysis%'

Now we're going to use this XPath path expression to create two Selenium test case steps -- one which uses the Selenium saveAttribute command to assign the value of the <a> tag's href to a variable named 'windowURL', and another which opens that URL within the current window:

[Background: Selenium steps consist of three components: Command, Target and Value - For full documentation of Selenium's functionality see its official site: http://seleniumhq.org/docs]

Step 1:
Command: storeAttribute
Target: //a/span[contains(.,'Brand Analysis')]/..@href
Value: windowURL

Step 2:
Command: open
Target: ${windowURL}
Value: [blank]

Here's how the commands would look like in Selenium:


There are many more aspects to Selenium integration with OBIEE to learn that I hope to touch on in subsequent posts - but this little tutorial should give you a good start.

Have fun...

Effective Regression Testing for OBIEE Applications

It's a dark and stormy Friday night, and as you drift off into slumberland in your warm, dry and cozy bed, a thought pops up in your mind: "If a butterfly flaps its wings in Costa Rica tonight, will the CFO's Executive Financial Summary dashboard still work?" Suddenly you are wide awake and feeling a bit of paranoia. You jump out of bed, fire up your laptop, log into the corporate VPN and then into OBIEE...

This sounds like the beginning of a bad BI horror story -- and hopefully for all of you a scenario like this truly is just that - a story. But we've all experienced some variation of this scene. The OBIEE equivalent of butterflies do flap their wings in Costa Rica, and the result does sometimes alter the OBIEE equivalent of weather patterns in Japan. Change the name of a presentation column... or the mapping rules of a logical column... or the aggregation rules of a logical table... or a minor extraction rule in the warehouse ETL logic... or even simply merge your local RPD work into a MUD repository... and you risk causing an error in a report that might not be noticed until days after the change -- usually when the report is most needed.

The real truth is that an OBIEE system just like any other software implementation has some inherent fragility that requires vigorous regression testing strategies to keep it running smoothly. But while most "traditional" software systems have very clearly defined behaviors that lend themselves well to control by means of various specific testing strategies, a Business Intelligence application by nature does not always necessarily have completely predictable outcomes - especially true in OBIEE given the complexity of the BI Server and, for example, how it constructs queries against the underlying data.

Given the complexity of the OBIEE architecture and the fleeting nature of the data, establishing an adequate regression testing strategy is a challenge.

One excellent approach is to identify commonly executed queries with Usage Tracking (or just identify a specific request's logical query), then execute the corresponding logical sql using nqcmd.exe. This is an excellent approach but can respond to only two results: success or failure. In most cases this is entirely adequate.

Another approach favored of course by Oracle is to implement their Application Testing Suite, which looks ike a promising tool but frankly I have not heard of any usage in the real world -- if anyone has direct experience with this tool, feel free to respond. Moreover it goes without saying that license fees would be a significant factor in evaluating its total ROI.

A third way to address regression testing in OBIEE is to set up a series of webcat (HTTP) requests that can be executed on a scheduled basis by an automated web testing utility. Several such utilities exist - LoadRunner being the most prevalent - but as with Oracle's offering, most require licensing.

One popular web testing utility distributed as open-source software and therefore free of licensing costs has gained some following among the developer community: a Firefox plugin called Selenium (the IDE flavor, to be precise), which can execute a wide range of web page interactions and combine them into test scripts. These test scripts can then be assembled into test suites and exported to a full-fledged program in a variety of commonly used languages/frameworks (C#, Java, Perl, PHP, Python, Ruby), at which point this program can be scheduled for execution just like any other.

While Selenium is highly flexible, fairly easy to use and very cheap (at least in terms of licensing costs), it also has some drawbacks, particularly when used to test an OBIEE system. In the hopes of blazing the "Selenium for OBIEE" trail, I have attempted my own small POC by writing some basic test scripts against the Paint webcat. In doing so I identified some gotchas and workarounds that will at the very least help you successfully construct OBIEE test scripts using this very capable tool. I will explore those workarounds and advanced configurations in later posts. But my overall opinion is that Selenium is a very capable testing tool which deserves serious consideration for use in an OBIEE environment.

Here are what I consider general strengths of Selenium:

Flexible

• Can execute a variety of actions: open urls, click links, interact with prompts, execute javascript ...
• Can be designed to respond to specified conditions either by failing OR by simply logging and continuing
• Can assemble multiple test scripts within a single test suite
• Can export scripts in a variety of languages / platforms - C#, Java, Perl, PHP, Python, Ruby
  

Straightforward to use

• Scripts can be written using a simple Firefox plugin
• Applies open standards: page elements are identified using Xpath
  

Economical

• Open Source therefore no licensing
• Fairly wide adoption therefore a decent user community
• Commercial organizations exist that specialize in Selenium development & support
  

And some general challenges:

Somewhat steep learning curve

• Though basic use is straightforwad, still takes a bit to get the hang of the tool
• Because it's based on open standards like XPath, the skills learned are not "siloed" but can be useful elsewhere
• Extending test suites for scheduled execution requires a leap of additional complexity
  

Open Source = No official support

• On the other hand, there are plenty of answers to common problems on the web
• Googling a solution is probably just as fast as relying on traditional paid support
  

OBIEE-Specific Challenges

• Problematic handling of daughter or "target" windows
• Default page element XPath queries cannot be used because they rely on IDs, which in OBIEE are dynamic
  

Session Variable Manipulation in Answers

Recently I worked on a project that used Non-System Session variables for some of the reports. While on the project I learned a nice trick on how to modify value of these variables in Answers to perform testing without having to jump out to a dashboard.

In this sample Answers report I'm using a Non-System Session variable called ReportDate to calculate the employees most recent department and compensation for head count purposes.
The report below shows the head count and compensation numbers as of May 31, 2010 since that is what I defaulted the session variable to in the initialization block.

Now I can modify the value of the ReportDate session variable by clicking on the "Advanced" tab at the top and then scrolling down to the "Prefix" portion of the page. Within the "Prefix" box I can enter the following syntax to change the date to January 1, 2009: SET VARIABLE ReportDate='01/01/2009';



Here is the new screen shot of the report showing the break out of employees for January 1, 2009.


If you need to modify multiple Non System Session Variable values simply delimit them with a comma like so...SET VARIABLE ReportDate='01/01/2009', SessionVariable2='Something', SessionVariable3='SomethingElse';

OBIEE 11g launch in London July 7th, 2010

Well, it seems that OBIEE 11g will be officially launched in London on 7th July, YES this 2010 :)
Here is the invitation to such event in the Oracle site:

Launch Event: Introducing Oracle Business Intelligence Enterprise Edition 11g

BI Apps Performance Optimization and Consideration - Part 3

In this last part we will look at other options of optimizing the performance of OBIEE



Aggregation Strategy

• Multi-level (pyramid) aggregation strategy: level 1 to level 2, level 2 built on level 1


• Incremental aggregation for level 1 aggregates to reduce ETL time


• Level 1 aggregates can be at the lowest leaf of each dimension to avoid bulk re-aggregation when dimension hierarchies change (e.g. sales force re-organization, product hierarchy changes)


• Bulk aggregations for higher levels for simplicity


• Data compression drives aggregation (at least 10 x, preferably closer to 100x). This could be implemented for Service Request tables


• Try aggregation based on dimensions instead of factsAggregation to the ratio of 50:1

Caching Strategy

• Proper caching strategy needs to be implemented as this is among the best practice of optimization . Caching must be implemented based upon users by groups


• Set the physical table cache property on Mater table not aliases


• Set cache persistence time based of refresh schedule


• Ideally do not opt Cache never expires in the physical table properties


• Caching logs may be monitored for user sessions and queries may be analyzed across various users to optimize


• Use ODBC extension functions to purge cache which has the following advantages


• Analytics Cache Purge ODBC Extension Functions


• ETL routines can proactively call ODBC extension functions to manage cache. This ensures that obsolete cache is purged as soon as possible


• Purging Cache by Logical Query is a new capability. This will be particularly helpful in purging cache associated with logical queries that are used in Cache Seeding.


• Purging the entire cache set in one command is a new capability that can be used in various scenarios including development and testing environments.


• SAPurgeCacheByQuery will purge a cache entry that exactly matches the logical query plan derived from a specified query. This function takes one, and only one, parameter representing query text. USE_ADVANCED_HIT_DETECTION. The default value is NO which will have the pre-existing cache hit detection behavior. The value YES will invoke the two pass algorithm which will examine a greater search space and is potentially more expensive than the default behavior. you should set the value to YES when you wish to maximize the number of cache hits they obtain
  
  Check the following Cache for scalability

Browser Cache
Scripts like Java or HTML
High resolution images
Data Access
Query Plan cache
Multithreaded Database Pools

BI Server Cache
Result Set
Aggregations
Summaries



Database Optimization

1. Database archive strategy

• The data in the databse needs to be archived from time to time in order to manage the data size. The idea is to keep only a certain amount of data in the warehouse and archive the rest of the data. For example, keep only the last 12 months of data in the warehouse, and archive older data from the warehouse.


• This database archive strategy will help limit the number of rows across tables with huge data
  

2. Database Partition strategy

• A good database partition strategy will help query performance since the query will only need to look at a specific partition to obtain the result. For example: The Organization table can be partition based on Active Flag ; The Service Request table can be partitioned based on X_CALC_FLG = 'Y’

3. Database Indexes

• A database index should be added for the most queried columns. The database index should also be rebuild from time to time to ensure its effectiveness. ALTER INDEX MONITORING USAGE command that collects statistics over a period of time.


• Increase Parallel Query by configuring the database server for an optimal degree of parallel processing.


• Given sufficient resources, this can greatly improve query


• Make sure that cost-based optimization is enabled on databases


• Set the tablespace to at least the same as the transactional database size. Make sure the temporary tablespace has adequate space


• Create histogram statistics for these indices to enable the optimizer to better perform queries on critical tasks


• Spread data across multiple disks. RAID Redundant Array of Independent Disks configuration if possible is recommended


• Partition Large fact tables like Asset, SR is recommended


• Set the number of log file groups to 4 on Warehouse schema


• Set the size of each log file to 10 MB. on warehouse db


• Set the sga_max_size to 700 MB or more


• On the client side, edit the tnsnames.ora file. Modify the TNS alias by adding SDU= and TDU=as follows:
  myhost_orcl.world=DESCRIPTION=(SDU=16384)(TDU=16384)
  ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521)) CONNECT_DATA=(SID=ORCL))


• On the Oracle client Set the NLS_SORT parameter to BINARY


• The ETL implementation team should use analyze table commands specific to their database environment, that can be executed after the respective session has completed. This can be achieved by using the post-session command property of each session
  Try to break up ETL processing so data is extracted and staged remotely then compressed for further local processing


• Remote ETL servers extract and stage to local flat file, compress and send the flat file to central ETL server


• Central ETL Server performs the ‘stage to data warehouse’ process


• Manage I/O Traffic --> Manage the input and output accesses to disk storage by striping the disk storage. The best and simplest action is to install disk storage arrays (RAID), the second best is to stripe volumes using a Logical Volume Manager.


• De fragment tables to which the data is extracted


• See how much memory is on the available server and set the values for SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE rather high. 20, 30 or 40 MB per session is not uncommon in a data warehouse situation. Also set HASH_AREA_SIZE rather large

Infrastructure Options

• Consider deployment options on separate hardware for various OBI components like BI Publisher, OBI Server, OBI Presentation Services, Oracle Delivers Server


• Hardware must be available based of the recommendations from Oracle. The hardware for database must be must also be setup based of the size of the database