Skip to main content

Custom expression does not respond to multiple filter values while using Oracle Essbase in-db connection in TIBCO Spotfire

Rich Talbot
  • Created
Date Posted:
Product: TIBCO Spotfire®

Problem:

Custom expression does not respond to multiple filter values while using Oracle Essbase in-db connection in TIBCO Spotfire


Solution:

If you have custom expression in TIBCO Spotfire visualizations while using Oracle Essbase in-database connection (external data), the custom expression may not respond to multiple values selected in filter selection.

For example, below is the cross table visualization showing Sales, Cost Price and Profit (Sales - Cost Price = Profit) with all values selected in Product filter. Note that data is external from Oracle Essbase and not imported in TIBCO Spotfire and Profit is built using a custom expression. The Oracle Essbase in-database connection supports only basic operations like +, -, /, * and all four result in same behavior.
User-added image

The MDX query generated will be of this format: 

WITH
  MEMBER [Measures].[__Sales__] AS
    'AGGREGATE({[400], [Diet]}, [Sales])', SOLVE_ORDER=255
  MEMBER [Measures].[__Profit__] AS
    'AGGREGATE({[400], [Diet]}, [Profit])', SOLVE_ORDER=255
  MEMBER [Measures].[Column_1_5] AS
    'IIF(ISEMPTY([Measures].[__Sales__]) AND ISEMPTY([Measures].[__Profit__]), {}.ITEM(0), ([Sales]) - ([Profit]))', SOLVE_ORDER=255
SELECT
  {[Measures].[__Sales__], [Measures].[__Profit__], [Measures].[Column_1_5]} DIMENSION PROPERTIES GEN_NUMBER, ANCESTOR_NAMES ON 0,
  NON EMPTY HIERARCHIZE(DISTINCT([Quarter].MEMBERS)) DIMENSION PROPERTIES GEN_NUMBER, ANCESTOR_NAMES ON 1
FROM [DxpDemo].[BasicDB]
The Oracle Essbase in-database connection uses MDX queries for accessing the cube. So queries generated also use MDX language and in above scenario, the MDX AGGREGATE function can be used to get the "correct" value. But this function can only aggregate "measures" in the cube, that is, it cannot aggregate an expression (A+B or A/B). This behavior is as per design, Oracle Essbase only allow one member from each dimension on the slicer axis (For example, Microsoft SSAS allow multiple members from each dimension) that is why AGGREGATE function is used for applying filters on measures.

You can use either of the approaches in order to have the correct values:
1) Create a dynamically calculated account member (dynamically calculated measure) defined as a formula in the Essbase cube

Here is an example on an formula based measure in Essbase Administration Console:
User-added image

2) Import the data in subsets by breaking up the queries so time consumed will be less and data can be merged in one data table through Insert > Rows. Doc: Working with data from Oracle Essbase External: Slicer Axis

Comments

0 comments

Article is closed for comments.