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:
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.
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:
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.
You have several approaches to creating joins in Designer:
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:
Defining join properties directly
You create a join by directly defining join properties in the Edit Join dialog box.
To create a join directly:
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:
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.
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.
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.
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:
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:
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.
You define join properties in the Edit Join dialog box. You can define the following properties for a join:
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:
Edit and Parse
The Edit Join dialog box also has two features available that allow you to edit and verify the join syntax:
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.
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:
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:
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:
The Join SQL Definition box appears. The SQL expression for the join appears in the text box.
You can use the editing features to modify or add SQL syntax as follows:
The column, operator, or function appears in the join definition.
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:
To display 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:
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.
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:
To delete a join:
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.
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|
Sap Bo Tutorial
Doing Basic Operations
Creating A Schema With Tables And Joins
Resolving Join Problems In A Schema 4
Working With Olap Universes 6
Creating Stored Procedure Universes
Creating Universes From Metadata Sources
Using The Sample Materials
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.