KB426353 : How to expose and change the Table Pattern and Advanced Table Pattern VLDB settings in MicroStrategy 2021.

Senior Cloud Support Engineer III • MicroStrategy

To have the table names in the SQL generated by the MicroStrategy SQL Engine 2021 enclosed with square brackets or some other character, For example, in Microsoft SQL Server, table names with spaces or numbers must be enclosed in square brackets.

To achieve this functionality in Strategy, the following VLDB setting "Advanced Table Pattern" must be exposed so that users can make the changes to the pattern of the generated SQL as desired.

Locate the VLDB.xml file in the "Program Files (x86)\Common Files\Strategy" folder on a machine with Strategy Developer installed and create a backup of this file, and then edit it.

Locate the last <Setting> XML entry in the file similar to the following entries:


<Setting>
      <Name>MDX Non Empty Optimization</Name>
      <MaxValues>4</MaxValues>
      <GroupID>10</GroupID>
      <Type>4</Type>
      <DataSourceType>4</DataSourceType>
      <ReportType>3</ReportType>
       <DisplayName>MDX Non Empty Optimization</DisplayName>
      <DisplayLevel>2</DisplayLevel>
      <ShowSQLPreview>0</ShowSQLPreview>
      <SQLPreview1/>
      <ValueOption1>22375</ValueOption1>
      <SQLPreview2/>
      <ValueOption2>22376</ValueOption2>
      <SQLPreview3/>
      <ValueOption3>22377</ValueOption3>
      <SQLPreview4/>
      <ValueOption4>22378</ValueOption4>
    </Setting>    
   <HTML>
      <body bgcolor="#FFFFFF">
         <font color="#0000FF" face = "Times Roman" size="3">
            <SettingName>Report PreStatement 1</SettingName>
         </font>

After the last closing tag for the settings (</Setting>), insert the following text:


<Setting>
      <Name>Advanced Table Pattern</Name>
      <MaxValues>1</MaxValues>
      <GroupID>5</GroupID>
      <Type>0</Type>
      <DataSourceType>25</DataSourceType>
      <DisplayName>Advanced Table Pattern</DisplayName>
      <DisplayLevel>2</DisplayLevel>
      <ShowSQLPreview>0</ShowSQLPreview>
      <SQLPreview1/>
   </Setting>

The contents of the file appear similar to the following:


  <Setting>
      <Name>MDX Non Empty Optimization</Name>
      <MaxValues>4</MaxValues>
      <GroupID>10</GroupID>
      <Type>4</Type>
      <DataSourceType>4</DataSourceType>
      <ReportType>3</ReportType>
       <DisplayName>MDX Non Empty Optimization</DisplayName>
      <DisplayLevel>2</DisplayLevel>
      <ShowSQLPreview>0</ShowSQLPreview>
      <SQLPreview1/>
      <ValueOption1>22375</ValueOption1>
      <SQLPreview2/>
      <ValueOption2>22376</ValueOption2>
      <SQLPreview3/>
      <ValueOption3>22377</ValueOption3>
      <SQLPreview4/>
      <ValueOption4>22378</ValueOption4>
    </Setting>   
   <Setting>
      <Name>Advanced Table Pattern</Name>
      <MaxValues>1</MaxValues>
      <GroupID>5</GroupID>
      <Type>0</Type>
      <DataSourceType>25</DataSourceType>
      <DisplayName>Advanced Table Pattern</DisplayName>
      <DisplayLevel>2</DisplayLevel>
      <ShowSQLPreview>0</ShowSQLPreview>
      <SQLPreview1/>
   </Setting>  
   <HTML>
      <body bgcolor="#FFFFFF">
         <font color="#0000FF" face = "Times Roman" size="3">
            <SettingName>Report PreStatement 1</SettingName> 
         </font>

Once the changes are made, the VLDB.xml file should be saved and closed.
Restart Developer.
Re-connect to the MD and if the project level VLDB settings for the database instance are edited, the new "Advanced Table Pattern" VLDB setting should appear as shown below. This setting has a default empty value, and to place square brackets around the table name, the setting should be set to as shown below:

After changing the VLDB Setting, the user may need to restart the Intelligence Server or reload the project to make the changes take effect.

NOTE:
This setting interacts with the "Space in Tablename Pattern" VLDB setting (also hidden) and may not need to be applied in all cases as this syntax is automatically handled for some databases. Also the previous setting "Table Name Pattern" applies to the table names used in Temporary tables and may be modified in the same manner as above.

Metadata Modification WARNING
Manually editing values in the Strategy Metadata incorrectly may cause serious, project-wide problems that may make your project unusable. Since these are user-initiated changes, they are not covered by any Strategy warranty. Users are strongly encouraged to backup Metadata prior to any alteration.

Comment

0 comments

Details

Knowledge Article

Published:

June 27, 2017

Last Updated:

July 31, 2024

<Setting> <Name>Advanced Table Pattern</Name> <MaxValues>1</MaxValues> <GroupID>5</GroupID> <Type>0</Type> <DataSourceType>25</DataSourceType> <DisplayName>Advanced Table Pattern</DisplayName> <DisplayLevel>2</DisplayLevel> <ShowSQLPreview>0</ShowSQLPreview> <SQLPreview1/> </Setting>

<Setting> <Name>MDX Non Empty Optimization</Name> <MaxValues>4</MaxValues> <GroupID>10</GroupID> <Type>4</Type> <DataSourceType>4</DataSourceType> <ReportType>3</ReportType> <DisplayName>MDX Non Empty Optimization</DisplayName> <DisplayLevel>2</DisplayLevel> <ShowSQLPreview>0</ShowSQLPreview> <SQLPreview1/> <ValueOption1>22375</ValueOption1> <SQLPreview2/> <ValueOption2>22376</ValueOption2> <SQLPreview3/> <ValueOption3>22377</ValueOption3> <SQLPreview4/> <ValueOption4>22378</ValueOption4> </Setting> <Setting> <Name>Advanced Table Pattern</Name> <MaxValues>1</MaxValues> <GroupID>5</GroupID> <Type>0</Type> <DataSourceType>25</DataSourceType> <DisplayName>Advanced Table Pattern</DisplayName> <DisplayLevel>2</DisplayLevel> <ShowSQLPreview>0</ShowSQLPreview> <SQLPreview1/> </Setting> <HTML> <body bgcolor="#FFFFFF"> <font color="#0000FF" face = "Times Roman" size="3"> <SettingName>Report PreStatement 1</SettingName> </font>