Best Practices: There are many different ways to organize your data and configure your joins. When joining your data, it may be helpful to consider the following factors:
- Who is the end user of the data source? Is this a developer who is capable of joining the appropriate tables in a consistent manner themselves to create content in WebFOCUS Designer? If so, then you can create a series of synonyms that they can connect with joins themselves during their analysis or content creation in WebFOCUS Designer, allowing them to independently investigate a wide selection of related data sources.
Alternatively, the end-user may be a power user or business analyst, who just needs access to a synonym so they can create simple content and analytics on the fly, without having to configure their own joins. In this case, you may want to simplify what these users need to do themselves in WebFOCUS Designer by making all of the necessary joins in the metadata using a cluster synonym, which they can use as a data source to create their content.
- Do you plan to reuse the same joins repeatedly in multiple content items? If the answer is yes, and you prefer to provide a consistent data source, then it may be best to join the tables at the metadata level, within your Business View. This ensures that the same exact join is repeated. This reduces the risk of errors or discrepancies produced by joining the tables in two different manners, such as an inner join in one instance and right-outer join in another instance, which can impact results, and produce different reporting outcomes. You can also use the Business View to limit the fields that are available when using the synonym to create content. This helps to improve performance and usability by limiting the availability of unnecessary or extraneous fields.
If the answer is no, and you prefer to give your users the flexibility of creating joins as they create their content, then you can create the synonyms and clusters that your users can then consume in WebFOCUS Designer. They can then opt to join tables ad-hoc or with any other data sources to which you provide access.
- From what databases are you joining tables? Are the tables spread across multiple databases or are they in a single database location? Joining tables or files of different types and data sources will increase the processing time of the join request in WebFOCUS. Similarly, if it is necessary to use DEFINE fields to apply transformations to the data values in order to create your joins, there may be some impact on performance, depending on the complexity of the transformations.
- How familiar are you with the data sources that you are joining? When you select the tables or synonyms that you want to use as part of a cluster synonym or in a single content item, they are joined automatically, if possible, based on certain criteria. If the tables are stored in a relational data source, then they are joined automatically based on primary key and foreign key constraints. If the tables are not stored in a relational database, for example, if they are a flat file or spreadsheet, then they are joined automatically based on matching field names and field formats. Joins created automatically based on field names and formats may not be as accurate as those based on primary and foreign key relationships. This automatic join behavior means that you do not have to determine which tables and fields should be used to create each of your new joins, saving time if there are many tables that you are joining together or if you are uncertain of how to create your joins.
If you are familiar with the tables or synonyms for which you are creating your joins, you may wish to delete or edit some of the automatically created joins and create new joins based on your own specified criteria. When you create new joins, you can specify the existing segment to which the newly added table should be joined, the join type, and the fields from each table that should be used to create the join.