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 |
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 |
|
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 |
|
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 |
|
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 |
|
Determines whether data is retrieved using third-party, native APIs. |
Only ODBC Allow Native API |
Only ODBC |
|
Defines the parameters used to retrieve data using third-party, native APIs. |
User-defined |
NULL |
|
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 |
|
Sets the format for date in engine-generated SQL. |
User-defined |
YYYY-MM-DD |
|
Lets you define the syntax pattern for Date data. |
User-defined |
NULL |
|
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) |
|
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 |
|
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 |
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 |
|
Determines how to handle columns for non_ID attributes. |
Use Max Use Group By |
Use Max |
|
Determines the string that is inserted at the end of insert and implicit table creation statements. |
User-defined |
NULL |
|
Determines the string inserted after table name in insert statements; analogous to table option. |
User-defined |
NULL |
|
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 |
|
Sets the maximum number of digits in a constant literal in an insert values statement. (0 = no limit). |
User-defined |
No limit |
|
Determines how to handle metrics that have the same definition. |
Merge same metric expression Do not merge same metric expression |
Merge same metric expression |
|
Defines the custom SQL string to be appended to all |
User-defined |
NULL |
|
Defines the custom SQL string to be appended to the final |
User-defined |
NULL |
|
This string is placed after the SELECT statement. |
User-defined |
NULL |
|
Determines the format of the time literal accepted in SQL statements. |
User-defined |
yyyy-mm-dd hh:nn:ss |
|
Sets the format of the timestamp literal accepted in the Where clause. |
User-defined |
yyyy-nn-dd hh:mm:ss |
|
Allows the Analytical Engine to UNION multiple insert statements into the same temporary table. |
Do not use UNION Use UNION |
Do not use UNION |
|
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 |