Introduction to Business Views

Business views are multitiered semantic layers that sit between report authors and end users and their respective data sources. These layers allow the complexities of the data source to be abstracted into logical groupings of the fields or business views.

The architecture provides multiple tiers with each tier performing a different function. This multitiered system is broken down into three layers: the client, business, and data tiers.

Note

The semantic layer is optional. In certain circumstances the costs of building a semantic layer outweigh the benefits. Crystal Reports XI still provides you the flexibility to use a semantic layer, or to access the data source directly, or use any combination of the two. For example, an organization upgrading its reports from earlier versions might be satisfied with the current state of its implementation and hence might never use a semantic layer. Crystal Reports XI even allows for a combination of semantic layer access and direct data source access via subreports.

Client Tier

The client tier consists of the applications that interact with the business views, in particular, Crystal Reports XI, Live Office, and InfoView.

Business Tier

The business tier consists of the components to create and manage the business views:

• Business View Manager

• Data connections

• Dynamic data connections (optional element)

• Data foundations

• Business elements

• Business views

• Dynamic and cascading prompts and prompt groups

At each component level, different security rights can be applied to the components and security setup. This provides great flexibility as to who controls which component. For example, a database administrator (DBA) can set up the data connections and data foundations, but have no rights to view the business element and business view layers.

Caution

There is functionality within certain components that might give users access to view the data. For example, when creating a data foundation, the user can browse the data, which will give her a better understanding of the field. If you do not want her to be able to do so, you need to explicitly remove her rights.

Business View Manager

The tool used to manage the business views and the repository is the Business View Manager, a Windows-based development tool that can be installed locally on the developer's or administrator's Windows workstation. The Repository Explorer allows the user to manage the Business Objects repository. To show the Explorer, click the View menu from within the Business View Manager and then check the Repository Explorer. To reiterate, the objects that can be stored in the Crystal repository are

• Business views

• Command objects

• Custom functions

• Images

• Text objects

It is worth describing some of the functionality of the Repository Explorer because it can become quite large in larger projects, with multiple users adding objects simultaneously. There are certain features that can simplify navigation and security when dealing with many objects.

Sorting Items in the Repository Explorer

As a content creator, you need to navigate quickly and effectively through the repository to reduce content development time. Below are some techniques to aid in the organizing of the Business Objects repository:

• View setting allows the user to filter objects by item type and then sort the object by name or type.

• Advanced filtering turns on filtering by author or filters objects by entering text. Only those objects with the associated text in their names will be displayed.

• Users can add or delete objects or folders, depending on their permissions. Creating your own folder structure can be an efficient way of organizing the repository.

• Before deleting and after adding objects, a user can check his dependencies from the standpoints of what might be dependent on that object or what that object might depend on. For example, if the user creates a data foundation, he can check what data connections it is dependent on by clicking the Show Referenced Objects icon, or the user could check what depends on this object by clicking on the Show Dependent Objects icon.

Setting Security of the Repository

The repository security is controlled via the Repository Explorer found in the Business View Manager. It works on an inheritance mechanism, whereby an object inherits the security settings from its parent and is designed so administrators do not have to set the security for every object. For example, you might have an administrators group that has view, edit, and set security rights, another group called Content Creators that has only view and edit rights, and another group called Everyone that has only view rights. Alternatively, objects can inherit security from the folder they are published to. If you set the security of the root folder, any folders created under the root folder will inherit the root folder's security.

Data Connections

The data connection component is where the user establishes a connection to the data source. The setting up of the connection component is similar to the setting up of a connection in Crystal Reports and, in a lot of cases, uses the same drivers and dialog boxes. The user can enter in a username and password. This is the username that will authenticate against the data source and the username and password are then stored in the repository.

Join Disparate Data Sources

Data in corporations typically sits in multiple data sources. A requirement might exist to pull information from multiple data sources and consolidate this information in a single report. A business view enables the administrator to join disparate data sources together or to link two universes together.

Dynamic Data Connections

Organizations typically have two or three environments. Most have at least a test/development environment and a production environment. In some cases, the test/development environment is split into separate environments. During the development process, reports are developed against the test environment, tested against the test environment, and finally put into production. In prior versions of Crystal Reports, the report had to be opened and the data source mapped to a new data source. Business views can switch data sources dynamically through the use of dynamic data connections. This creates a Crystal Report parameter that prompts the user to select his data source. Alternatively, the report designer can use logic to switch the data source. For example, an organization may have a production database and periodically archive data to an archive database. The report developer can programmatically swap data sources to the archive database, based on the date entered for the data that the user wishes to view.

Using dynamic data connections, users can easily switch data sources at runtime, yet maintain a single report.

The dynamic data connection is an optional component. If the dynamic data connection component is used, the Crystal Report automatically has a read-only parameter created. The parameter's name consists of the data connection's name, concatenated with the dynamic connection parameter, and the parameters available selections consist of the various database options.

Tip

The dynamic data connections are useful for quickly moving from development to test environments. They can also be used to change languages dynamically; for example, if there are English and French databases, the user can switch between the two. However, bitmaps and text objects do not automatically switch and the report designer needs to have one for each language.

If the database is designed with a column that designates language (hence, there would be a row in the table for each language), a row-level filter is required. This is described in the later "Filters" section.

The Data Foundation Component

The data foundation component allows the user to join the various tables exposed by the data connection(s) or dynamic data connection(s). The user would typically have knowledge of the data structures and correct joins. To join a table to another table, simply drag the field from one table to the corresponding field in the other table. The data fields need to be of the same type for the join to be successful. Right-clicking on the join and selecting the link type allows the user to change the join type, as shown in Figure 18.1. The correct joining of the tables, especially when there are multiple disparate data sources involved, is vital from a performance standpoint and for ensuring that the correct data is returned. Related to this is the ability to order the links. The user can set the linking order the business view will use by selecting Order Links from the Linking Diagram menu. In addition, link enforcement can be vital to correct implementation of security filters. For instance, the filter that determines row-level permissions might require information from a particular table. However, if that particular table is not used in the report, you need to force the use of that table to ensure correct filtering. In that case, you can set join enforcement to result in the correct behavior.

Figure 18.1. Double-clicking a join in the data foundation will display the join type.

Data warehouses can have hundreds, sometimes thousands, of tables. The data foundation component can become complex to manage. Following are some pointers to aid in the management of the foundation:

• Map only the tables from which you require information A common mistake is trying to map all the tables in the data warehouse. This might sound obvious, but you might require information only from certain functional areas of the data warehouse and hence you need only to map those tables.

• Keep it simple It generally requires less effort to go from simple to complex than complex to simple. Bite off as much as you can chew, work out the relationships, test, check performance and expected data results, and then take another bite.

• Use indexes For performance reasons, try to join indexed fields (indicated by a colored icon to the left of the field) and have the database do the work, as opposed to the business view. Because indexed fields are much faster to find at the database level, the database can return data much faster, especially in large tables, if the fields used for the join are indexed.

  Tip

  In some cases the index pointer does not appear next to the indexed field. Select the Fetch Table Indexes from the Linking Diagram menu to make these appear.

  
  

• Keep the number of retrieved records to a minimum The data foundation allows the user to create SQL expressions and formulas. SQL expressions are evaluated at the database level, whereas in many cases the formula is evaluated by the Business Objects Enterprise server. The number of records retrieved can be reduced significantly by having the database filter the records based on a SQL expression rather than a formula, effectively pushing the filter into the query itself rather than retrieving all the data and then discarding the unnecessary portion.

• Use the tools available in the Business View Manager A number of tools, described in Table 18.1, are applicable to the data foundation and allow for management of the tables within the business view. All of these can be found under the Link Diagram menu.

  Table 18.1. Available Tools for Managing the Data Foundation

  Tool	Function
  Locate tables	The user can select the table name from a list and be taken to that table in the data foundation. This saves the user from having to search by scrolling.
  Rearrange tables	This attempts to arrange the tables, saving the user from having to move tables around.
  Select visible tables	Allows the user to hide tables and show only required tables. The other tables are still linked; they just do not show up in the window.
  Fetch table indexes	Show what fields are indexed. Related to this is the index legend.
  Change linking view	To allow for more tables to be visible, the view can be changed so that only the table name is displayed and not all the fields are visible.

  
  

SQL Expressions

Within the data foundation components, the user can create a SQL expression. A SQL expression is a SQL statement that is executed on the database. It is typically used to create data fields that do not exist in the database. A good example of using SQL expressions is to join disparate data sources where one of the data sources is missing a relevant field to join on. For example, you might have a database where you want to join a numeric Vendor Number ID field to a Vendor Number field that is of type string in the other data source. In this case, you can create a SQL expression that converts the Vendor Number to a numeric field so that the field types can be joined.

The administrator has the ability to set security on the SQL expression by right-clicking the SQL expression and selecting Edit Rights. See the earlier "Security" section for more detail on setting security.

Formulas

In some cases the database SQL might not support the required functionality, or a calculation might be required using fields from disparate data sources. In this case a formula can be used, as opposed to using SQL expressions. For example, you might have a quantity shipped number coming from a Shipping database and a price field from an Orders database. In order to see the total value of orders shipped, these values could be calculated in a formula.

The administrator has the ability to set security on the formula, and any other object, by right-clicking the formula and selecting Edit Rights. See the earlier "Security" section for more detail on setting security.

Tip

Custom functions stored in the repository can be added to the data foundation and reused within formulas.

Parameters

The user can create parameters at this level to use at the business element level. The parameter can be either static or can make use of list of values or a prompt group. Static parameters are better suited for values that do not exist in the database and are manually entered by the user or the business view designer. Dynamic parameters are better suited for getting the list of values from the database. Implementing parameters at this level ensures reuse and consistency across all your reports.

Filters

Business views use filters to select the data based on a particular logic. In some cases the logic prevents too much data from being returned to the user. For example, the user might be interested in seeing data from the manufacturing division only. Another case might be that the user is not allowed to see the row of data based upon some business security requirement. This is commonly called row-level security. To achieve this, the business view creator makes a filter, or multiple filters, and concatenates them together using either AND or OR logic. He must make sure that the logic is correct. Users can filter data using fields, SQL expressions, formulas, other filters, parameters, Boolean logic, or a special Crystal Reports field called Current CE User. The user right-clicks the filter and selects either the user or the group the filter needs to apply to. By default, the system creates a Full Data Access Filter and a No Data Access Filter. These are the two extremesan organization's security will typically fall somewhere in between. For example, the Accounts Payable department or group might have a filter that filters the transaction type to Accounts Payable.

Tip

If the organization is not using business views or wants to quickly filter a report without having to set up a business view, {CurrentCEUserID} or {CurrentCEUserName} can be used with the Record Selection formula of Crystal Reports.

Associated with row-level security is the concept of column-level security. In this case, the column's data is displayed or not displayed based on certain business logic. For example, if you belong to the Human Resources group, you might have permission to see the Identity Number or Social Security column. All other groups would not have access to that column; however, they can see the rest of the report.

In order to enable the column-level security, right-click on the field in the Object Explorer and select Edit Rights. Select the group or user you want to deny or grant access. A user's net rights can be determined using the Rights Test View described in the Business View section later.

Business Elements

Business elements provide a layer on top of the data foundation. It is at this level that the developer can further abstract the complexity of the database by aliasing the fields with business terminology and logically grouping these fields around business requirements. Here is where the division of labor might occur between technical- and business-oriented focuses. In other words, the business-oriented person might want to start renaming the fields to business terms.

When creating a new business element, the Business View Manager prompts the user for a data foundation. Select the required one. Insert the required fields for the business element. The business element allows for a logical grouping of the fields. For example, the business elements might be designed based on a division or department, such as an accounts payable element.

Applying Changes at the Business Element Level

Making changes, such as security, at this layer will only affect this layer and will not affect the underlying data foundation layer. However, changes made at the data foundation layer will be added to any changes made at this layer. For example, if you denied a user the right to see the Country field at the data foundation level and now you deny the same user the right to see the Region field, when the user runs the report he will see neither the Region nor Country fields. This illustrates the flexibility in making changes for certain business groups without creating multiple similar data foundations.

Business Views

Finally, you can collect business elements into business views. A primary consideration is that a business view must contain business elements derived from the same data foundation. Stated differently, a business view can contain data from only one data foundation.

At this stage in the architecture, the appropriate filtering has been performed and the business view is a cumulative view of all the underlying components.

For example, assume that the database contains multiple company numbers, each representing a separate organization as part of a large conglomerate. The administrator could set up the data foundation and filter by each company number, assigning only that company the rights to the data foundation. Within that specific company, there might be multiple sales departments. This could be defined in a business element; once again, a filter is applied at this level so that only the associated sales department is seen by the relevant personnel. The business view combines the business elements to provide a view of data. This view will be filtered by relevant company and by relevant sales department, which is the cumulative effect of the layers.

To create a new business view, the user can select the New icon, and select a new business view, or from the File menu, select the business view. Select the business elements that are going to be part of the view and save the view.

By right-clicking on the view in the Object Explorer, the user can set the rights to the business view. In this case the rights refer to who has rights to edit, view, and set security on the business view.

The Rights Test view, shown in Figure 18.2, allows the content creator to check each user's net rights for the visible fields. If column-level security is applied, the field has a red cross associated with it and will appear as a NULL value to Crystal Reports. How Crystal Reports is set up to handle the NULL value determines what is displayed. As far as the row-level security is concerned, the cumulative total of all the filters is applied, from both the Element and Foundation levels and is shown as the final filter text.

Figure 18.2. Rights Test View showing the final column- and row-level filters that will be applied for the selected user.

Dynamic and Cascading Parameters

Crystal Reports XI introduces the concept of dynamic and cascading parameters. The ability to dynamically query a field in the database for a list of values and then filter the list of values for the following parameter based on the prior selected value is now supported within business views. However, business views add one further dimensionthe ability to schedule the list of values on a predetermined basis, prepopulating the list of values.

After the user has created the parameters, she can group them logically in the form of prompt groups. The user only has to select the prompt group to apply all the parameters within the group.

Data Tier

The final tier of the architecture is the data tier. This consists of all the data sources available to the business view. The available set of data sources changes depending on the platform Business Objects Enterprise is installed on.

Please check the documentation, specifically platforms.txt (found on the Business Objects Enterprise CD), for a list of available data sources for your install.

Tip

http://support.businessobjects.com/ provides a searchable knowledge base for related patches (hot fixes), knowledge base articles, and technical papers.