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

Session Variable Manipulation in Answers

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

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

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



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


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

OBIEE Date Expressions Reference

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.

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

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:

1. Selecting Multiple Members Based on Criteria
2. Defining Member Lists
3. Referencing separate data sources within the same grid
4. 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:

1. Double-click a dimension cell for which you want to assign members.
   
   
2. In the Select Members dialog box, right-click inside the selected area, then click Advanced.
   
   
3. Build the expression to satisfy the above need by 1^st selecting the descendants of "Software/Other"
   
   
4. Next toggle the Select Member pane to "Lists" and select "Level 0, Product "
   
   
5. Now update the operator to "AND"
   
   
6. Now render the report in Print Preview, the objective has been obtained
   
   
7. 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 on Unix: Tips and Tricks

Oracle's Hyperion Essbase is a multidimensional database primarily utilized for providing robust analytical capabilities. Real-time slicing and dicing of data, exploration of KPIs and their material basis, and decision making assistance make Essbase an indispensable financial engine for organizations of all sizes. This blog addresses some of the lesser known tips and tricks with running Essbase on Unix. Linux, AIX, Solaris, and HP UX are all supported, see the Oracle web site for a full list of supported operating systems, including specific release notes.

1. Essbase startup
Oracle Essbase has a preference for running under a C or Bourne shell. Often, this conflicts with the default shell on many installations (Korn). Ensure that your startup script for Essbase begins with "/bin/sh". Also, avoid using "nohup" (it is not necessary for a C or Bourne shell; it is only needed for a Korn shell)- nohup has been shown to cause a race condition in larger installations when making changes to the security file. For some reason, the default startup script included with the version 11 release does not follow the documented convention to start Essbase; if you're having strange problems with your installation I would recommend making changes to this script to make it similar to the documented startup script. For an example startup script see the Essbase Technical Reference chapter "Maintaining Essbase", section "Running Essbase Servers, Applications, and Databases." (the example script also shows how to hid the console password from nosey users on HPUX/Solaris systems)

2. Environment variables
Oracle Essbase requires certain environment variables to be preconfigured for correct operation. These variables are typically documented in a file named "hyperionenv.doc" on the Unix server during installation, however the convention utilized does not export the variables. Ensure that the processes that start Essbase, as well as any scripts calling Esscmd or Maxl scripts, have their environment configured by using this script as a template (remember to export the variable settings).

3. Filesystems
Essbase doesn't like NFS. It gets all whiny and fussy, like a 6 year old that can't have cake. In all seriousness, Essbase's IO requirements and file locking characteristics don't mesh well with filesystems mounted via NFS. This problem is particularly troublesome for ASO database, but affects BSO databases as well. To sum it up, using NFS for Essbase is not supported, so save yourself the headache- don't do it.

4. Stopping Essbase
No one runs Essbase in the foreground, so you can't just type "exit" to stop Essbase. Essbase can be stopped via Administration Services by manually selecting the server and selecting Stop. Alternatively, scripts can be written on the Unix server utilizing either Maxl or Esscmd to stop the server. The relevant Maxl statement is "alter system shutdown" (after logging in to the server) or "shutdownserver" in Esscmd (no login necessary, as the login information is used as a part of the shutdownserver command syntax). Whatever you do, do not "kill" the Essbase server- at worst you're risking serious database corruption, at best you're applications will need to go through free space recovery on startup- a time consuming operation that is unavoidable when the server is killed and which prevents users from being able to connect to their applications and be productive. Note- the version 11 installation does not include a shutdown script; you'll have to write your own, and add it to the appropriate place in the EPM shutdown script to properly stop your server.

5. Best Practices (not necessarily for Unix, but for Essbase administration in general)
Consider carefully the tradeoffs with any of these recommendations. They're all made with the assumption that their advantages outweigh their disadvantages.

• Database restructures (particularly for read/write applications) - performing periodic full database restructures (using Maxl statement "alter database application.database force restructure;") will improve performance, and reduce free space recovery time if a crash does occur.
• Place the application in read-only mode before shutting the server down - this forces the Essbase server to write all open files held in memory to the disk, in particular the Free Space file.
• Fully shutdown the server before performing a backup. Applications can be backed up in read-only mode while the server is running, however the Essbase security file can not. Shut the server completely down to make a backup of this critical file.