Dates as Filters

Date Filters in OBIEE

Filtering dates by default in your Answers is a fairly common theme.
You also have the option to replace the IN with greater than or less than.

Yesterday:

From Your Analysis
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY,-1,CURRENT_DATE))



Setting the Dashboard Prompt:
SELECT TIMESTAMPADD (SQL_TSI_DAY,-1,CURRENT_DATE) FROM "ReportDate"


I'll leave out setting the dashboard prompt with the next few examples as the syntax is similar and the changes from the filter can be worked out from the example above.


First Day of the Month:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFMONTH(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Year:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAYOFYEAR(CURRENT_DATE )-1) , CURRENT_DATE ))


First Day of the Quarter:
"ReportDate" IN (TIMESTAMPADD(SQL_TSI_DAY, -1*(DAY_OF_QUARTER(CURRENT_DATE )-1) , CURRENT_DATE ))


This is not an exhaustive list of what is possible, but should get you started.

Firewall Ports and OBIEE 12c

Firewalls for OBIEE 12c

Firewalls are incredibly useful and alsy immensely frustrating, especially when you're in a large corporation and want to get things done. You have a new server sitting somewhere on the network and install (or try to) OBIEE only to discover that only you can see this machine as localhost and it is buried behind firewalls and varying levels of security.

Getting approval to open this machine up to be useful is bound to be a challenge.

Here's a list of the ports I have uncovered that I had opened enabling a multi node OBIEE install.

OBI System Components

9507: BI Presentation Services
9508: Cluster Controller
9509: Cluster Controller (monitor)
9510: Java Host
9511: Scheduler
9512: Scheduler (monitor)
9513: Scheduler (script)

Weblogic Components

9500: Admin Server (HTTP)
9501: Admin Server (HTTPS)
9504: Admin Server (Internal)
9502: Managed Server (HTTP)
9503: Managed Server (HTTPS)
9505: Managed Server (Internal)
9506: Node Manager

And don't forget 1521 - very important if you want to talk to your Oracle database.

BICS Upload files

BICS allows you to upload files that you can include in your models and visualisations.

But, there is a limitation on the file size of your uploads.
  1MB per file
 10MB per user
100MB per instance

Now this will probably change over time to be more generous, but, it is a frustration.

OBIEE 12c Architecture vs 11g

Quite a lot has happened architecturally under the covers with the release of OBIEE 12c.

OBIEE 12c Architecture diagram

OBIEE 11g Architecture Diagram

There is one major difference...... OPMN is gone in OBIEE 12c.

OBIEE components are now started via a series of commands run from {DOMAIN_HOME}/bitools/bin

• start
• stop 
• status

and it is also possible to run the 'commands' to stop and start the components in (the new look) FMW Enterprise Manager.

OBIEE 12c - the correct location for the tnsnames.ora file

Where is the correct location for the tnsnames.ora file in OBIEE 12c?

Create or copy your tnsnames.ora file to 

         {DOMAIN_HOME}/config/fmwconfig/bienv/core  

(e.g. /.../Oracle_Home/user_projects/domains/bi/config/fmwconfig/bienv/core/tnsnames.ora)

Note: This would also include other OCI files, such as sqlnet.ora if used.

This is documented in the Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition 12c

see: Setting Up Oracle Database Data Sources

OBIEE 12c - RCU - Create Repository

There are a few dependencies when running the OBIEE 12c RCU, one of which is that the database must be at least 11.2.0.4.

After that the next thing you notice after starting the RCU is the options available.

You now have the ability to generate the RCU scripts and pass them on to a DBA to run in seperately, rather than beg for admin access, organise meetings etc... just to run the RCU.

If you have the permissions already then nothing has changed and the top option System Load and Product Load is for you.

The remaining two options are to generate scripts and then load in the product information

• Select Prepare Scripts for System Load if you are unable to provide login credentials for a user with DBA privileges. RCU will create the necessary SQL scripts for creating the component schema and also update schema_version_registry entry for selected components and change the status to "LOADED." Once the scripts are generated, another user with DBA privileges can run the scripts using SQL*Plus to complete the system load phase. 
• Select Perform Product Load only after the system load phase scripts have been run to update the schema_version_registry and set the status to "VALID." Users with non-DBA privileges are able to select and execute this option. During the system load phase, some components will generate an additional post-data load script that requires DBA permissions. These scripts will also be run during the product load phase.

This means that the DBA gets to keep control, and you get your repository without ruffling too many feathers.

OBIEE 12c is Out!

Just arrived OBIEE 12c. This from a message on the Business Analytics - Proactive Support blog

I'm having a look and will have a play.

Find out more here

It looks like there is no in place upgrade, more a install and migrate, but, like I say, I'll let you know how I get on.