Database functions

Analytica User GuideAnalytica EnterpriseDatabase functions

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

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.

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 table-Types 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.

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.

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.

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.