A new formatting mechanism is added to Measure to allow you to control the cube column names. The new pattern field in the Measure Dialog allows you to enter a pattern, including variable substitutions that will become the column name.
Using the above traditional name as an example, here are some patterns and their outputs:
-
[blank] -> US/Oregon/Married/Count(employeeid) // backwards compatible -
${*} -> US/Oregon/Married/Count(employeeid) // all, as before -
${0} -> US // extract using index -
-${1}-${0}-${2}- -> -Oregon-US-Married- // another index eg. -
${hierarchy} -> US/Oregon/Married // without the measure -
${measure} -> Count(employeeid) // without the hierarchy -
${hierarchy}/${measure} -> US/Oregon/Married/Count(employeeid) // all -
${field} -> employeeid // just the field name -
${fn} -> Count // just the measure fn -
${fn} of ${field} -> Count of employeeid // varying formatting
Note after setting these patterns, you should Infer-schema to update and check your column names - otherwise downstream processors won’t see the change. Also, it is your responsibility to avoid or handle duplicate column names - eg. ${0} -> US is likely to result in a lot of “US” columns. The best value to use for the CrossTab sample is ${hierarchy} this should save a bit of processing downstream.
Also, these changes only apply to the “flattened” cube output to the next processor, they don’t affect the interactive hierarchical cube view in Ensemble and Perspective - these still show the full hierarchy as before.