Model Secondary Dimensional Attributes

Dimension attributes that are not the dimension's key, and not part of a hierarchy are called secondary attributes. Secondary attributes cannot be used to create relationships between datasets and dimensions.

Attribute Type: Dimensional Secondary Attributes

The default Secondary Attribute Type provides an independent "Dimensional" attribute for grouping Measure data. Dimensional Secondary Attributes may be referenced from AtScale Calculated Measures by following the canonical MDX syntax of [Dimension Name].[Hierarchy Name].[Level Name], however the "Hiearchy Name" and "Level Name" are both set to the Attribute's Name. For example, if you define a secondary attribute of "QuarterOfYear" on a hierarchy of the Date dimension, then such an attribute is referenced from a Calculated Measure as: [Date].[QuarterOfYear].[QuarterOfYear]. Note that the secondary attribute reference is independent of the Design Center Hierarchy that displays it.

Attribute Type: Level Alias Secondary Attributes

Secondary Attributes of type "Level Alias" enable the creation of tabular reports that select hierarchical expressions without forcing the user to drill down a hierarchy. For example, consider a tabular report of Monthly Sales vs Last Year Month Sales. The "Last Year Month Sales" calculation is easily computed using an AtScale ParallelPeriod function as follows:

(ParallelPeriod(
   [Order Date Dimension].[Order Retail445].[Order Reporting_Year], 1,
   [Order Date Dimension].[Order Retail445].CurrentMember),
   [Measures].[salesamount1])

Because this expression uses a hierarchy's CurrentMember function, it is necessary for the report designer to include the [Order Date Dimension].[Order Retail445] hierarchy in the report. When including a hierarchy in the report, most BI Tools force the user to navigate the hierarchy to the desired level, month in this case. Besides being a usability inconvenience, this interaction pattern causes the calculation of level subtotals that the report designer is not interested in.

These problems can be avoided by using a Secondary Attribute that aliases a hierarchy level. In this case, the problem is solved by defining an "Order Month" secondary attribute as an alias for the [Order Date Dimension].[Order Retail445].[Order Month] level. The report designer can then use the "Order Month" Secondary Attribute with the "Last Year Month Sales" calculation without forcing the user to navigate a hierarchy. As a result, the BI Tools display the data at the Month level without forcing the navigation of a hierarchy and without computing unnecessary subtotals.

When configuring the Secondary Attribute as an alias of a level, it is important that Calculated Measures continue to reference the Hierarchy Level directly. Do not rewrite the calculated measure to reference the level alias. Doing so executes an inner query which may return different results than expected.

Attribute Type: Metrical Secondary Attributes

Metrical Secondary Attributes are an experimental feature that must be enabled by your system administrator. See Add or Edit a Measure within a Dimension for more information.

Procedure

1. Open the dimension editor canvas.

2. Drag a dataset column directly on top of an existing level in the Hierarchies panel. The Create Secondary Attribute dialog appears.

3. Set properties of the attribute.

   Field	Description
   Display Name	The user-friendly name for the dimension level attribute.
   Query Name	The SQL or MDX column name for this dimension attribute in the published AtScale cube. The query name is how the AtScale engine refers to this attribute internally. This should not be changed after the cube is published, as changing the query name may break existing data sources and reports that rely on this cube.
   Description	This is a description of the data that this attribute contains. Some BI tools can show this description, some can't. It's a good practice to enter descriptions to help other people get familiar with the data in the cube.
   Attribute Type	Dimensional indicates that this is a Secondary Dimensional Attribtute. Level Alias indicates that this attribute is an alias for the Level selected under the Level Alias control. Metrical indicates that this attribute is a Measure; this type can only be set if your system administrator has enabled the experimental feature. For more information about these types see the sections above.
   Level Alias	Indicates which Hierarchy Level this attribute aliases. Only visible when Attribute Type is set to Level Alias
   Dataset	The source dataset that contains the column that the attribute is based on.
   Key Column(s)	The dataset column that a dimension attribute is based on. If the attribute has a compound key, click Add Key Column to specify other columns that comprise the attribute's key.
   Value Column	The dataset column that the attribute is based on.
   Sort Column	The column to use to sort the values in result sets. This applies to MDX queries only (i.e. queries received through the 'xmla interface').
   Exclude from System-Generated Dimension-Only Aggregates	Excludes this attribute from system generated dimension-only aggregates. Useful if the attribute contains a large number (e.g. millions) of distinct values that you don't want to aggregate.
   Exclude from System-Generated Fact-Based Aggregates	Excludes this attribute from system generated fact-based aggregates. Useful if the attribute contains a large number (e.g. millions) of distinct values that you don't want to aggregate.
   Contains Unique Names	Enable if each member of this attribute has a unique name. This must NOT be enabled if two members have different keys but the same name.
   Custom Empty Member	Enable this property to ensure un-joinable values such as NULL that may reside in the Foreign Key columns of the fact dataset are included in your query results. The Custom Empty Member feature provides the convenience of handling NULL values so Data Architects are not forced to fix their fact tables. For maximum compatibility with BI Tools, you will have to add an empty member record to the dimension dataset. To learn more about Custom Empty Members, see Custom Empty Members for Levels and Attributes.
   Visible?	If selected, the attribute will be visible in the cube.
   Folder	(optional) The name of the folder in which to display this attribute in BI client software, such as Microsoft Excel and Tableau. If your cube has a lot of attributes, folders are a good way to organize them.

4. Click Save.