PERFORMANCE

English (Español a continuación)

The efficiency of a database is especially relevant in 3 moments:

  • Loading data
  • Calculations
  • Consultation

In databases of medium size of these three moments the most critical for efficiency purposes is the calculation.

The factors that mainly affect the efficiency of a database are:

  • Structure of dimensions
  • Calculation design
  • Caches size

I.- Structure of the dimensions

Of the three previous factors, the most critical of them for efficiency purposes may be the structure of the database itself.

  • This aspect refers to the number of dimensions and the typology of these dimensions (dense or sparse).

a) The number of dimensions must be the minimum necessary to manage the information in the database; You should never include an extra dimension if it is not absolutely necessary.

Including a new dimension increases the potential size of the database by as many times as the number of members of the new dimension.

  • For example, if we have a database with 6 dimensions with a maximum potential number of 1,000,000 blocks, if we incorporate a new dimension with 8 members, the maximum potential number of blocks rises to 8,000,000 blocks.

b) The typology of the dimensions is another fundamental aspect of the database: which dimensions are dense and which are sparse. In principle, those dimensions whose members will have data in most of the blocks should be dense, while those dimensions whose members not always will be informed should be sparse.

Let’s see an example: starting from an Outline with the following dimensions:

It is foreseeable that any block will always (or almost always) have data in all the members (or most) of the following dimensions:

  • Years (2017, 2018, 2019…).
  • Months (January, February, March…).
  • Weeks (1st Week, 2nd Week, 3rd Week…).
  • Parameters (Sales, Budgets, Margin…).
  • Currencies (Euros, Pounds, Dollars…) (suppose that all data are expressed in the different currencies).

As these members are generally going to be informed, it is appropriate for these dimensions to be dense.

Regarding the other dimensions:

  • Markets (Germany, France, Italy …)
  • Vendors (Smith, Ortega, Rossi …)
  • Products (PC K25, PC K32, Tablet Y7…)

In some blocks there will be data of some markets and in other blocks of other markets; in some blocks there will be data of some vendors and in other blocks of other vendors; some products will hava data in some blocks and other products in other blocks. Therefore these dimensions should be sparse.

This would be a preliminary analysis of the typology of the dimensions.

c) A third aspect that must be analyzed is the size of the blocks.

  • The size is calculated by multiplying the number of members of the dense dimensions and this result is multiplied by 8 Bytes.

In principle, the size of the blocks should range between 8KB and 100KB. Be especially careful with very large blocks (> 150 KB).

  • It can greatly slow down both the loading of the bases and the calculations; it can also have an effect on higher levels of fragmentation, considerably increasing the size of the base.

Let’s assume that the dimensions above have the following members:

With this structure the size of the blocks would be:

  • Size = (5 * 12 * 5 * 22 * ​​10) * 8 = 528 KB

This size is excessively large so it would be convenient to change the type of one of the dimensions from dense to sparse.

  • For example, we can change the type of the “Currencies” dimension.

The size of the block would be:

  • Size = (5 * 12 * 5 * 22) * 8 = 52.8 KB

A block size that falls within the recommended limits.

In any case, it is convenient to do data upload tests and calculations with the different outline options, comparing the times.

d) Once we have established the types of the dimensions, we must establish their order in the outline.

It is recommended to place first the dense dimensions (from the greatest number of members to the least number of members) and then the sparse dimensions (from the least number of members to the greatest number of members).

The outline structure would be:

II.- Calculation design

a) It is essential to limit each calculation instruction to the area of ​​the base required for that particular calculation:

  • Avoid calculating blocks that are not necessary.

This implies:

  • Limit the use of the CALC ALL function, applying it only when justified.
  • Use the FIX … ENDFIX structure to delimit the scope of the base to which a specific calculation is applied.

For example,if in the previous outline we want to add the sales (dimension “Parameters”) in euros for all the weeks of January 2019 of all markets and all vendors of the PC K25 product. We define the calculation:

  • FIX (Sales, January, Euros, ”2019”, @DESCENDANTS(“Weeks”), ”PC K25”)
  • CALC DIM (Markets, Vendors);
  • ENDFIX

b) Once a FIX has been opened, try to perform all the calculations that affect that FIX and not be opening and closing FIX.

For example, if in the previous calculation we need to calculate the sales for January and February, the following calculation is not efficient:

  • FIX (Sales, January, Euros, ”2019”, @DESCENDANTS(“Weeks”), ”PC K25”)
  • CALC DIM (Markets, Vendors);
  • ENDFIX
  • FIX (Sales, February, Euros, ”2019”, @DESCENDANTS(“Weeks”), ”PC K25”)
  • CALC DIM (Markets, Vendors);
  • ENDFIX

It is efficient:

  • FIX (Sales, @LIST(January, February), Euros, ”2019”, @DESCENDANTS(“Weeks”), ”PC K25”)
  • CALC DIM (Markets, Vendors);
  • ENDFIX

Another example: if we need to calculate sales of January and margin (dimension “Parameters”) of February, it is appropriate to open a first FIX with those elements that are common to both calculations and within that first FIX open other specific FIXs for each calculation:

  • FIX (Euros, ”2019”, @ DESCENDANTS (“Weeks”), ”PC K25”)
    • FIX (Sales, January)
      • CALC DIM (Markets, Vendors);
    • ENDFIX
    • FIX (Margin, February)
      • CALC DIM (Markets, Vendors);
    • ENDFIX
  • ENDFIX

c) The calculation flow must be well structured within the script to avoid overlaps:

  • Avoid calculating the same block two or more times.

For example: we cannot launch the calculation:

  • FIX (Sales, @LIST(January, February), Euros, ”2019”, @DESCENDANTS(“Weeks”), ”PC K25”)
  • CALC DIM (Markets, Vendors);
  • ENDFIX

And later in the script launch the following calculation for all months and all years:

  • FIX (Sales, @DESCENDANTS (Months), Euros, @DESCENDANTS(Years), @DESCENDANTS (“Weeks”),”PC K25”)
  • CALC DIM (Markets, Sellers);
  • ENDFIX

We would be calculating January and February of 2019 twice.

d) As a general rule, efficiency is gained by activating the parallel calculation.

Essbase performs the calculations in two ways:

  • Serial: is the default type; the calculation is executed sequentially following the order of the script.
  • In parallel: Essbase divides the calculations into sub-tasks; These sub-tasks can be performed simultaneously, one independent of the other.

Parallel calculation can be activated in the calculation script by including the instruction

  • SET CALCPARALLEL n;

“n”: is an integer that indicates the number of sub-tasks that Essbase can perform simultaneously: this value can range from 1 to 64 (on 32-bit platforms) or from 1 to 128 (on 64-bit platforms); including this statement with the value n = 0 tells Essbase to run the serial calculation.

e) Restructure the database: repeated calculations and data loading can generate fragmentation in the database, significantly increasing its size and slowing down the calculations.

  • It is convenient to proceed regularly (for example, weekly) to restructure the database.

III.- Caches size

Essbase selects some default levels that serve as a starting point, but must be adjusted to gain efficiency.

  • To predict the optimal size of the caches, tests must be carried out to optimize response times.
  • When adjusting the cache it is better to do it progressively in small increments: small increments can have the same impact as large increments.
  • All caches have a hit ratio that can be consulted in “Database properties” / “Statistics”. This ratio indicates the % of times that a requested piece of information is available in the cache. A high % means that Essbase finds that information in the cache more frequently without having to go to disk, which improves its efficiency.

a) Index Cache

  • Minimum size: 1MB
  • Maximum size: 4GB (32-bit Essbase) to 256 TB (64-bit Essbase)
  • Default size: 1 MB (I / O buffer); 10 MB (Direct I / 0)
  • Recommended size: sum of the size of all “essxxx.ind” files, plus an additional % for future increments (eg 10%).
    • If due to system limitations it is not possible to reach this size, its size should be as large as possible.

In any case, if a good hit ratio is achieved with a smaller size, it is not necessary to increase the size.

The “Index Cache” hit ratio indicates the % of times Essbase locates the index information in the “Index Cache” without having to go to disk.

  • The hit ratio should be close to 1 (for example, 0.95 is good).

b) Data Cache

This cache needs to store a limited % of the database data.

  • Minimum size: 3,072 KB
  • Default size: 3,072 KB
  • Recommended theoretical size: 0.125 * (sum of the size of all the “essxxxx.pag” files). This size should be increased if:
    • There are usually many simultaneous queries to the database (which can query different blocks of information).
    • Calculation on wide ranges and functions that require consulting wide ranges of data blocks (@RANK, @RANGE …).
    • Simultaneous calculations usually coincide.

In any case, you have to check the “Data Cache” hit ratio to see how optimally the cache is working.

c) Calculation cache

3 options can be considered, which ordered from highest to lowest level of efficiency are:

  • A single anchor dimension and multiple bitmaps.
  • A single anchor dimension and a single bitmap.
  • Multiple anchor dimensions and a single bitmap.

Of these three options, Essbase will select at any times the optimal depending on the size we have assigned to the calculation cache.

  • The first option need a bigger cache tan the second, and the second a bigger than the third (see “CALCULATOR CACHE” in this Blog).
  • If the size of the calculation cache is very small Essbase does not use it, which can negatively affect the efficiency of the calculation.

The size of the calculation cache is calculated:

  • Cache size: Bitmap size (in bytes) * number of bitmaps.
  • Bitmap size (number of member combinations of the bitmaps dimensions / 8); the minimum size is 4 bytes.
  • No. of bitmaps: maximum number of dependent parents in the anchor dimensions + 2 (constant bitmaps).

Español

La eficiencia de una base de datos es especialmente relevante en 3 momentos:

  • Carga de datos
  • Cálculos
  • Consulta

En bases de datos de tamaño medio de estos tres momentos el más crítico a efectos de eficiencia es el del cálculo.

Los factores que afectan principalmente a la eficiencia de una base de datos son:

  • Estructura de las dimensiones
  • Diseño de los cálculos
  • Tamaño de las caches

I.- Estructura de las dimensiones

De los tres factores anteriores el más crítico de ellos a efectos de eficiencia puede ser la propia estructura de la base de datos.

  • Este aspecto hace referencia al número de dimensiones y a la tipología de estas dimensiones (densas o dispersas).

a) El número de dimensiones debe ser el mínimo necesario para gestionar la información de la base de datos; nunca se debe incluir una dimensión de más si no es absolutamente necesaria.

Incluir una nueva dimensión incrementa el tamaño potencial de la base de datos en tantas veces como número de miembros tenga la nueva dimensión incorporada.

  • Por ejemplo, si tenemos una base de datos con 6 dimensiones con un número potencial máximo de 1.000.000 de bloques, si incorporamos una nueva dimensión con 8 miembros, el número potencial máximo de bloque se eleva a 8.000.000 bloques.

b) La tipología de las dimensiones es otro aspecto fundamental de la base de datos: que dimensiones son densas y cuales son dispersas. En principio deben ser densas aquellas dimensiones cuyos miembros por le general van a tener datos en la mayoría de los bloques, mientras que deben ser dispersas aquellas dimensiones donde sus miembros no siempre van a estar informados.

Veamos un ejemplo: partiendo de un Outline con las siguientes dimensiones:

Es previsible que cualquier bloque tenga informado siempre (o casi siempre) todos los miembros (o la mayoría) de las siguientes dimensiones:

  • Años (2017, 2018, 2019…).
  • Meses (Enero, Febrero, Marzo…).
  • Semanas (1º Semana, 2ª Semana, 3ª Semana…).
  • Parámetros (Ventas, Presupuestos, Margen…).
  • Divisas (Euros, Libras, Dólares…) (supongamos que todos los datos se expresan en las diferentes monedas).

Como estos miembros por lo general van a estar informados lo apropiado es que estas dimensiones sean densas.

En cuanto a las otras dimensiones:

  • Mercados (Alemania, Francia, Italia…)
  • Vendedores (Smith, Ortega, Rossi…)
  • Productos (PC K25, PC K32, Tablet Y7…)

En unos bloques estarán unos mercados y en otros bloques otros mercados; en unos bloques estarán unos vendedores y en otros bloques otros vendedores; también en unos bloques estarán unos productos y en otros bloques otros productos. Por ello estas dimensiones deberían ser dispersas.

Este sería un análisis preliminar de la tipología de las dimensiones.

c) Un tercer aspecto que hay que analizar es el tamaño de los bloques.

  • El tamaño se calcula multiplicando el número de miembros de las dimensiones densas y este resultado se multiplica por 8 Bytes.

En principio el tamaño de los bloques debería oscilar entre 8KB y 100 KB. Hay que tener especial cuidado con bloques de tamaños muy grande (> 150 KB).

  • Puede ralentizar mucho tanto la carga de las bases como los cálculos; además puede repercutir en mayores niveles de fragmentación, incrementando considerablemente el tamaño de la base.

Vamos a suponer que las dimensiones anteriores constan de los siguientes miembros:

Con esta estructura el tamaño de los bloques sería:

  • Tamaño = (5 * 12 * 5 * 22 * 10) * 8 = 528 KB

Este tamaño sería excesivamente grande por lo que sería conveniente cambiar el tipo de una de las dimensiones de densa a dispersa.

  • Por ejemplo, podemos cambiar el tipo de la dimensión “Divisas”.

El tamaño del bloque quedaría:

  • Tamaño = (5 * 12 * 5 * 22) * 8 = 52,8 KB

Un tamaño de bloque que entra dentro de los límites recomendados.

En todo caso es conveniente hacer pruebas de subidas de datos y cálculos con las distintas opciones de outline, comparando los tiempos.

d) Una vez que hemos establecido los tipos de las dimensiones hay que establecer su orden en el outline.

  • Lo recomendado es situar primero las dimensiones densas (de mayor número de miembros a menor número de miembros) y luego las dimensiones dispersas (de menor número de miembros a mayor número de miembros).

La estructura del outline quedaría:

II.- Diseño de los cálculos

a) Es fundamental limitar cada instrucción del cálculo al área de la base que necesita ese cálculo concreto:

  • Evitar el cálculo de bloques que no son necesarios.

Ello implica:

  • Limitar el uso de la función CALC ALL, aplicándola únicamente cuando esté justificada.
  • Utilizar la estructura FIX … ENDFIX para delimitar el ámbito de la base a la que se aplica un cálculo concreto.

Por ejemplo, sin en el outline anterior queremos agregar las ventas (dimensión Parámetros) en euros de todas las semanas de enero del año 2019 de todos los mercados y de todos los vendedores del producto PC K25. Definimos el cálculo:

  • FIX(Ventas,Enero,Euros,”2019”,@DESCENDANTS(“Semanas”),” PC K25”)
  • CALC DIM (Mercados,Vendedores);
  • ENDFIX

b) Una vez que se ha abierto un FIX hay que tratar de realizar todos los cálculos que afecten a ese FIX y no estar abriendo y cerrando FIX.

Por ejemplo, si en el cálculo anterior necesitamos calcular las ventas de enero y febrero, el siguiente cálculo no es eficiente:

  • FIX(Ventas,Enero,Euros,”2019”,@DESCENDANTS(“Semanas”) ,” PC K25”)
  • CALC DIM (Mercados,Vendedores);
  • ENDFIX
  • FIX(Ventas,Febrero,Euros,”2019”,@DESCENDANTS(“Semanas”) ,” PC K25”)
  • CALC DIM (Mercados,Vendedores);
  • ENDFIX

Sí es eficiente:

  • FIX(Ventas,@LIST(Enero,Febrero),Euros,”2019”,@DESCENDANTS(“Semanas”) ,” PC K25”)
  • CALC DIM (Mercados,Vendedores);
  • ENDFIX

Otro ejemplo: si de enero necesitamos calcular las ventas y de febrero el margen (dimensión Parámetros), lo apropiado es abrir un primer FIX con aquellos elementos que sean comunes a ambos cálculos y dentro de ese primer FIX abrir otros FIX específicos para cada cálculo:

  • FIX(Euros,”2019”,@DESCENDANTS(“Semanas”) ,” PC K25”)
    • FIX(Ventas,Enero)
      • CALC DIM (Mercados,Vendedores);
    • ENDFIX
    • FIX(Margen,Febrero)
      • CALC DIM (Mercados,Vendedores);
    • ENDFIX
  • ENDFIX

c) Hay que estructurar muy bien dentro del script el flujo del cálculo para evitar solapes:

  • Evitar calcular dos o más veces el mismo bloque.

Por ejemplo: no podemos lanzar el cálculo:

  • FIX(Ventas,@LIST(Enero,Febrero),Euros,”2019”,@DESCENDANTS(“Semanas”) ,” PC K25”)
  • CALC DIM (Mercados,Vendedores);
  • ENDFIX

Y más adelante en el script lanzar el siguiente cálculo de todos los meses y de todos los años:

  • FIX(Ventas,@DESCENDANTS(Meses),Euros,@DESCENDANTS(Años),@DESCENDANTS(“Semanas”), ”PC K25”)
  • CALC DIM (Mercados,Vendedores);
  • ENDFIX

Los meses enero y febrero del año 2019 los estaríamos calculando dos veces.

d) Por regla general se gana en eficiencia activando el cálculo paralelo.

Essbase ejecuta los cálculos de dos formas:

  • En serie: es el tipo por defecto; el cálculo se ejecuta secuencialmente siguiendo el orden del script.
  • En paralelo: Essbase divide los cálculos en sub-tareas; estas sub-tareas pueden realizarse simultáneamente, una independiente de otra.

El cálculo paralelo se puede activar en el script de cálculo incluyendo la instrucción

  • SET CALCPARALLEL n;

“n”:  es un número entero que indica el número de sub-tareas que Essbase puede realizar simultáneamente: este valor puede ir entre 1 a 64 (en plataformas de 32 bits) o entre 1 a 128 (en plataformas de 64 bits); si se incluye esta instrucción con el valor n=0 se indica a Essbase que ejecute el cálculo en serie.

e) Reestructurar la base de datos: los cálculos repetidos y la carga de datos puede generar fragmentación de la base de datos, incrementando notablemente su tamaño y ralentizando los cálculos.

  • Es conveniente proceder regularmente (por ejemplo, semanalmente) a reestructurar la base de datos.

III.- Tamaño de las caches

Essbase selecciona unos niveles por defecto que sirven como punto de partida, pero que hay que ajustar para ganar en eficiencia.

  • Para predecir el tamaño óptimo de las cachés hay que hacer tests para ir optimizando los tiempos de respuesta.
  • Cuando se ajusta la cache es mejor hacerlo progresivamente con pequeños incrementos: pequeños incrementos pueden tener el mismo impacto que grandes incrementos.
  • Todos los cachés tienen un hit ratio que se puede consultar en “Propiedades de la base de datos” / “Estadísticas”. Este ratio indica el % de veces que una pieza de información solicitada está disponible en el cache. Un % elevado significa que Essbase localiza esa información en el cache con más frecuencia sin tener que acudir al disco, lo que mejora su eficiencia.

a) Index Caché

  • Tamaño mínimo: 1MB
  • Tamaño máximo: 4GB (32-bit Essbase) a 256 TB (64-bit Essbase)
  • Tamaño por defecto: 1 MB (buffer I/O); 10 MB (Direct I/0)
  • Tamaño recomendado: suma del tamaño de todos los archivos “essxxx.ind”, más un % adicional para incrementos futuros (por ejemplo, un 10%).
    • Si por limitaciones del sistema no es posible alcanzar este tamaño, su tamaño debe ser el mayor posible.

En todo caso si con un tamaño menor se logra un hit ratio bueno no es necesario incrementar el tamaño.

El hit ratio del “Index Cache” indica el % de veces que Essbase localiza la información del índice en el “Index Cache” sin tener que acudir al disco.

  • El hit ratio debería ser próximo a 1 (por ejemplo, un 0,95 es bueno).

b) Data Cache

Este caché necesita almacenar un % limitado de los datos de la base de datos.

  • Tamaño mínimo: 3.072 KB
  • Tamaño por defecto: 3.072 KB
  • Tamaño teórico recomendado: 0,125 * (suma del tamaño de todos los ficheros “essxxxx.pag”). Este tamaño se debe incrementar si:
    • Suele haber muchas consultas simultáneas a la base de datos (que pueden consultar diferentes bloques de información).
    • Cálculo sobre rangos amplios y funciones que requieren consultar rangos amplios de bloques de datos (@RANK, @RANGE…).
    • Suelen coincidir cálculos simultáneos.

En todo caso hay que consultar el hit ratio del “Data Cache” para ver cómo de óptimo está funcionando la caché.

c) Caché de cálculo

Se pueden plantear 3 opciones, que ordenadas de mayor a menos nivel de eficiencia son:

  • Una única dimensión de anclaje y múltiples bitmaps.
  • Una única dimensión de anclaje y un único bitmap.
  • Múltiples dimensiones de anclaje y un único bitmap.

De estas tres opciones Essbase seleccionará en cada momento la que resulte óptima para el cálculo, selección que va a depender del tamaño que le hayamos asignado al caché de cálculo.

  • La primera opción necesita mayor caché que la segunda, y ésta que la tercera (puedes consultar “CALCULATOR CACHE” en este Blog).
  • Si el tamaño el cache de cálculo es muy pequeño Essbase no lo utiliza, lo que puede afectar negativamente a la eficiencia del cálculo.

El tamaño del cache de cálculo se calcula:

  • Tamaño del cache: Tamaño del bitmap (en bytes) * número de bitmaps.
  • Tamaño del bitmap (número de combinaciones de miembros de las dimensiones bitmaps / 8); el tamaño mínimo es 4 bytes.
  • Nº de bitmaps: número máximo de padres dependientes en las dimensiones de anclaje + 2 (bitmaps constantes).