Microsoft Access: Inteligencia de negocios en una cinta de zapatos


Aprenda cómo entregar contenido dinámico mediante la construcción de un Business Intelligence Aplicación significativa, utilizando sólo lo que está disponible en el escritorio del cliente, cuando un almacén BI Application Data, SQL Server y SSIS / SSRS no son una opción.
Introducción
Hace poco estaba entre los principales contratos y se puso en contacto por una agencia que he trabajado para antes. Tenían un departamento dentro de un banco minorista importante que estaba teniendo dificultades con una base de datos de Microsoft Access, que había sido creado para permitir que el departamento para hacer un seguimiento de los resultados de satisfacción del cliente en la empresa. Como ya he comenzado la vida como un desarrollador de Acceso y tenía un par de meses de sobra antes de mi próximo gran proyecto debía comenzar. Estuve de acuerdo para estudiar sus problemas.
La base de datos, aunque bien escrito por un recurso interno. era bastante rudimentaria en su funcionalidad y sólo se utiliza para almacenar las importaciones manuales de archivos de Excel y CSV una vez al mes en varias tablas de datos. Después de la importación, el usuario tendría que seguir una serie de instrucciones para modificar las consultas almacenadas en la base de datos para obtener resultados significativos, que luego podrían ser exportados a Excel para volver al equipo a formato a las gráficas. Una vez exportados los datos se utilizó para crear manualmente gráficos y tablas que se pueden agregar a un panel de control, que se utiliza para presentar los datos de esta empresa. Los problemas, según lo descrito por el jefe del departamento, fueron el hecho de que la base de datos era lento y requiere dos o tres días de intenso trabajo por un recurso de carácter no técnico para introducir los datos y luego crear los informes, que se había producido datos contradictorios debido al factor de “error humano” al modificar las consultas en el segundo paso.
Después de ver la base de datos, estuve de acuerdo en celebrar una reunión con las principales partes interesadas para discutir sus necesidades reales y ofrecer orientación sobre lo que realmente lo requieran. Después de la reunión, era obvio que el departamento requiere lo siguiente:
• La capacidad de almacenar más que el límite de 2 GB de MS Access para permitir que las tendencias que se prevén a partir de datos almacenados
• Automatizado de subida de archivos entregados
• La producción automatizada de los informes requeridos, incluyendo cuadros de mando y KPI de
• La entrega automática de los tableros resultantes para la empresa
En la reunión, hablé de mis recomendaciones y sugirió que este proyecto sería un escenario ideal para un almacén de datos de aplicaciones de BI utilizando SQL Server y SSIS / SSRS para entregar el contenido dinámico para el departamento y las partes interesadas mediante un servidor Sharepoint. El jefe del departamento de mis recomendaciones discutió con él y le preguntó acerca de la viabilidad de un proyecto para ofrecer lo anterior, lo que sin embargo no tuvo éxito ya que la empresa estaba pasando por una fusión y que ya estaba plenamente comprometido con la actualización de los sistemas de administración de base dentro de la empresa . En este punto, el jefe del departamento discutió conmigo si yo pensaba que había alguna manera de que pudiera ayudarle. Tras las conversaciones con un compañero, a quien había conocido en un papel anterior cuando trabajaba en el área de IT, se estableció que podía tener acceso a una instancia de servidor de MS SQL, lo que me llevó a creer que podía ayudar. Tras una nueva negociación, me embarqué en la entrega de una suite de BI utilizando sólo las aplicaciones disponibles en el escritorio estándar junto con el acceso a una instancia de SQL Server.
Discusión
El objetivo principal de este proyecto es establecer la aplicación de un rudimentario Microsoft Access Database a una aplicación de pleno derecho con lo que sea aplicaciones y herramientas que estaban disponibles en el área de negocios. Investigación del escritorio estableció que MS Office 2003 Professional se instala en el escritorio de cada usuario, junto con Adobe Distiller 6.0. Esto, junto con la disponibilidad de una instancia de SQL Server 2005, condujo a la decisión de convertir la base de datos de MS Access existente a un proyecto de Microsoft Access conectado a un servidor SQL Server, que luego utilizan VBA y COM para automatizar todos los procesos manuales incluyendo la creación y la entrega del Dashboard. Utilizando un proyecto de MS Access limpio, me conecta a una instancia de SQL Server 2005 en el cuadro de desarrollo de las empresas y procedió a convertir las rutinas de importación de la antigua base de datos en cargas de datos y comprobación de errores rutinas, utilizando VBA y procedimientos almacenados de SQL Server para comprobar los datos sobre la carga. Debido a la exigencia de no creación de la tabla impuesta a la empresa de SQL Server, que era necesario construir tablas de carga permanente para cargar los datos en el ADP.
Para permitir que los datos agrupados y se suman para ser utilizados con la salida de la ADP, adapté una rutina de pivote dinámico que he utilizado antes en SQL Server 2005 – He incluido un ejemplo más abajo – esto proporciona una funcionalidad muy similar a la consulta de la lengüeta dentro de la Cruz MS Access.
– =============================================
– Autor: Peter Evans
– Autor: Peter Evans
12 de enero 2008,: – Fecha de Creación
– Descripción: Este procedimiento proporciona una lista de todas las
– Ciento para apuntar las puntuaciones por la división y mes
– Para la creación de paneles. Se llama desde el extremo delantero que es un acceso
– 2002 el frontal. El procedimiento se llama mediante llamadas VBA.
– Parámetros pasados son una coma septiembre listado no de meses para
– El primer año y segundo año y los dos años las fechas como enteros a lo largo de
– Con identificador de canal y concpet identificador
– Los datos se devuelve desde el DBoardConceptScore Vista que tiene un
– MonthId campo derivado, que proporciona una capacidad de ordenación de la
– Tabla dinámica creada y también un identificador del mes en formato varchar
– =============================================

ALTER PROCEDURE [dbo]. [T100output_DBoardConceptPercData]
– Añadir los parámetros para el procedimiento almacenado aquí
@ IntChanId smallint, @ strRMth1 nvarchar (2000), @ intYear1 smallint,
@ StrRMth2 nvarchar (2000), @ intYear2 smallint, @ intConcept smallint

COMO
EMPEZAR

DECLARE @ colsY2 NVARCHAR (MAX)
SELECT @ colsY2 COSAS = ((SELECT DISTINCT TOP 100 POR CIENTO
‘], [‘ + Cast (t2.MonthId como Varchar (9))
DE CalendarView COMO t2 INNER JOIN
dbo.iter_intlist_to_tbl (@ strRMth1) AS i EN t2.Month = i.number DONDE Año = @ intYear1
ORDER BY ‘], [‘ + Cast (t2.MonthId como varchar (9))
FOR XML PATH (”)
), 1, 2,”) + ‘],’

SELECT @ colsY2 = @ colsY2 + COSAS ((SELECT DISTINCT TOP 100 POR CIENTO
‘], [‘ + Cast (t2.MonthId como Varchar (9))
DE CalendarView COMO t2 INNER JOIN
dbo.iter_intlist_to_tbl (@ strRMth2) AS i EN t2.Month = i.number DONDE Año = @ intYear2
ORDER BY ‘], [‘ + Cast (t2.MonthId como varchar (9))
FOR XML PATH (”)
), 1, 2,”) + ‘]’

DECLARE @ cols2 NVARCHAR (MAX)
SELECT @ cols2 COSAS = ((SELECT DISTINCT TOP 100 POR CIENTO
‘], Pvt. [‘ + Cast (t2.Monthid como Varchar (9))
DE CalendarView COMO t2 INNER JOIN
dbo.iter_intlist_to_tbl (@ strRMth1) AS i EN t2.Month = i.number
DONDE Año = @ intYear1
ORDER BY ‘], pvt. [‘ + Cast (t2.MonthId como Varchar (9))
FOR XML PATH (”)
), 1, 2,”) + ‘],’

SELECT @ cols2 = @ cols2 + COSAS ((SELECT DISTINCT TOP 100 POR CIENTO
‘], Pvt. [‘ + Cast (t2.Monthid como Varchar (9))
DE CalendarView COMO t2 INNER JOIN
dbo.iter_intlist_to_tbl (@ strRMth2) AS i EN t2.Month = i.number
DONDE Año = @ intYear2
ORDER BY ‘], pvt. [‘ + Cast (t2.MonthId como Varchar (9))
FOR XML PATH (”)
), 1, 2,”) + ‘]’

DECLARE @ query NVARCHAR (MAX)
SET @ query = N ‘
SELECCIONAR pvt.Title, ‘+ @ cols2 +’
FROM (SELECT t2.Title, t2.MonthId, t2.Concept, t2.PercScore
DE DBoardConceptScores como T2
DONDE t2.Concept = ‘+ cast (@ intConcept como varchar (2)) +’
Y t2.ChanFk = ‘+ cast (@ intChanId como varchar (1)) +’) p
PIVOTE (Suma ([percscore]) Para [Monthid] IN (‘+ @ colsY2 +’)) AS PVT
ORDER BY pvt.Title ‘

EXEC (@ query)

FIN

Una vez que los datos se habían importado y guardado correctamente, fue luego a la cuestión de la entrega de los informes. Permitir a los usuarios seleccionar mediante un formulario de la ADP y luego usando un módulo dentro de VBA para llamar a un procedimiento almacenado para crear los datos necesarios para los informes extraídos del lado “error humano” de la ecuación. Una vez seleccionados, los informes se ejecutan en segundo plano, la creación de una versión en Excel de cada informe seleccionado, utilizando vba com llama para abrir excel en la máquina del cliente, llame a una plantilla existente y rellenar los datos con registros ADO basado en procedimientos almacenados. Estos informes incluyen datos medios mensuales y los resultados contra los objetivos, los datos resumidos sobre la base de los datos almacenados y creados dinámicamente anuales y trimestrales y el salpicadero mensual, lo que dio una visión general de los resultados de las empresas en contra no sólo los objetivos, sino también su competencia, pero utilizan produce automáticamente cartas en vez de figuras.

BI3

BI1

Después de los informes de Excel han sido verificados por el equipo que se requiere la capacidad de crear versiones PDF de los documentos que se han enviado por correo electrónico automáticamente a sucursales, divisiones y regiones. Esto se logró usando pdf, los que se habían instalado en las máquinas del usuario como estándar. El envío por correo electrónico de los informes se logró mediante el aprovechamiento del componente COM de MS Access para hablar con un servidor SMTP para crear el elemento de correo y adjuntar los informes requeridos y después de envío. El servidor SMTP se utiliza para evitar que las recientes actualizaciones de seguridad de MS Outlook, lo que habría requerido un script especial que ha sido escrito para los usuarios de despachar el correo para evitar que los molestos pop de la advertencia de seguridad, que habría aparecido por cada informe (en el nivel más bajo de este sería de más de 700). Para lograr un elemento de ‘enviado’ en el buzón departamentos, se envió una copia a la casilla de correo de grupo de departamentos y una regla de ejecutar en la carpeta de entrada para transferir mails con cierta línea de asunto en la carpeta de enviados del buzón. Junto con los informes y gráficos elaborados, los usuarios también se les dio la posibilidad de generar informes en formato Excel para permitir más trabajo de investigación se complete.
Conclusión
Es posible con un poco de creatividad y mucho trabajo duro para proporcionar una forma de inteligencia de negocios para la comunidad en general y sin la utilización de un almacén de datos o de cualquiera de las herramientas normales asociados ya sea con o almacenamiento MOLAP ROLAP. Usando una combinación de aplicaciones de escritorio estándar y medios de almacenamiento disponibles, un almacén seudo ha creado la que se accede a los procedimientos almacenados de SQL controlados desde el escritorio. Se aprecia que esta aplicación se centra casi exclusivamente en un área de la entrega de inteligencia de negocios, pero se espera que la capacidad de exportación de las rebanadas de las tablas de datos almacenados en MS Excel le permitirá al departamento de entregar informes extendidos basa en el cuadro de mandos y KPI ha creado. Esta aplicación ya ha estado funcionando en la empresa desde hace casi dos años y ha tenido un gran impacto en cómo la empresa trata a sus clientes.
Prestaciones incluidas:
• Automatizado de subida de excel y cvs entrega de datos – incluidos los controles de datos para la coherencia e integridad de los archivos que se cargan.
• Conversión de las consultas y los módulos a 2.005 procedimientos almacenados y funciones MS SQL Server MS Access.
• Población automática de tablas de datos almacenados resumidos.
• Las reglas del juego de MS Excel capacidades COM COM MS Access y crear la producción de tablero automatizado una vez al mes utilizando 15 indicadores individuales.
• Las reglas del juego de MS capacidades de Outlook COM para crear distribución mensual automatizado incluyendo dinámicamente datos actualizables a cada grupo de correos electrónicos de MS Access y COM.

Por Peter Evans http://www.databasejournal.com/features/msaccess/article.php/3871841/Microsoft-Access-Business-Intelligence-on-a-Shoestring.htm

Responder

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