IBM Business Analytics Proven Practices: Leveraging Packaged Queries On Salesforce.com Data Sources In Virtual View Manager 10.2

Product(s): IBM Cognos Virtual View Manager 10.2; Area of Interest: Modeling

Explains about packaged queries in IBM Cognos Virtual View Manager 10.2 and how they can be leveraged for better performance while reporting on Salesforce.com data sources.

Share:

Gopal Chikkam, Software Developer, IBM

Gopal Chikkam is a Senior Staff Engineer with IBM India Software labs. For the past 5 years he has been part of Cognos platform team and primarily responsible for developing data providers for SAPR3 and Salesforce.com datasources. Prior to that he was involved in developing enterprise applications in the CRM domain.



Santosh Ghule, Software Developer, IBM

Santosh Ghule is a Software Developer with IBM India Software labs. He specializes in IBM Cognos Virtual View Manager Product and has experience in IBM Cognos FM modeling and Reporting. He was also involved in development and defect fixing of IBM Cognos ERP Adapters-SAP/Siebel/SFDC.



03 July 2013

Introduction

Purpose of Document

This document explains about packaged queries in IBM Cognos Virtual View Manager 10.2 and how they can be leveraged for better performance while reporting on Salesforce.com data sources.

Applicability

This document is written and tested against IBM Cognos Virtual View Manager 10.2.

Exclusions and Exceptions

This document does not cover how to create a data source in IBM Cognos Virtual View Manager. Users can refer to the product documentation for details on creating a new Salesforce.com data source.

Assumptions

This document assumes users have experience with modeling Salesforce.com views in IBM Cognos Virtual View Manger 10.2 and use Cognos BI to report on top of them. Some understanding of Salesforce Object Query Language (SOQL) will be helpful.


Overview

Sometimes a complex query written for a particular database may not be feasible or desirable to re-write in IBM Cognos Virtual View Manager’s SQL. For example, the query may require a database specific feature which is not available or perhaps the query takes advantage of a database-specific feature for performance reasons. In such cases, a database-specific query can be employed as a packaged query and subsequently be used in other queries. This article explains in detail about packaged queries and how they can be useful when reporting on Salesforce.com data sources.


What are Packaged Queries?

Packaged queries let the user use database-specific queries (native SQL) within the IBM Cognos Virtual View Manager (VVM). They wrap native SQL into a single unit and push the SQL directly to the underlying data source for execution.

Packaged queries behave much like a stored procedure - accepting input parameters and producing rows of data. The output of packaged query is a cursor with at least one column. The results from output cursor can be fed as an input to other queries/operations.


Advantages of packaged queries with Salesforce.com

While reporting on Salesforce.com, there are few situations where packaged queries can be helpful.

Queries With Joins - Due to complex nature of joins in Salesforce.com, VVM server chooses to handle all types of joins between Salesforce.com objects by processing them locally. To process a Join operation, VVM has to fetch the required data into memory and evaluate the join. With this approach, joining large data sets can be cumbersome. By re-writing the VVM join query as a native query, the join operation can be pushed to Salesforce.com which can improve performance. More details about the Salesforce.com native language are discussed in the subsequent sections.

Date Literals and Functions - Date functions and literals of Salesforce.com are not supported by VVM. By using a native query one can leverage these features.


SOQL and Basics

The native query language for Salesforce.com is Salesforce Object Query Language (SOQL). Syntax for SOQL is similar to ANSI-SQL and supports standard SQL features such as sorting, grouping and sub-queries.

SOQL Syntax
SELECT list_of_fieldsSelection fields or Projection fields
FROM obj_typesSalesforce Objects (or tables)
[WHERE condition_expression]Filtering criteria (optional)
[GROUP BY field_group_by_list]Grouping fields (optional)
[HAVING having_condition_expression]Referred with group by clause (optional)
[ORDER BY field_order_by_list ASC | DESC ? NULLS FIRST | LAST ?]Ordering of result set having NULLS either first or last as specified.

More information on SOQL Syntax can be found in the Resources section at the bottom of this document.

SOQL Joins- Relationship Queries

There is no direct support for joins in SOQL. Instead they support special types of queries named relationship queries which allow retrieving data from multiple Salesforce.com objects in a single request. Though they are similar to SQL joins they do not support arbitrary joins. A relationship query is allowed between two objects only when a valid relationship (parent- child or child-parent) exists between them.

More information on relationship queries can be found in the Resources section at the bottom of this document.


Creating a new packaged query

The following steps are necessary to create a packaged query using IBM Cognos Virtual View Manager Studio.

Create a Salesforce.com data source using the New Data Source wizard. Refer to Virtual View Manager Getting Started guide for more details on how to create a Salesforce.com data source.

To create a new packaged query, right-click on the Shared folder in the resource tree and select New Packaged Query item as illustrated in Figure 1.

Figure 1: IBM Cognos Virtual View Manager Studio showing right click options on the Shared folder to create a new packaged query
Figure 1: IBM Cognos Virtual View Manager Studio showing right click options on the Shared folder to create a new packaged query

In the New Package Query dialog, specify an appropriate name for the new packaged query in the Name field and select a data source the packaged query will belong to. As shown in Figure 2, the data source named Salesforce.com is being used and the name will be Pkg1.

Figure 2: The New Package Query dialog showing the selection of the datasource named Salesforce.com and giving the packaged query the name Pkg1
Figure 2: The New Package Query dialog showing the selection of the datasource named Salesforce.com and giving the packaged query the name Pkg1

The SQL editor is used to specify the database specific SQL as illustrated in Figure 3. Click on the packaged query item named Pkg1 in the left hand pane and in the SQL editor, enter an example query such as select id, name, type from account.

Figure 3: VVM Studio SQL editor showing native SOQL for the packaged query named Pkg1
Figure 3: VVM Studio SQL editor showing native SOQL for the packaged query named Pkg1

The name and datatype information about each selection field needs to be specified under the Parameters tab in the same order as specified in the native SOQL. Use the Add button under the Parameters tab to add information about selection field to the result cursor as illustrated in Figure. 4. In our example query, id, name and type are projections of type VARCHAR.

Figure 4: VVM Studio showing Parameters tab highlighting the newly added field to result cursor with a datatype of VARCHAR
Figure 4: VVM Studio showing Parameters tab highlighting the newly added field to result cursor with a datatype of VARCHAR

Note: There will be a one to one mapping between the output cursor parameters and the projection fields in select clause.

On executing a packaged query, the embedded SOQL is pushed directly to a Salesforce.com system. The result is returned as a cursor with the projection fields as its output parameters. As shown in Figure 5, the results returned after executing our example query select id, name, type from account will appear under the Results tab.

Figure 5: VVM Studio showing the execution result of packaged query Pkg1 in the Results tab
Figure 5: VVM Studio showing the execution result of packaged query Pkg1 in the Results tab

Parameterized Packaged Queries

A parameterized packaged query is essentially a native SQL statement that can have named parameters in the projections or selections and it is implemented as a single-statement SQL script. The input parameters can be in the SELECT, WHERE, or FROM clause of a query. Values to these parameters will be provided at execution time.

Parameterized packaged queries can be helpful in IBM Cognos reports where prompt values are populated in a query. When input parameters are defined, the native query will use substitution patterns. If the query defines N inputs, it will contain {0}...{N-1} substitution patterns, where the numbers between {0} and {N-1} are contiguous. At run time, these substitution patterns are replaced with actual values entered by the user. For example, if we want to filter account records on the type field and value for type is provided at execution time, we need to specify an input parameter {0} in the native query. In the example shown in Figure 6, the SOQL select id, name, type from account where type = ‘{0}’ contains a filter condition with {0} as an input parameter.

Figure 6: VVM Studio SQL editor tab containing native SOQL with one input parameter {0} for the packaged query named Pkg2
Figure 6: VVM Studio SQL editor tab containing native SOQL with one input parameter {0} for the packaged query named Pkg2

For each substitution pattern in the query, a corresponding entry needs to be created under the Parameters tab in the Packaged Query editor. In this entry, the parameter name and data type needs to be specified.

The parameter name is displayed during query execution time, prompting for an input value. The parameter name has no effect on the database-specific query. However, the data type you specify effects how a value is formatted before it is substituted into the database-specific query. The data type needs to be specified for the input parameter field along with the projection field data type in the parameters tab. As shown in Figure 7, a new input field named IP-Type has been given a datatype of VARCHAR(255). Click the arrow key to change the direction of the parameter.

Figure 7: IBM Cognos VVM Studio showing the Parameters tab highlighting the newly added input field named IP-Type to cursor with datatype of VARCHAR(255)
Figure 7: IBM Cognos VVM Studio showing the Parameters tab highlighting the newly added input field named IP-Type to cursor with datatype of VARCHAR(255)

When server executes the packaged query, a dialog box will prompt the user to enter values for input parameters. Figure 8 shows VVM studio displaying a prompt dialog accepting the value Customer–Direct for the input parameter IP-Type. This value is substituted with input parameter {0} in SOQL.

Figure 8: IBM Cognos VVM Studio showing packaged query editor prompting the user to enter value for the field IP-Type during execution
Figure 8: IBM Cognos VVM Studio showing packaged query editor prompting the user to enter value for the field IP-Type during execution

The values of input parameters are substituted into the database-specific query before the packaged query is sent for execution. When you use the STRING and DATE data types, the input parameter substitution pattern should be enclosed in single quotes (') - for example ‘{0}’ in the SOQL. This rule doesn’t apply to numeric data types. In this example, string substitution happens for input parameter {0} to "Customer - Direct". Upon execution of SOQL, cursor result is returned under the Results tab as shown in Figure 9.

Figure 9: IBM Cognos VVM Studio showing execution result of the packaged query Pkg2 when the input value for parameter {0} is 'Customer - Direct'
Figure 9: IBM Cognos VVM Studio showing execution result of the packaged query Pkg2 when the input value for parameter {0} is 'Customer - Direct'

Salesforce.com Relationship Queries

Parent-Child Relationship

In a parent-child relationship, the relationship path is traversed from parent to its children using a nested query. Here the SOQL retrieves data from parent and all its related children in a single request. In the query, the child object will be referred using its relationship name.

For example, to retrieve account details along with its related contact details, the relevant VVM SQL will have a LEFT OUTER JOIN between the Account and Contact objects as shown below.

select Account.id, Account.name, Account.type, Contact.name from Account 
 LEFT OUTER JOIN Contact ON Account.id = Contact.Accountid

The equivalent SOQL statement select Account.id, Account.name, Account.type,(select Contact.name from Account.Contacts)from Account will use the relationship name Contacts to fetch data from the Account and Contact objects in a single request. This SOQL statement in a packaged query named Parent-Child is shown in Figure 10 and Figure 11 shows the execution result of the request for Account ID, Account Name, Account Type and Contact Name.

Figure 10: IBM Cognos VVM Studio showing SQL editor with parent-child Relationship between Account and Contact objects in a packaged query named Parent-Child
Figure 10: IBM Cognos VVM Studio showing SQL editor with parent-child Relationship between Account and Contact objects in a packaged query named Parent-Child
Figure11: IBM Cognos VVM Studio showing result of execution of parent-child relationship SOQL between the Account and Contact objects
Figure11: IBM Cognos VVM Studio showing result of execution of parent-child relationship SOQL between the Account and Contact objects

Child-Parent Relationship

In a child-parent relationship, the relationship path is traversed from child to its parent. Here the SOQL retrieves data from a child and its related parent in a single request. For example, to retrieve the contact details along with the details of its parent account, the following VVM SQL will have a LEFT OUTER JOIN between Contact and Account objects.

select Contact.id, Contact.FirstName, Contact.LastName, Account.Name from Contact 
 LEFT OUTER JOIN Account ON Contact.Accountid = Account.id

An equivalent SOQL statement to fetch the data in a single request will be

select Contact.id, Contact.FirstName, Contact.LastName, Contact.Account.Name from Contact

This SOQL statement is shown in Figure 12 for the packaged query named Child-Parent and Figure 13 shows the execution result of the request for ContactId, Contact First Name, Contact Last Name and Account Name.

Figure 12: IBM Cognos VVM Studio showing SQL editor containing child-parent Relationship between the Contact and Account Objects in the packaged query named Child-Parent
Figure 12: IBM Cognos VVM Studio showing SQL editor containing child-parent Relationship between the Contact and Account Objects in the packaged query named Child-Parent
Figure 13: IBM Cognos VVM Studio showing execution result of Child-Parent Relationship SOQL between Contact and Account Objects
Figure 13: IBM Cognos VVM Studio showing execution result of Child-Parent Relationship SOQL between Contact and Account Objects

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Big data and analytics on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Big data and analytics
ArticleID=935859
ArticleTitle=IBM Business Analytics Proven Practices: Leveraging Packaged Queries On Salesforce.com Data Sources In Virtual View Manager 10.2
publish-date=07032013