Using cardinalities - SAP BO

Cardinality is a property of a join that describes how many rows in one table match rows in another table. Cardinality is expressed as the minimum and maximum number of rows in a column at one end of a join, that have matching rows in the column at the other end of the join.

The minimum and the maximum number of row matches can be equal to 0, 1, or N. A join represents a bidirectional relationship, so it must always have two cardinalities, one for each end of the join.

Cardinality of a join

The two tables Customer and Reservations are linked by a join.

Cardinality of a join
The cardinalities in the above join can be expressed as follows:
using cadinalities

How are cardinalities used In Designer?

The cardinality of a join does not have a role in the SQL generated when you run a query. However, Designer uses cardinalities to determine contexts and valid query paths.

A context is a collection of joins which provide a valid query path. You use contexts to resolve join problems that can return too many or too few rows because of the way that tables are linked in the target database.

Contexts affect the SQL generated for a query as they either direct the end user to take a particular join path, or solve a join path problem:

You need to verify that cardinalities are correctly set for all joins in your schema to ensure that you have the correct contexts, and that you have valid join paths.

Setting cardinalities can also help you understand how tables are related in the database, and to graphically identify potential join path problems in your schema.

Displaying cardinalities

You can display cardinalities in the Structure pane using the following symbols:

Displaying cardinalities

To display cardinalities:

  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. Click the Arrow, Arity, or 1,n radio button.
  6. Click OK.

What cardinalities can be set for a join?

You can set the following cardinalities for a join:

What cardinalities can be set for a join?

You can set cardinalities manually, or use the automatic cardinality detection tool in Designer. Both methods are described below.

Setting cardinalities manually

You can manually set cardinalities for joins by defining cardinality for a join in the "Edit Join" box for a join.

Why set cardinalities manually?

When you set cardinalities manually, you must consider each individual join. This helps you to become aware of potential join path problems in your schema. You may not find these problems if you only select automatically detected cardinalities; for example, isolated one -to-one joins at the end of a join path, or excessive primary keys where not all columns are required to ensure uniqueness.

Understanding keys

You determine cardinalities for most join cases by evaluating the primary and foreign keys in each table. Primary and foreign keys are described as follows:

Primary and foreign keysPrimary and foreign keys

What are the criteria for setting cardinalities?

You evaluate the relationship between primary and foreign keys to determine the cardinality for a join as follows:

What are the criteria for setting cardinalities?

To set cardinalities manually:

  1. Double-click a join.
  2. Or
    Click a join and select Edit > Properties.
    The "Edit Join" dialog box appears.
  3. Select the Cardinality check box.
  4. Select the 1 or N radio button for "Table1".
  5. Select the 1 or N radio button for "Table2"
  6. To set cardinalities manually.

  7. Click OK

Detecting cardinalities automatically

You can use the Designer feature Detect Cardinalities to automatically detect cardinalities for the following situations:

  • Selected joins
  • All joins
  • At join creation
  • From the Edit Join box

When using automatic cardinality detection, cardinalities are implemented automatically on detection.

Note:You should use automatic cardinality detection appropriately. It can be very useful to quickly get all the cardinalities detected in the schema, however, there are a number of structural problems inherent in many relational databases which can lead to incorrect cardinality detection. These include incomplete primary joins, and over engineered primary keys.

Detecting cardinalities automatically for selected joins

To automatically detect cardinalities for a selected join:

  • Click a join and select Tools > Detect Cardinalities.
  • Right click a join and select Detect Cardinalities from the contextual menu.

The cardinality is displayed with the crow's foot at the many end.

Detecting cardinalities automatically for selected joins

If you select Tools > Detect Cardinalities directly without selecting a join, you receive a message indicating that no join is selected, and asking if you want to detect cardinalities for all joins.

Detecting cardinalities automatically for all joins
To automatically detect cardinalities for all joins:

  1. Select Tools > Automated Detection >Detect Cardinalities.
  2. Or

    Detecting cardinalities automatically for selected joins

    Click the Detect Cardinalities button.
    A message box appears asking if you want to detect cardinalities for all joins.
  3. Click Yes.
  4. All joins in the Structure pane are shown with cardinalities.

Automatically detecting cardinalities on join creation

To automatically detect cardinalities on join creation:

  1. Select Tools > Options.
  2. The Options dialog box opens to the General page.
  3. Click the Database tab.
  4. The Database page appears.
  5. Select the Detect Cardinalities in Joins check box.
  6. Automatically detecting cardinalities on join creation

  7. Click OK.
  8. When you create a new join, the cardinality is automatically detected and displayed on the join.

Automatically detecting cardinality from the Edit Join box

To automatically detect cardinality from the Edit Join box:

  1. Double click a join.
  2. The Edit Join dialog box appears.
  3. Select the Cardinality check box.
  4. Click the Detect button.
  5. The cardinality radio buttons are automatically selected for the detected cardinality. The two cardinalities are also expressed in sentence form.

    Automatically detecting cardinality from the Edit Join box

  6. Click OK.

Optimizing automatic cardinality detection

You can improve the response time of cardinality detection by modifying a parameter in the PRM file of the target RDBMS. This directs the detection algorithm to read two instead of three SQL statements, improving the performance of the algorithm.

The PRM file is a text file that lists parameters used to configure universe creation and SQL query generation in Web Intelligence. There is a PRM file for each supported RDBMS.

PRM files are located in the database folders under<INSTALLDIR>win32_x86dataAccessConnectionServer

Verifying which PRM file is used by a connection

To verify which PRM file is used by a universe connection:

  1. Select File > Parameters
  2. The Parameters dialog box appears.
  3. Click the Test button.
  4. The Test Connection message box appears.
  5. Click the Details button.
  6. The details of your connection appear in a drop down message box.
  7. Scroll down the message box to the line that starts with PRM.
  8. This line indicates the file path and name of the PRM file currently used by the active universe.

    Verifying which PRM file is used by a connection

  9. Click OK.
  10. You return to the Parameters dialog box.
  11. Click Cancel.

Optimizing cardinality detection using the PRM file

To optimize cardinality detection using the PRM file:

  1. Open the PRM file for your target database in a text editor.
  2. The PRM files are stored in the Data Access folder in the Business Objects path.

  3. Set the LIGHT_DETECT_CARDINALITY parameter to YES.
  4. Save and close the PRM file.

The next time you open the universe, automatic cardinality detection is optimized.

Using cardinalities to resolve database limitations

You can use the following criteria for determining cardinalities in special join situations, which if untreated, could lead to errors in your schema design:

Using cardinalities to resolve database limitations


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

SAP BO Topics