Wednesday, July 27, 2016

Web UI for Essbase BI Accelerator in OBIEE 12c

BI query performance can be improved by adding Essbase cubes as aggregated data sources to the business model in OBIEE. The system comes with a convenient Web UI to let user create and delete these aggregates (along with the OBIEE metadata) within a few clicks.

That acceleration wizard is available as part of Cube Deployment Services in Essbase 12c and uses its API to create these Essbase cubes based on any existing RPD business model.

The wizard can be launched from the URL http://yourservername:7780/cds/view on your OBIEE install.

It guides through the process and implements the model configuration behind the scenes using the following steps:
A - Selecting a BI business model that will be accelerated,
B - Specifying an Essbase application on which to deploy the cube,
C - Selecting BI measures, dimensions, and logical columns to include in the cube,
D - Deploying the Essbase cube and seamlessly connecting it to the BI business model as a data source

Once the necessary metadata is incorporated within the RPD, appropriate query rewrite to the Essbase cube is optimally done by the BI Server when these metrics/dimensions are referenced in a query.

The following brief video guides you through the steps to create an aggregate cube using Essbase BI Accelerator

Wednesday, July 20, 2016

Embedding Oracle DV Projects in BIEE Dashboards

Embedding DV Projects directly into BIEE dashboards can simply be done by posting the URL of the project as an 'Embedded Content' object into the target dashboard.

The option of defaulting the DV Project directly in a Presentation Mode is possible by appending the following text at the end of the project URL : '&reportmode=presentation'.

The following brief video shows the steps to get this done :

Tuesday, July 5, 2016

Sourcing DV from Essbase data - simply

Need to create some nice DV visualisations on your Essbase cube data, but you don't have a BIEE server connected to Essbase... 

A simple robust way to do this is to leverage SmartView in-between Essbase and Oracle DV.

Create an Excel spreadsheet with your Essbase data using SmartView. Make sure you have selected a few SmartView options to make it consumable by Oralce DV (see video), and simply import that file into Oracle DV.
Check out this short spoken video example.

June 2016 : What's new in DV Desktop 1.1 (

A new version of Oracle DV Desktop was just released on Jun 3rd 2016. Build 05 28 011143, aka V1.1 of DVD. If you were already using DV Desktop, you should get a prompt to upgrade, if not, just visit the OTN page here and download the software.
Along with bug fixes, few new items came with this release. below is a summary list, we will publish more detailed entries/videos soon to illustrate some of these :

  • New beta connections types for more datasources : Greenplum and RigthNow are added as Beta connections.

  • Connectivity to Oracle Dbaas : using the oracle DB connection type, users can directly create direct connections to their DBaaS services (DB as a Service sources).
  • Ability to invoke database specific SQL syntaxes directly from within a calculated item, using EVALUATE function. This enables to fully leverage the underlying power of any database, by function-shipping the calculation process directly down to the database and retrieving only the result rows. For example, clustering tens of millions of transaction IDs into only few clusters : the clustering process can be pushed down in the database, and DVD will only retrieve and visualize the few cluster rows produced by the database calculation. This capability is now available OOB within the 'New Calculation' dialog box, at the time of building analysis, using EVALUATE function. See this video describing the feature

  • The 'Attribute' Function ATTRIBUTE function is available in custom calculations, and turns expressions (either measures or attributes) into attributes. If you are familiar with Answers, and ever used the 'Treat as an Attribute' Flag in the Answers expression editor, ATTRIBUTE function is the syntax behind this functionality. It is useful when computing aggregates on rows that are grouped by values of another aggregate. For example, how many customers have made 1,2,3, N orders ? How much profit in each revenue by customer decile... etc.Syntax : ATTRIBUTE(<expression> BY <attribute list> WHERE <predicate>) 
      A more detailed blog entry will come soon about this feature

Using Forecast Function in Oracle DV / OBIEE 12c

Oracle DV and OBIEE 12c offer a right click interaction to include forecast data in many visualizations.

But it also let users manually configure and edit Forecast functions as custom calculations. This Forecast function is based on a R script ran by Oracle BI Server, and comes with various options and parameters enabled. For example, it allows to define the number of periods to forecast, the type of forecasting model to use (Arima, ETS) and what specific parameters for this model (error type, seasonality, trending, Box Cox, Damping...) as well as what output data to produce (value, confidence levels high and low bounds). All these options combine in a range of various possibilities for calculating the most appropriate forecast information for the use case.

A free pre-built DV Destkop project showing various combinations of these options has been posted on the Oracle Technology Network Dataviz example page here (scroll to project example 'Forecast Syntax Examples') and this YT video gives a very brief introduction of how to use this function :

The FORECAST() Logical SQL Function takes a measure parameter e.g. revenue and a variable list of time dimensions. Optional column aliases can also be used. The default FORECAST XML (filerepo://obiee.TimeSeriesForecast.xml) script file on your server or laptop can be overridden by specifying a new one in the options string. 
Syntax : FORECAST( <measure_expr>, (<time_dimension_expr>), <column_name> , <options>, [<runtime_binded_options>])

- measure_expr represents the measure, e.g. revenue data to forecast.
- time_dimension_expr the time dimension to forecast. One or more columns may be provided.
- column_name represents the output column name for forecast.
- options is a string list of name/value pairs separated by ';'

Option Name

numPeriods the number of periods to forecast Integer
the confidence for the prediction
Integer (1 to 99)
the model to use for forecasting
if TRUE use box cox transformation
the Box-Cox transformation parameter. Ignored if NULL or FALSE.
(ETS model). if TRUE, use damped trend, ie reduce effect of recent trends.
(ETS models) : controls how the nearest prior periods are weighted in the output
additive('A'), multiplicative('M'), automatic('Z')
(ETS models) : controls how the effect of trend is modeled in the output
None('N'), additive('A'), multiplicative('M'), automatic('Z')
(ETS models) : controls how seasonal effects are affecting the model outputs.
None('N'), additive('A'), multiplicative('M'), automatic('Z')
modelParamIC Information criterion to be used in comparing and selecting different models and select the best model. 'ic_auto', 'ic_aicc', (corrected Akaike IC),'ic_bic‘ (Bayesian IC), 'ic_auto'(default)

- runtime_binded_options is an optional comma separated list of runtime binded colums and options.

Some Examples from the DVD Project :
Selecting prediction confidence interval, and showing low end and high end bounds

ETS vs ARIMA : what are the differences ?

Playing with ETS Trending and Seasonality parameters, what does it mean :

Find out more by downloading the examples from our public page here (scroll to project example 'Forecast Syntax Examples').

Advanced Analytics in Oracle DV

Yes, Oracle DV gives you advanced analytics literally at the click of a button. Advanced analytics operations such as clustering, outlier detection, trend lines and forecast are built-in, and they can be simply dragged dropped on to your charts.

However access to advanced analytics is not limited to these functions exposed in the UI. You can leverage the advanced analytics framework to invoke any custom R function and seamlessly blend its results with rest of your analysis. The EVALUATE_SCRIPT function of Oracle DV can be used to execute an R script registered in the script repository.  Its powerful and easy to use.
For example, this video shows an example of using custom R script to decompose a time series data to its seasonal, trend and remainder components.

Following video is another example where use R to perform market basket analysis (rule mining) on fast foods transactions data.

Take a look at some of these examples downloadable from Oracle DV samples page on OTN.

Advanced custom SQLs as live sources for Oracle Data Viz

Oracle DV lets you define custom data sources as free SQL statements to any database connection (ORCL, SQLServer, TD...). These SQL statements can include any advanced syntaxes specific to the underlying database.

The video below shows how to define a custom SQL data-source from an Oracle DB table, and then extending it by adding a dynamic predictive clause in the SQL. Resulting data-set interacts live with user filters and allows to build impact-full visuals while leveraging the full power of underlying highly scalable databases...

Oracle Data Visualization Desktop Examples Gallery

Multiple Oracle Data Vizualisatoin public Examples are freely available download on Oracle Tech Network (OTN). 

Downloads are small sized and deploy immediately on any install of DVD. 

Visit the DVD public gallery page to see the updated gallery :