![]() | ![]() | ![]() | ![]() |
To perform more in-depth analysis, users often want to drill-through to the detail records that constitute a particular crossing in an OLAP-based table. By default, drill-through queries are processed on the SAS® OLAP Server. In some cases, when the amount of data in the cube or detail table is extremely large, returning those results might cause resource and out-of-memory issues on that server.
As an alternative, you can use report linking to leverage the workspace server, which is designed to handle larger amounts of data. This sample provides some tips and techniques to make report linking between OLAP-based reports and relational (detail) reports work more efficiently.
This sample uses two information maps.
One is a multidimensional information map that is built on a simple cube, which was created from the SASHELP.PRDSALE data set. The information map contents are shown in the following display.
The other is a relational information map that is built on the SASHELP.PRDSALE data set. The relational map is used for the target report that contains the detail data. Some extra steps are recommended to prepare the relational information map.
This section provides some tips for preparing the relational information map for linking.
Change numeric data items to character and ensure that the format length matches what is defined in the OLAP cube
In this sample, Quarter is defined with a length of 8 in the cube, so a new data item is created to convert Quarter to character and ensure that the length is 8. One way to accomplish this is to use an expression similar to the following:
Tip: The data items that are converted to character can be set to hidden in the final report.
Create filters for each of the data items to be used in the linking
The next step is to define filters for any level of any dimension that might be displayed in the OLAP-based report. In this sample, filters were created for Product Type, Product, Year, and Quarter. The following steps illustrate the best practices for defining these filters with the All option set as default.
The final result
The following display shows the final relational information map based on SASHELP.PRDSALE.
Tip: After you have saved this view, use SAS® Management Console to deny WriteMetadata to all of the users accessing this report. This denial will prevent users from saving over your default view definition. It will also suppress the "Changes that you made to the current report have not been saved" message when navigating between the linked reports.
Display the OLAP-based report and click on a particular cell.
The detail records of that cell are displayed in the relational report. The report linking passes all of the proper parameters and uses (all possible values) for anything that is not passed.
For more information about SAS Web Report Studio, see the SAS Web Report Studio documentation page.
For more information about SAS Information Map Studio, see the SAS Information Map Studio documentation page.
For more information about creating cubes, see the SAS OLAP Server and SAS OLAP Cube Studio documentation pages.
For more information about SAS Management Console, see the SAS Management Console documentation page.
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.