SYMPTOM
When creating a report from an Intelligence cube that uses a function to go back to the database such as an applysimple users may see the error "There is no data for this result. This may be because the applied filter excludes all data". Although, if the same report is ran outside of the cube the data is displayed with no error.
STEPS TO REPRODUCE
1.connected to a blank DSN as a database instance
2.Created and populated the following tables in Dbquery tool using the following text:
CREATE TABLE contacts
( contact_id number(10) not null,
last_name varchar2(50) not null,
first_name varchar2(50) not null,
address varchar2(50),
city varchar2(50),
state varchar2(20),
zip_code varchar2(10),
CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);
3.In DB Query tool populated the DSI with the following command:
INSERT ALL
INTO contacts(contact_id, last_name, first_name, address) VALUES (1000, 'Smith', 'Jane', '10 Somewhere St.')
INTO contacts(contact_id, last_name, first_name, address) VALUES (2000, 'Smash', 'Jone', '10 Somewhere St.1')
INTO contacts(contact_id, last_name, first_name, address) VALUES (3000, 'Lash', 'Jones', '15 Somewhere St.')
INTO contacts(contact_id, last_name, first_name, address) VALUES (4000, 'Trash', 'Jones', '17 Somewhere St.')
INTO contacts(contact_id, last_name, first_name, address) VALUES (5000, 'Crash', 'Jones', '18 Somewhere St.')
INTO contacts(contact_id, last_name, first_name, address) VALUES (6000, 'Slash', 'Jones', '1 Elm St.')
SELECT * FROM dual;
4.Added database Instance for new DSI to project on VM 10.27.72.64 (DI Titled "Oracle")
5.created a security filter with the cutom Id of (Titled "applysimple")
ApplySimple("UPPER(#0)","Smith")
6.Applied security filter to administrator.
7.Created cube with lastname and any other attribute.
8.Run report off cube.(Titled: "Cube broken aplysimple") No data is returned the following SQL view is seen:
Report: cube broken applysimple
Job: 198
Total Number of Passes: 1
Number of Intelligent Cube SQL Passes: 1
Number of Analytical Passes: 0
Intelligent Cubes Accessed:
[oracle]
Intelligent Cube SQL Statements:
Tables Accessed:
Table4 [F_MAIN_INDEX]: First Name, Contact, Last Name, ADDRESS, FACT_TABLE
select [Last Name]@[LAST_NAME],
[ADDRESS]@[ADDRESS]
from oracle
with Table Join Tree: [F_MAIN_INDEX]<[Last Name]@[LAST_NAME] = UPPER(Smith)>
[Analytical engine calculation steps:
1. Perform cross-tabbing
]
CAUSE
In-Memory intelligent cubes, do not and cannot support Security filters that use pass through DB functions like ApplySimple. This is due to complexities arising when trying to integrate multiple database types and versions and the conflicting syntax. For the time being the only suggestion we may offer is to not use both data base functions and intelligence cubes in conjunction.
ACTION
This issue was reviewed by our Technology team for feasibility and will not be scoped for a code fix in any upcoming Strategy release. KB440822
