Defining joins - SAP BO

Once you have inserted more than one table in the schema, you need to create joins between related tables. Joins are as important as the tables in a schema, as they allow you to combine data from multiple tables in a meaningful way.

What is a join?

A join is a condition that links the data in separate but related tables. The tables usually have a parent-child relationship. If a query does not contain a join, the database returns a result set that contains all possible combinations of the rows in the query tables. Such a result set is known as a Cartesian product and is rarely useful.

For example, the Cartesian product of a query referencing two tables with 100 and 50 rows respectively has 5000 rows. In large databases or queries involving many tables, Cartesian products quickly become unmanageable. In Designer, joins are represented as lines linking tables in a schema.

Why use joins in a schema?

You use joins to ensure that queries returning data from multiple tables do not return incorrect results. A join between two tables defines how data is returned when both tables are included in a query.

Each table in a schema contains data in one or more columns that correspond to user requirements.In a production universe, Web Intelligence users may want to run queries that combine a number of different objects (each inferring a column) returning data from any combination of tables.

Linking all tables in the schema with joins ensures that you restrict the number of ways that data from columns in different tables can be combined in a query. Joins limit column combinations between tables to matching or common columns. This prevents result data being returned that contains information from columns that have no sense being matched.

Note:You should always create joins in the Structure pane. Joins that are not created from the Structure pane, for example a join manually defined in the Where clause for an object, are created at run time, so are not considered by Designer for integrity checks and context detection. The information for these processes is required at design time.

What SQL does a join Infer?

By default Designer specifies a join implicitly in a WHERE clause through a reference to the matching or common columns of the tables.

Normally there is one WHERE clause for each pair of tables being joined. So, if four tables are being combined, three WHERE conditions are necessary.

The result of a query run including two tables linked by a join is a single table with columns from all the combined tables. Each row in this table contains data from the rows in the different input tables with matching values for the common columns.

ANSI 92 support

If the target RDBMS supports ANSI 92, then you can set a universe parameter (File > Parameters > Parameter) ANSI92 to Yes to activate ANSI 92 support for joins created in your schema. When a universe supports the ANSI 92 standard for joins, newly created joins are specified in the FROM clause.

You can also select the objects that are inferred by columns to be included in the FROM clause.

What tables do not have to be joined?

You should join all tables in the schema that are inferred in the SQL generated by objects in Web Intelligence queries run against the universe. The only exceptions to these are the following types of tables:

  • Base tables from the schema that have been aliased for each use. These are the original tables for which you have created aliases either for renaming, or join problem resolution reasons. These base tables are typically not used in any object definition.
  • Tables that are the target of aggregate awareness syntax (although this has to be taken on a case-by-case basis). For example the aggregate tables in the sampleefashion universe (their names begin with "Agg_") are not joined to any table in the schema:

Joining primary and foreign keys

You normally create a join between the primary key in one table and the foreign key of another table. You can also create a join between two primary keys. It is very unusual for at least one side of a join to not include the primary key of the table.

You need to understand how each key is constructed in your database. Multi column keys can affect how you set cardinalities for joins, and this can affect how you set up contexts in your schema.

Displaying keys

You can display primary and foreign keys in all tables in the Structure pane. The key columns appear underlined in each table that contains keys. When you select the option to display keys, you must refresh the structure before keys appear underlined.

The ability to display key columns as underlined depends on primary keys being defined in the target database.

Note:When you display underlined key columns, the information is stored in the.UNV file. This information is lost when you export a universe to the Central Management Server ( CMS ) repository. You have to re-display keys for a universe, each time it is imported.

To display keys:

  1. Select Tools > Options.
  2. The Options dialog box opens to the General page.
  3. Click the Graphics tab.
  4. The Graphics page appears.
  5. Select the Underline Keys check box in the Columns group box.
  6. Click OK.
  7. You need to refresh the structure before key columns appear underlined.
  8. Select View > Refresh Structure.
  9. Displaying keys

The database structure is refreshed. The key columns in your schema are underlined as shown :

Understanding the cardinality of a join

Cardinalities further describe a join between 2 tables by stating how many rows in one table will match rows in another. This is very important for detecting join problems and creating contexts to correct the limitations of a target RDBMS structure.

You should set cardinalities for each join in the schema. Designer can automatically detect and set cardinalities, but you should always manually check the cardinalities, taking into account the nature of the keys that are joined.

Creating joins

You have several approaches to creating joins in Designer:

  • Tracing joins manually in the schema.
  • Defining join properties directly.
  • Selecting automatically detected joins.
  • Automatically creating joins on table insertion.

Each of these approaches is described in detail below.

Tracing joins manually in the schema

You can graphically create individual joins between tables by using the mouse to trace a line from a column in one table to a matching column in another table.

To create a join by tracing manually:

  1. Position the pointer over a column that you want to be one end of a join.
  2. The pointer appears as a hand symbol.
  3. Click and hold down the left mouse button.
  4. The column is highlighted.
  5. Drag the mouse to the column in another table that you want to be the other end of the join.
  6. As you drag, the pointer is transformed into a pencil symbol.

    Tracing joins manually in the schema

  7. Position the pencil symbol over the target column.
  8. The target column is highlighted.

    creating joins

  9. Release the mouse button. The join between the two tables is created.
  10. Double click the new join.The Edit Join dialog box appears. It lists join properties. The properties that you can set for a join, including cardinality and join type.
  11. Enter and select properties for the join.
  12. Click OK.

Defining join properties directly

You create a join by directly defining join properties in the Edit Join dialog box.

To create a join directly:

  1. Select Insert > Join.
  2. Or
    Click the Insert Join button.
    The Edit Join dialog box appears.

    Defining join properties directly

  3. Select a table from the Table1 drop-down list. The columns for the selected table appear in the list box under the table name.
  4. Click the name of the column that you want to be at one end of the new join.
  5. Select a table from the Table2 drop-down list box. The columns for the selected table appear in the list box under the table name.
  6. Click the name of the column that you want to be at the other end of the new join.
  7. Enter and select properties for the join.
  8. Click OK.

The new join appears in the schema linking the two tables defined in the Edit Join dialog box.

Selecting automatically detected joins

You can use the Designer feature Detect Joins to automatically detect selected joins in the schema. Designer identifies column names across tables in the target database and proposes candidate joins for the tables in your schema. You can then select which, or accept all, proposed joins you want to be created.

How are joins automatically detected?

The joins are detected based on the Joins strategy that appears in the Strategies page of the Parameters dialog box (File > Parameters > Strategies tab).

A strategy is a script file that automatically extracts structural information from the database. There are a number of inbuilt strategies that are shipped with Designer. These are listed in drop-down list boxes on the Strategies page of the Parameters dialog box.

The default automatic join detection strategy detects joins based on matching column names, excluding key information. You can select which join strategy you want to apply when you use automatic join detection.

Using automatic join detection appropriately

Detecting joins automatically is useful to help you quickly create joins in your schema. However, you need to be aware of the limitations of automatic join detection when designing your schema.

Join strategies used to detect candidate joins match column names from the database. There may be instances in the target database when primary, foreign keys, and other join columns do not have the same name across different tables. Designer will not pick up these columns. You should always verify manually each join that you accept to be created that has been automatically detected. You should be aware that there may be other joins necessary that have not been detected.

To create a join using automatic detection:

  1. Verify that the join strategy that you want to use to detect joins is selected in the Joins drop down list box on the Parameters dialog box. You can verify this as follows:
    • Select File > Parameters and click the Strategies tab.
    • Select the strategy that you want to use to detect joins from the Joins drop-down list box and click OK.
  2. Select multiple tables in the Structure pane.
  3. You can select multiple tables by pressing SHIFT while clicking each table, or you can select all tables in a zone by clicking in an empty space, and dragging the cursor to define a rectangular zone that includes any number of tables.

  4. Select Tools > Automated Detection >Detect Joins.
  5. Or
    Click the Detect Joins button.

    The Candidate Joins dialog box appears. It lists candidate or proposed joins for the selected tables. The candidate joins also appear as blue lines between selected tables in the Structure pane.

    selecting-automatically detected-joins

  6. Click Insert to create all candidate joins.
  7. Or
  8. Select one or more joins and click Insert.
  9. You can select one or more joins by holding down CTRL and clicking individual tables, or holding down SHIFT and clicking the first and last join in a continuous block.

    The joins are inserted in you schema.

  10. Click Close.

Inserting joins automatically with associated tables

You can choose to insert joins automatically in the schema at the same time as the tables that use the joins are inserted into the structure pane. Automatic join creation isdetermined by two processes:

  • The active join strategy determines the column information used to detect the join.
  • The default creation option Extract Joins With Tables must be selected to allow the automatic creation of joins with their associated tables. This option is on the Database page of the Options dialog box.

Limitations when inserting joins automatically

Inserting joins automatically into your schema with associated tables is a quick way to get joins into your schema, but it can lead to serious design faults with your schema. The joins are inserted based on the database structure, so columns common to more than one table that have been renamed in the database will not be picked up.

You should not use this technique to create joins in a productionuniverse. Instead, use it for demonstration purposes, or as a quick way to build a universe, in which you will then carefully validate each join after insertion.

To create a join automatically with an associated table:

  1. Verify that the join strategy that you want to use to detect joins is selected on theStrategies page of the Parameters dialog box.
  2. Select Tools > Options. The Options dialog box appears.
  3. Click the Database tab. The Database page appears.
  4. Select the Extract Joins With Tables check box.
  5. Click OK.

Now when you insert a table that has columns referencing other columns in tables that have already been inserted into the Structure pane, the references between tables are automatically inserted as joins between appropriate tables.

Join properties

You define join properties in the Edit Join dialog box. You can define the following properties for a join:

Join propertiesJoin properties

Join Operators

You can select an operator for a join from the drop-down list box between the Table1 and Table2 boxes. The operator allows you to define the restriction that the join uses to match data between the joined columns.

You can select the following operators for a join:

Join OperatorsJoin Operators

Edit and Parse

The Edit Join dialog box also has two features available that allow you to edit and verify the join syntax:

Edit

The Edit button opens an SQL editor. You can use this graphic editor to modify the syntax for tables, columns, operators, and functions used in the join.

Parse

The Parse button starts a parsing function that verifies the SQL syntax of the join expression. If the parse is successful, you receive a result is OK message. If Designer encounters an error, you receive an error message indicating the source of the problem.

Editing a join

You can use any of the following methods to edit a join:

  • Modify join properties from the Edit Join dialog box.
  • Modify join SQL syntax directly using the Join SQL Editor.
  • Modify join SQL syntax directly using the formula bar.

Each of these methods is discussed.

Using the Edit Join dialog box

You can use the Edit Join dialog box to define and edit join properties. You can also access the Join SQL Editor to edit join syntax directly from this dialog box.

To edit a join using the Edit Join dialog box:

  1. Double click a join in the Structure pane.
  2. Or
    Click a join and select Edit > Join.
    The Edit Join dialog box appears.

    Using the Edit Join dialog box

  3. Select an operator from the drop-down list box between the tables.
  4. Select other properties as required.
  5. If you are defining a join with ANSI 92 syntax, then click the Advanced button.
  6. Click OK.

Tip:You can edit the SQL directly for the join by clicking the Edit button and using the Join SQL editor.

Using the Join SQL Editor

You can use a graphical editor to directly modify the SQL expression for a join. You access this editor from the Edit Joins dialog box.

To modify a join using the Join SQL Editor:

  1. Double click a join in the Structure pane.
  2. Or
    Click a join and select Edit > Join.The Edit Join dialog box appears.
  3. Click the Edit button.
  4. The Join SQL Definition box appears. The SQL expression for the join appears in the text box.

    Using the Join SQL Editor

  5. Click the join expression in the edit box at the place where you want to add or modify the SQL syntax.
  6. You can use the editing features to modify or add SQL syntax as follows:

    editing features to modify or add SQL syntax

    The column, operator, or function appears in the join definition.

  7. Click OK.

Using the Formula bar

The "Formula" bar is a text box above the "Universe" window that shows the formula or expression of any selected join in the "Structure" pane, or selected object in the "Universe" pane. You can use three editing buttons placed to the left of the Formula bar:

Using the Formula bar

To display the Formula bar:

  • Select View > Formula Bar.
  • The "Formula Bar" appears above the "Universe" window. To modify a join using the "Formula Bar":
  1. Click a join that you want to edit.
  2. The formula for the join appears in the "Formula Bar".
  3. Click the join expression in the "Formula Bar" at the place you want to modify the syntax.
  4. Modify the expression as required.
  5. Click Validate to apply the changes.
  6. Press the Return key to quit the "Formula Bar" .
  7. Or
    Click anywhere outside of the "Formula Bar".

ANSI 92 support for joins in a universe

Designer supports ANSI 92 syntax for joins. ANSI 92 is not supported by default. You must activate support by setting the SQL universe parameter ANSI92 to YES. This parameter is listed on the Parameter page of the universe parameters dialog box (File > Parameters > Parameter). Once activated, you can choose to use ANSI 92 syntax for joins in the universe.

Note:The ANSI 92 setting is also declared in the .prm files. If the .prm setting is 'usual', then the designer setting takes preference. If the .prm setting is 'ANSI92', then the designer-level settings will be overridden.The behavior can depend on your database version. Refer to your database technical details for more information.

Ensure that you verify that the target RDBMS supports ANSI 92 before using the syntax in joins.

Activating ANSI 92 support in the universe and defining a join using ANSI 92 syntax are described below.

comparing default join syntax and ANSI 92 syntax

Join syntax for two joins is shown below. The first shows the default behavior where the join is defined in the WHERE clause, the second shows the same join in the FROM clause using the ANSI 92 standard.

Default join syntax

Same join using the ANSI 92 standard


Activating ANSI 92 support in a universe

To activate ANSI 92 support for joins:

  1. Select File > Parameters.
  2. The Universe Parameters dialog box appears.
  3. Click the Parameter tab.
  4. The Parameters page appears. It lists certain SQL generation parameters that you can set at the universe level to optimize SQL generation for the current universe. These are parameters that were included in the PRM file for the target RDBMS in previous versions of Business Objects products. Certain RDBMS specific parameters are still contained in the PRM files, but many standard SQL parameters are now listed in the Parameter page.

  5. Click the ANSI92 parameter in the list.
  6. Type YES in the value box.
  7. Click Replace.
  8. Click OK.

The ANSI 92 standard can now be applied to join definitions for the current universe. When you click the Advanced button on the Edit Join dialog box, the Advanced Join box appears. You can define a filter to determine which dimensions you want to include in the FROM clause for a join.

Defining a join with ANSI 92 syntax

You can use ANSI 92 syntax to define a join from the Edit Join properties dialog box. You can do this by using an advanced editing box that allows you to select objects to be included in a join definition.

To define a join using ANSI 92 syntax:

  1. Activate ANSI 92 support for the universe.
  2. Double click a join in the schema.
  3. The Edit Join box for the join appears. 3
  4. Click the Advanced button.
  5. The Advanced Joins Properties dialog box appears.
  6. Select one of the following FROM clause filters from the drop down list.
  7. Select objects to be included in the FROM clause if you selected the
  8. Selected objects in FROM filter.
  9. Click OK.
  10. Enter any other join parameters in the Edit Join box.
  11. Click OK.

Deleting joins

To delete a join:

  1. Click a join.
  2. The join is selected
  3. Do any of the following:
    • Press the backspace key on your keyboard
    • Press the Delete button on your keyboard
    • Right click the join and select Clear from the contextual menu.
    • A confirmation box appears asking to you to confirm the join deletion.
  4. Click Yes.

The join is deleted.

Note:Ensure that you are aware of all the consequences in both the schema and universe when you delete a join. Verify that deleting the join does not affect a context. If you try to delete a join, Designer warns you if the join is used in one or more contexts. You need to manually verify which context, and access the effect on the universe if the context is affected by the join deletion.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SAP BO Topics