Using SpotlightXL-Model Attributes Subtask

Overview

Attributes are characteristics associated with dimensions and are most commonly used to create alternative roll-ups for analysis and reporting. Dimension Security supports attributes in views and reports.

Suppose in a Budget model, you have Customer, Company, Product dimensions defined. For a dimension called Product, you can configure attributes like size or color. These attributes provide additional flexibility to look into your data from a different perspective.

Attributes can be used in view and reports for reporting and analysis.

Additionally, attributes are calculated dynamically at run-time. When you add or delete or change an attribute or its mapping, you do not need to recalculate the model. You can quickly map certain dimension members to an attribute and then run a view or report using that attribute and see its rollup value. Then you can make changes to the mappings to see the differences in the rollup values quickly.

Setting Up Attributes

Power user and Contributor roles can set up attributes. Reviewers can use attributes that are set up, but must first be provided access.
There are two ways to set up attributes:

• Import attributes from the Structured Planning, Consolidation, or Reporting Applications
• Create attributes within Dynamic Planning (SpotlightXL)

Importing Attributes and Attribute Hierarchies from the Structured Planning, Consolidation, or Reporting Applications

When importing attributes and attribute hierarchies from Structured Planning, Consolidation, or Reporting applications, you must define a map in SpotlightXL to transfer the data from Structured Planning, Consolidation, or Reporting applications.

In this example the VP_Resp attribute (not shown below) defined on the Department dimension in the Structured Planning, Consolidation, or Reporting applications and the ReportingResponsibility attribute hierarchy (shown below) defined on the Department dimension are mapped as attributes in the Dynamic Planning SpotlightXL application.

Attributes as Defined in the Structured Planning, Consolidation, or Reporting applications for this example is shown below.

In Practice

1. In SpotlightXL, access the Model > Map page.
2. For Source Dimension select Attribute.
3. For Source Filter, all members below VP_Resp including VP_Resp will be transfered to Dynamic Planning from the Structured Planning, Consolidation, or Reporting applications.
4. For Source Value, the VP_Resp attribute (which is the root level member of the AllReportingResponsbility attribute hierarchy as defined in the Structured Planning, Consolidation, or Reporting applications) is selected as it is the attribute in the Structured Planning, Consolidation, or Reporting applications that will be transfered to Dynamic Planning.
5. Select the Target Dimension, which is VP_Resp. You must always include the dimension when mapping an attribute from the Structured Planning, Consolidation, or Reporting applications. In this case the dimension is Department, which is included in the map shown below. Another example might be if you want to map the Color attribute defined on the Product dimension in Structured Planning, Consolidation, or Reporting applications. In this case, your map must include both the Product dimension and the Attribute color.
6. The Target Filter, like the Source Filter is MemberAndBelow so that all members below VP_Resp (including VP_Resp) will be populated in Dynamic Planning.
   Note:

   MemberAndBelow is the only source filter that is currently supported for creating and moving the attributes from the Structured Planning, Consolidation, or Reporting applications to the Dynamic Planning application.
7. Lastly, the Target Value is the attribute named VP_Resp.
8. Similar mapping is done for the attribute hierarchy named ReportingResponsiblity as shown below.
9. Also the attribute that you are mapping from the Structured Planning, Consolidation, or Reporting applications should have mapping defined to the leaf members of the base dimension in the Structured Planning, Consolidation, or Reporting applications.
   
   
   
10. Attributes mapped will be created in Dynamic Planning once the model is refreshed. To refresh the model, run the calculation by accessing the Model Calculation page. Ensure the DownloadData and DownloadMetadata types (shown below) are selected.
    
    
    
    Note:

    You can also access the Manage, Application Administration, Metadata Download page and click Run to refresh metadata.
11. Once the model is refreshed, access the Model > Setup page to verify the attributes were created for the model and associated dimension. An example is shown below.

If you want to make changes to an attribute in Dynamic Planning that is sourced from Structured Planning, Consolidation, or Reporting applications, make the change in the Structured Planning, Consolidation, or Reporting applications, and then refresh the model to reflect the same changes in Dynamic Planning.

Creating Attributes within Dynamic Planning

Navigate to Model > Attribute. The new Attribute page displays attributes associated with a selected Model and Dimension. It also provides functionality to create new attributes for a specified Model and Dimension as well as update attributes already associated with a Model and Dimension.

The image below displays the Budget Model, Account Dimension, and the associated attribute.

See: Displaying Leaf Members for detailed Enable Dimension Leaf Members property information.

• Model —Name of model to which the attribute will be or is associated
• Dimension —Name of the dimension to which the attribute will be or is associated
• Attribute— Name of the attribute associated with the model. By default, a New Attribute is displayed
• Enable Dimension Leaf Members - Select Yes to display leaf level members of an attribute’s mapped dimension in views and reports.
• Yes must also be selected to save data on attributes in views and reports. Ensure Enable Save is set to Yes for view on the Design View and/ or Design Report Properties pages. Drill through is supported for Views and Reports which have attributes when the ‘Enable Dimension Leaf Members’ property is set to ‘Yes’.
• Action— There are two actions available based on the stage of the attribute.
  • Select the Update action to update attribute data associated with a Model and Dimension with the latest changes (see Updating an Attribute).
    

The following fields are available when you select Update:

• Member Code - A unique identifier for the dimension member.
• Member Parent - The name of the parent member in the dimension hierarchy that the member rolls up to. For example, the Time dimension Parent Member might be 2016 and the Rollup Member might be Q12016.
• Member Display Label - Allows you to customize the display label. For example, if the Member Code is Jan-15 and you want to display the full name of the month, enter January-15 in the Member Display Label field.

Once the attribute is set up to map the attribute members to leaf dimension members, select Set Attribute Mapping so that all leaf members of the selected dimension will be displayed. You can type the attribute members in the attribute member code column or you can select from the drop-down and then save the attribute.
The following fields are available when you select Set Attribute Mapping:

• Dimension Member Code - Dimension code for all leaf members of the selected dimension is displayed.
• Dimension Member Parent - Parent member for all leaf members of the selected dimension is displayed.
• Attribute Member Code - Enter the attribute members here or select from the drop-down.

How to Create a New Attribute on a Dimension?

1. In SpotlightXL, navigate to Model > Dimension.
2. Select the Model you want a new attribute associated with.
3. Select the Dimension you want a new attribute associated.
4. Enter the name of the new attribute in the New Attribute field.
5. In the Member Code area, add new members. Adding attribute members is similar to adding dimension members. You can define the attribute with one root member or have multiple levels. You can also provide display label details for all the members of the attribute.
6. Map dimension members to attribute members using the Set Attribute Mapping action.
7. Save.
8. View attribute mapping details by accessing the Model Dimension page as shown below. Make sure you select the View Attribute Mapping action on this page.

Providing Access to the Attribute Page

Power users can disable access to the attributes setup for Contributor users from the Navigation Access screen as shown below.

Deleting Attributes

You can delete attributes from a generated (and unlocked) model without clearing and regenerating the model. There are no effects on data in the model.

This option is available only in SpotlightXL.

How to Delete an Attribute from a Generated Model?

1. In SpotlightXL, select Model > Setup.
2. Select the model from the Model drop-down.
3. Make sure the model is unlocked.
4. Select Model > Attribute.
5. Select the dimension that the attribute is associated with.
6. Select the attribute that you want to delete.
   
   
   
7. Click Delete from the menu ribbon. A message appears asking you to confirm.
8. Click Yes to confirm the deletion.
9. Select Model > Model Administration > Validation.
10. Select the model from the Model drop-down.
11. Review the status of the validation report to determine if any model artifacts need to be updated. You may have reports or views that refer to the deleted attribute and that need to be updated. Repeat the validation until you have made corrections to all the artifacts.

How to Use Attributes in Views?

1. In SpotlightXL, navigate to Analyze >Data.
2. Select the model/view for which you created the attributes.
3. Select the Design View subtask.
4. Click the Properties action.
5. In the Attributes section of the page(shown below), select the attribute properties for the selected view.
   
   
   
6. For Attribute Display, select from one of the following options:
   1. Attributes with Dimension - Displays the attribute and the associated dimension
   2. Attributes without Dimension - Displays the attribute only
7. Select the attributes you want to use for the view. Attributes are displayed for selection in the Select Attribute1, and Select Attribute2 fields. Note that a maximum of 2 attributes is allowed for selection in a View. The image below shows how the attributes created on the Attribute page are populated and available in the Attributes list box on the Properties page.
   
   
8. Save the view.
   Note:

   When you are using attributes in a View > Design View mode, the data will not be displayed. Data will only be displayed during run mode.
9. Navigate to Analyze > Data to run the view. The attributes defined are displayed in a way similar to page level POV as shown below.
   

Using Attributes in Reports

You can use attributes in reports as you would dimensions. A report with attributes is shown below.

To use attributes in your reports, provide required mappings for each cell in the report by accessing the Design subtask and the Design Manager action (shown below).

If you create a report from a View that has attributes, you can use the Design subtask or the Capture data option. Both options will capture attribute mappings defined in the view and carry the mappings into the report.

If you create the data intersection in an existing report or a new report, Design Manager will display the attributes in the selected model if you select the Attributes checkbox at the bottom of the Design Manager screen.

Make attribute member selections as you would dimension members. You can select up to two attributes per report.

Using POV in Reports with Attributes

When creating the dimension member or POV member in an existing report or in a new report, Design Manager will display the attributes of the selected model in the Dimension drop-down if you select the Attributes checkbox at the bottom of the Design Manager screen as shown below.

Using Attributes in Formulas to Identify Cells to Calculate

You can use Attributes within formulas to identify the data cells that should be modified by the formula.

Then define a formula that assumes that freight costs will be higher for products with an Olympics logo on them. In the example below, freight costs per unit are increased by $2 for products with a 2-color Olympic logo on them.

If you want to set the year and attribute value just before running the calculation, you could add variables to the Filter Value. Regular dimensions and Attributes can both use variables.

You can also use variable expressions for the Filter Value. In the following example, the 4th quarter of the year and the first logotype in the Olympics attribute will be calculated with the formula. Regular dimensions and Attributes can both use variable expressions.

For more information on expressions, see Example Substitution Variable Expressions.

To run the formula, create a calculation that includes a row for the formula to be executed and defines the variables. Then add a row to aggregate the parents affected by the formula, if desired. In the example below, Scenario and Year is a separately defined Scope with variables @Scen@ and @Year@.

Best Practice

Use attributes in formulas only if needed.

Using Attributes in Maps for Leaf Data Transfer

You can use Maps with Attributes to copy leaf-level data from one analytic model to another, from a master model to an analytic model, or from an analytic model to a master model. This feature is not designed to transfer data from HACPM_Financial to analytic models.

Attributes provide a way to group a set of leaf dimension members. The group can be composed of leaf members at different levels or in different parent structures within a dimension. Thus, it may be more efficient for you to copy leaf data between models using attributes to describe the set of members, instead of specifying each member you want to copy.

Attributes do not have data associated with them directly. Attributes point to base dimension leaf members which have data associated with them. When you transfer data to or from an attribute, you are copying data to or from the associated base dimension leaf members.

The Map must be of Type Data and must Transfer only Leaf data.

There are three methods to choose from:

• Attribute in Source to Attribute in Target (one to one)
• Attribute in Source to Dimension in Target (one to one)
• Dimension in Source to Attribute in Target (one to many)

Attribute in Source to Attribute in Target (One-to-One)

This method copies the data for source base leaf members to corresponding target base leaf members.

• Specify Filter Type LeafMembers in Source to LeafMembers in Target, or MemberAndBelow in Source to MemberAndBelow in Target.
• Specify Match Criteria Common if the base leaf members associated with the source and target are not identical.
• The attribute member names can be the same or different between source and target. For example, the attribute name in the Source could be USA/Can and the attribute name in the Target could be US and Canada.
• You can use variables and variable expressions with attribute and dimension member names.

Example 1

The following example copies leaf-level data from Everest Cycles to My Cycles, and both models have a Region attribute. Leaf data associated with the Region attribute USA/Can is copied to My Cycles.

A scope defines which data in My Cycles will be used in the calculation. In this case, no variables are in use, but you can use them if you wish.

Here is a look at the Target data before the calculation is run.

A calculation clears data in the Target, runs the map, and aggregates data.

Here is a look at the Target data after the calculation is run.

Attribute in Source to Dimension in Target (One-to-One)

This method copies the data for source base leaf members to corresponding target dimension leaf members.

• Specify Filter Type LeafMembers in Source to LeafMembers in Target, or MemberAndBelow in Source to MemberAndBelow in Target.
• Specify Match Criteria Common if the base leaf members associated with the source and target are not identical.
• The source attribute member names can be the same or different than the target dimension member names. For example, the attribute name in the Source could be USA/Can and the dimension member name in the Target could be North America.
• You can use variables and variable expressions with attribute and dimension member names.

Example 2

The following example copies leaf-level data associated with the Region, USA/Can attribute from Everest Cycles to the America's hierarchy in the Company dimension in My Cycles.

A scope defines which data in My Cycles will be used in the calculation. In this case, no variables are in use, but you can use them if you wish.

A calculation clears Target data, runs the map, and aggregates data.

Here is a look at the Target data after the calculation is run.

Dimension in Source to Attribute in Target (One-to-Many)

This method copies the data from a source dimension member to all corresponding target base leaf members associated with the attribute.

• Specify Filter Type FixedMember in Source to LeafMembers in Target.
• Match Criteria is ignored on the row that refers to the attribute.
• The source dimension member names can be the same or different than the target attribute names. For example, the dimension member name in the Source could be Europe and the attribute name in the Target could be EMEA.
• The source dimension member can be a roll-up member or a leaf member. Its data will be copied to each of the leaf base dimension members associated with the Target attribute. You copy data for only one source dimension member at a time.
• You can use variables and variable expressions with attribute and dimension member names.

Example 3

The following example copies the data for the fixed member Europe in the Company dimension from Everest Cycles to all base dimension leaf members associated with the EMEA hierarchy in the Region attribute in My Cycles. This is a one-to-many operation.

Note that Europe is a parent-level member. You can use any level of member as the Source.

A scope defines which data in My Cycles will be used in the calculation. This example uses variables. 

Here is a look at the Source data in Everest Cycles for Europe.

A calculation clears Target data, runs the map, and aggregates data.

Here is a look at the Target data in My Cycles after the calculation is run. Notice that the same data was copied to each base leaf dimension member that is associated with the EMEA attribute. This is a one-to-many operation.

Using Attributes in Maps for Metadata Transfer

You can copy Attributes along with their base dimension member mappings from a Source model to a Target model.

Each Attribute is associated with a dimension, and attribute members are mapped to the leaf members in the base dimension. You can copy both the attribute and the mappings from one model to another. This feature is not designed to transfer data from HACPM_Financial to analytic models.

• The Map must be of Type Metadata or Both.
• The Transfer type must be Leaf if used with map type Both.

When copying Attribute definitions and mappings, the following rules apply:

• The Target model must be cleared and regenerated as part of the process.
• The Source Filter Type can be AllMembers, LeafMembers, or MemberAndBelow.
• The Target Filter Type can be AllMembers, LeafMembers, or MemberAndBelow.
• Match Criteria Common is assumed, by default, on the row in the map that refers to the Attribute, whether you specify Common or not.
• Lookups are not supported for attributes.
• You can use variables and variable expressions with attribute and dimension member names.

The Attribute is created in the Target model and the dimension member mappings are copied for all dimension members that are common between the source and target.

If the Target model already has the same Attribute, its attribute hierarchy and mappings are deleted and replaced with the attribute hierarchy and mappings from the Source. The display labels originally assigned to the Target Attribute members are retained.

Example

The following example copies the attribute Olympics and its mappings on the base dimension Product from Detailed Revenue to My Cycles.

Here is a look at the attribute and mapping in the Source model.

Here is a look at the Product dimension in the Target model, My Cycles, before the calculation is run. My Cycles has another Attribute called Intended Use that is unrelated to the Olympics Attribute that will be copied. It has many product codes that are different than the products in Detailed Revenue.

A calculation clears the Target model and regenerates it. When generating a model, only one map for that model can be of type Both or Metadata. Because the map we created is of type Metadata, the GenerateModel step automatically runs that map to create and move the metadata from the Source model.

Here is a look at the Product dimension in the Target model after the calculation is run. The Attribute has been copied along with the mappings.

Displaying Leaf Members of an Attribute’s Mapped Dimension

This feature allows you to display leaf level members of an attribute’s mapped dimension in views and reports. For example, let’s say you have an attribute called Product Type, which is an attribute of the Product dimension. Leaf level members of this Product dimension include; electric, single gear, and competitive. Within a view or report, you can now select the Product Type attribute and view the Product leaf level dimension members (electric, single gear, and competitive).

To enable this feature, access the Model task and the Attribute subtask. Select Yes for Enable Dimension Leaf Members as shown below.

Now, when you view the Product Type attribute in a view or report for the Expenses model, you can see the leaf level dimensions for the Product dimension. An example is provided below.

In the example above, ’20 inch’ is the product type attribute member whereas ‘B006742’, ‘B006813’, and ‘B006834’ are the product dimension members.

If Enable Dimension Leaf Members is set to No, the leaf level dimension members will not be available to view in a report or view.

In some cases, selecting Yes for Enable Dimension Leaf Members may result in a duplication conflict due to your naming convention for dimension and attribute members. For example, you might have a dimension named Sales in your dimension hierarchy. You might also have an attribute named Sales in your attribute hierarchy. This conflict may occur due to the attribute Member Code or Member Display Label. For the application to respond properly, you must change the Member Code or Display Label of the attribute.

To change the Member Code or Display Label of the attribute, access the Model task and the Attribute subtask. Select the Model, Dimension, and Attribute. Change the Member Code or Display Label and click Save.

When Enable Dimension Leaf Members is set to Yes and a duplicate is detected by the application, an email will be sent to the user. You can also view duplicate members, by accessing the Model task and the Model Validation subtask. The Model Validation page as well as the duplicate Sales Member Code (which is SALES) for the AllReportingResponsibility attribute is shown below.

The fields under the Duplicate Attribute Member Codes heading are described below.

Limitations

• A maximum of 5 attributes per Dimension is allowed
• A maximum of 2 attributes in a View is allowed
• A maximum of 2 attributes per Model in a Report is allowed
• The attribute name must be unique
• The attribute name cannot match any dimension name for the model
• Attribute name cannot contain "." (dot) character.
• The use of special characters in the attribute name is not recommended
• "New Attribute" is a reserved keyword and cannot be used as an attribute name
• Drill through functionality in Views and Report is not supported if attributes are enabled
• Data cannot be saved from the View / Report if attributes are being used in that View / Report
• When you are using attributes in a View, data will not displayed in design mode i.e. Analyze > Design View. Data will only be displayed only during the run model i.e. Analyze > Data