Modifying the Microstrategy SQL Generator

Question

I am using Microstrategy, and I am aware of the Freeform SQL Report functionality. However, I'd like to change how Microstrategy generates its SQL.

Since I'm using Infobright, I'd like to use sub-selects instead of full joins, etc. Example:

Query generated by Microstrategy: select b.colb, min(b.cold), sum(a.colb) from tablea a join tableb b on a.id = b.id group by b.colb

I'd like it to generate the following way: select b.colb, min(b.cold), sum(a.colb) from (select id, sum(cold) from tablea a group by colb) join tableb b on a.id = b.id group by b.colb

Is it possible to modify the generator to take this type of approach as opposed to the current approach? Or, must I use the freeform generator?

Answer 1

You can change the way MSTR generates SQL by changing the VLDB properties (at the report level or at the database instance level).

On the VLDB settings, I think you should be looking for the "Derived Table" option, on the "Intermediate Table Type" inside the Tables folder, but have a look at the other options on the Tables folder and also the Query Optimizations folder.

Answer 2

In Report->Go to VLDB Properties->Sub Query Type->select 6 th option->where col 1 in (select s1.col1..) falling back to exists (Select col1, col2...) for multiple columns IN