Power Tip: Become an early Business Intelligence adopter or fall behind

Today's guest blogger is Access MVP Garry Robinson, who offers the Smart Access collection of articles at http://www.vb123.com/kb.

Excel 2010 is coming and your boss and peers might soon start asking questions about Business Intelligence and Data Mining. This leaves the Access professional looking a little behind the times unless you adapt to this new area. In this article I show you how you can use Microsoft Access 2007 (and 2003) to provide business intelligence and to mine your data. For that, I will illustrate how data filters, pivot graphs, queries in graphs and filters in reports can help this cause. I also suggest that you may want to look into Excel Automation to further your skills.

What will Excel 2010 Provide That's New?

To be honest, Excel has always been a reasonable tool for drilling into data and because there are so many Excel experts, many people have tried. In Excel 2010, you are going to hear buzz words such as data slicers, PowerPivots, improved Pivot Tables and Data Visualization and because Excel can store a lot more data in this version, it has the grunt to do real data studies.

This page is a good start for exploring these terms.
http://blogs.technet.com/office2010/archive/2010/01/28/business-intelligence-features-in-excel-2010.aspx

If you decide to read into those topics, you may decide that this is an area where you (a database expert) can provide good expertise to your company and clients. If so, it looks like Excel 2010 could be an area where you could move into with a short period of learning. For the rest of you, the reality is that Excel 2010 might take a while to find itself onto your desk and earlier versions of Access has some tricks up its sleeve as I will show now.

A Case For Access Business Intelligence

Firstly Access is tightly integrated with the charting tools used in Microsoft Office/Excel.  (See Figure 1).The charting tools used by Access are similar the same as the product used by Excel, the main difference is that Access uses queries where Excel refers to cells.  Secondly Access supports industrial strength SQL queries and through Links, can be used as a client application to any variety of databases ranging from text files, spreadsheets, SharePoint Lists, SQL server, Oracle, MySQL etc.  On top of that, well designed forms & VBA can help you establish an interface that will give your users the confidence to start investigating their data by themselves.

In Access 2007 there have been some new innovations that will help people explore their data. These include the user interface used for data filtering has been made like for like to data filtering in Excel 2007; pivot charts, pivot tables are prominent in the user interface and interactive filters are now available in Access reports.

Figure 1 - A sample of the range of graph styles available in Pivot Charts

Before I start show examples of the different tools, Figure 2 show the sample data that I have used to create the Figures in this article.

Figure 2 - The sample data

Filtering Data In Queries and Forms

So how are we going to use Access as a data mining tool ?   Well the truth is that since Access 95 came out with Filter By Selection and Filter by form for both tables and queries, a basic set of data exploring tools has been available in queries and forms.

For example, Filter by Selection is a primary example of a how you can explore your data.  By right clicking on the year (say 2008) and choosing filter by selection, you will show only sales for that month.  This filter by selection is in essence one of the most important data mining techniques - a drilldown.  It is into this framework that the changes to the filtering user-interface in Access 2007 to match Excel data filtering has made Access a more universal tool. This is demonstrated in Figure 3 where the new filtering interface is demonstrated.

Figure 3 - Filtering in Access 2007 is very similar to Excel

Adding Totals In Queries

If you like filtering data, then there is one great new innovation that will answer many of your data questions and that is Totals. When you have a query open, in the Home Ribbon you will find a Totals button. Click on this and an extra row will be added to your query as shown in Figure 4.  Now you can right click in that new Row and add Sums or Averages or Counts like I have done. For me this innovation is really useful as I was for ever copying and pasting to Excel to compute totals after filtering.

Figure 4 - Adding a Totals row to an Access query reduces the need for extra reports or copying to Excel

Turning Your Queries Into Pivot Tables for Further Investigation

In earlier versions of Access, when you looked at the data in a query, you could do little else in the query interface apart from Sort and Filter. In Access 2007, you now can turn the Query into an instant interactive Pivot grid view with lots of further data exploration options.  If you have a look at Figure 5 you will see the interface that you get when you view the query in this manner. Also when you switch from an ordinary query view to the PivotTable view, the filters that you have already used are maintained.

Figure 5 - Viewing queries in Pivot Table View

Turning Your Queries Into Pivot Charts for Further Investigation

As well as viewing queries directly as Pivot Tables, you can view them as Pivot Charts and use the same field and filtering interface as the PivotTable. Whilst this to is a valuable tool, it will require the user to understand queries to ensure that the data that is being extracted is suitable to the chart views. This is one area where it is good for the Access expert who understands the database model to sit down with the charting user to ensure that they are getting the information in the right way.  Usually just one or two good samples is enough to fire the creative juices of the power user. Also if you tell the power user that the pivots views work the same as Excel Pivots, they will get motivated.

Figure 6 - Viewing queries as charts in PivotChart view

Note that you can incorporate Pivots into Forms and Reports as subforms but you have to set the default view property of the subform to the correct view and allow that type of view.

Filtering Reports On The Fly

When it comes to software, I always find that the best innovations are ones which extend a popular technique rather than ones that force you to understand a new technique. In Access 2007, reports can now be filtered as they are being displayed, this allows you to make your reports more powerful with almost no changes. To make this work, you can bring up the filtering options (which work just like queries) by right clicking on a field in the report as I have done in Figure 7.

Figure 7 - Exploring data by filtering an Access 2007 report

Microsoft Graphs

Now the final way that you can explore data uses Microsoft Graph and this is more the realm of the professional programmer.  With MS Graph, when you have a large amount of data, it is totally impractical to review data as individual records.  So with MS Graph objects, you will almost always use consolidation queries (group by) for two dimensional graphs and crosstab queries which allow you to display the data in three dimensions.

For example, this consolidation query computes the Bottled Sales by Month for "The Americas" and this query can be used to provide the row source for a Microsoft Graph.

SELECT Format([SalesDate],"mmmm") AS Months, Sum(Sales) AS SumSales
 FROM zWorld_Demo
 WHERE (((Region)='The Americas') AND
 ((ProductName)='Bottled Water'))
 GROUP BY Format([SalesDate],"mmmm");

Here is a page of Charting Articles that have featured in Smart Access: http://www.vb123.com/kb/cat_charts.htm

Microsoft Excel

In the last 15 years of programming Access, I have made more money from projects that have been converted from Excel to Access than I have from "Start from scratch" Access projects. In most of those projects and many others, I deploy Excel Automation in Access VBA to report back into Excel templates. Without being too big headed about it, the clients love this approach.  Here is a page that links to a number of the best Smart Access Excel automation articles to get you inspired into this area.

http://www.vb123.com/kb/cat_word_and_excel.htm

Conclusion - So How can you get Intelligent with Your Business data

The trick for professionals to getting these tools to work is understanding the data model, establishing appropriate queries and fitting them into the user interfaces. The trick for Power users is probably to get the developers to setup a good sample of the correct tool with a similar query and then using that as a prototype to explore your data.  What ever your skill level, you will agree after seeing the sexy new features in Excel 2010 that you need to get with the times and help your peers in this area.

Garry Robinson  from Sydney, Australia

Become a guest blogger! Send your Power Tips to Mike and Chris at accpower@microsoft.com.