Ajedrez - antoniomartel.com

Archivos por Etiqueta: Oracle

Auditoría en Oracle Discoverer

Otro post técnico, en esta ocasión sobre Oracle Discoverer (Oracle Business Intelligence SE One) y cómo conocer, entre otras cosas, qué usuarios trabajan sobre qué libros, con qué frecuencia usan la herramienta, cuando fue usado por última vez un libro o el uso de la aplicación que cada semana hacen los usuarios.

Oracle guarda en las tablas del esquema propietario de la EUL toda la información relativa a los documentos o informes a ejecutar, los datos a utilizar por la capa de presentación y mucha otra información. Estos datos son usados por Oracle Discoverer para hacer una predicción de los queries que el usuario va a ejecutar y poder anticipar los datos a traer de la base de datos para dejarlos en caché por si los solicitase.

En la tabla eul?_qpp_stats se guarda esta información de predicción y es la que se usa en estos ejemplos para ejecutar una auditoría.


Auditoría por usuarios

Como ejemplo, la siguiente consulta muestra el nombre de todos los usuarios que han hecho uso de la aplicación, la fecha de la última vez que la usaron, el número total de veces que la han usado y la media de días entre cada uso:

SELECT 
       stats.qs_created_by AS «Usuario»,
       TRUNC (MAX (stats.qs_created_date)) AS «Fecha último uso»,
       COUNT (stats.qs_created_date) AS «Nº de veces que se ha usado»,
       TO_CHAR ((MAX (stats.qs_created_date) – MIN (stats.qs_created_date)) / COUNT (stats.qs_created_date), ‘99990.00’ ) AS «Media de días entre cada uso»


FROM eul5_qpp_stats stats
GROUP BY (stats.qs_created_by)
ORDER BY MAX (stats.qs_created_date) DESC


Auditoría por documentos

Con esta otra consulta SQL es posible obtener un listado de los informes/reports creados por los usuarios y guardados en la base de datos, el nombre del usuario que los creó y la fecha de la última vez que fue ejecutado el documento (los informes sin estas fechas son informes que nunca fueron lanzados después de guardar):


SELECT DISTINCT
       DOCS.DOC_CREATED_BY AS «Usuario»,
       DOCS.DOC_NAME as «Informe»,
       (SELECT TRUNC(MAX(DM.QS_CREATED_DATE)) 
        FROM EUL5_QPP_STATS DM
        WHERE DM.QS_DOC_NAME = STATS.QS_DOC_NAME) as «Última ejecución»
FROM EUL5_QPP_STATS STATS, EUL5_DOCUMENTS DOCS
WHERE DOCS.DOC_NAME = STATS.QS_DOC_NAME(+)
GROUP BY DOCS.DOC_CREATED_BY, DOCS.DOC_NAME, STATS.QS_DOC_NAME
ORDER BY «Última ejecución» DESC, DOCS.DOC_CREATED_BY, DOCS.D
OC_NAME;

Auditoría por semanas

En esta consulta se obtiene la lista de usuarios que usaron la aplicación cada semana y el número de veces que lo hicieron. Para cada usuario se muestra:

  • Año al que corresponde la semana.
  • Fecha del lunes de esa semana.
  • Fecha del domingo de esa semana.
  • Usuario que usó la aplicación esa semana.
  • Número de veces que ese usuario usó la aplicación en la semana.
La consulta sería la siguiente:

SELECT

       TO_CHAR (stats.qs_created_date, ‘yyyy’) «Año»,
       TRUNC (stats.qs_created_date, ‘day’) «Lunes»,
       TRUNC (stats.qs_created_date, ‘day’) + 6 «Domingo»
       u.nombre «Usuario»,
       COUNT (stats.qs_created_date) «Nº de usos» 
FROM eul5_qpp_stats stats, usuarios u
GROUP BY 
       TO_CHAR (stats.qs_created_date, ‘yyyy’),
       TRUNC (stats.qs_created_date, ‘day’),
       TRUNC (stats.qs_created_date, ‘day’) + 6,
       u.nombre
ORDER BY 
       TO_CHAR (stats.qs_created_date, ‘yyyy’) DESC,
       TRUNC (stats.qs_created_date, ‘day’) DESC,
       TRUNC (stats.qs_created_date, ‘day’) + 6 DESC,
       u.nombre ASC 

Oracle XE, Forms 6i y un viaje a Ecuador

Hace algún tiempo tuve que realizar una implantación de una aplicación desarrollada en Oracle Developer 6i (Forms y Reports) para un museo. Por razones económicas decidimos utilizar una licencia Oracle Express Edition 10g, pero al intentar arrancar la aplicación accedíamos a ver durante un instante la consola de Windows y se cerraba inmediatamente.

El proyecto se estaba pasando de las horas estimadas para resolverlo y ahora teníamos un problema con el que nadie en la empresa se había topado antes. Consultamos la página metalink de Oracle, foros y páginas sobre desarrollo con Oracle.

Finalmente, tras muchas búsquedas en Google obtuvimos la respuesta al problema en el foro de Oracle Users Group de Ecuador, exactamente en una entrada de Paola Pullas y los comentarios al mismo.

Parece ser que Oracle XE 10g necesita de un parche para funcionar con Developer 6i y además cambiar el juego de caracteres a UTF8. Les indico aquí los pasos que tuvimos que realizar.

Primero, pedirle al DBA responsable de la instalación de la base de se conectara a la base de datos XE como SYSDBA en el sqlplus y ejecutara las siguientes instrucciones para cambiar el juego de caracteres.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;

Posteriormente fue necesario ejecutar el siguiente parche de Oracle sobre la instalación de Oracle Developer: Parche 18 de Developer, Patch number 4948577.

Espero que esta entrada resuelva el problema a algún desarrollador. Seguro que aún quedan sistemas y aplicaciones que están usando esta configuración.

Totales en blanco en Oracle BI Discoverer

Otro post técnico. En este caso cómo resolver el problema de los totales en blanco en Oracle Business Intelligence Discoverer:

En algún trabajé como administrador de la EUL (capa de usuario final) con las herramientas de Discoverer y nos encontramos con un problema que nos trajo de cabeza durante unos días: Los totales de algunas columnas se mostraban en blanco a pesar de contener datos y solamente mostraban una cantidad cuando sumaban sólo un registro. Si había más se mostraban vacíos.


Es algo que aparentemente puede parecer un error o bug de esta herramienta pero resultó ser una decisión de diseño en la herramienta. Me explico:

Si tenemos dos carpetas relacionadas entre sí como maestro-detalle y en la carpeta maestra tenemos la cantidad o el número que vamos a sumar, tendremos un problema ya que la suma contará la cantidad del registro maestro tantas veces como elementos haya en el detalle, y probablemente no es esto lo que queremos.

Mejor verlo con un ejemplo. Si tenemos una carpeta maestra con los datos de guías turísticos en la que además indicamos cuál es la tarifa que cobra cada guía mensualmente:

Id.     Nombre Tarifa
1         Antonio    2000€
2        Juan          1000€
3        Marta        1500€

Si además tenemos una carpeta detalle en la que tenemos la relación de idiomas que habla cada guía, tendremos algo como esto:

Id.     Nombre     Tarifa Idioma
1         Antonio        2000€        Inglés
2         Juan              1000€        Inglés
2         Juan              1000€       Alemán
2         Juan              1000€       Sueco
3         Marta            1500€       Alemán

Si hacemos un ‘Summary‘ o ‘Total‘, la herramienta de Discoverer nos mostrará que Antonio cobra 2000€ al mes, Marta 1500€ y que Juan cobra «_____». Es decir nos mostrará la casilla en blanco o vacía. Si mostrase un resultado nos daría 3000€, cantidad que no es la que realmente cobra Juan.

Tenemos algunas formas de resolver esto:

  1. Quizás la cantidad que estás sumando no deba ir en la carpeta detalle. Deberías revisar el diseño de la base de datos.
  2. Quizás la relación entre ambas carpeta deba ser de 1 a 1 y lo tienes puesto por error de 1 a muchos.
  3. Si todo lo anterior está correcto, hay una cosa más que puedes hacer: Decirle a Discoverer que sume la cantidad de todas formas, aquí están los pasos:
    1. Activar en las opciones del Discoverer Desktop la casilla ‘Show the sum of the values displayed in the contributing cells
    2. Establecer con valor 1 en lugar de 0 las propiedades AllowAggregationOverRepeatedValues yAggregationBehavior del fichero prefs.txt en el servidor de Discoverer en la ruta $ORACLE_HOMEDiscovererutil

Este último caso debemos tomarlo siendo conscientes de lo que supone. Aunque sea perfectamente válido para el caso que estás intentado resolver es posible que en otro informe que en el futuro esté colgado del mismo servidor, impidamos ver al administrador que hay un problema (fan trap) y se muestren totales con importes erróneos como el de la tarifa de Juan.

Oracle XE, Forms 6i and a trip to Ecuador

Quite some time ago I had to deploy an application developed with Oracle Developer 6i (also known as Forms & Reports). Another public institution bestowed upon our customer, a museum, a system that would help them to catalogue, inventory and keep track of their works of art loans.


To avoid costs of licencing, as their data volume wasn’t high at all, we decided to use, as database, the free Oracle Express Edition 10g license. The cataloguing system provided required an Oracle database as storage.

But, as soon as we double clic the application icon on screen, a Windows console was opened and closed in a blink of an eye. No log, no error messages, nothing.

We thought a lot about this, we queried Oracle Metalink, web pages and forums on Oracle development but no clues at all.

Ultimately, after many Google searches, we found the solution to our problem at the Oracle Users Group of Ecuador, precisely in a Paola Pullas’s post and the comments which followed.

It seems that a patch needs to be applied to Oracle XE 10g to make it work with Developer 6i. Besides that, it was needed to set the database character UTF8, that way you could make it work with tilda and accents. So here are the steps we had to follow:

First of all, we had to ask our DBA to connect to the XE database as SYSDBA and to execute these commands to change the character set:

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN;
STARTUP RESTRICT;
SHUTDOWN;
STARTUP;

After that we had to apply in our Oracle Developer installation the following patch: Oracle Developer Patch 18 (number 4948577).

Hope it still helps someone. Sure there are many people out there that are still maintaining a system like this one.

Blank Totals in Oracle BI Discoverer

Another technical post. This time about how to solve the Blank Totals problem in Oracle Business Intelligence Discoverer:

I worked years ago as an EUL administrator with the Oracle Discoverer tools. We found a problem that troubled us during days: Grand Totals for some columns were empty despite of containing data that should be shown. The info was visible only when there was only one record to be added up but only an empty cell was displayed when there were more than one.

That seemed to us like a glitch or bug in this tool but it came out to be a technical decision of the tool’s designers. Let me explain:

When we got two folders related to each other as master-detail but, at the master’s folder we have got the numeric field that is going to be added up, we will get into trouble as the addition should add the master records numeric fields as many times as detail items are in our database. That’s probably not what we want.

Let’s see an example. If we got a master folder with data from official tourist guides, which includes their fares, like this:

Id.     Name       Salary
1       Pedro        2.000€
2       Juan          1.000€
3       Marta        1.500€

And if, on top of this, we have a detail folder, where we get the list of languages spoken by each tourist guide, it would look like this:

Id.     Name       Salary       Language
1           (Pedro)        2.000€          English
2          (Juan)          1.000€           English
2          (Juan)          1.000€           German
2          (Juan)          1.000€           Swedish
3          (Marta)        1.500€           German
When we create a Summary or a Total, Discoverer will show us that Pedro’s fare is 2.000€, Marta’s 1.500€ but it will display ‘_____’ (blank cell) instead of the Juan’s fare. Whether the tool showed a result, it should be 3.000€, but that is not the real fare for Juan. It is only aggregating three amounts as Juan speaks three languages.

There is a few workarounds to fix this:

  1. Maybe the amount being added should not be at the Detail folder. Should the database design be reviewed?
  2. Check if the relation between master and detail folder is 1 to 1 but it is mistakenly set as 1 to many.
  3. If everything above is right, there is one more thing that you can do: Tell Discoverer that it should add up those amounts anyway. To do so, follow these steps:
    1. Activate the ‘Show the sum of the values displayed in the contributing cells‘ option at the Discoverer Desktop settings.
    2. Set the properties AllowAggregationOverRepeatedValues and AggregationBehavior to 1 instead of 0 at the prefs.txt file that you can find in Discoverer server path $ORACLE_HOMEDiscovererutil.

This latter case should be taken being aware of what that implies. Even if it were a perfectly valid solution for the case you are trying to fix, it could exist another report, now or in future, run at the same patched server, which wouldn’t allow the administrator to see there is a problem (fan trap) and totals or summary fields are displaying wrong amounts as they are being added up two or more times (like the Juan’s fares).

Suscríbete