Uso de “IF” en expresiones vs uso de “Set Analysis”

En este articulo voy a tratar de aclarar todas las diferencias y similitudes que se presentan cuando en una expresión podemos usar tanto sentencias del tipo IF o su “equivalente” SET ANALYSIS, y relativizo esta supuesta equivalencia ya que en algunos casos se obtiene (casi) el mismo resultado y en otros no, para lo cual es necesario entender el funcionamiento de cada mecanismo para saber cual debe aplicarse en cada escenario.

En principio voy a decir que:

–         Al hacer un SET ANALYSIS se restringe el conjunto de datos sobre el que se va a operar, y es el equivalente programático exacto de una selección de usuario. Cuando se ejecuta la expresión a la derecha del set ({<…>} xxxxx) esta se aplica sobre el conjunto resultante del modelo.

–         Por otro lado y complementario a lo anteriormente dicho una expresión que no tiene set analysis se ejecuta sobre todos los datos disponibles (según las selecciones y dimensiones vigentes).

Sea por un camino o por otro cuando QV ejecuta una expresión internamente arma una tabla “local” (para resolver la expresión) como resultado de hacer un natural join entre todos los campos usados en dicha expresión, y esto como en cualquier join puede producir que algunas filas de alguna de las tablas joineadas se dupliquen (o multipliquen) produciendo en algunas ocasiones efectos indeseados.

Por ejemplo:

Supongamos que tenemos un modelo con dos tablas: la primera con un detalle de las ventas anuales de clientes y otra con los reclamos de cada cliente y el monto reclamado. De aquí podremos obtener varios indicadores como: Cantidad de clientes con reclamos, Monto de facturación anual vs monto reclamado (riesgo de pérdida), Monto total de los reclamos, cantidad de reclamos, varios promedios y porcentuales, etc.

Aquí la estructura de las tablas:

Customers:
load * inline [
CustID, Anual_Sales, VIP
1 , 100, Y
2 , 200, Y
3 , 300, Y
4 , 400, N
];

Claims:
Load * inline [
CustID, ClaimID, Claimed_Amount
1, 1, 5
1, 2, 8
4, 3, 15
4, 4, 1
];

Y obtenemos:

Customers:

                     Image20

 Claims:

                  Image21

tercera

Ahora veamos distintos indicadores y las formas correctas de obtenerlos y otros modos erroneos que debemos evitar:

1-      Monto reclamado por los clientes VIP:

          a- Empleando Set Analysis (resultado CORRECTO):

cuarta

                Aquí QV primero “filtra” del modelo solo aquellas filas de la tabla Customers donde VIP=”Y”, obteniendo internamente algo como:

                Tabla Customers ——->     quinta

               Y por las relaciones del modelo:

               Tabla Claims——->  sexta  

               Finalmente suma Claimed_Amount obteniendo 13.

          b- Empleando IF (resultado CORRECTO):

septima

                Aquí QV joinea ambas tablas (haciendo un Natural Join) lo que arroja algo como:

octava

              Se puede observar que QV obtiene el resultado 13 en forma correcta y solo muestra donde Claimed_Amount es distinto de nulo, lo que podríamos cambiar mediante el check “Supress zero values” en el tab “Presentation”, obteniendo:

novena

             Esto sucede porque lo que estamos sumando se encuentra en el “extremo derecho” del join, es decir: la tabla Claims mantuvo la misma cantidad de filas que antes del join.

             En este primer caso (1) usar IF o Set Analysis es, al menos en principio y a solo efecto del resultado, idéntico, aunque luego veremos algunas sutiles diferencias.

2-      Facturación Anual comprometida de los clientes: se busca mostrar “cuanto se podría perder si no se resuelven los reclamos”

           a- Empleando Set Analysis (resultado CORRECTO):

              Se filtra el modelo buscando las filas que tienen cualquier valor (‘*’) en ClaimID.

Image12             Al realizar esto QV a través de la relación existente en el modelo filtra la tabla Customers obteniendo lo siguiente:

 Image11             Mostrando el resultado correctamente:

 Image12  

         b-Empleando IF (resultado INCORRECTO):

            Aquí QV primero hace un Natural Join entre Customers y Claims, y esto sucede porque en la expresión empleada se usan campos de esas dos tablas (esto “fuerza” el join entre las tablas).

Image13

           Luego sobre el “result set” obtenido QV recorre fila por fila buscando aquellas que cumplen con el criterio (que tenga al menos un reclamo) y agrupando según la dimensión correspondiente, arrojando el siguiente resultado ERRONEO:

image013

           Esto se debe a que los valores 100 y 400 figuran 2 veces cada uno en la tabla joineada.

           Existe una situación en el que un cálculo similar a este podría funcionar y consiste en si (por ej) estamos contando Identificadores únicos. Por ejemplo para contar la cantidad de clientes que tienen reclamos podríamos usar la expresión:

Count(DISTINCT if(not isnull(ClaimID),Cust_ID))

           Donde aunque Cust_ID se “multiplica” tantas veces como reclamos hay el modificador DISTINCT realiza la magia requerida.

           Aunque vale aclarar que QV desaconseja hacer count’s distinct por razones de performance.

        c- Empleando IF y AGGR (Resultado CORRECTO):

           Existe la posibilidad de no usar Set Analysis y aun así obtener un resultado correcto mediante el uso de AGGR.

            La instrucción AGGR construye una “tabla temporal” en memoria agrupando por algún campo del modelo y obteniendo un UNICO resultado para cada valor del campo agrupado (es como un Select agregacionFuncion() from… GROUP By … pero en runtime).

            El resultado que explico más abajo sería:

    Image15           Aquí se combinan campos de las dos tablas pero internamente hay un paso más que permite un resultado correcto a diferencia de la alternativa explicada en 2b.

           En primer lugar para cada CustID diferente QV cuenta la cantidad de valores que hay en ClaimID, obteniendo una tabla temporal e intermedia como esta:

     Image16            Así QV obtiene 1 y solo 1 línea por cada cliente que tiene al menos 1 reclamo.

            Es realmente esta tabla la que QV emplea para Joinear con Customers (campo Anual_Sales) en la expresión:

sum(if(aggr(count(ClaimID),CustID)>=1 , Anual_Sales))

             De tal modo que no se “multiplican” las filas de la tabla padre (Customers) al joinear con la de detalle (Claims) (relaciòn 1 a 1) y así se suman importes UNICOS.

         d- Desde el script usando Group by y Flags (Resultado CORRECTO):

             Como ya vimos el problema que debemos evitar es que se “multipliquen” las filas de la tabla donde se encuentra el campo que deseamos sumar al hacer joins con otras tablas del modelo. De un modo muy similar al AGGR podemos “marcar” los clientes que tienen reclamos en tiempo de corrida del script y luego simplemente sumar las facturaciones anuales de estos clientes sin tener que joinear con ninguna otra tabla. Esto se hace creando un campo nuevo en la tabla “padre” (Customers) que funciona como un flag: en nuestro script agregamos:

left join(Customers)
LOAD CustID,
if(isnull(count(ClaimID)),’N’,’Y’) as Commited
RESIDENT Claims
GROUP BY CustID;

y luego solo debemos sumar las facturaciones de los clientes “comprometidos” (Commited):

 Image17              Cuando existe la posibilidad de emplear tanto IF como Set Analysis  QV aconseja el empleo de Set Analysis por resultar mas performante, aunque suele ser aun mas performante la resolución vía script, especialmente si hay un volumen de datos importante y/o un modelo complejo. El uso de Aggr en este tipo de modelos puede resultar muy costoso en memoria y cpu, especialmente si no se tiene control sobre otros filtros que pudiera estar realizando el usuario.

             Una última consideración acerca del uso de Set Analysis: en algunas circunstancias y muy ligado al diseño del tablero y los casos de uso planteados por el usuario, puede resultar algo confuso y generar algo de ruido. Por ejemplo supongamos que tenemos un tablero con una tabla dinámica y un selector así:

Image18         Image19

            En caso que el usuario hubiera seleccionado VIP=”N” nuestra tabla dinámica ignorara dicha selección ya que el Set Analysis (tal como aquí está escrito) ignora la selección de usuario, generando un resultado que podría resultar confuso a un usuario desprevenido. Esto no sucedería cuando se emplea IF, aunque además de tener una pobre performance no siempre se consigue el resultado buscado.

Acerca de pparnisari
Casi 44 vueltas al sol. Licenciado en sistemas. Curioso, investigador, excelente para resolver problemas prácticos. Casado, 2 hijos, cada vez con mas preguntas y respuestas mas en duda. Almost 44 laps to the sun. System engineer. Curious, researcher, great for solving practical problems. Married, 2 children, each time with more questions and more answers in doubt.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: