Lowering the level of fact data: Fact degradations

Degradation, which lowers a fact level, is the logical opposite of aggregation. To view fact data at a lower logical level than the fact is stored at, you must degrade the fact to a lower level. This scenario may occur because you stored a fact at a level that is used most commonly in reports. However, you must support those users who wish to view and analyze the same fact data at a lower logical level.

For example, the Human Resources Analysis Module includes a Planned Compensation fact that is stored at the Department level, and has a fact degradation to the Employee level (the attributes, facts, and metrics used in this example can all be found in this Analytics Module). The fact extension does not use an allocation expression to degrade Planned Compensation to the Employee level. This causes every employee to be listed with the same planned compensation value as the employee’s department, as shown below:

The analytical value of this fact degradation is not immediately recognizable. However, now that Planned Compensation is available at the Employee level, you can create more meaningful analysis with other fact data that is stored at the Employee level. For example, the Compensation Cost fact is stored at the Employee level. The metric Actual as % Planned Compensation has been created to calculate the actual compensation of an employee as a percentage of the planned compensation for the entire department of the employee. The metric definition is ([Compensation Cost]/[Planned Compensation]), which performs a division of metrics defined from the Compensation Cost and Planned Compensation facts, respectively. You can now view what percentage of your planned compensation per department has been spent per employee, as shown below:

Without using a degradation of Planned Compensation to Employee, you could not include Department and Employee on a report with these metrics and return accurate values.

The following procedure steps through how to create the fact degradation that has been created for the Planned Compensation fact of the Human Resources Analysis Module. The procedure also describes general principles of creating fact degradations which you can use to create fact degradations for the facts in your project.

To define a fact degradation

1 In Developer, log in to the Human Resources Analysis Module.
2 Browse to the Facts / Compensation / Planning folder and double-click the Planned Compensation fact to edit it. If a message is displayed asking if you want to use read only mode or edit mode, select Edit and click OK to open the Fact Editor in edit mode so that you can make changes to the fact. The Fact Editor opens.
If you are only given the option of opening the Fact Editor in read only mode, this means another user is modifying the project’s schema. You cannot open the Fact Editor in edit mode until the other user is finished with their changes and the schema is unlocked.
For information on how you can use read only mode and edit mode for various schema editors, see Using read only or edit mode for schema editors.
3 Click the Extensions tab.
4 Select Degradation to Employee and click Modify. The Level Extension Wizard opens.

To create a new fact degradation you would click New. However, this example steps through how the Planned Compensation fact degradation Degradation to Employee was created.

5 Read the Welcome statement and click Next. The General Information page opens.
6 Enter a name and a description for your fact extension (already provided). Then select whether you want to:
Lower the fact entry level: define a fact degradation
Extend the fact entry level: define a fact extension on a table relation, dynamic fact relation, or a cross product join (see Defining a join on fact tables using table relations and Defining a join on fact tables using fact relations)
Disallow partially or completely the fact entry level: define a fact extension that does not allow a fact to be reported at a certain level (see Disallowing the reporting of a fact at a certain level)

For this example you are creating a fact degradation so select Lower the fact entry level, and click Next. The Extended Attributes page opens.

7 Select the attributes you want to degrade the fact to, allowing the fact to be reported at the new level. For this example Employee is already selected. Click Next. The Join Type page opens.

To extend the fact so that it can be reported at any level in a hierarchy, choose the lowest level attribute in that hierarchy.

8 Select what attribute(s) to perform the join. For this example, the Department attribute is already selected. Click Next. The Join Attributes Direction page opens.
9 You can choose to join using the attribute, or join using the attribute and its children. For this example, the join is performed on the Department attribute and its children. Click Next. The Allocation page opens.
10 Enter an allocation expression that calculates the fact at the new level. For this example, you do not need to include an allocation expression. See Fact degradations with allocation expressions for an example of using an allocation expression for a fact degradation.
11 Click Finish to create the fact degradation.

Fact degradations with allocation expressions

Not all fact degradations can simply be lowered to a new level. Ordinarily, you must add an allocation expression, which allows the distribution of values according to a calculation you specify, to change the definition of the fact in a level extension.

This is similar in concept to choosing an aggregation function (Sum, Avg, and so on) when aggregating data to higher levels.

For example, if your fact is stored at the yearly level and you want to report the data at the monthly level, you can create a degradation on the fact to relate it to the monthly level. You select Month to be the attribute to which to degrade. You then specify that the allocation expression is fact/12.

By creating allocation expressions, you define how higher-level facts are degraded to lower-level attributes. Allocation expressions are defined by operations you set on attributes and facts in the Level Extension Wizard in the Fact Editor.

Fact degradations often produce data estimates rather than exact values for the fact data at lower logical levels. For example, consider the allocation expression fact/12 for a degradation from Year to Month. Using such an allocation expression would spread a year’s fact data evenly over the 12 months of that year. While it is possible that the fact data would be the same for every month of the year, this is often an unlikely scenario. Such fact degradations should be used only when fact data is not stored at a lower logical level and there is no way to directly relate the fact data to the lower logical level.