KB16423: What do the different branches in the SQL view mean in MicroStrategy SQL Generation Engine?

• Strategy

This article describes what the different branches in the SQL view mean in MicroStrategy SQL Generation Engine.

In some cases, the SQL view for certain reports shows different branches. This happens only when reports hit partitioned base tables (PBTs). When users switch from the editor mode to the SQL view mode without executing the report first, the Strategy SQL Generation Engine 8.x does not know yet what the SQL will look like. Thus, three distinct possible cases are shown. Each one of these possible scenarios is a branch. For more information about how to set up a project using partitioned tables, refer to the following Strategy Knowledge Base technical note:
KB5529: How to set up a project using partition mapping tables in MicroStrategy Architect 9.4.x - 10.x.

The Strategy Tutorial project is used to explain this behavior and the sample report SQL:

Metric:
M1 = Sum() {~+}
Report:

Filter:
Year = 2006
The SQL generated by this report (before running) is the following:


select distinct a11. AS PBTNAME
from  a11, 
          a12
where a11. = a12.
and a12. in (2006)

The first pass is a pre-query. The pre-query is in charge of determining which tables will answer the report. In this case, the pre-query goes against the table, which contains the and the columns, the first one holds the quarter number and the second one, the partitioned table that contains the data for that quarter.

When the report is shown in the SQL view only, no SQL is executed, so the Strategy SQL Generation Engine 8.x does not know yet what tables will be hit.




create table ZZPO00 (
 MONTH_ID LONG, 
 CATEGORY_ID BYTE, 
 WJXBFS1 DOUBLE)

The first branch shows the possible SQL that will be executed in case that the pre-query returns no data. This means that the filter on the pre-query did not meet the partition requirements for any of the partitioned base tables.


  

insert into ZZPO00 
select a11. AS MONTH_ID,
 a13. AS CATEGORY_ID,
 sum(a11.) AS WJXBFS1
from  a11, 
  a12, 
  a13, 
  a14
where a11. = a12. and 
 a12. = a13. and 
 a11. = a14.
 and a14. in (2006)
group by a11.,
 a13.

The second branch shows the possible SQL that will be executed in case that the pre-query returns only one table. In this case, there is no need for a temporary table and the Strategy SQL Generation Engine 8.x only selects the data from the partitioned table that meets the criteria. Since no SQL has been run, the Strategy SQL Generation Engine 8.x shows { Partition_Base_Table } because it does not know what table it is yet.


 

create table ZZPO00 (
 MONTH_ID LONG, 
 CATEGORY_ID BYTE, 
 WJXBFS1 DOUBLE)
insert into ZZPO00 
select a11. AS MONTH_ID,
 a13. AS CATEGORY_ID,
 sum(a11.) AS WJXBFS1
from  a11, 
  a12, 
  a13, 
  a14
where a11. = a12. and 
 a12. = a13. and 
 a11. = a14.
 and a14. in (2006)
group by a11.,
 a13.



create table ZZMB01 (
       CATEGORY_ID BYTE, 
 WJXBFS1 LONG)
insert into ZZMB01 
select pc11. AS CATEGORY_ID,
 max(pc11.) AS WJXBFS1
from  pc11
group by pc11.

The third branch shows the possible SQL that will be executed in case that the pre-query returns more than one table. In this case, a temporary table will be created, several passes of SQL will then insert into the temp tables.


select distinct pa11. AS CATEGORY_ID,
 a13. AS CATEGORY_DESC,
 pa11. AS WJXBFS1
from  pa11, 
  pa12, 
  a13
where pa11. = pa12. and 
 pa11. = pa12. and 
 pa11. = a13.
drop table ZZPO00

A consolidation pass at the end, will re-aggregate the data in the temporary table.

Now, if the report is run, the following SQL will be shown when going back to the SQL view:


select distinct a11. AS PBTNAME
from  a11, 
  a12
where a11. = a12.
 and a12. in (2006)

The pre-query is the same as before running the SQL but now it is run. At this point the Strategy SQL Generation Engine 8.x decides that there is more than one table needed to return the desired results, so it will follow Branch 3 algorithm and create a temporary table with the real name.


create table ZZTHC02008VPO000 (
 MONTH_ID LONG, 
 CATEGORY_ID BYTE, 
 WJXBFS1 DOUBLE)

The queries go against all the tables returned by the pre-query. In this example, tables returned by the query above are: INVENTORY_Q1_2006, INVENTORY_Q2_2006, INVENTORY_Q3_2006, INVENTORY_Q4_2006.


insert into ZZTHC02008VPO000 
select a11. AS MONTH_ID,
 a13. AS CATEGORY_ID,
 sum(a11.) AS WJXBFS1
from  a11, 
  a12, 
  a13, 
  a14
where a11. = a12. and 
 a12. = a13. and 
 a11. = a14.
 and a14. in (2006)
group by a11.,
 a13.

insert into ZZTHC02008VPO000 
select a11. AS MONTH_ID,
 a13. AS CATEGORY_ID,
 sum(a11.) AS WJXBFS1
from  a11, 
  a12, 
  a13, 
  a14
where a11. = a12. and 
 a12. = a13. and 
 a11. = a14.
 and a14. in (2006)
group by a11.,
 a13.

insert into ZZTHC02008VPO000 
select a11. AS MONTH_ID,
 a13. AS CATEGORY_ID,
 sum(a11.) AS WJXBFS1
from  a11, 
  a12, 
  a13, 
  a14
where a11. = a12. and 
 a12. = a13. and 
 a11. = a14.
 and a14. in (2006)
group by a11.,
 a13.

insert into ZZTHC02008VPO000 
select a11. AS MONTH_ID,
 a13. AS CATEGORY_ID,
 sum(a11.) AS WJXBFS1
from  a11, 
  a12, 
  a13, 
  a14
where a11. = a12. and 
 a12. = a13. and 
 a11. = a14.
 and a14. in (2006)
group by a11.,
 a13.

create table ZZTHC02008VMB001 (
 CATEGORY_ID BYTE, 
 WJXBFS1 LONG)

insert into ZZTHC02008VMB001 
select pc11. AS CATEGORY_ID,
 max(pc11.) AS WJXBFS1
from  pc11
group by pc11.

The results from all of the temp tables are consolidated and join to the lookup tables are generated at the final pass.


select distinct pa11. AS CATEGORY_ID,
 a13. AS CATEGORY_DESC,
 pa11. AS WJXBFS1
from  pa11, 
  pa12, 
  a13
where pa11. = pa12. and 
 pa11. = pa12. and 
 pa11. = a13.

drop table ZZTHC02008VPO000
drop table ZZTHC02008VMB001

Comment

0 comments

Details

Knowledge Article

Published:

June 12, 2017

Last Updated:

June 12, 2017

insert into ZZPO00 select a11. AS MONTH_ID, a13. AS CATEGORY_ID, sum(a11.) AS WJXBFS1 from a11, a12, a13, a14 where a11. = a12. and a12. = a13. and a11. = a14. and a14. in (2006) group by a11., a13.

create table ZZPO00 ( MONTH_ID LONG, CATEGORY_ID BYTE, WJXBFS1 DOUBLE) insert into ZZPO00 select a11. AS MONTH_ID, a13. AS CATEGORY_ID, sum(a11.) AS WJXBFS1 from a11, a12, a13, a14 where a11. = a12. and a12. = a13. and a11. = a14. and a14. in (2006) group by a11., a13. create table ZZMB01 ( CATEGORY_ID BYTE, WJXBFS1 LONG) insert into ZZMB01 select pc11. AS CATEGORY_ID, max(pc11.) AS WJXBFS1 from pc11 group by pc11.

insert into ZZTHC02008VPO000 select a11. AS MONTH_ID, a13. AS CATEGORY_ID, sum(a11.) AS WJXBFS1 from a11, a12, a13, a14 where a11. = a12. and a12. = a13. and a11. = a14. and a14. in (2006) group by a11., a13. insert into ZZTHC02008VPO000 select a11. AS MONTH_ID, a13. AS CATEGORY_ID, sum(a11.) AS WJXBFS1 from a11, a12, a13, a14 where a11. = a12. and a12. = a13. and a11. = a14. and a14. in (2006) group by a11., a13. insert into ZZTHC02008VPO000 select a11. AS MONTH_ID, a13. AS CATEGORY_ID, sum(a11.) AS WJXBFS1 from a11, a12, a13, a14 where a11. = a12. and a12. = a13. and a11. = a14. and a14. in (2006) group by a11., a13. insert into ZZTHC02008VPO000 select a11. AS MONTH_ID, a13. AS CATEGORY_ID, sum(a11.) AS WJXBFS1 from a11, a12, a13, a14 where a11. = a12. and a12. = a13. and a11. = a14. and a14. in (2006) group by a11., a13. create table ZZTHC02008VMB001 ( CATEGORY_ID BYTE, WJXBFS1 LONG) insert into ZZTHC02008VMB001 select pc11. AS CATEGORY_ID, max(pc11.) AS WJXBFS1 from pc11 group by pc11.

select distinct pa11. AS CATEGORY_ID, a13. AS CATEGORY_DESC, pa11. AS WJXBFS1 from pa11, pa12, a13 where pa11. = pa12. and pa11. = pa12. and pa11. = a13. drop table ZZTHC02008VPO000 drop table ZZTHC02008VMB001