Oracle BI Blog - EPM, Business Intelligence, and OBIEE

Working with the Master-Detail Report Feature of OBI 11g (pt 3)

2011-02-07T07:41:00.005-06:00

The final installment of this series will look at configuring detail views to listen to multiple event channels.

My first step will be to create a new Master View analysis. I am going to create a simple tabular view as my second master view including the Qtr column I have used previously for my data event driver. I have set the Channel name to MDS3 in the same way I configure my first master view.

I have saved that analysis and now want to edit 1 of my detail views to listen on this channel as well as the MDS2 channel it is currently listening on.

Just a side note: If you have been following this series, you will notice the view names have changed as well as the view layout on the dashboard page from my previous posts. I have renamed my views and reorganized them on the dashboard to make things a little clearer. Other than the changes I have outlined here, the other views remain unchanged.

So, the detail view I decided to modify is the second one I created which has the pie chart. Opening the pie chart view in the editor window and opening the properties for it, I have added my new channel to the event channel. Notice the channels are separated by a comma. With that change made, I then save this analysis.

Adding the new Master view to my dashboard page, I am ready to test this new functionality.

Clicking on the 2008 Q2 cell from my first master view, both detail views respond with the Qtr column slider moving appropriately.

I now click on the 2008 Q3 cell on my second master view, on the detail pie chart view does respond as expected with the Qtr slider moving to the appropriate value while the other detail view remains unchanged.

Thus we can see that a detail view can listen to multiple events from different master views.

There is actually more to learn and do with Master-Detail links but that is a different discussion as it involves extending OBI with some custom code. I will leave that for a later time.

Working with the Master-Detail Report Feature of OBI 11g (pt 2)

2011-01-31T14:23:00.004-06:00

In previous part of this series, I stepped through how to configure a Master-Detail report link when using the same analysis criteria to create the master and detail views. However, what if we want to create a totally independent detail view based on different criteria. Can we link the Master view from 1 analysis to the Detail view of another analysis?

Let’s see.

We will the Master view we created previously; so, we need to first create a new, independent Detail view and set our Event channel, the Event channel we used previously was MDS2.

Our event channel on our master view is driven by the “Per Name Qtr” so we need this column on our new detail view so that it can accept and act on the value being passed.

We will use a pie chart view for our detail view. With the view created, we can now set the Event channel through the view’s property window. We are using the slider again for the Quarter to make it easy to see the change.

With our new detail view created and configured to listen on the same channel as our master view we will add the view to our dashboard and check out if it works as we hope.

So, we have now shown that the detail view of a Master-Detail link does not need to come from the same report criteria as the master view.

I did come across an issue when using Master-Detail links and setting up a detail view to listen on the event channel, but configuring the layout of the detail view to not allow it to respond correctly. For example, I added a tabular view to my Master-Detail dashboard but placed the Qtr column in the “section” area of the layout. The report displayed all 4 quarters when I issued the event and my other detail views responded as expected. However when I tried issuing a subsequent event, none of the detail reports would respond. For the tabular view to respond correctly to an event, the column accepting the event must be placed in the “page” area of the view layout.

Here is a table containing specifics about views being used as detail views.

Answers Dashboard Reports: Directory Structure Best Practices

2011-01-31T10:28:00.018-06:00

When developing reports and organizing them on dashboards, it is important to keep the reports named and grouped in a way that is consistent and intuitive, making life easier for yourself and others who may work with them in the future.

1. Use a Consistent Naming Convention

Use names that are descriptive but concise
Include a theme, dashboard topic or subject area in report names, such as AR, Bonus Goals, or Executive Summary to communicate the general content
Using a theme, dashboard topic or subject area consistently at the beginning of report names can make it easier to organize reports, since those with similar names will be listed together within the directory and will appear in alphabetical order

Include a general description of what is in the report, such as Accounts Past Due or Commissions by Region, to make it clear what is contained in the report

Full report names may look something like this: "AR Accounts Past Due" or "Sales Commissions by Region"

Additional detail may be added to the title if there are different variations, such as "AR Accounts Past Due (Top Ten)" or "Sales Commissions by Region Year to Date"

Use abbreviations for generally accepted business terms or terms used within your organization, such as "AR" for "Accounts Receivable" or "YTD" for "Year to Date"

2. Create folders for each dashboard page and/or tab to make the reports easier to find for updating and editing

If using identical reports on more than one dashboard, both can refer to the same report, or a duplicate can be created

If the report will be customized or changed for use on another dashboard, make another copy so the original version does not get changed inadvertently. If there is more than one version, especially in the same directory, make the names descriptive so it is easy to tell which version is which, e.g. "Accounts Past Due by Month" or "Accounts Past Due YTD"

3. Housekeeping

Keep only the most current version and delete previous copies that are no longer used. This makes organization easier, makes it easier to find what is needed, and helps prevent the inadvertent use of an incorrect or outdated version of a report

Working with the Master-Detail Report Feature of OBI 11g (pt. 1)

2011-01-27T10:05:00.012-06:00

One of the new features included in OBI 11g is the Master-Detail linking feature. The Master-Detail linking allows you to establish a relationship between 2 or more views; one view is called the Master and will drive changes in 1 or more views called detail views.

You can think of a Master-Detail relationship in a similar manner that you would when navigating from 1 report to another works, but you do not lose sight of the master view.

So, how do we implement a Master-Detail link?

We start out by defining an analysis that will be used to create the master view. Here we have created a simple analysis and filtered for Year = 2008.

Now we want to configure this analysis so that we can create our master view. We will select the “Per Name Qtr” column as the data driver; so, we open the Column Properties window of this column and move to the Interaction tab.

Here we select the “Send Master-Detail Events” under the Value Primary Interaction. Once you select this option a secondary edit box labeled “Specify channel” will be made available. You can enter any unique identifier in this box. For our example, we have entered “MDS2”.

Unlike some interaction options like Action Links” which is available on both Column Heading and Value, the Master-Detail option is only available on the Value. This has to do with how a Master-Detail link functions. The value of the cell you click on is passed to the detail view(s); thus, a column heading cannot be used.

We now navigate to Results and create the view we want as our Master view. Here we are using a simple tabular view. Notice that our “Per Name Qtr” values do not show the typical hyperlink that you would expect from the time dimension column.

Now we need to create a detailed view that will listen to events from our master view. To do this, we will add an additional graphical view from the same analysis. Our graphical view will look at Revenue by Brand with our Quarter values placed into a view slider.

With our graphical view in edit mode, we need to open the properties window.

One of the properties available to us is the “Listen to Master-Detail Events” checkbox. By checking this box, we can define this view to be a detailed view for the event channel we want. In our case, we will enter the value “MDS2” as our event channel which is the same value we defined on the “Per Name Qtr” column properties which is our data driver on the master view.

We want to validate our Master-Detail report is working as expected. We will do this by placing the compound layout on to a dashboard page. I have edited the compound layout so that my views, master and detail, are side-by-side. When you click on a cell under the “Per Name Qtr”; such as, “2008 Q2” on the tabular master view, the secondary detail view (graph) accepts the Qtr value and the slider automatically moves to the selected value.

We will continue to explore the Master-Detail link by looking at more advanced features of this link in my next entry.

Three dashboard ideas for finance executives, a good read for those thinking of their organization’s strategy for the coming year…

2010-12-27T22:35:00.002-06:00

On this holiday week I decided to dedicate this post to giving some thought to the challenges leaders of finance organizations face and to looking at them in terms of coming up with a short list of indicators that can be leveraged as they reflect on the ways to shape their team’s strategy in a way that maximizes their contribution and impact on the company this soon to come new year.

To keep things simple and avoid taking too much of your holiday time this post focuses on three dashboard ideas that align values and traits of high performance teams and that are built in response to governance and strategic challenges common to many financial organizations.

These dashboards can be easily implemented with Oracle Business Intelligence or other similar reporting tool. My recommendation is to have the first iteration of your dashboard be as simple and concise as possible and build upon it as your needs and processes evolve.

Discipline (IRR)

One of the organizational values that are hardest to measure is the discipline that your overall organization exhibits when making strategic decisions. Measuring the return on capital allocations is a process common in large conglomerates that isn’t always transparent or free of political maneuvering.

Deploying a simple dashboard that tracks the progress of large strategic projects in terms of both schedule and actual versus budgeted costs allows the office of the CFO to educate executives and managers in other areas on financial methodologies such as calculating the Internal Rate of Return (IRR) of a project or estimating the Total Cost of Ownership of a piece of equipment.

Having a dashboard with Key Performance Indicators that measure the rate of return, and actual versus projected performance in terms of cost and schedule will help your organization bring more transparency to the process of deciding which projects will be funded and will help your counterparts in other areas of the business in improving their financial decision making skills.

Efficiency (Time To Close)

One other function that usually resides with the finance organization is the process of closing the books and preparing financial and management reports. This process which is, for the most part, simple and repetitive is implemented and managed very differently from company to company. These differences are often a result of industry specific accounting and differences in management style.

The best way to save your staff late nights and weekends while delivering analysis and reports to your management faster might be as unorthodox and simple as breaking your closing process in the different tasks that compose it and tracking the time each of them takes to complete each month. You can start simply by writing the list on a white board and later on make the transition to a dashboard page that can be shared with all of your team.

By understanding which components of your closing process take the most effort from your team you gain great visibility and thinker with the processes to shorten your cycle time. There have been corporations that implement daily reconciliation of their accounts therefore gaining the ability to detect unusual account activity much faster than if they had to wait for the bank statements to arrive in the mail.

You can use the data in your closing cycle dashboard as a development opportunity for your staff by encouraging them to detect processes that exhibit a lot of variance from month to month and coming up with ways to proactively eliminate the causes of this variance.

Value Creation

With the economy signaling it is finally turning around towards recovery and finance and accounting staff job satisfaction at the lowest level in years it is time to identify top performers and give them opportunities to feel challenged and excel.

It is in the best interest of finance leaders to create opportunities for their staff to get out of the routine of their day to day work and go work in teams within other functional areas of the organization by providing them with internal consulting on financial management and compliance best practices. This aligns very closely with the first dashboard idea, your staff can provide training and consulting to teams working on strategic projects being tracked on the IRR/Projects dashboard.

The value created by these select members of your team can be tracked by the number of projects they are consulting for and the number of training and consulting hours they provide to other areas of your organization. This in turn can be used as a tool in the justification of any proactive compensation activity you might decide to undertake as you try to retain high performing employees in your team.

Conclusion

When planning your finance organization’s strategy for the year that is about to begin you can use the dashboard ideas in this article as tools that will allow you to communicate to your staff and to other functional areas and fellow executives what improvement is being made in the internal processes of your area and; most important, how is it that your team can be engaged as a partner in value creation.

Please send your feedback and comments to Ignacio.delaTorre@biconsultinggroup.com, I’ll be very interested to hear your opinion and any additional ideas that might be useful to others reading this article.

Integrating Relational Databases with Essbase Studio

2010-12-07T22:33:00.001-06:00

Integrating Relational Databases with Essbase Studio

Because relational databases can store several terabytes of data, they offer nearly unlimited scalability. Multidimensional databases, generally smaller than relational databases, offer sophisticated analytic capabilities. By integrating a relational database with an Essbase database, you leverage the scalability of the relational database with the conceptual power of the multidimensional database.

By default, when Essbase Studio creates an Essbase outline, it loads all member levels specified in the metaoutline into a multidimensional database. You can, however, set Essbase Studio to build to a specified member level (Hybrid Analysis) or build only to the dimension level (Advanced Relational Access). Building down to a specified level produces a smaller multidimensional database and a smaller Essbase outline.

A source relational database can be integrated with an Essbase database by using XOLAP (extended online analytic processing). This is a variation on the role of OLAP in business intelligence. Specifically, XOLAP is an Essbase multidimensional database that stores only the outline metadata and retrieves data from a relational database at query time. XOLAP thus integrates with an Essbase database, leveraging the scalability of the relational database with the more sophisticated analytic capabilities of a multidimensional database.

Essbase Studio - Model Development Workflow

Some XOLAP Specifics

XOLAP (extended online analytic processing) is a variation on the role of OLAP in business intelligence. Specifically, XOLAP is an Essbase multidimensional database that stores only the outline metadata and retrieves data from a relational database at query time. XOLAP thus integrates a source relational database with an Essbase database, leveraging the scalability of the relational database with the more sophisticated analytic capabilities of a multidimensional database.

OLAP and XOLAP store the metadata outline and the underlying data in different locations:

- In OLAP, the metadata is located in the Essbase database, and the underlying data is also located in the Essbase database.
- In XOLAP, the metadata is located in the Essbase database while the underlying data remains in your source relational database.

The differences in the locations of the metadata and data are key to understanding how XOLAP can be of benefit because these differences affect the functionality of OLAP and XOLAP.

OLAP lends itself to traditional relational data storage and data analysis. XOLAP lends itself to operations supported in mixed or "hybrid" environments such as Hybrid Analysis and Advanced Relational Access (familiar to users of Essbase and Essbase Studio). Many of the basic concepts of Hybrid Analysis and Advanced Relational Access have been folded into the functionality of XOLAP cubes in Oracle Essbase Studio.

XOLAP Workflow

The workflow of data retrieval in an XOLAP environment is much like that of a non-XOLAP environment:

The model is designated as XOLAP-enabled in Essbase Studio.

The cube is deployed in Essbase Studio; however, no data is loaded at that time.

The Essbase database is queried, using Smart View, Oracle Essbase Visual Explorer, or another reporting tool which can access an Essbase database.

Essbase dynamically generates the required SQL to retrieve the data from the source relational database.

Integrating XOLAP with Traditional OLAP Sources

XOLAP has the following restrictions:

No editing of an XOLAP cube is allowed. If you wish to modify an outline, you must, instead, create a new outline in Oracle Essbase Studio. XOLAP operations will not automatically incorporate any changes in the structures and the contents of the dimension tables after an outline is created.

When derived text measures are used in cube schemas to build an Essbase model, XOLAP is not available for the model.

XOLAP can be used only with Aggregate Storage. The database is automatically duplicate-member enabled.

XOLAP supports dimensions that do not have a corresponding schema-mapping in the catalog; however, in such dimensions, only one member can be a stored member.

Usages Not Supported in XOLAP

XOLAP does not support use of the following:

Flat files

Ragged hierarchies

Alternate hierarchies

Recursive hierarchies

Calendar hierarchies

Filters

Typed measures

User defined members at the leaf level

Multiple relational data sources

Hybrid Analysis

Hybrid Analysis eliminates the need to load and store lower-level members and their data within the Essbase database. This feature gives Essbase the ability to operate with almost no practical limitation on outline size and provides for rapid transfer of data between Essbase databases and relational databases.

Hybrid Analysis integrates a relational database with an Essbase multidimensional database so that applications and reporting tools can retrieve data directly from both databases.

Data Flow for Hybrid Analysis

The initial step in setting up XOLAP or Hybrid Analysis is to define the relational database as a XOLAP or Hybrid Analysis relational source.

You define the XOLAP or Hybrid Analysis relational source in Essbase Studio. Through Essbase Studio, you first specify the relational data source for the OLAP model. The OLAP model is a schema that you create from tables and columns in the relational database. To build the model, Essbase Studio accesses the star schema of the relational database. Using the model, you define hierarchies and tag levels whose members are to be enabled for Hybrid Analysis. You then build the metaoutline, a template containing the structure and rules for creating the Essbase outline, down to the desired Hybrid Analysis level. The information enabling Hybrid Analysis is stored in the OLAP Metadata Catalog, which describes the nature, source, location, and type of data in the Hybrid Analysis relational source.

Next, you perform a member load, which adds dimensions and members to the Essbase outline. At this point XOLAP databases are complete and can queried by a multitude of reporting tolls.

For Hybrid Analysis databases, when the member load is complete, you must run a data load to populate the Essbase database with data.

Applications and reporting tools, such as spreadsheets and Report Writer interfaces, can retrieve data directly from both databases using the dimension and member structure defined in the outline, Essbase determines the location of a member and then retrieves data from either the Essbase database or the Hybrid Analysis relational source if a Hybrid Analysis database and from the relational data source when a XOLAP model is specified.
- If the data resides in the Hybrid Analysis relational source, Essbase retrieves it through SQL commands.
- XOLAP also leverages transactional SQL to access data from the fact table at the time the query is initiated by the end user.

To modify the outline in Hybrid Analysis, you can use Outline Editor in Administration Services to enable or disable dimensions for Hybrid Analysis on an as-needed basis. Changes to metadata in XOLAP require a complete drop and rebuild of the Application and database through Essbase Studio

Comparison of Aggregate and Block Storage

Since XOLAP only supports the Aggregate Storage Kernel, it is pertinent to highlight the differences in ASO and BSO.

Essbase provides an aggregate storage kernel as a persistence mechanism for multidimensional databases. Aggregate storage databases enable dramatic improvements in both database aggregation time and dimensional scalability. The aggregate storage (ASO) kernel is an alternative to the block storage (BSO) kernel. Aggregate storage databases typically address read-only, "rack and stack" applications that have large dimensionality, such as the following applications:

Customer analysis. Data is analyzed from any dimension, and there are potentially millions of customers.

Procurement analysis. Many products are tracked across many vendors.

Logistics analysis. Near real-time updates of product shipments are provided.

Aggregate storage applications, which differ from block storage applications in concept and design, have limitations that do not apply to block storage applications.

Inherent Differences between ASO and BSO

Inherent Differences	Aggregate Storage	Block Storage
Storage Kernel	Architecture that supports rapid aggregation, optimized to support high dimensionality and sparse data	Multiple blocks defined by dense and sparse dimensions and their members, optimized for financial applications
Physical Data Storage	Through the Application Properties window, Tablespaces tab in Administration Services	Through the Database Properties window, Storage tab in Administration Services
Databases supported per application	One	Several (one recommended)

Outline Differences with ASO and BSO

Outline Functionality	Aggregate Storage	Block Storage
Multiple hierarchies enabled, dynamic hierarchy, or stored hierarchy designation	Relevant	Irrelevant
Accounts dimensions and members on dynamic hierarchies	Support with the following exceptions: • No two-pass calculation • No association of attribute dimensions with the dimension tagged Accounts • Additional restrictions for shared members.	Full support
Members on stored hierarchies	Support with the following exceptions: • Support for the ~ (no consolidation) operator (underneath label-only members only) and the + (addition) operator • Cannot have formulas • Restrictions on label only members • No Dynamic Time Series members • Stored hierarchy dimensions cannot have shared members. Stored hierarchies within a multiple hierarchies dimension can have shared members.	Full support
Member storage types	Support with the following exceptions: • Dynamic Calc and Store not relevant • On stored hierarchies, two limitations if a member is label only: o All dimension members at the same level as the member must be label only o The parents of the member must be label only.	Support for all member storage types in all types of dimensions except attribute dimensions

Calculation Differences between ASO and BSO

Calculation Functionality	Aggregate Storage	Block Storage
Database calculation	Aggregation of the database, which can be predefined by defining aggregate views	Calculation script or outline consolidation
Formulas	Allowed with the following restrictions: Must be valid numeric value expressions written in MDX No support for Essbase calculation functions On dynamic hierarchy members, formulas are allowed without further restrictions	Support for Essbase calculation functions
Calculation scripts	Not supported	Supported
Attribute calculations dimension	Support for Sum	Support for Sum, Count, Min, Max, and Average
Calculation order	Member formula calculation order can be defined by the user using the solve order member property	Defined by the user in the outline consolidation order or in a calculation script

Partitioning Differences between ASO and BSO

Partitioning Functionality	Aggregate Storage	Block Storage
Partitioning	Supported with the following restrictions: No Outline Synchronization	Fully supported

Partitioning Functionality

Aggregate Storage

Block Storage

Partitioning

Supported with the following restrictions:

No Outline Synchronization

Fully supported

Data Load Differences between ASO and BSO

Data Load Functionality	Aggregate Storage	Block Storage
Cells loaded through data loads	Only level 0 cells whose values do not depend on formulas in the outline are loaded	Cells at all levels can be loaded (except Dynamic Calc members)
Update of database values	At the end of a data load, if an aggregation exists, the values in the aggregation are recalculated	No automatic update of values. To update data values, you must execute all necessary calculation scripts.
Data load buffers	The loading of multiple data sources into aggregate storage databases is managed through temporary data load buffers.	Not supported
Atomic replacement of the contents of a database	When loading data into an aggregate storage database, you can replace the contents of the database or the contents of all incremental data slices in the database.	Not supported
Data slices	Aggregate storage databases can contain multiple slices of data. Data slices can be merged.	Not supported
Dimension build for shared members	Full support for parent-child build method. Duplicate generation (DUPGEN) build method limited to building alternate hierarchies up to generation 2 (DUPGEN2).	Support for all build methods
Loading data mapped to dates	In a date-time dimension, you can load data into level-0 members using supported date-format strings instead of member names.	Date-time dimension type is not supported.

Query Differences between ASO and BSO

Query Functionality	Aggregate Storage	Block Storage
Report Writer	Supported, except for commands related to sparsity and density of data	Fully supported
Spreadsheet Add-in	Supported, with limited ability to change data (write-back)	Fully supported
API	Supported	Supported
Export	Support with the following restrictions: • Export of level 0 data only (no upper-level export) • No columnar export	Supported
MDX queries	Supported	Supported
Queries on attribute members that are associated with non-level 0 members	Returns values for descendants of the non-level 0 member.	Returns missing for descendants of the non-level 0 member
Queries on attribute members and shared members	A shared member automatically shares the attribute associations of its nonshared member	A shared member does not share the attribute associations of its nonshared member
Query logging	Not Supported	Supported
Query performance	Considerations when querying data from a dimension that has multiple hierarchies.	Hierarchies not relevant

Feature Differences between ASO and BSO

Featues	Aggregate Storage	Block Storage
Aliases	Supported	Supported
Currency Conversion	Not Supported	Supported
Data Mining	Not Supported	Supported
Hybrid Analysis	Support with the following restriction: queries that contain a relational member and an Essbase member with a formula in the same query are not supported.	Supported
Incremental Data Load	Supported	Supported
LROs	Not Supported	Supported
Time Balance Reporting	Support with the following restrictions: • Skip Zeros is not supported • Time dimension must contain at least one stored hierarchy • Shared members must be at level zero	Supported
Triggers	After-update triggers supported	On-update triggers and after-update triggers supported
Unicode	Supported	Supported
Variance Reporting	Not Supported	Supported
Date-time dimension type and linked attribute dimensions	Supported	Not Supported
User ability to change data (write-back)	Transparent partition technique used to enable limited write-back	Fully Supported

Links to Blogs written by BICG on XOLAP

Part 1 of the XOLAP blog

http://oraclebiblog.blogspot.com/2010/02/xolap-virtual-cubes-against-data.html

Part 2 of the XOLAP blog

http://oraclebiblog.blogspot.com/2010/02/xolap-virtual-cubes-against-data_15.html

OBI 11G Fusion Middleware Control (EM)

2010-12-03T13:54:00.007-06:00

Fusion Middleware Control:
The Fusion Middleware Control console is used to monitor and start/stop various OBI components. It is located on the application server at http://localhost:7001/em. Much of the configuration of OBI that used to be done in the NQSConfig.ini files can now be done using this front end console, which in turns updates the NQSConfig.ini file. Things in the NQSConfig.ini file that are set by EM are noted in the file with '# This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager'. The only thing I noticed that needs to be setup manually in the NQSConfig.ini file are:

1. Usage Tracking
2. Dynamic Libraries
3. Query Optimization Flags
4. MDX Member Name Cache Section (SAP BW)
5. More specific General Settings where EM doesn't contain the level of detail required

The Fusion Middleware Control EM console has five tabs to manage the application. From the domain tree, expand Business Intelligence and click coreapplication as shown below.

Overview:
This view shows system status, and allows you to start/stop the system. The other tabs allow you to further configure the application.

Capacity Management:
This view has four tabs:

-Metrics: This gives metrics on request response time, load and query errors. You once might have looked at Usage Tracking for this information.

-Availability: Shows current availability of all components, allowing you to start/stop them. Also includes 'Point of Failure' analysis which shows areas in the infrastructure likely to fail when a hardware/software failure occurs.

-Scalability: This tab lets you enable Verticle Clustering, increasing the amount of instances of OBI on a server.

-Performance: Used for cache management, including settings that would have been set in NQSConfig.ini in 10g. It manages user query response time and max rows returned, settings that were set in InstanceConfig.xml.

Diagnostics:
This view has 2 tabs. One for server log messages, and the other for log file settings. These used to be kept in the RPD and the NQSServer.log.

Security:
This view allows you to enable SSO. Also links to Weblogic Admin Console Security realm to manage Weblogic security.

Deployment:
This section is for managing the deployment of various aspects of OBI 11G.

-Presentation: Contains a few settings for Dashboard and Analysis defaults. These used to be managed on a less global scale through Manage Privileges.

-Repository: This is where the RPD and Presentation Services are managed, and shared between servers for horizontal clustering.

-Scheduler: Schema connection information for the scheduler tables

-Marketing: For Siebel Marketing connection

-Mail: Scheduler mail server connection information

OBIEE 11g, Opening Ports on Oracle Linux - How-To Video

2010-11-27T08:46:00.008-06:00

Working with OBIEE 11g on Oracle Enterprise Linux (OEL), with or without the Unbreakable Kernel, one will need to open up several trusted ports as the firewall is turned on by default. Clearly, the best practice is too keep the firewall in-tact and enabled but for extra ports that are needed to access the OBI server environment. Configuring such access is straightforward. The video below takes an informal look at accomplishing opening ports using the OEL graphical user interface. This approach is great for a development or test environment but it is not recommended for a production environment.

The OBI 11g ports that are essential to open are:

9704 - Presentation Services
9703 - BI Server (Administration tool DSN access)
7001 - WebLogic Server (Administration Console)

This video is part of a series of video blog posts on installing and configuring OBIEE 11g.

OBIEE 11g, Setup Client DSN for Administration Tool - How-To Video

2010-11-26T17:38:00.006-06:00

After installing the OBI 11g client tools each OBI developer or administrator will need to access the Oracle BI RPD using the OBI Administration Tool. The Administration Tool is the GUI that connects to the Oracle BI Server RPD in Online mode (or on the network in offline mode) allowing development and administration functionality of the RPD. The informal video below highlights the process in which to create an ODBC data source connection to the Oracle BI server and test that the connectivity is working.

This video how-to makes the assumption that the client workstation already has the OBI 11g binaries installed on the machine from any of the install options via the OBI11g installer.

This video is part of a series of videos on OBI 11g installation and configuration.

OBIEE 11g Certification Available

2010-11-23T10:16:00.004-06:00

Oracle tends to work hard to ensure that best practices are adhered to when working with their products. They even go as far to offer certifications which acknowledge one's ability to demonstrate product knowledge and acuity for implementing, developing, etc. with Oracle products. The release of OBIEE 11g is no exception, and Oracle has recently released the OBI 11g Certification Exam for those so inclined and ready to be stamped as an OBI 11g guru.

This certification holds some similar exam topics to that of its predecessor certification exam for the 10.1.3.x OBI version. But clearly with OBI 11g there are many new areas on which to be tested. Reading over the OBI 11g Exam certification topics one can get a good feel for what Oracle thinks are "must have" areas within the new application to focus on. Even if not taking the exam, learning these topics should provide an administrator, developer, or super-user with keen insight to working with and implementing OBI 11g.

The OB 11g Oracle certification is currently only available for Oracle partners. However, whether you gearing up for the OBI 11g exam or just needing to get educated on OBI 11g, BI Consulting Group has already released several OBI 11g training courses via their BICG University education center on the following topics:

# OBIEE 11-301: Dashboard/Report Application Development
# OBIEE 11-401: Repository/Metadata App Dev
# OBIEE 11-402: Server Architecture

In conclusion, I have always been a fan of official certifications as they show that one took the initiative to learn the information, was grilled on that information via examination, and knew the material well enough to achieve certification status. To me, this is only one or two steps below taking the time to write a book on the topic in question - guru status.

Do you think Oracle certifications are worthwhile?
How has an Oracle certification the helped you in the past?
Should Oracle offer a public OBI 11g certification?

Easy Download Link

2010-11-22T18:16:00.008-06:00

When setting up a navigation from a report on a dashboard to a target report (not on a dashboard), one of the pieces missing and most asked for is a download link. To provide it, follow these simple steps.

1. Open the report that should contain the download link
2. Add a static text view to the compound view

3. Check the "Contains HTML Markup" checkbox
4. Type:

5. Click the "Display Results" link to verify it is working then click ok
6. Test it

Syntax:
http://virtualxp:9704/analytics/saw.dll? - the path to the BI Server (typically /analytics/saw.dll is all that is needed)
Download - the action to perform (other actions include Go and Navigate, not covered in this post, maybe next time)
&Format=excel - the format of the download
&Extension=.xls - the extension of the file (the report name will be the file name)
path=/users/administrator/Target%20Report - the path to the report

The path to the report can be found in the address bar.
If you are manually typing it in or providing it through a column value, don't forget to replace spaces, quotes, and other special characters with their HTML equivalents.

Other valid formats and extensions:
CSV - &Format=txt&Extension=.csv
PPT - &Format=powerpoint
Excel 2000 - &Format=excel2000&Extension=.xls
HTML - &Format=mht

An OBI 11g Client Tools Only Install Work-Around

2010-11-19T19:55:00.009-06:00

OBI 11g has been out for several months now and it is getting great reviews - As it should! It is a clean and powerful upgrade from its 10g predecessor. With that being said, many things have changed, not only from a graphical end-user perspective but from a foundation and architecture one as well. This post is specifically discussing the later. Even more specifically, this post touches on what is probably the loudest complaint of System Integrators thus far which is that OBI 11g does not have a client tools install like OBI 10g did. What does this mean OBI 11g integrators and developers? It means that basically anytime one needs to leverage the Administration Tool, Catalog Manager, etc. on a client workstation (away from the OBI Server, i.e.: client/server interaction) the workstation must be loaded with all binaries and configurations as if it was to be the OBI 11g server itself. Oracle's documentation goes on to say that after a full install (Simple or Enterprise) on a client workstation (Windows only, of course) that one must shutdown and disable all server related Windows Services. Clearly this is cumbersome to say the least just to get client tools loaded.

To workaround this one can conduct the OBI 11g installation on a certified client OS using the Software Only Install option. This option requires that a Fusion Middleware Home already be established. So, WebLogic 10.3.3 must be installed on the client machine prior to conducting the OBI 11g Software Only installation. You may download from here or from the Oracle e-delivery site.

If you attempt to conduct the software only install without prior installation of the WebLogic Server you will be confronted with the following error messages (INST-07407, INST-07247):

Once the WebLogic Server has been installed on the client OS, install OBI 11g using the Software Only option. The installation should complete without incident as only the binaries are installed on the client machine. Once the install completes, navigate to the administration tool executable now under the path <fmw_home>\Oracle_BI1\bifoundation\server\bin\admintool.exe.

Please note that the administration tool and all OBIEE 11g client tools are certified to run on a 32-bit OS only. Be warned that the client tools will install onto a 64-bit machine but Oracle currently will not support that version OS if something goes wrong. Please see this link for the current OBI 11g certification matrix, here (OBI 11g Release 1) or here for the full Full Fusion Middleware Certification Matrices. Take note that this is an OBI 11g Client Tools Only Install Work-Around and it is not-officially supported by Oracle But, it is much more efficient than the extra steps per the Oracle documentation.

References
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=1186342.1

Using DAC & Informatica to send proactive email alerts for OLTP or .CSV config file data issues that cause ORA-01452: cannot CREATE UNIQUE INDEX

2010-11-19T19:21:00.012-06:00

It is possible that a DAC ETL load for an OBI Apps DW implementation can fail due to source system data or improperly maintained configuration files. As much as we hope that the business users and or the OLTP system itself remain error-free, this is not always the case especially when you take into account human errors (manual updates to key .csv config files) or OLTP data integrity issues (unstable source system, multiple external feeds to source system, improperly coded or outdated OLTP processes). Normally, if a DAC ETL load is scheduled to be nightly, the earliest notice will be sent by the DAC upon success/failure and no corrective action can be taken until the logs are traced and the business is notified to correct the OLTP or .csv file error(s) and the load most often needs to be restarted and the business will have lost that time.

To mitigate this risk, The DAC and Informatica can also be used to create a proactive email alert notification to allow the business or OLTP support team to correct any issues that may break the nightly ETL load. One common example of an avoidable OBI Apps ETL error is the notorious ORA-01452: cannot CREATE UNIQUE INDEX error. The DAC will issue a CREATE UNIQUE INDEX command to eliminate redundancies and ensure accuracies of the source system.

Step 1: Find the INTEGRATION_D ID that caused the UNIQUE INDEX ERROR via the following method replacing the W_XXX_D with the appropriate Target table from the error log

SELECT INTEGRATION_ID, DATASOURCE_NUM_ID, EFFECTIVE_FROM_DT, COUNT(*) FROM W_XXXX_D
GROUP BY INTEGRATION_ID, DATASOURCE_NUM_ID, EFFECTIVE_FROM_DT HAVING COUNT(*) > 1;

Step 2: Find the appropriate Source Qualifier extract SQL that is used for the mapping/mapplet that populates the target table (make sure to check for any SQ overrides at the session level). For example, the example below uses the SQ for the PeopleSoft mapplet C_mplt_BC_EmployeeDimension_Biography1. Use the same SQL concept as STEP1 to identify the exact records that are causing the downstream UNIQUE INDEX error by concatenating the fields that together may be used to create the INTEGRATION_ID downstream.

Using the above method should allow you to pinpoint the .CSV or the OLTP data anomaly that is causing the downstream Index Constraint.

Step 3: Create a simple Informatica Mapping/Session that uses the above formulated query to query the datasource (.CSV or a set of OLTP tables) depending on the source of the data issue. As a target, its best to create a .csv file in the same SrcFiles directory that is already defined on the INFA/DAC server. The mappings should populate the .csv file.

Step 4: Create a Session based on mapping in Step 3 that populates the .csv target with the problematic rows and conditionally sends an email to the appropriate business users or OLTP system admins with an attachment of the problem records.

Step 5: Create the associated DAC components (tables, tasks, execution plan) etc and schedule this as a separate execution plan that is run at the same frequency of the actual Load. Preferably the alert should go to the correct business users who maintain the config file or the OLTP system contacts who can correct the source system issue detected.

The above approach can be useful for OBI Apps implementations that have issues related to source system data and/or config file errors. It will proactively notify the business to take corrective action on data anomalies that can cause Execution plan failures due to ORA UNIQUE INDEX error.

Cool New Feature in OBIEE 11g: Master-Detail Linking

2010-11-10T11:32:00.019-06:00

A few weeks ago, I was fortunate enough to attend an OBIEE 11g workshop given by Oracle. There were so many cool, new features to be found in this new release of OBI, but one of them that seemed really slick was called Master-Detail Linking.

In a nutshell, you can create an analysis (report) with multiple different views and have the views dynamically change as you click on values from another view. Here’s an example of how to accomplish this:

1. Create a new Analysis and add some columns of interest:

2. View the results and add a pivot table view. Edit the pivot table by moving Region to Columns, and add a Grand Total to the Rows:

3. Now, my compound layout looks like this:

4. Go to the criteria tab, and select the properties of one of your dimensions. I’ll choose Year:

5. Go to the Interaction tab, and under Primary Interaction, select ‘Send Master-Detail Events’. Under Specify Channel, you must give it a name. It doesn’t matter what… just remember it for later. I like to use MD_ (for Master Detail) and then the column I’m using.

6. Go to the results tab, and add a vertical bar graph. Edit the graph by moving Year to Sections and check the ‘Display as Slider’ checkbox. To make my graph more interesting and easier to read, I’m going to move Line of Business down to ‘Vary by Color’.

7. Click on the graph properties icon:

8. On the General tab, check the box labeled ‘Listen to Master-Detail Events’. Be sure type in the Event Channel that you created earlier.

9. Back at the Compound Layout, click the preview icon:

10. Click on the various years displayed in your pivot table. Your graph will dynamically refresh according to the year your clicked on the pivot table and a neat, animated fashion! Very cool!

11. Things to keep in mind: A map view can be the Master, but never the Detail. All other views can be either the Master or the Detail.

Dashboard Design Tips

2010-11-09T18:54:00.012-06:00

When designing a set of dashboards, I like to include an “at-a-glance” or summary page as the first dashboard a user sees when they log on each day. This page should include a condensed version of the user’s key performance indicators as well as any generated alerts. Users should then have the option of drilling down or to additional detailed reporting. In order to encourage usage of the dashboards, embed your dashboards as a link within the corporate portal so users have one click access to the information

Consider the page placement of your key performing metrics based on web page eye movement. The diagram below shows how the human eye views a web page, based on level of priority. Place the most important elements for viewing in the upper left hand corner and the least important in the lower right corner to mimic the way the eye scans a page.

Some additional design guidelines are:

Ideally, the dashboard should fit on one page. Try to avoid any scrolling from left to right.

The best screen size for the dashboard window is 1024 x 768.

The ability to drill is critical to the success of your dashboards. Users need to know that the underlying data is going to be available to them. Some projects I’ve worked on have been very successful when allowing the users to drill not only to the detailed data but also back to the source transactional systems.

Introduce competition by using Top 10 reports, conditional formatting, etc.

Avoid decorative dashboard elements cluttering the screen like dials, gauges, excessive colors and images. Your dashboard can still be visually interesting while following standards.

Speaking of standards, it’s important that dashboard standards and best practices be defined early in the design process. This ensures consistency among various groups within your organization. I recently worked on a project where OBIEE had been in place for over a year, being used by a few departments. Additional teams were being brought on board and new development had already started. There were no design guidelines in place and the new dashboards were completely different among each of the teams. One of our exercises was to look at what was in production today and identify standards to be used in future development. This also caused a little rework on the production dashboards to meet the requirements of all teams, resulting in a consistent approach for the organization as a whole.

As a little bonus, I’ve included a few tips from Dashboard expert Stephen Few:

Characteristics of A Well-Designed Dashboard

Exceptionally Well Organized

Condensed, Primarily in the form or Summaries and Exceptions

Specific to and Customized for the Dashboard’s Audience and Objectives

Displayed Using Concise and Other Small Media that Communicates the Data and Its Messages in the Clearest and Most Direct Way Possible

Common Mistakes in Dashboard Design

Exceeding the Boundaries of a Single Screen

Supplying Inadequate Context for the Data

Displaying Excessive Detail or Precision

Arranging the Data Poorly

Highlighting Important Data Ineffectively or Not at All

Cluttering the Display with Useless Decoration

Misusing or Overusing Color

Designing an Unattractive Visual Display

Starting and Stopping BIEE from command line

2010-11-05T16:40:00.013-05:00

BIEE11g uses now Weblogic as application server. This means we need to know some basic commands such as how to start and stop Weblogic server (WLS). In addition to this from the previous post we know that BIEE11g does not only require WLS but also other additional processes. All this plus the need to interact with these components by means of a script in a development / test environment led us to the conclusion that we need to know how we can start or stop BIEE (or even its processes) from a command line. So let us get in this:

Starting BIEE:

First, read and understand the BIEE11g architecture. This is discussed in a previous post (it can be found here).
So now, you know we need to start / stop basically the Java and System components respectively:
a. The Java Components:
i. Admin Server
ii. Managed Server
b. System Components (OPMN Processes)
i. BI Server
ii. BI Presentation Services
iii. BI Java Host
iv. BI Cluster Controller
v. BI Scheduler

Fortunately we do not need to create scripts for each of the components from scratch since during BIEE11g installation some of them were already created for us. We will make use of some of them: ( Note: In our examples our Fusion Middleware home is C:\fusionMW )

1. Start NodeManager:
This process starts automatically in Windows as a service (everytime the OS is restarted). Additional reference for this component can be found here

2. Start Admin Server:
Command: startWeblogic.cmd
Path: C:\fusionMW\user_projects\domains\bifoundation_domain\bin

You will be prompted for the admin user and password. You either enter these values everytime you start/stop the services or alternatively you can set these values in boot.properties file.

The WLS will have started when you see these messages:

OPTIONAL:
File Name: boot.properties

Content:
username=enter your user name here
password=enter your password here

For WLS place this file in:
C:\fusionMW\user_projects\domains\bifoundation_domain\servers\AdminServer\security

For Managed Server place this file in:
C:\fusionMW\user_projects\domains\bifoundation_domain\servers\bi_server1\security

The next time you restart WLS the password in the boot.properties will be encrypted.

3. Start Managed Server:
Instance: bi_server1
URL: http://hostname:7001
Command: startManagedWeblogic.cmd bi_server1 http://hostname:7001
Path: C:\fusionMW\user_projects\domains\bifoundation_domain\bin

The Managed Server will be up when you see the following lines:

4. Start All OPMN processes

Finally, you need to start all OPMN processes: BI Server, BI Presentation Services, BI Java Host, BI Cluster Controller, BI Scheduler.

Start OPMN services:
Command: opmnctl.cmd startall
Path: C:\fusionMW\instances\instance1\bin

Monitor Progress:
To monitor the progress of this task simply put the following command in a similar window (from the same directory):
Command: opmnctl.cmd status -l
Path: C:\fusionMW\instances\instance1\bin

Once all processes are "Alive" then you will get this screen:

Stopping BIEE:

This process is almost the same but in reverse order. To stop all BIEE services you will have to perform the following commands in the respective order in the same directories where each of them started

1. Stop OPMN processes:
> opmnctl stopall

2. Stop Managed Server (bi_server1)
> stopManagedWeblogic.cmd bi_server1 t3://hostname:7001 username password

3. Stop WLS (Admin Server)
> stopWebLogic.sh username password t3://host.domain:7001

This is all you need to know about it to get started. If you already installed BIEE11g then you should be familiar with the Start/Stop BIEE services script that the installer produces. Well, that script uses a series of ANT commands to produce the same effect we have here described. In a future post I will explain about ANT and take such script as example.

Until next time

OBIEE 11g Architecture

2010-10-15T14:46:00.027-05:00

In this article we will briefly explain the new OBIEE 11g architecture and its components. At first glance it seems complex to understand all the new pieces in OBIEE 11g, but let us take a look at the following picture:

We can identify two main component types the OBIEE 11g: The A) Java Components and the B) System Components.

A) Java Components: These are basically J2EE applications running in Weblogic Server. There are two subcomponents here: 1. Admin Server and 2. Managed Server. Each of them running in a dedicated Java virtual machine (JVM).

Admin Server: This is nothing more than a set of J2EE applications that will help us Administering the system:

Admin Console (WLS) — An adminstrative user interface that provides advanced management for Weblogic, JEE components, and security
Fusion Middleware Control — An administrative user interface that is used to manage the BI domain.
JMX MBeans — Java components that provide programmatic access for managing a BI domain.

Managed Server: These are J2EE applications which will help the functioning of the BIEE System (highlighted in RED):

BI Plugin — It routes HTTP and SOAP requests to BI Presentation Services.
BI Security — It enables the integration of BIEE Server and Fusion Middleware security platform through webservices calls.
BI Action Services — It provides the dedicated Web services that are required by the Action Framework (a nice introduction can be found here, another example of use is here and here) and that enable an administrator to manually configure which Web service directories can be browsed by users when they create actions.
Webservices SOA — This component provides Web services for objects in the BIEE Presentation Catalog, to invoke analysis, agents, and conditions.
BI Office — This component provides the integration between Oracle Business Intelligence and Microsoft Office products.

and finally

Two particular applications: BI Publisher (Reporting System) and RTD (technology platform which enable the analysis of data and provides insight by using data mining algorithms and techniques in real time).

B) System Components: These are non-J2EE components, such as processes and services written in C++ and java.

BI Server — It provides capabilities to query and access data as well as services for accessing and managing the RPD file (BIEE Metadata).
BI Presentation Services — It provides the framework and interface for the presentation of business intelligence data to Web clients. It maintains an Oracle BI Presentation Catalog service on the file system for the customization of this presentation framework.
BI Scheduler — Provides extensible scheduling for analyses to be delivered to users at specified times. (BI Publisher has its own scheduler.)
BI JavaHost — It enables BI Presentation Services to support various components: Java tasks for BI Scheduler, BI Publisher, and Graph generation.
BI Cluster Controller — It distributes requests to the BI Server, ensuring requests are evenly load-balanced across all BI Server process instances in the BI domain.

In addition, the BI Domain requires a set of configuration files, the repository (RPD) file, the Presentation Catalog files, etc. Likewise, BIEE System uses a set of metadata tables stored in a relational database like Oracle.

How to Start BIEE System: There are multiple ways to start the BIEE System, but it must follow certain order. Based on the description given above we need to :

1. Start NodeManager : By default in Windows it runs when the OS starts (Reference about NodeManager can be found here)
2. Start Admin Server : Initiate Weblogic Server.
3. Start Managed Server : Initiate the main applications. Based on the picture above, these are highlighted in Red : BI Plugin, BI Security, BI Action Service, BI Webservices SOA. If you are planning to use BI Publisher or RTD then it must also be started.
4. Start BIEE Services : These are the main BIEE processes which are controlled by OPMN: BI Server, BI Presentation Server, BI JavaHost. If you are planning to use BI Scheduler then also start it. Or if your instance is a clustered environment then also start the BI Cluster Controller.

In our next post, we will describe how to actually perform this operation and we will describe how to troubleshoot issues by looking at the log files.

References:
1. Introduction to BIEE Administration (here).
2. Rittman Mead posts on Action Services (here).

Report Return HTML

2010-10-13T17:36:00.001-05:00

General:
When you navigate down to a third level report from a top level Report in OBIEE, when you hit the default return value on the dashboard it returns you to the first level report, and not the report that you navigated from. Also when you use the Go URL Command to integrate a Report into a web portal it will not have a return value to return you to a high level report, when you navigate from a top level report.

To solve the above issues you can incorporate HTML in a Static View on the request.

Purpose:
The purpose of this document is to show how to use the Static View on the OBIEE Request to add a return link for the report.

Steps:
1. Create the OBIEE Request
2. Add a Static View to the Request
3. Add the onclick HTML to the Static View
4. Add the Static View to the Request Compound View
5. Left justify the Static View
6. Save the request

HTML for a Return Link:

Return

Summary:
This document shows how to create a return link for the report that has been navigated to from a higher level report in OBIEE. This same techniques in also used to create a return link for a Report that has been added to a web portal, and has a navigation to a lower level report.

Testing Project Business Requirements

2010-10-13T17:33:00.003-05:00

Discussion:
We usually think of testing during the Project Development Cycle. The first instance of testing is to do some unit testing during development to insure that the system does not break when a user logons on to the system and accesses screens to enter data. A second instance of testing is usually during User Acceptance Testing (UAT) to let the user logon to the system and test the functionally of the system. If testing is developed in the environment focused on testing the actual testing begin when the Project is first undertaken. One of the first documents in the testing cycle is to develop a Test Strategy Document. This document is normally developed by the Testing Team with the help of the other Project Team Members especially the business users. Once the document is completed it should be reviewed by all the team members, and agreed to by the Project Team. The next document is the detail test plan. This is normally developed during the Requirements and Design Phases of the Project Life Cycle, and details the detailed testing needs and requirements. The next documents that are completed during the Project Life Cycle are the Test Cases. The Test Cases detail are the scenarios that the users will encounter when using the system, and provides the user the capability to respond to whether the system processes meet the expected conditions.

Requirement Testing:
One of the most often missed steps during the Project Testing Cycle is to test the System Requirements. Once the Requirements Document is completed, the next step in the process it to test the documented requirement to insure that the meet the Business Users needs. This is usually a very difficult process because many Business Users may understand part of their business process, but do not have a detailed understanding of all phases of their business processes, and the data that supports their business processes. One of the main functions of the Information Technology Department is to work with the Business Users to help them understand the business processes and the data that supports their business processes. Usually Requirements Gathering Workshops are one of the best ways to develop the Project Requirements and help educate the Business Users about their business processes the that data that supports their business processes.

The Requirements Testing Phase is an excellent to give all members the opportunity gain an understanding of the Business Requirements, and to ask detailed questions about all phases of the Project Business Requirements. This is very critical because during the Project Design Stage the design should address all the Project Business Requirements. I have seen projects where the Project Design is not compatible with the Project Requirements Documents. This can only lead to project failure.

Some of the main questions that need to be asked during the Business Intelligence Requirements Testing Phase are:

1.) Do the metrics enable the business user to measure the efficiency and effectiveness of his business processes?
2.) Do the Dimensions provide the attributes that enable business user to provide the ways to view the metrics to measure their business processes?
3.) Is the proper security established to access to the Reports and Dashboards by the appropriate business users?
4.) Is the proper data security established to permit the business users access their data and not see other users secure data?
5.) Can we look at Charts and Graphs to view tends in data?
6.) Can we easily navigate through the BI Application without having to minimize the keystrokes and scrolling?
7.) Does the data provide the information to measure progress with the Business Users Goals and Objectives?
8.) Do the information, reports, and dashboards provide the needed information to help the business users better manage their business processes?
9.) Do the information, Reports and dashboards show out of bound conditions so that the business user can respond in a timely manner to correct the situation?
10.) Can the business users use the data to further analyze out of bound conditions or look at the data from a different perspective?

Summary:
Testing the Business Requirements is an important step of the Project Life Cycle. If the Business Requirements are thoroughly tested by all team members, it will lead to a more successful and rewarding development effort for the Project Team. By Testing the requirements early in the Project Life Cycle and getting agreement that they are correct, all the remaining Project Phases will be easier and lead to a better developed system that will meet the Business Users needs and Requirements.

OBIEE Go URL Command to access Reports and Dashboards from External Portals or Applications

2010-10-05T11:30:00.003-05:00

General:
When using OBIEE at a Customer’s sites, as part of the requirement it may be necessary to integrate OBIEE Reports and Dashboards within other applications or portals. In one instance at a client site, the requirement was to integrate the OBIEE Repots, Dashboards, and Alerts into a Web Portal. The Portal was the main way for Line Manager to view their OBIEE Reports and Dashboards. Also another requirement was to distribute alerts from OBIEE Delivers to the Line Mangers email and let the Line Managers navigate to more detailed reports from the report delivered to their email. The alerts were based on a condition occurring in situation where the Line Manager needs to be notified. OBIEE Delivers enable an alert to be sent to specific uses based on a conditional report; however when the alert was received by the Line Manager he was unable to drill down to the more detailed reports. The way that the requirements were met was to use OBIEE Presentation Services Go URL Command.

Reference:
Chapter 11 of Oracle Business Intelligence Presentation Services Administration Guide, Version 10.1.3.2

Purpose:
The purpose of this document is to discuss the OBIEE Go URL Command and show how it can be used to incorporate Oracle Business Intelligence Results into External Portals using the Go URL Command. Also it will discuss how to permit navigation down to detailed reports from a report delivered to a Mangers’ email.

Snytax:
The starting syntax of the GO URL Command is:

http://server/analytics/saw.dll?GO

There are many other parameters and option that can be used in the GLO URL to access Reports and Dashboards. The following topics will show some of the parameters and filters that can be used with the GO URL Command. The parameters and filters are discussed in more detail in the referenced document.

Syntax for use in Portals:
Below is an example of the Go URL used in a web portal to access an OBIEE Report. The sections of the Go URL command are discussed below the example command.

https://server/analytics/saw.dll?GO&Path=/Shared/Expense/Expense%20Cost/Direct%20Reports%20Requestor%20Summary&ViewName=compoundView!1&Options=drf

https://server/analytics/saw.dll?GO – The standard GO URL syntax

&Path=/Shared/Expense/Expense%20Cost/Direct%20Reports%20Requestor%20Summary – The path to the OBIEE Presentation Server Report. Note that %20 is used in the statement where spaces are in the Presentation Server Path to the Report.

&ViewName=compoundView!1 – The view that is requested in the Go URL to be displayed in the portal. The View Name is found in the XML for the Report in the Advance Tab in OBIEE Answers. If there are different compound views needed for different reasons, multiple compound views can be made for the request. Only the one that is needed for the portal would be referenced in the Go URL Statement. Instead of using a compound view, any view in the request can be referenced in the Go URL command.

&Options=drf – A link option in the Go URL command that permits the following for the report in the portal: d – download to Excel, r – refresh results, f – printer friendly

Other optional parameters can be used in the Go URL command. Refer to the referenced document for a discussion of the optional parameters that can be used for the Go URL command.

Syntax for use in an email Alert Report:
Below is an example of a Go URL command that was used in an Alert to permit enable navigation to a lower level report. A discussion of each section of the Go URL Command will be discussed below the example.

'href=http://server/analytics/saw.dll?Go&Path=/Shared/Project%20Central/Expense%20Report%20Listing%20Person&Action=Navigate&P0=1&P1=eq&P2=Requestor."Requestor%20Name"&P3='|| Replace(Requestor."Requestor Name",' ','%20') || ' > '||Requestor."Requestor Name"||'

This Go URL command is placed in the column formula section of the column on the report that you want to navigate from. You also have to edit the column and make it a HTML format. The above href command must be enclosed in the HTML anchor syntax.

href=http://server/analytics/saw.dll?Go - The href statement for the Go URL command

&Path=/Shared/Project%20Central/Expense%20Report%20Listing%20Person – The path to the report in the OBIEE Presentation Server. Note the use of the %20 for the spaces in the OBIEE Presentation Server catalog path to the report.

&Action=Navigate – the action filter to tell that tell the Go URL Command that you want to navigate

&P0=1 – the number of columns that you want to filter

&P1=eq – the operation operator. The other potential operation operators are shown in the referenced document

&P2=Requestor."Requestor%20Name" – the requestor column in the report that you want to navigate to. This must be a filter with an “ is prompted” value.

|| - concatenation symbol

&P3='|| Replace(Requestor."Requestor Name",' ','%20') || ' – the name of the column on the report that you need to filter from. The Replace statement is needed because the Requestor Name has spaces in it in the database.

' > '||Requestor."Requestor Name"||' – the name of the column that should be displayed in the column on the Report that you want to navigate from.

Once you build the Go URL command you can test it in the Web Browser to check the syntax and insure that the Report will drill down to the detail report from the displayed report.

Other Options and Filters:
The referenced document discusses the optional parameters and filters that can be used the Go URL command. The Go URL command can access Dashboards as well as OBIEE Reports. Refer to the attached document for more information on the Go URL Command.

Summary:
The Go URL command is a good way to incorporate OBIEE Reports and Dashboards in into External Portals or other Applications. It can also be used to navigate down to detailed reports from Alerts sent to Managers by email. Going forward I anticipate that many other companies will want to incorporate OBIEE Reports and Dashboards into their existing Application instead of having to navigate and login to the OBIEE Application. Using the Go URL command is not difficult but you need to become familiar with the syntax, parameters, and filters to effectively use the command to meet your client or your requirements.

OBI Apps Informatica Performance Tuning – Optimizing SIL Read Throughputs – Teradata Response Buffer Size Optimization

2010-09-24T01:18:00.001-05:00

Teradata Response Buffer Size

For the reader connection to source data from a Teradata database, ODBC connection is used out of the box. There is another option using piped FastExport utility connection with which there has been mixed success and will be discussed in a later blog post. OBDC can be tuned to achieve better performance in read throughput by changing the response buffer size. MaxRespSize is used to change the value in ODBC.ini. The default is 8192. The max value that can be specified is 1048576. From experience at a client site, simply specifying the max value did not help in improving the throughput. Increase the value systematically to determine the sweet spot for a network. The graph bellow illustrates throughputs achieved at different MaxRespSize values

Driver=/usr/odbc/drivers/tdata.so

Description=Teradata Linux 64-bit

DBCName=DWTESTcop1.XXXX.COM

SessionMode=Teradata

StCheckLevel=0

LastUser=

Username=

Password=

Database=

DefaultDatabase=

OutPutAsResultSet=Yes

MaxRespSize=8192

OBI Apps Informatica Performance Tuning - Optimizing SDE Read Throughputs - Oracle DB Network Optimization

2010-09-03T21:10:00.001-05:00

Oracle DB Network Optimization

After exhausting query performance with viable indices, consider modifying Session Data Unit (SDU) and Transport Data Unit (TDU) parameters to increase session throughput. The default value of SDU and TDU is 2048 and the maximum is 32767. These can be set as global parameters in sqlnet.ora or for a particular descriptor in tnsnames.ora. They need to be set on both server and client. Consider setting these parameters in tnsnames.ora so that it affects only a particular connection descriptor that is used for Oracle DB server to Informatica server communication. The SDU and TDU parameters can be set higher depending on the network and memory. Get help from your Infrastructure team in determining the MSS (Maximum Segment Size) of the network. Ideally they should be in the multiple of MSS. In addition, the SDU should not be greater than TDU. Ideally, they should be the same. For slower networks, consider a lower value.

Client Side Configuration (Informatica Server):

tnsnames.ora:

CRMOLTP =

(DESCRIPTION =

(SDU=32768)

(TDU=32768)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = crmoltp.xyzcompany.com)(PORT = 1521))

Server Side Configuration:

listener.ora:

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SDU=32768)

(TDU=32768)

(GLOBAL_DBNAME = CRMOLTP)

(ORACLE_HOME = /u01crmoltp/oracle/product/10.2.0)

(SID_NAME = CRMOLTP)

)

tnsnames.ora:

CRMOLTP =

(SDU=32768)

(TDU=32768)

(DESCRIPTION =

(ADDRESS =

(PROTOCOL= TCP)

(Host= CRMOLTP)

(Port= 1521))

(CONNECT_DATA =

(SID = CRMOLTP)

)

Impressions of the Oracle BI 11g Launch in New York

2010-07-23T16:23:00.018-05:00

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

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

BICG at Florida OAUG Summer Meeting

2010-07-19T08:53:00.004-05:00

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

2010-07-07T13:06:00.006-05:00

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">
 
 Brand Analysis
 
 </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 element is "class" and its value is 'NavLinkCaption".

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

The 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 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 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

2010-06-18T12:56:00.006-05:00

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

2010-06-04T10:25:00.010-05:00

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

2010-06-03T20:45:00.003-05:00

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

2010-06-02T09:58:00.004-05:00

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

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

OBIEE Date Expressions Reference

2010-05-26T10:32:00.002-05:00

During a recent project I had several requirements that involved converting dates to the first or last day of the current month, past month, future month etc. Thanks to that project I’ve become proficient at Date manipulations and thought I would share some of the expressions I had to use on the project and how they worked. For people who have not worked a lot with OBIEE Date manipulation this may come in handy since OBIEE does not offer built in functions like Oracle which has the LAST_DAY function to return the last day of a given month.

Any date manipulation within OBIEE will utilize the TIMESTAMPADD function. For the examples shown in the table below I used CURRENT_DATE as the starting point for all dates. You can replace CURRENT_DATE with whatever valid Date type you need as a starting point or can CAST a CHAR value to a Date type if needed.

The table below describes what is being calculated, the expression to perform the calculation, and then an explanation that explains how the expression arrives at the desired date value.

Date Calculation	OBIEE Expression	Explanation
First Day of the Previous Year	TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.
First Day of the Current Year	TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)	This calculation returns the first day of the year by deducting one less than the total number of days in the year.
First Day of the Next Year	TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.
First Day of the Previous Month	TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.
First Day of the Current Month	TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)	This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.
First Day of the Next Month	TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.
First Day of Current Quarter	TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)	This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.
Last Day of the Previous Month	TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.
Last Day of Current Month	TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.
Last Day of the Next Month	TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
Last Day of Previous Year	TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.
Last Day of Current Year	TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.
Last Day of the Next Year	TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.
Last Day of Current Quarter	TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.
Number of days between First Day of Year and Last Day of Current Month	TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))	For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

BI Publisher - Webservices update to10g

2010-04-19T21:45:00.004-05:00

BI Publisher 10g provides an update to its webservices from previous releases with the patch 9546699. All you have to do is (with OC4J and BI Publisher 10g running) to open the following URL:

http://mybipserver:9704/xmlpserver/services/PublicReportService_v11?wsdl

Where: mybipserver is the machine's name where OC4J is installed and where BI Publisher has been deployed. Save this file locally.

Once you get the WSDL file you can immediately test the services using SOAP UI (a webservices tester).

Start loading BI Publisher's WSDL into SoapUI and create a new project:

So for example, we can find out which Security mode is being used in a BIP installation. Simply invoke the service "getSecurityModel". This service does not require any parameter, therefore just click on the green button "play" .

This returns the security model employed in BI Publisher's environment: "BI_SERVER", which means the security model used is based and controlled by BIEE Server.

The next example will employ BI Publisher's web services which require parameter values: "login" and "getFolderContentsInSession". Invoke "login" and enter "Administrator" as user and password:

This returns the session string, known as BIP's session token:

Next, invoke the "getFolderContentsInSession" service and enter the string just obtained in previous step plus the folder path to find out its content: "/HR Manager".

Finally, click on Run (the green arrow button on the upper left hand side of the window) to obtain a list for reports and folders:

With this you now get a flavor of all the things you can do using BI Publisher's webservices. Of course you can start developing your web application using JDeveloper and BIP WSDL, but that will be the topic for new post soon.

Note: Based on BI Publisher's PM comments I have updated our post's original title.

BI Publisher 10.1.3.4.1 (build 1145)- latest patch - 9546699 (April 1st 2010)

2010-04-08T23:15:00.044-05:00

More than a week ago Oracle BI Publisher PM team released a new patch 9546699 (on March 31st, but published in Metalink April 1st), which brings many bug fixes and product enhancements.

There is a long list of updates you can read in the readme file that comes in the patch. But for those of you interested in getting this patch applied in your environment I have compiled a step by step screenshot sequence (for Windows environment) which I hope can help you in this process. Here is how it goes:

I) Upgrade of BI Publisher Server

1. First, take a backup copy of $ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/xmlp-server-config.xml , where as usual $ORACLE_HOME refers to the location of your base directory for BI Publisher server installation.

2. Second, take a back up of BI Publisher's repository. Default location usually is /xmlp/XMLP:

3. In case BI Publisher's Scheduling feature has been enabled and configured ==> Also take a back up of such schema in the database used. Although as the upgrade will be performed on the application server side then there is no really need to perform this step, it is just as precaution.

4. You can now use Enterprise Manager for the rest of the steps : uninstall of the current version and the installation of the new version. Notice the port number is 9704 since the original installation of BI Publisher was part of BIEE installation and that is the default port number. Thus, invoke Enterprise Manager as follows : http://server_name:9704/em

Normally, the password is "oc4jadmin" but it could be something different. This password is defined during the first install of BI Publisher, so you need to get this value from whoever performed the first installation. Otherwise, if the password is not known then use google to obtain a process on how to reset the OC4J administrator's password. Here is a link from Mike Lehmann's : Reset OC4J admin password

4.1 Shut down BI Publisher.
4.2 Undeploy the BI Publisher application ("xmlpserver") from your J2EE application server. Here a screenshot from Enterprise Manager for OC4J:
4.3 Deploy the 10.1.3.4.1 (patch 9546699 ) xmlpserver.ear or xmlpserver.war to your application server. If you are using OC4J then here is the sequence of screenshots:

- Click on "Deploy"
- Select the EAR or WAR file to deploy:
-Enter the deploy application attributes:

In the third step simply accept defaults and Apply and Deploy. You get finally this:

4.4 Stop BI Publisher application (repeat as in step 4.1). This step is required as you will restore the backup files taken in step 1. and 2.

5. Copy the saved backup copy of the xmlp-server-config.xml file from step 1 to the newly created BI Publisher: $ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/ directory

6. Copy the repository (the complete XMLP directory and subdirectories from the back up folder, see step 2.) into $ORACLE_HOME/xmlp/

7. If your environment is 10.1.3.3.2 (03-April-2009, Patch 8398277) or earlier then perform the following two steps, Otherwise continue with step 8.

Copy this file: ssodefaults.xml and place it in the following directory: $ORACLE_HOME/xmlp/XMLP/Admin/Security. Default values for properties can be updated depending on your configuration if SSO is implemented.

Copy database-config.xml to the following directory: \XMLP\Admin\Scheduler. This file stores information about the database which will be used to store BI Publisher Scheduler's metadata tables.

8. Restart xmlpserver application or Application Server

9. Finally you can test your new patch by going to: http://server_name:9704/xmlpserver

Notice, down on the right hand side the BI Publisher's version 10.1.3.4.1 (build 1145). To see this version your application must be in DEBUG mode though. Alternatively, to see the build number from the login page you can view the page source (IE View --> Source) and Notepad will come up. You will see this line:

This indicates that the patch has been applied and now BI Publisher's version is 10.1.3.4.1 build 1145.

II) Upgrade of BI Publisher Desktop

Finally, there is one more upgrade to make. The BI Publisher's desktop should also be upgraded with the new xdocore.zip which, is shipped with the new patch. To apply it follow the next steps:

1. Using the just deployed new patch, get a copy of this file:

$ORACLE_HOME/oc4j_bi/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib/xdocore.jar

2. Take a backup of the the following existing xdocore.jar files:

a)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\TemplateViewer\lib\xdocore.jar
b)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib\xdocore.jar

3. Paste the xdocore.jar file copied in step 1., into the following directories:

a)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\TemplateViewer\lib\
b)C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib\

After this, BI Publisher Desktop has been upgraded and is in sync with the patch 9546699 just applied.

OBEE's... the official sub shop of Oracle BI?

2010-04-08T14:04:00.003-05:00

There is a sandwich shop down the road from our headquarters that is frequented by the staff at BICG. Up until today, I never realized why the name of their establishment looked so familiar. They simply forgot to put an "I" in their name...

Identifying Business Processes:

2010-04-02T11:54:00.001-05:00

Ralph Kimball in his newest book, “The Kimball Group Reader”, is a compilation of the articles that the Kimball Group has published since the original article in 1995. They have spent over 15 years working and gaining experience with Business Intelligence / Data Warehouse Courses. I selected one of his articles in his book to summarize because it is the heart of gathering requirements for a Business Intelligence / Data Warehouse Project.

The requirements for many Business Intelligence / Data Warehousing Projects are gathered by interviewing the business user and looking at his existing reports or his transactional system. By looking at the existing reports or the current transactional system it leads many times to inadequate or incomplete business requirements. Many of the existing reports were developed and built by someone usually using at tool such as Microsoft Excel to report on the existing transactional system. Also in many cases the transactional system may be used by different departments that use the same system to run different but similar business processes.

The article says that the Dimensional Model for the Business Intelligence / Data Warehouse Project should be built on the business process and not on the different usage of the transactional system or the reports built from the transactional system. One of Kimball’s core concepts is to identify the business process for the dimensional data model. The article says that a business process is “an event or activity that generates or collects metrics. These metrics are performance measures for the organization. Business Analytics inevitably want to scrutinize and evaluate these metrics by a seemingly limitless combination of filters and constraints. As dimensional modelers, it is our job to present these metrics in an easy-to-understand structure that responds quickly to unpredictable inquiries.”

The article points our common characteristics and patterns when identifying the business process for dimensional modeling. They are:

1.) Business processes are usually supported by an operational system.
2.) Business processes generate or collect unique measurements with unique granularity and dimensionality used to gauge organizational performance. Sometimes the metrics are a direct result from the business process. Other times, the measures are derivations.
3.) Business processes are frequently expressed as action verbs with their associated dimensions as nouns describing who, what, where, when, why, and how associated with the process.
4.) Business processes are usually triggered by an input and result in an output that needs to be monitored.
5.) Analysts sometimes want to drill across business processes, looking at the results of one process along the results of another. Drilling across is certainly viable if the dimensions to both processes are conformed.

Some of the major conclusions of the article are:

1.) Determining the core business processes is critical to establishing your overall framework of the dimensional models.
2.) The easiest way to determine these processes is by listing to the business users
3.) A dashboard is not a business process; it presents the results of numerous individual business processes.
4.) Technical considerations are very important for a Business Intelligence / Data Warehousing Project, but if the proper business requirements are not captured and modeled the success rate for the project is very low.

BI Publisher - Layout Builder preview in 10g

2010-03-31T18:04:00.011-05:00

If you are a BI Publisher user of release 10.1.3.4.1 (Patch 9229122) then you would like to know that this release includes a preview of the Layout Builder (WSYWYG Editor) part of the official release 11g.
Only thing you would have to do is set one particular parameter in xmlp-server-config.xml located in the repository directory xmlp\XMLP\Admin\Configuration as follows:
After restarting OC4J this change will take effect. When you create or modify a report then you will notice that under the "Layout" section there is a new button "Layout Builder" available.

The template prepared by the Layout Builder will have extension .xpt and will be an XML file which contents will only be rendered properly in the Layout Builder tool. In addition to this, the tool will not read any other file type than .xpt.

I guess that if you want to edit the .xpt file outside the Layout Builder you could use any XML text editor and assign a schema or DTD to it, but that is the topic for another post in this blog :).

At this point I would like to make a disclaimer: the parameter is an undocumented feature in 10.1.3.4.1 release and thus is not supported by Oracle in this release.

Which Tool Should Be Used for Reporting?

2010-03-26T23:26:00.001-05:00

My clients often wonder which Oracle tool they should use for reporting, building graphs, and generating dashboards. It’s a great question because, as anyone in this realm knows, there are many different options. This blog will touch on the three reporting tools I’m familiar with: Financial Reporting Studio (FRS), Web Analysis (WA), and Oracle Business Intelligence Answers (OBI).

One caveat here is that the bulk of my experience comes from the Oracle Hyperion side, so I have much more familiarity with FRS and WA, and definitely less so with OBI. So, this blog will be written from that perspective.

Below, I’ll give a synopsis of what each tool does, based on my experience, followed by my opinion of when each should be used.

Financial Reporting Studio
FRS is a great tool for producing regular reports, like a monthly reporting package. It allows you to create production level reports with a multitude of formatting options. FRS reports can be viewed in PDF or HTML format, from a client component or over the web. Reports can be gathered together in Books and batch scheduled to run at the frequency of your choosing, and then saved to a particular location or emailed out to a list of users.

FRS is not a tool for producing dashboards, is limited in its chart and graph functionality, and would not generally be used for ad-hoc reporting.

Web Analysis
WA comes from the Hyperion suite of products as the tool for building dashboards. In my experience, it’s most often used to create quick snapshots of data for management or executive level users. WA allows you to create multi-view looks at your critical business metrics, either in graphical or grid format. For example, a dashboard might include a line graph of sales by region in one quadrant, a bar chart of sales by VP in another quadrant, a pie chart of expenses by category, and a grid showing spending by department. WA includes traffic lighting as a feature, allowing you to highlight or color significant variances of data.

WA would not be used for production reporting.

Oracle BI Answers
OBI Answers is a tool for building both reports and dashboards from a variety of sources including relational and multi-dimensional databases. You can build similar dashboards to what WA offers and publish reports similar to what FRS offers. The entire OBI suite has pre-built modules by industry that allow for easier implementation depending on the particular business case.

While OBI Answers can use Oracle Essbase as a data source, there are some issues in doing so that prohibit the use when a particular hierarchy exists. This should be addressed in an upcoming release, but at this point, the OBI link to the Oracle Hyperion suite of products is very limited.

Which Tool?
So, which reporting tool should be used when? With the current releases and functionality available, here is how I would use them:

FRS – Use for regular production reporting such as income statements, operating expenses, headcount, and any other meaningful financial metrics from an Oracle Hyperion data source such as Essbase, Planning, or Financial Management. I generally don’t create charts and graphs using FRS because the options and functionality are fairly limited. But, if you have fairly straightforward and simple chart requirements, then FRS should work fine for you.

WA – I would use Web Analysis for producing grid and chart dashboards from an Oracle Hyperion data source. WA does a good job of incorporating “bells and whistles” that make a dashboard “pop”, providing important metrics quickly.

OBI Answers – OBI is a great tool to use to quickly build reports and dashboards from a data warehouse. In my opinion, this is the easiest tool to learn and use of the three. As I mentioned above, I don’t think it’s currently the right tool to use with Oracle Hyperion data sources, but that very well could change in the near future.

Going Forward
In future releases, I think that OBI will become the tool of choice for reporting and dashboarding, even for Oracle Hyperion data sources. Oracle is very good at creating synergies between their product lines, and while each individual application generally has their own set of tools, eventually, they converge to similar toolset technologies. I think as OBI gets more integrated with the Oracle Hyperion suite of products, it will become the tool of choice for reporting. In my opinion, it is easier to both learn and use compared to both Financial Reporting Studio and Web Analysis.

Vintages - Time x Time

2010-03-26T13:01:00.004-05:00

One of the more unique conceptual challenges when designing an Essbase or Planning model can revolve around handling multiple Time-basis dimensions. Many model requirements need granularity beyond the standard observation date that is typically defined as being the Time Period in a model. Consider the following:
A customer has a portfolio of investments, each having several characteristics related to some measurement of time.

The origination date of the investment
The maturity date of the investment
An expected length of the investment, based from which the origination or maturation date can be interpreted
An activity date

A single Time dimension (or two time dimensions that split portions of time apart, i.e. separating Years from Months and Quarters) can not accurately capture metrics in the previous example like the performance over time of all investments that originated in a given period, or how much return at a certain age of an investment can be expected.

Multiple Time Dimensions
Assuming the standard Essbase or Planning time dimensions represent the observation date, additional time-style dimensions can be created to provide more granularity. In the previous example, a dimension indicating the age of an investment can be utilized to analyse performance of investments based on how long ago they were originated. Likewise, identification of the starting or ending dates of investments allows analysis based on similar origination or maturation dates.

How much is too much granularity?
That all depends on the analysis requirements for a model. Adding more detail to a model will increase its size and complexity, but will provide a more detailed picture of time's effect on activity. All decisions regarding expanding a model require careful considerations of the benefits of additional complexity.

Other Time Time appears multiple Times (Time beyond core modelling requirements)

Scenario revisions

First pass, second pass, etc: These are essentially observations or snapshots of a process at a point in time. Depending on how many of these snapshots are required, it may be advantageous to have a separate dimension identifying when the snapshot was presented. Consider the benefits carefully of disposing of forecast revisions. Often, one key long term goal to improve forecast reliability is the ability to track variances between revisions over time.

How to Start Testing during an OBIEE Project

2010-03-25T16:44:00.006-05:00

How to start testing during an OBIEE Environment:

In an earlier posting I discussed the importance of and where to implement testing in the Project Life Cycle for an OBIEE Application. In this posting I want to discuss further how to implement testing for the application and some of the major steps during the Project Life Cycle.
During the Project Initiation Stage of the Project Life Cycle the first step in testing is to develop a Test Strategy. The Test Strategy outlines the recommended approach to the testing processes during the Project Life Cycle. Some of the key components of the Test Strategy are:

· Purpose and Test Objectives
· Quality Standard
· Test Phase Ownership
· Test Phases and Test Types
· Test Plans
· Test Case Repository
· Test Case Design
· Defect Tracking System
· Severity Levels of Defects
· Test Tools
· Test Reports

Since testing will be incorporated of the Project Life Cycle, it is critical that all project team members be familiar with the test strategy and understands their roles in the Project Testing Processes. All project team members will be involved is multiple phases of the testing processes, and it is important that they understand their role in testing and the roles of the other project team members in the testing processes.

Once the Test Strategy has been completed by the Testing Team and approved by the Business Users the next step is to start the Project Test Plan. The Test Plan is started during the Project Requirements Phase. As the requirements are being gathered it is important to identify how the requirements can be validated and tested to insure that they are met by the provided application. The development of the Test Plan involved all members of the Project Team as well as the Business Sponsor and Users. Once a requirement has been defined it is important to have some way to substantiate that the requirement has been met by the application. While looking for ways to test a requirement, it also helps the business user better define and understand the requirements. In most cases the business users have a good understanding of their source application, but have very little insight into the metrics and attributes needed by measure and manage their business processes. Also when a business application is used by many different groups they may have many different business processes to do the same functionally. While gathering the Business Requirement and Testing Requirements, there may be many different test cases to measure the same functionally across different departments in an organization. It is important to gather this testing information to insure that the business requirements can be validated by the different departments. One of the important steps that are often overlooked in testing is to test the actual requirement to insure that is valid and verifiable to measure and manage their business processes. Unfortunately this is often not the case and not until the business users see the Dashboards and Reports do they understand the value of the metrics and attributes for measuring their business processes. The Test Plan can help to identify the type of Testing and Test Cases that need to be developed to validate the requirements. Some of the items that can be included for testing are:

Validate the access to the System – Security
Validate the navigation to a particular screen, dashboard, page, and report
Validate access to the right data, screens, dashboards, pages, and reports
Validate the response time to access the application
Validate the response time to access a particular dashboard, page and report
Validate the capability and ease of using prompts to select data on a report with specific attributes
Validate the use of drilldown on Reports
Validate the use of navigation on Reports
Validate that the attributes that are used to display the metrics are correct
Validate that the metrics on the Reports provide helpful and valuable information to help them measure and manage their business processes

The next major testing task is to develop test cases to validate the user requirements. The Test Cases are usually designed and built during the design and build stages of the Project Life Cycle. In most situations the Test Cases are iterative, because more details are fleshed out during the Project design and build stages. For each requirement the minimum information needs to be developed for each test case:

Test Case Number
Test Case Summary
Test Case Description
Test Case Steps
Test Case Expected Results
Test Case Error Condition
Person responsible for Test Case

Once the Test Cases are built and agreed upon by the Project Team and Business Users, and the design and build stages for the application are complete then the execution of the Test Cases can begin. One major advantages of building the Test Cases early in the Project Life Cycle is the Development Team will understand the test conditions and can execute many of the Test Plans during Unit Testing. Quality is an important of any project and each team member is responsible for building quality into the finished product. By using the aforementioned Testing Processes quality can be built into the project from the earliest part of the Project Life Cycle, instead of waiting until the finished product is turned over to the business users for user acceptance testing. If testing is only implemented during user acceptance testing many of the quality issues that would have been identified and corrected in the early part of the Project Life Cycle will come to light, and will cost more in terms of resources and money to correct than if they were identified and corrected in the earlier stages of the Project Life Cycle.

BICG Events, News, Knowledge Base, and Video Now Available Through the iPhone and iPod

2010-03-25T10:58:00.002-05:00

BICG is providing mobile iPhone access to a variety of features including its BICG events, case studies, white papers, tips and tricks, and YouTube video.

Not only have specific features been specifically designed for mobile browsing, they also have been streamlined to speed up on-the-go web surfing.

1. Go to www.biconsultinggroup.com on your iPhone
2. Touch the plus sign (+)
3. Touch "Add to Home Screen" button.

Read the full press release here >>

Attributes, UDAs, and Alternate Hierarchies

2010-03-15T09:09:00.005-05:00

I'm often asked what the best practice is regarding implementation of Attribute Dimensions, Alternate Hierarchies, and User Defined Attributes. While there are no hard and fast rules, these guidelines outline the strengths and weaknesses of each.

Attribute Dimensions
Attribute dimensions are created by tagging members in a sparse dimension with an attribute tag. Members tagged with a member from an attribute dimension must be at the same Level as all other members tagged with an attribute from the respective attribute dimension. In theory, as long as members are at the same level they may be assigned attributes. In practice I recommend avoiding utilizing attributes for members that are not level zero members. This is due to difficulties ensuring members are at the same level. While this often is apparent in ragged hierarchies (also known as unbalanced hierarchies), symmetrical hierarchies can also have members that appear to be at the same level (i.e. they are at the same generation), but are in fact at different levels due to implied sharing where a parent only has one child. Also, this can create situations where a member is at multiple levels, and the resulting data value utilized by an attribute is not what a user expects.

User Defined Attributes (UDAs)
UDAs are flags that are placed on an Essbase member. A given UDA can be placed on any dense or sparse member in the outline. In addition, unlike Attribute dimensions, UDAs are not linked to the level of the members they are tagged to. In practice, this flexibility is most often leveraged to identify member sets for use in a calculation, especially since UDAs can be tagged to Dense members. While it is possible to refer to UDAs in most reporting and analysis packages, however, I do not recommend deploying UDAs for use by end users.

Alternate Hierarchies
Alternate hierarchies are secondary rollups of members within the same dimension. The level zero members in the alternate hierarchy are called Shared Members, and point back to an identically named real member elsewhere in the dimension. It is important to note that the real members do not have to be level zero members, or at the same level. One of the key advantages of Alternate Hierarchies is the ability for upper level summary members to have a completely unique structure (as opposed to utilizing an Attribute dimension, which merely re-totals the same hierarchy with only a subset of members). While there are many uses for this functionality, this often is utilized to build Management and Legal organizational hierarchies from the same source data, as well as facilitate financial reporting with different standards (i.e. IFRS vs. US GAAP). Conversely, Alternate Hierarchies typically require more maintenance, and increase the size of database. Therefore, they should be carefully deployed only in circumstances that justify the additional expense.

In summary, Attribute Dimensions, User Defined Attributes, and Alternate Hierarchies all have advantages and disadvantages. A typical deployment will often leverage all three areas of functionality to solve specific business problems, but will pay particular attention to the negatives of each option to prevent creating an overly difficult to support and expensive database.

Tips and hints relating to the development of Financial Reporting Studio reports – Tips 2 & 3

2010-03-14T23:27:00.008-05:00

When we left off on the last installment of Tips and Hints for Financial Reporting Studio, we had covered Selecting Multiple Members Based on Criteria, in this installment I intend to walk you through how to create a User defined Member List. Member lists are user-defined saved queries. Member lists can include members, other member lists, and functions.

Member lists
To define a member list:

Double-click a dimension cell for which you want to assign a member.

In the Select Members dialog box, take an action:

In the Members or Lists tab, highlight the members to include in the member list, right-click inside the Available area, and then click Save Highlighted.

Or once members have been selected and are contained within the Selected window, right click in the selected area and select save

Click OK.

Enter a name and description for the member list, and then click OK.

Click OK to return to your report.

Two limitations that you should be aware of:

Do not use quotation marks (") when entering a member list. If you do, Financial Reporting saves the name with the quotation marks, but you cannot select the name later.

User-defined lists can be created only with Oracle Essbase as a database connection. The size of a member list cannot exceed 4K.

Assigning Multiple Database Connection to Grids

You can populate a grid with data from multiple database connections. After you define a grid's initial database connection and dimension layout, you can change the database connection for one or more rows or columns of the grid. You can define another database connection for each data row or data column.

You can preserve the members selected in the grid when changing database connections on a grid row or column. You must map all dimensions that are assigned to rows or columns in a grid from the current database connection to the new database connection. Dimensions on the point of view (POV) need not to be mapped.

To assign multiple database connections to a grid:

In a grid, select one or more data rows or columns for which you want to change the database connection.

In the Row or Column Properties sheet, click the Select Database Connection button, .

In the Select a Database Connection dialog box, select a database connection from the Database Connection drop-down list, then Click OK.

In the "Map Dimensions Between..." dialog box, map dimensions between the default database connection and the selected database connection. You do this by selecting a dimension from the pull-down list in the right column for each dimension in the left column.

Click OK.

What skill set should a Hyperion EPM Administrator have? Part I

2010-03-12T13:32:00.002-06:00

Working with clients in today’s environment of streamlined resources, one of the areas often overlooked in an EPM implementation is the candidate who is going to fill the role of System Administrator for an organization. There is a direct correlation between the level of success an EPM project has and the candidate fulfilling this all important role.

I’ve been associated with projects where the Administrator has had a less than enthusiastic attitude toward this responsibility; thinking that this is just one more area of responsibility being piled on them. I always try to encourage and communicate that this is an incredible opportunity for them to learn/master a new technology on the job. In addition, the skill set they can acquire through “knowledge-transfer” with the project consultants is invaluable. The portability of this knowledge with future employers is also a benefit that administrators don’t always seem to grasp, as there is a constant need for seasoned Hyperion administrators in the marketplace. Clients consistently inquire as to whether we can assist them in recruiting experienced Hyperion Administrators based on our network of contacts.

Some of the skills from an implementation perspective that we as a project team like to see in an Administrator is a willingness to embrace the technology that’s being implemented Successful Administrators are the ones who jump in to the “deep-end” of the pool with the consulting team and understand that there may be some hiccups along the way, but ultimately the organization will be in a significantly better place once the project has gone “Live”. A consistent theme to those who are resistant to change is how their current legacy systems can handle can adequately handle the organizations requirements, however when questioned a little deeper about the limitations and issues of their current system and how that platform can position the organization for future growth, that’s when the realization of the shortcomings of the current are truly recognized.

The ability to effectively communicate is another critical skill that a successful Administrator must have because they will need to have the ability to speak to different audiences. Success administrators need to be able to communicate with their peers as users and often times users from different parts of the organization outside the familiar confines of the Finance area. In addition, successful Administrators need the ability to effectively communicate with Manager, Director, Vice President level employees. The Administrator must also be able to effectively communicate with the IT group as they will be the liaison between the business side and the technical side of the organizations. Each of these groups has their own “language” and vested interest in the organization which must be recognized by the Administrator in order to be successful.

Much of skill set that successful Administrators need is technical in nature but too often the “people” side is under emphasized. Overall, a good attitude with the ability to effectively communicate at various levels within the organization as well as the technical aptitude to learn a new technology are the ingredients for a successful administrator.

In Part II, we’ll discuss some of the more technical skills that a Hyperion Administrator should either have or acquire in order to be an effective and successful administrator.

Tips and hints relating to the development of Financial Reporting Studio reports – Tip 1

2010-03-12T13:08:00.005-06:00

In this installment of my blog contributions I would like to focus on some techniques that I have leveraged to get through some of the more challenging aspects of reports development.

While the function builder for member selection often allows you to get the detail selection that is needed I have found instances where I have had to become creative to achieve the exact detail that was necessary within the report being developed.

Four approaches that I leverage quite frequently are:

Selecting Multiple Members Based on Criteria
Defining Member Lists
Referencing separate data sources within the same grid
Using multiple grids and hiding all but one in the final product

Selecting Multiple Members Based on Criteria

You can select members dynamically based on user-defined criteria. You define criteria by creating expressions of members, Boolean operations, and commands. Boolean operators enable you to specify precise member combinations for the report, which is useful for dealing with large volumes of data. Use the AND, OR, UNION, and NOT Boolean operators, combined with expression commands, to refine your member selections.

For demonstration sake, lets assume you need to include all level 0 children of "Software/Other" but none of these members parents within the report. It is also necessary that when a new level 0 member is added that the report dynamically picks this member up.

To select multiple members based on criteria for this need I perform the following:

Double-click a dimension cell for which you want to assign members.
In the Select Members dialog box, right-click inside the selected area, then click Advanced.
Build the expression to satisfy the above need by 1^st selecting the descendants of "Software/Other"
Next toggle the Select Member pane to "Lists" and select "Level 0, Product "
Now update the operator to "AND"
Now render the report in Print Preview, the objective has been obtained
Explore other functions:

Select NOT to add the Boolean operator NOT to the expression. NOT is the inverse of the selected condition.
Type a left parenthesis, (, to add an opening character to the expression.
Type a right parenthesis, ), to add a closing character to the expression.
Click in the OPERATOR column, then select AND, OR, or UNION.
When all conditions must be met, use the AND operator.
When one condition of several must be met, use the OR operator.
To combine the data, use the UNION operator.

In the next installment we will cover "Defining Member Lists."

Essbase member name manipulation - Net Transshipments

2010-03-09T11:32:00.005-06:00

Transshipments
In some Essbase models there is a need to capture two essentially identical elements in separate dimensions. One common example of this is a transshipment model, where it is necessary to identify both the origination and destination of a shipment.

Two Dimensions
The most straightforward, and often user-friendly, manner to accomplish this type of tracking is to have the same hierarchy in two dimensions, with each member prefixed differently (i.e. the Source location members might be prefixed with "TO:" while the Destination members would be prefixed with "FROM:").

Net Shipments
A simple metric to consider in this type of model is often Net Shipments. In a multidimensional database, to calculate Net Shipments, shipments out of a location needs to be subtracted from shipments in (or vice-versa, depending on your preference). Assuming this is being calculated for Location A, the formula might look like:
Shipments -> TO:A -> Total Sources - Shipments -> From:A -> Total Destinations
(I.E. take all of the shipments that are sent to "A", regardless of where they are from, and subtract all of the shipments from "A" regardless of where they go to).

The Problem
The formula is simple and straightforward for one location, and remains similar for all other locations, but, every time a location is added, renamed, or deleted the calculation must be updated manually.

The Solution
Assuming the hierarchies are carefully constructed to always require "TO:" before the source location and "FROM:" before the destination location, Essbase can programatically determine the corresponding member by removing and replacing the prefix.

Components
Several string manipulation functions are required:

@NAME or @ALIAS: Used to pass either an Essbase Member Name or the respective member's Alias Name to another function as a string.
@SUBSTRING: This function will return a portion of a string passed to it.
@CONCATENATE: Used to join two strings together.
@MEMBER: Turns a string into a reference to a member name.

For this example, I'll also utilize @CURRMBR. This function returns the current member being calculated from a given dimension.

So, to determine the corresponding destination from a member in the source dimension:

Turn the current member in the source dimension into a string: @NAME(@CURRMBR("Source"))
Remove the prefix "TO:" from the string: @SUBSTRING(@NAME(@CURRMBR("Source")),3)
Prefix the new string with "FROM:": @CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3))
Convert the string into a reference to a member: @MEMBER(@CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3)))

Using this formula, you can fix on the portion of a hierarchy in the Source dimension, and have access to each member's corresponding Destination member. The original example of calculating Net Shipments might look like:
Fix(@RELATIVE("Source",0))
"Net Shipments"(
"Shipments"->"Total Destination" - "Shipments"->"Total Source"->@MEMBER(@CONCATENATE("FROM:",@SUBSTRING(@NAME(@CURRMBR("Source")),3)));
);
ENDFIX

The Impact of IFRS for EPM Reporting – Part 7

2010-03-05T19:58:00.005-06:00

In Part 7, I want to provide more detail on the similarities and differences regarding Foreign Currency Matters. This week I'll keep it short due to other projects I am working on... but again the details below were from a presentation during a company sponsored educational seminar about IFRS.

Foreign Currency Matters

Similarities

Similar approaches to foreign currency translation, guidance is different, but generally results in the same determination
Both consider the same economies to be hyperinflationary
Both required foreign currency transactions to be remeasured into functional currency with amounts resulting from changes in exchange rates being reported in income
Both require assets and liabilities to be translated at period-end rate, and income statement amounts generally at average rates, with differences in equity

Differences

Translation in hyperinflationary economy

US GAAP – Local functional currency remeasured as if it was the reporting currency (parent)
IFRS – local functional currency financial statements are indexed using a general price index and then translated to the reporting currency at the current rate

Consolidation of foreign operations

US GAAP – step by step method is used
IFRS – method is not specified. Either the direct or the step by step method is used.

The Impact of IFRS for EPM Reporting – Part 6

2010-02-26T12:13:00.007-06:00

In Part 6, I want to provide more detail on the similarities and differences for reporting Financial Instruments. The details below were from notes I took during a presentation during a company sponsored educational seminar about IFRS.

Financial Instruments

Similarities

Both require financial instruments to be classified into specific categories to determine measurement

Both require the recognition of all derivatives on the balance sheet

Hedge accounting is permitted under both

Both require detailed disclosures in the footnotes Differences

Fair value measurement

US GAAP – one measurement model (FAS 157) based on exit price
IFRS – various standards use slightly varying wording to define fair value – transaction price at inception date is generally considered fair value

Use of fair value option

US GAAP – financial instruments can be measured at fair value with changes in income
IFRS – financial instruments can be measured at fair value with changes in income, when certain criteria (more restrictive) are met

Differences

Day one profits

US GAAP – can recognize day one gains on financial instruments even when all inputs to the measurement model are not observable
IFRS – only recognized when all inputs are observable

Debt vs. equity classification

US GAAP – certain instruments with characteristics of both debt and equity must be classified as liabilities
IFRS – classification focuses on the contractual obligation

Compound (hybrid) financial instruments

US GAAP – not bifurcated into debt and equity components, but may be bifurcated into debt and derivative components
IFRS – required to be split into a debt and equity component, and if applicable a derivative component

Hedge effectiveness – short cut method

US GAAP – permitted
IFRS – not permitted

Hedging a component of a risk in a financial instrument

US GAAP – risk components that may be hedged are specifically defined – no additional flexibility
IFRS – allows entities to hedge components of risk that give rise to changes in fair value

Impairment recognition – available for sale debt instrument

US GAAP – may have an impairment due solely to a change in interest rate if the entity does not have the positive ability and intent to hold the asset
IFRS – generally only evidence of a credit default results in impairment of an AFS debt instrument

Convergence

In 2007, the IASB exposed a discussion paper to propose one measurement model for fair value whenever fair value is required. This paper was consistent with the concepts in FAS 157. Both boards appear to be moving towards ultimately measuring all financial instruments at fair value with changes in fair value reported through income.

Oracle BI Blog - EPM, Business Intelligence, and OBIEE

Working with the Master-Detail Report Feature of OBI 11g (pt 3)

Working with the Master-Detail Report Feature of OBI 11g (pt 2)

Answers Dashboard Reports: Directory Structure Best Practices

Working with the Master-Detail Report Feature of OBI 11g (pt. 1)

Three dashboard ideas for finance executives, a good read for those thinking of their organization’s strategy for the coming year…

Discipline (IRR)

­­­­­Efficiency (Time To Close)

Value Creation

Conclusion

Integrating Relational Databases with Essbase Studio

OBI 11G Fusion Middleware Control (EM)

OBIEE 11g, Opening Ports on Oracle Linux - How-To Video

OBIEE 11g, Setup Client DSN for Administration Tool - How-To Video

OBIEE 11g Certification Available

Easy Download Link

An OBI 11g Client Tools Only Install Work-Around

Using DAC & Informatica to send proactive email alerts for OLTP or .CSV config file data issues that cause ORA-01452: cannot CREATE UNIQUE INDEX

Cool New Feature in OBIEE 11g: Master-Detail Linking

Dashboard Design Tips

Starting and Stopping BIEE from command line

OBIEE 11g Architecture

Report Return HTML

Testing Project Business Requirements

OBIEE Go URL Command to access Reports and Dashboards from External Portals or Applications

OBI Apps Informatica Performance Tuning – Optimizing SIL Read Throughputs – Teradata Response Buffer Size Optimization

Teradata Response Buffer Size

OBI Apps Informatica Performance Tuning - Optimizing SDE Read Throughputs - Oracle DB Network Optimization

Oracle DB Network Optimization

Impressions of the Oracle BI 11g Launch in New York

BICG at Florida OAUG Summer Meeting

Using Selenium for OBIEE Regression Testing

Effective Regression Testing for OBIEE Applications

Session Variable Manipulation in Answers

OBIEE 11g launch in London July 7th, 2010

BI Apps Performance Optimization and Consideration - Part 3

OBIEE Date Expressions Reference

BI Publisher - Webservices update to10g

BI Publisher 10.1.3.4.1 (build 1145)- latest patch - 9546699 (April 1st 2010)

OBEE's... the official sub shop of Oracle BI?

Identifying Business Processes:

BI Publisher - Layout Builder preview in 10g

Which Tool Should Be Used for Reporting?

Vintages - Time x Time

How to Start Testing during an OBIEE Project

BICG Events, News, Knowledge Base, and Video Now Available Through the iPhone and iPod

Attributes, UDAs, and Alternate Hierarchies

Tips and hints relating to the development of Financial Reporting Studio reports – Tips 2 & 3

What skill set should a Hyperion EPM Administrator have? Part I

Tips and hints relating to the development of Financial Reporting Studio reports – Tip 1

Essbase member name manipulation - Net Transshipments

The Impact of IFRS for EPM Reporting – Part 7

The Impact of IFRS for EPM Reporting – Part 6

Efficiency (Time To Close)