Performance of Analytical Engine CASE function vs. DBMS

[robpotter]

Question on Performance of Analytical Engine CASE function vs. DBMS CASE using Applysimple

For reporting off large tables that will join with security views, our data mart designs have the majority of transformations as pre-calculated fields that arestored in the fact tables. However, a few attribute groupings need to be handled within MSTR.

Although I have thought of contingency to add additional pre-calculated fields for these groupings, I am trying to address requirement without going that route. So I am considering options similar to these:

1) A pass through ... Applysimple ( "CASE WHEN LEFT(#0,1) in ('A', 'B','C','D','E','F') THEN 'Group A' WHEN LEFT(0#,1) in 'G','H','I','J','K','L','M')THEN 'Group B' ELSE '-' END", [PERSON_DESC] )

2) Equivalent Analytical Engine using internal function CASE.

While I believe the DB probably would have equal or better performance in evaluating the attribute in Select clause, I'm not sure about whether it is better is Group By.

Does anyone have advice regarding handling non-complex attribute transformations within MSTR, aka simple attribute groupings? I welcome any comments or suggestions.

Thanks. Rob

 

[entaroadun]

For certain RDBMSs, MSTR converts CASE functions into CASE statements in SQL, so there may be no difference.

In instances that the analytical engine is doing the work, it will almost certainly be slower just because the data has to be brought from the DB to the IServer and then inserted back into a temp table on the DB.

The only way you could get a CASE into a group by is by putting a CASE statement into an attribute form expression. The analytical engine won't do group bys.

Also, you may want to think about converting your WHEN clause to:

WHEN #0 < 'G'

which will get all F-words (no pun intended). This should us slightly fewer CPU cycles than the LEFt function and in-list match.

 

[robpotter]

Thanks very much, entaroadun

I didn't know that Analytical Engine didn't perform the Group by on attributes. Since aggregating by groups is requirment, I'll stick with the Applysimple. Of course, in performance testing, we may determine that all encoding should be pre-calculated (even simple ones) and then add such additional fields representing groups to the fact table.

We hadn't reviewed the SQL yet, but I'll definitedly go ahead with optimization of all CASE statements.

Robert