Spectre Build ODBC Input

The Spectre Build ODBC Input object brings database data into the build script by using a query against an Open Database Connectivity (ODBC) data source.

odbc-input icon

The odbc input object has three sections in the properties panel where you set attributes.

odbc-input properties panel

You specify additional attributes in the ClosedColumns override section of the Properties Panel.

odbc-input column overrides

NOTE: The column overrides are not intended to be used as a way to do data type casting. For example, in general, a column override does not work with date data in a DB. Either create an SQL query that converts the field, or, once the field is read by the Build, replace the value using an explicit date() function.

Use the plus and minus buttons to add and delete rows as needed.

odbc-input column overrides

With a row selected, you can set related attributes on the Column Override Properties tab.

Attribute Description
Column

Lists the name of the column in the data source.
Original Type

Shows the data type as defined in the database schema.
Override Type

Sets the Spectre data type to be used for this column. Valid values are boolean, date, datetime, double, fixed100, integer, period, and string. See Spectre Data Types.

If the type is period, be sure to specify the calendar in the Column Override Properties tab.

TIP: Fields of type bit from SQL Server default to type string in Spectre. You can specify the type as boolean for better performance.

Each object has an area where you can enter comments for documentation purposes.

It is a DI best practice to enter a note for every object in a Spectre build script.

Comments dialog

The Properties section has four tabs.

In the odbc input Properties ClosedQuery tab, you enter an SQL select statement to pull data from the database table.

odbc-input query property

Enter a query using SQL syntax. See the ODBC tab for an external file option.

In the odbc input Properties ClosedODBC tab, you set connection values and other options.

Attribute Description

Connect File Path

Specifies a binary file from which to retrieve credentials. This assumes that you previously generated a connect file using Integrator. For example:

echo "DSN=my-dsn;UID=my-user;PWD=my-password" | integ -save_connect_file my_connect_file.connect

See Options for ODBC Login Credentials.

Connect String

Specifies, using one tag, a string with DSN, user, and password. For example:

"DSN=my-dsn;UID=my-user;PWD=my-password"

NOTE: Different ODBC drivers might use other connect string formats.
DSN

Specifies the data source name (DSN) for the ODBC data. A DSN is a data structure that contains the information about a specific database that an ODBC driver needs in order to connect to it.

Password

Sets the password for the user logging into the ODBC data. This tag is optional.

User

Sets the user to log into the ODBC data. This tag is optional.

Allow DateTime

This attribute is deprecated. Previous usage allowed a timestamp column in the ODBC source to be recognized as a datetime column. Without this tag, an SQL datetime field was treated as a date type, discarding the time portion of the field.

Datetime columns are now recognized without using this attribute. Default is true.

Encoding

Defines the encoding used in the input and output files. Encoding types supported include: cp-1252, iso-8859-1, utf-16be, utf-16le, utf-8, and gb18030.

  • Best practice is to specify the encoding of the input data.
  • For text inputs, if no encoding is specified, Spectre analyzes the first few megabytes of the file to attempt to determine the encoding.
  • For databases, if no encoding is specified, Spectre queries the driver for unicode data; if encoding="utf8" is specified, Spectre accepts the data as-is from the driver; if encoding is specified as something else, Spectre transcodes the bytes from the driver.
  • Information output by the build indicates which encoding is in use.
  • Non-Unicode DI clients are Unicode-aware. See also Integrator Unicode Data Support.

  • When using the Easysoft ODBC-to-JDBC Bridge, specify encoding="utf-8" to work around a driver bug.

Limit Rows

Directs the build to use a specific number of rows from each input. If multiple inputs are used, the first x rows are read from each, ensuring a complete sampling of data values.

TIP: Using a "LIMIT" statement or "TOP X" in your SQL query is the optimal way to limit the number of rows coming from the database.
Prefix Prefixes all the outputted column names with the specified string.

Query File Path

Specifies the path to a query that is saved in an external file.

Stale After

Defines a time threshold, in seconds, for a cache entry to turn stale.

Trim Whitespace

Controls removal of leading and trailing white space before writing strings to the cBase. Default is true.

In the ODBC input ClosedColumn Override Properties tab, you can configure more settings. Be sure you have made a selection in the Column override table on the left.

odbc-input column override properties

Attribute Description
Calendar

Assigns a calendar and period type to the column. Valid calendars are:

  • standard—Gregorian calendar; period type options are year, year-half, year-quarter, year-tertile, and year-month.
  • offset <month>—Fiscal year similar to the standard calendar except starting on a different month; period type options are the same as those for standard calendars.
  • iso 8601—ISO week-numbering system; period type options are year and year-week.
  • custom—Custom calendar created from a text file with a contiguous range of dates; required columns are date and year, optional columns are half, quarter, tertile, month, and week.
Column Name

Indicates the name of the column in the data source. This corresponds to the selected row in the Column override table.
Disable Similar Values Warning

Turns off data warnings of similar values for this column. Use only after data validation steps have determined that the warnings are in fact not worrisome.
Format

Formats the "date" or "period" data for the cBase. To store dates like July 4, 2013, use format="MMMM D, YYYY".

Use to format currency. For example: column-override "Revenue" type="fixed100" format="$#,#.00"
Input Format

Clarifies the incoming 'date' or 'period' data. The default format is YYYY/MM/DD. Use input-format to define the date format in the source to ensure correct interpretation of date data.
Label

Defines the display label used for a column.
Required Dimension

Sets a column to behave like classic Info Fields. In ProDiver, this field is available as a column that you can add to other columns displayed in a dive.
Suggested Dimension Allows you to promote (true) or demote (false) a column:
  • Set to true to promote a numeric column type to become a diveable dimension and have it appear in the ProDiver console.
  • Set to false to demote a column from classification as a dimension so it does not appear in the ProDiver console.
  • When set to false, the new dimension does not appear in the console (which requires dimcount processing), but it can still be promoted to a dimension for diving—equivalent to the concept of a dynamic dimension for a classic model.
  • Using required-dimension on a column effectively sets suggested-dimension=false.
Summary Type

Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) are allowed. Most common are sum, info, any, first, last and const. The default for numeric data is sum; use kahan_sum if concerned about precision; use const for a column with only one value. The rest of the functions (for example, min, average, median) are better done as a separate calc (that is, not the default one).

See Summary Functions.

In the odbc input Properties ClosedQuery Bindings tab, you enter parameter binding information for the database query.

odbc-input properties for query binding

Attribute Description
Value Enter what should appear after the bind tag in the Spectre Build script.
Type

Indicate what kind of literal type to use after the bind tag. Options are integer, boolean, double, expression, fixed 100, integer, null, or string. The default is string.

For example:

  • value=foo and type=string gives `bind "foo"`

  • value=12 and type=integer gives `bind 12`

  • value=$(ClientID) and type = string gives `bind "$(ClientID)"`

See also ODBC Input Build Tags.