| This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post. |
Have you ever had a requirement to display additional details about data in a report but couldn’t find a good way to do so? Showing OLAP Member Properties in a SAS Web Report could be a useful way to sneak more details about data into a crosstab table.
Crossing Multiple Dimensions with High Cardinality Creates Data Headaches
I recently ran into a data challenge with a large OLAP cube (NWAY of about 12 million records) where I needed to summarize a simple count by a “group ID” crossed with a “group name” crossed with another insignificant dimension. There were a large number of “groups” – over a 1000 distinct group IDs with matching group names. Since the cardinality between the “group ID” and “group name” with the source data was pretty high, the OLAP query took a very long time to run and ultimately failed due to a lack of memory. Rather than figuring out how to cross two dimensions with high cardinality (which is not going to be realistic), I used the “group name” to define a member property of the “group ID”. Since the group name had a one-to-one relationship with the group ID, the member property makes sense. When defining a member property, remember the additional value you want to show needs to have a one-to-one relationship with the OLAP member level it is being applied to.
In the past I built additional columns in the source data, which concatenated the cryptic ID number with the name or description. With OLAP member properties this practice becomes obsolete and allows you to avoid creating redundant data.
Define Member Properties in an OLAP Cube
Before displaying member properties in a web report you have to first define them in the OLAP cube. To demonstrate, I have created two member properties of the Product level in my sample Candy Sales cube from SAS Global Forum 2012 (Paper 020-2012).
PROPERTY ‘Gross Margin’n
level=Product
column=GrssMrgn
hierarchy=(Products)
caption=’Gross Margin’
description=’Gross Margin’
;
PROPERTY ‘Retail Price’n
level=Product
column=Retail_Price
hierarchy=(Products)
caption=’Retail Price’
description=’Retail Price’
;
I can define these properties of the Product because they exist in the same table as the Product level itself:
SAS Support has additional documentation about OLAP member properties.
Showing Member Properties in SAS Web Report Studio
When editing a report in SAS Web Report Studio you can display member properties by right-clicking the crosstab table object and selecting Member Properties. The next window will list the member properties available – simply check the ones you want to show!
This is what the “Gross Margin” and “Retail Price” member properties look like in the Web Report. Notice I have defined TWO properties of the Product member 
.
If we defined the “Gross Margin” and “Retail Price” as individual dimensions in the OLAP cube, the MDX query submitted to the OLAP server would be crossing FOUR dimensions (Product, Gross Margin, Retail Price, and Time). Now we have only crossed TWO dimensions (Product and Time) – which in some cases can dramatically reduce the tuple size of an OLAP query.
Nothing was done inside the SAS Information Map used in this web report. The nice thing about displaying Member Properties is that it reduces the complexity of the NWAY aggregation in the OLAP cube which in turn results in faster query response because of a less complex MDX query. The physical data of the OLAP cube will also be a lot smaller in cases where there is a ton of source data.
I have added the PROPERTY statements described above into my SAS Global Forum 2012 Candy Sales cube code. You can find the OLAP code at my website under the 020-2012 paper section.
| This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post. |