Unable to display detail data From An OLAP Cube Report

[databobjr]

I built a star-schema cube (using OLAP Cube Studio 4.2) and used the information map to create a full-join view for drill-through table. I was able to run test query in information map and registered the information map to a libref which was created used SAS Information Map Library. I was able to build the cube without error, viewed the cube and created a report using Web Report Studio 4.2 M2.

However, when I tried to drill to the detail data, which was using the information map view, I got the following error:

Unable to display detail data
The immediate MDX execution failed
# select * from ...
# ERROR: SQL passthru expression contained these errors: ERROR: File LIBIM.fact_full_join_map.DATA does not exist.... ERROR: The metadata for the table/view LIBIM.fact_full_join_map does not exist.
# The server encountered an error accessing a source data table.

Could anyone please help?

Thanks,
Bob

[AngelaHall]

It sounds as if the security settings for the library & or table/view are not setup to allow access for drill-through. Read through the SAS Documentation "Security for Drill-Through Tables" found on page: http://support.sas.com/documentation/cdl/en/olapug/59574/HTML/default/viewer.htm#a003212372.htm


~ Angela

[databobjr]

Hi Angela:

Thank you very much for your reply!

I checked on the document (I pasted at the bottom of this message) and I added the SAS Trusted User to have access to the information map. I also added the following line to the cube script:

LIBNAME LibIM SASIOIME MAPPATH='/Shared Data/Reports/Metadata';

LibIM was the library I created for the drill through table.

However, I still got the following message:


ERROR: SQL passthru expression contained these errors: ERROR: File LIBIM.fact_full_join_map.DATA does not exist.... ERROR: The metadata for the table/view LIBIM.fact_full_join_map does not exist.

Where is the file LIBIM.fact_full_join_map.DATA located? Or where is the metadata located? How do I check? I created a data library call INFORMAP and the location was /Shared Data/Reports/Metadata. Is the metadata location be /Shared Data/Reports/Metadata ?

I am very new to SAS BI tools. And, thanks again for your help.

Bob.


Security for Drill-through Tables


Different users of cube data may have different security and access restrictions that must be adhered to and applied when querying the underlying data for a cube. When selecting a data table for drill-through, you may need to define user restrictions for certain data in the drill-through table. The SAS Metadata LIBNAME engine is used to assign the drill-through table library on a per-session basis. This allows client credentials to be used when determining which columns the user can see. Columns which have ReadMetadata permissions denied on the drill-through table will not be visible to the user. If access is denied for a column on the drill-through table, that level must also be denied Read access in the cube. Conversely, if a level is denied Read access in the cube, that column in the drill-through table must have ReadMetadata access denied. For more information on the SAS Metadata LIBNAME engine see the topic "Pre-assigning Libraries" in the SAS Intelligence Platform: Data Administration Guide.

Because of this enforcement, the following changes could affect your site:

•User-defined formats will not work if the FORMATS catalog is accessed by the same libref as the data. You must specify a different libref for the FORMATS catalog or move the user-defined formats into a different location and assign a new library.

•If SAS Trusted User does not have ReadMetatdata permissions on the library definition, then the drill-down functionality fails. You can grant ReadMetadata permission to the SAS Trusted User by using SAS Management Console.

•You cannot drill down in an OLAP cube if there are discrepancies between the physical table and the metadata that is defined for the table. You can update the metadata for the table by using the Update Metadata function in SAS Management Console or SAS Data Integration Studio. If the table is part of a job in SAS Data Integration Studio, then check the code for the job to verify that there are no LENGTH statements that would cause a mismatch to occur.

Note: For further information on drill-through tables, see Defining Drill-Through Tables.


--------------------------------------------------------------------------------

[AngelaHall]

Sorry for the delay Bob. The last couple weeks have been crazy. Are you still working on this item? My guess is that there is an issue with dynamically assigning the SASIOIME library.

Could you try to add an library assignment path before your PROC OLAP code (or include it in the Cube Creation GUI: on the first screen for creating/edit the cube, click 'Advanced' and move to 'Submit SAS Code' tab).

Included is the meta libref:
libname LibID meta liburi="SASLibrary?@name='LibIM'";

Included is a reference on methods to preassign libraries for OLAP Server: http://support.sas.com/kb/12/215.html

~ Angela

[databobjr]

Thank you very much Angela and sorry for the late reply. I also took a detour for something else.

I tested it last Friday and it work. Thanks again.

For those are newbies like me, if you are not sure how to get the libname, etc., you can get LIBNAME Statement from the management console. Here are the steps:

After logging on to management console:
1. Expanded the Data Library Manager folder
2. Expanded the Libraries folder
3. Highlight the library which you want to get information from.
4. Right mouse click and select Display LIBNAME Statement.
5. Copy the LIBNAME statement.

Bob.