Strategy ONE

Selecting and Inserting Data with SQL: Select/Insert

The following table summarizes the Select/Insert VLDB properties. Additional details about each property, including examples where necessary, are provided in the sections following the table.

Property Description Possible Values Default Value

Attribute Selection and Form Selection Option for Intermediate Passes

Allows you to choose whether to select attribute forms that are on the template in the intermediate pass (if available).

Select ID form only

Select ID and other forms if they are on template and available in existing join tree

Select ID form only

Attribute Form Selection Option for Intermediate Pass

Allows you to choose whether to select additional attributes (usually parent attributes) needed on the template as the join tree and their child attributes have already been selected in the Attribute Form Selection option for Intermediate Pass.

(Default) Select only the attributes needed

Select other attributes in current join tree if they are on template and their child attributes have already been selected.

Select only the attributes needed

Selecting and Inserting Data with SQL: Select/Insert

Determines whether multiple insert statements are issued in the ODBC call, and if together, the string to connect the multiple insert statements.

User-defined

NULL

Constant Column Mode

Allows you to choose whether to use a GROUP BY and how the GROUP BY should be constructed when working with a column that is a constant.

Pure select, no group by

Use max, no group by

Group by column (expression)

Group by alias

Group by position

Pure select, no group by

Custom Group Interaction with the Report Filter

Allows you define how a report filter interacts with a custom group.

No interaction - static custom group

Apply report filter to custom group

Apply report filter to custom group, but ignore related elements from the report filter

No interaction - static custom group

Data Retrieval Mode

Determines whether data is retrieved using third-party, native APIs.

Only ODBC

Allow Native API

Only ODBC

Data Retrieval Parameters

Defines the parameters used to retrieve data using third-party, native APIs.

User-defined

NULL

Data Mart Column Order

Allows you to determine the order in which datamart columns are created.

Columns created in order based on attribute weight

Columns created in order in which they appear on the template

Columns created in order based on attribute weight

Date Format

Sets the format for date in engine-generated SQL.

User-defined

YYYY-MM-DD

Date Pattern

Lets you define the syntax pattern for Date data.

User-defined

NULL

Decimal Separator

Use to change the decimal separator in SQL statements from a decimal point to a comma, for international database users.

Use "." as decimal separator (ANSI standard)

Use "," as decimal separator

Use "." as decimal separator (ANSI standard)

Default Attribute Weight

Use this to determine how attributes are treated, for those attributes that are not in the attribute weights list.

Lowest weight

Highest weight

Highest weight

Disable Prefix in WH Partition Table

Allows you to choose whether or not to use the prefix partition queries. The prefix is always used with pre-queries.

(Default) Use prefix in both warehouse partition pre-query and partition query

Use prefix in warehouse partition prequery but not in partition query

(Default) Use prefix in both warehouse partition pre-query and partition query

Distinct/Group by Option (When No Aggregation and Not Table Key)

If no aggregation is needed and the attribute defined on the table is not a primary key, tells the SQL Engine whether to use Select Distinct, Group by, or neither.

Use DISTINCT

No DISTINCT, no GROUP BY

Use GROUP BY

Use DISTINCT

GROUP BY ID Attribute

Determines how to group by a selected ID column when an expression is performed on the ID expression.

Group by expression

Group by alias

Group by column

Group by position

Group by expression

GROUP BY Non-ID Attribute

Determines how to handle columns for non_ID attributes.

Use Max

Use Group By

Use Max

Insert Post String

Determines the string that is inserted at the end of insert and implicit table creation statements.

User-defined

NULL

Insert Table Option

Determines the string inserted after table name in insert statements; analogous to table option.

User-defined

NULL

Long Integer Support

Determines whether to map long integers of a certain length as BigInt data types when MicroStrategy creates tables in a database.

Do not use BigInt

Up to 18 digits

Up to 19 digits

Do not use BigInt

Max Digits in Constant

Sets the maximum number of digits in a constant literal in an insert values statement. (0 = no limit).

User-defined

No limit

Merge Same Metric Expression Option

Determines how to handle metrics that have the same definition.

Merge same metric expression

Do not merge same metric expression

Merge same metric expression

Select Post String

Defines the custom SQL string to be appended to all SELECT statements, for example, FOR FETCH ONLY.

User-defined

NULL

Select Statement Post String

Defines the custom SQL string to be appended to the final SELECT.

User-defined

NULL

SQL Hint

This string is placed after the SELECT statement.

User-defined

NULL

SQL Time Format

Determines the format of the time literal accepted in SQL statements.

User-defined

yyyy-mm-dd hh:nn:ss

Timestamp Format

Sets the format of the timestamp literal accepted in the Where clause.

User-defined

yyyy-nn-dd hh:mm:ss

UNION Multiple INSERT

Allows the Analytical Engine to UNION multiple insert statements into the same temporary table.

Do not use UNION

Use UNION

Do not use UNION

Use Column Type Hint for Parameterized Query

Determines whether the WCHAR data type is used as applicable to return data accurately while using parameterized queries.

Disabled

Enable ODBC Column Type Binding Hint for "WCHAR" and "CHAR"

Disabled