SAS OLAP Server names for cubes, dimensions, hierarchies, measures, and member properties follow these general rules:
- can be up to 32 characters in length
- can contain embedded blanks
If the name has embedded blanks or characters other than letters of the Latin alphabet, numbers, or underscores, then PROC OLAP formats the name as a name literal, which means that it is enclosed within quotation marks followed by the letter n. (Name literals enable you to use special characters or blanks that are not otherwise allowed in SAS names.) Here are some examples:
DIMENSION ‘Product@Work Dimension’n hierarchies=(Product@Work Hierarchy’n)
HIERARCHY ‘Product@Work Hierarchy’n levels=(prodtype product)
- can contain mixed-case letters
SAS stores and writes the variable name in the same case that is used in the first reference to the variable. However, when SAS processes a variable name, SAS internally converts it to uppercase. You cannot, therefore, use the same variable name with a different combination of uppercase and lowercase letters to represent different variables. For example, cat, Cat, and CAT all represent the same variable.
- do not contain periods (.)
These guidelines assume that the SAS Workspace Server is running with the VALIDVARNAME= system option set to ANY. If the SAS Workspace Server is not running with VALIDVARNAME=ANY, then you can either add code on the Submit SAS Code tab in the Advanced Options dialog box to set the option, or you can modify your names to meet the naming requirements that the server is running with. For more information about the VALIDVARNAME= option, see "VALIDVARNAME= System Option" in SAS Language Reference: Dictionary.
I created some nice data cubes using SAS Data Integration Studio recently. I had a local, regional, and state-level hierarchy for my geographical dimension as well as a monthly, quarterly, and annual hierarchy for my time dimension. I had eight measures which were vital for assessing where the organization was through time. I added drill-through to detail records capability and useful, descriptive names for all of measures, hierarchies, and dimensions. To present my cube to the staff via SAS Web Report Studio, I needed to make an information map first. I loaded up SAS Information Map Studio 3.1 (IMS 3.1) and added my cube data. I dragged my dimensions and measures over to the new information map window and noticed that the descriptive names of my measures were truncated. I went to the properties of those measures and tried to add the missing letters. Each attempt to save my edits prompted a dialog box stating “This data item label is invalid”. I counted up the characters in each of the truncated names and noticed that 30 characters was the magic number. I opened up some of my other information maps that had measure names longer than 30 characters, and they looked and worked fine – but they weren’t built from OLAP cubes.
I browsed to support.sas.com, and then drilled down to the Hot Fix page for Information Map Studio 3.1. Compared to the other SAS tools I use, there were relatively few issues addressed in the five hot fix history of IMS 3.1. Browsing down the issue list I did notice the magic number 30, and sure enough, the issue “Measure captions are limited to 30 characters for OLAP information maps” was fixed in hot fix 31MAPCNTR03. I did not have any hot fixes applied, so I downloaded the latest one (31MAPCNTR06) from ftp://ftp.sas.com/techsup/download/hotfix/ims31.html. I applied the hot fix, opened Information Map Studio, added my cube data, and created my information map without a problem.
I recently took the opportunity to sit through a SAS e-Learning course titled Creating OLAP Cubes. It was a pretty detailed walkthrough for individuals creating OLAP cubes using SAS OLAP Cube Studio or Data Integration Studio. Dimensions, levels, and hierarchies were described thoroughly, but measures were inadequately addressed. Overall, it was a fine course for staff preparing to develop their first cube, particularly for small course fee.
There were a few takeaway points from the course that were worth sharing, particularly the last one, which I learned the hard way:
- Every cube must have at least one dimension.
- You can have only one TIME dimension per cube.
- In a TIME dimension, you must order levels from the most general time period to the most specific with regard to the drill path.
- You can specify a maximum of 1,024 measures per cube.
- If you are creating a cube from a detail table or a star schema and you are including aggregated data from tables other than the input data source, then you must include measures for the stored statistics that are required for each derived statistic that you want to create for the new cube.
- When you edit the definition of a cube, any existing physical cube is deleted.
- To perform tasks on a physical cube (such as deleting, rebuilding, and tuning), you must have the appropriate file access permissions at the operating system level. If you do not have access, contact your system administrator for more information.