Customizing SQL Statements: Pre/Post Statements

The table below summarizes the Pre/Post Statements VLDB properties. Additional details about each property, including examples and a list of wild cards, are available by clicking on the links in the table.

Property Description Possible Values Default Value

Cleanup Post Statement

Appends string after final drop statement.

User-defined

NULL

Data Mart SQL to be Executed After Data Mart Creation

SQL statements included after the CREATE statement used to create the data mart.

User-defined

NULL

Data Mart SQL to be Executed Before Inserting Data

SQL statements included before the INSERT statement used to insert data into the data mart.

User-defined

NULL

Data Mart SQL to be Executed Prior to Data Mart Creation

SQL statements included before the CREATE statement used to create the data mart.

User-defined

NULL

Drop Database Connection

Defines whether the database connection is dropped after user-defined SQL is executed on the database.

Drop database connection after running user-defined SQL
Do not drop database connection after running user-defined SQL

Drop database connection after running user-defined SQL

Element Browsing Post Statement

SQL statements issued after element browsing requests.

User-defined

NULL

Element Browsing Pre Statement

SQL statements issued before element browsing requests.

User-defined

NULL

Insert Mid Statement 1-5

SQL statements issued between multiple insert statements. For the first four statements, each contains single SQL. The last statement can contain multiple SQL statements concatenated by ";".

User-defined

NULL

Insert Post Statement 1-5

SQL statements issued after create, after first insert only for explicit temp table creation. For the first four statements, each contains single SQL. The last statement can contain multiple SQL statements concatenated by ";".

User-defined

NULL

Insert Pre Statement 1-5

SQL statements issued after create before first insert only for explicit temp table creation. For the first four statements, each contains single SQL. The last statement can contain multiple SQL statements concatenated by ";".

User-defined

NULL

Report Post Statement 1-5

SQL statements issued after report requests. For the first four statements, each contains single SQL. The last statement can contain multiple SQL statements concatenated by ";".

User-defined

NULL

Report Pre Statement 1-5

SQL statements issued before report requests. For the first four statements, each contains single SQL. The last statement can contain multiple SQL statements concatenated by ";".

User-defined

NULL

Table Post Statement 1-5

SQL statements issued after creating new table and insert. For the first four statements, each contains single SQL. The last statement can contain multiple SQL statements concatenated by ";".

User-defined

NULL

Table Pre Statement 1-5

SQL statements issued before creating new table. For the first four statements, each contains single SQL. The last statement can contain multiple SQL statements concatenated by ";".

User-defined

NULL

You can insert the following syntax into strings to populate dynamic information by the SQL Engine:

  • !!! inserts column names, separated by commas (can be used in Table Pre/Post and Insert Pre/Mid statements).
  • !! inserts an exclamation (!) (can be used in Table Pre/Post and Insert Pre/Mid statements). Note that "!!=" inserts a not equal to sign in the SQL statement.
  • ??? inserts the table name (can be used in Data Mart Insert/Pre/Post statements, Insert Pre/Post, and Table Post statements).
  • ;; inserts a semicolon (;) in Statement5 (can be used in all Pre/Post statements). Note that a single ";" (semicolon) acts as a separator.
  • !a inserts column names for attributes only (can be used in Table Pre/Post and Insert Pre/Mid statements).
  • !d inserts the date (can be used in all Pre/Post statements).
  • !f inserts the report path (can be used in all Pre/Post statements except Element Browsing). An example is: \MicroStrategy Tutorial\Public Objects\Reports\MicroStrategy Platform Capabilities\Ad hoc Reporting\Sorting\Yearly Sales
  • !i inserts the job priority of the report which is represented as an integer from 0 to 999 (can be used in all Pre/Post statements).
  • !o inserts the report name (can be used in all Pre/Post statements).
  • !u inserts the user name (can be used in all Pre/Post statements).
  • !j inserts the Intelligence Server Job ID associated with the report execution (can be used in all Pre/Post statements).
  • !r inserts the report GUID, the unique identifier for the report object that is also available in the Enterprise Manager application (can be used in all Pre/Post statements).
  • !t inserts a timestamp (can be used in all Pre/Post statements).
  • !p inserts the project name with spaces omitted (can be used in all Pre/Post statements).
  • !z inserts the project GUID, the unique identifier for the project (can be used in all Pre/Post statements).
  • !s inserts the user session GUID, the unique identifier for the user's session that is also available in the Enterprise Manager application (can be used in all Pre/Post statements).
  • The # character is a special token that is used in various patterns and is treated differently than other characters. One single # is absorbed and two # are reduced to a single #. For example to show three # characters in a statement, enter six # characters in the code. You can get any desired string with the right number of # characters. Using the # character is the same as using the ; character.

The table below shows the location of some of the most important VLDB/DSS settings in a Structured Query Language (SQL) query structure. If the properties in the table are set, the values replace the corresponding tag in the query:

Tag VLDB properties (MSTR 7.x)

<1>

Report PreStatement (1-5)

<2>

Table PreStatement (1-5)

<3>

Table Qualifier

<4>

Table Descriptor

<5>

Table Prefix

<6>

Table Option

<7>

Table Space

<8>

Create PostString

<9>

Pre DDL COMMIT

<10>

Insert PreStatement (1-5)

<11>

Insert Table Option

<12>

SQL Hint

<13>

Post DDL COMMIT

<14>

Insert PostString

<15>

Insert MidStatement (1-5)

<16>

Table PostStatement (1-5)

<17>

Index Qualifier

<18>

Index PostString

<19>

Select PostString

<20>

Report PostStatement (1-5)

<21>

Commit after Final Drop

<22>

Cleanup PostStatement

Query Structure

<1> 
     <2>
          CREATE <3> TABLE <4> <5><table name> <6>
          (<fields' definition>)
          <7>
          <8>
          <9>(COMMIT)
     <10>
          INSERT INTO <5><table name><11>
          SELECT <12> <fields list>
          FROM <tables list>
          WHERE <joins and filter>
          <13>(COMMIT)
     <14>
     <15>
     <16>
          CREATE <17> INDEX <index name> ON 
				<fields list>
     <18>
          SELECT <12> <fields list>
          FROM <tables list>
          WHERE <joins and filter>
     <19>
<20>
          DROP TABLE TABLENAME
<21>
<22>

The Commit after Final Drop property (<21>) is sent to the warehouse even if the SQL View for the report does not show it.