Create a Calculation Measure Using Physical Columns

Create a Calculation Measure Using Physical Columns

In this topic, you define a new calculation measure named Gross Profit Physical in the Sales Facts logical table, using physical columns to define the calculation formula.

To create a calculation measure using physical columns, perform the following steps.

1.	Return to the SH repository, which should still be open in online mode in the Administration Tool.
2.	Right-click the Sales Facts logical table and select New Object > Logical Column.
3.	In the Logical Column dialog box, name the logical column Gross Profit Physical.
4.	Click the Aggregation tab.
5.	Set the default aggregation rule to SUM.
6.	Click OK to close the Logical Column dialog box. Gross Profit Physical is added to the business model.
7.	Expand Sales Facts > Sources and double-click the SALES logical table source. The Logical Table Source dialog box opens.
8.	Click the Column Mapping tab.
9.	Click the Expression Builder button for the Gross Profit Physical logical column.
10.	In the Expression Builder, select Physical Tables > SALES > AMOUNT_SOLD and then click the Insert button to add the column to the formula.
11.	Click the minus sign operator to add it to the formula.
12.	Select Physical Tables > COSTS > UNIT_COST and then click the Insert button to add the column to the formula.
13.	Click OK to close the Expression Builder. Notice that the expression is added in the Logical Table Source dialog box.
14.	Click OK to close the Logical Table Source dialog box. The icon for Gross Profit Physical changes to indicate an aggregation rule is applied.
15.	Drag Gross Profit Physical to Sales Facts in the Presentation layer.
16.	Check in changes.
17.	Click Yes when prompted to check global consistency. If the repository is consistent (no Error messages), close the Consistency Check Manager. If there are Error messages, you must correct the errors before continuing.
18.	Save the repository.
19.	Return to Answers, which should still be open from the previous topic.
20.	Click Reload Server Metadata.
21.	Expand Sales Facts and verify that the Gross Profit Physical column is now visible in Answers.
22.	Create the following request. Times.Calendar Year, Sales Facts.Amount Sold, Sales Facts.Unit Cost, Sales Facts.Gross Profit Physical
23.	Click Results. Verify that the results are the same for the Gross Profit Physical (built using physical columns) as they were for the Gross Profit column (built using logical columns). The calculation formula for the logical columns looks like this: sum(Amount Sold) – sum(Unit Cost), whereas the calculation formula for the physical columns looks like this: sum(AMOUNT_SOLD – UNIT_COST). Because of arithmetic laws, you know that you can sum ColumnA and sum ColumnB and then take the differences of those sums, and have exactly the same results if you calculate the difference first (the value in ColumnA – the value in ColumnB for each row) and then sum the difference. So in this example, the results are the same for the logical column and the physical column calculations.
24.	Click Settings > Administration > Manage Sessions > View Log to view the query log. Your results should look similar to the picture. Note that the difference between amount sold and unit cost is calculated first and then summed:sum(T245.AMOUNT_SOLD - T168.UNIT_COST)in the example pictured here.
25.	Close the query log.
26.	Click Finished to close the Session Management window.
27.	Click Close Window to close Oracle BI Presentation Services Administration.
28.	Leave Answers open for the next topic.