Business Object Views in the EIQ ProduCt Suite.. 1

Specifying JOINS in the EIQ Server Configuration Tool 1

Editing an Existing Business Object View.. 6

Querying Business Object Views. 7

Catalog Statements. 8

SELECT Statements. 10

Business Object Views on Federation Servers. 11

Creating a Business Object View with a Table Alias. 12

Creating the Federation Server 15

Querying the EIQ Federation Server 19

Business Object Views in the EIQ Product Suite

Overview

By default, the EIQ SuperAdpter SuperSchema interface presents a view called MyTable. MyTable is a virtual, de-normalized/flattened view of the data source schema and uses Automatic Query Processing(AQP) to automatically figure out the optimized join path among the native schema tables. The join type, order of the tables, and join conditions are determined by the Primary Key – Foreign Key configurations provided during the EIQ Index build (and stored in the EIQ Index) as well as the join type specified at the client session level.

An alternative to the default MyTable view based on AQP JOINs is a Business Object View. Business Object Views allow configuration users to provide specific JOIN conditions required for a business rule, thus overriding AQP. This gives the EIQ SuperAdapter configuration user the flexibility to specify the join rule among the native tables when needed to answer particular business questions. Business Object Views co-exist with MyTable.

In the default scenario, a basic query on a virtual data source with a SuperSchema interface looks like this:

SELECT XXXX, XXXX FROM MyTable WHERE…..

Here, MyTable is the default table available through the SuperSchema interface. The columns belonging to MyTable are determnined by the SuperSchema mappings to native schema table columns.

Internally, this query is translated into a JOIN query on native schema tables and looks like this:

SELECT XXX, XXXX FROM [join clause] WHERE…..

For Business Object Views, configuraiton user needs to provide the definition of the view and the JOIN rule associated with it using the EIQ Server Configuration Tool. This user provided JOIN rule overrides the AQP and determines the JOIN clause when the flat Business Object View query is translated.

Similar to MyTable, the Business Object View columns are determined automatically by the underlying SuperSchema mappings but limited to only the mappings belonging to the native tables mentioned in the JOIN rule.

Business Object View query:

SELECT XXXX, XXXX FROM Patient_Obj WHERE…..

Internally, the Business Object View query is translated into a JOIN query on native schema tables and looks like this:

SELECT XXX, XXXX FROM [join clause] WHERE…..

Specifying JOINS in the EIQ Server Configuration Tool

Business rules are created through JOIN specification using the EIQ Server Configuration Tool. This is done under the ‘SuperSchema Mapping’ tab. Select a data source node in the left pane of the tab, for example, the external data source WORKS under the WBLT_VDS. Once it is selected, there are two indexed tables shown: PERSON and PERSON_PHONE. Perform the following SuperSchema mapping.

In order to configure a business object view under this tab, select ‘Business Object Logic Table’ under the WORKS database item. The list of business object views appears on the right hand side.

Currently, there is one view called PAT_OBJ with a JOIN rule already defined. A preview of this JOIN can be seen under ‘Join rule’.

Configuring a Business Object View

To configure a new business object view, follow these steps:

· Right-click in the blank space on the right and select ‘New…’ from the context menu.

· This opens a dialog box where users can configure a new view.

· Use the following dialog box to configure a new view.

· Enter a name for the new view in the ‘Name’ box.

· Enter the join clause in the dialog box labeled “Join rule as join clause in SQL”.

The available data source tables and table aliases are shown in the dialog box labeled “Tables”.

· Select a table item from the list.

The list labeled “Columns” shows the available columns in the selected data source table. The user can enter the JOIN clause directly into the “Join rule as join clause in SQL” box or double-click an item in either the ‘Tables’ or ‘Columns’ list. The selected item will be added to the end of the rule.

· Add desired tables and columns to the JOIN rule

An example join clause may be:

"dbo"."PERSON" left join "dbo"."PERSON_PHONE" on "dbo"."PERSON_PHONE"."ID" = "dbo"."PERSON"."ID"

· Click ‘Apply’ to validate the syntax.

If it is a valid clause, the dialog box labeled “Metadata Columns” will display a list of standard data model columns currently mapped in the native tables or table aliases present in the JOIN clause.

· Click ‘OK’ to finish configuring the business object view.

Editing an Existing Business Object View

If a business object view ever needs to be changed or viewed, follow these steps:

· Right-click the desired business object view and select “Edit…” from the context menu.

· This opens a similar dialog box where users can update the configuration of an existing view.

· Use the dialog box to make the necessary changes on the join rule.

To delete an existing view, right-click the appropriate view from the list and select “Delete” from the context menu. Once the necessary configuration or editing is complete, click ‘Save Now’ at the bottom of the window. The configuration will be sent to the EIQ Server where it is stored in the EIQSetting DB.

Querying Business Object Views

In order to see the business object in action, connect to the WBLT_VDS using the EIQ Server Query Tool.

· Enter the credentials for the tool.

· Select the WBLT_VDS.

· Connect using the ‘SchemaInterface = StandardDataModel’ string.

Catalog Statements

Performing a basic catalog statement shows the new business object view.

Execute “SCHEMA TABLES” against the WBLT_VDS. The results contain MyTable and the business object view ‘PAT-OBJ’.

Business object views have BusinessObject as its schema instead of SuperSchema.

Execute “SCHEMA COLUMNS” against the WBLT_VDS. The results contain:

· MyTable (ROWID from 0 to 5)

· The business object view defined on the SuperAdapter, a.k.a ‘PAT_OBJ’. (ROWID from 6 to 11)

The result sets will change if a user modifies the SuperSchema mappings or business objects views.

SELECT Statements

Now that you’ve seen what tables and columns are available, you can query the data.

Execute “SELECT * FROM PAT_OBJ”

The “SELECT * FROM PAT_OBJ” statement is translated into SQL with native schema:

SELECT "dbo"."PERSON"."DateofBirth",

"dbo"."PERSON"."FirstName",

"dbo"."PERSON"."LastName",

"dbo"."PERSON_PHONE"."PhoneNumber"

FROM

"dbo"."PERSON" left join "dbo"."PERSON_PHONE" on "dbo"."PERSON_PHONE"."ID" = "dbo"."PERSON"."ID"

Note: The bold texts shows the join rule configured through the EIQ Server Configuration Tool.

Querying against the business object returned all the mapped tables for that particular join.

Business Object Views on Federation Servers

Business object views can also be viewed across SuperAdapters on an EIQ Federation Server. This is done by creating business object views with the same name on multiple adapters and pointing those adapters to the same EIQ Federation Server.

Creating a Business Object View with a Table Alias

The WBLT_VDS already has a business object view configured. Next, you’ll need to configure one on a different adapter. For example, use the PBLT_VDS created on the PM database.

· Return to the EIQ Server Configuration Tool and click on the ‘SuperSchema Mapping’ tab.

· Expand the PBLT_VDS and click on the PM database node.

This VDS has a table alias. Table aliases can also be joined in a business object as long as there is a SuperSchema mapping for the alias tables.

· Expand the ‘PM’ database node.

· Click on ‘Business Object Logic Table’.

· Right-click in the blank space and select ‘New’ from the context menu.

· In the next window, configure the join rule like so:

· This join will connect the main CONTACTS table, the table alias for CONTACTS, and the ADDRESSES table.

· Click ‘Apply’ to create the join and then click ‘OK’ to close the window.

· The join rule will now appear in the business object list.

Creating the Federation Server

Now that both adapters are mapped and have business object views, create the EIQ Federation Server. First, you must create Remote Server Data Sources for the two VDSs.

· Go to the ‘Data Sources’ tab.