Tuesday, August 21, 2012

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












13 comments:

  1. hey Tenny your example really helped.
    I have a question related to your above example. What if I wanted to add a grouping of all the state attributes called all Sellers Region.Which would be a combination of
    (California, Oregon, Nevada,Atlanta, New York,Florida, North Carolina)

    How would I accomplish this in the apply simple statement in addition to the James sales region, mary sales....etc


    Thanks
    Alex

    ReplyDelete
  2. Hi Alex,
    Usually you would create an aggregate metric like Total on the report itself, not in the data model. In the Data drop down menu, click on Show Totals.

    In any case, the way I approach a problem is, I would first write the sql and get the results I want, then I reverse engineer back into Microstrategy to make it do what I want.

    ReplyDelete
  3. Thanks Tennny,
    My challenge is I need to create some custom groupings of attributes.
    In my report I am using 3 different attributes. I a trying to figure out how apply simple can accomplish this.
    I need to accomplish this in Sql because I would rather not use custom groups, derivative elements, or consolidations.
    some of the attribute elements are in 2 or more groups. For example I need the combination of all.Also, a combination of California, Atalanta, and North Carolina. In addition to the

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


    Thanks,
    Alex

    ReplyDelete
  4. Why don't you try writing out the sql first in your database environment first. Once you are happy with the resultset, we can easily translate your sql back into ApplySimple in microstrategy.

    ReplyDelete
  5. Great article, thanks for sharing usefull information and i have seen more info onMicroStrategy Online Training

    ReplyDelete