Data Access Guide ■ SAP BusinessObjects Business Intelligence platform 4.
Copyright © 2011 SAP AG. All rights reserved.SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries.
Contents 3 Chapter 1 Document History.................................................................................................................11 Chapter 2 Introduction to data access guide.........................................................................................13 2.1 2.2 2.3 2.4 2.5 About the data access guide..................................................................................................13 Chapter 3 Introduction to data access..............................
Contents 4 4.4 4.4.1 4.5 4.5.1 4.5.2 4.5.3 4.6 4.6.1 4.6.2 4.7 Connection pools...................................................................................................................31 Chapter 5 Creating a connection...........................................................................................................37 5.1 5.2 5.2.1 5.2.2 5.2.3 5.2.4 5.2.5 5.2.6 5.2.7 5.2.8 5.2.9 5.2.10 5.3 5.3.1 5.3.2 5.3.3 5.3.4 5.3.5 5.4 5.4.1 5.4.2 5.4.3 5.5 5.5.1 5.5.2 Connection requirements...
Contents 5 6.1.1 6.1.2 6.1.3 6.1.4 6.2 6.2.1 6.2.2 6.2.3 6.2.4 Driver capabilities...................................................................................................................63 Chapter 7 Connection reference...........................................................................................................75 7.1 7.2 7.3 7.4 7.5 7.6 7.7 7.8 7.8.1 7.8.2 7.8.3 7.8.4 7.8.5 7.8.6 7.9 7.10 7.10.1 7.11 7.11.1 Apache Hadoop HIVE connections.......................................
Contents 6 8.4.2 Configuring Connection Server for a Web Intelligence Rich Client connection to Data Federator...............................................................................................................................91 8.4.3 8.4.4 Setting the Windows RichClient registry key .........................................................................92 Chapter 9 Configuring data access global parameters..........................................................................95 9.
Contents 7 10.2 10.2.1 Configuring data access for DataDirect ODBC branded drivers...........................................117 Chapter 11 SBO parameter reference...................................................................................................121 11.1 11.2 11.3 11.3.1 11.3.2 11.3.3 11.3.4 11.3.5 11.3.6 11.3.7 11.3.8 11.3.9 11.3.10 11.3.11 11.3.12 11.3.13 11.3.14 11.3.15 11.3.16 11.3.17 11.3.18 11.3.19 11.3.20 11.3.21 11.3.22 11.3.23 11.3.24 11.3.25 11.3.26 11.3.27 11.3.28 11.3.
Contents 8 11.4.1 11.5 11.5.1 11.6 11.6.1 11.6.2 11.6.3 11.6.4 11.6.5 11.6.6 11.6.7 11.6.8 11.7 11.7.1 11.7.2 11.7.3 11.7.4 11.7.5 11.7.6 11.7.7 11.7.8 11.7.9 11.8 11.8.1 11.8.2 11.9 11.9.1 11.10 11.10.1 11.11 11.11.1 11.11.2 11.11.3 11.11.4 11.11.5 JavaBean Class...................................................................................................................140 Chapter 12 Configuring database capability parameters.......................................................................
Contents 9 12.4 12.5 To view and edit a function help text file...............................................................................158 Chapter 13 PRM parameter reference...................................................................................................159 13.1 13.1.1 13.1.2 13.1.3 13.1.4 13.1.5 13.1.6 13.1.7 13.1.8 13.1.9 13.1.10 13.1.11 13.1.12 13.1.13 13.1.14 13.1.15 13.1.16 13.1.17 13.1.18 13.1.19 13.1.20 13.1.21 13.1.22 13.1.23 13.1.24 13.1.25 13.1.26 13.1.27 13.1.28 13.
Contents 13.1.37 UNION_IN_SUBQUERY......................................................................................................176 Chapter 14 Data type conversion reference..........................................................................................177 14.1 14.1.1 14.1.2 14.1.3 14.1.4 14.1.5 14.1.6 14.1.7 14.2 14.3 Data type conversion ..........................................................................................................177 Appendix A More Information.......
Document History Document History The following table provides an overview of the most important document changes. Version Date Learning about Connectivity Services, see Connectivity services. Deployment modes of Connection Server, see Deployment mode. How to install ODBC drivers, see Installation of ODBC drivers. A list of the names of the folders where to place JAR files for JDBC connections, see JAR file location reference.
Document History Version Date New Sybase IQ 15 connectivity through ODBC and JDBC introduced on all platforms. SAP BusinessObjects Business Intelligence platform 4.0 Support Package 2 ODBC Data Source Administrator Tool, see 64-bit Microsoft Windows support. May 2011 Support of JDBC connectivity in 3-tier mode, see Creating JDBC connections . Backward compatibility limitation with BusinessObjects OpenConnectivity, see CSV file connections.
Introduction to data access guide Introduction to data access guide 2.1 About the data access guide The Data Access Guide helps you learn about Connection Server functionality and how to configure Connection Server to enable connections of SAP BusinessObjects Business Intelligence platform 4.0 Feature Pack 3 to production databases.
Introduction to data access guide • • • • • • • • • New connectivities are available through Connection Server. See the Product Availability Matrix for more information. A new driver is available for SAP ERP systems (SAP R/3 Release 4, mySAP ERP 2004, and SAP ERP 6) through SAP JCo 3.x API on all platforms. The driver for CSV files through BusinessObjects OpenConnectivity has been improved.
Introduction to data access guide Note: For administrative tasks such as starting and stopping Connection Server, managing properties and metrics, refer to the SAP BusinessObjects Business Intelligence platform Administrator Guide. Related Topics • Configuring the deployment mode • Configuring the drivers to load • To view and edit SBO files • To run the cscheck tool • Creating JDBC connections • SAP HANA connections 2.
Introduction to data access guide 16 2012-03-15
Introduction to data access Introduction to data access 3.1 About Connection Server Connection Server is the data access software that manages the connection between an SAP BusinessObjects application and a data source. Connection Server allows applications such as universe design tool, information designer tool and SAP BusinessObjects Web Intelligence to connect to and run queries against a data source. Connection Server does not have a user interface.
Introduction to data access • the application and the data access driver • Connection Server and the data access driver 3.2.1 System architecture The diagram below details where Connection Server and data access drivers fit into an SAP BusinessObjects configuration. 3.2.2 Data access drivers Data access drivers provide the connection between Connection Server and a data source. A database requires a data access driver in order that it can be accessed by an SAP BusinessObjects application.
Introduction to data access • Contact your SAP representative to determine if there is a driver available, and if you are licenced to use it. • Use the Driver Development Kit (DDK) to develop a driver to use. Contact your SAP representative for details. When you create a new connection, you select the appropriate data access driver for the target data source.
Introduction to data access The cs.cfg file contains parameters that apply to all installed data access drivers. Related Topics • About global parameters 3.3.
Introduction to data access Driver specific file .cod Can be edited? No Description Each data access driver has a COD file. Stores information related to connection definitions. Defines the fields that appear when you create a new connection. Example oracleen.cod Note: Do not modify these files. .rss .stg No Each data access driver has a RSS file. Stores predefined SQL sentences, which are used by Connection Server. oracle.
Introduction to data access • Server mode Connection Server is a CORBA server and is accessed remotely. Connection Server serves the CORBA and HTTP clients to address the 2-tier and web tier deployment modes respectively. See the SAP BusinessObjects Business Intelligence platform Administrator Guide for more information on the deployment scenarios. Related Topics • Setting the deployment mode • Configuring the deployment mode • Configuring the drivers to load 3.
Introduction to data access Connection Connectivity Service Description All 64-bit native data sources Native Connectivity Service Support of ODBC, OLE DB, OCI, and so on. All 32-bit native data sources Native Connectivity Service Support of data sources available in 32bit only. This service is only available on MS Windows. Adaptive Connectivity Service Support of all Java-based middleware. This service is a Java implementation based on the Platform Java Service (PJS) framework.
Introduction to data access 24 2012-03-15
Learning data access specifics Learning data access specifics 4.1 64-bit operating system support SAP BusinessObjects provides releases of the SAP BusinessObjects Business Intelligence platform 4.0 Feature Pack 3 for the following operating systems: • • 32-bit versions of Microsoft Windows 64-bit versions of Microsoft Windows and UNIX flavors The Data Access layer then provides data access drivers that are able to run either in a 32-bit or a 64-bit environment.
Learning data access specifics • • • • • • • DB2 UDB for iSeries V6 with Client Access AS400 DB2 UDB for i v6.1 with Client Access AS400 Generic datasource through OLE DB MS Access 2010 through ODBC MS Excel 2010 through ODBC MS SQL Server 2005, 2008, and 2008 R2 through OLE DB SAP HANA 1.0 SP3 through ODBC 4.1.
Learning data access specifics Related Topics • Array Fetch Size 4.1.3 Installation of ODBC drivers For installations of the information design tool or universe design tool on a 32-bit Windows operating system, the drivers for any ODBC data sources that need to be created, tested, and accessed in the tool should be defined using the ODBC Data Source Administrator that can be found at the following location on the physical machine where tool is installed: • C:\Windows\System32\odbcad32.
Learning data access specifics Data source Description MS Analysis Services on Microsoft Windows SSO to BI platform is Windows AD with Kerberos. MS SQL Server through ODBC or OLE DB on Microsoft Windows SSO to BI platform is Windows AD with Kerberos. Oracle through OCI on Microsoft Windows SSO to BI platform is LDAP. Oracle EBS through OCI on all platforms You enable SSO by installing and configuring the authentication plugin that comes with the platform.
Learning data access specifics Connection Server is able to manage data from data sources that result either from SQL query or stored procedure execution. Stored procedures are SQL scripts that are stored as executable code in an RDBMS. They can receive arguments and return data.
Learning data access specifics Note: The other IN/OUT cursor parameters of the procedure are ignored.
Learning data access specifics b. Write the following statement with several parameters: CREATE or REPLACE PROCEDURE get_ownerobjects(owner_name IN varchar2, cat_cv IN OUT catalog_data.catcurtyp) AS BEGIN OPEN cat_cv FOR SELECT * FROM all_objects WHERE owner=owner_name; END; Note: See your Oracle documentation for more information on how to create packages and stored procedures. 4.3.5 Teradata stored procedures Connection Server only supports Teradata macros when the connection established uses ODBC.
Learning data access specifics • Each time that Connection Server requires information, the data access driver opens a connection to the database, retrieves the data, then closes the connection. • Connection Server keep available connections open and maintain their details in a connection pool. Each time that Connection Server requires information from the data source, the data access driver checks the connection pool to see if it contains an unused, suitable connection.
Learning data access specifics • • client proxies in a 2-tier deployment, on the client tier server bridges in a web tier deployment, on the web tier Note: Connection Server also provides an application-level lookup mechanism, which helps it to choose the right server instance for a job. Connection Server binds the client to the appropriate server, depending on the type of data source requested by the client and on what network layer and database pair the server supports.
Learning data access specifics • • available memory is the difference between max memory and the current amount of allocated memory constant is the maximum HEALTH (set to 10000 for Connection Server). 4.5.2 Load balancing logic 1. Each candidate server reports its HEALTH to the load balancer. A candidate server is a Connection Server server that supports the requested network layer and database pair.
Learning data access specifics The Data Access layer provides the following binaries: • • ConnectionServer.exe, which is the default binary. HOARD is not enabled. ConnectionServerOptimized.exe, which is the HOARD-enabled binary 4.6.1 To enable HOARD 1. Open the CMC. 2. Under "Connectivity Services", stop the Native Connectivity Service hosted by the ConnectionServer server. 3. Locate the Connection Server executable. It is located in the bip-install-dir\win64_x64 directory. 4.
Learning data access specifics • Tree view, where successive function calls of a specific workflow are displayed and time spent on a function is highlighted to track the time-consuming activities easily Note: This feature is available starting from SAP Business Enterprise XI 3.1 SP3. For more information, see the SAP BusinessObjects Business Intelligence platform Administrator Guide.
Creating a connection Creating a connection 5.1 Connection requirements This section covers the requirements for creating a connection. • Ensure that your platform conforms to the platforms supported for SAP connections. • Ensure that the database middleware is installed correctly, and that you can access your database through either your computer or a server. • Ensure that you have all the information necessary to access your database, for example the database login name and password.
Creating a connection Note: The results of all checks apply to your local machine, from which you run the tool. The cscheck tool is installed in the boe-install-dir\platform_dir where boe-install-dir is the BOE installation directory and platform_dir is win32x_86, win64_x64, and so on. You run the cscheck tool from a command console (DOS or shell). The output is displayed on the screen. You can specify that the output is generated as XML format, or you can suppress output, to use the tool in a script.
Creating a connection Example: To display help in English on the cscheck tool, use the following command: cscheck --help To display help in French on the connectivity function, use the following command: cscheck --language fr --help connectivity 5.2.2 To run the cscheck tool You can run the cscheck tool at any time after you have installed your SAP BusinessObjects solution. 1. Open a command console. 2. Change directory to the path where the tool is installed. 3.
Creating a connection The remaining part of the command consists of the function and its option arguments. function name can take the following values.
Creating a connection The following is an excerpt of the result list: Oracle Client Oracle 10 Oracle 11 Sybase Open Client Sybase Adaptive Server 12 Sybase Adaptive Server 15 Informix ODBC Driver Informix XPS 8.4 Informix XPS 8.5 Informix Dynamic Server 7.3 Informix Dynamic Server 2000 Informix Dynamic Server 10 Informix Dynamic Server 11 Teradata ODBC Driver Teradata V2 R5 Teradata V2 R6 Teradata 12 ODBC Drivers Generic ODBC Datasource Generic ODBC3 Datasource ...
Creating a connection Informix XPS 8.5 Informix Dynamic Server 7.3 Informix Dynamic Server 2000 Informix Dynamic Server 10 Informix Dynamic Server 11 This access driver is installed: Teradata ODBC access driver Client layer: Teradata ODBC Driver Database engine(s): Teradata V2 R5 Teradata V2 R6 Teradata 12 ... Related Topics • Check tool—function overview • Displaying help on the cscheck tool 5.2.
Creating a connection Table 5-1: Function input parameters The mode in which the client application accesses Connection Server: • local: lists connectivity types available on the local machine. Connection Server access mode (-m) • corba: lists connectivity types available using CORBA. • http: lists connectivity types available using HTTP. • java: lists Java connectivity types available on the local machine. • extended: lists local, java, and CORBA connectivity types.
Creating a connection Example: Finding CORBA server connectivities The following command returns a list of the data access drivers available from a CORBA server. cscheck find –m corba Related Topics • Check tool—function overview • Displaying help on the cscheck tool 5.2.7 Check tool—middleware For a supplied network layer and database client, this function checks for a valid installation of the client middleware.
Creating a connection Related Topics • Check tool—function overview • Displaying help on the cscheck tool • Check tool—connectivity • Check tool—accessdriver 5.2.8 Check tool—accessdriver For a supplied network layer and database client, this function checks for a valid data access driver installation. To check both the middleware and data access driver for a supplied network layer and database client, you can use the connectivity function.
Creating a connection The result is the following: Starting to check the access driver component installation... Begin AND operator... Config Directory... success. %SharedRoot%\ConnectionServer\Network Layers\Oracle OCI... success. Directory... success. /connectionserver-install-dir/connectionServer//oracle... success. Library... success. /connectionserver-install-dir/connectionServer//libdbd_oci10.so... success. /connectionserver-install-dir/connectionServer//libdbd_oci11.so... success. Data File Name...
Creating a connection cscheck -l en connectivity -c "Oracle Client" -d "Oracle 10">c:\result.txt If the middleware is not correctly installed, the result will be the following: Starting to check the middleware component installation... Begin AND operator... ORACLE_HOME... The environment setting does not exist. End AND operator: failure. The middleware is not correctly installed. Starting to check the access driver component installation... Begin AND operator... Config Directory... success.
Creating a connection Table 5-5: Function input parameters The mode in which the client application accesses Connection Server: • local: Connection Server is running on the local machine. • corba: Connection Server is running on a CORBA server. • http: Connection Server is running on a HTTP server. • java: Connection Server uses a Java data access driver on the local machine.
Creating a connection • Network layer: Oracle Client • Database: Oracle 10g • Data source: Harlaxton • User name: efashion • Password: X2345 cscheck ping -m local -c "Oracle Client" -d "Oracle 10" -u "efashion" -p "X2345" -s "Harlaxton" Example: Pinging a Sybase database using CORBA The following command checks access for: • Connection Server access mode: CORBA, that is, Connection Server runs on a CORBA server.
Creating a connection Note: JDBC connectivity is available for SAP BusinessObjects Enterprise XI 3.0 and higher. Web Intelligence Rich Client supports JDBC connectivity in 3-tier mode in the SAP BusinessObjects Business Intelligence platform 4.0 and higher. SAP BusinessObjects software also includes configuration files for using JDBC drivers to access your databases. To use these drivers, you do the following: 1. Obtain the java driver software from your database supplier. 2.
Creating a connection 3. Add the required .jar file details to the ClassPath area. Include the fully qualified path names when specifying these files, for example: C:\\JDBC Drivers\\MSSQLSERVER2000\\msutil.jar Note: These files need to be installed on the machine running the application. 4. Locate the Driver Capabilities parameter, and check that it is set to either Procedure, Queries, or both. Note: In the last case, settings are separated by a comma.
Creating a connection 5.3.3 To create a JDBC connection with Extensions In order to create a JDBC connection: • Obtain the necessary JDBC driver software for the database, and copy the files to your system. These files are available from the database vendor. The driver software consists typically of one or more JAR files. • Ensure that you have the database access details to hand, for example the login and password details. 1. Go to the next section of the guide to find the Extensions parameter values.
Creating a connection 5.3.4 JAR file location reference The following table describes the names of the folders where you must place middleware JAR files to enable JDBC connections. The first column lists the database vendor names as shown in the connection wizard. The second column lists the names of the databases that support JDBC connections, as described in the DataBase section of the corresponding SBO file.
Creating a connection Vendor Database Extensions parameter values Ingres Ingres Database 9 ingres9, ingres, jdbc MS SQL Server 2005 sqlsrv2005, sqlsrv, jdbc MS SQL Server 2008 sqlsrv2008, sqlsrv, jdbc Netezza Server 4 netezza4, netezza, jdbc Netezza Server 5 netezza5, netezza, jdbc Netezza Server 6 netezza6, netezza, jdbc MySQL 5 mysql5, mysql, jdbc Oracle 10 oracle10, oracle, jdbc Oracle 11 oracle11, oracle, jdbc Progress OpenEdge 10 progress10, progress, jdbc Data Federator XI R3
Creating a connection Vendor Sybase Database Extensions parameter values Sybase Adaptive Server Enterprise 15, Sybase Adaptive Server Enterprise 15.
Creating a connection 2. Copy the required .jar file to the connectionserver-install-dir\connectionServer\jd bc\drivers\jdbc directory. You have to create the directory if it does not exist. 3. Run the connection wizard. The JDBC driver appears in the list of available connections under Generic. 4.
Creating a connection Layers on Database Middleware Selection screen. If you do not, the connection wizard does not display the JavaBean drivers that are available. Related Topics • Creating JDBC connections • To create a JavaBean connection • To create a JavaBean connection with Extensions 5.4.1 To create a JavaBean connection 1. Use an XML editor to open the javabean.sbo file for editing.
Creating a connection com.businessobjects.beans.excel.Excel $DATASOURCE$ Java Beans bean_excel Bypass bean_excel,javabean 5.4.3 To create a JavaBean connection with Extensions 1.
Creating a connection A set of data access drivers are installed when you install the SAP BusinessObjects Business Intelligence platform. You can use these data access drivers to create connections to databases. They are located in the connectionserver-install-dir\connectionServer\drivers\lib32 or connection server-install-dir\connectionServer\drivers\lib64 directory. SAP BusinessObjects software also includes configuration files for using ODBC drivers to access your databases.
Creating a connection The configuration files are located in the connectionserver-install-dir/connectionServ er/odbc directory. 2. Use an XML editor to open the odbc.sbo file for editing. 3. Locate the following section: ...
Creating a connection 5.5.2 To create a generic ODBC3 connection The following procedure shows how to configure a generic ODBC connection to a database with the following assumptions: • The driver is ODBC3 • The driver supports the unixODBC driver manager 1. Navigate to the directory that contains the odbc.sbo and odbc.prm files. The configuration files are located in the connectionserver-install-dir/connectionServ er/odbc directory. 2. Use an XML editor to open the odbc.sbo file for editing. 3.
Creating a connection 9. Install the relevant ODBC drivers on your UNIX machine. 10. Configure the data source by editing the odbc.ini file. 11. Save and close the odbc.ini file. 12. Run the connection wizard. The ODBC driver that you have configured appears in the list of available connections under Generic. 13. Select the ODBC driver and use the wizard to configure the connection.
Data access driver reference Data access driver reference 6.1 CSV file connections This section only relates to connections to be created in the information design tool. The Data Access layer allows the SAP BusinessObjects Business Intelligence platform to connect to Comma-Separated Value (CSV) files through BusinessObjects OpenConnectivity network layer. It provides a data access driver called CSV OpenDriver to access the CSV files.
Data access driver reference • • DISTINCT clause within SELECT statement UNION and UNION ALL Note: The ORDER BY operation depends on the Bucket Split Size SBO parameter. The data access driver also supports the MIN, MAX, AVG, SUM, and COUNT grouping functions in SELECT statements. Driver limitations The CSV OpenDriver does not implement any functionality for altering the state of the CSV file, such as INSERT, UPDATE and ALTER.
Data access driver reference 6.1.3 Table mapping The data access driver exposes CSV files as tables to the information design tool. You can connect to multiple files if the Filepath or Pattern parameter of the connection wizard is set to a pattern, for example report_*.csv, which is matched by files such as report_2010-09-22.csv and report_2010-09-21.csv.
Data access driver reference 6.1.4 Schema detection The Data Access layer provides the following methods for the schema detection of a CSV file: • • • • no detection automatic detection use of a Data Definition Language (DDL) file use of an SQLDDL file You choose the method to apply to the files using the Schema Detection parameter in the connection wizard. You can provide the schema by using schema files (DDL or SQLDDL). This can be helpful for large and complex CSV files.
Data access driver reference Caution: Only Probe Rows set to all allows the driver to find the longest string. • column nullability Nullability is true if there is a null value in the column, false if all values are filled. Unless you select all rows, you choose the number of lines the driver has to analyze by setting the Number of Probe Rows parameter in the connection wizard. Note: See the Information Design Tool User Guide for recommendations.
Data access driver reference 6.2 SAP ERP connections The Data Access layer allows the SAP BusinessObjects Business Intelligence platform to connect to SAP ERP systems through the SAP Java Connector (JCo) 3.x network layer. It provides a data access driver that allows access to the following ERP objects: • • • InfoSets SAP Queries ABAP functions For more information about supported SAP ERP systems, see the Product Availability Matrix. SAP ERP connectivities support single sign-on (SSO) on all platforms.
Data access driver reference The data access driver does not allow ordering columns by index in SQL statements. Only ORDER BY with column names is a valid clause. The ORDER_BY_SUPPORTS_COLUMN_INDEX parameter is set to NO in the jco.prm file. Related Topics • ORDER_BY_SUPPORTS_COLUMN_INDEX 6.2.2 Access to InfoSets and SAP Queries About InfoSets and SAP Queries In the SAP ERP system, InfoSets are created within either a local work area or a global work area.
Data access driver reference Fields are mapped to table columns. An InfoSet or an SAP Query contains fields that are both projection and selection fields. It also contains selection fields that are not used for projection. When you create a connection with the connection wizard, you choose whether these selection fields are represented as table columns. The Map Selection Fields into Table Columns parameter addresses this functionality. If selected, then the field is mapped as an optional input column.
Data access driver reference internally by the driver (other filtering with WHERE, GROUP BY, DISTINCT and sorting only with ORDER BY aggregate functions). When the application user queries an SAP Query, some operations are also passed to the ERP system. These are SELECT, WHERE, ORDER BY (only if the SAP query already contains the same sorting) and AS. Note: For more information on the operations directly managed by the system, see your SAP ERP system documentation. 6.2.
Data access driver reference • • An optional parameter that has a default value is mapped to an optional input column. The user does not have to set a value to this column. The value returned to Connection Server by the data source in that case is the default value. An optional parameter with no default value is mapped to a mandatory input column. The user has to set a value to this column, because a primary key attribute cannot be null.
Data access driver reference 6.2.4 ERP limitations • The JCo API does not support array fetch. This means the result data is returned all at once. To restrict the number of resulting rows and to avoid an out-of-memory issue, the application user has to filter data appropriately. Regardless of this limitation, Connection Server still applies array fetch. Note: The application user can also set a value to Max Rows in the Query Panel of the information design tool.
Data access driver reference 74 2012-03-15
Connection reference Connection reference 7.1 Apache Hadoop HIVE connections The Data Access layer allows the SAP BusinessObjects Business Intelligence platform to connect to the Apache Hadoop HIVE 0.7.1 and 0.8.0 databases through JDBC on all platforms. To create a connection to the Hive Thrift server, you first have to place the following sets of JAR files to the connectionserver-install-dir\connectionServer\jdbc\drivers\hive directory: HIVE database version JAR files hadoop-0.20.1-core.
Connection reference HIVE database version JAR files hadoop-0.20.1-core.jar or hadoop-core-0.20.2.jar hive-exec-0.8.0.jar hive-jdbc-0.8.0.jar hive-metastore-0.8.0.jar hive-service-0.8.0.jar 0.8.0 libfb303.jar log4j-1.2.16.jar commons-logging-1.0.4.jar slf4j-api-1.6.1.jar slf4j-log4j12-1.6.1.jar 7.2 IBM DB2 connections Restriction: Due to a database driver limitation, reference key names of IBM DB2 10 for z/OS database tables are mapped to NULL. 7.
Connection reference Related Topics • http://technet.microsoft.com/en-us/library/cc917711.aspx • http://technet.microsoft.com/en-us/library/cc917712.aspx 7.4 Oracle EBS connections The Data Access layer provides a new driver for connecting SAP BusinessObjects applications to Oracle E-Business Suite (EBS) through OCI. It allows applications to access data from EBS views and stored procedures. You can only use OCI to connect the driver to Oracle EBS.
Connection reference 7.6 Oracle RAC connections The Data Access layer allows the SAP BusinessObjects Business Intelligence platform to connect to Oracle Real Application Clusters (RAC) through JDBC. To create a connection from your application using the connection wizard, you need to provide the Oracle RAC datasource entry, which has the following format: :,:,...,: The number of host and port pairs depends on the number of machines involved in the cluster.
Connection reference 13. Save the file. 14. Restart the service and application. When you complete this task, the connection to salesforce.com is properly configured. 7.8 SAP HANA connections The Data Access layer allows the SAP BusinessObjects Business Intelligence platform to connect to SAP HANA database 1.0 SP3 through ODBC on MS Windows and through JDBC on all platforms. Array Fetch Size The Array Fetch Size default value is 1000 for SAP HANA connections.
Connection reference Creating a JDBC connection The SAP HANA 1.0 SP3 JDBC drivers are installed as part of the BI platform in the connectionserv er-install-dir\connectionServer\jdbc\drivers\newdb directory. Consequently, you do not need to perform any additional configuration to create a connection to SAP HANA database. Connection wizard To create a connection from your application using the connection wizard, you need to provide the SAP HANA server and port entries.
Connection reference Option Description To configure SSO for local connections through JDBC a. Exit the information design tool. b. Open the InformationDesignTool.ini file for editing. It is located at the bip-install-dir\win32_x86 directory. c. Add the following lines: -Djava.security.auth.login.config=C:\\bscLo gin.conf -Djava.security.krb5.conf=C:\\Krb5.ini where is the configuration file directory on the machine where Connection Server is running. d. Save the file. e.
Connection reference The following steps enable single sign-on to the HANA database through JDBC for SAP BusinessObjects Web Intelligence. Note: This procedure applies to the Java or HTML interface of SAP BusinessObjects Web Intelligence that the application user launches from the BI launch pad. Remember: This configuration only addresses Web Intelligence reporting services hosted by the Web Intelligence Processing Server. 1. Open the CMC. 2.
Connection reference Related Topics • To configure single sign-on for the information design tool 7.8.6 To configure the Java Virtual Machine for instrumentation You can monitor the activity of SAP HANA drivers if you install CA Wily Introscope with the BI platform. This instrumentation provides end-to-end tracing support for SAP HANA connections through ODBC and JDBC. SAP HANA tracing is enabled by default in the platform.
Connection reference ODBC driver connects to ASCII database kernels via ASCII and to Unicode database kernels with UCS2. SAP MaxDB does not require a specific driver manager on UNIX. However, it can be configured to work with the following driver managers if needed: • • unixODBC 2.0.9 or higher iODBC 3.0.5 or higher On JDBC, make sure to use the latest version of the sapdbc.jar driver. For more information about SAP MaxDB JDBC driver, refer to maxdb.sbo configuration file. 7.
Connection reference 7.11 SAS connections Connections to SAS do not use Connection Server. They use an adapted JDBC connector to SAS/SHARE data sets. To configure these connections, you need to install the compatible JDBC driver. For more information about configuring SAS connectors, refer to the Data Federation Administration Tool Guide. 7.11.
Connection reference 86 2012-03-15
Creating a connection to Data Federator XI 3.0 Query Server Creating a connection to Data Federator XI 3.0 Query Server 8.1 About Data Federator XI 3.0 Query Server connections You can create connections to tables deployed on Data Federator XI 3.0 Query Server, in order to use these tables with an SAP BusinessObjects application. This chapter describes configuration settings you must do on Data Federator XI 3.0 Query Server and Connection Server to create connections.
Creating a connection to Data Federator XI 3.0 Query Server 8.2 Configuring the connection wizard for a Data Federator JDBC or ODBC connection In order to create a connection to Data Federator XI 3.0 Query Server, you need the following information.
Creating a connection to Data Federator XI 3.0 Query Server Related Topics • Configuring the Data Federator ODBC middleware • Configuring Connection Server for a Data Federator ODBC connection 8.3.1 Configuring the Data Federator ODBC middleware Note: This topic applies to all SAP BusinessObjects applications that use Connection Server except Web Intelligence Rich Client. To configure the Data Federator ODBC middleware, you need to modify the openrda.ini file.
Creating a connection to Data Federator XI 3.0 Query Server To configure the cs.cfg file, set parameters under the JavaVM tag, as follows: \\data-federator-drivers-install-dir\\OaJdbcBridge\\oajava\\oasql.jar \\data-federator-drivers-install-dir\\JdbcDriver\\thindriver.jar 8.
Creating a connection to Data Federator XI 3.0 Query Server 8.4.1 Configuring the Data Federator ODBC middleware for a connection to Web Intelligence Rich Client To configure the Data Federator ODBC middleware, you need to modify the openrda.ini file. This file is installed in the following directory: • data-federator-drivers-install-dir\OaJdbcBridge\bin\iwinnt To configure the openrda.
Creating a connection to Data Federator XI 3.0 Query Server 8.4.3 Setting the Windows RichClient registry key To modify the Windows RichClient registry key, use a tool such as regedit. 1. In the registry, locate the HKEY_LOCAL_MACHINE\SOFTWARE\SAP BusinessObjects\Suite XI 4.0\default\WebIntelligence\RichClient key. 2. In this key, add the following values to the classpath entry. As with all registry key values, the values must be separated with a semicolon.
Creating a connection to Data Federator XI 3.0 Query Server Caution: The universe design tool and OpenEdge bridge must specify the same JVM directory path.
Creating a connection to Data Federator XI 3.
Configuring data access global parameters Configuring data access global parameters 9.1 About global parameters You can configure the global parameter values that apply to all connections. You can do this to improve performance, or to resolve issues with the connection that arise. Data access global parameters are maintained in the cs.cfg file. This is an XML file that contains Connection Server configuration parameters, and default configuration parameters that apply to all data access drivers.
Configuring data access global parameters This section defines parameters that apply to all data access drivers. They can be overridden for a specific driver by corresponding settings in the .sbo configuration files, where is the name of the data access driver to which the SBO file relates. • Traces This section defines parameters that allow the recording of connection activity through Connection Server in log files.
Configuring data access global parameters • possible values that can be set for the parameter (where applicable) • default value for the parameter Note: Some settings can only be modified from the CMC. For more information about it, refer to the SAP BusinessObjects Business Intelligence platform Administrator Guide. 9.4.1 Charset List Extension crs Description Note: Do not change this setting. Sets the file extension for character set files.
Configuring data access global parameters Description Note: Do not change this setting. Sets the file extension for the connection description files. Default cod 9.4.4 Ignore Driver Load Failure Yes Description Determines action taken when a driver fails to load. The parameter lets you choose whether you want a usable connection possibly without all drivers operating, or a fatal error and no functionality when a driver fails to load.
Configuring data access global parameters Caution: Load Drivers On Startup only applies to library mode. Description Determines how driver libraries are loaded. • Yes: all installed drivers are loaded during the initialization phase. Values • No: drivers are loaded on demand. Default No 9.4.6 Max Pool Time -1 Caution: Max Pool Time is available for library mode only.
Configuring data access global parameters Related Topics • Connection pools 9.4.7 Setup File Extension setup Description Note: Do not change this setting. Sets the extension for data access driver setup files. Default setup 9.4.8 SQL External Extension rss Description Note: Do not change this setting. Sets the file extension for external SQL files. Default rss 9.4.
Configuring data access global parameters Description Note: Do not change this setting. Sets the file extension for SQL parameter files. Default prm 9.4.10 Strategies Extension stg Description Note: Do not change this setting. Sets the extension for strategy files. Default stg 9.4.
Configuring data access global parameters 9.4.12 Validate XML Streams No Triggers the validation of the XML streams parsed within Connection Server implementation against the XML schema (for example: connection definition). Description Default Note: You can only validate XML streams when using Java-based connectivities. No 9.
Configuring data access global parameters This configuration allows the library deployment mode with JNI and the server deployment mode. 9.6 Configuring the deployment mode Parameters defined in the Settings section of the cs.cfg file control the deployment mode. Library mode Parameters in the Library section control library mode. Server mode You manage this mode from the CMC of your SAP BusinessObjects Enterprise installation.
Configuring data access global parameters Caution: To avoid connection failures, you must make sure your environment provides all the necessary middleware, otherwise you must set the network layers and databases you only need in the Active Data Sources section. By allowing server specialization, this setting also addresses complex deployment scenarios involving multiple Connection Servers in server mode.
Configuring data access global parameters The Traces section of the cs.cfg file helps you activate traces for Connection Server jobs and drivers and log them separately from SAP BusinessObjects applications. You activate jobs traces by setting the Active attribute of Job and JobLevel to Yes. You activate driver traces by setting the Active attribute of the Middleware to Yes. You can activate traces for a specific driver by setting the Active attribute of the driver to Yes.
Configuring data access global parameters The traces are logged into a file whose default name is TraceLog___trace.log. If you want to set another name to the log file, update the BO_TRACE_PROCESS environment variable with the new name. 9.9.2 To activate traces in server mode You enable traces for jobs and drivers from the CMC. This allows you to let the server run while enabling traces. 1. Start the CMC. 2.
Configuring data access global parameters 2006/11/14 10:49:49.600|<<|||5784|5852|||||||||||||||||unknown|unknown|JobId:0| ENTER SQLSetEnvAttr SQLHENV 0x02E012A0 SQLINTEGER 200 SQLPOINTER 0x00000003 SQLINTEGER -6 2006/11/14 10:49:49.600|<<|||5784|5852|||||||||||||||||unknown|unknown|JobId:0| EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS) SQLHENV 0x02E012A0 SQLINTEGER 200 SQLPOINTER 0x00000003 SQLINTEGER -6 2006/11/14 10:49:49.
Configuring data access global parameters 108 2012-03-15
Configuring data access driver parameters Configuring data access driver parameters 10.1 Configuring driver parameters To configure data access for a particular data access driver, you can either edit the driver configuration files to adjust the parameter settings, or create your own database entries if you need connections for specific databases of your environment.
Configuring data access driver parameters Note: The parameters set in the DriverDefaults section of cs.cfg are overridden by corresponding settings in the SBO files. • .setup This file defines the SBO file name, directory and the database network layer or middleware that relates to the driver. This file is required to make the driver usable. A driver without any setup file cannot be used. All the files are in the connectionserver-install-dir\connectionServer\setup directory.
Configuring data access driver parameters Sub-directory jdbc 111 Database technology SBO file Data Federator Server datafederator.sbo IBM DB2 db2.sbo Derby derby.sbo Greenplum and PostgreSQL greenplum.sbo HSQLDB hsqldb.sbo IBM Informix informix.sbo Ingres ingres.sbo Generic JDBC jdbc.sbo MaxDB maxdb.sbo MySQL mysql.sbo HP Neoview neoview.sbo Netezza netezza.sbo SAP HANA newdb.sbo Oracle oracle.sbo Progress OpenEdge progress.sbo Microsoft SQL Server sqlsrv.
Configuring data access driver parameters Sub-directory 112 Database technology SBO file Teradata teradata.
Configuring data access driver parameters Sub-directory odbc 113 Database technology SBO file Microsoft Access access.sbo Microsoft Access 2010 access2010.sbo Data Federator Server datafederator.sbo IBM DB2 iSeries db2iseries.sbo Greenplum and PostgreSQL greenplum.sbo Greenplum 4 greenplum4.sbo IBM Informix informix.sbo Ingres ingres.sbo MaxDB maxdb.sbo MS Excel 2003 and 2007 msexcel.sbo MySQL mysql.sbo HP Neoview neoview.sbo Netezza netezza.sbo SAP HANA newdb.
Configuring data access driver parameters Sub-directory Database technology SBO file Progress OpenEdge progress.sbo Microsoft SQL Server sqlsrv.sbo Sybase sybase.sbo Teradata teradata.sbo Generic OLE DB oledb.sbo Microsoft SQL Server sqlsrv.sbo oledb_olap Microsoft Analysis Services slqsrv_as.sbo open CSV files open.sbo Oracle oracle.sbo Oracle EBS oracle_ebs.sbo sap SAP sap.sbo sybase Sybase sybase.
Configuring data access driver parameters from the greenplum.sbo and access.sbo files, because they deploy the configuration on both 32-bit and 64-bit MS Windows. Note Greenplum 4 database is also available on 64-bit Linux. odbc subdirectory also contains the msexcel.sbo configuration file for managing connections to MS Excel 2003 and 2007 through ODBC network layer. This file deploys the configuration on 32-bit MS Windows only. The personalfiles.
Configuring data access driver parameters You specify the SBO file name, its directory and the database network layer that is used for the connection. 2. Check that the file is valid against the DTD, save and close the file. 3. Either browse to the sub-directory where you want to store the SBO file or create your own sub-directory as specified in the setup file. 4. Create the SBO file with your target database and driver libraries by using an XML editor. 5.
Configuring data access driver parameters 10.1.6 JDBC driver properties You can add JDBC driver properties by doing one of the following: • • In the connection wizard of your application, at connection creation, set the "JDBC Driver Properties (key=value,key=value):" parameter. It is optional. Set driver properties in the appropriate SBO file after you stop Connection Server.
Configuring data access driver parameters These can work with either DataDirect ODBC 6.0 SP2 driver or DataDirect ODBC 6.0 SP2 branded driver. You can find the DataDirect branded driver files in boe-install-dir/enterprise_xi40/platformname/odbc/lib directory, where boe-install-dir stands for the BI platform installation directory and platform-name for the UNIX platform name. Caution: To use the branded driver, you must make sure the data access is configured correctly. 10.2.
Configuring data access driver parameters For example: [ODBC Data Sources] sql2005=sql=DataDirect 6.0 SQL Server Native Wire Protocol Driver=/.../enterprise_xi40/linux_x64/odbc/lib/CRsqls24.so Description=DataDirect 6.0 SQLServer Wire Protocol Driver Address=10.180.0.197,1433 Database=bodb01 8. Start Connection Server from the CMC. Connection Server is able to establish a connection to MS SQL Server databases with DataDirect ODBC branded drivers. Example: The following is an excerpt of the default sqlsrv.
Configuring data access driver parameters 120 2012-03-15
SBO parameter reference SBO parameter reference 11.1 SBO file structure There is a .sbo file for each supported data access driver. Each .sbo file is divided into the following sections: 121 File section Description Defaults This section contains the default configuration parameters that apply to all database middleware that uses the data access driver. These parameters override any corresponding values set in the database middleware.
SBO parameter reference File section Description This section contains a DataBase child element for each database middleware that is supported by the data access driver. Each DataBase element can contain the following elements or attributes: • Active: this attribute specifies if middleware support is activated or not. Its value is YES or NO. • Name: this attribute specifies the name of the middleware supported by the data access driver.
SBO parameter reference • • • • • ODBC OLE DB OLE DB for OLAP Open Sybase ASE/CTL Each parameter is shown with the following information: • example of how the parameter appears in the XML file • description of the parameter • possible values that can be set for the parameter • default value of the parameter Related Topics • Common SBO parameters • JavaBean SBO parameters • JCO SBO parameters • JDBC SBO parameters • ODBC SBO parameters • OLE DB SBO parameters • OLE DB OLAP SBO parameters • Open SB
SBO parameter reference Yes: the database supports array binding. Values No: the database does not support array binding. Default The value set in the cs.cfg file. 11.3.2 Array Bind Size Restriction: SAP BusinessObjects BI platform Client Tools do not use this parameter. 5 Description Specifies the number of rows exported with each INSERT query. Values The number of rows that are exported with each INSERT (integer). Default The value set in the cs.
SBO parameter reference Default The value set in the cs.cfg file. 11.3.4 Array Fetch Size 10 Specifies the number of rows of data retrieved per slice. Connection Server provides array fetch capability in any deployment mode. Remember: The Array Fetch Size value is propagated to the database middleware if it supports array fetch.
SBO parameter reference The number of rows retrieved per slice (integer). Values Note: 1 specifies that array fetching is disabled. Remember: 0 specifies that the driver decides which array fetch size to use. This value is only valid for JDBC drivers. The value set in the cs.cfg file. Default Note: 1000 is the default value for SAP HANA connections. Related Topics • ERP limitations • SAP HANA connections 11.3.
SBO parameter reference Description Specifies the separator character that is used between elements of database identifiers (qualifiers, owners, tables and columns). For example, database_name.table_name.column_name. Values The separator character to use. Usually a period. Default If not specified, Connection Server uses the separator specified in the database middleware. 11.3.
SBO parameter reference 11.3.9 Driver Capabilities Procedures,Query The capabilities of the driver, that is whether it can access stored procedures and queries available in the database software. This parameter is set using the connection wizard. You can include both values in the parameter. Description Note: This parameter must be set to Procedures for a JavaBean driver.
SBO parameter reference The name of the driver. Values Note: You can use a regular expression based on the GNU regexp syntax from PERL. Default No default value. 11.3.11 Escape Character / Description Specifies the character to use to escape strings of special characters, for example patterns. Values The character to use as the escape character. Default If not specified, Connection Server retrieves the value from the middleware. 11.3.
SBO parameter reference Related Topics • To create a JDBC connection with Extensions 11.3.13 Family Sybase Note: Do not edit this parameter. Description Specifies the family of the database engine that is displayed in the "Database Middleware Selection" page of the connection wizard. The set of middleware that corresponds to your license is displayed on this page in a tree view. 11.3.
SBO parameter reference 11.3.15 Identifier Quote String " Description Specifies the character used to quote database identifiers. Values The character used to quote database identifiers. Usually quotes ("). Default If not specified, Connection Server retrieves the information from the database middleware. 11.3.
SBO parameter reference Description Note: Do not edit this parameter. Specifies if performance monitoring through CA Wily Introscope is activated for the driver. True: the monitoring of the driver is activated. Values False: the monitoring of the driver is not activated. Default The parameter is set to False for all the drivers in the cs.cfg file. The monitoring is actually activated for SAP HANA connections only in the newdb.sbo file. 11.3.
SBO parameter reference Description Note: Do not edit this parameter. Indicates if 64-bit integers can be handled directly by the middleware. True: 64-bit integers can be handled by the middleware. Values False: the Data Access Layer emulates the Int64 methods. Default False 11.3.20 Optimize Execute False Description Specifies whether Connection Server optimizes the execution of SQL queries. This parameter is supported by Oracle and ODBC drivers only.
SBO parameter reference Yes: owners are supported. Values No: owners are not supported. Default Not specified. Connection Server retrieves this information from the database middleware. 11.3.22 Qualifiers Available No Specifies whether data access drivers handle database qualifiers. Description Note: To set table qualifiers manually in the information design tool, you must set this parameter to Yes. Yes: qualifiers are supported.
SBO parameter reference True: the database middleware handles query timeouts. Values False: the database middleware does not handle query timeouts. Default False 11.3.24 Quote Identifiers True Description Specifies whether the identifier of the stored procedure supports quotes. True: quotes are supported. Values False: quotes are not supported. Default True 11.3.
SBO parameter reference 11.3.26 SQL Parameter File oracle The name of the file that stores database parameters. The extension of this file is .prm. Description You must ensure that this file is located in the same directory as the SBO configuration file. Values See the list of values in the SBO file. Default The listed values. 11.3.27 SSO Available False Specifies whether single sign-on (SSO) is supported.
SBO parameter reference oracle Specifies the name, with no extension, of the Strategy file (.stg). Description This file contains the external strategies that universe design tool uses for automatic universe creation. Strategy files are stored in the same directory as the SBO file. db2 for IBM DB2 data access drivers informix for IBM Informix oracle for Oracle Values sqlsrv for MS SQL Server sybase for Sybase teradata for Teradata Default The listed values.
SBO parameter reference Default Yes. This is set in the cs.cfg file. 11.3.30 Type Relational Description Note: Do not edit this parameter. Specifies the data source type. 11.3.31 Unicode UTF8 Specifies if the access driver can benefit from the Unicode configuration of the client middleware. Description This parameter appears as a driver default in the cs.cfg file. Its value applies to all data access drivers.
SBO parameter reference 11.3.32 URL Format string Specifies the URL Format. The JDBC specification does not specify the format of the connection string that it requires. Vendors use different kinds of URL format, for example: • MySQL vendor: Description jdbc:mysql://$DATASOURCE$/$DATABASE$ • Oracle vendor: jdbc:oracle:thin:@$DATASOURCE$:$DATABASE$ Note: This parameter is supported by JDBC and JavaBean driver files only. Values The URL Format.
SBO parameter reference 11.4 JavaBean SBO parameters These parameters apply to the JavaBean SBO file. They are used to configure a JavaBean connection. These parameters are defined in the \\connectionserver-install-dir\connectionServ er\javabean\javabean.sbo file. 11.4.1 JavaBean Class string Defines the entry point of the JavaBean that the SAP BusinessObjects application uses.
SBO parameter reference 2147483647 Description Specifies the maximum number of rows that can be returned by a query to an InfoSet or SAP Query with no filter. Values An integer equal or lower than 2147483647. Default 2147483647 Related Topics • SAP ERP connections 11.6 JDBC SBO parameters These parameters apply to the JDBC SBO file. They are used to configure a JDBC connection.
SBO parameter reference Related Topics • Shared Connection 11.6.2 Escape Character Available True Description Specifies whether the JDBC network layer handles an escape clause after the like expression of the SQL query. This clause helps you to specify a character to escape specific characters such as underscores (_). True: an escape clause is supported. Values False: no escape clause is supported. Default True 11.6.
SBO parameter reference 11.6.4 Get Extended Column No Note: Do not edit this parameter. Description Specifies if the Data Access layer uses the getExtendedColumns stored procedure provided by the data federation implementation to retrieve input columns. Yes: getExtendedColumns is used. Values No: getExtendedColumns is not used. Default No 11.6.
SBO parameter reference 11.6.6 PrimaryKey Available True Description Specifies whether the primary keys of database tables can be retrieved. True: primary keys can be retrieved. Values False: primary keys cannot be retrieved. Default True 11.6.7 PVL Available True Specifies whether the connection supports the Preferred Viewing Locale (PVL) functionality.
SBO parameter reference Note: Do not edit this parameter. Description Specifies whether the requested connection of a connection pool is shared. Operates in conjunction with the Connection Shareable parameter. Is not taken into account if the Max Pool Time parameter of cs.cfg is set to 0. True: the connection is shared. Values False: the connection is not shared. Default False Related Topics • Connection Shareable • Max Pool Time 11.
SBO parameter reference Values ISO88591: the default character set value specified for HP Neoview on UNIX. Default If not specified, Connection Server uses the character set specified in the database middleware. 11.7.2 Connection Status Available True Description Specifies whether the middleware can detect a bad connection (ping function). True: the middleware can detect a bad connection.
SBO parameter reference 11.7.4 Empty String EmptyString Description Specifies that certain functions, for example SQL tables, receive either an empty string or a null pointer to replace missing parameters. NullString: a null string is used. Values EmptyString: an empty string used. Default EmptyString 11.7.5 ODBC Cursors No Description Specifies if the ODBC cursor library is used by the data access driver.
SBO parameter reference Description Specifies whether the middleware handles the SQLDescribeParam ODBC function. This function helps you describe the parameters of a stored procedure. This parameter is only used for IBM Informix database. True: the SQLDescribeParam function is available. Values False: the SQLDescribeParam function is not available. Default The value set in the database middleware. 11.7.
SBO parameter reference Yes: the connection can use the branded driver. Values No: the connection cannot use the branded driver. Default No Related Topics • Configuring data access for DataDirect ODBC branded drivers 11.7.9 V5toV6DriverName {Informix 3.34 32 BIT} Description Specifies the conversion rule from Informix Connect to Informix ODBC.
SBO parameter reference 11.8.1 Enumerator CLSID MSDASQL Enumerator Description Note: Do not edit this parameter. Specifies class ID of OLE DB enumerator. This parameter is used with OLE DB only. 11.8.2 Provider CLSID MSDASQL Description Note: Do not edit this parameter. Specifies class ID of OLE DB provider. This parameter is used with OLE DB only. 11.
SBO parameter reference msolap.4 Description Note: Do not edit this parameter. Specifies class ID of OLE DB provider. This parameter is used with OLE DB for OLAP only. 11.10 Open SBO parameters These parameters are used to configure a CSV or text file connection through BusinessObjects OpenConnectivity network layer. These parameters are defined in the \\connectionserver-install-dir\connectionServ er\open\open.sbo file. 11.10.
SBO parameter reference 11.11 Sybase SBO parameters These parameters apply to the Sybase ASE/CTLIB SBO file. These parameters are used to configure a Sybase ASE/CTLIB connection. These parameters are defined in the \\connectionserver-install-dir\connectionServ er\sybase\sybase.sbo file. 11.11.1 Driver Behavior Dynamic Description Specifies which one of the Sybase drivers is used. Dynamic: the ct_dynamic driver is used.
SBO parameter reference True: the encryption password mechanism of the middleware is used. Values False: the encryption password mechanism of the middleware is not used. Default The value set in the cs.cfg file. 11.11.3 Quoted Identifier False Description Specifies whether quoted identifiers are supported. This parameter is supported by Sybase middleware only. True: quoted identifiers are supported. Values False: quoted identifiers are not supported.
SBO parameter reference 11.11.5 Text Size 2147483647 Note: This parameter is not mandatory. Description Specifies the maximum size of large variable length binary or character data supported. Large variable length binary or character data retrieved from Sybase CTL database is truncated if larger than 32 KBytes. You add this parameter to the appropriate Database section of the configuration file to avoid the data truncation.
Configuring database capability parameters Configuring database capability parameters 12.1 About database capability parameters Database capability parameters describe capabilities of databases used as sources of data for the contents of a universe. You can set these parameters to operate at the following levels: • Universe level You set these parameters when you create or modify a universe. • Database level You set these parameters in the PRM file of the database.
Configuring database capability parameters 12.2.1 PRM parameter file structure There is a PRM file for each supported database driver. Each file is divided into sections, which contain specific parameters. The following table describes the content and meaning of each PRM file section. File section Description Parameters used to describe capabilities of databases used as source of data for universes, for example EXT_JOIN, ORDER_BY, and UNION.
Configuring database capability parameters File section Description Functions available to universe design tool and information design tool, for example Average, Sum, Variance. Functions are also available to Desktop Intelligence, which is not part of this release. Help text that appears when functions in this section are selected in universe designer tool and information design tool is listed in the file .prm, for example, oracleen.prm.
Configuring database capability parameters 3. Expand sections as required. 4. Set values by entering the value in the appropriate tag. 5. Save and close the file. 12.4 To view and edit a function help text file The Help text that appears under each function when selected in the universe design tool or information design tool, is maintained in a separate XML file. You can edit and add text to describe a function by editing the .prm file.
PRM parameter reference PRM parameter reference 13.1 PRM file configuration reference The database capability parameters are listed alphabetically. To view functions, date operators, and other operators available, open a .prm file in an XML editor, each parameter is defined in the following tag: value where parameter is the name of the parameter, and value is the value attributed to the parameter.
PRM parameter reference WHEN: indicates a WHEN clause is used. This is the default value for IBM Red Brick databases. Values QUALIFY: indicates a QUALIFY clause is used. This is the default value for Teradata databases. Note: Refer to your documentation database to find how it implements the SQL clause. Default See values above. Related Topics • ANALYTIC_FUNCTIONS 13.1.
PRM parameter reference YES: the database supports random sampling. Values NO: the database does not support random sampling. Default YES 13.1.4 DISTINCT YES Description Specifies if the database supports the DISTINCT keyword in SQL statements. This parameter is used with MS Access. YES: the database supports the DISTINCT keyword. Values NO: the database does not support the DISTINCT keyword.
PRM parameter reference YES: the database supports outer joins. Values NO: the database does not support outer joins. The Outer join check boxes in the Edit Join dialog box of the universe design tool are greyed. Default YES 13.1.6 FULL_EXT_JOIN YES Description Specifies if the database supports full outer joins. YES: the database supports full outer joins. Values NO: the database does not support full outer joins. Default YES 13.1.
PRM parameter reference 13.1.8 GROUP_BY_SUPPORTS_COLUMN_INDEX NO Description Specifies if the database supports the use of column indexes from the SELECT statement in the GROUP BY clause. YES: the database supports the use of column indexes from the SELECT statement instead of column names. Values NO: the database does not support the use of column indexes from the SELECT statement instead of column names. Default NO 13.1.
PRM parameter reference YES: the database supports complex expressions in the GROUP BY clause. Values NO: the database does not support complex expressions in the GROUP BY clause. Default NO 13.1.10 GROUP_BY_SUPPORTS_CONSTANT YES Description Specifies if the database supports the use of constant objects in the GROUP BY clause. This parameter is used by IBM DB2 and Microsoft SQL Server databases.
PRM parameter reference YES: the database supports the HAVING clause. Values NO: the database does not support the HAVING clause. Default NO 13.1.12 INNER_JOIN JOIN Description Specifies the syntax used to define inner joins between tables in FROM clauses. JOIN: the syntax used in queries to HIVE data sources is JOIN. Values INNER JOIN: the syntax used in queries to all other data sources is INNER JOIN. Related Topics • Apache Hadoop HIVE connections 13.1.
PRM parameter reference Default INTERSECT 13.1.14 INTERSECT_ALL YES Description Specifies if the database supports the INTERSECT ALL set operation. YES: the database supports the INTERSECT ALL set operation. Values NO: the database does not support the INTERSECT ALL set operation. Default YES 13.1.
PRM parameter reference YES Description Specifies if the database supports a JOIN operation between two tables. YES: the database supports joins with between any columns of two tables. Values STRUCTURE_JOIN: the database supports joins between two tables that are related with referential constraints. Is identical to NO in this release. NO: the database does not support joins between two tables. 13.1.
PRM parameter reference $(+): this syntax is used with Oracle. Values $*: this syntax is used with Sybase, MS SQL Server and IBM Red Brick. Note: $ represents a join expression. Default See values above. Example If table1.col1 is joined to table2.col2 in Oracle, the expression generated is then : table1.col1(+) = table2.col2. 13.1.
PRM parameter reference MINUS: the database supports the MINUS set operator. Values EXCEPT: the database supports the EXCEPT set operator. No value: the database does not support any keyword for the MINUS set operation. In this case, two queries are generated. Default MINUS 13.1.21 MINUS_ALL Yes Description Specifies if the database supports the MINUS ALL set operation. YES: the database supports the MINUS ALL set operation.
PRM parameter reference 13.1.23 ORDER_BY YES Description Specifies if the database supports the ORDER BY clause. YES: the database supports the ORDER BY clause. Values NO: the database does not support the ORDER BY clause. 13.1.24 ORDER_BY_REQUIRES_SELECT NO Description Values Specifies if the database requires columns used in the ORDER BY clause to be referenced in the SELECT statement.
PRM parameter reference Description Specifies if the database supports the use of column indexes from the SELECT statement in the ORDER BY clause. YES: the database supports the use of column indexes from the SELECT statement instead of column names. Values NO: the database does not support the use of column indexes from the SELECT statement instead of column names. 13.1.
PRM parameter reference Default YES 13.1.28 RIGHT_EXT_JOIN YES Description Specifies if the database supports right external joins. YES: the database supports right external joins. Values NO: the database does not support right external joins. 13.1.29 RIGHT_OUTER $(+) *$ Description Specifies the syntax to be used for right outer join expressions.
PRM parameter reference 13.1.30 SEED_SAMPLING_SUPPORTED YES Description Specifies if the database supports seed sampling. Seed sampling is a variation of random sampling in which the random seed is provided by the user. YES: the database supports seed sampling. Values NO: the database does not support seed sampling. Default NO 13.1.
PRM parameter reference 13.1.32 SUBQUERY_IN_FROM YES Description Specifies if the database supports the use of subqueries inside the FROM clause. YES: the database supports the use of subqueries inside the FROM clause. Values NO: the database does not support the use of subqueries inside the FROM clause. 13.1.33 SUBQUERY_IN_IN YES Specifies if the database supports the use of subqueries inside the IN clause.
PRM parameter reference YES: the database supports the use of subqueries inside the WHERE clause. Values NO: the database does not support the use of subqueries inside the WHERE clause. 13.1.35 UNION UNION Description Specifies the keyword supported by the database for the UNION set operation. UNION: the keyword supported by the database is UNION. Values No value : the database does not support any ekyword for the UNION set operation.
PRM parameter reference 13.1.37 UNION_IN_SUBQUERY YES Description Specifies if the database supports the use of the UNION set operation in subqueries. YES: the database supports the UNION set operation in subqueries. Values NO: the database does not support the UNION set operation in subqueries.
Data type conversion reference Data type conversion reference 14.1 Data type conversion In the information design tool, data foundations expose tables from one or more relational databases, which are the basis for business layers. The data type associated with each table column is displayed in a data foundation with other column details. Business layers expose table columns as metadata objects, like dimensions and hierarchies, and display the data type associated with each object.
Data type conversion reference The Data Access layer manages the conversion of data types exposed by network layers into Connection Server data types, which are then mapped to data types exposed in data foundations. This section provides conversion tables between generic network layer data types (JDBC and ODBC) and data foundation data types.
Data type conversion reference Type declared in the DDL file Data foundation data type BIT, BOOLEAN BIT DATE DATE TIME TIME TIMESTAMP TIMESTAMP BIGINT, DECIMAL DECIMAL FLOAT, DOUBLE, REAL DOUBLE INTEGER, INT, SMALLINT INTEGER NUMBER, NUMERIC NUMERIC VARCHAR VARCHAR Related Topics • Schema detection 14.1.3 JDBC The following table lists the data types that show up through JDBC and their equivalent in data foundations.
Data type conversion reference JDBC data type Data foundation data type BLOB, LONGVARBINARY LONGVARBINARY BIT, BOOLEAN BIT CLOB, NCLOB LONGVARCHAR DATE DATE TIME TIME TIMESTAMP TIMESTAMP DOUBLE DOUBLE FLOAT FLOAT REAL REAL SMALLINT SMALLINT CHAR, NCHAR CHAR LONGNVARCHAR, LONGVARCHAR, NVARCHAR, ROWID, VARCHAR VARCHAR SQLXML XML IBM Informix The following table lists the data types that could show up in results when the user queries an IBM Informix database through JDBC, and their
Data type conversion reference Informix JDBC data type Data foundation data type TIMESTAMP It can be mapped to: • DATE if the Informix data type follows the pattern DATE TIME\\s+(HOUR|MINUTE|SEC OND)\\s+TO\\s+(HOUR|MINUTE|SECOND) • TIME if the Informix data type follows the pattern DATE TIME\\s+(YEAR|MONTH|DAY)\\s+TO\\s+(YEAR|MONTH|DAY) • TIMESTAMP in the other cases BOOLEAN SMALLINT MS SQL Server The following table lists the data types that could show up in results when the user queries an MS SQL S
Data type conversion reference Oracle JDBC data type SQL type name Data foundation data type any value TIMESTAMP TIMESTAMP BINARY_DOUBLE any value DOUBLE OTHER FLOAT DOUBLE BINARY_FLOAT any value REAL any value NCHAR CHAR any value NVARCHAR2, ROWID, UROWID VARCHAR 14.1.4 ODBC The following table lists the data types that show up through ODBC and their equivalent in data foundations.
Data type conversion reference ODBC data type Data foundation data type SQL_DATETIME, SQL_TIME, SQL_TIMESTAMP, SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP TIMESTAMP SQL_LONGVARCHAR, SQL_WLONGVARCHAR LONGVARCHAR SQL_BIGINT BIGINT SQL_DECIMAL DECIMAL SQL_DOUBLE DOUBLE SQL_FLOAT FLOAT SQL_INTEGER INTEGER SQL_NUMERIC NUMERIC SQL_REAL REAL SQL_SMALLINT SMALLINT SQL_TINYINT TINYINT SQL_CHAR, SQL_GUID, SQL_WCHAR CHAR SQL_VARCHAR, SQL_WVARCHAR VARCHAR IBM DB2 The following table lists the data ty
Data type conversion reference DB2 ODBC data type Data foundation data type SQL_BLOB LONGVARBINARY SQL_CLOB, SQL_DBCLOB, SQL_LONGVARGRAPHIC LONGVARCHAR SQL_DECFLOAT DOUBLE SQL_GRAPHIC CHAR SQL_VARGRAPHIC VARCHAR SQL_XML XML IBM Informix The following table lists the data types that could show up in results when the user queries an IBM Informix database through ODBC, and their equivalent in data foundations. These data types overwrite the generic ones.
Data type conversion reference MS SQL Server The following table lists the data types that could show up in results when the user queries an MS SQL Server database through ODBC, and their equivalent in data foundations. These data types overwrite the generic ones. MS SQL Server ODBC data type Data foundation data type SQL_SS_TIME2, SQL_SS_TIMESTAMPOFFSET TIMESTAMP SQL_SS_XML XML 14.1.
Data type conversion reference OLE DB data type Data foundation data type DBTYPE_DECIMAL DECIMAL DBTYPE_I1, DBTYPE_UI1 TINYINT DBTYPE_I2, DBTYPE_UI2 SMALLINT DBTYPE_I4, DBTYPE_UI4 INTEGER DBTYPE_I8, DBTYPE_UI8 BIGINT DBTYPE_R4 REAL DBTYPE_R8 DOUBLE DBTYPE_CY, DBTYPE_NUMERIC NUMERIC DBTYPE_STR, DBTYPE_WSTR VARCHAR DBTYPE_STR, DBTYPE_WSTR if DBCOLUMN FLAGS_ISLONG=true LONGVARCHAR DBTYPE_STR, DBTYPE_WSTR if DBCOLUMN FLAGS_ISFIXEDLENGTH=true CHAR MS SQL Server data types The following t
Data type conversion reference MS SQL Server OLE DB data type Data foundation data type DBTYPE_XML XML 14.1.6 Oracle OCI The following table lists the data types that show up through Oracle OCI and their equivalent in data foundations.
Data type conversion reference 14.1.7 Sybase CTL The following table lists the data types that show up through Sybase CTL and their equivalent in data foundations. Note: Sybase CTL data types are values of datatype field of CS_DATAFMT structure, which is actually exposed by the network layer. The usertype field value of the structure is also provided for some data types and used for mapping by the Data Access layer.
Data type conversion reference Sybase CTL data type 189 Sybase CTL usertype Data foundation data type CS_DECIMAL_TYPE, CS_MONEY_TYPE, CS_MONEY4_TYPE DECIMAL CS_FLOAT_TYPE DOUBLE CS_INT_TYPE , CS_UINT_TYPE INTEGER CS_NUMERIC_TYPE NUMERIC CS_REAL_TYPE REAL CS_SMALLINT_TYPE, CS_USMALLINT_TYPE SMALLINT CS_TINYINT_TYPE TINYINT CS_CHAR_TYPE CS_CHAR_USERTYPE or CS_NCHAR_USERTYPE or CS_UNICHAR_USERTYPE CHAR CS_CHAR_TYPE CS_VARCHAR_USERTYPE or CS_NVARCHAR_USERTYPE or CS_UNIVARCHAR_USERTYPE or
Data type conversion reference 14.2 Large variable length data limitation Restriction: The limitation only relates to Crystal Reports applications. Due to Data Access layer implementation, the maximum size of a column for large variable length binary and character data is limited to 16MB for the following data sources: • IBM DB2 through ODBC • MS SQL Server through ODBC • Sybase CTL If an application user inserts large variable length data larger than 16MB into a report, system performance may be affected.
More Information More Information Information Resource Location SAP product information http://www.sap.com http://help.sap.com/businessobjects SAP Help Portal Access the most up-to-date English documentation covering all SAP BusinessObjects products at the SAP Help Portal: • http://help.sap.com/bobi (Business Intelligence) • http://help.sap.com/boepm (Enterprise Performance Management) • http://help.sap.
More Information Information Resource Location https://service.sap.com/notes Notes These notes were formerly known as Knowledge Base articles. Forums on the SAP Community Network https://www.sdn.sap.com/irj/scn/forums http://www.sap.com/services/education Training From traditional classroom learning to targeted e-learning seminars, we can offer a training package to suit your learning needs and preferred learning style. http://www.sap.
Index 64-bit support about 25 Microsoft Windows support 26 UNIX environment 25 A ABAP functions 71 about the guide 13 ActiveDataSources setting 103 Adaptive Connectivity service 22 ANALYTIC_CLAUSE 159 ANALYTIC_FUNCTIONS 160 Apache Hadoop HIVE connections 75 architecture 18 Array Bind Available 123 Array Bind Size 124 Array Fetch Available 124 Array Fetch Size 73, 125 audience of the guide 13 B backward compatibility 34, 63 BigDecimal Max Display Size 126 branded drivers 117 Bucket Split Size 151 C Catalo
Index data types (continued) Sybase 188 databases capability parameters 155 checking access 47 configuring access 103 listing supported 40 selecting access to 104 supporting stored procedures 28 DataDirect 117 DDL files 66 deployment mode 21, 103 Description Extension 97 Description File 127 DISTINCT 161 Driver Behavior 152 Driver Capabilities 128 Driver Name 128 DriverDefaults 95 dsn entry 89 E Empty String 147 Enumerator CLSID 150 ERP Max Rows 73, 141 Escape Character 129 Escape Character Available 142
Index middleware (continued) listing supported 42 MINUS 168 MINUS_ALL 169 MINUS_IN_SUBQUERY 169 MS Access DISCTINCT 161 MS SQL Server 117 MSOlap CLSID 151 multisource universes 190 ORDER_BY 170 ORDER_BY_REQUIRES_SELECT 170 ORDER_BY_SUPPORTS_COLUMN_INDEX 170 Owners Available 133 P Native Connectivity service 22 Native Int64 Available 132 network layers checking connectivity with middleware 46 listing supported 40 selecting 103 new in the guide 13 Password Encryption 152 PERCENT_RANK_SUPPORTED 171 Primar
Index SUBQUERY_IN_IN 174 SUBQUERY_IN_WHERE 174 supported connectivities 42 supported databases 40 supported network layers 40 Sybase connectivity check example 47 data types 188 Driver Behavior 152 LEFT_OUTER 167 Password Encryption 152 Quoted Identifier 153 Recover Errors 153 RIGHT_OUTER 172 Text Size 154 system architecture 18 196 T Teradata ANALYTIC_CLAUSE 159 Cost Estimate Available 146 SELECT_SUPPORTS_NULL 173 stored procedures 31 Text Size 154 traces 104, 105, 106 Traces 95 Transaction Available 13
