KB6125: VLDB settings are not applied to the datamart SQL passes in MicroStrategy

SYMPTOM:
Users may have changed the Very Large database (VLDB) settings on the database instance project or report level for a datamart. When they run the datamart, the SQL passes of the datamart do not inherit that setting.
 
CAUSE:
 
The Strategy SQL Generation Engine distinguishes between the SQL passes generated for the Report (to return the data for the result set) and the passes generated for the datamart (to create and populate the datamart table) when applying the VLDB settings.
 
Assuming that users have two database instances, one for the warehouse and one for datamarts.
 
Refer to Strategy Knowledge Base document below for more details of Datamarts.
KB5395: How to create and use Datamarts in Strategy Intelligence Server

• If users modify the VLDB settings of the warehouse database instance, only the report passes will be affected but not the datamart passes.
• If users modify the VLDB settings of the datamart database instance, only the datamart passes will be affected.
• If users modify the VLDB setting on the report level, only the report passes will be affected because the report level is a grandchild of the warehouse database instance.

 
EXAMPLE:
 
In Teradata V2R3, the Strategy SQL Generation Engine generates a primary index when a table is created. By default, all the attribute columns in that table are included in the index as shown below:
 
Report SQL pass:
…
create table msr_t.ZZT2C04007JSP001 , NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL (
  week_id DECIMAL(6, 0),
  ibw_cat_id DECIMAL(2, 0),
  ibw_store_id DECIMAL(4, 0),
  WEVKNETTO FLOAT,
  WJXBFS1 FLOAT,
  WJXBFS2 FLOAT,
  WEVKNETTONAA FLOAT,
  WJXBFS3 FLOAT,
  WJXBFS4 FLOAT,
  WJXBFS5 FLOAT,
  WJXBFS6 FLOAT)
primary index (week_id, ibw_cat_id, ibw_store_id)
…
 
Datamart SQL pass:
…
create table msr_d.dm_abschrift_wo (
  week_id DECIMAL(6, 0),
  WEEK_DESC VARCHAR(33),
  DISTRI_WAY_CD DECIMAL(4, 0),
  DISTRI_WAY_DESC VARCHAR(50),
  reg_sales_id DECIMAL(4, 0),
  reg_sales_desc VARCHAR(50),
  reg_district_id DECIMAL(4, 0),
  reg_district_desc VARCHAR(50),
  ibw_store_id DECIMAL(4, 0),
  ibw_store_sdesc VARCHAR(50),
  ibw_sales_main_domain_id DECIMAL(3, 0),
  ibw_sales_main_domain_desc VARCHAR(50),
  ibw_sales_upper_domain_id DECIMAL(3, 0),
  ibw_sales_upper_domain_desc VARCHAR(50),
  ibw_sales_domain_id DECIMAL(3, 0),
  ibw_sales_domain_desc VARCHAR(50),
  ibw_cat_id DECIMAL(2, 0),
  IBW_CAT_DESC VARCHAR(50),
  Panel1 DECIMAL(3, 0),
  Panel2 DECIMAL(3, 0),
  Panel3 DECIMAL(3, 0),
  Panel4 DECIMAL(3, 0),
  WeVKn FLOAT,
  WeWre FLOAT,
  WeVKnNaa FLOAT,
  WeWreNaa FLOAT,
  PvWeAuMaVKn FLOAT,
  PvWeAbMaVKn FLOAT,
  PvWeAuKoVKn FLOAT,
  PvWeAbKoVKn FLOAT,
  PvWeAuWVKn FLOAT,
  PvWeAbWVKn FLOAT,
  BvWeAbBVVKn FLOAT,
  PvBvWeVKn FLOAT)
primary index (week_id, WEEK_DESC, DISTRI_WAY_CD, DISTRI_WAY_DESC, reg_sales_id, reg_ sales_desc, reg_district_id, reg_district_desc, ibw_store_id, ibw_store_sdesc, ibw_sales_main_domain_id, ibw_sales_main_domain_desc, ibw_sales_upper_domain_id, ibw_sales_upper_domain_desc, ibw_sales_domain_id, ibw_sales_domain_desc, ibw_cat_id, IBW_CAT_DESC)
…
 
Users can limit the indices by changing the VLDB setting 'Max Columns in Index'.

 
If users change this setting at the report level, it will only affect the SQL of the report passes and not the datamart passes for the reasons mentioned above.
 
The same happens if users change the VLDB setting at the warehouse database instance level AND the datamart uses a database instance different from the warehouse database instance.
 
Given the example above the SQL returned will be the following:
… create table msr_t.ZZT2C04007JSP001 , NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL (
  week_id DECIMAL(6, 0),
  ibw_cat_id DECIMAL(2, 0),
  ibw_store_id DECIMAL(4, 0),
  WEVKNETTO FLOAT,
  WJXBFS1 FLOAT,
  WJXBFS2 FLOAT,
  WEVKNETTONAA FLOAT,
  WJXBFS3 FLOAT,
  WJXBFS4 FLOAT,
  WJXBFS5 FLOAT,
  WJXBFS6 FLOAT)
primary index (week_id)
…
 
The SQL pass for the datamart will remain the same.
 
 
It is possible to have different VLDB settings applied for datamart passes and report passes within the same datamart report. Datamart SQL is only affected by the VLDB settings of the database instance defined in the datamart itself. There is no VLDB setting for datamart passes available on other levels.
 
 
ACTION:
 
This is working by design. If users wish to change the VLDB settings for the datamart, they will have to change the datamart database instance (DBI). This will affect all datamarts using that particular DBI.
 
If users wish to have the datamart passes changed for a certain report or a certain project only, they have to create a new DBI and assign the datamart report to that DBI.