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.


Now, if you select Product = Fruit Soda in filter, you will see correct values as seen below:


Now, select more than one value in Product filter, you will see the Profit column does not correct data instead it shows the data seen when all values are selected in filter


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]

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

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:


This account is not "dynamically" calculated so we edit member properties and change the data storage to "Dynamic calc":


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

• https://docs.tibco.com/pub/sfire-analyst/7.11.1/doc/html/en-US/TIB_sfire-analyst_UsersGuide/connectors/cubes/connect_working_with_data_from_oracle_essbase.htm

External: Slicer Axis

• https://docs.oracle.com/en/cloud/paas/analytics-cloud/adess/using-slicer-axis-set-query-point-view.html
• https://docs.oracle.com/en/cloud/paas/analytics-cloud/tress/mdx-slicer-specification.html