Semantic Layer - Context Designer

1. In ITBA, click ADMIN > Semantic Layer > Semantic Layer. The Context Management page opens. If not , click Data Loader. For details, see Context Designer Page.

2. In the Context Designer page that opens:

   • To create a new context, click Create a new context.
   • To edit a context, click Open an existing context.

For details on the KPIs and their formulas, see the KPI and Metric Library in Excel format, in the relevant integration sections or Content Acceleration Pack (CAP) sections .

A Context (or Universe) is a set of entities. Each entity is a set of fields. Each field can be a dimension, measurement, or fact that can provide information about the business.

A formula calculates, for a specified time period, using the values of specific entities, a value that represents a specific aspect of the business. The value is assigned to a Key Performance Indicator (KPI) so that the KPI represents a specific aspect of the business.

Each Context includes some KPIs. The KPIs are the building blocks of the Studio and the KPI engine.

These entities that are used in the calculation of the KPIs are provided by the relevant integrated data source. Each data source corresponds to a specific Content Pack that provides the connection between the data source and ITBA.

Dimension. An entity that describes, qualifies, or otherwise adds meaning to the measurements (facts) that business users want to analyze.

Measure. A value collected by the executable during execution, such as the number of rows processed during an ETL job, or an amount extracted from a table that describes expenses in a source application.

Metric. A framework to establish and collect measurements of success or failure on a regulated, timed basis that can be audited and verified.

To uniquely identify contexts, entities, and fields, a universally unique identifier (UUID) is assigned to them. When you save a new context, the UUID is auto-generated.

The flowchart explains the different configurations and their impact on the import of a Context in the Context Designer:

Contexts can include tables that are linked together forming a loop.

To perform the calculation of a KPI or Metric, the system selects the path with the lowest number of joins as the default path. If the formula includes both fields in A and in E, then the system will choose A-B-E (2 joins) instead of A-C-D-E (3 joins).

If the structure has the same number of joins and the formula includes fields from the three tables, then the system cannot select the path with the lowest number of joins (same number for each path).

In that case, the selection of the path used for calculation is random.

The paths could then be:

Or

Or

Workarounds:

• Modify the formulas: If you want the path to follow a specific path, you must modify the formula so it includes the path. For example, if the formula is SUM (A.duration, *) filter E.status=’OK’ it will follow the A-B-E path because it has the smallest number of joins, but if you want it to pass via C you can modify the formula as follows: SUM (A.duration,*) with filter E.status=’OK’ but then the path could go A-C-D-E or C-A-B-E. If you want it to follow A-C-D-E you should use SUM (A.duration, C.id=C.id and D.id=D=id) with filter E.status=’OK’.

  

• Duplicate the table:Duplicate the table that is linked to a large number of other tables. If you have the following context structure:

  

  You can create the same structure without loops by using copies (aliases) of the Member table, give each table another name, and create the relevant joins, as follows:

  

  Once you have created the aliases you can drag them to the Entities area.

• Modify the view: If you have the structure below then when the tables are dragged to the Context Designer Entities area, then each entity and its attributes appear separately .

  

  You can modify the views in the Target schema to correspond to the structure below, where the attributes of the entity include both the entity original attributes and the attributes corresponding to the person who submitted the idea. You create these views in the SQL server. This way when the tables are dragged to the Context Designer Entities area they display the entity and its attributes as well as the corresponding Member entity and its attributes.

  

When you create a Context, you select tables, create links between the tables, and select the relevant columns in the tables to be part of the Context. For details, see Semantic Layer - Context Designer.

When you create a KPI or a Metric you assign them a Context in the Studio. The Context tables become variables that you can use when creating KPI or Metric formulas, and the table columns become the variable entities.

For example the % of Reopened Defects KPI has the following formula:

RATIO_MATH(COUNT_DISTINCT( DefectHist.Defect , DefectHist.ReopenFlag =1 And Defect.Status <> 'Closed') , COUNT( Defect ,Defect.DefectSummary<> 'INVALID' And Defect.DefectSummary<> 'UNKNOWN' ),0)*100

Its definition is:

The Business Context is ALM_Defect. Click Admin > Semantic Layer and open the ALM_Defect Context:

The contents of the Context including the ALM tables is displayed:

In the Entities area on the right, you can see the tables and the table columns that were selected to be part of the ALM_Defect Context itself.

It is recommended that the person who creates the KPI or Metric formula should be familiar with the ALM entities that are used in the ALM_Defect context:

The Defect_Hist table corresponds to one of the entities (DefectHist) that appears in the list of Variables that can be used when creating or modifying the formula of a KPI based on the ALM_Defect context. To access the KPI or Metric formula, click Studio, highlight the relevant KPI (% of Reopened Defects for example), click the Calculation Details tab, and then click Open Formula builder.

The variables you see in the formula builder correspond to the Entities in the Context Designer (above) and correspond to the <EntityName> in the syntax of the formulas (as explained in KPI or Metric Formula .)

When you expand a variable, the items you see below correspond to the fields (in the Context Designer) and correspond to the <FieldName> items in the formulas syntax (as explained in KPI or Metric Formula.)

1. In Business Analytics, click ADMIN > Semantic Layer > Semantic Layer. The Context Management page opens.

2. The list of out-of-the-box Contexts is displayed.

   

3. Select the relevant context and click Launch Context Designer.

4. The structure of the Context is displayed.

   

1. Create a new Context

   1. In ITBA, click ADMIN > Semantic Layer > Semantic Layer.
   2. In the Context Management page, click Launch Context Designer.
   3. Click Create a New Context. The Context Designer page opens.
   4. Enter the name of the Context .
   5. Click Open Repository. The Repository area opens. You can use:
      • Tables from the ExternalTables list. These tables are created from external tables in CSV format that are uploaded to ITBA. For details on how to upload a CSV table, see Semantic Layer - Excel (or .CSV) File Loader . After the table is uploaded, a corresponding table named ext.<csv_table_name> is listed in the ExternalTables list.

      • Internal tables from the InternalTables list.

        If you are working with Enterprise ITBA, the tables listed in the InternalTables list are the Data Warehouse tables, the Period tables created during the post-install procedure, and the View table.

   6. Drag the relevant tables from the Repository to the Scheme area.
   7. Define the relationships between the tables by dragging a specific column from one table and dropping it over the relevant column in another table. A visual link is added to the graph to indicate the relationship.
   8. Drag the relevant tables from the Scheme area to the Entities pane. The tables become variables and the columns become the variable entities that can be used to create KPI or Metric formulas when a KPI or Metric is assigned the Context in the Studio.
   9. Click to save the Context.

2. Load the Context and verify the variables and entities

   1. In ITBA, click Studio.
   2. Activate a KPI or a Metric or clone any active KPI or Metric that does not have a KPI Breakdown or Breakdown Metric. For details, see Activate Scorecards, Perspectives, Objectives, Metrics, or KPIs Using Templates or Create Active Scorecards, Perspectives, Objectives, Metrics, or KPIs.
   3. Click the Calculation details tab, and click Select business context. For details, see KPI or Metric Configuration and Calculation Details.
   4. In the Business Context dialog box, change its Context to the new Context.
   5. You can then modify the formula that is used to calculate the KPI or Metric status by selecting the variables that correspond to the entities and fields that were defined in the Context Designer. To do that click Open Formula Builder.

   6. In the Formula Builder dialog box, verify that the variables correspond to the tables you selected in the Entities area in the Context Designer, and that the entities of the variables correspond to the selected columns of those tables. Click OK to save the KPI.

      

   Note In the same way, you can modify the Filter of the KPI or Metric to use the variables corresponding to the .CSV file-based Context (universe).

1. In ITBA, click ADMIN > Semantic Layer > Semantic Layer. In the Context Management page, click Launch Context Designer. The Context Designer page opens. For details, see Context Designer Page.
2. Select the relevant Context.

3. Make the relevant changes: delete or add entities, fields, tables, columns, or relationships.

4. Click to save the Context.

5. Load the Context and verify.

To export contexts, proceed as follows:

1. Create a CAP that only includes the contexts you want to export. For details, see Create a CAP with the ITBA application data .
2. Export the CAP you created. For details, see Download a CAP to the user's local system .

To import contexts, proceed as follows:

1. Upload the CAP. For details, see Upload a CAP to the Business Analytics application .
2. Activate the CAP in ADMIN > Data Management > Activate CAP. For details, see Activate a CAP.
3. Verify the upload by accessing the contexts in ADMIN > Semantic Layer > Semantic Layer.

When you restructure the Target database by adding a column to a table, the corresponding tables in the relevant Contexts are automatically refreshed. You must then proceed as follows:

1. Restructure the Target database by adding a column to a specific table.

   The column is automatically and immediately inserted into the corresponding table in the Context, and the links between the Context tables are maintained. A new index is not created if you add a new column directly in the target DB so you have to create an entry for the new column accessing by the SQL Studio Manager. The index is located under the table and then under the columns.

2. If needed, create additional links between the new column and the other tables.
3. If needed, drag the new column into the Entities area.
4. Save the context.

When you restructure the Target database by removing a column from a table, the corresponding tables in the Contexts are automatically refreshed. You must then proceed as follows:

1. Remove the index corresponding to the relevant column by accessing the SQL Studio Manager. The index is located under the table and then under the columns.

2. Remove the relevant column from the table in the Target database.

   The column is automatically and immediately removed from the corresponding table in the Context, and the links between the Context tables are maintained. The link from this column to other tables are removed.

3. Remove the column from the Entities area.
4. Save the context.

To clone a context:

1. Create a CAP that includes only the context. For details, see Create Content Acceleration Packs.
2. Download the CAP to your local system. For details, see Download a CAP to the user's local system .

3. Open the <context_name>.XML file and change the context name and at least one character in the UUID. Change also the UUID of all the entities, and the fields in the context.

   For example:

   <?xml version="1.0" encoding="UTF-8" standalone="true"?>
   -<contexts>
    -<context uuid="4c60950d-2fdc-49c5-a34e-5c041963d0bb" active="false" name="SALARY">
     -<entity uuid="c3a48abd-7156-47a7-a489-527bfa2385c9" name="ext_IT_Salary_new" isVisible="false">
         <field uuid="7adedab5-9336-490b-8594-db81fe559c37" name="NAME" 
         isVisible="false" 
         tableName="@extension.schemaName@.IT_Salary_new" 
         columnName="NAME" qualification="DIMENSION" type="STRING" 
         isLOV="false"/>
       </entity>
       -<table name="@extension.schemaName@.IT_Salary_new" height="126" yPos="236.95" xPos="493.0">
        <column name="NAME" type="varchar"/>
        <column name="ORGANIZATION_NAME" type="varchar"/>
        <column name="ORG_ID" type="numeric"/>
        <column name="SALARY" type="numeric"/>
        </table>
     </context>
   </contexts>

4. Save the XML file.
5. Rename the CAP in your local system.
6. Upload it. For details, see Upload a CAP to the Business Analytics application .
7. Activate it. For details, see Activate a CAP .

The Context Designer page enables you to manage the Contexts that you create using the Context Designer feature.

User interface elements are described below (when relevant, unlabeled elements are shown in angle brackets):

UI Element	Description
	Click to display a list of existing Contexts created in the Context Designer. Select the relevant Context and click OK.
	Click to create a new Context. The Context Designer Wizard opens. For details, see below.
	Click to open the Import Content wizard. For details, see Semantic Layer - Excel (or .CSV) File Loader .

The Context Designer wizard enables you to create a Context with tables, entities, and relationships. Once you have completed the Context, save it. The Context is then added to the list of Contexts available in the Studio. The tables that compose the universe are added as variables and the table columns as variable entities. The variables and entities can be used to calculate the formulas for the KPIs that are assigned the Context. For details, see KPI or Metric Configuration and Calculation Details.

This page enables you to configure a Context.

Context Designer Toolbar

User interface elements are described below (when relevant, unlabeled elements are shown in angle brackets):

UI Element	Description
	New. Click to create a new Context.
	Save. Click to save the currently opened Context.
	Open. Click to display a list of existing Contexts created in the Context Designer. Select the relevant Context and click OK.
	Add table. Click to add tables in CSV format to the Repository. The data loader wizard opens. For details, see Semantic Layer - Excel (or .CSV) File Loader.
	Click to validate the Context. The validation checks if the name of the Context is unique, if all the tables in the Context exist in the Repository, and more. When you click the Save button, the Context is validated. An invalid Context is not saved.

Context Area

Scheme Area

User interface elements are described below (when relevant, unlabeled elements are shown in angle brackets):

UI Element	Description
Repository	Click the button to open the table repository. For details, see Repository.
<Drag and drop>	Select the relevant table in the Repository and drag and drop it in the Scheme area to make the table part of the universe.
<Create relationships>	Select the relevant entity in a table and drag and drop it on the relevant entity in another table to create the relationship between these two entities. Note   Double-click a table to shrink it down to its title. Double-click a shrunk table to expand it. To delete a relationship, click it and press the Delete key.
	Use the slider to zoom in or out.

Repository

The Repository area enables you to select external or internal tables to add to the Context you are building.

User interface elements are described below (when relevant, unlabeled elements are shown in angle brackets):

UI Element	Description
	Click to close the Repository.
External Tables (CSV)	Lists the CSV tables that were uploaded using the data loader wizard. Click the relevant table to display its column details.
	Click to add tables in CSV format to the Repository. The data loader wizard opens. For details, see Semantic Layer - Excel (or .CSV) File Loader.
Internal Tables	Lists the tables. Click on the relevant table to display its entities in the Column details section.
Column Details	Lists the entities (columns) of the table selected in the Internal Tables section. Each entity is preceded by an icon indicating the type of entity: - date. - string. - numeric

Entities Area