Limit the number of drill-through to detail records available from OLAP cubes


Drill-through to detail is the ability to right click within a cell of a web report or OLAP viewer and request the detail source records that make up that specific cell's measure. The maximum number of records, by default, is set to 300,000. Feasibly the report user could download all of your source records (if the source is smaller than 300,000 records) from the top level of the OLAP cube. Of course we all know that allowing users to grab everything not only eats server resources it does not encourage using the OLAP cube for what it's real purpose is - adhoc slicing and dicing of the data.

Therefore, for many organizations, it might make sense to reduce the maximum number of records available to something much smaller. 1000 records, 5000 records, something agreeable ... but something that causes them to use the OLAP cube first to filter/analyze information and then drill through to the source records. Training is imperative when implementing this, since users will quickly get confused when the number of records in the OLAP cube does not match the number or resulting records expected in the drill-through data.

SAS Administrators can  implement this change, server-wide, in SAS Management Console. Navigate to the OLAP Server Advanced Options screen found in the properties.

´╗┐Select the Server tab and modify the 'Maximum number of flattened rows' value from 300,000 to something smaller. In the example below, I've changed the value to 30.

The resulting data queries only provide 30 records of data to the users. In the example below, I have requested all of Bulls Eye Emporium data but only 30 records (on the right) appear.


About Author

Angela Hall

Senior Technical Architect

Angela offers tips on using the SAS Business Intelligence solutions. She manages a team of SAS Fraud Framework implementers within the SAS Solutions On-Demand organization. Angela also has co-written two books, 'Building BI using SAS, Content Development Examples' & 'The 50 Keys to Learning SAS Stored Processes'.

Related Posts

Leave A Reply

Back to Top