Reporting Server > Metadata > Using Data Assist

Using Data Assist

Topics:

Data Assist is an environment for managing and editing Synonyms. The number and types of changes you can make to synonyms has been greatly expanded.

Introduction to Metadata

Topics:

Metadata is data about data. Metadata describes the characteristics of your data sources and provides the information needed to access those data sources. This insulates you from the need to know how to optimally access the data, so that you can concentrate on using the data for reporting, charting, visualizations, and applications. Examples of metadata components are data type, data location, and data content and information about how to connect to it. Metadata can be generated whenever data is created, acquired, added to, deleted from, or updated in any data source within an enterprise.

Understanding Data Terminology

The smallest meaningful element of data in a data source is referred to as a column (for relational data sources) or a field (for non-relational data sources). Column and field are used interchangeably throughout this content. Every column or field has several characteristics, such as the data type (character, date, integer, or some other type) and length or scale. Field characteristics can also include output display options, such as comma inclusion, currency symbol, date display, and so on. For consistency, all fields should be included in the metadata, so all users get the same view of the data. However, you can create a logical view of the data in which only a subset of the fields are available.

Certain fields in a data source may have a one-to-one relationship with each other and describe a group of related characteristics. For example, each unique ID number represents only one employee. These fields can be grouped together into a segment or, in relational terms, a table. You can relate segments to each other by creating a multi-segment data source or by joining segments together. Segments are the building blocks of larger data structures.

Fields can be categorized as measures (facts), dimensions, hierarchy levels, or attributes.

A measure or fact is a numeric value, such as Gross Profit or Cost of Goods Sold, that you may want to aggregate. All numeric values that can logically be summed can be categorized as measures. Numeric fields that cannot be summed, such as product number and order ID, are not categorized as measures. Instead, they may be used in the same way as dimension fields to analyze measures. It is up to you to understand your data and determine whether each numeric field should be summed.

A dimension is a way to categorize data or sort output. You can use a dimension to analyze and compare measures. Generally, dimension fields are alphanumeric fields such as product. Certain dimension fields can be organized into hierarchies to define the relationships between the fields in the hierarchies and provide the information needed for the AUTODRILL feature. For example, a Geography hierarchy can contain the Continent, Country, State, and City fields. On report output, with AUTODRILL turned on, links will be provided on report or chart output that let you drill down the hierarchy levels to more detailed reports or charts. You can also define dimension fields that are not part of a dimension hierarchy.

An attribute is a field or collection of fields that add additional information about a dimension.

Using the Business View Plus (BV+) feature, you can create a view of the data that represents its business use by creating virtual segments called folders and adding fields to the folders. You can reuse fields in multiple folders. You can then categorize those folders and fields as measures (facts), dimensions, hierarchy levels, or attributes. Only the folder structure, not the real segments will be visible in the WebFOCUS tools and be available for use in requests. For information about BV+, see Business View Plus (BV+).

Accessing Data

When WebFOCUS accesses data, it needs to know how to interpret the data that it finds. A Master File, which is the primary component of metadata, is a map of the segments in the data source and all of the fields in each segment. The Master File also identifies the name of the data source and the type of data source it is.

For some types of data sources, the Master File is supplemented by an Access File. An Access File includes additional information that completes the description of the data source. For example, it includes the full data source name and location. You require one Master File and, for some data sources, one Access File to describe a data source. These metadata files (Master File and Access File) are referred to as a synonym.

Synonyms are stored separately, apart from the associated data source. WebFOCUS uses a synonym to interpret a data source as follows:

  1. It identifies the data sources available and their respective fields.
  2. It examines the security rules, if Information Builders data source security (DBA) has been specified for the data source, and ensures that user access is allowed based on any DBA security specified.
  3. It identifies the Access File for the data source, if that data source requires an Access File.

The data source content is interpreted based on the information in the Master File and, if applicable, the Access File.

Working With Data Adapters

With the appropriate WebFOCUS Data Adapters configured, you can access data from many different sources, including the following:

  • Relational data sources, such as Db2, Teradata, Oracle, and Sybase.
  • Hierarchical data sources, such as IMS and FOCUS.
  • Indexed data sources, such as ISAM and VSAM.
  • Network data sources, such as CA-IDMS.
  • Sequential data sources, both fixed-format and delimited format.
  • Multi-dimensional data sources, such as SAP BW and Essbase.
  • XML data sources.

Every adapter is specifically designed for the data source that it accesses, and, as a result, is able to translate between SQL or WebFOCUS and the data management language (DML) of the data source. The adapter manages the communication between the data interface and the data source and returns either answer sets or messages to the requestor. Adapters provide solutions to product variations, including product differences in syntax, functionality, schema, data types, catalogs, data representations, message processing, and answer set retrieval. It is the adapter that manages the synonym creation process.

The parameters required to configure supported adapters and to create synonyms for the corresponding data sources are described in detail in the Adapter Administration manual.

Generating Synonyms

Topics:

Synonyms are useful because they insulate client applications from changes to the location and identity of a request. You can move or rename a request without modifying the client applications that use it. You need to make only one change, redefining the request synonym on the server. Synonyms provide support for the extended metadata features of the server, such as virtual fields and security mechanisms. Creating a synonym generates a Master File and an Access File.

Often, a synonym already exists on the server, and reporting can begin at once. However, if the synonym you require does not exist on the server, an authorized server or application administrator can create it directly using the Web Console, the Data Management Console (DMC), or the Metadata canvas in App Studio.

Although, you can use Data Assist to create a new synonym, the situations in which you need to do this are extremely rare. The Web Console, DMC, and Metadata Canvas provide automatic and error-free synonym creation tools.

Once you have generated a synonym, you can report against the synonym. In many instances, the configured adapter and the generated synonym are all you need to access your data and create reports and graphs.

However, you may wish to enhance the synonym in order to implement particular capabilities that are supported in the WebFOCUS data description language. To do this, you can use Data Assist or the Metadata canvas.

Enhancing a Synonym

The following are some of the attributes you might want to add to the synonym to enhance your data access and reporting capabilities. You can:

  • Add virtual columns (DEFINE fields) and columns for aggregated values (COMPUTE fields).
  • Add filters to specify data selection criteria.
  • Add group definitions for data sources that support groups.
  • Add meaningful titles and descriptions, including multilanguage variations.
  • Change the format of fields (for example, the size of an alphanumeric field or the format of a date field).
  • Create a cluster join view by linking available synonyms to create a multisegment (multitable) file for reporting.
  • Create business views of the metadata in order to limit the fields available to any retrieval request that references the business view and to group fields together based on their roles in an application.
  • Define parent and child hierarchies for cube data sources.
  • Apply security rules for fields and values to ensure that user access is based on Information Builders data source security (DBA) specifications.

Accessing Data Assist

You can access Data Assist by:

  • Clicking Data Assist on the Applications ribbon and selecting New Data Assist.

    A dialog box opens for selecting an application and a synonym to open.

  • Double-clicking a synonym on the Applications tree.
  • Right-clicking a synonym on the Applications tree and selecting Open.

The synonym opens in Data Assist.

Understanding the Data Assist User Interface

Topics:

The Data Assist user interface consists of a ribbon and up to four types of panes used for viewing and editing a synonym. You can open multiple synonyms in Data Assist. Each will have its own tab on the Data Assist page.

The number of panes you actually see when you open a synonym depends on the environment from which you are accessing Data Assist and the characteristics of the synonym you open.

Reference: Data Assist Ribbon

The Data Assist Ribbon is divided into sections, as shown in the following image. The sections that display depend on the environment from which you opened the synonym.

File Section

The File section provides the following options.

File Menu

Provides the following file handling options.

New

Enables you to create a new Custom Copy, Upload Data, or Synonym.

Open
Opens the Application Tree so you can open another synonym in the Data Assist window.
Switch Files

Opens a list of files currently open in Data Assist tabs and lets you select one to switch to.

Save

Saves the file in the tab that currently has the focus.

Save all Files

Saves the files open in all tabs.

Save As

Opens the Save As dialog box to save the current file.

Close

Closes the file that currently has the focus. If you have made changes and have not saved the file, a Close dialog box opens asking if you want to save changes.

Exit all without saving

Exits Data Assist without saving any changes.

Close Tool Retaining Context

Closes Data Assist. If you reopen Data Assist, the tabs from the previous session will be intact.

Related Menu

Provides the following options.

Open Base Synonym

This option will only be available if the current tab is for a cluster synonym. It provides a list of the synonyms referenced by the cluster. Select one or more to open them in new tabs.

Custom Copy

Opens a Custom Copy window in a new tab.

Help Section

The Help section provides the following options.

Test Menu

Provides the following options.

Session Log

Opens the Session Log window.

Clear Session Log

Deletes all lines from the Session Log.

View MFD

Opens a text view of the Master File in the Output pane.

View AFD

Opens a text view of the Access File in the Output pane.

Tutorials

Provides two options, Upload Data and Business View Plus (BV+) tutorials. The selected tutorial will open in new window.

Help

Opens help about the wizards (Upload and SQL) and Data Assist.

Undo/Redo Section

The Undo/Redo section provides the following options.

Undo

Undoes the most recent actions. You can set the number of undo actions supported using the Options menu. The default number of undo actions is 50.

Redo

Redoes the most recent actions. You can set the number of redo actions supported using the Options menu. The default number of redo actions is 50.

Metadata Section

The Metadata section provides the following options.

Define

Opens the Add Detail dialog box with an expression editor for creating a virtual field. For more information, see Creating a Virtual Field.

Compute

Opens the Add Summary dialog box with an expression editor for creating a calculated summary value. For more information, see Creating a Calculated Value.

Variable

Opens a Properties dialog box to add a new global variable to the Variables list in the synonym. The new variable is added to the Tables/Columns pane. For more information, see Adding a Global Variable to a Synonym.

Join Editor

Opens the modeling (pictorial) view of the Master File showing the joins in effect. Enables you to edit the joins and create new cluster synonyms. For more information, see Creating a Join.

DBA

Opens the DBA dialog box for adding security rules to the synonym. For more information, see Adding Data Source Security (DBA).

Pivot

Opens a Pivot Columns to Rows dialog box that lets you select columns to display as rows and to define their properties. For more information, see Pivoting Columns.

Sample Data Section

The Sample Data section provides the following options.

Autorun Menu

Provides the following autorun options.

Autorun

Can be Off or On. On creates a tab in the output window with a sample of the current data. Off does not show sample output. On is the default value.

Data Preview

Can be Off or On. On shows sample values in the Table/Columns and Business View panes. Off does not show sample data values in the Tables/Columns and Business View panes. On is the default value.

Sample Data Button

Lets you generate a sample data report of the current synonym in the output pane.

Options Section

The Options section provides the following options.

View Menu

The View menu provides the following options.

Reset View

Returns the Data Assist page to its default view.

Grid Details

Lets you choose whether to show metadata as columns in the Table/Column and Business View panes, or to just show the tree in the panes and show the metadata as tooltips when the mouse hovers over a field.

Table/Column Pane

Lets you choose whether to tile the Table/Column and Business View panes horizontally or vertically and whether to show or hide the Table/Column pane.

Business View Pane

Lets you show or hide the Business View pane.

Filters/Groups Pane

Lets you show or hide the Filters/Groups pane.

Output Pane

Lets you hide the output pane or show it pane floating (as a separate window on top of the Data Assist page), docked (as a pane in the Data Assist page), or maximized (as a full window).

Multiple Output Panes

Lets you select whether to create tabs in the output pane for each change or to shown only the latest output.

Options Menu

Provides the following options.

Advanced

Opens an Options dialog box for setting the following preferences.

Synonym Editor

Includes the following options.

  • Support extended options. Selections are Yes and No. The default value is No. This will add right-click options sort objects and styles to appropriate fields and folders.
  • Enable Quick Copy from inside Synonym Editor. Selections are Yes and No. The default value is No.
Column Management

Includes the following options.

  • Name display strategy. Lets you select the type of metadata to display in the tree. Supported values are Name, Title, Description, Alias. The default value is Title.
  • Function display strategy. Lets you select whether to display function syntax or a short description when you right-click a field and add a function. The default is a short description.
Language Generation

Includes the following options.

  • Use segment to qualify field reference. Defines how field references will be generated in requests. Selections are For duplicate fields and Always. The default value is For duplicate fields.
  • Use application name to qualify synonym reference. Defines how synonym references will be generated in requests. Selections are Yes and No. The default value is No.
Limits

Includes the following options.

  • Maximum number of rows for test. Enter the maximum number of rows for test queries. The default value is 50.
  • Maximum number of columns for test. Enter the maximum number of columns for test queries. The default value is 999999.
  • Maximum number of identical error messages. Enter the maximum number of identical error messages that can be issued. The default value is 10.
  • Random Sampling limit (in %). Enter the maximum sample size for data profiling, as a percent.
  • Undo/Redo Limit. Enter the maximum number of undo/redo actions supported. The default value is 50.
  • Omit missing (null) values in Data Profiling charts. Select Yes to omit missing values in the charts generates for data profiling. The default value is No, which includes missing values.

Understanding Data Assist Panes

Reference:

The Data Assist panes that you see by default depend on the environment from which you are opening a synonym and the characteristics of the synonym. The panes available in Data Assist are:

  • Table/Column pane. This pane shows the physical structure (segments and fields) of the data source.
  • Business View pane. This pane shows the logical view (BV+) of the data source.
  • Filters/Groups pane. This pane shows any filters and groups defined in the synonym or view.
  • Output pane. This pane shows sample data and reports.

Each pane has a menu bar that offers options specific to that pane as well as options that are common to multiple panes.

The following image shows the four panes available in Data Assist.

Reference: Common Data Assist Options

The following menu bar options are common to multiple panes.

  • The Find (binocular search) button is common to all Data Assist panes. It opens a text box for entering a search term, a search icon for starting the search, previous and next arrows for advancing through the search, and a menu for selecting the following search options.
    Case Sensitive

    When checked searches for the term in the case in which it is entered.

    Contains search term

    When selected, searches for any text object that contains the search term.

    Starts with search term

    When selected, searches for any text object that starts with the search term.

    Ends with search term

    When selected, searches for any text object that ends with the search term.

    Show only matched items

    When checked, shows only the matched items in the window after the search. This is checked by default.

  • The X (Hide) button is common to the Table/Column, Filters/Groups, and Output panes. It hides the pane.

In addition, there are common right-click menu options for fields in the Table/Column, Business View, and Output panes. Some of the options that show up are dependent on the data type of the field. For more information, see Right-Click Options for Fields in the Table/Column, Business View, and Output Panes.

Reference: Table/Column Pane

The Table/Column pane displays the physical characteristics of the underlying data sources represented in the synonym. You can use options on the ribbon, or use the context sensitive right-click menu options to make changes to the physical synonym.

The Table/Column pane is shown in the following image.

The menu bar at the top of the pane has the following options.

  • The Display menu at the top of the pane gives you the following display options for the synonym tree.
    Columns/Tables

    Lists the segments and fields in the synonym with their data types and two data values. This is the default display.

    Tables

    Displays the segments (tables) and their relationships in the form of a tree.

    Join Editor

    Displays the modeling view of the synonym, with the Application Tree in the left pane and a pictorial view of the joined tables on the right. You can edit the joins using this display option. For more information, see How to Create or Edit a Join.

  • The View menu gives you the following options.
    Expand All

    Expands the tree.

    Collapse All

    Collapses the tree.

    Choose Columns ...

    Opens a dialog box for selecting the metadata attributes and number of field values to display.

    Reset to Defaults

    Resets the pane to the default view.

Reference: Business View Pane

The Business View pane shows the logical view using folders that has been created in the synonym to create a view (BV+) of the data and lets you add new folders or edit or delete existing folders. You can drag fields from the Table/Column pane. use options on the ribbon, or use the context sensitive right-click menu options to make changes to the folders. For information about BV+, see Business View Plus (BV+).

The Business View pane is shown in the following image.

The menu bar at the top of the pane provides the following options. Note that certain options only appear when the view supports them.

  • The Reset Default button resets the pane to the default view.
  • The Flatten button removes child folders.
  • The Display menu provides the following options for displaying the folder tree.
    Columns/Folders

    Lists the folders and fields in the view with their data types and two data values. This is the default display.

    Folders

    Displays the folders and their relationships in the form of a tree.

    Modeling View of Folders

    Displays the modeling view of the BV+, a pictorial view of the filters, join, and variables.

  • The View menu provides the following options.
    Expand All

    Expands all folders.

    Collapse All

    Collapses all folders.

    Choose Columns ...

    Opens a dialog box for selecting the fields.

    Reset to Defaults

    Resets the pane to the default view.

  • The X (Hide All) button removes the folders from the display.
  • The BV menu provides the following options.
    BV NAMESPACE=OFF

    Enables BV Namespace Off mode. New folders cannot be added to the view, you are limited to adding new DV structures in the synonym.

    BV NAMESPACE=ON

    Enables BV Namespace On mode. You can create a BV+ view of the synonym, adding new folders to the view and assign DV roles.

    For information about BV+, see Business View Plus (BV+).

Reference: Filters/Groups Pane

The Filters/Groups pane shows filters and groups defined in the synonym.

Each filter or group added is displayed in its own card (vertically tiled pane) within the Filters/Groups pane.

The Filters/Groups pane is shown in the following image.

The menu bar at the top of the pane has the following options.

Common Menu Bar Options

Every card in the Filters Groups pane has the following options.

Move Up

Moves the card up in the pane. This option is only available if the card is not the top card in the pane.

Move Down

Moves the card down in the pane. This option is only available if the card is not the bottom card in the pane.

Each group card has an expand button on the menu bar, which expands the Filters/Groups pane. Once the pane is expanded, a Restore button appears for collapsing the pane.

Filter Menu

The down arrow (menu) for a filter provides the following options.

Display Menu

Provides the following options for displaying and selecting the filter values.

Dropdown List

Generates a dropdown list for filter values. When expanded, a check list of values.

Check List

Lists the field values with a check box next to each, and a scroll bar, if needed to scroll through the values. This is the default display option for filters on alphanumeric and integer fields.

Slider

Available for filters on non-integer numeric fields only. Displays a bar with two sliders for selecting From and To values. When you move the sliders, a Reset button displays that resets the range to the entire range of values. This is the default display option for filters on non-integer numeric fields.

Text Box

Opens a text box in which you can enter a value.

Double List

Shows two lists, one with available values and one with selected values. To add values to the Selected Values list, you can right-click a value or multi-selected values (if Multiple Values is enabled) and choose Select. You can also double-click a value or drag values. To remove values from the Selected Values list, you can right-click a value or multi-selected values and choose Unselect. You can also double-click a value or drag values.

Once you have added a value to the Selected Values list, an Unselect all button appears to make it easy to remove all selections.

Multiple Values

Enables multiple selections. This is the default selection option.

Single Value

Disables multiple selections.

Relation Menu

Provides the following operators for the filter.

EQ

Selects fields with values equal to a filter value. This is the default operator for alphanumeric and integer fields.

NE

Selects fields with values not equal to any filter value.

LT

Selects fields with values less than the filter value.

GT

Selects fields with values greater than the filter value.

LE

Selects fields with values less than or equal to the filter value.

GE

Selects fields with values greater than or equal to the filter value.

LIKE

Selects fields whose values match a pattern. This operator is available for alphanumeric fields.

RANGE

Selects fields whose values fall within a range. This operator is available for numeric fields. This is the default operator for non-integer numeric fields.

Variables Menu

Supported for filters on alphanumeric fields. It lists all defined variables for selection and includes the default option to prompt for actual data.

Values Menu

Supported for filters on alphanumeric fields. Provides the following options.

Show Null

Shows null values on the list of selection values.

Hide Nulls

Does not show null values on the list of selection values. This is the default option.

Advanced Menu

Provides the following options.

No delay, immediate update

Updates the filter as soon as you select a value. This is the default update option.

Use Apply button

Does not update the filter until you click the Apply Filter button, which displays on the menu bar when you select this option.

Show selected values on top

Moves selected values to the top of the list. This is the default option.

Keep selected values in place

Does not move selected values to the top of the list.

Hide Filter Card

Removes the filter from the pane, but leaves it in the synonym. To reopen the filter in the Filters/Groups pane, right-click the filter in the synonym and select Edit.

Delete Filter

Deletes the filter from the synonym and the Filters/Groups pane.

Properties

Opens a dialog box showing filter properties in which you can change the title for the filter that appears on output.

Group Menu

The down arrow (menu) for a group provides the following options.

Display Menu

Provides the following options for displaying groups.

Double List

Shows two lists, one with input values and one with selected values. To add values to the Selected Values list, you can right-click a value or multi-selected values and choose Select. You can also double-click a value or drag values. To remove values from the Selected Values list, you can right-click a value or multi-selected values and choose Unselect. You can also double-click a value or drag values. If you right-click in the Input Values list and select Make a new group, the new group will be added in the Selected Values list. The added groups are named based on the values selected and contain the values selected.

Single List

Shows a list of values, initially in one default group. Right-click menu options let you create a new group, copy and paste values, ungroup values, and edit the group name, and recreate groups based on like-sounding values.

Add All

Adds all shown ungrouped values to a new group.

Create a New Group

Creates a new group with no values in it.

Ungroup All

Removes all groups except the default group.

Edit properties

Opens the Edit properties dialog box. You can change the overall group name, title, and default value.

Hide Group Card

Removes the group from the pane, but leaves it in the synonym. To reopen the group in the Filters/Groups pane, right-click the group in the synonym and select Edit Grouping.

Reference: Output Pane

The Output pane shows the results of changes to a synonym or view, sample data output, and output of sample data, impact analysis, and data profiling reports and charts.

Each new output can be added on a new tab in the Output pane or replace the existing tab, depending on your preferences. The following image shows the output pane with two tabs. If there are too many tabs to display in the window, left/right arrows (< >) display for scrolling.

The menu bar at the top of the pane provides the following options.

Double the row limit

Doubles the number of rows used for sample and changed data display.

View as Columns

Displays the output with grid lines separating rows and columns.

View as Tooltip

Displays the output without grid lines.

View Printable Format

Opens the output in a browser.

Choose Columns ...

For certain types of reports, opens a check list for selecting the columns to display.

Reset to Defaults

Resets the output to its default view.

Each tab has a right-click menu with the following options:

  • Close other tabs. Closes all tabs but this one.
  • Close tabs to the right. Closes all tabs to the right. If this tab is the rightmost tab, this option is grayed out.
  • Show only the latest. Closes all tabs but the most recent tab.

On each tab, each column title has a menu (opened using the down arrow) that has the same options as the right-click menu for the field in the Table/Column and Business View panes. For information, see Right-Click Options for Fields in the Table/Column, Business View, and Output Panes.

In addition, the following options are specific to the Output pane.

  • Sort Ascending. Sorts the values in low to high order.
  • Sort Descending. Sorts the values in high to low order.
  • Reset to Original Order. Resets the order to the original when the tab first opened.

Enhancing a Synonym

Topics:

You can enhance a synonym by:

Note: When you enhance a synonym, in most cases you can use the Business View pane to create the enhancement. However, since this pane gives you a logical view of the synonym, it does not give you full access to the features that are added. To have full view of and access to synonym components, you must open the Table/Column pane, if it is not already open. To open the Table/Column pane, click Table/Column Pane on the View menu in the Options group on the ribbon. This pane represents the physical view of the synonym and provides full access to all of its components.

Business View Plus (BV+)

Topics:

Business View Plus (BV+) combines the power and capabilities of Business View (BV) and Dimension View (DV) into one feature that allows a customized view of the data source and enables the use of joins, measures, hierarchies, attributes, expressions, and filters. This allows for robust report, chart, document, and visualization development using a customized logical view of a data source.

A traditional Business View offered users a customized logical view of a data source by grouping related items into folders that reflect business logic for an application, rather than the physical position of items in the data source. However, the fields in these folders did not have any indication of their roles in a request.

A traditional Dimension View, on the other hand, categorized fields on the basis of their roles in a request. Measures were placed in measure groups, hierarchies were organized within dimensions, levels were organized within hierarchies, and attributes were organized within levels. Then, when a field was double-clicked in InfoAssist+ or dragged onto the report or chart canvas in App Studio, it was added as a sort field or aggregation field depending on its placement in the Dimension View structure. Dimension Views, however, offered no ability to create a custom logical view of the data source.

BV+ combines Business Views and Dimension Views by enabling you to group fields into folders and, for each field, assign a role that indicates its role in a request. The syntax is clear and simple, and it provides all of the functions of both traditional Business Views and Dimension Views. In addition, BV+ gives you total flexibility in creating folders anywhere in the structure, and in reusing fields in multiple folders.

For example, if you assign the role DIMENSION to a field, it will automatically be added to the By field container for reports and the horizontal axis for charts if you double-click or drag the field onto the report or chart canvas. If you assign the role Drill Level to successive fields in a folder and turn AUTODRILL on, automatic drilldowns will be generated from the top level to the bottom level on the generated output.

You can create or edit a synonym to use BV+ in the Reporting Server Web Console, the Data Management Console, or the App Studio Metadata Canvas.

Introduction to BV Namespace Modes

Reference:

When you upload a file or create or open a synonym in the Reporting Server Web Console or Data Management Console, the synonym opens in one of two modes for creating a BV+ structure, BV_NAMESPACE=OFF mode or BV_NAMESPACE=ON mode. The mode in which the synonym opens determines the types of BV+ structures you can add to your synonym. It also controls the type of field name qualifiers that will be used, after the synonym is saved, when you create a request using the WebFOCUS tools.

You can set your Web Console preferences to open Data Assist, the wizards, the DMC, and the App Studio Metadata Canvas in your preferred BV Namespace mode by going to the Workspace tab, clicking FOCUS Sets and Info, clicking Settings for Web Console Preferences, and setting the parameter AUTO_BV_NAMESPACE to OFF (the default if you install a new server) or ON. If the synonym already contains a DV or BV structure, the presence of this existing structure will override your setting with BV_NAMESPACE=OFF or BV_NAMESPACE=ON, respectively.

If you create a new synonym, or open an existing synonym that has no DV or BV, it will respect the setting you set in the Web Console. When you upload a file, the synonym has no existing DV or BV structure, so it respects the mode you set in the Web Console.

Reference: BV_NAMESPACE=OFF Mode

BV_NAMESPACE=OFF mode uses physical segment names to qualify field references in reports. If you open an existing synonym that has a DV, it will open in this mode, regardless of the setting you configured. In this mode, you can only add nodes that conform to the DV structure. That is, you can add new measure groups and dimensions under the existing measure group and dimension structure.

The following describes the structures and actions available in new or existing synonyms in BV_NAMESPACE=OFF mode:

  • Creating a new cluster synonym or opening an existing synonym that has no BV or DV. If the synonym has no logical view defined, It opens with three default nodes in the Business View pane, Filters and Variables, Measure Groups, and Dimensions, as shown in the following image.

    These nodes are based on a DV structure, and any edits you make to the synonym in this pane using BV_NAMESPACE=OFF mode must adhere to this structure. The right-click menu options provide only the structures available for a DV. You can insert a new measure group under the Measure Group node or a new dimension folder under the Dimensions node, but you cannot create a new node in the synonym. In a measure group folder, you can only place measures. In a dimensions folder, you can only place dimensions. Under a dimension, you can only add hierarchies, and under a hierarchy field, you can only add attributes.

  • Opening an existing synonym with a DV defined. It opens showing the DV organization and icons in the Business View pane. The synonym syntax is converted to use BV+ folders with the DV structure. You cannot create new folders, you are limited to the structure already defined in the Business View pane. The right-click menu options provide only the structures available for a DV, as shown in the following image that shows the right-click options for the Measure Group folder.
Reference: BV_NAMESPACE=ON Mode

BV_NAMESPACE=ON mode uses logical folder names to qualify field references in reports. If you open an existing synonym that has a BV, it will open in this mode, regardless of the setting you configured. In this mode, you can create your own logical view of the synonym. You are free to add new folders and assign DV roles to fields and folders.

The exception to this is the Upload Wizard. When a file is selected in the wizard, the business view default structure that is automatically populated is based on the three DV nodes. For BV_NAMESPACE=ON, you will be able to insert new folders anywhere in the structure. You are not limited to the three default node.

The following describes the structures and actions available in new or existing synonyms in BV_NAMESPACE=ON mode:

  • Creating a new cluster synonym or opening an existing synonym with no DV or BV. No nodes are pre-configured, as shown in the following image.

    The right-click menu options provide all BV+ options.

  • Opening an existing synonym with a BV defined. It opens in BV_NAMESPACE=ON mode. It will open showing the Business View structure defined within it. The right-click menu options provide BV+ options, as shown in the following image.
Reference: Usage Notes for BV_NAMESPACE Modes
  • When you enhance a synonym by adding a new item to the synonym, such as a virtual field or calculated value, you can hide it in the Business View pane, but that does not delete the object from the synonym. To physically delete an object from a synonym, you must do it from the Table/Column pane, which contains the physical representation of the synonym.
  • In BV_NAMESPACE=ON or OFF mode, the folder structure must include all of the fields referenced in the request, as fields not included in folders will not be accessible.
  • In BV_NAMESPACE=ON mode, moving a field from one folder to another may also make the field inaccessible, as the request will not have the correct folder qualifier for the field.
  • Synonyms in BV_NAMESPACE=OFF mode have the attribute BV_NAMESPACE=OFF on the file declaration.
  • You can change BV Namespace modes in the Reporting Server Web Console by clicking the BV menu at the top of the Business View pane and selecting BV_NAMESPACE=ON or BV_NAMESPACE=OFF.
  • If you generate a request using the WebFOCUS tools in one BV_NAMESPACE mode, it may not run if the synonym is converted to the other BV_NAMESPACE mode, as the field qualifiers will be incorrect for the new mode.
  • If you change the BV_NAMESPACE mode, a popup window opens to warn you of the potential effects on existing requests.
  • When you begin the cycle of developing reports and metadata in parallel, use BV_NAMESPACE=OFF mode to make sure that procedures developed earlier in the cycle will continue to run as changes are made to the metadata.
  • Any changes to metadata should be thoroughly tested before use in production environments.

Assigning DV Roles

In BV+, you define folders, which function as segments to provide a view of the synonym and to define the accessible fields and their relationships. Folder relationships are the same as segment relationships, with parent folders, child folders, and sibling folders.

While you have total flexibility defining a structure using any fields from your data source, when you issue a report request against the synonym, the retrieval path for the data must conform to any constraints imposed by your DBMS entity diagrams and by the rules of WebFOCUS retrieval.

Only the folders will be displayed in the WebFOCUS tools, not the real segments, and only the fields within the folder structure will be accessible for reporting.

You can assign a DV role to a folder or field by right-clicking the folder or field and selecting a DV role. The following image shows the right-click options for a folder.

You can explicitly assign a DV role to a folder or field, or have it automatically inherit its role from its parent. If you explicitly assign a DV role, that role moves with the object if you drag it to another location within the BV+ structure. If you do not explicitly assign a DV role, the role changes as you move the object under a new parent, except if you drop it onto a field with the Drill Level role. If dropped onto a Drill Level field, the moved field inherits the Drill Level role.

The following DV roles can be assigned.

  • Dimension. A dimension field, when double-clicked or dragged onto the report or chart canvas in the WebFOCUS tools, will automatically be added to the request as a vertical (BY) sort field.

    A folder can be assigned the role Dimension.

    A field can be assigned the role Dimension (Standalone) or Dimension (Drill Level). When it is assigned the role Dimension (Drill Level), it will become part of a hierarchy where the levels depend on the order of the fields in the folder. Then, when AUTODRILL is turned on, automatic drill-downs will be created on the report or chart output. The following image shows the choices for DV role when you right-click a dimension field.

    For a folder assigned the DV role Dimension or a field assigned the DV role Dimension (Standalone), the following attribute is added to the folder or field declaration in the synonym. 

    DV_ROLE=DIMENSION

    For a field assigned the DV role Dimension (Drill Level), the following attribute is added to the field declaration in the synonym. 

    DV_ROLE=LEVEL

    A folder can contain only one drill level hierarchy. However, you can use the same fields in multiple hierarchies by placing each hierarchy in a separate folder. A folder with a drill level hierarchy is not limited to just the hierarchy. It can contain other fields with different DV_ROLEs.

  • Measure. A measure field, when double-clicked or dragged onto the report or chart canvas in the WebFOCUS tools, will automatically be added to the request as an aggregated value (SUM), if it is numeric. If it is alphanumeric, it will be added as a vertical (BY) sort field. A folder or field can be assigned the role Measure.

    For a folder or field assigned the DV role Measure, the following attribute is added to the folder or field declaration in the synonym. 

    DV_ROLE=MEASURE
  • Attribute. An attribute field, when double-clicked or dragged onto the report or chart canvas in the WebFOCUS tools, will automatically be added to the request as an aggregated value (SUM), if it is numeric, or as a vertical sort field (BY), if it is alphanumeric.

    For a folder or field assigned the DV role Attribute, the following attribute is added to the folder or field declaration in the synonym. 

    DV_ROLE=ATTRIBUTE
  • Folder. A folder is a virtual segment in a BV+. It can be assigned the roles Dimension, Measure, or Attribute.

    Note: When a folder is inserted as a child of a field, the attribute PARENT_FIELD describes this relationship. By default, such a folder and its fields will be assumed to have the Attribute role.

  • None. If no role is assigned, the field or folder will inherit its role from its parent. If a role has been assigned, you can remove it by selecting the option to inherit its role from its parent, as shown in the following image.
Example: Sample BV+ Declarations

The following declarations show sample BV+ folder and field definitions. Note that the declaration for each field in a BV+ folder specifies the real segment it actually belongs to.

Sample Dimension Folder Declaration

The DV_ROLE for the PRODUCT_CATEGORY folder is DIMENSION.

 FOLDER=PRODUCT_CATEGORY, PARENT=FOLDER1,
    DV_ROLE=DIMENSION, 
    DESCRIPTION='Product and Vendor', $

Sample Attributes Folder Declared as a Child of a Field

The ATTRIBUTES1 folder has the DV_ROLE ATTRIBUTE. Its parent folder is PRODUCT_CATEGORY, and its parent field is MODEL.

FOLDER=ATTRIBUTES1, PARENT=PRODUCT_CATEGORY, PARENT_FIELD=MODEL,
    DV_ROLE=ATTRIBUTE, 
    DESCRIPTION='Model Details', $

Sample BV+ Level Hierarchy and Standalone Dimension

The PRODUCT_CATEGORY folder has a hierarchy defined consisting of the PRODUCT_CATEGORY, PRODUCT_SUBCATEG, BRAND, and MODEL fields. The field VENDOR_NAME is a standalone dimension field. 

 FOLDER=PRODUCT_CATEGORY, PARENT=FOLDER1,
    DV_ROLE=DIMENSION, 
    DESCRIPTION='Product and Vendor', $
    FIELDNAME=PRODUCT_CATEGORY, ALIAS=PRODUCT_CATEGORY, 
      BELONGS_TO_SEGMENT=WF_RETAIL_PRODUCT,
      DESCRIPTION='Product Category',
      DV_ROLE=LEVEL,  $
    FIELDNAME=PRODUCT_SUBCATEG, ALIAS=PRODUCT_SUBCATEG, 
      BELONGS_TO_SEGMENT=WF_RETAIL_PRODUCT,
      DESCRIPTION='Product Subcategory',
      DV_ROLE=LEVEL,  $
    FIELDNAME=BRAND, ALIAS=BRAND, 
      BELONGS_TO_SEGMENT=WF_RETAIL_PRODUCT,
      DESCRIPTION='Product Brand',
      DV_ROLE=LEVEL,  $
    FIELDNAME=MODEL, ALIAS=MODEL, 
      BELONGS_TO_SEGMENT=WF_RETAIL_PRODUCT,
      DESCRIPTION='Product Model',
      DV_ROLE=LEVEL,  $
    FIELDNAME=VENDOR_NAME, ALIAS=VENDOR_NAME, 
      BELONGS_TO_SEGMENT=WF_RETAIL_VENDOR,
      DESCRIPTION='Vendor Name',
      DV_ROLE=DIMENSION,  $

Sample Measure Field

The PRODUCT_COST field has been assigned the DV_ROLE MEASURE.

FIELDNAME=PRODUCT_COST, ALIAS=PRODUCT_COST, BELONGS_TO_SEGMENT=WF_RETAIL_PRODUCT,
      DESCRIPTION='Product Cost',
      DV_ROLE=MEASURE,  $

Creating a Virtual Field

How to:

A virtual field can be used in a request as though it is a real data source field. The calculation that determines the value of a virtual field is performed on each retrieved record that passes any screening conditions on real fields. The result of the expression is treated as though it were a real field stored in the data source. A virtual field defined in a synonym is available whenever the data source is used for reporting.

Procedure: How to Create a Virtual Field

  1. Click Define on the Data Assist ribbon, or right-click the filename node in the Table/Column pane, point to Insert, and select Advanced Define.

    The Add Detail expression calculator opens, as shown in the following image.

  2. Create the expression.

    For more information about creating an expression see Using the Expression Calculator.

  3. Click OK.

Creating a Calculated Value

How to:

A calculated value is a temporary field that is evaluated after all the data that meets the selection criteria is retrieved, sorted, and summed.

Procedure: How to Create a Calculated Field

  1. Click Compute on the Data Assist ribbon.

    The Add Summary expression calculator opens, as shown in the following image.

  2. Create the expression.

    For more information about creating an expression see Using the Expression Calculator.

  3. Click OK.

Using the Expression Calculator

How to:

When you generate a virtual field or calculated value, the Expression Calculator opens with buttons that contain all of the elements needed for generating any type of WebFOCUS expression.

Procedure: How to Use the Expression Calculator

The expression calculator is used for defining virtual fields (DEFINE) and creating calculated summary values (COMPUTE).

  1. Click Define or Compute on the Data Assist ribbon.

    The expression calculator opens, as shown in the following image.

    Note that if you clicked Define, the title in the expression calculator window says Add Detail (Define). If you clicked Compute, it says Add Summary (Compute).

  2. Enter a name for the virtual field or calculated value, or accept the default.
  3. Add a title or leave the field blank. The title will be used as the column heading on reports, if you enter one. If you do not, the field name will be used.
  4. Enter a format for the field or accept the default.

    The default format is A20. You must make sure the format supports the expression you will be creating.

  5. Select the type of elements to show. Choose a view that will help you the most in creating the expression.

    Select one of the following.

    • Metadata. This view shows the fields in the synonym in their logical folders. This is the default view if a Business View already exists.
    • Metadata (Segment View). This view shows the fields in their physical segments.
    • Variables/Functions. This view shows the system variables and functions available.
    • User Functions. This view shows your application tree so that you can access any user functions you have created.

      Note: To be recognized as a procedure that contains user functions, any .fex file that contains user functions (DEFINE FUNCTIONs) must have the following syntax at the beginning. 

      -*DM_JOB_TYPE=512
  6. Choose a view for the metadata pane.

    The default view is to show the columns. You can expand or collapse the tree and select the columns to show. You can also reset the view to its default.

    In addition, you can search for a metadata element using the binocular search function. Clicking the binoculars opens a text box for entering a search term and navigation buttons for searching backward and forward.

  7. Create the expression.

    In the Expression pane, you can enter an expression in the text box or double-click fields from the metadata pane and use the calculator buttons to create the expression. For more information about expressions and operators, see the Creating Reports With WebFOCUS Language manual. The buttons are:

    • Relational Operators.
      • LT. Less than. Is true if the expression on the left evaluates to be less than the expression on the right.
      • EQ. Equal to. Is true if the expression on the left evaluates to be equal to the expression on the right.
      • GT. Greater than. Is true if the expression on the left evaluates to be greater than the expression on the right.
      • LE. Less than or equal to. Is true if the expression on the left evaluates to be less than or equal to the expression on the right.
      • NE. Not equal to. Is true if the expression on the left evaluates to be not equal to the expression on the right.
      • GE. Greater than or equal to. Is true if the expression on the left evaluates to be greater than or equal to the expression on the right.
    • Conditional Operators.
      • IF. Specifies the condition to be tested.
      • THEN. Specifies the result if the condition is true.
      • ELSE. Specifies the result if the condition is false.
    • Logical Operators.
      • AND. Is true if both expressions are true.
      • OR. Is true if at least one of the expressions is true.
      • NOT. Is true if the expression is false.
    • Alphanumeric Operators.
      • |. Weak concatenation.
      • ||. Strong concatenation (remove trailing blanks).
      • ' '. Enclose in single quotation marks.
    • Arithmetic Operators.
      • **. Raise to a power.
      • +. Plus. Add the numbers on either side of the + sign.
      • -. Minus. Subtract the number on the right of the minus sign from the number on the left.
      • *. Multiplication. Multiply the number on the left by the number on the right.
      • /. Division. Divide the number on the left by the number on the right.
    • DateOperators.
      • Date. Opens a dialog box with the current date entered and lets you select a date using a calendar control.
      • Datetime. Opens a dialog box with the current date and time entered and lets select a date using a calendar control and a time using drop-down lists.
    • Case Operators.
      • a > A. Changes the case to uppercase.
      • A > a. Changes the case to lowercase.
    • Other Buttons.
      • Validate. Checks if the expression entered is valid.
      • Function Assist. Helps you apply a function to an expression. First you must change the view in the metadata pane to show the list of functions and select a function. Clicking Function Assist will open a dialog box that describes what the function does and lets you select a field from the metadata. You can click the Example button to see an example of the function syntax. Click OK to add the function to the expression or Cancel to cancel.

    You can clear the current expression by clicking the Clear (X) button.

  8. Click OK to create the Define or Compute, or Cancel to cancel.

    The virtual field or calculated summary field is added to the list of fields in the Metadata pane.

Adding a Global Variable to a Synonym

How to:

You can define global variables in a Master File and use them to parameterize certain attributes in the Master File and its corresponding Access File. You can also add certain system variables to a synonym.

Procedure: How to Insert a Global Variable

  1. Click Variable on the Data Assist ribbon.

    The Properties window opens, as shown in the following image.

  2. Enter a name for the variable or accept the default.
  3. Enter a prompt caption, if needed, for describing what type of value to enter when prompted for the value.
  4. Enter a default value, if there is one.
  5. Select the type of value container to show, when a request is run.

    You can select from the following General options:

    • Input box. The user must enter a value.
    • Static list of values. The user must select from a predefined list of values. If you select this option, additional input fields appear in which you list the static values and select whether the user can select a single value or multiple values.

      Enter a list of values enclosed in single quotation marks and separated by the logical operator OR.

    • Static list of values/captions. The user must select from a predefined list of values. If you select this option, additional input fields appear in which you list the static values and the captions that should display instead of the actual values, and select whether the user can select a single value or multiple values.

      Enter a list of pairs, each pair containing the actual value and the caption to appear separated by commas. The list must be enclosed in parentheses. Each entry must be enclosed in single quotation marks and each pair must separated by commas.

    • Synonym based dynamic list of values. The user must select from a list of values that is generated dynamically by looking up field values in a lookup synonym. If you select this option, additional fields appear for entering or selecting the lookup synonym, the lookup field, the display field for the caption to appear, and whether the user can select only a single value or multiple values.
    • Procedure based dynamic list of values. The user must select from a list of values that is generated dynamically by running a procedure that retrieves the lookup field values and display field values. If you select this option, additional fields appear for entering or selecting the lookup procedure, any parameters required for running the procedure, the lookup field, the display field for the caption to appear, and whether the user can select only a single value or multiple values.

    You can also enter or select the following Advanced properties for the variable.

    • Whether to add single quotation marks around strings.
    • The data type.
    • The length of the variable value.
  6. Click Apply to save these properties or Cancel to cancel..

Creating a Join

How to:

A join describes a relationship between multiple synonyms based on a condition. The condition may be equality between fields in the synonyms or the result of an expression that relates fields in the synonyms. For more information about joins in a synonym, see the Describing Data With WebFOCUS Language manual.

Procedure: How to Create or Edit a Join

  1. To create a new cluster synonym, from the Application tree right-click an application, point to New, and click Synonym via Data Assist, or, to edit an existing join open an existing synonym in Data Assist.
  2. Click the Join Editor button on the Data Assist ribbon.

    The Join Editor opens, as shown in the following image for an existing synonym..

    If you are creating a new cluster synonym, the right pane will be empty and you can double-click or drag synonyms into it.

  3. To insert a new root synonym, click Insert Root on the Join Editor menu bar.

    The Insert Root window opens, as shown in the following image.

    Select a synonym to insert as a root.

    You can also drag a synonym from the tree, and set up the join manually.

  4. To insert a new child synonym, click Insert Child on the Join Editor menu bar.

    The Insert Child window opens, as shown in the following image.

    Select a synonym to insert as a child.

    You can also drag a synonym from the tree, and set up the join manually.

    Use the Filtering menu to help with selection.

  5. Click OK when you are finished.
  6. You can edit the join properties by right-clicking a join connector.

    Select from the following options.

    • Join Multiple. Makes the join a one-to-many join.
    • Join Unique. Makes the join a one-to-one join.
    • Inner Join. Selects only those records that have values that satisfy the join condition in both synonyms.
    • Left Outer Join. Selects all records from the host synonym. Where there is no record that satisfies the join condition in the cross-reference file, default values are substituted.
    • Delete Link. Deletes the join.
    • Edit Parent Link. Opens a window for selecting new join fields and changing the join type and condition as shown in the following image.

      To test your changes, click Test.

      When you are finished, click OK or Cancel to cancel.

  7. Close the Join Editor using the x button when you are finished.

You can also make the following edits:

  • Click Delete All to delete all joins.
  • Click Undo to undo your last action.
  • Click Redo to redo your last action.
  • Click View to change the view.

You can collapse all of the synonyms so that the fields do not show. You can expand all of the synonyms, you can expand the synonym boxes to full size, set them to standard size (to show four fields at a time), or customize which dimensions are set to standard or full size. You can select Auto Arrange to automatically arrange the synonyms.

Click Options to set join defaults. The options are:

  • Insert child segment with snowflakes. The default is No, to insert only the segment selected.
  • Default Join Type. The default is Multiple, to create one-to-many joins.
  • Display full component names. The default is Yes.

Click OK to save your defaults.

Adding Data Source Security (DBA)

How to:

The DBA facility lets you define restrictions and access rights for users.

Procedure: How to Add DBA Security

  1. Click DBA on the Data Assist ribbon.

    The DBA window opens, as shown in the following image.

  2. Click Initialize DBA.

    A DBA section is added to the synonym, as shown in the following image.

    The Initialize DBA selection is replaced by a Remove DBA selection.

    • The top node, named DBA1 by default, is the password for the Database Administrator (DBA). The DBA has complete access, so no access rights need to be defined.
    • The next node, named USER0001 by default, represents a user password. By default, the user is given read access to the data source.

    You can rename the passwords and add and change access rights using right-click options. You can undo actions using the Undo link on the menu bar.

  3. To change a password, right-click a user and select Rename.

    A Rename dialog box opens.

    Enter the new password and click OK.

  4. To add a user, right-click the DBA password, point to Insert, and select User.
    A new user node is added to the DBA Statements pane.
  5. To add an access right to a user password, right-click a user password, point to Insert, and select one of the following:
    • Read Access. This allows the user to only read the data source.
    • Write Access. This allows the user to only write new segment instances to the data source.
    • Read/Write Access. This allows the user to both read from and write to the data source.
    • Update Access. This allows the user to only update records in the data source.

    The access right is added under the user password. You can then restrict those rights to specific segments, fields, or field values.

  6. You can further restrict the access rights of a user based on segment names, field names, and field values.
    1. Field Restriction. To prevent access rights to a field, do the following.
      1. Right-click an access right under the user you want to restrict, point to Insert, and select Field Restriction as shown in the following image.

        A field restriction placeholder is added under the access right.

      2. Drag a field to the placeholder.

        The placeholder is replaced by the field, as shown in the following image.

      3. To restrict access to additional fields, repeat these steps for each additional field.
    2. Value Restriction. To prevent access rights to specific field values, create a filter.
      1. Right-click an access right under the user you want to restrict, point to Insert, and select Value Restriction.

        A value restriction placeholder is added under the access right.

      2. Drag a field to the placeholder.

        The placeholder is replaced by the field, with a default expression. A Restrictions dialog box opens for you to create the expression that will identify the restricted values, as shown in the following image.

        For a non-integer numeric field, a text box opens in which you can enter values. Using the menu on the top right of the Restrictions pane, you can change the display to a slider or a check list. For alphanumeric or integer fields, the default display is the check list. You can multi-select values for the EQ and NE relations. If your display option is a text box, you can click the Add Value (+) button on the menu bar to add another text box. To delete a value, click the Delete Value (-) button on the left of the text box that contains the value you want to delete.

        By default, the EQ (equal to) relation is used, so the restriction is in effect for field values equal to the values you enter or select. To change the relation, point to Relation on the menu and select one of the following relational operators.

        • EQ. Equal to. The restriction is in effect for field values equal to any of the values specified in the restriction expression.
        • NE. Not equal to. The restriction is in effect for field values not equal to any of the values specified in the restriction expression.
        • LT. Less than. The restriction is in effect for field values less than the value specified in the restriction expression.
        • GT. Less than. The restriction is in effect for field values greater than the value specified in the restriction expression.
        • LE. Less than or equal to. The restriction is in effect for field values less than or equal to the value specified in the restriction expression.
        • GE. Less than. The restriction is in effect for field values greater than or equal to the value specified in the restriction expression.
        • Range. From-to. The restriction is in effect for field values greater than or equal to the first value in the range and less than or equal to the second value in the range.

        Once you have created an expression, Advanced options become available on the menu. You can update the expression immediately (the default) or use an Apply button. For check lists you can move selected values to the top (the default) or leave them where they are.

    3. Segment Restriction. To prevent access to one or more segments, do the following
      1. Right-click an access right under the user you want to restrict, point to Insert, and select Segment Restriction.

        A segment restriction placeholder is added under the access right.

      2. Drag a segment to the placeholder.

        The placeholder is replaced by the segment, as shown in the following image.

      3. To restrict access to additional segments, repeat these steps for each additional segment.
    4. Noprint Restriction. To let the user access a field or segment for retrieval but not display its value on output, do the following.
      1. Right-click an access right under the user you want to restrict, point to Insert, and select Noprint Restriction.

        A Noprint restriction placeholder is added under the access right.

      2. Drag a field to the placeholder.

        The placeholder is replaced by the field.

      3. To restrict print access to additional fields, repeat these steps for each additional field.
  7. When you are finished adding restrictions to the synonym, close the DBA window and save the synonym.

    In order to access the synonym, a user must issue a SET PASS command and enter one of the user passwords. This command can be issued in any supported profile or in a procedure.

Pivoting Columns

How to:

You can pivot columns that represent identical data or groups of such columns to display them as rows. This is useful when you want to access their values as one field. For example, if a spreadsheet has a column for each year, you may want to create a row containing all of the years for reporting.

Procedure: How to Pivot Columns

The spreadsheet pivot_demo.xlsx is created as part of the WebFOCUS Retail tutorial and can be uploaded to demonstrate this feature.

  1. Click Pivot on the Data Assist ribbon.

    The Pivot dialog box opens, as shown in the following image.

  2. Enter or select the following values.
    • Pivot Type. Select Repeating column or Repeating group of columns.

      If you select Repeating group of columns, some of the information you need to enter changes, as shown in the following image.

    • First Column. This is the first column in a group of repeating columns.
    • Last Column. This is the last column in a group of repeating columns.
    • Number of groups. This is the number of groups of columns for repeating groups of columns. For example, if an Excel spreadsheet has data that includes two columns of consecutive data for Plan and Actual figures for six years, this number would be 6.
    • First column in group. Is the name of the first column in a group. For example, if an Excel spreadsheet has groups of data consisting of two columns called year_plan and year_actual, the first year_plan of the first group would be selected for First column in group.
    • Last column in group. Is the name of the last column in a group. For example, if an Excel spreadsheet has groups of data consisting of two columns called year_plan and year_actual, the first year_actual of the first group would be selected for Last column in group.
    • Column Title for Pivoted Value. This is the name for the new column containing the data values. the default is Pivot value. For groups, separate the titles for the columns in the groups with slashes (/).
    • Column Title for Pivoted Key. This is the name of the new column that identifies the values in the data column. If the source is a spreadsheet this would be the name that could apply to the column headers. The default is Pivot key.
    • Formula for Pivoted Key. This is automatically calculated based on the values selected for First column/Last column or First column in group/Last column in group.
  3. Click OK.

    The data is pivoted using an occurs attribute. The following image shows the synonym and its output pane after pivoting the years from 1960 to 2012.

    In the original synonym, each year was a column, but in the pivoted synonym, each year is a row.

Other Synonym Enhancements

Other synonym enhancements are available for fields in the Table/Column, Business View, and Output panes, by right-clicking a field. Some of the right-click menu options that are available depend on the data type of the field.

Reference: Right-Click Options for Fields in the Table/Column, Business View, and Output Panes

Insert

Right-click a field to insert one of the following.

  • Filter. You can open the Filters/Groups pane to create a filter for the field that will select only values satisfying the filter expression. For more information, see Filters/Groups Pane.
  • Function. The types of functions available depends on the data type of the field. For more information about individual functions, see the Using Functions manual or generate a Function Parameters report from the FOCUS SETs and Info button on the Workspace page of the Web Console.

    Point to a type of function and select one of the functions from the list that appears, as shown in the following image.

    Selecting Advanced Function opens the expression calculator, described in Using the Expression Calculator.

    Once you select a function, a dialog box opens where you can enter any necessary parameters and enter a name and title for the virtual field, as shown in the following image.

    When you are finished entering parameters, click OK.

    The virtual field is added to the synonym, as shown in the following image.

  • Grouping. This option opens the Filters/Groups pane so that you can categorize the field values into groups for sorting. It creates a new virtual field using a conditional expression, as shown in the following image.

    For more information about the Filters/Groups pane, see Filters/Groups Pane.

    To open the Filters/Groups pane to edit the grouping, you can right-click the virtual field and select Edit.

    To open the expression calculator for more advanced editing, right-click the virtual field and select Edit (Advanced).

  • Phonetic Grouping. This option is available for grouping alphanumeric fields based on one of two methods for encoding names based on pronunciation, Soundex or Metaphone. Soundex calculates a code for alphanumeric values so that if they sound the same but are spelled differently, they will still have the same code. Metaphone is an improved version of Soundex that takes into account anomalies in English spelling and pronunciation. During upload, this option can correct spelling inconsistencies before actually uploading the data.

    For example, upload the file sales_orders_misspelled_cities.csv that is in the Uploads folder of the WebFOCUS Retail application.

    Right-click the City field in the Geography dimension, point to Insert, and select Phonetic Grouping. The group will initially open in the Filters/Groups pane with the data values grouped based on the default phonetic algorithm for your operating environment, as shown in the following image.

    Each group contains different spellings of city names that would be pronounced the same way based on the phonetic algorithm used. If you do not agree with a grouping, you can edit it. Any data value that could not be placed in a group based on the phonetic algorithm is placed in a group called (Ungrouped Values). You can move those values into another group, if necessary.

    A virtual field is created with an expression that causes all of the values within each group to be uploaded with the correct spelling, as shown in the following image.

    You can upload both the CITY and CITY_GROUP fields, or hide the CITY field and rename CITY_GROUP to CITY so that the CITY field has the corrected data values.

    The options on the group menu specific to phonetic grouping are:

    • Recreate groups by Metaphone.
    • Recreate groups by SoundEx.

    For information about other options for groups, see Filters/Groups Pane.

  • Binning. This option opens a wizard for dividing the data values in the field into a specified number of bins based on equal intervals, equal intervals using a formula, or quantiles, as shown in the following image.

    The default number of bins is 10, but you can specify a different number for equal intervals and quantiles. For equal intervals via formula, you specify the bin width.

    The virtual fields created using this option can be used for sorting.

    The following image shows the binning card for bins based on equal intervals.

    The virtual field is added to the synonym, as shown in the following image.

  • Subquery. If your data source is relational, you can select this option to create a field that runs a subquery to retrieve the values of the specified field.
Geographic Role

You can right-click a field and assign it a geographic role, or change its geographic role assignment if it already has one, as shown in the following image.

To remove a geographic role, select (None).

For a field that has a geographic role assigned, you can generate a sample map, as shown in the following image.

Sample Data

You can right-click a field and select Sample Data to generate sample data for that field, as shown in the following image.

Partial Output is shown in the following image.

Data Profiling

Data profiling provides reports and charts analyzing the values in a field or, for some reports, for a segment. For most reports, a View menu on the menu bar of the reports lets you generate a printable view of the report, and a binocular search function lets you search for characters. Right-click a field or segment, point to Data Profiling, and select one of the following options.

  • Statistics. This report lists counts, percents, limits, distinct values, and information about null values in a field or a segment, as shown in the following image.
  • Key Analysis. This report lists counts and distinct and duplicate counts and percents for a field or a segment, as shown in the following image.
  • Patterns. This report shows the patterns of values for alphanumeric fields. The capital A character represents a capital letter, the lowercase a character represents a lowercase letter, and a 9 character represents a numeric character. Other characters represent themselves, as shown in the following image for the customer email field.
  • Hex View. This report lists the values in a field and their corresponding hexadecimal equivalents, as shown in the following image.
  • Values. This report lists the values, count of duplicates, and corresponding percent, as shown in the following image.
  • Values Graph. This chart shows a horizontal bar graph of the field values, as shown in the following image. The tooltip for each bar also shows the count of instances of the field.
  • Values Pie Graph. This chart shows a pie chart of the field values, as shown in the following image for the days delayed field. The tooltip for each bar also shows the count of instances of the field and the percent of the total.
  • Duplicate Values. This report lists the values that have multiple instances, the count of instances, and the corresponding percent, as shown in the following image.
  • Outliers. This report shows high and low values with a count of instances that fall outside the bulk of the data for a field, as shown in the following image.

WebFOCUS

Feedback