Connecting to Data and Editing Data

Topics:

The data source being used determines the type of metadata that is required. For example:

Whatever your data source, the adapter you are using manages the synonym creation process for you, creating a synonym that meets your specific requirements.

x
Defining Synonyms

Synonyms define unique names (or aliases) for each table, view, transaction, or procedure that is accessible from the server. Synonyms are useful because they hide the underlying data source location, identity, transaction, or procedure from client applications. They also provide support for extended metadata features of the server, such as virtual fields and additional security mechanisms.

Note: Although all synonym creation panes have the same look and feel, the parameters are specific to each adapter. To obtain detailed information for an adapter, click the question mark (?) next to a parameter.

x
Creating a Synonym With the Connect to Data Wizard

Topics:

How to:

The Connect to Data wizard provides a step-by-step process for configuring adapters that connect to data sources, that you can use to build reports, charts, and visualizations in InfoAssist+.

Using the Connect to Data wizard you can perform the following steps:

  1. Connect to SQL. Configures an adapter, creates a new connection, or changes connection parameters.
  2. Import Metadata. Creates a synonym for one or more data sources.
  3. Edit Metadata. Enhances metadata by adding measures, dimensions, and dimension hierarchies.

Note:

These steps are displayed in the navigation pane, as shown in the following image. The step that you are on is highlighted. Click any step to go directly to that step.

Metadata tool

As you complete the tasks for each step, the tool automatically proceeds to the next step.

x
Launching the Connect to Data Wizard

You can launch the Connect to Data wizard in one of the following ways:

  • From the Resources tree, right-click a domain or folder, point to Metadata, and then click Connect to Data.

  • On the Home page, on the Quick Links tab, click Connect to Data.

  • In the Open dialog box, in InfoAssist+, click Connect to Data.

x
Step One: Connect to SQL

The first step in the Connect to Data wizard is creating a connection to your data. In this step, you can configure an adapter, add a connection, or change a connection.

x
Procedure: How to Configure a New Adapter/Connection
  1. From the Configure connection pane, click New Adapter/Connection.
    The Configure Adapter pane opens, as shown in the following image.

    Configure Adapter pane

  2. Select the radio button for the adapter and click Next.

    Note: You can click the Adapter, Release/Dialect, Adapter Category, or Adapter Sub-category columns to sort the available adapters.

    The Add to Configuration pane opens.

  3. Enter the parameters for the specific adapter and click Configure. For information on the parameters, see the online Help.
x
Procedure: How to Add a Connection
  1. From the Configure connection pane, click Add Connection.

    The Add Connections pane opens.

  2. Select the radio button for the adapter and click Next.
  3. Enter the parameters for the specific adapter and click Configure.
x
Procedure: How to Change a Connection
  1. From the Configure connection pane, click Change Connection.

    The Edit Connections pane opens.

  2. Select the radio button for the connection and click Next.

    Note: You can click the Adapter, Connection, Connection string, or Profile columns to sort the available connections.

  3. Change the parameters for the specific connection and click Configure.
x
Step Two: Import Metadata

The first pane in this step is the Select connection to create synonyms pane. In this step, you select the connection and metadata that you want to import for further use.

x
Procedure: How to Import Metadata
  1. Select the radio button for the adapter and click Next.

    Note: You can click the Adapter, Connection, or Connection string columns to sort the available adapters.

    The Select Synonym Candidates pane opens.

  2. Select or enter the parameters for each screen and click Next.

    The Create Synonym pane opens.

  3. Select one or more synonym check boxes.
  4. Click Next.

    The synonyms are created in the application directory.

x
Step Three: Edit Metadata

The first pane in this step is the Categorize Fields into Measures, Dimensions and Hierarchies pane. In this step, you can modify the view of your measures and dimensions.

The Measure/Dimensions window shows a logical, generic cube, structure of your synonym. By default, the numeric fields are represented as measures, and non-numeric fields are categorized as dimensions and dimension hierarchies, based on joins and selected synonyms. Dimensions and dimension hierarchies reside inside dimension folders, and can include attributes. The icons, located next to each synonym component, represent its type. In the following table, the first column lists the icons, and the second column describes what they represent.

Icon

Identifies

Description

Folder icon

Measure group

Contains individual measures.

My Content Folder Icon

Measure

Resides inside a measure group. Measures can be moved between measure groups and between measures and dimensions areas.

Reports image

Dimension folder

Contains dimensions, dimension hierarchies, and attributes.

Dimension Hierarchy Icon

Dimension Hierarchy

Resides inside the dimension folder and contains individual dimensions and associated attributes.

Visualization Icon

Dimension

Resides inside the dimension hierarchies. Dimensions can be moved between dimension folders, between and outside dimension hierarchies. You can move a dimension into a measure group and turn it into a measure. You can also move a dimension inside an attribute folder and turn it into an attribute.

Attribute Folder Icon

Attribute folder

Contains individual attributes associated with a dimension.

Attribute Icon

Attribute

Resides inside an attribute folder. Attributes can be moved between attribute folders. If you move an attribute outside an attribute folder, it inherits the identity of a hierarchical level to which it is moved.

The following image shows an example of a Measure/Dimensions window showing all of the categorizations that are described above.

Measure/Dimension Window

From the Measure/Dimensions window, you can:

  • Click the Reset to Default button to undo all changes and refresh the view back to the initial categorization of measures and dimensions.

    Note: Dimensions are grouped in hierarchies based on name pattern matching and date-time analysis.

  • Click the Remove Hierarchies button to revert the hierarchical grouping of dimensions to a list of individual columns.
  • Click the Hide All button to move all columns back to the Table/Column window.

    Note: The Table/Column window is hidden by default. To display the Table/Column window, do the following: on the Ribbon, in the Options group, click View, point to Table/Column Window, and then click Show.

  • Drag dimensions, hierarchies, and other columns within the pane to rearrange the order.
  • Select multiple list items by holding the Shift key.
  • Add new variable definitions, segments, fields, defines, computes, and filters to the Measure/Dimensions window.
  • Use the shortcut menu for more actions.

Using shortcut menu options, you can:

  • Right-click the Measure Groups folder, point to Insert and click New Measure Group, or click Sample Data.

    Note: The sample data displays in the Output Window, which is docked in the right pane by default. To undock, maximize, or hide the Output Window, do the following: on the Ribbon, in the Options group, click View, point to Output Window, and then click Show Floating, Show Maximized, or Hide.

  • Right-click a measure and click Properties, Insert (to gain access to the Filter, Define, Group Values, and Compute options), Pivot, Geographic Role, Sample Data, Data Profiling, Impact Analysis, Rename, Hide, Cut, or Paste. The Hide option moves the measure to the Table/Column window.
  • Right-click a dimension and click Properties, Create Hierarchy (to learn more about creating a hierarchy, see How to Create a Hierarchy), Insert (to gain access to the Filter, Define, Group Values, and Compute options), Pivot, Geographic Role, Sample Data, Data Profiling, Impact Analysis, Rename, Show/Hide Attribute, Hide, Cut, or Paste. The Hide option moves the dimension to the Table/Column window.
  • Right-click a hierarchy and click Properties, Remove Hierarchy, Sample Data, Rename, Hide, Cut, or Paste. The Hide option removes the hierarchy and moves the columns contained in the hierarchy to the Table/Column window.

From the Ribbon, you can:

  • View the session log.
  • Click Test in the Diagnostics group to view the Master file and Access file of the current synonym.
  • Undo or redo the previous action.
  • Create a new Define, Compute, or a Variable field.
  • Click Joins in the Metadata group to open the Modeling View window and create a cluster by joining two or more existing data sources. To learn more about creating a cluster, see How to Create a Cluster.
  • Create DBA statements.
  • Click Pivot in the Metadata group to access the pivot options for transforming columns into rows.
  • Stop existing requests and view sample data.
  • Access the View options to customize the Categorize Fields into Measures, Dimensions and Hierarchies pane.
  • Access Advanced Options to customize the synonym.
x
Procedure: How to Create a Hierarchy

When you create a synonym, the Connect to Data wizard creates dimension hierarchies automatically, based on name pattern-matching and date-time analysis. Optionally, you can create additional hierarchies to organize individual columns, enable drill-downs in your data, and add more flexibility to your synonym.

  1. In the Measure/Dimensions window, right-click a dimension folder, point to Insert, and then click New Levels Hierarchy.

    The Hierarchy1 is added to the dimension folder.

  2. Right-click the Hierarchy1, and then click Rename.

    The Rename dialog box opens.

  3. Type the name of your new hierarchy and click OK.

    Note: You can also turn an existing dimension into a dimension hierarchy. To do so, right-click the dimension, and then click Create Hierarchy.

  4. Drag individual columns into the new hierarchy in a logical order to populate it.
  5. To remove a field from the hierarchy, right-click the field, and then click Remove from Hierarchy.
x
Procedure: How to Create a Cluster

Optionally, you can join two or more tables together to create a cluster.

  1. On the Ribbon, in the Metadata group, click Joins.

    The Modeling View dialog box opens.

  2. Click Insert Child.

    The Insert Child dialog box opens.

  3. Click a Master File from the list, and then click OK. You can select several Master Files by holding the Shift key.

    The completed cluster displays in the Modeling View dialog box, as shown in the following image.

    Modeling View dialog box

  4. Click Close.

    The new columns are added to the Table/Column window.

  5. Drag columns to the Measure/Dimensions window to modify the synonym.
x
Procedure: How to Save a Synonym and Use Your Data
  1. Once you have edited your metadata, click Next.

    The Save As dialog box opens.

  2. Enter a name for the synonym in the File Name field and click OK.

    A pop-up window opens, providing access to the following options:

    Create.

    Allows you to create a report, chart, document, or visualization with your new Master File.

    Finish.

    Closes the Connect to Data wizard.

x
Editing And Deleting Metadata

How to:

You are able to edit or delete previously created synonyms using the shortcut menu.

x
Procedure: How to Edit a Synonym
  1. To edit an existing synonym, right-click a domain or folder, point to Metadata, and then click Edit.

    The Reporting Server window opens. A list of synonyms associated with the folder displays in the Applications directory.

  2. Right-click a synonym and click Open, as shown in the following image.

    Reporting Server

    The Categorize Fields into Measures, Dimensions and Hierarchies pane opens, where you can modify your synonym. For more information on the Categorize Fields into Measures, Dimensions and Hierarchies pane, see Step Three: Edit Metadata.

  3. To save your synonym, click File, and then click Save.
  4. Close the Reporting Server window.
x
Procedure: How to Delete Metadata
  1. To delete an existing synonym, right-click a domain or folder, point to Metadata, and then click Edit.

    The Reporting Server window opens. A list of synonyms associated with the folder displays in the Applications directory.

  2. Right-click the synonym that you wish to Delete, point to Data Management, and then click Delete All Data.

    A confirmation window opens, alerting you that you are deleting every row of data.

  3. Click OK to proceed.
  4. To remove the synonym from the view, right-click this synonym again and then click Delete.
  5. Close the Reporting server window.
x
Reference: Configurable Adapters

The following table lists the configurable adapters in WebFOCUS Business User Edition.

Category

Adapter

SQL

1010data

MS SQL Server

Apache Drill

MS SQL Server ODBC

Apache Hive

MySQL

Apache Phoenix

Netezza

C9

Nucleus/SAND

Cache

ODBC

DB2

Oracle

Excel (via ODBC)

Oracle TimesTen

Google BigQuery

parAccel

Greenplum DB

PostgreSQL

Hyperstage

Progress

Hyperstage (PG)

PSQL

IBM i Access

SQLBase

Informix

Sybase

Ingres

Teradata

JBoss

Transoft

JDBC

Vertica

MongoDB

UniData

MS Access

 

XML Based

JSON

XML

ERP

Salesforce.com

 

OLAP

SQL Server Analysis Services

Procedures

REST

Web Services

Sequential and Indexed

Delimited Flat File

Flat File

Excel (via direct retrieval)

MicroFocus CISAM

DBMS

LDAP

Lotus Notes

Social Media

Words Analysis


WebFOCUS

Feedback