Before you Begin

Learn how to create calculated, level-based, and share measures in the logical layer of a semantic model in Oracle Analytics Cloud.

Background

This tutorial describes how to build governed semantic models using the Semantic Modeler.

This is the next tutorial in the Create a Semantic Model series. Do the tutorials in the order listed:

What Do You Need?

  • Access to Oracle Analytics Cloud
  • Access to DV Content Author, BI Data Model Author, or a BI Service Administrator role
  • Access to the Sample Sales Semantic Model

Create Calculated Measures

In this section, you create a calculated measure in the logical layer's F1 Revenue table using a logical expression and a calculated measure using a function.

Begin with step 3 if you're continuing this tutorial directly after completing the steps in the Create Logical Dimensions tutorial.

  1. If you closed your semantic model, sign in to Oracle Analytics Cloud using one of DV Content Author, BI Data Model Author or service administrator credentials. On the Home page, click the Navigator Navigator icon, and then click Semantic Models.
  2. In the Semantic Models page, select Sample Sales, click Actions menu Actions menu icon, and then select Open.
  3. In the Sample Sales semantic model, click the Logical Layer Logical Layer icon.
  4. Expand Sample Sales BM, expand F1 Revenue, and select Revenue, and then click Detail View Detail View.
  5. In F1 Revenue Columns, click Add Column Add Column icon.
  6. In Name, enter Actual Unit Price. Click Logical Expression, and then click Open Expression Editor Expression Editor icon.
  7. In the Expression Editor, click Logical Layer Logical Layer icon, expand F1 Revenue, and then drag Revenue to the expression field.
  8. From Operators, double-click the divisor (/). After the divisor, start typing Uni, and then select Units. Click Validate, and then click Save in the calculated measure.


    Actual Unit Price equals Revenue divided by Units.

  9. In F1 Revenue, click Add Column Add Column icon. In Name, enter Revenue Rank. Click Logical Expression, and then click Open Expression Editor Expression Editor icon. Under Functions, expand Display, and then double-click Rank.
  10. Click Logical Layer Logical Layer icon, expand F1 Revenue, and then drag Revenue to column in the parentheses next to Rank in the Expression Editor field. Click Validate, and then click Save to save the calculation.


    The Revenue Rank columns uses the Rank function for its calculated measure.

  11. Click Save Save icon.


    Description of calc_measures.png follows
    Description of the illustration calc_measures.png

Create Level-Based Measures

In this section, you create measure columns in the Revenue table using physical expressions.

  1. In the Logical Layer, double-click F1 Revenue. In the Columns tab, click Add Column Add Column icon. Double-click New Column_1 to open the detail view.
  2. In Name, enter Product Total Revenue.
  3. In Sources with Physical Expression selected, click Add Level Add Level icon.
  4. Click LTS1 Revenue in the Logical Table Source field. Double-click Physical Column, expand F1 Revenue, and then select Revenue.
  5. In Aggregation, click Add Aggregation by Level Add Level icon. From the Dimension list, click D2 Products. From the Logical Level list, click Total, and then click Save Save icon.


    Description of prod_rev_total.png follows
    Description of the illustration prod_rev_total.png
  6. In the F1 Revenue Columns tab, click Add Column Add Column icon.
  7. In Name, enter Product Type Revenue.
  8. In Sources with Physical Expression selected, click Add Level Add Level icon.
  9. Click LTS1 Revenue in the Logical Table Source field. Double-click Physical Column, expand F1 Revenue, and then select Revenue.
  10. In Aggregation, click Add Aggregation by Level Add Level icon. From the Dimension list, click D2 Products. From the Logical Level list, click Product Type and then click Save Save icon.


    Description of prod_type_rev.png follows
    Description of the illustration prod_type_rev.png

Create a Share Measure

In this section, you create a measure that calculates the revenue share of a product type in the total revenue.

  1. In the F1 Revenue Columns tab, click Add Column Add column icon. Enter Product Share to replace New Column_1. Click Detail View Detail View icon.
  2. Click Logical Expression, and then click Open Expression Editor Expression Editor icon.
  3. In the Expression Editor search bar, enter Round, and then double-click ROUND to add the function. In the expression field after ROUND in the parentheses, enter the following:

    (100*Revenue/Product Type Revenue, 1)

  4. Click Validate, and the click Save in the Expression Editor.


    Description of prod_share.png follows
    Description of the illustration prod_share.png

  5. Click the Presentation Layer Presentation Layer icon, expand Sample Sales, double-click the Base Facts presentation table.
  6. Click the Logical Layer Logical Layer icon, expand F1 Revenue, hold down the Ctrl key and drag the following to the Base Facts Columns tab:
    • Actual Unit Price
    • Product Share
    • Product Total Revenue
    • Product Type Revenue
    • Revenue Rank
  7. Click Save Save icon

Deploy and Validate the Changes

In this section, you run the consistency checker, deploy the updated semantic model, and create a workbook with the updated Sample Sales subject area.

  1. Click the Consistency Checker Consistency checker icon and select Include Warnings.


    Oracle Analytics didn't find any errors in the Sample Sales semantic model.

  2. In the semantic model, click the Page Menu Page Menu icon, and select Deploy.


    The message, "Deploy successful" appears when the deployment process is complete.

  3. Click Go back Go back icon. On the Semantic Models page, click Navigator Navigator icon, and then click Home.
  4. On the Home page, click Create, and then click Workbook.
  5. In Add Data, click Sample Sales, and then click Add to Workbook.
  6. In the Data panel, expand the Products and Base Facts folders.
  7. Hold down the Ctrl key, select Product from Products, and then select Revenue, Revenue Rank, Units, and Actual Unit Price from Base Facts.
  8. Right click one of the selected data elements, select Pick Visualization, and then select Table.

     

    Description of calc_level_based_share_measures_table.png follows
    Description of the illustration calc_level_based_share_measures_table.png

Next Steps

Create Ragged and Skipped Level Hierarchies

Learn More