Proscai cuenta con herramientas para facilitar el análisis de la información de tu base de datos y para que la conviertas en oportunidades de negocio, apoyando el desarrollo y la productividad de tu empresa.
El Generador de cubos te permite de forma muy sencilla estructurar sentencias de SQL sin necesidad de grandes conocimientos, para crear consultas y generar Cubos con el fin de operar de forma flexible, potente y veloz la información de interés de tu base de datos. Esta una herramienta para el uso de personal con conocimientos básicos de SQL, pero también para programadores experimentados.
La base de Proscai archiva los datos en tablas separadas que se encuentran conectadas por relaciones definidas que hacen posible combinar datos de diferentes tablas sobre pedido, en la cual solo es necesario identificar con claridad la necesidad y el conocimiento de las tablas en las cuales se encuentran los datos que brindarán la información solicitada.
Contenido del artículo:
- Estructura de la base de datos para manejar sentencias SQL y query
Funciones básicas para generar sentencias SQL
- Cubos y consultas en Proscai
Utilizar el Generador de cubos
Estructura de la base de datos para manejar sentencias SQL y query
Las bases de datos son sistemas de trabajo organizado que permiten catalogar y clasificar información, utilizando tablas que el sistema utiliza para ubicar rápidamente cualquier información guardada en ellas. Es necesario identificar en qué tabla se encuentran los campos, y conocer qué tablas requieren una de mayor detalle para poder extraer la información.
Para iniciar conoceremos la estructura lógica de almacenamiento que usa la base de datos, es semejante a un archivo de papel que se localiza por ejemplo en un edificio, piso, pasillo, ubicación, ficha y que de este modo es posible recuperar la información que interesa de un modo ágil, gracias a los índices y la estructura organizada del archivo. La estructura de la base de datos está conformada por Tablas (Main file), que contienen Registros (filas), compuesta por Campos donde se encuentran los datos.
En el ejemplo cada registro contiene información de un cliente. Observe que la tabla almacena en este caso 3 registros con 7 campos. El nombre de cada campo viene dado por la fila de encabezado. Cada registro contiene una secuencia que lo identifica y uno o más campos llave.
En resumen: Una base de datos contendrá tablas, que a su vez contienen registros y en estos se encontrarán los datos distribuidos en una serie de campos. Cada registro de la tabla guarda la información particular de una unidad o miembro de un mismo grupo.
La instrucción para hacer una selección de datos, de una o más tablas contenidas en una misma base de datos, se denomina SENTENCIA, para generar cubos será necesario que la sentencia incluya dimensiones y métricas.
- Las DIMENSIONES son los nombres de los elementos que conforman la tabla para manejar los datos: parámetros y familias, tablas y datos, las cuales contendrán a su vez un conjunto de elementos, por ejemplo en la dimensión Producto, cada uno de sus productos será un elemento.
- Las MÉTRICAS son elementos que servirán para comparar los datos, son aquellos rangos que se pueden medir: Importe bruto, Importe Neto, Costos, Piezas, Ofertado, etc.
Para construir una consulta SQL des conveniente hacernos como mínimo tres preguntas básicas:
- ¿Qué datos nos están pidiendo?
por ejemplo: el Código y Nombre de los clientes. - ¿Dónde se encuentran esos datos?
están en la tabla Clientes. - ¿Qué requisitos deben cumplir los registros?
por ejemplo: quienes tienen un saldo mayor o igual a 4000 pesos
Sintaxis SQL
| Ejemplo | select CODIGO, NOMBRE from CLIENTES where SALDO ACTUAL >= 4000 |
| Sintaxis SQL | select CAMPOS(separados por comas) from TABLA where CONDICIÓN |
| Para Proscai | select CLICOD,CLINOM from FCLI where CLISACT >=4000 |
También es posible extraer información de tablas vinculadas a la que en ese momento sea la de mayor detalle, para combinarlas en un reporte o consulta.
- ¿Qué datos nos están pidiendo? Clientes y sus documentos con saldo.
- ¿Dónde se encuentran esos datos? en la tabla Documentos
- ¿Qué requisitos deben cumplir los registros?
| Ejemplo | select CODIGO , NOMBRE, NUMERO DE DOCUMENTO Y SALDO from FDOC |
| Sintaxis SQL | select CAMPOS from TABLA |
| Para Proscai | Select CLICOD AS CODIGO, CLINOM AS NOMBRE, DNUM AS NUMERO_DE_DOCUMENTO, DCANT AS SALDO From FDOC left join FCLI on FCLI.CLISEQ=FDOC.CLISEQ |
Funciones básicas para generar sentencias SQL
| Función | Modo de empleo |
| SELECT En la cláusula SELECT se establecen las listas de campos que mostrará el resultado de la consulta. |
¿Qué datos quiero extraer? Código de cliente: CLICOD, y Número de documento: DNUM select CLICOD, CLINOM |
| FROM En la cláusula FROM se establece la fuente de los datos, las tablas que intervienen en la consulta. |
¿Dónde están los datos? Determinar la tabla de detalle (según la estructura de la BD) que contenga los campos a extraer, para emplearla como tabla principal. Tabla principal: FCLI: CLIENTES from FCLI |
| WHERE Las condiciones establecidas en la cláusula WHERE tienen como propósito filtrar registros de la tabla. De hecho es donde se establece el filtro de registros, es decir, qué registros serán considerados para mostrar los datos y cuáles no. |
¿Qué requisitos deben cumplir los registros? Condición que debe cumplirse para la extracción de los datos: = Igual que >= Mayor o igual <= Menor o igual <> Diferente de AND OR where CLISACT >=4000 |
|
LEFT JOIN |
Permite realizar una "consulta combinada" en la cual se busca en la tabla superior la liga con la tabla principal. left join FCLI ON FCLI.CLISEQ=FDOC.CLISEQ |
| GROUP BY En la cláusula GROUP BY se establecen los grupos de datos por los que se quiere obtener totales. |
¿Cómo deben agruparse los datos? Especifica la agrupación que se da a los datos. Se usa siempre en combinación con funciones de totalización (SUM, COUNT, MAX…). |
| ORDER BY La cláusula ORDER BY permite finalmente ordenar el resultado por diferentes columnas, y en sentido ascendente o descendente, antes de ser presentado. |
¿Cómo deben ordenarse los datos resultantes? Presenta el resultado ordenado por los campos indicados. El orden puede expresarse con ASC (ascendente) y DESC (descendente). El valor predeterminado es ASC. |
Es importante resaltar que si deseas extraer información de varias tablas debes respetar las ligas de vinculación, esto te permitirá generar una consulta piramidal.
Principales tablas de Proscai
| Nombre de tabla Proscai | Descripción de la tabla |
| FCLI | Catálogo de Clientes |
| FPRV | Catálogo de Proveedores |
| FINV | Catálogo de Inventarios por producto |
| FDOC | Documentos (Recepciones / Facturas-Tickets) |
| FAXINV | Líneas de documento o Auxiliar de inventarios |
| FAX | Auxiliar de Clientes y Proveedores (importe) |
| FPENC | Encabezados Pedidos y Órdenes de compra |
| FPLIN | Líneas de pedidos u órdenes de compra |
| FBENC | Catálogo de cuentas |
| FPOLIZA | Encabezados Póliza |
| FBANMOV | Movimientos de Bancos |
| FAG | Parámetros |
| FALMCAT | Catálogo de almacenes |
| FFAM | Familias |
| FUNIDAD | Unidad de medida |
| FCAJAS | Rollos y números de serie |
| FALM | Detalle de almacenes |
| FENS | Ensambles de producción |
Si desea consultar los nombre de los principales campos en Proscai, revise el tema Nombre de tablas y campos en Proscai.
Cubos y consultas en Proscai
Utilizar el Generador de cubos
El Generador de cubos una herramienta que te ayudará a crear cubos y consultas sin necesidad de grandes conocimientos de SQL y te facilitará la elaboración de sentencias SQL a través de un manejo más sencillo y dinámico de la base de datos.
1. Selecciona del menú Proscai el módulo SIG. Del menú Importa / Exporta selecciona la opción Genera cubos (1).
El sistema presenta la ventana Generador de cubos que trae precargadas las principales Tablas de Proscai y de cada una, los campos más importantes.
A continuación te describimos la ventana:
Conforme a la estructura de la base de datos de Proscai se han agrupado en 4 bloques las tablas necesarias para consultas específicas de acuerdo con su uso:
FDOC: incluye las tablas correspondientes a clientes y cuentas por cobrar, proveedores y cuentas por pagar, así como los documentos que los afectan y cada una de las partidas contenidas en ellos.
FAG, FALMCAT, FFAM, FUNIDAD,FCLI,FPRV, FDOC, FINV, FAX, FAXINV
FPED: Incluye las tablas de pedidos, órdenes de compra, órdenes de producción y cotizaciones, así como su relación con los catálogos de clientes, proveedores y productos.
FCLI, FINV, FPENC, FPLIN
FINV: Incluye las tablas de los catálogos de productos, almacenes, ensambles y la información relacionada con ellos.
FCAJAS, FPROV, FUNIDAD, FINV, FALM,FENS
CONT: Incluye las tablas que contienen el catálogo de cuentas, los movimientos de cada una de ellas y los asientos contables generados.
FBENC, FPRV, FPOLIZA, FBANMOV, FAX
En la ventana de trabajo encontrarás el campo FILTR que permite registrar símbolos que sirven como condicionantes para la extracción de los datos, los principales filtros son:
En el campo GROUP podrás agregar un valor para agrupar la información por cualquiera de las dimensiones seleccionadas, según los datos que solicites; y a través del campo ORDER podrás ordenar la información por uno o más campos.
La casilla de verificación SELEC permite deshabilitar campos necesarios para el filtro, pero que no desees que se vean reflejados.
Si deseas borrar una línea, selecciónala y haz clic en el botón Borrar, para borrar todos los elementos de la ventana haz clic simultáneamente en la tecla Shift + botón Borrar.
A partir de las 3 preguntas claves que comentamos al inicio de este artículo, podrás realizar múltiples consultas. Para iniciar vamos a generar una consulta básica para ver el saldo de los clientes.
- ¿Qué datos nos están pidiendo? Saldo actual de los clientes.
- ¿Dónde se encuentran esos datos? En la tabla Clientes.
- ¿Qué requisitos deben cumplir los registros? Ningún requisito.
2. Haz clic en el botón FDOC (1) y selecciona la tabla FCLI (2).
Observa que aparece un listado de campos en la parte inferior de la ventana (3).
3. Haz doble clic en el campo CLICOD, sobre el campo CLINOM y sobre el campo CLISACT (1), estos se irán agregando en la ventana (2).
4. Haz clic en el botón Make Select (3).
Se agrega la sentencia en la parte inferior (1), y además la guarda en memoria para que puedas validarla en MySQL Workbench.
5. Abre MySQL Workbench, pega la sentencia que traes en memoria (1) y ejecútala (2).
Observa el listado (3), también muestra varios clientes cuyo saldo es 0 o muy bajo, razón por la cual vamos a registrar un filtro y que sólo muestre los saldos mayores a $10,000 pesos.
6. Regrese a la ventana Generador de cubos y haz clic en el botón Cambia (1), selecciona la línea CLISACT (2) y en el campo FILTR registra la condición >=10000 (igual o mayor a 10,000 pesos) (3). Haz clic en el botón OK para que se registre la condición (4).
7. Haz clic en el botón Make Select (5). Observa que agrega la condicionante del saldo a la sentencia (6).
8. En MySQL Workbench pega la nueva sentencia (1) y ejecútela (2).
La lista de clientes disminuye considerablemente para este ejemplo (3).
Ahora vamos a indicar el orden en que deseamos ver los datos.
9. Nuevamente ve a la ventana Generador de cubos y haz clic en el botón Cambia (1), selecciona la línea CLISACT (2) y en el campo Order registra el valor 1 (3) y haz clic en el botón OK (4).
Se agrega el valor en la columna Order (5) y con esto indicamos que queremos ver la información en orden ascendente con referencia al saldo.
10. Haz clic en el botón Make Select (6) para crear la sentencia (7).
11. Pega y ejecuta la sentencia en MySQL Workbench.
Los datos aparecen ya en el orden indicado.
Ahora vamos a manejar información de DOS TABLAS y utilizaremos la herramienta SELECT.
12. Siguiendo con el mismo ejemplo, de la tabla FDOC (1) selecciona con doble clic los campos DNUM (Número de documento) y DCANT (Saldo) (2). Haz clic en el botón Make Select (3).
Esto va a presentar todos los documentos de cada cliente, de los ya filtrados cuyo saldo total era mayor o igual a 10,000.
13. Al revisar en MySQL Workbench se observa que aparecen muchos documentos con saldo en ceros y se repite el saldo actual en cada línea.
Ahora se aplicarán unos filtros para que sólo aparezcan de los clientes filtrados anteriormente, los documentos con saldo mayor a $5,000 pesos.
14. Selecciona el campo DCANT (1) y a través del botón Cambia registra el filtro para que sólo muestre los documentos iguales o mayores a $5,000.00 pesos (2). Con esto eliminaremos los documentos pequeños.
15. Ahora selecciona el campo CLISACT Saldo actual (1) y desmarca la casilla Select (2), haz clic en el botón OK (3).
Al desmarcar casilla Select se le está indicando que lo considere (para nuestro ejemplo es que filtre solo los clientes cuyo saldo total es mayor a 10,000 pesos y los ordene) pero que no lo muestre especificado como tal.
16. Observa que automáticamente se registra ahora el valor 0 para el campo DCANT. Haz clic en el botón Make Select (1) para generar la sentencia.
17. Al revisar en MySQL Workbench la sentencia obtenida, observamos solo los documentos de los clientes con saldo actual.
18. Regresa a Proscai y elimina todos los campos de la ventana Generador de cubos.
Otra característica es que algunas tablas presentan campos con * que despliegan información directamente relacionada con el campo seleccionado, pero que pertenecen a otra tabla.
1. Haz clic en el botón de la tabla FINV (1), elige la tabla FINV (2) y agrega con doble clic los campos ICOD e IDESCR (3).
2. Elige la tabla FALM Almacenes (4) y agrega el campo ALMCANT Existencia (5), selecciona ALMNUM -*Número de bodega (6) y oprime simultáneamente la tecla Ctrl + doble clic sobre el campo ALMNUM, de esta forma agregará en automático la descripción del almacén que pertenece a la tabla FALMCAT (7).
3. Oprime el botón Make Select (8).
4. Revisa el resultado en MySQL Workbench.
5. Regresa a Proscai y elimina todos los campos de la ventana Generador de cubos.
Existe otra forma en que se vinculan las tablas y es a través de campos específicos de diferentes tablas que mantienen una relación.
Para este ejemplo utilizaremos de la tabla FINV el campo IPRV y de la tabla FPRV el campo PRVCOD.
1. De la tabla FINV (1) agrega los campos ICOD e IDESCR (2), elige también el campo IPRV que contiene * (3) y con la tecla Ctrl + doble clic en el campo agrégalo, de esta manera podremos ver los productos por proveedor. Observa que el campo IPRV (Código del proveedor) pertenece a la tabla FINV y muestra la descripción desde la tabla FPRV (4).
Si además queremos agregar algunos datos de contacto con el proveedor, por ejemplo teléfono y nombre del contacto, puedes combinar las tablas de la siguiente manera.
2. De la tabla FPRV (1) agrega los campos que requieras, para este ejemplo Teléfono y Contacto (2) y genera la sentencia (3).
3. Prueba la sentencia en MySQL Workbench y observa que muestra los datos solicitados.
Al relacionar diferentes tablas, el sistema te podría enviar un mensaje cuando no existe conexión entre ellas.
Si manejas Color y Talla existe la posibilidad de llamar los diferentes elementos que componen al producto: el código del producto específico (hijo), el producto raíz, el color y la talla.
1. Selecciona la tabla FINV (1).
Observa que el campo ICOD no tiene *, sin embargo desplegará el detalle del producto (2).
2. Simultáneamente presiona la tecla Ctrl más doble clic el campo ICOD para agregarlo (3).
Observa que de forma automática despliega el detalle del producto.
Cuando se utiliza la tabla FDOC Documentos (1) existe la posibilidad de filtrar los documentos a analizar, ya sean de CxC o CxP.
1. De la tabla FCLI (2) agrega los campos CLICOD y CLINOM (3).
2. De la tabla FDOC (4) agrega los campos DNUM y DCANT (5), al utilizar el campo DESCXC (6) si su valor es 1 mostrará los documentos contenidos en el módulo Facturación y cuando su valor sea 2 mostrará los documentos del módulo Recepciones.
De forma automática registra el valor de filtro como 1 (7), genera la sentencia a través del botón Make Select (8).
Nota: Para trabajar con Proveedores, deberás cambiar manualmente el valor a 2.
3. Revise la información en MySQL Workbench.
Ahora vamos a agrupar la información por cliente.
4. Nuevamente en la ventana Generador de cubos, seleccione el campo CLICOD agregado (1).
5. En el campo de filtro GROUP agrega el valor 1 (2) y genera la sentencia a través del botón Make Select (3).
6. Nuevamente revisa el resultado en MySQL Workbench.
Se observa que agrupó por cliente totalizando el saldo, dejando como referencia uno de los documentos del cliente.
Las posibilidades que te ofrece esta ventana son muy amplias y te puede simplificar tu trabajo.
Comentarios
Inicie sesión para dejar un comentario.