Scatter Charts / Maps without extensions 

Few days ago I had to implement a distribution graph showing sales per customer on a GoogleMaps.
ScatterChart

The small bubbles aren’t show

While there are some extensions able to do this no one (at least among free) implements a drag-zoom so that when you zoom in a map area it affect the current selections. On the other hand most of the extensions require at least some changes in its javascript to represent amounts with sizes (grater sales = larger bubble).
Mainly for these two reasons I decided to use a scatter chart to display this information.
Anyone who has used one of these chart previously known that the bubble size often becomes a real problem and the Presentation tab gives few parameters to display the information in a way that meets all the requirements:
1- Larger bubbles should not hide the map nor the smaller adjacent bubbles. This means that increasing the “Symbol Size” it is rarely the right answer.
2- All bubbles must appear, even if in relative percentage of the greatest of all is negligible (eg: some bubbles representing $ 100 million and other $ 1000).
3- The values ​​represent the graph must be real ($ 100 million and $ 1000). Can not display text value other than what is actually sold.
It is understandable that a user wants to see ALL customers on the map and not that being tiny percentages do not appear or are almost invisible. Searching, I found someone asking about the key problem here: how could set the “minimum bubble size” ? Unfortunately the question was not answered, but reviewing the very Qlikvie’s examples I discovered the solution:
To represent a map three expressions are used: the first corresponds to the longitude, the second to the latitude and the third one is the volume or size of the bubble. To set a minimum size while displaying the correct values (as a pop-up) ​​should be used only a “dual” function in this last expression, eg:

dual(‘Sales: ‘ & SUM(SALES),

if(  SUM(SALES) < 0.05 * max(total aggr(sum(SALES),CUSTNMID)) ,

0.05 *  max(total aggr(sum(SALES),CUSTNMID)) ,

SUM(SALES)  )

QlikView will use the text component (first part of the dual) to display when the user hovers the cursor over the bubble, but if sales are less than 5% (0.05) of the sales of the largest customer (“max (total aggr (sum (SALES), CUSTNMID)) “) then it defines the size of the bubble as 5% of the size of the largest bubble, thus achieving a” minimum bubble size. “
Aditionally: If you want the bubbles to even distinguish if overlie assigning them a spherical shape and an accent color (eg: rgb (255,0,255)) by putting it in the first expression (longitude).

Gráficos de distribución / Google Maps (sin extensiones)

Hace algunos días tuve que implementar un gráfico de distribución mostrando las ventas por cliente sobre un mapa de GoogleMaps.

mapa

Las burbujas mas pequeñas no se muestran

Si bien existen algunas extensiones capaces de realizar esto ninguna (al menos entre las gratuitas) implementa un drag-zoom tal que al hacer zoom sobre un área del mapa se termina afectando las selecciones actuales, en mi caso los clientes. Por otro lado la mayoría de las extensiones requieren al menos algunas modificaciones en su javascript para representar importes con tamaños (mayores ventas mayor tamaño de la burbuja).

Principalmente por estos dos motivos decidí emplear un gráfico de distribución (scatter chart) para mostrar la información.

Cualquiera que haya usado uno de estos gráficos anteriormente sabe que el tamaño de las burbujas suele transformarse en un verdadero problema y la solapa de “Presentation” da pocos parámetros para poder mostrar la información de un modo que satisfaga todos los requerimientos:

1- Las burbujas más grandes no deben ocultar el mapa y en lo posible la menor cantidad de burbujas adyacentes menores. Es decir que aumentar el “Symbol Size” casi nunca es la repuesta adecuada.

2- Todas las burbujas deben aparecer, aún si en porcentaje relativo de la mayor de todas es insignificante (p.ej: las ventas a un Hipermercado son de 100 millones, y a un comercio minorista en otro punto del país se le vende solo $10.000 – el 0.01% respecto del hipermercado -).

3- Los valores que represente el gráfico deben ser los reales ($100 millones y $10.000 respectivamente). No se puede mostrar en texto otro valor mas que lo que realmente se vendió.

Es entendible que un usuario quiera ver TODOS sus clientes en el mapa y no que, por ser porcentualmente pequeños, algunos no aparezcan o sean casi invisibles. Buscando encontré alguien que da con el concepto clave preguntando como se puede establecer el “tamaño mínimo de la burbuja”. Lamentablemente la pregunta no había sido respondida, hasta que revisando el mismísimo código de ejemplos que provee QlikView descubrí la solución:

Para representar un mapa se usan 3 expresiones: la primera corresponde a la longitud, la segunda a la latitud y la tercera es el volumen o tamaño de la burbuja. Para fijar un tamaño mínimo y al mismo tiempo mostrar los valores correctos solo se debe emplear la función dual en esta expresión, p.ej:

dual(‘Sales: ‘ & SUM(SALES),

if(  SUM(SALES) < 0.05 * max(total aggr(sum(SALES),CUSTNMID)) ,

0.05 *  max(total aggr(sum(SALES),CUSTNMID)) ,

SUM(SALES)  )

Qlikview va a usar el componente de texto (primera parte de la dual) para mostrar cuando el usuario pase el cursor sobre la burbuja, pero si las ventas son menores que el  5% de las ventas del mayor cliente ( “max(total aggr(sum(SALES),CUSTNMID))” ) entonces definimos que el tamaño de la burbuja será el 5% del tamaño de la mayor burbuja, logrando de este modo un “tamaño mínimo de burbuja”.

Para terminar: Si se quiere que las burbujas se distingan aún si quedan superpuestas sugiero definirles un estilo de forma esférica y asignarles un color destacado (p.ej: rgb(255,0,255)) poniéndolo en la primera expresión, es decir la que define la longitud.

Incremental partitioned reload – Part II

In the first part of this article we saw what it was an incremental load and how it’s done using a technique that meets most of the situations that we can find.

But as I said there are cases where the volume of data being handled is so large (on the order of terabytes) than simply reading and / or recording involved QVD file can take a considerable amount of time and server resources, being that maybe we just need add a few news to it.

To avoid this situation what we should do is to split this file into several fragments which each contain a set of rows grouped by some criteria. One approach that is often used is the month-year of a record’s field, thereby generating QVDs repositories that looks something like this:

                                 Extraction_Customers_CreateDate_012013.QVD
                                 Extraction_Customers_CreateDate_022013.QVD
                                 Extraction_Customers_CreateDate_032013.QVD
                                 Etc….

Where a fixed part of the file name describes the content/source/stage and a variable part describes the period (month-year) corresponding to the records in each file, in this case the customer’s creation Date.

Although we can not always have a date we can always count on a numeric field by which to classify and group records, either the Id field or other unique identifier that can be created using the Autonumber() or Hash…() functions.

The important thing here is that the field you choose must meet the following characteristics:

      – MUST be numeric: to define ranges or segments.
      – NEVER change value over time: in order to locate in what QVD file is stored as a record.
      – Should have a statistical distribution close to random (ie the class frequencies should tend to be equal)
      – As a consequence of this should not have empty ranges: using the example customer’s creation date this means that customers should be created EVERY month in a continuous way.

The last two points are not mandatory, but rather a recommendation for best performance.

In general the same unique identifier used to perform incremental load is an excellent (if not the best) candidate field to partition the QVD, because at the source (transactional) system it is usually numeric, time-invariant (PK), and auto-increment (1 to 1).

To improve readability of the code we will use procedures (Subs) operating similar to a function or routine other languages. Here’s the code of the procedure commented which should be saved in a file called “incrementalPartitionedReload.txt” that must be in the same folder as the qvw on which we are working:
 
sub loadIncrementalPartitioned (V_chunck,V_KEY,V_QVDname,V_SQLquery,V_incrementalWhere,V_fieldsLoaded,V_fieldsTransformations)

     Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]';
    
LET TABLA_SOURCE=’DATA_SOURCE';
    
    
// set the variable used to recover ONLY the news (since last execution)
     LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’);
    
    
// Check if the first full load has been done
     IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD’)) THEN
    
        
//Extract data from datasource
           pre_$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery)
          
$(V_incrementalWhere);
          
          
//for each row calculate the corresponding patition and make transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
           //Fill a little table which will be used to build the affected (if already exist) QVDs files names, each one containing upto V_chunk rows        
           AffectedQVDs:
          
LOAD DISTINCT TARGET_QVD
          
RESIDENT $(TABLA_SOURCE);
          
          
// get the quantity of QVDs that will be used
           LET rows = NoOfRows(‘AffectedQVDs’);
    
          
FOR row=1 to rows
              
// assembles QVD file name
                let txtQVD ='[‘ & (FieldValue(‘TARGET_QVD’,row) * V_chunck) &';’& ((FieldValue(‘TARGET_QVD’,row)+1) * V_chunck – 1) & ‘]';
               
// If the QVD file already exists add the news to this, discarding the old versions of same records
                IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD’)) THEN         
                         
CONCATENATE ($(TABLA_SOURCE))
                         
$(TABLA_SOURCE):
                         
LOAD *
                         
FROM $(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD (QVD
                         
WHERE NOT EXISTS (PRIMARY_KEY);
               
ENDIF     
    
          
NEXT
    
    
ELSE
    
// Is the first run of this script. It’s necessary to do a full SQL extraction
    
           pre_
$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery);
          
    
          
//for each row calculate the corresponding patition and make field transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
    
          
//will store a dummy file just for signal that the first full load has been done
           flagFile:
          
NoConcatenate
          
LOAD 1 as dummy
          
AutoGenerate 1;
          
STORE flagFile INTO $(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD;
          
DROP TABLE flagFile;
    
    
END IF
    
    
//Recover the max and min value of the field used to partition
     MAXI_MINI:
    
LOAD min($(V_KEY)) as minPK,
          
max($(V_KEY)) as maxPK
    
RESIDENT $(TABLA_SOURCE);
    
    
    
LET maxPK=round(Peek(‘maxPK’));
    
LET minPK=round(Peek(‘minPK’));
    
// calculates the number of partitions
     LET partitions=ceil(maxPK/V_chunck);
    
    
LET V_I=1;
    
    
    
do while V_I<= partitions
          
if (V_I*V_chunck>=minPK) then
               
// extract the chunk for each partition        
                toStore:
               
NoConcatenate
               
LOAD *
               
RESIDENT $(TABLA_SOURCE)
               
WHERE $(V_KEY)>= ($(V_I)-1)*$(V_chunck) and $(V_KEY)<$(V_I)*$(V_chunck);
               
               
LET q = NoOfRows(‘toStore’);
               
               
//verify if the partition is not empty
                IF q>0 then
                    
Let since=(V_I-1)*V_chunck ;
                    
Let upto=(V_I*V_chunck)-1;
                    
// store the partition, using an assembled file name with information about the content
                     STORE toStore INTO $(_QVDsPath)$(V_QVDname)=[$(since);$(upto)].QVD;
               
ENDIF
               
               
DROP TABLE toStore;
          
ENDIF
          
let V_I=V_I+1;
    
loop
    
drop table $(TABLA_SOURCE);
    
    
// prepare variable to detect the news in the next run
     LET v_CompareDate_2 = ‘$(v_CompareDate_1)';
end Sub

and finally we complete in the qvw script our extractor, in this case for the Customers table:

 //////////////LIBRARIES/////////////////////////
// Load library
$(Include=.\incrementalPartitionedReload.txt);
//////////////END LIBRARIES////////////////////

CONNECT TO […YOUR DB CONNECTION STRING….];
// Quantity of IDs in each QVD’s partition, change to build bigger partitions and therefore more or less QVDs
LET _chunk = 500;
// Unique ID used to patitioning and insert the news
LET _Key=’IDCUSTOMER';
// First part of  QVDs file name
LET _QVDname=’Extract_CUSTOMER_’ & _Key;
// Extraction SQL query
SET _SQLquery=SELECT * FROM CUSTOMERS;
// WHERE clause which detects the news (inserts + updates)
SET _incrementalWhere= WHERE LASTUPDATE >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);
// Datasource fields read and load to QV
SET _FieldsLoaded= IDCUSTOMER, NAME, STREET, NUMBER, CITY, LASTUPDATE;
// Optional: filds tranformations
SET _transformFields= ,CITY & ‘,’ & STREET & ‘ ‘ & NUMBER AS FULL_ADDRESS; // Could be empty – Optional, but if it is must begin with “,”
// QVDs path
SET _QVDsPath =..\Qvd\;
// call the incremental partitioned load:
call loadIncrementalPartitioned(_chunk,_Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _transformFields);

 

Substituting the values ​​of the variables _chunk, _Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _QVDsPath and _transformFields can easily adapt and create incremental scripts to load partitioned.

For example, with _chunk = 500 we get something like this:

QVDS Partitioned

Last considerations:

 – The first time the script is run in addition to a full extraction of the datasource and save QVDs with data, it saves a “dummy” QVD file which is only used to indicate (flag) that the first run was already done. If this file is deleted the script will return to repeat a full reloading.

 – The _chunk variable determines the number of rows in each QVD and QVDs quantity. The number varies depending on the number of rows in the table and if there are “holes” (discontinuities) in the numbering.

- If we do not want partition the table but still keep doing incremental reloading must only define a value _chunk large enough so that all Ids enter into the single partition that will be created.

 – Finally to read and use QVDs created in the layer extraction in our intermediate layer (or presentation depending on the application architecture) we just make use of wildcar “*” to load all the parts as if they were one. P.Ej:

 LOAD *
FROM
[D:\Ventas\Qvd\Extraction_Customer_IDCUSTOMER=[*]].QVD]
(qvd);

Recarga Incremental Particionada – Parte II

En la primera parte de este artículo vimos que era y como se hace una carga incremental empleando una técnica que satisface la mayoría de las situaciones con que nos podemos encontrar.

Pero como dije anteriormente hay casos donde el volumen de datos que se maneja es de tal magnitud (en el orden de TeraBytes) que la simple lectura y/o grabación del archivo QVD involucrado puede tomar una cantidad considerable de tiempo y recursos del servidor, siendo que quizás solo debemos agregar unas pocas novedades al mismo.

Para evitar esta situación lo que debemos hacer es partir este archivo en varios fragmentos donde cada uno de ellos contenga un conjunto de filas agrupadas por algún criterio. Un criterio que se suele emplear es el mes-año de algún campo del registro, generando de este modo repositorios de QVDs que se ven mas o menos así:

                                 Extraction_Customers_CreateDate_012013.QVD
                                 Extraction_Customers_CreateDate_022013.QVD
                                 Extraction_Customers_CreateDate_032013.QVD
                                 Etc….

Donde una parte fija del nombre del archivo describe el contenido/origen/etapa y otra parte variable describe el periodo (mes-año) al que corresponden los registros contenidos en cada archivo, en este caso la fecha de alta (Creation Date) del Cliente.

Aunque no siempre podemos contar con una fecha siempre podremos contar con un campo numérico por el cual clasificar y agrupar los registros, ya sea el identificador único u otro campo que podamos crear usando las funciones Autonumber() o Hash…().

Lo importante es que el campo que elijamos debe cumplir con las siguientes características:

     -     DEBE ser numérico: para poder definir rangos o segmentos.
     -     NUNCA debe cambiar de valor a lo largo del tiempo: para poder ubicar en que archivo QVD está almacenado un registro dado.
     -     Debería tener una distribución cercana a random (es decir que las frecuencias de clase deben tender a ser iguales)
     -     Y como una consecuencia de lo anterior no debería tener rangos vacíos: usando el ejemplo de fecha de creación del cliente esto implica que se deberían crear clientes TODOS los meses.

Los dos últimos puntos no son una restricción absoluta, sino más bien una recomendación para obtener un mejor rendimiento.

En general el mismo identificador único empleado para realizar la carga incremental es un excelente (sino el mejor) campo candidato para particionar el QVD, debido a que en su sistema origen suele ser del tipo numérico, invariable en el tiempo (PK), único y autoincremental (de 1 en 1).

Para mejorar la lectura del código vamos a usar procedimientos (Subs) que funcionan semejantes a una función o rutina de otros lenguajes. A continuación va el código del procedimiento comentado, el cual debería ser guardado en un archivo llamado “incrementalPartitionedReload.txt“  que debe estar en la misma carpeta que el qvw sobre el que estamos trabajando:
 
sub loadIncrementalPartitioned (V_chunck,V_KEY,V_QVDname,V_SQLquery,V_incrementalWhere,V_fieldsLoaded,V_fieldsTransformations)

     Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]';
    
LET TABLA_SOURCE=’DATA_SOURCE';
    
    
// set the variable used to recover ONLY the news (since last execution)
     LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’);
    
    
// Check if the first full load has been done
     IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD’)) THEN
    
        
//Extract data from datasource
           pre_$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery)
          
$(V_incrementalWhere);
          
          
//for each row calculate the corresponding patition and make transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
           //Fill a little table which will be used to build the affected (if already exist) QVDs files names, each one containing upto V_chunk rows        
           AffectedQVDs:
          
LOAD DISTINCT TARGET_QVD
          
RESIDENT $(TABLA_SOURCE);
          
          
// get the quantity of QVDs that will be used
           LET rows = NoOfRows(‘AffectedQVDs’);
    
          
FOR row=1 to rows
              
// assembles QVD file name
                let txtQVD ='[‘ & (FieldValue(‘TARGET_QVD’,row) * V_chunck) &';’& ((FieldValue(‘TARGET_QVD’,row)+1) * V_chunck – 1) & ‘]';
               
// If the QVD file already exists add the news to this, discarding the old versions of same records
                IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD’)) THEN         
                         
CONCATENATE ($(TABLA_SOURCE))
                         
$(TABLA_SOURCE):
                         
LOAD *
                         
FROM $(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD (QVD
                         
WHERE NOT EXISTS (PRIMARY_KEY);
               
ENDIF     
    
          
NEXT
    
    
ELSE
    
// Is the first run of this script. It’s necessary to do a full SQL extraction
    
           pre_
$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery);
          
    
          
//for each row calculate the corresponding patition and make field transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
    
          
//will store a dummy file just for signal that the first full load has been done
           flagFile:
          
NoConcatenate
          
LOAD 1 as dummy
          
AutoGenerate 1;
          
STORE flagFile INTO $(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD;
          
DROP TABLE flagFile;
    
    
END IF
    
    
//Recover the max and min value of the field used to partition
     MAXI_MINI:
    
LOAD min($(V_KEY)) as minPK,
          
max($(V_KEY)) as maxPK
    
RESIDENT $(TABLA_SOURCE);
    
    
    
LET maxPK=round(Peek(‘maxPK’));
    
LET minPK=round(Peek(‘minPK’));
    
// calculates the number of partitions
     LET partitions=ceil(maxPK/V_chunck);
    
    
LET V_I=1;
    
    
    
do while V_I<= partitions
          
if (V_I*V_chunck>=minPK) then
               
// extract the chunk for each partition        
                toStore:
               
NoConcatenate
               
LOAD *
               
RESIDENT $(TABLA_SOURCE)
               
WHERE $(V_KEY)>= ($(V_I)-1)*$(V_chunck) and $(V_KEY)<$(V_I)*$(V_chunck);
               
               
LET q = NoOfRows(‘toStore’);
               
               
//verify if the partition is not empty
                IF q>0 then
                    
Let since=(V_I-1)*V_chunck ;
                    
Let upto=(V_I*V_chunck)-1;
                    
// store the partition, using an assembled file name with information about the content
                     STORE toStore INTO $(_QVDsPath)$(V_QVDname)=[$(since);$(upto)].QVD;
               
ENDIF
               
               
DROP TABLE toStore;
          
ENDIF
          
let V_I=V_I+1;
    
loop
    
drop table $(TABLA_SOURCE);
    
    
// prepare variable to detect the news in the next run
     LET v_CompareDate_2 = ‘$(v_CompareDate_1)';
end Sub

y por último completamos en el qvw el script de nuestro extractor, en este caso para la tabla Customers:

 //////////////LIBRARIES/////////////////////////
// Load library
$(Include=.\incrementalPartitionedReload.txt);
//////////////END LIBRARIES////////////////////

CONNECT TO […YOUR DB CONNECTION STRING….];
// cantidad de IDS en cada QVD particionado, cambiar para hacer particiones
// mas grandes o pequeñas, y por lo tanto
// mayor o menor cantidad de archivos QVDs
LET _chunk = 500;
// ID unico usado para particionar e ingresar las novedades
LET _Key=’IDCUSTOMER';
// Primera parte del nombre que tendrán los QVDs
LET _QVDname=’Extract_CUSTOMER_’ & _Key;
// SQL que se ejecuta contra la BD
SET _SQLquery=SELECT * FROM CUSTOMERS;
// Clausula WHERE que detecta  las novedades (inserts + updates)
SET _incrementalWhere= WHERE LASTUPDATE >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);
// Campos del datasource que son leidos y cargados
SET _FieldsLoaded= IDCUSTOMER, NAME, STREET, NUMBER, CITY, LASTUPDATE;
// Optativo: transformaciones sobre los campos de la misma tabla
SET _transformFields= ,CITY & ‘,’ & STREET & ‘ ‘ & NUMBER AS FULL_ADDRESS; // Could be empty – Optional, but if it is must begin with “,”
// Path donde serán guardados y leidos los QVDs
SET _QVDsPath =..\Qvd\;
// call the incremental partitioned load:
call loadIncrementalPartitioned(_chunk,_Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _transformFields);

 

Sustituyendo los valores de las variables _chunk, _Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _transformFields y _QVDsPath se puede adaptar y crear fácilmente scripts de carga incremental particionada.

Por ej, con _chunk=500 obtendremos algo como: QVDS Partitioned

Ultimas consideraciones:

-          La primera vez que se ejecuta el script además de hacer una extracción full del datasource y grabar los QVDs con los datos se grabará un QVD “dummy” que solo es utilizado para señalar que la primera ejecución ya fue realizada. Si este archivo es borrado se volverá a repetir una recarga full.

-          La variable _chunk determinará la cantidad de filas en cada QVD y la cantidad de QVDs. El número varía dependiendo de la cantidad de filas en la tabla y de si existen “huecos” (discontinuidades) en la numeración.

-          Si no queremos particionar la tabla pero si queremos seguir haciendo recarga incremental solo debemos definir un valor _chunk lo suficientemente grande de tal modo que todos los Ids entren en la única partición que será creada.

-          Por último para leer y usar los QVDs de  la capa de extracción en nuestra capa intermedia (o de presentación dependiendo de la arquitectura de la aplicación) solo debemos hace uso del wildcar “*” para cargar todas la partes como si fueran una sola. P.Ej:

 LOAD *
FROM
[D:\Ventas\Qvd\Extraction_Customer_IDCUSTOMER=[*]].QVD]
(qvd);

Incremental partitioned reload – Part I

In this article I’ll explain how to make one (usually daily) periodic incremental load and provide the code to perform incremental loads partitioned by any numeric field.

Basically an incremental load works as follows:

incrementalLoad

 This means that for the first time perform a full load and then just take each day’s  news of the previous day and then add to these news everything that was already stored in the QVD and that is NOT on these news.

For example if a client (ID = 99) on day X is created on data source, and his address declared as “Route 66 Mi 456″ that day will detect this row as a novelty, because it does not exist in the QVD, and therefore will be added to qvd. Later on X + N day customer moves to “Route 2 Mi 38″ and the process of incremental reload will detect the novelty and will add the rest of the QVD (other customers), discarding the data which had kept to the client 99 (Route 66 Mi 456) and instead keeping customer ID=99 data  that were recovered from the BD.

Achieve this requires that the table on which it will do incremental recharge meets two characteristics:

1-      It must has a unique identifier.

2-      It must have a field of type date or date-time where each time a record is created and / or modified in ANY way by the source system (transactional) this field is updated with the current date-time. This task is a commitment that must be agreed with the responsible source system.

To specify the explained and move forward here’s the code for a basic incremental recharge:

Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]'; //Spanish format

LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’); // Yesterday

//Checks if the QVD file exists for the first time
IF NOT ISNULL(QvdCreateTime(‘incrementalTable.QVD’)) THEN
//It is not the first time, because the QVD file exists

//Recover all the news (new records + updates since last load (v_CompareDate_2)

incrementalTable:
LOAD *; //If prefer can detail a list of fields
SQL SELECT *  //Can detail fields list
FROM sourceTable
WHERE LastUpdateDate >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);

//Adds records stored en prvious days into QVD file and doesn’t exist as news
CONCATENATE (incrementalTable)
LOAD *
FROM incrementalTable.QVD (QVD)
WHERE NOT EXISTS (UNIQUEID);

ELSE
// first time running: the QVD file doesn’t exist

// So it makes a full load

incrementalTable:
LOAD *;
SQL SELECT * FROM sourceTable;

END IF

STORE incrementalTable incrementalTable.QVD;
DROP TABLE incrementalTable;

LET v_CompareDate_2 = ‘$(v_CompareDate_1)';
 

We will highlight some points of the code:

1) How to detect the latest news?

We do read all of BD what happened after the last time ran this same script: “WHERE LastUpdateDate> = TO_DATE (‘$ (v_CompareDate_2)’, ‘YYYY-MM-DD’)”. This could be yesterday or two weeks ago, it does not matter because the date of the last run (less one day) was stored in “v_CompareDate_2″ variable (remember that QV variables are persistent over time).

2) How to avoid loading new rows that had been previously loaded (duplicity)?

If the QVD had rows stored already present among the news we will keep these news and discard what was stored: “WHERE NOT EXISTS (UNIQUEID)”

3) How the join the news with other previously stored data?

Concatenating two sets (news + “legacy” data): “CONCATENATE (incrementalTable)”

4) How “prepare” the script for the next run? Seting the value of  “v_CompareDate_2″ to “v_CompareDate_1″:   “LET v_CompareDate_2 = ‘$ (v_CompareDate_1)'”

Thus we have a script able to load an entire table in his first run and after that just extract and store the news replacing the old versions of these records if any.

However although this technique optimizes BD reading and transfer of data it has a performance issue:  if the resulting QVD is truly large (> 1 or 2 TByte) ,  every time it runs reads and saves a very large file to disk, so even with fairly powerful servers it can take on the order of 10-20 minutes, doing heavy execution and consuming valuable time of an operating window that in many organizations is often narrowed to a few dawn hours.

 In the second part of this article I will explain how to modify and package this code to read and write only small portions of QVD (actually many small QVDs), optimizing the time and resource disk access.

Recarga incremental particionada – Parte I

En este artículo voy explicar cómo realizar una carga incremental peródica (habitualmente diaria) y proveer el código para realizar cargas incrementales particionadas por cualquier campo numérico.

Básicamente una carga incremental funciona del siguiente modo:

incrementalLoad

 

Es decir que por primera vez realizamos una carga completa y luego cada día solo tomamos las novedades del día anterior y a estas novedades le agregamos todo lo que ya estaba almacenado en el QVD y que NO está en dichas novedades.

A modo de ejemplo si el día X el cliente (ID=99)  se da de alta y declara como dirección “Ruta 66 Km 456” ese día se va a detectar esta fila como una novedad, debido a que no existe en el QVD, y se va a agregar a dicho QVD. Posteriormente el día X+N el cliente se muda a “Ruta 2 Km 38” y el proceso de recarga incremental va a detectar la novedad y la va a agregar al resto del archivo QVD (otros clientes) DESCARTANDO por completo de este archivo lo que había guardado para el cliente 99 (Ruta 66 Km 456) y en su lugar guardando los datos del cliente 99 que se recuperaron de la BD.

Para hacer esto se requiere que la tabla sobre la cual se va a hacer la recarga incremental cumpla con 2 características:

1-      Debe tener un identificador único.

2-      Debe tener un campo de tipo fecha o fecha-hora donde cada vez que un registro es creado y/o modificado en CUALQUIER modo por el sistema origen (transaccional) este campo se actualice con la fecha hora del momento. Esta tarea es un compromiso que debe pactarse con los responsables del sistema origen.

Para concretar lo explicado y seguir avanzando aquí va el código de una recarga incremental básica:

Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]'; //Spanish format

LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’); // Yesterday

//Checks if the QVD file exists for the first time
IF NOT ISNULL(QvdCreateTime(‘incrementalTable.QVD’)) THEN
//It is not the first time, because the QVD file exists

//Recove all the news (new records + updates since last load (v_CompareDate_2)

incrementalTable:
LOAD *; //If prefer can detail a list of fields
SQL SELECT *  //Can detail fields list
FROM sourceTable
WHERE LastUpdateDate >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);

//Adds records stored en prvious days into QVD file and doesn’t exist as news
CONCATENATE (incrementalTable)
LOAD *
FROM incrementalTable.QVD (QVD)
WHERE NOT EXISTS (UNIQUEID);

ELSE
// first time running: the QVD file doesn’t exist

// So it makes a full load

incrementalTable:
LOAD *;
SQL SELECT * FROM sourceTable;

END IF

STORE incrementalTable incrementalTable.QVD;
DROP TABLE incrementalTable;

LET v_CompareDate_2 = ‘$(v_CompareDate_1)';
 

 Vamos a destacar algunos puntos del código:

1)      ¿Cómo detectamos las novedades?

Lo hacemos leyendo de la BD todo lo que sucedió después de la última vez que corrió este mismo script: “WHERE LastUpdateDate >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’)”. Esto podría ser ayer o hace dos semanas, no importa porque la fecha de la última corrida (menos un día) quedó almacenada en la variable v_CompareDate_2 (recordar que las variables de QV son persistentes en el tiempo).

2)      ¿Cómo evitamos cargar novedades de filas que ya habían sido cargadas previamente?

Si en el QVD hubiera filas almacenadas ya presentes entre las novedades nos quedaremos con las novedades y DESCARTAMOS lo que estaba guardado: “WHERE NOT EXISTS (UNIQUEID)”

3)      ¿Cómo juntamos las novedades con el resto de los datos previamente almacenados?

Concatenando ambos conjuntos: “CONCATENATE (incrementalTable)”

4)  ¿Cómo “preparamos” el script para la próxima ejecución? Setando la fecha v_CompareDate_2 al valor de v_CompareDate_1: “LET v_CompareDate_2 = ‘$(v_CompareDate_1)’”

De este modo tenemos listo un script capaz de cargar la totalidad de una tabla en su primera ejecución y luego solo extraer y almacenar las novedades sustituyendo las viejas versiones de esos registros si existieran.

No obstante esta técnica qunque optimiza la lectura de la BD y la transferencia de datos desde esta tiene un problema de performance en caso que el QVD resultante sea verdaderamente grande (>1 ó 2 TByte) y es que cada vez que corre este script tal y como está primero lee y luego graba un archivo muy grande al disco, lo que incluso con servidores medianamente potentes puede estar en el orden de los 10-20 minutos, haciendo muy pesada la ejecución, consumiendo valioso tiempo de una ventana operativa que en muchas organizaciones suele estrecharse a unas pocas horas de madrugada.

En la segunda parte de esta nota voy a explicar cómo modificar y empaquetar este código para leer y grabar solo pequeñas porciones del QVD (en realidad muchos pequeños QVDs), optimizando los tiempos y el recurso de acceso a disco.

Using expression’s color to show more information

In this post I show how can use the color expression’s in a chart to show information to the user in a single glance and maintaining the graph in a simple and easily readable format.

The idea is that each bar (in the case of a bar chart) can be assigned to a particular color using RGB () function with some kind of calculation based on the model’s data.

For example suppose you have a table of receivables with the following information:

Receivables:
load * inline [
CustomerID, Name, Debt, Days_Old, Credit_Qualification
1,   Pedro , 100, 50, 2-Good
2,   Maria , 140, 20, 1-Very Good
3,   Susana, 180, 90, 3-Poor
4,   John, 50, 35, 4-Bad
5,   Carlos, 70, 70, 5-Very Bad
6,  Sofia, 250, 10, 2-Good
];

And we want to show debtors in a bar chart where the expression is the amount owed, the dimension the debtor’s name, but we also want to show the age of the debt as follows: the oldest debt we will highlight with intense red color and vice versa, getting something like this:

Chart

 

Because Sofia has a quite new debt (10 days, low risk) will use a dimmed color, in contrast with a 90 days Susana’s debt showed with deep red.

To achieve this we will use the RGB function RGB(255, X, X) where 255 represents the red component and X is a function: while X is closest to 230 (maximum value) more faint result the color, and the more close to 0 (zero) more intense.

To do this add the following code to the script:

 MinMax:
Load
min(Days_Old) as minDaysOld,
max(Days_Old) as maxDaysOld
RESIDENT Receivables;

LET V_minDaysOld = Peek(‘minDaysOld’,0);
LET V_maxDaysOld = Peek(‘maxDaysOld’,0);

Drop Table MinMax;

 Finally we create an expression and use it in the “Background Color” from the main expression:

 BackgroundColor

 Of course you can create color expression that is required, with more or less levels of complexity or assigning colors to age ranges, etc..

The important thing to remember here is another resource, the color, which can be used to display user’s valuable information.

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

A %d blogueros les gusta esto: