Example: Creating Overrides

This topic provides an example of creating overrides and discusses the affect of dimension order on calculation.

The following table describes the three dimensions used in this example. The first column lists the names of the dimensions. The second column lists the dimension order, which determines calculation priority. The third column lists the dimension override functions that are used if member override functions do not exist for the children of the parents in the dimension.

Dimension	Dimension Order/Priority	Dimension Override User Function
ACCT	1	<ACCT_DIM_DEFAULT_FORMULA>
TRANS_DATE	2	<NONE>
DEPT	3	<DEPT_DIM_DEFAULT_FORMULA>

The following table describes the hierarchy of the ACCT dimension that is associated with the AcctTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child.

Note:

Overrides are not performed on cells denoted (leaf) or <none>.

Parent	Child	Member Override User Function
100	110	<SALES_ACCT_SUM>
100	110	<DIRECTOR_ACCT_SUM>
100	120	<none>
110	111	(leaf)
110	112	(leaf)
120	121	(leaf)

The following table describes the hierarchy of the TRANS_DATE dimension that is associated with the QrtrlyTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child.

Note:

Overrides are not performed on cells denoted (leaf) or <none>.

Parent	Child	Member Override User Function
Q1	Q1	<none>
Q1	Jan	(leaf)
Q1	Feb	(leaf)
Q1	Mar	(leaf)

The following table describes the hierarchy of the DEPT dimension that is created from the DeptTree tree. The first column lists the parents in the hierarchy. The second column lists the children of the parents. The third column lists the member override user functions that are performed on each child.

Note:

Overrides are not performed on cells denoted (leaf) or <none>.

This example uses the <RED_HERRING> child node override as incorrect data. Leaf nodes do not have aggregations.

Parent	Child	Member Override User Function
GBL	GBL	<SOME_DEPT_AVG>
GBL	USA	<none>
GBL	EUR	<none>
USA	CA	(leaf)
USA	GA	(leaf)
USA	NY	(leaf)
USA	TX	<RED_HERRING>
USA	IL	(leaf)

Assume that a SALES data cube exists in the cube collection, and the three dimensions of this example are attached to this data cube.

Read the instructions carefully before analyzing the following table; the table describes two methods that the analytic calculation engine can use to calculate hierarchies.

• When you analyze only the first four columns of the table (ignore the fifth column), the basic analytic model does not contain any cube dimension overrides.

  The first column displays the row numbers. The second, third, and fourth columns list the members of each of the three dimensions.

• When you analyze all five rows of the table, the basic analytic model does contain cube dimension overrides.

  The first column displays the row numbers. The second, third, and fourth columns list the members of each of the three dimensions. The fifth column—where it applies—lists the override that the analytic calculation engine uses to calculate the row.

  For example, if the developer applies the SALES_CUBE_OVERRIDE cube dimension override to the TRANS_DATE dimension as it is attached to the SALES data cube, the Cube Dimension Override User Function column indicates where the override is applied.

In the following table, italicized values indicate aggregate members. A row may contain more than one aggregate member. If a row contains one or more aggregate members, the table denotes the winning aggregate member along with the method that the analytic calculation engine uses to calculate the member. If a row does not contain any aggregate members, the analytic calculation engine uses the data cube's rule to calculate values.

Row	ACCT Dimension Member Priority 1	TRANS_DATE Dimension Members Priority 2	DEPT Dimension Members Priority 3	Cube Dimension Override User Function
1	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Q1	USA	N/A
2	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Q1	CA	N/A
3	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Q1	NY	N/A
4	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Q1	TX	N/A
5	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Jan	USA	N/A
6	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Jan	CA	N/A
7	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Jan	NY	N/A
8	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Jan	TX	N/A
9	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Feb	USA	N/A
10	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Feb	CA	N/A
11	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Feb	NY	N/A
12	Winning aggregate: 100 Use member override user function: USA <SALES_ACCT_SUM>	Feb	TX	N/A
13	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Q1	USA	N/A
14	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Q1	CA	N/A
15	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Q1	NY	N/A
16	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Q1	TX	N/A
17	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Jan	USA	N/A
18	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Jan	CA	N/A
19	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Jan	NY	N/A
20	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Jan	TX	N/A
21	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Feb	USA	N/A
22	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Feb	CA	N/A
23	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Feb	NY	N/A
24	Winning aggregate: 110 Use member override user function: <DIRECTOR_ACCT_SUM>	Feb	TX	N/A
25	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Q1	USA	N/A
26	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Q1	CA	N/A
27	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Q1	NY	N/A
28	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Q1	TX	N/A
29	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Jan	USA	N/A
30	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Jan	CA	N/A
31	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Jan	NY	N/A
32	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Jan	TX	N/A
33	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Feb	USA	N/A
34	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Feb	CA	N/A
35	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Feb	NY	N/A
36	Winning aggregate: 120 Use the <ACCT_DIM_DEFAULT_FORMULA> dimension override user function because a member override user function does not exist for this member.	Feb	TX	N/A
37	111	Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension.	USA	<SALES_CUBE_OVERRIDE>
38	111	Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension.	CA	<SALES_CUBE_OVERRIDE>
39	111	Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension.	NY	<SALES_CUBE_OVERRIDE>
40	111	Winning aggregate: Q1 Use default sum aggregation because neither a member override user function exists for this member, nor a dimension override user function exists for this dimension.	TX	<SALES_CUBE_OVERRIDE>
41	111	Jan	Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member.	N/A
42 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	111	Jan	CA	N/A
43 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	111	Jan	NY	N/A
44 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	111	Jan	TX	N/A
45	111	Feb	Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member.	N/A
46 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	111	Feb	CA	N/A
47 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	111	Feb	NY	N/A
48 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	111	Feb	TX	N/A
49	112	Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member.	USA	<SALES_CUBE_OVERRIDE>
50	112	Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member.	CA	<SALES_CUBE_OVERRIDE>
51	112	Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member.	NY	<SALES_CUBE_OVERRIDE>
52	112	Winning aggregate: Q1 Use default sum aggregation because a member override user function does not exist for this member.	TX	<SALES_CUBE_OVERRIDE>
53	112	Jan	Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member.	N/A
54 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	112	Jan	CA	N/A
55 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	112	Jan	NY	N/A
56 No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	112	Jan	TX	N/A
57	112	Feb	Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member.	N/A
58 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	112	Feb	CA	N/A
59 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	112	Feb	NY	N/A
60 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	112	Feb	TX	N/A
61	121	Winning aggregate: Q1 Use default sum aggregation.	USA	<SALES_CUBE_OVERRIDE>
62	121	Winning aggregate: Q1 Use default sum aggregation.	CA	<SALES_CUBE_OVERRIDE>
63	121	Winning aggregate: Q1 Use default sum aggregation.	NY	<SALES_CUBE_OVERRIDE>
64	121	Winning aggregate: Q1 Use default sum aggregation.	TX	<SALES_CUBE_OVERRIDE>
65	121	Jan	Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member.	N/A
66 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	121	Jan	CA	N/A
67 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	121	Jan	NY	N/A
68 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	121	Jan	TX	N/A
69	121	Feb	Winning aggregate: USA Use the dimension override user function <DEPT_DIM_DEFAULT_FORMULA> because a member override user function does not exist for this member.	N/A
70 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	121	Feb	CA	N/A
71 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	121	Feb	NY	N/A
72 Note: No winning aggregate. Because this row does not contain any aggregates, use the data cube's rule for calculating values.	121	Feb	TX	N/A

The analytic calculation engine used this logic to determine which cell of the row it finally used to calculate the aggregate:

• Aggregation for row 25:

  The analytic calculation engine used the 120 value from the TRANS_DATE dimension because this dimension was the only dimension that contained an aggregate member. Understand that if either of the two other dimensions contained an aggregate member, the analytic calculation engine would still select the 120 value because the TRANS_DATE dimension is first in priority. The analytic calculation engine used the <ACCT_DIM_DEFAULT_FORMULA> dimension override because neither a cube dimension user function nor a member override user function existed for this member.

• Aggregation for row 37:

  The analytic calculation engine used the Q1 value from the TRANS_DATE dimension because this was the only dimension that contained an aggregate member. The analytic calculation engine used the default sum aggregation because neither a member override user function existed for this member nor a dimension override user function existed for this dimension.

• Aggregation for row 41:

  The analytic calculation engine used the USA value from the DEPT dimension because this was the only dimension that contained an aggregate member. The analytic calculation engine used <DEPT_DIM_DEFAULT_FORMULA> because neither a cube dimension user function nor a member override user function existed for this member.