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.
The cardinalities in the above join can be expressed as follows:
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.
You can display cardinalities in the Structure pane using the following symbols:
To display cardinalities:
What cardinalities can be set for a join?
You can set the following cardinalities 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.
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:
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:
To set cardinalities manually:
Detecting cardinalities automatically
You can use the Designer feature Detect Cardinalities to automatically detect cardinalities for the following situations:
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:
The cardinality is displayed with the crow's foot at the many end.
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:
Click the Detect Cardinalities button.
Automatically detecting cardinalities on join creation
To automatically detect cardinalities on join creation:
Automatically detecting cardinality from the Edit Join box
To automatically detect cardinality from the Edit Join box:
The cardinality radio buttons are automatically selected for the detected cardinality. The two cardinalities are also expressed in sentence form.
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:
This line indicates the file path and name of the PRM file currently used by the active universe.
Optimizing cardinality detection using the PRM file
To optimize cardinality detection using the PRM file:
The PRM files are stored in the Data Access folder in the Business Objects path.
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:
SAP BO Related Interview Questions
|SAP BO Interview Questions||SAP ABAP Interview Questions|
|SAP BW Interview Questions||SAP BPC Interview Questions|
|SAP BODS Interview Questions||SAP BDC Interview Questions|
|SAP BW on HANA Interview Questions||Sap Bapi Interview Questions|
|Sap Business One Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.