I used an oracle view as my drill thought table. It worked fine in my development server. But when I moved it to the production server, it gave me the following error when I tried to drill to the details:
The immediate MDX execution failed select * from ... ERROR: SQL passthru expression contained these errors: ERROR: Incorrect length in SAS Metadata Repository for column xxx.... ERROR: The following columns were not found in the contributing tables: xxx.The immediate MDX execution failedThe server encountered an error accessing a source data table.
I used olap cube 4.2 M2 and Metadata server 9200
I did use the management console to update the metadata.(right mouse click and select update metadata). But it still did not work. I also deleted and registered the table again. But it still did not work.
On thing I may need to point out, in development, we use a single server for all the SAS component. In production, we used multi servers. But I don't see this is a problem. Is there a log I can check for more details?
Sorry for the delay. Were you able to get any further on this issue? Outside of the metadata server log, you might find some information in the workspace server log files. (\Lev1\SASApp\WorkspaceServer\Logs).
If you can, can you then compare the properties of the table here with what was imported from Mgmt Console? There are some issues with pulling DBMS tables into Mgmt Console & this might be what is happening. Here is an example of one: http://support.sas.com/kb/37/118.html
I just found other error information from the web studio log:
2011-02-21 17:52:09,483 [http-0.0.0.0-8180-7] ERROR  com.sas.commands.olap.ShowDetailDataCommand - Drill through cannot be performed. No valid OLAP connection to get Show Detail results.Please contact your data administrator.
java.lang.IllegalStateException: Drill through cannot be performed. No valid OLAP connection to get Show Detail results.Please contact your data administrator.
The source for the drill-through is Oracle, correct?
"Most connections to the data are performed using the OLAP Server's
process ID. The exception to this is a SAS/ACCESS library against a
third-party RDBMS when the USER option has been specified."
My guess with the error below is that the OLAP Server process id is trying to connect to Oracle and doesn't have access.