Custom Group vs Attribute ApplySimple in Microstrategy

You might have heard that creating a custom group in Microstrategy may result in bad database performance. To decide for yourself whether you should or should not use custom group, you must first understand the SQL executed by Microstrategy when  you create a custom group.

Say you want to create a custom group called "Sales Region" based on the State attribute:

1. Tom sales region - California, Oregon, Nevada
2. Mary sales region - Atlanta, New York
3. Jame sales region - Florida, North Carolina

You then create a sales report based on the custom group you created above.

Microstrategy will execute 3 sql passes, 1 for each custom group element you created. It will then union the resultset of the 3 sql passes in the final report. 

sql pass 1 - 

select sum(salesamt)  WJXBFS1

from tbl_FACT a11

join region_DIM a12

 on (a11.region_SID = a12.region_SID)

where a12.state in ('California, 'Oregon', 'Nevada')

sql pass 2 - 

select sum(salesamt)  WJXBFS1

from tbl_FACT a11

join region_DIM a12

  on  (a11.region_SID = a12.region_SID)

where a12.state in ('Atlanta, 'New York')

sql pass 3 - 

select sum(salesamt)  WJXBFS1

from tbl_FACT a11

join region_DIM a12

  on  (a11.region_SID = a12.region_SID)

where a12.state in ('Florida', 'North Carolina')

Say you have 10 custom group elements, guess what? It's going to execute a similar query 10 times, with your attribute filter conditions being the only difference. But if the query executes really fast and you don't mind the many sql calls, custom group can be useful because it's simplifies your development effort.

You can achieve the same output using another method, which is to create an attribute based on an expression involving ApplySimple. Think of it sql case statement. Your attribute expression will look something like this:

ApplySimple("Case When #0 in ('Florida',  North Carolina') then 'James sales region' When #0  in ('Atlanta',  New York') then 'Mary sales region'  When #0 in ('California', 'Oregon', 'Nevada') then  'Tom sales region' end", [state] )

If you then create a report using this attribute, Microstrategy will general a sql statement that involves only 1 sql pass using case statement. The generated SQL looks something like this:

select Case When state in ('Florida',  North Carolina') then 'James sales region'
                         When state in ('Atlanta',  New York ') then ' Mary sales region' 
                         When state in ('California', 'Oregon', 'Nevada') then  'Tom sales region' end  StateCol_2,
sum(salesamt)  WJXBFS1
from tbl_FACT a11
join tbl_DIM a12
 on  (a11.region_SID = a12.region_SID)
group by Case When state in ('Florida',  North Carolina') then 'James sales region'
                         When state in ('Atlanta',  New York ') then ' Mary sales region'
                         When state in ('California', 'Oregon', 'Nevada') then  'Tom sales region' end