Creating and Using Oracle-Based Report Models
New: 12 December 2006
You can generate report models based on an Oracle database running version 9.2.0.3 or later by using Model Designer, Report Manager, or SQL Server Management Studio.
To generate models based on Oracle databases, you must be running an Oracle client version 9.2.0.3 or later. The Oracle client directory must be located in the system path and both the Report Server Windows service and Report Server Web service must have permissions to access the files in this directory.
In addition, verify:
- The Reporting Services Oracle data processing extension is available in your Reporting Services installation.
- The Oracle client is installed in a folder that can be accessed by the Report Server Windows service and the Report Server Web service.
- The Report Server Windows service, Report Server Web service, and unattended execution accounts must all have Read and Execute directory access to the Oracle client installation.
Connection Strings
When building a report model based on an Oracle database, your connection string appears similar to the following:
Data Source="Oracle9";Unicode="True"
where "Oracle9" represents the logical name of the Oracle server.
Note
When you specify a connection string to the Oracle database, the ServerName must match the names defined in the tnsnames.ora configuration file.
For more information about how to connect to an Oracle data source, see Defining Report Datasets for Relational Data from an Oracle Database.
Important
When generating the data source view for your Oracle model, make sure that you click the Advanced button on the Select a Data Source page of the Data Source View Wizard and, in the Advanced Data Source View Options dialog box, select the schema name to filter the database objects. Then select the database objects that you want to add to your model.
Functional Limitations
Because of the underlying behavior of the Oracle database, there are some behavioral differences in the Semantic Model Definition Language (SMDL) query translator when used with an Oracle database. These behaviors can include minor differences in functionality and configuration switches that are required to control workarounds for bugs and design limitations in the target database. The following is a description of these differences.
Data Types
SMDL supports only the following Oracle data types.
| SMDL Type | Oracle Type |
|---|---|
String |
CHAR, NCHAR, VARCHAR2, NVARCHAR2, LONG, CLOB, NCLOB |
Decimal or Integer |
NUMBER |
Boolean |
NUMBER If an Oracle column is marked as Boolean in the data source view (DSV) file, the underlying column must be NUMBER and contains only the values 0, 1, or NULL. |
Date |
DATE, TIMESTAMP |
Binary |
RAW, LONGRAW, BLOB |
SMDL does not support any data type that is not listed above, including the following:
- BINARY_FLOAT
- BINARY_DOUBLE
- TIMESTAMP WITH TIME ZONE
- TIMESTAMP WITH LOCAL TIME ZONE
- INTERVAL YEAR TO MONTH
- INTERVAL DAY TO SECOND
- ROWID
- UROWID
- BFILE
During auto generation of a report model using Report Manager or SQL Server Management Studio, columns that contain unsupported data types are excluded from the model. In addition, if an unsupported data type is part of a primary key, the entire entity is not generated.
When creating a report model using Model Designer, everything is added to the report model; therefore, you will have to filter out all unsupported data types listed above manually when refining the model.
Functions
The behavior of some functions may depend on the underlying database. If you specify a value less than zero for the length argument in the Left, Right, or Substring function, the behavior is undefined in SMQL.
Configuration Switches
Due to the underlying data source, many configuration switches are available to modify the behavior of the query translator based on the specifics of the data source and the environment. The following switches are defined in the RSReportServer.config file, which is installed in the <SysDrive>:\Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportServer\ folder by default.
| Name | Type | Definition Default | Description |
|---|---|---|---|
Disable NO_MERGE InLeftOuters |
Boolean |
False |
Switch turns off the NO_MERGE optimizer hint that is generated for Oracle queries. Due to technical issues in the Oracle query optimizer, certain classes of queries involving left outer joins will fail or return incorrect results. Therefore, the Reporting Services Oracle query translator uses the NO_MERGE optimizer hint in some left outer joins. While this results in correct query execution, performance may be adversely affected. If you obtain a software update from Oracle which addresses this issue, this switch may be set to True to disable the NO_MERGE workaround. |
EnableUnistr |
Boolean |
False |
Switch forces the explicit use of the UNISTR function to represent Unicode literals in Oracle queries. Typically, when the locale of the database matches the locale of the string constants contained in the query, Unicode string constants can be generated as regular strings in the query. When using literal constants which do not exist within the codepage defined by the NLS_CharacterSet of the Oracle database, Oracle requires the use of the explicit UNISTR function to represent Unicode literals. Setting this switch to True results in all Unicode string constants in queries to be encoded using the Oracle UNISTR function. |
DisableTS Truncation |
Boolean |
False |
Switch turns off explicit truncation of fractions of seconds in TIMESTAMP when converting to DATE. Due to Oracle’s default rounding behavior, which casts timestamps as dates, and because certain date operations require dates rather than timestamps, the only way to guarantee the accuracy of these operations is to explicitly truncate fractions of seconds from timestamp data types before converting to date. This truncation is applied to dates as well because the Oracle query translator does not have access to the underlying Oracle type information to distinguish between timestamp and date. This has a small performance impact on many date operations. In the event that no timestamps are used in any target database, this switch can be set to True, omitting the unnecessary truncation. |
EnableMath OpCasting |
Boolean |
False |
Switch turns on casting of results of math operations into a Microsoft .NET Framework decimal format. Certain math operations in queries may result in decimal values with a combination of precision and scale that cannot be represented in the .NET Framework decimal data type. While you can explicitly cast the results of such decimal expressions as decimal, it is unlikely that your Report Builder users will know that this is necessary at design time. In the event that such expressions are frequently created, this switch can be set to True, to automatically perform the needed cast. |
See Also
Concepts
Working with Model Designer
Generating Models Using Report Management Tools
Data Sources Supported by Reporting Services
Connecting to a Data Source