Dashboard Design Tips

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

Effective Regression Testing for OBIEE Applications

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

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

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

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

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

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

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

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

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

Here are what I consider general strengths of Selenium:

Flexible

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

Straightforward to use

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

Economical

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

And some general challenges:

Somewhat steep learning curve

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

Open Source = No official support

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

OBIEE-Specific Challenges

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

Attributes, UDAs, and Alternate Hierarchies

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.

Division of Responsibilities Matrix for Hyperion Planning Implementations

I am often asked, "How should we delineate task responsibilities across the organization to support our Oracle Hyperion Planning implementation?" I have compiled an Excel workbook that I have shared through Microsoft Office Live Workspace which I have developed for clients in the past which provides my perspective on where tasks should reside throughout the organization.

By clicking on the title above, Division of Responsibilities Matrix for Hyperion Planning Implementations, you will be brought to the Microsoft Office Live Workspace that houses this spreadsheet for your review and use.

Please let me know your thoughts relating to this matrix, any comments are greatly appreciated

What's the right level of detail to include in Hyperion Planning

When facilitating design sessions with clients, the question that invariably comes up is what level of detail should be included within their Hyperion Planning application.

Often, a clients first instinct is to include the level of detail that exists within their ERP system for actual data. When confronted with this desire/requirement, I like to encourage spirited conversation between project sponsors and subject matter experts by having them address the following two items.
1) Does a desire exist across the majority of the user base, to plan/budget at the same level of detail for which actual data occurs?
2) Does this level of detail for formulating a plan/budget coincide with management’s performance management objectives?

I have found that the best practice for the level of detail contained within a planning application or applications is that it should reflect management’s performance management objectives. Detail for detail’s sake wastes resources, but arbitrarily limiting the level of detail is not a best practice if it limits analysis against important factors that must be controlled.

If the driving desire to include this level of detail in the Planning application is only to facilitate analysis against actual data and business owners have no desire to perform data entry to each and every travel expense account for example, then other alternatives exist that will not encumber the Planning application(s).

Successful Planning implementations provide the level of detail within the planning model that business owners are accustom to and desire to formulate their Plans/Budgets. Meaningful reporting and analysis often may require further details then what users desire to plan to. This disconnect can be addressed through properly architected reporting applications, partitioning, or drill through.

When addressing the level of detail that is to be included within a Planning application, answering the two fundamental questions above has lead me to successfully architect and implement in excess of 30 Planning applications. More often than not, most clients when really pushed to consider what is really necessary from a Plan/Budget perspective versus what is necessary from a Reporting/Analysis perspective will arrive at a Planning application that is more summary level.

While meeting management’s performance management objectives for Reporting and Analysis shouldn’t be ignored, encumbering users with unnecessary levels of detail within a Plan/Budget only introduces potential for issues with performance, maintenance and end user satisfaction.

The mantra that I as well as many other seasoned EPM professionals subscribe to when asked to architect a Planning application is that, “Provide the level of detail necessary to formulate the Plan. Planning should be used as a means to facilitate Plan/Budget vs. Actual analysis, but if the level of detail that this analysis occurs at is beneath the level of Plan formulation then this analysis should be done outside of Planning (i.e. Essbase, partitioning to a reporting app, drill through).”

BI and the Tools of Diplomacy: Summits and Treaties

In my last post I explored the importance of language & communication in diplomacy and, by extension, the BI practice. In this post I'd like to consider two more tools of diplomacy and how they can be applied to the BI practice.

The Summit

A Summit provides a forum where all sides have the opportunity to speak directly with each other in a highly focused and most importantly, face-to-face format. In today's business environment it is too easy to fall victim to the limitations of email, telephone or even video communications. Even with the best video conference, few mediums of communication replace the extraordinarily "high bandwidth" of physical presence. (As an example within BI Consulting Group's experience, read my colleague Ed Martin's recent post reporting on our company-wide Summit held last week in Minneapolis.)

But common focus is also important. Well-organized summits often have a stated purpose agreed upon by all participants before or immediately at commencement. Sometimes ground rules even require that participants not leave until specified goals are met (think the Catholic Church's "White Smoke" summit -- or Conclave -- of Cardinals that elects a new Pope).

Of course a typical run-of-the-mill business meeting is a similar exercise... But when signficant, concrete progress must be made between parties of disparate backgrounds, framing the discussion in the specific terms of a Summit can be an effective way of setting the stage for the quality of interactions that will ensue.

The Treaty

The ultimate formal goal of any diplomatic effort is the execution of an agreement between all parties that sets specific parameters for behavior. The most obvious parallels in the BI practice are requirements documents, functional specs and service level agreements. In each case, the agreement clearly specifies the "rules" under which the signatories are required to behave.

In BI of course, one of the actors is the technology itself. In a Treaty, individual countries are held accountable for the performance of their borders, regulations and tarriff; in a Service Level Agreement, the IT support staff is accountable for the timely and stable operations of servers and applications. Well-written Treaties, SLAs and really any legal contract are useful to all signatories because they clearly define the expected performance of each participant and actions to be taken when that performance is breached. But the key concept is "well-written" -- a good agreement is clear and specific but not so obtuse that a lawyer is needed to interpret it.

An important component of an effective BI "Treaty" is the data validation report. A good BI implementation will include data audit reports -- confirmed as authoritative by all parties -- that compare warehoused data against source transactional data. These audit reports serve as the arbiters of performance that determine objectively and decisively whether the warehouse (in the "Tech Nation") is holding up its end of the Treaty by correctly reflecting source data (coming from the "Business Nation").

I plan to explore more tools of diplomacy in posts to come (trying to work "the Spy" into this discussion!), but as usual, in the meantime I'd love to hear your feedback and especially any other examples of Treaties and Summits in the BI practice.

OBIEE 10.1.3.4 Sample Application

In case you were not aware, the recently shipped 10.1.3.4 release of OBIEE includes a sample set of data, metadata, reports & dashboards that represent best practice for OBIEE metadata, report & dashboard development. There are some really good example reports and dashboards as well as solutions to common metadata modeling problems. It's well worth installing.

The sample files are also available stand alone on OTN for those not wishing to download the entire 10.1.3.4 distribution, and yes, they do work with previous 10g releases. Apparently the real trick is actually finding the file on OTN, so to save you the trouble here is a direct link: *LINKS UPDATED*

http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_analysis_setup_files.zip

The documentation of best practices is contained in the following file:

http://download.oracle.com/technology/products/bi/files/oracle_bi_sample_app_content_guide.zip

High Level OBIEE report design strategy

So often an organization will take on the task of installing OBIEE, establishing the infrastructure to support it, and even re-organizing their data appropriately. But when it comes to the actual report design, standards fail to be established, reports and dashboards become awkward and cumbersome, and user adoption suffers.

Since OBIEE is often seen as an Ad-Hoc reporting tool, this problem can often be the result of many developers developing with an Ad-Hoc mentality. The result is mismatched dashboards and confusing layouts. Other times, it’s the result of too many people involved in the development process. Once something gets approved by 15 people in a Power Point mock up, it’s difficult to get changes approved when something doesn’t pan out as expected. As a result, the small imperfections get set aside, but never fail to add up.

The following is a high level list of items to keep in mind when entering the report development stage of any OBIEE project. Keeping each of these items in mind will help you design attractive and useful reports and dashboards, which will ultimately lead to greater user adoption and pervasive use of the tool.


1. Trust your users - If your users don’t use your BI or other technology systems, it’s because they either get no value from it, or the system is too slow. These same users will spend their evenings shopping online and paying bills over the internet. It’s ignorant to believe they can’t use a well designed online reporting tool.

2. Think Insight, not Reports - Take the time to understand what the business user searches for, what they mark up with a yellow highlighter, and what they look at next after discovering an “exception”

3. Move Business Skills into your IT shop, not IT skills into your Business – Make an effort to understand the business needs, and then apply the technology accordingly. Don’t ask the business to “make due” with what IT provides.

4. Design your Dashboard in OBI EE itself using an iterative methodology – Don’t use Power Point to design your reports. Designing directly in OBIEE allows you to know immediately what will and will not work. Plan to make a series of adjustments throughout the design process, using feedback from the business users.

5. Establish a set of standards – If your users can’t derive insight in the first few seconds of looking at a Dashboard, it’s a bad design. This is no longer a world in which IT programmers lead the user’s impression of standards. Amazon, Google, Yahoo!, Apple iPhones, etc. all lead the pack when it comes to quality interfaces and standardized interfaces.

OBIEE Performance Tuning Tip – Turn off Query Logging

Though query logging has immeasurable development value, do not use this for regular production users as the runtime logging cost is extremely high. Every log item is flushed to the disk, which in turn hurts query response. Also, note that the query log files are not created on per user or query basis, there is only one query log per OBIEE server and it would have exclusive lock on the log file, which kills concurrent performance.

On the other hand, usage tracking has a very low runtime cost and is preferred to monitor the queries being used.

CACHE MANAGEMENT

Databases are periodically refreshed with updated data due to ETL (Extract-Transform-Load) processes being executed. This will cause the information that is stored in the query cache to become outdated, or ‘stale’. OBIEE Administrators must enable a method of purging and refreshing the cache on a continuous basis.


IT TAKES MONEY TO MAKE CACHE

There is a cost associated with utilizing cache. This cost comes in the form of disk space for storage, purge transactions on the server, and administration. However, all the costs itemized above are easily outweighed by the decreased response times and increased performance gained by the application.


SOME CACHE IS WORTH MORE THAN OTHER CACHE

Not all data sets are treated the same. Although caching is set as a default within OBIEE, some queries may not be a suitable use of the cache.

Situations in which queries return an extra-large amount of data may not be ideal because the extra-large data set will also cause the cache file to be extra-large as well. The larger the cache, the less performance enhancement can be derived from the cache. A general rule-of-thumb is that a direct database hit should be sought with queries that will generate more than a 1GB cache entry.

Situations in which particular data elements must be refreshed frequently, or on a near real-time basis, may not be good candidates for a caching solution. Depending on your requirements, there may be a threshold for which the performance advantage of cache can be trumped by the frequency at which the cached data will need to be purged and refreshed.


OPEN YOUR CACHE PLAN

1. Make a decision to either (a) start with cache enabled for the application... or (b) use cache only as a peformance tuning tool following initial development.

2. Then.. develop a cache updating method

3. On an ongoing basis, monitor query requests to identify opportunities for improvement


CACHE METHODS

The important hurdle to overcome when implementing a cache solution is to eliminate the opportunity for data latency, or ‘stale’ data to exist. ‘Stale’ data will exist when the cached data is not purged after the ETL process has updated the data warehouse.

1. No Cache Method
2. Manually Administered Cache Method
3. Table Level Caching Method
4. Polling Table Reference Method


No Cache Method –

A new SQL query will call the database every time a request for data is generated from the users. This will greatly affect system performance and user productivity because of the increased network traffic and demands on the server.

The system will only be as good (or fast) as its weakest link. If the network connection is slow or the database is slow returning results… the users will feel this pain with slow response times.


Manually Administered Cache Method --

When connected in to a repository within online mode, a cache manager utility is available from Manage>Cache. Note that this option may be unavailable if you have disabled caching in the NQSConfig.ini file.

Manual cache management is best served as a useful utility during testing phases. It is generally not a dependable option for daily operations as it requires the ad-hoc purging of the cache by a user.


Polling Table Reference Method –

The system can identify when to refresh cached data with the use of a polling table, better known as an event table. The event table contains timing information about specific events that occur. When an ETL process is executed, an event table in the database is updated with an entry recording the details of a data table when that data table is updated by the ETL process. The BI Server can poll the event table and purge the data from the cache if a data table has been updated.

The frequency with which the BI Server checks the polling table can be set to coincide with that of the ETL so data from the more frequently updated tables is purged from the cache more often to avoid ‘stale’, or out-of-date’, data.

The polling table method can serve to be most useful where incremental ETL processes run during the day. An example of this would be to update transaction or sales data.

The polling table method is not as beneficial when the incremental ETL is run once a day or overnight.

Frequency settings for which the BI Server polls the event table is set in the OBIEE Administration tool, Tools>Utilities>Oracle BI Event Tables. Note that the parameters for the event table contain table names only and cannot contain an alias. This can lead to misleading results and an alternative purging strategy must be found for the alias.


Table Level Method –

The simplest description of this cache method is to manage caching on a table-by-table basis. All tables have caching enabled by default. The default amount of time data is left in cache (called persistence time) is infinite.

The best combination of performance improvement and storage space conservation can be achieved by deselecting tables from this process that are rarely queried. The modification of persistence time to coincide with the incremental ETL processes (similar to the Polling Table Reference Method) can also further increase performance.

Building Calculations in Oracle BI Pivot Tables

Recently, I ran into a situation with designing a report to appear exactly like an Excel spreadsheet that our client's finance department used for years. Using the pivot table view, I was able to mimic 90% of the Excel spreadsheet. The remaining 10% that remained incomplete was creating group subtotals and grand totals. A colleague of mine, Kevin McGinley, an individual known for his dashboard prowess, suggested creating the pivot table report by building out each row calculation using the option "New Calculated Item," which can be found by clicking on the More Options button in the column.



Following the advice of my colleague, I was able to mimic the Excel spreadsheet 100%.

To build a calculation for an item in an Oracle BI pivot table, you need to do the following:

1. In the Pages, Sections, Rows, or Columns area, click the More Options button for the measure on which you want a calculation performed.

2. Select the option New Calculated Item. The Calculated Item window appears.

3. Assign a name for the calculation in the Name field.

4. To build a formula, choose the Formula function.

• NOTE: A formula creates a dynamic custom grouping within the pivot table. All measures references in a formula must be from the same logical column and must be present in the results. Formulas can be inserted into, or combined with, other calculations.

The mathematical operators become visible. The operators are shown in the following table.

+ Plus sign, for an addition operation in the formula.
- Minus sign, for a multiplication operation in the formula.
/ Divide by sign, for a division operation in the formula.
$ Dollar sign, for acting upon the row position of an item in a formula.
( Open parenthesis, to signify the beginning of a group operation in the formula.
) Close parenthesis, to signify the ending of a group operation in a formula.

• In the Function field, build the formula by typing or clicking measure names, and clicking operators to insert them into the formula.
• Use parentheses where appropriate.

5. When the calculation is complete, click Finished.

Keep in mind that this option isn't perfect. In fact, the main drawback is that each calculated row CANNOT be moved up and down the list of calculated items. If you've created 25 new calculated items and forgot to create an item that should be located at the 10th spot, then you'll have to recreate ALL of the calculated items after #10.

Another draw back that I have encountered is that if your column formulas contain calculations, those calculations may or may not work correctly. This example is more specific to the situation that I encountered and may not apply, but take notice.

The alternative to doing this is building union queries using "Combine with Similiar Request" option in Criteria. In my particular situation, union queries was not an option because there would be too many queries and performance would have suffered significantly.

Dashboard Navigation Tips

Some common designs for Dashboard navigation include:

1. Spreading content across Dashboard pages and letting the user navigate to each page
2. Linking Dashboards to specific reports
3. Linking Dashboard pages to other Dashboard pages (sometimes to hidden pages in the same Dashboard)

These are just some of the options, but when deciding which is best, there are a few things to consider. First off, I prefer option 2 (linking Dashboards to Reports) as opposed to Option 3 (linking Dashboards to Dashboards), simply because OBIEE will automatically create a "Return" link on the target Report, whereas no such link will appear on a target Dashboard. This link allows the user to navigate back to the original Dashboard with all of the prompts set (i.e. the original source page contains the user's settings, not the defaults). I am not aware of any easy way to achieve this same return navigation for a target Dashboard. Of course the user could press the browser's back button to return to the original Dashboard, but this method seems to have unpredictable and sometimes incomplete results (for example, in some IE browsers users have experienced issues when using the back button whereby charts do not display or other data "freezes", so the back button is definitely not the preferred method).

Either way, when implementing option 2 or 3, I often use Navigation Targets (these are set under Column Heading Interaction or Value Interaction in the Column Properties). Navigation Targets are useful because they allow you to link from a source Dashboard/Report to another target Dashboard/Report and this type of navigation also passes prompt values from the source to the target. Passing prompt values is the default behavior and I am not aware of any way to turn this off; however, there is a nice trick to avoid this behavior if you do not want the source prompt values to override filters in the target - the trick is, for any prompted column that the source and target have in common and that should not have prompt values passed, simply create a duplicate presentation column with the same name (and logical mapping) and place it under a different presentation folder. For example, if my source Dashboard contains a prompted column called Year, and so does my target report, but I want my target report to always contain a hardcoded filter of Year = 2008, then simply create a duplicate Year column in the presentation layer and place it under a different presentation folder (sometimes I create a folder called "FOR DEVELOPER USE ONLY" and set its permissions to hide it from front end users). Set the source report to use the original Year column and the target report to use the duplicate Year column. Even though the column names are the same in the source and target, OBIEE will recognize these columns as distinct since they are in different folders, and therefore, the source prompt value will not override the target filter (and this is seemless to the end user).

One last tip - be careful with Navigation Targets because 1) I find that their behavior is not always intuitive to the end user, especially when linked from charts and 2) be aware that if you use Value Interaction with Navigation Targets, then the actual value that a user clicks on will be passed to the target as part of the filter criteria. If you simply want to provide a text link from a source to a target without passing prompt values, then you can use basic HTML and a hardcoded URL for the target report/Dashboard. However, if you want to provide a text link (as opposed to a Navigation Target embedded in a table/chart), and you DO want to pass prompt values, then you could actually accomplish this with another trick - you can still use Navigation Targets but just create a dummy report, with one real colum and one dummy column. Hide the real column and design the report so that it only returns one row. Also hide the column headings. Then create a Navigation Target under the Value Interaction for the dummy column. Set the dummy column to a constant, such as a description of the target. The final result which displays on the dashboard just resembles a regular text link but it contains the OBIEE built-in Navigation Target functionality which will link to the target and pass the prompt values, so you get the best of both worlds!

Branding - LESS is MORE

Experience has shown me that, when people start learning how to change skins and style attributes in OBIEE, they tend to go overboard. There are literally thousands of Cascading Style Sheet elements that make OBIEE look and feel the way it does.

Additionally, it’s extremely difficult to locate certain elements, and many times you end up resorting to a trial and error strategy. I think once someone discovers an element where they can change a text font, or modify a background color, they do it.

Eventually, someone really digging into these skins and style sheets are going to come across a whole list of changes they can make… and they will probably make them. My thought is that, just because something can be changed, doesn’t mean that it should be changed.

With that said, here are some guidelines to follow when attempting to brand OBIEE:

• Identify an inspiration source (company website, intranet, presentation template, etc…)


• Create a color scheme before starting the branding process… stick with it (or as close as you can)


• Follow the leader. There is no need to re-invent the wheel. Try to change existing colors verses introducing colors where they don’t belong.


• Keep a neutral backdrop. It’s ok to leave white space on the screen… Google is a great example.


• Many skins/style changes will effect more than one element in OBIEE… beware!


• Periodically save a copy of the skins/styles folders as you go. Keep multiple versions on file so you can revert back to them any point in the design process. (ex. s_ver1, s_ver2, s_ver3, etc…)


• Be creative and experiment… just don’t go overboard

These are simply guidelines and not meant to be rules since branding is a creative process. Just keep in mind that this is something everyone in your organization has to look at and use on a daily basis. It’s probably a good idea to get a second opinion at checkpoints during the design process. But try to limit the amount of input, as too many ideas can become overwhelming.

For additional assistance with branding, visit BI Consulting Group’s website. There is an entire service offering dedicated specifically to Branding OBIEE called Identity. They have even developed a tool to help identify certain style elements, and where to change each setting.

Branding - Dashboard Page Tabs

One of the most dramatic changes you can make to the dashboard design is the Page Tab color. In my opinion, this is where you really start geting the impression you’re working with a truly customized version of the software. I say this assuming there has already been a significant amount of customization done on the dashboard header. There has to be some level of congruence maintained. Modifying the page tabs, without a previously customizing the header to match would make it seem awkward and out of place.


Unfortunately, it’s not a simple process. There are multiple image files that need to be changed, consistencies that need to be maintained between these image files, and a handful of HTML modifications to go with it.
Fortunately, I know exactly what needs to be done… and plan to share it with you now.

Before you begin, you’ll need to choose a color for your “selected” an “not-selected” tabs. I suggest sticking with the “out of the box” theme of having your selected tab the same tone, but a slightly darker shade than the unselected tabs. Each tab (selected and not-selected) is made up of 2 images each, making a total of 4 image modification necessary to complete this task. Be sure to make copies of the original image files so you can revert back in the event of a mistake.

1. Open the file “bg_tab.gif” using an image editing software like Photoshop, or Fireworks.

2. Adjust the Hue, Contrast, and Lightness settings until you achieve your desired “Selected” tab color (be sure to document the exact setting adjustments as you’ll need them in the next step. Save the file.

3. Open the file “subtabr.gif” and apply the EXACT same setting adjustments made in step 2. Save the file.

4. Repeat steps 1-3 with the files “bg_dim_tab.gif” and “subdimtabr.gif” using the “Not-Selected” tab color you’ve already chosen.

5. To change the text color within the Selected tab, you’ll need to adjust the HEX color code (“color: #XXXXXX;”) within the file “PortalContent.css”, for the label - TabHiFont

6. To change the text color within the Not-Selected tabs, you’ll need to adjust the HEX color code (color: #XXXXXX;) within the file “PortalContent.css”, for the following labels:

TabDimFont:link

TabDimFont:visited

TabDimFont:hover

I suggest choosing a text color slightly less bold than the “Selected” tab text color. I also recommend choosing a different color to utilize the hover functionality. Choose either Black (#000000) or white (#ffffff) for the label “TabDimFont:hover”

The final task is to set the color of the Tab Line to match the darkest color of the “Selected” tab. The goal here is to make the bottom color of the “Selected” tab seamlessly blend into the tab line.

7. Open the file “bg_tab.gif” you modified in step 2. Identify the HEX color code of tab at its bottom most point. Remember this code.

8. Open the file “PortalContent.css”, and find the entry “TabLineCell”. Edit the entry to look just like the example shown here, with “XXXXXX” being the HEX color code you identified in step 5.

.TabLineCell {background-color: #XXXXXX;}

9. Refresh your browser to see the changes… make adjustments if needed.

You may need to repeat steps 1 – 6 a few times until you achieve the exact feel you’re looking for. Just for fun, play around with making the “Selected” tabs a completely different color than your “Not-selected” tabs, or vice versa. You’ll be amazed at the difference it’ll make.


If you really feel ambitious… you can do the same to the 'Answers' and 'Delivers' Page Tabs and style settings. Realistically, it isn’t feasible for a do-it-yourself-er to spend the time to identify each individual setting and redesign every detail. It would probably take months… or even years just to find everything.

Some consulting firms who implement OBIEE will do a certain level of branding, if requested. BI Consulting Group advertises a service specific to branding called IDENTITY. They have even developed a tool that pinpoints and links to each of the 20+ CSS files where the bulk of these changes are made.

Check back for future updates where I’ll cover steps on customizing other areas of OBIEE.

Basic Branding - Dashboard Header... Continued

In my last Blog… I outlined the steps necessary to update your header image and dashboard links. Here we will update the product link color, welcome bar background color, dashboard name color, the welcome text color.


Whenever you’re making additional color changes to the header, or anywhere else in the software, keep your company color palette in mind. Pull colors from the company logo or an already header image whenever possible. So without further ado… we begin:

1. Identify a desired color and HEX color code for the welcome bar. The welcome bar is the area directly under the dashboard header image and sits behind the page tabs. This color should complement the existing header image.

2. The welcome bar has two color settings… so first open the file “PortalContent.css” and find the entry “TabTable”. Replace the existing “background-color“ HEX code with the one you identified in step 1.

3. Now open the file “PortalBanner.css” and find the entry “PortalBottomTable”. Replace the existing background-color HEX code just like step 2.

4. To change the dashboard name text color, open the file “PortalBanner.css” and find the entry “PortalName”. Replace the HEX code for “color” with one of your choice.

5. Within the file “PortalBanner.css”, do the same thing for the entry “WelcomeTextCell” to change the color of the welcome message.

6. To update the product links, find the following entries within the file “PortalBanner.css”:

a. .DashBarProductCell A,
.DashBarProductCell A:visited,
.DashBarProductCell A:link

b. .DashBarActiveProductCell A,
.DashBarActiveProductCell A:visited,
.DashBarActiveProductCell A:link

c. .DashBarActionCell A,
.DashBarActionCell A:visited,
.DashBarActionCell A:link

d. .DashBarProductCell A:hover,
.DashBarActiveProductCell A:hover,
.DashBarActionCell A:hover,
.DashBarAlertCell A:hover

e. DashBarSep

7. For each of these entries, with the exception of “DashBarSep”, you will need to add an attribute named “color” between the “{…}”. It should look like the following with “XXXXXX” being your desired HEX color code.
color: #XXXXXX;

…for entries with the color attribute already existing, simply replace the HEX code.

8. Refresh your browser to see the changes… make adjustments if needed.


You are now one step further to achieving a completely customized OBIEE. A completely branded style would include customized colors and images for:

• each page tab
• welcome text
• product links
• section colors
• table and pivot table colors
• chart colors
• ...and the list goes on and on.

If you really feel ambitious… you can do the same to the 'Answers' and 'Delivers' areas of the software. Realistically, it isn’t feasible for a do-it-yourself-er to spend the time to identify each individual setting and redesign every detail. It would probably take months… or even years just to find everything.

Some consulting firms who implement OBIEE will do a certain level of branding, if requested. BI Consulting Group advertises a service specific to branding called IDENTITY. They have even developed a tool that pinpoints and links to each of the 20+ CSS files where the bulk of these changes are made.

Check back for future updates where I’ll cover steps on customizing other areas of OBIEE.

Basic Branding - Dashboard Header

There’s a lot of talk… and confusion... surrounding the topic of branding OBIEE. There obviously isn’t much in regards to documentation as Oracle doesn’t make it a priority to show companies how to erase the Oracle name from their own product. But since I don’t really care about any of that… I’ll share some of what I know.

I’m going to outline a few simple steps you can use to change your OBIEE dashboard header. Keep in mind that this is just one of many steps required to achieve a completely branded environment. The following is a step by step guide designed to get you started:



1. Open the image file bg_header.jpg using an image editing software like Photoshop, or Fireworks. In 10.1.3.3, the location is within \OracleBI\web\app\res\s_oracle10\b_mozilla_4.

2. Modify the image as desired, but be sure to maintain the image size. I also recommend using a similar layout as the original image, as the dashboard links tend to get lost if the logo or background image is too busy. You can also crop screenshots from your company website to capture certain designs or themes.

3. Save or export this file with the same name (bg_header.jpg). Save a copy of the orginial file under a different name to revert back to if needed.

4. Depending on the color of you new header, you may need to change the color of the dashboard links located at the top center of the header. Using the Image editing software, identify the HEX code for your desired color. (white = FFFFFF, Black = 000000)

5. Open the file PortalBanner.css, and find the following entries:

• PortalLink:visited
• PortalLink:hover
• PortalLink:link
• PortalLink:active
• CurrentPortal:link/visited/hover

6. Each of these 5 entries will have an attribute listed under names “color”. Each entry is displayed depending on the link status (visited, hover, current…etc.). The default color is the same for each situation, but you can play with these setting if you want. All you need to do is insert your new color code in place of the existing one.

7. Refresh your browser to see the changes… make adjustments if needed.


You have now completed the most basic tasks needed to brand your version of OBIEE. A completely branded style would include customized colors and images for:

• each page tab
• welcome text
• product links
• section colors
• table and pivot table colors
• chart colors
• ...and the list goes on and on.

If you really feel ambitious… you can do the same to the 'Answers' and 'Delivers' areas of the software. Realistically, it isn’t feasible for a do-it-yourself-er to spend the time to identify each individual setting and redesign every detail. It would probably take months… or even years just to find everything.

Some consulting firms who implement OBIEE will do a certain level of branding, if requested. BI Consulting Group advertises a service specific to branding called IDENTITY. They have even developed a tool that pinpoints and links to each of the 20+ CSS files where the bulk of these changes are made.

Check back for future updates where I’ll cover steps on customizing other areas of OBIEE.

Performance Testing

I recently picked up a book titled "Troubleshooting Oracle Performance", which covers methods for tuning SQL and the Oracle DB versions 9iR2 through 11gR1. I have only read a few chapters to this point, but it appears to contain some useful information. One interesting point it makes in these early chapters is that many IT projects (BI implementations included) fail to define specific performance requirements (or SLA's) in addition to the functional requirements. In some instances, performance does not ever become an issue after implementation, but far too often, performance is not adequately tested during the test phases, and performance issues are not encountered until the application is already in production. This point is easy to ignore (until it occurs in production and it's too late), but poor performance can have the same damaging effect as functional defects on the overall user adoption of an application, and therefore performance testing should be given equal consideration as functional testing - this point is just something to think about which bares repeating every now and then.

The Six Guiding Principles of Dashboard Design

Most of the best practices standards we’ve provided, once learned, can be quickly implemented on each new dashboard created. Others will take some practice, but all have been proven in production implementations, each of which have received overwhelming user acceptance. Our advice is to avoid the approach of “getting something out the door” and then going back later to apply best practices; instead, we recommend using these standards and best practices from the very start with the first release. The fact is, once you’ve learned the ideas in this document (in particular our section on “Shortcuts, Cheats and Other Tips & Tricks”), you’ll discover that you can build a powerful, flexible, and aesthetically pleasing report in the same time it takes to build a clumsy, static, low-functionality report.

There are six main Tenets of good Dashboard Design best practices and are as follows:

I. Provide insight, not reports.
When a key user drops off a stack of Excel spreadsheets in response to your request for “requirements”, take the time to understand what results they are trying to derive from that content. Understand what they search for, what they mark up with a yellow highlighter, and what they look at next after discovering an exception. With the best practices that follow, you’ll discover that you can offer multiple ways of providing insight from the same content packed into one Excel spreadsheet (which offered no insight, just data); and that you’ll be able to compact multiple Excel spreadsheets into a single, flexible report module that can fit into the upper left hand corner of a dashboard. Column selectors, view selectors, and bubble charts are not complex. If you think they are, see Tenet #3.

II. Protect your real estate and eliminate the white space.
Unlike printed reports where you can get away with saying “the answer is on page 42”, the same is not true for online dashboards where you will most likely be restricted to what you can fit into an 1024 x 768 pixel screen (or 1024 x 2304 if your dashboard is three pages long). If your users can’t derive insight in the first few seconds of looking at a dashboard, the design could use improvement. If your dashboard has large patches of white space, it will look incomplete to the users and you are wasting space. If you believe that it’s impossible to fit meaningful content into 1024 x 2304 pixels, go check out popular Internet portals like MyYahoo, Google, or MSN.

III. Trust your users will get it.
Most users in today’s business world are familiar with browsing the web and the typical controls used to shop, bank, research, and consume information. Controls like dropdowns, buttons, checkboxes, and text fields are all used on popular sites throughout the Internet – and on OBIEE, too! Trust your users. If you’ve followed rules #1 and #2, your users are going to get it.

IV. Merge business skills into your IT skills.
In order to create the insightful reports we discussed in Tenet #1, report designers need to know more than what the business users are asking for on a report. A highly successful BI project will have designers who know the business side well and can develop new reports and enhance existing ones without explicit instruction from the business users. In Business Intelligence, we need to move away from simply asking what the user wants the report to look like and what data they want in the database. We need to move toward understanding the business processes and determining what data and reports will bring the insight the users need. Once you’ve gotten to know the processes driving the reports, you can make suggestions to the users on what they need to be successful. Most of your users have no idea what kind of reports Oracle BI is capable of producing when utilized to its full potential. With a time spent getting to better know the business, you’ll be able to deliver powerful, insightful reports the users hadn’t considered or didn’t even know were possible.

V. Design your dashboard (and insight) in Oracle BI itself using an iterative methodology.
Dashboard design requires multiple iterations to be successful. Business users often don’t know or realize what they want until they can see something they like or something they recognize needs to be changed. It’s important that you have a plan in place during the build process that allows for several levels of refinement. This plan should include a sound strategy for capturing and documenting user input, prioritizing the changes, and determining the complexity. Careful planning of this process will ensure that all iterations run smoothly and add as much value as possible.

VI. Be prepared to review, refine, and re-do.
After you’ve rolled out your BI project, if the implementation is viewed as a success by the users, you’ll notice that BI slowly becomes more pervasive throughout your organization and is eventually no longer thought of as BI - it’s simply part of the way the business is run. But the business focus can change, processes change, needs change. A periodic review should be done to asses whether the dashboards are still meeting the needs of the business as well as when they were first deployed. Refinement may be needed to evolve the dashboards with the business, and sometimes even more dramatic re-do’s are necessary.

Cache Basics

Part of any OBIEE Implementation should be spent defining the proper caching strategy specific to a given project. This post is a basic look at what cache means to OBIEE.

WHAT IS CACHE?
Pronounced "cash", it is generally an easily accessible place to store frequently used information. The most recognizable use of cache relates to the manner in which a personal computer utilizes this information. As it relates to PC, there are two basic types of cache: memory caching and disk caching.

Both types follow the same principle of storing recent or frequently used information in a memory buffer. Memory cache uses a portion of static RAM memory. Disk caching uses conventional main memory. When a piece of information is required, the system (or application) first checks the cache to see if the data is there.

The main purpose of cache is to improve performance. Accessing information stored in a memory buffer can be thousands of times faster than accessing a byte on a hard disk.

OBIEE CACHE
Caching as it relates to OBIEE is a bit different, but the same general purpose applies. Rather than utilizing memory buffer to quickly access recent information, OBIEE will access a file containing a stored record set of information rather than spend processing time querying the database upon each request.

When a request, or query, is made for a set of records, OBIEE can store this as cached information in a file to be used later. When a similar request is made for this same information, OBIEE will retrieve this information from the cache rather than spending processing time negotiating with the database. This can greatly improve the performance of the application.

In the OBIEE/Siebel Analytics world, this is referred to this as ‘query cache’. By utilizing query cache, the cost of database processing only needs to be paid for the one time the query is run against the database. Subsequent identical queries will run against the cache.

"Bullet Proof" SSO

The following pieces are a subset of info presented at Oracle OpenWorld's OBI SSO Unconference by Kevin McGinley and myself. The content framework was provided by John Andrzejek of BI Consulting Group.

The full presentation can be found via http://www.biconsultinggroup.com/OOW

Many organizations have enabled SSO (Single Sign-On) with OBI EE; however, not all have taken the appropriate steps to make it "Bullet Proof", aka next to impossible to hack. Until now!

Below is a typical OBI EE login path when SSO is not enabled. This method works well in an intranet environment where personal data is not available to be queried by OBI EE.



Many organizations have taken the step to enable SSO for OBI EE so their users do not have to enter/remember yet another username/password combination. SSO is more secure than the basic OBI EE login but is still susceptable to hackers tricking OBI EE into authorization.

In order to ensure a more robust/secure method of SSO with OBI EE, the following approach should be taken so that a hacker cannot trick OBI EE into thinking the user has been authenticated. This is handle by Step 4 (see pic below) whereby the user clicks on a link in their portal to go to OBIEE and the portal writes a Record to a database table and then the BI Server checks this record to make sure the user has been authenticated.

If the user was not properly authenticated by the Portal then OBI will deny the user access to OBI EE.