ODBC Input Build Tags

Defines the block for this build description.

For example:

build {

...

}

Workbench 7.0 only. Allows a timestamp column in the ODBC source to be recognized as a datetime column. Without this tag, an SQL datetime field is treated as a date type, discarding the time portion of the field.

For example:

odbc-input {

dsn "bugzilla"

query "select \"lastdiffed\" from \"bugs\" LIMIT 10"

allow-datetime

}

Binds the parameter value to the variable in the SQL query.

For example:

build {

take-parameter "INTNUMB" default="11167800"

odbc-input{

dsn "star-prod"

query """

SELECT INTNUMB ,SEX ,BIRTHDATE

FROM AGEDEMO WHERE INTNUMB = ? """ {

bind "$(INTNUMB)"

}

}

output "/temp/import_star_agedemo.cbase"

}

Available in 7.0(35) and later. 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 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.

See General Build Tags for a different use of calendar as a Build tag.

For example:

column-override "Quarter" type="period" calendar="standard" period="year-quarter"

column-override "Fiscal Month" type="period" calendar="offset August" period="year-month"

Supports a 'period' type column. Valid calendars are:

• gregorian month—Gregorian year-month
• gregorian quarter—Gregorian year-quarter
• iso8601 week—ISO-8601 year-week
• gregorian+<N> month—Offset Gregorian year-month for fiscal calendars. Specifying "gregorian+6 month" means "add six months to the real year-mo to get the fiscal year-mo", which is like saying "The fiscal year starts in July".
• <custom calendar file>—For more tailored fiscal calendars
• See Spectre Calendars.

For example:

column-override "Quarter" type="period" calendar="gregorian month" format="YYYY/MM" input-format="YYYY/M"

Specifies the columns for the cBase and overrides the indicated attributes. For example, override the default numeric type to string; specify that the number has 2 decimal places.

NOTE: Use of column-override causes the Spectre build engine to request the given field from the database using a specific type. If the database or its ODBC driver cannot perform that conversion, then an error is generated. Use of column-override does not cause the Spectre build engine to perform any conversions itself.

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

column-override "Order ID" type="string"

column-override "Order Unit Price" type="fixed100"

Specifies a binary file 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.

For example:

odbc-input {

connect-file "my_connect_file.connect"

...

}

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

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

For example:

odbc-input {

connect-string "DSN=my-dsn;UID=my-user;PWD=my-password" ...

}

Defines custom-properties to associate extra data with a column. The custom-property can be extracted after the build and used in expressions.

For example:

custom-property "Confidence" "High"

Specifies the data source name (DSN) for the ODBC data.

For example:

dsn "Northwind"

dsn "Meditech"

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.

NOTE

• 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.
• Spectre Build's informational output indicates which encoding is in use.
• Non-Unicode DI clients are Unicode-aware.

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

For example:

odbc-input encoding="utf-8" {

...

}

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 "Request Date" type="date" format="YYYY-MM-DD" input-format="YYYY-M-D"

column-override "Price" format="$#,0.00"

column-override "Revenue" type="fixed100" format="$#,#.00"

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.

For example:

column-override "Date" type="date" format="MMM D, YYYY" input-format="YYYY-MM-DD"

Defines the display label used for a column.

For example:

column-override "Color" type="string" label="favorite"

Directs the build to use a specific number of rows from each input. If multiple inputs are used, the first 100 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.

For example:

limit-rows=100

Sets the ODBC input. Note the following:

• ODBC data source name (DSN) and query are required.
• ODBC user, password, column-override types, calendar, and format are optional.
• Zero or more column-overrides, properties, and custom-properties are allowed.
• A text or variable length field with an unspecified maximum length is capped at 32,767 characters.
• When run, key stages are reported: opening the DSN, running the query, reading the structure of the result, reading the data of the result, and constructing the output table.
• The query can be inline or stored in an external file.

For example:

odbc-input encoding="UTF8" {

dsn "Northwind"

query """

select Orders.OrderID,

Orders.OrderDate,

Orders.ShippedDate

from Orders

"""

}

odbc-input query-path="/data/inventory.sql" {

...

}

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

For example:

password "mysecret"

Verbose way to set label/format/calendar properties.
Deprecation expected.

For example:

property "Function" "Managers"

Specifies the query into the database. The SQL query string takes multiple lines, so triple quotes are needed to open and close the string.

See also query-path. Use query or query-path, but not both.

For example:

query """

SELECT Orders.OrderID

Orders.OrderDate,

Customers.CompanyName as CustomerName,

Customers.ContactName as CustomerContact,

Customers.Phone as CustomerPhone

FROM Orders, Customers
WHERE Orders.CustomerId = Customers.CustomerId
"""

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

For example:

odbc-input query-path="/data//revenue-one-query.sql"

odbc-input query-path="/data/queries/$(TYPE)_drg.sql"

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.

For example:

column-override "Product Status" type="string" required-dimension="Status"

Sorts the data when processing. When specifying columns, use sort-by to order them based on another column's value.

NOTE

• An alternate sort can only be applied to a string column, and the sort-by column must be a string or an integer column
• Dive and cPlan files have a different sort tag.
• Spectre uses "numeric collation" when sorting strings. When a subset of a string looks like a number, then the numeric value is considered during the sort. For example, Spectre sorts "a2" before "a10". See also sort-natural.
• Null strings sort to the end.
• If the alternate sort has duplicates, the fall back is the main column.

• If a row has the value null or the value unknown in the column with an alternate sort, then the sort-by column must also have the same value.

• If the sort-by column has type string AND has the value null or the value unknown, then the column to be sorted must have the same value.
• Spectre issues a warning if you attempt to alternate sort null as non-null, or unknown as not-unknown.

For example:

column-override "Symbol" type="string" sort-by="Number"
column-override "Number" type="integer"

column-override "Priority Sort" type="integer"
column-override "Priority" type="string" sort-by="Priority Sort"

Disables natural string sorting in odbc-input. The default is true—Spectre interprets value of digits as decimal numbers and sorts according to the numeric values in the string.

TIP: This feature only makes sense together with sort-by if the sort-by column is also a string column.

For example:

column-override "ICD-10-code" type="string" sort-natural=false

add "diagnosis" `value("Diagnosis Code")+" -- "+value("Diagnosis Desc")` sort-natural=false

Defines a time threshold, in seconds, for a cache entry to turn stale. Use with odbc-input.

The example here will re-run the lookup at most once every minute so that people looking at the result see data at most one minute old.

The stale-after attribute can also be used with tunnel-input.

For example:

cplan {

cbase-input "foo.cbase" {

lookup {

odbc-input {

dsn="some dsn"

query="select a, b from foo"

stale-after 60

}

key "a" "a"

column "b"

}

}

}

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.

For example:

column-override "Product Class" type="string" suggested-dimension=false

Sets the column's summary type explicitly. Any summary function that takes a single argument (except for count and percentile) are allowed. See Summary Functions.

Most common are sum, info, any, first, and last. 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).

For example:

column-override "Quantity In Stock" type="integer" summary-type="info"

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

For example:

odbc-input trim=false {

...

}

Specifies the type of data in the column. Valid values are boolean, date, datetime, double, fixed100, integer, period, and string. See Spectre Data Types.

If the type is period, a calendar tag is required.

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

For example:

column-override "Qty Ordered" type="integer"

column-override "Telephone Number" type="string"

column-override "OrderUnitPrice" type="fixed100"

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

For example:

user "joey"