Database functions

Analytica User GuideIntegration with data and applicationsDatabase functions

The Database library on the Definition menu contains five functions for working with ODBC databases.

You can avoid writing SQL and make sure your code is compatible with several common database platforms by using the Database library.

These functions are available in Analytica Enterprise and Optimizer.

DbLabels(dbIndex)

Returns a list of the column labels for the result table. This statement can be used to define an index which can then be used as the second argument to DbTable(). The first argument, «dbIndex», must be defined by a DbQuery() statement. See DbLabels().

DbQuery(connection, sql, key)

Used to define an index variable. The definition of the index should contain only one DbQuery() statement. «connection» specifies a data source (e.g., 'DSN = MyDatabase') and «sql» defines an SQL query. The optional «key» parameter may specify the name of a column in the database that will be used to determine the row index values returned by DbQuery. It is best to only use key columns that contain unique values in each row.

When placed as the definition of an index variable, DbQuery() is evaluated as soon as the definition is complete, or immediately when the model is loaded. If you place it in a variable node, it will be evaluated when the result is requested, rather than when the model is loaded, provided there are no index nodes downstream that depend on it. When it is evaluated, the actual query is performed. The resulting result table is cached inside Analytica, to subsequently be accessed by DbTable() or DbLabels().

When the optional «key» parameter is not specified, DbQuery() returns a sequence 1..n, where n is the number of records (rows) in the result table. When «key» is specified, DbQuery() returns a list consisting of the values from that column in the data source.

DbQuery() should appear only once in a definition, and if it is embedded in an expression, the expression must return a list with n elements.

DbQuery() processes the sql statement in read-only mode, so that the data source cannot be altered as a result of executing this statement. To alter the data source, use DbWrite().

DbTable(dbIndex, column), DbTable(dbIndex, columnList), DbTable(dbIndex, columnIndex)

DbTable() is used to get at the data within a result table. The first argument, «dbIndex», must be the name of a variable (normally an index) in your Analytica model that is defined with a DbQuery() statement. If the second argument, «column», is a text value, it identifies the name of a column label in the result table, in which case DbTable() returns a 1D array (indexed by «dbIndex») with the data for that column. If the second argument is a list of text values (the «columnList» form), then DbTable() returns a 2D table with records indexed by «dbIndex», and columns implicitly indexed (i.e., self-indexed/null-indexed). If the second argument is the name of an Analytica variable (usually an index) whose value evaluates to a list of text values, those text values become the column headings for a 2D table with columns indexed by «columnIndex», and rows indexed by «dbIndex». With this last form, «columnIndex» can be defined as DbLabels(dbIndex).

DbTableNames(connection, catalogs, schemas, tables, tableTypes)

Connects to an ODBC data source and returns catalog data for the data source. «connection» specifies a data source (e.g., 'DSN = MyDatabase'). «Catalogs», «schemas», «tables», and «tableTypes» may be names or patterns (as long as the driver manager on your computer is ODBC 3-compliant). Use the percent symbol (%) as a wildcard in each field to match zero or more characters.Underscore (_) matches one character. Most drivers use backslash (\) as an escape character,so that the characters %, _, or \ as literals must be entered as \%, \_, or \\. «tableTypes» might be a comma-delimited list of table types. Your data source and ODBC driver might or might not support this call to varying degrees. See DbTableNames().

Examples: To get all catalog entries (tables, views, schemas, etc) in My db:

DbTableNames('DSN = My db')

To get just information on tables in My db:

DbTableNames('DSN = My db', tables:'%')

To get all valid views:

DbTableNames('DSN = My db',tableTypes:'VIEW')

The precise value you give to «tableTypes» depends on which database server you are querying. If you are not sure which type names are used by your database, evaluate DbTableNames() first with none of the parameters and you should see a column that provides the type names for existing catalog entries.

DbWrite(connection, sql)

This function is identical to DbQuery() except that the query is processed in read-write mode, making it possible to store data in the data source from within Analytica. See DbWrite().

MdxQuery(connection, mdx)

MdxQuery() lets you read or write multidimensional data on an OLAP server database, returning or sending a multidimensional Analytica array. It uses the standard query language, MDX. MDX is analogous to SQL, but where SQL accesses any standard relational database, MDX accesses multidimensional “hypercube” databases. MdxQuery() works with Microsoft SQL Server Analysis Services.

«connection» is the standard text used to identify and connect with the database, similar to that used in other database functions, such as DbQuery(). «mdx» is text containing the query in the MDX language.

MdxQuery() creates a local index for each dimension. The local indexes are named .Axis1,.Axis2, .Axis3, etc., and contain the cube member captions as elements. Some cube axes returned from MDX queries are hierarchical, and for these, MdxQuery() concatenates member captions, separated by commas. For example, if a particular hierarchical axis included calendar year and quarter, an element of .Axis1 might be “2003,1”, i.e., Calendar year 2003, quarter 1. To use a separator other than comma, specify an optional parameter, «sep», to MdxQuery.

For additional usage information and examples, please refer to MdxQuery on the Analytica wiki.

SqlDriverInfo(driverName)

Returns a list of attribute-value pairs for the specified driver. If driverName=' ' (an empty text value), returns a list of the names of the drivers. «driverName» must be a text value — it cannot be a list of text values or an index that is defined as a list of text values. This statement would not normally be used in a model, but might be helpful in understanding the SQL drivers that are available. See SqlDriverInfo().