MDX has a limit to the length of values used to query. Therefore, all OLAP data must maintain a format less than 256 characters in length. You can follow the steps below to reduce the size during PROC OLAP creation (using OLAP Cube Studio) and then regeneration using the PROC OLAP short form. Of course, your other option is to clean up the source data. We all know how much fun that can be :)
- Using OLAP Cube Studio, on the Level Properties for the offending Variables, change the format to below $256.
- Save the PROC OLAP code, however don't even THINK that it will run on it's own.
- Copy the first part of the PROC OLAP code and add the 'delete_physical' option. This removes the actual OLAP cubes without destroying the metadata.
proc olap cube=cube_name;
metasvr metaserver connect info
delete_physical; run; quit; - Regenerate the OLAP cube by rerunning the above code WITHOUT (of course) the 'delete_physical' option.
- And finally, you can save all of the above & update the OLAP Cube daily, weekly, monthly, whenever your heart desires in SAS Batch Scheduling.