Learn how to deliver dynamic content by building a meaningful Business
Intelligence Application, utilizing only what is available on the client's
desktop, when a Data Warehouse BI Application, SQL Server and SSIS/SSRS aren't
an option.
Introduction
Recently I was between major contracts and was contacted by an agency that I
have worked for before. They had a department within a major retail bank that
was having difficulties with a Microsoft Access database, which had been created
to allow the department to track the results of customer satisfaction within the
company. As I started life as an Access developer and had a couple of months to
spare before my next major project was due to start. I agreed to look into their
problems.
The database, although well written by an internal resource. was quite
rudimentary in its functionality and was only used to store manual imports from
excel and csv files on a monthly basis into various data tables. Following the
import, the user would then have to follow a set of instructions to amend stored
queries within the database to create meaningful results, which could then be
exported back to excel for the team to format into graphs. Once exported the
data was then used to manually create graphs and tables that would be added to a
dashboard, which was used to present the data to the business. The problems, as
described by the head of the department, were the fact that the database was
slow and required two to three days of intensive work by a non-technical
resource to input the data and then create the reports, which had produced
inconsistent data due to the “human error” factor when amending the queries in
the second step.
After viewing the database, I agreed to hold a meeting with the major
stakeholders to discuss their actual requirements and provide guidance on what
they may actually require. Following the meeting, it was obvious that the
department required the following:
- The ability to store more than the 2 GB limit of MS Access to allow trends
to be forecast from stored data
- Automated upload of delivered files
- Automated production of the required reports including Dashboards and KPI’s
- Automated delivery of the resultant dashboards to the company
At the meeting, I discussed my recommendations and suggested that this
project would be an ideal scenario for a Data Warehouse BI Application utilizing
SQL Server and SSIS/SSRS to deliver dynamic content to the department and
interested parties using a Sharepoint server. The head of the department
discussed my recommendations with IT and asked about the feasibility of a
project to deliver the above; this however proved unsuccessful as the company
was going through a merge and IT was already fully committed to upgrading the
core delivery systems within the company. At this point, the head of department
discussed with me if I thought there was any way I could assist. Following
discussions with a colleague, who I had known from a previous role when I worked
within the IT area, it was established that I could gain access to an instance
of MS SQL server, which led me to believe that I could help. Following further
discussions, I embarked on delivering a BI suite utilizing just those
applications available on the standard desktop along with access to a SQL Server
instance.
Discussion
The main focus of this project was to move the application from a rudimentary
Microsoft Access Database to a fully-fledged application using whatever
applications and tools that were available within the business area.
Investigation of the desktop established that MS Office 2003 Professional was
installed on the desktop of every user along with Adobe Distiller 6.0. This,
along with the availability of an instance of SQL Server 2005, led to the
decision to convert the existing MS Access database to a Microsoft Access
Project connected to a SQL Server backend, which would then utilize VBA and COM
to automate all those manual processes including creation and delivery of the
Dashboard. Utilizing a clean MS Access Project, I connected to an instance of
SQL Server 2005 on the companies’ development box and proceeded to convert the
import routines from the old database into data loads and error checking
routines, using vba and SQL Server stored procedures to check the data on load.
Due to the requirement of no table creation imposed on the company SQL Server,
it was necessary to build permanent load tables to load the data in from the
ADP.
To enable grouped and summed data to be used with the output of the ADP, I
adapted a dynamic pivot routine that I have used before within SQL Server 2005 –
I have included an example below – this provides very similar functionality to
the Cross Tab Query within MS Access.
-- =============================================
-- Author: Peter Evans
-- Author: Peter Evans
-- Create date: 12 Jan 2008,
-- Description: This procedure provides a list of all
-- percent to target scores by division and month
-- for Dashboard creation. It is called from the Front end which is a Access
-- 2002 Front end. Procedure is called using VBA calls.
-- parameters passed in are a non comma sep list of months for
-- year one and year two and both year dates as integer along
-- with channel identifier and concpet identifier
-- data is returned from the View DBoardConceptScore which has a
-- derived field MonthId which provides a sort capability for the
-- pivot table created and also an identifer of month in varchar format
-- =============================================
ALTER PROCEDURE [dbo].[T100output_DBoardConceptPercData]
-- Add the parameters for the stored procedure here
@intChanId smallint, @strRMth1 nvarchar(2000), @intYear1 smallint,
@strRMth2 nvarchar(2000), @intYear2 smallint, @intConcept smallint
AS
BEGIN
DECLARE @colsY2 NVARCHAR(MAX)
SELECT @colsY2 = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],['+ Cast(t2.MonthId as Varchar(9))
FROM CalendarView AS t2 INNER JOIN
dbo.iter_intlist_to_tbl(@strRMth1) AS i ON t2.Month = i.number WHERE Year = @intYear1
ORDER BY '],['+ Cast(t2.MonthId as varchar(9))
FOR XML PATH('')
), 1, 2, '') + '],'
SELECT @colsY2 = @colsY2 + STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],['+ Cast(t2.MonthId as Varchar(9))
FROM CalendarView AS t2 INNER JOIN
dbo.iter_intlist_to_tbl(@strRMth2) AS i ON t2.Month = i.number WHERE Year = @intYear2
ORDER BY '],['+ Cast(t2.MonthId as varchar(9))
FOR XML PATH('')
), 1, 2, '') + ']'
DECLARE @cols2 NVARCHAR(MAX)
SELECT @cols2 = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],pvt.['+ Cast(t2.Monthid as Varchar(9))
FROM CalendarView AS t2 INNER JOIN
dbo.iter_intlist_to_tbl(@strRMth1) AS i ON t2.Month = i.number
WHERE Year = @intYear1
ORDER BY '],pvt.['+ Cast(t2.MonthId as Varchar(9))
FOR XML PATH('')
), 1, 2, '') + '],'
SELECT @cols2 = @cols2 + STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],pvt.['+ Cast(t2.Monthid as Varchar(9))
FROM CalendarView AS t2 INNER JOIN
dbo.iter_intlist_to_tbl(@strRMth2) AS i ON t2.Month = i.number
WHERE Year = @intYear2
ORDER BY '],pvt.['+ Cast(t2.MonthId as Varchar(9))
FOR XML PATH('')
), 1, 2, '') + ']'
DECLARE @query NVARCHAR(MAX)
SET @query = N'
SELECT pvt.Title,' + @cols2 +'
FROM (SELECT t2.Title, t2.MonthId, t2.Concept, t2.PercScore
FROM DBoardConceptScores AS t2
WHERE t2.Concept = ' + cast(@intConcept as varchar (2)) + '
AND t2.ChanFk =' + cast(@intChanId as varchar(1)) + ')p
PIVOT (Sum([percscore]) FOR [Monthid] IN (' + @colsY2 + ')) AS pvt
ORDER BY pvt.Title'
EXEC (@query)
END
Once the data had been imported and saved correctly, it was then down to the
matter of delivering the reports. Allowing the users to select using a form from
the ADP and then using a module within vba to call a stored procedure to create
the data required for the reports removed the “human error” side of the
equation. Once selected, the reports run in the background, creating an excel
version of each report chosen, utilizing vba com calls to open excel on the
clients machine, call an existing template and populate the data using ado
recordsets based on stored procedures . These reports included monthly average
data and results against targets, summary data based on yearly and quarterly
stored and dynamically created data and the monthly dashboard, which gave an
overview of the companies’ performance against not only targets but also their
competition, but utilized automatically produced charts instead of figures.
After the excel reports had been verified by the team they then required the
ability to create pdf versions of the documents to be automatically emailed to
branches, divisions and regions. This was achieved using pdf distiller, which
had been installed on the user’s machines as standard. The emailing of the
reports was achieved by leveraging the COM component of MS Access to talk to an
SMTP server to create the mail item and attach the required reports and then
despatch. The SMTP server was utilized to avoid the recent updates to MS Outlook
security, which would have required a special script to have been written for
the users despatching the mail to prevent the annoying pop up of the security
warning, which would have appeared for each report (at the lowest level this
would be over 700). To achieve a ‘sent’ item in the departments mail box, a copy
was sent to the department group mail box and a rule run on the incoming folder
to transfer mails with a certain subject line into the sent folder of the
mailbox. Along with the produced reports and graphs, the users were also given
the ability to generate reports as Excel files to allow further investigatory
work to be completed.
Conclusion
It is possible with a little creativity and a lot of hard work to provide a
form of Business Intelligence to the broader community without utilizing a Data
Warehouse or any of the normal tools associated with either MOLAP or ROLAP
storage. Using a mix of standard desktop applications and available storage
mediums, a pseudo warehouse has been created which is accessed with SQL stored
procedures controlled from the desktop. It is appreciated that this application
is narrowly focused on one area of business intelligence delivery but is hoped
that the ability to export slices of the stored data tables into MS Excel will
allow the department to deliver extended reports based upon the Dashboard and
KPI’s created. This application has already been up and running within the
company for nearly two years and has had a major impact on how the company deals
with its customers.
Deliverables included:
- Automated upload of excel and cvs delivered data – including data checks for
consistency and completeness of the file being uploaded.
- Conversion of MS Access queries and modules to MS SQL Server 2005 stored
procedures and functions.
- Automated population of stored summarized data tables.
- Leverage of MS Access COM and MS Excel COM abilities to create automated
dashboard production on a monthly basis utilizing 15 individual metrics.
- Leverage of MS Access COM and MS Outlook COM abilities to create automated
monthly distribution including dynamically updateable details to each group of
emails.
By Peter Evans http://www.databasejournal.com/features/msaccess/article.php/3871841/Microsoft-Access-Business-Intelligence-on-a-Shoestring.htm
