Create a Calculation Measure Using Logical Columns

Create a Calculation Measure Using Logical Columns

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

To create a calculation measure using logical 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 and check Use existing logical columns as the source.
4.	Click the Expression Builder button (three dots) to open the Expression builder.
5.	In the left pane, click Logical Tables.
6.	Select Sales Facts in the middle pane, Amount Sold in the right pane, and click Insert. The Amount Sold column is added to the formula.
7.	Click the minus sign operator to add it to the formula.
8.	Double-click Unit Cost in the right pane to add it to the formula.
9.	Click OK to close the Expression Builder. Notice that the formula appears in the Logical Column dialog box.
10.	Click OK to close the Logical Column dialog box. The Gross Profit logical column appears in the business model.
11.	Drag the Gross Profit logical column to the Sales Facts table in the Presentation layer.
12.	Check in changes.
13.	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.
14.	Save the repository.
15.	Return to Answers, which should still be open from the previous topic.
16.	Click Reload Server Metadata.
17.	Expand Sales Facts and verify that the Gross Profit column is now visible in Answers.
18.	Create the following request. (If the request from the previous topic is still open, delete the Gross Profit column you created in Answers and then add the Gross Profit column you created in the repository to the query.) Times.Calendar Year, Sales Facts.Amount Sold, Sales Facts.Unit Cost, Sales Facts.Gross Profit
19.	Click Results. Verify that the results are the same as for the query you built using the Gross Profit column created in Answers.
20.	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 being calculated in the outer query block (SAWITH0.c2 - SAWITH0.c1 as c4 in the example pictured here). Because you defined the Gross Profit calculation using logical columns, the columns are summed first and then the difference is calculated. You compare these results to the query results in the next practice.
21.	Close the query log.
22.	Click Finished to close the Session Management window.
23.	Click Close Window to close Oracle BI Presentation Services Administration.
24.	Leave Answers open for the next topic.