Working with Metadata in WebFOCUS
Data is integral to building reports, charts, and visualizations in WebFOCUS Designer and other ibi tools. Metadata, or data about data, enables you to efficiently manage and manipulate your data in support of the business applications that you develop for yourself or for other members of your organization. WebFOCUS offers many advantages in metadata management that support and empower developers and business users with access to the information they need to solve problems or make business decisions. This topic provides an overview of working with metadata in WebFOCUS, and describes the concepts and best practices you need to enable your organization’s success.
The Big Picture
Here is an illustration of how WebFOCUS enables you to manage metadata:
When you create content, you can select a data source that you have uploaded to your
environment, or connected to, by providing credentials and connection parameters to a data server.
WebFOCUS can access data from many different data sources, such as spreadsheets, delimited files, and relational and non-relational data sources. To access your data files, WebFOCUS reads the associated metadata which, in WebFOCUS, is referred to as a synonym. The metadata, or synonym, is then consumed by WebFOCUS Designer and other ibi tools, such as InfoAssist, to enable you to manipulate your data as needed. For example, to build reports, charts, and visualizations in WebFOCUS Designer.
When WebFOCUS accesses data, it is initially accessing the metadata representation of the data source. The metadata enables WebFOCUS to access the actual values from the data source, and to determine its overall structure.
Examples of metadata include: a list of the fields in your data source, user-friendly titles for each field, the formats of each field, and other information about the entire data source.
These metadata elements are important in facilitating access to your data for quick, independent content creation, regardless of your level of data expertise, because they allow WebFOCUS to:
- Reference the technical characteristics of the data structures so that it knows how to present these structures to you, and how to write requests for access to the data.
- Enrich columns with clear, functional names to organize the data by categories, remove technical columns without functional interest, and automatically join related tables, which facilitates quick data access without prior technical knowledge.
Metadata can include the type of database, the name of the database in which the data resides, and how the data source is broken up into separate tables or segments, each of which contains a separate set of fields.
In WebFOCUS, your metadata takes the form of a synonym, which is comprised of one or two WebFOCUS files: Master File or Master File + Access File. Synonyms are created automatically when you upload data or connect to an adapter and invoke the Create Synonym process. Depending on the type of data source, a synonym consists of the one or two files, which are saved on the WebFOCUS Server for every data source that you have access to. These files are a Master File (.mas), which describes the structure of a data source, its fields, and the mapping of the data types and, for some data sources, an Access File (.acx).
An Access File supplements a Master File and includes additional information that completes the description of the data source, for example, the full data source name and location. You need one Master File and, for some data sources, one Access File to describe a data source. The adapter that you use to access your data source indicates whether you require an Access File, as well as the Access File attributes. You can find these details in the appropriate documentation for your adapter.
When you select a data source from which to create new content in a WebFOCUS tool, you are actually selecting the Master File that represents the data in your data source. It contains a list of fields and basic properties for each data source. The fields listed in the Master File may be broken up by the table or segment to which they belong.
Typically, the division of fields between different tables in your data source is mirrored in the Master File, although you can reorganize the fields into your own field groups and hierarchies using what is called a Business View. A Business View is a logical view of your fields, organized in a way that is easier to understand for end users, even if it differs from the arrangement of fields in the original data source. Business Views enable you to create a view of the data that represents a business use by creating virtual segments (or folders) and adding fields to those folders. You can reuse the fields in multiple folders, and then categorize them for use in hierarchies.
|Best Practice: A benefit to using Business Views is that only the folder structure, not the real segments, are visible by end users from within WebFOCUS Designer or other ibi tools, and are available for use in other content creation requests.|
To create a synonym, you can select an existing data source in WebFOCUS, or you can upload or connect to your data if it exists on your desktop or on a server.
You can automatically create synonyms using WebFOCUS in the following ways:
- Connect to Data. Use this option to access a relational or non-relational data source. You must configure an adapter for that data source, create a connection to that data source, and then select one or more individual tables or files that are available to that connection. A synonym is created that represents your selected data source or sources. If the synonym will describe multiple tables, they will be related using joins in the synonym. Joins describe how to relate the individual tables.
- Upload Data. Use this option to upload an Excel®, delimited, fixed format, or JSON file from your local file system. A synonym is created automatically as part of the upload process.
Once you have created a synonym, you can edit and enhance it to configure data joins between two or more data sources (add, remove, or edit joins, or fields) and to create new virtual fields or expressions, which are used as temporary fields from which you can report. A Business View is also automatically created in the synonym. The Business View categorizes and organizes the individual fields in the data.
Best Practice: Although there is no limit to the number of synonyms you can place in an application folder, consider organizing synonyms within a series of folders based on their business use. For example, you can organize synonyms by business category, such as finance, or by user groups, such as sales analysts. When you create an organizational structure of application folders, you provide the users who are consuming the synonyms with a structured, simplified list of files from which they can choose.
Best Practice: If you are joining synonyms to create a cluster synonym, which is a synonym that is comprised of multiple tables connected by joins, consider organizing each of your base synonyms in a single application folder, and your joined or cluster synonyms in another application folder. This limits end-user access to the base synonyms application folder, keeping your base synonyms intact. You could then enable end-user access only to the cluster synonyms application folder and the metadata they need. This reduces the level of complexity that is exposed to end users.
Using Business Views
A Business View is created by default when you create a synonym, and is a customized set of fields accessible by users and stored as part of the synonym. Business Views are powerful. Using a Business View, you can reorganize the fields from the original data source in a way that makes sense to the end users who need access to this information to solve a business problem. For example, you can use Business Views to group related fields together, and structure dimension fields into hierarchies. Grouping related fields together makes them easier to find than if they were in a flat list.
You can also use a Business View to indicate whether a field is a measure or a dimension, so that it can be aggregated or used as a sort field by default when you add it to a chart or report in Designer. (Measures are numeric fields that can be used in calculations, while Dimensions are usually not numeric and are used to sort and filter report output.)
While the segments in the synonym describe the physical layout of the data, a Business View is organized into virtual segments called folders. Separate, top-level folders are created to separate and contain measures and dimensions, while lower-level folders can be used to further organize different categories of fields. For example, you may create a folder in a Business View to show dimensions related to products, and another to show dimensions related to stores. You can indicate that these fields are arranged hierarchically, from most general to most specific, allowing you to enable powerful, automatic WebFOCUS reporting features, such as Auto Drill, which allows you to drill through values in a hierarchy, and filter chaining, which applies filtering to filters for fields lower in the hierarchy so that only valid values are available.
|Best Practice: You can also modify Business Views for joined data. For example, you may want to set up a cluster synonym that pulls sales data from multiple tables across an enterprise. You could create a base synonym that does not contain any folder or hierarchical structure, and is simply a flat Master File. Then create your cluster, customizing the Business View organization into folders around the sales data, resulting in a meaningful synonym for your analysis.|
Data sources may contain fields that you do not want to include in your content. For example, ID fields are not always useful in a chart or report, because it is not clear what values they represent. You can hide these fields from the Business View, while retaining them in the synonym, to prevent them from being used in the content. If you open a synonym that contains a Business View in a WebFOCUS tool, only the folders in the Business View display. Any fields in the synonym that have not been added to a folder will not display in the Resources tree.
Since these changes are made on the metadata level, they do not affect how fields are arranged and displayed in the original data source. You can create another synonym later that connects to the same data source, but has a different Business View, if you prefer to highlight a different arrangement of fields.
You can make many enhancements to the synonym from the Business View pane. You can add filters, edit or enhance the Business View, pivot data, or add geographic roles.
You can also enhance a synonym with calculations, which you can then use in your content.
WebFOCUS supports two types of calculations:
- DEFINE calculations. A DEFINE creates a new virtual field, which you can use as you would real fields in your content. The DEFINE calculation is performed for every record in the data source, as WebFOCUS reads each record. DEFINE calculations have certain restrictions on which fields can be incorporated together.
- COMPUTE calculations. A COMPUTE is performed on the data after it has all been read, aggregated, sorted, and selected.
Calculations can be added to the synonym or to the report or chart request.
Some data marts contain pre-aggregated data. If you are accessing such a data source, do not attempt to do further calculations on this data, simply print it as is.
If you do create your own calculations, consider the following:
- Since COMPUTE calculations are performed on a subset of the original data, they can be more efficient. However, COMPUTE calculations do not create new fields that you can use in the content. If you need a field, for example, to use in a join or to use to sort the detail data, use a DEFINE calculation. COMPUTEs in the synonym or request have no effect at design time.
- Since DEFINE calculations in the synonym are parsed, in order to be displayed in the Resources tree, having a lot of them in the synonym will slow the time it takes to display the Resources tree. DEFINEs in a request have no effect at design time.
To add a DEFINE expression, right-click a field that will be used in the calculation, and select New Expression. You can select various ways to create the expression. If you select Advanced Expression, the expression calculator opens, giving you access to all of the available WebFOCUS functions and all arithmetic and logical operators.
The Expression Calculator is shown in the following image.