Setting up an ODBC Data Connection in Microsoft Windows

Someday you may find yourself needing to set up an ODBC data connection in Windows.  Depending on your reporting tool and the database you need to access, on ODBC connection may be required to connect your database to the reporting tool.  This can feel overwhelming, but it is actually straightforward and only a few steps.

In Microsoft Windows, you need to open the Control Panel.  Depending on your version of Windows, there are different ways to do that.   In Windows 8, you will find it under Settings in the right-side menu.

Once your Control Panel is Open, double-click on Administrator Tools.  

In the Administrator Tools window, you will see “ODBC Data Sources”.  If you are using later versions of Windows, you will have to choose either 32 bit or 64 bit.  Double-click to choose the one you need.

In the ODBC Data Source Administrator window, click the Add button.

Next, you will see a list of available drivers.  In this case, this PC has only one driver available, for SQL Server.  You will want to select the driver that matches the type of data you need to access.  Select the driver, and click Finish.

Next you will need to tell Windows where to find the data.  The name of the data source should match the name of the database in most cases.  Select the server from the dropdown list.  Click Next.

If you have a single-sign-on using Windows authentication to access the data, select use Windows.  If not, select  authentication specifically for that database and enter the User ID and Password below.  Some databases may not require authentication at all.  Click Next

Clicking next will test the connection and will let you know if any errors occur.  The next step is to use your new ODBC connection in your software.  If you have any issues going forward, come back to the ODBC Data Source Administrator in Control Panel, select the source and choose Configure to modify the settings.

Wishing you happy database reporting!!

Joining Data from Multiple Tables: Link Types Explained

Relational databases store data efficiently across many tables related to each other by common fields or columns. Reporting on relational databases requires some knowledge about those relationships and columns in order to link tables together in a way that will give you accurate results. Here are a few examples of 1:many relationships:

•    1 Sales Professional is authorized to sell many products 
•    1 Company has many contacts in different departments
•    1 police or fire call has many units responding

There are several tools available for reporting on relational databases.  Regardless of the reporting tool, you need to first understand the way the tables are set up and what common field connects or joins the information in those tables together.  Once you know that, you can decide what style of link or join will meet your needs.

For the purpose of explaining, I will describe two tables the “Main | Left” table and the “Many | Right” table and have designated examples of these in scenarios below.

Inner Join

Inner Joins are often the default setting in the reporting tool.  This returns records from both tables only if they have a match in each table.  Inner joins are designed for occasions where you only care to see data where there are matches present in both tables.  Because it is default, it is the most commonly used, but it can cause miscounts if records are unexpectedly left out of the results when no match is found.  

Outer Joins are designed for occasions when you need to see data, even if it does not match anything in the other table.  We are given the option of the Left Outer Join, the Right Outer Join and the Full Outer Join.

Left Outer Join

The Left Outer Join displays all records from the left main table and only the matching records from the right many table. This is the safest method for joining two tables where full record counts are required, even if data is missing, and for this reason, it is also very commonly used. 

Right Outer Join

The Right Outer Join displays all records from the right table and only the matching records from the left table.  This style of linking is not as common.  In most relational databases, there isn’t much opportunity for a Many table to have a record without an associated Main table record.  For this reason, right outer join can be used to identify potential problems in the database.

Full Outer Join

The Full Outer Join will show all records from both tables regardless of a match. This style of linking is also not as common.  I have seen this used primarily in HR/Finance for error checking and reconciliation.  For example, it’s sometimes necessary to identify instances where there is a payroll record without an employee 401k contribution or a employee 401k contribution without an accompanying payroll record.  This form of join lets you quickly identify problem records and make corrections.

In Summary

When in a hurry, the safest option is always the left outer join where the left table is the main table and the right table is the many table.   This will ensure that you have all of your main records present plus the associated matching records from the many table.  When you need to find errors, try a full outer join to see where matches are missing.  

There’s no harm in linking different ways and looking at results until you find the right join strategy for your tables.  The most important ingredient of any report is data familiarity, and experimenting with link types can help you learn more about the way your data is setup.  

Web Data Decisions for Auditing and Compliance: 4 Areas for Consideration

A good web application provides good experience on the front end for customers and the back end for web managers.  Project funding tends to focus on the front end experience primarily, but it’s really the back end that can break a business when something goes wrong.  

• A customer calls and claims they were given last year’s terms and conditions.  
• A product manager says IT uploaded the wrong PDF.  
• A customer says they never logged in to authorize that payment.  
• A customer says that the data they saw was not theirs.

Careful planning now will make auditing requests like these much less painful in the future.  

First, take steps to enhance security for your portal database.  Close all of the possible unauthorized ports of entry.  Ensure that firewalls are strong.  Check the latest security recommendations and do all that’s possible to meet each one.  Are there features to require additional authentication under circumstances where the user is out of his or her normal area or has failed some login attempts?

Now, check the design of the portal database to be sure that there are adequate auditing tables available.  How are transactions tracked? Is there a session detail record to view all activity during a session?  Is the user’s IP addresses from sessions recorded so that likelihood of identity can be considered?   A well-designed web portal database is an important part of auditing because it can include details of every transaction and activity by user, including text entered where necessary.  This is very helpful when a user calls into the call center with concerns about his or her account.

Next, prepare your Web Analytics software for auditing capabilities.  Work with your web analytics professional to pass an unintelligible user ID key to the web analytics software for logged in users and create a custom report to record pages and downloads by this user key.  This simple step is a godsend when something goes wrong, like a PDF or web page has an error in it and you need to quickly identify who has seen this content so that a correction can be made to that group of users.  This is extremely valuable from a risk management perspective.

Now, take an inventory of the tools you have.  Do you have any session recording capabilities?  It is great to have access to a tool that will allow you to replay any session in question to see exactly what pages were viewed and what was accomplished.  Most companies do not have access to these tools for their web properties, but tools like these can be very helpful for auditing, especially in industries that are strictly regulated.

After some preparation, answers to tough auditing questions become more routine.

• A customer calls and claims they were given last year’s terms and conditions.  Retrieve that customer’s encrypted ID, open the web analytics reporting and query which PDFs were viewed.  
• Then, a product manager says IT uploaded the wrong PDF.  In the web analytics package, query that PDF and view which customers, if any, saw the incorrect PDF so that the product manager can draft a letter to those customers.  
• Now, a customer says they never logged in to authorize that payment.  Use your web portal database to see details of that transaction, verify the session details to see if there was a chance the account was compromised.  
• If a customer says they saw data from another customer and you have a session recording tool, check the tool to see what happened.  If not, look into the web portal database session activity to see if any wires might have been crossed.

With careful planning and a few simple steps, you will be prepared for these and any other questions that come up in the future.  

Product Managers: Building Good Measurement into your Web Applications

With so much focus on appearance and functionality, web portal data decisions can be easily overlooked at a time when performance metrics are more crucial than ever.  As more functionality is added, good data collection is needed for product management.

Product managers need to understand how the site is being used to inform the product roadmaps and order website changes as needed.  There are two different building blocks for measuring the web performance and both are needed to achieve a complete understanding of the online customer: web analytics and web portal database.

Web Analytics

Web analytics tells you about the online behavior of the customer by painting a picture of the most popular content and how a customer moves through the website to accomplish tasks.  There is a wealth of information in the basic analytics reports found in web analytics packages like Google Analytics, WebTrends,  Site Catalyst, and CoreMetrics.  Even more information on fall out from web form fills, clicking to off-site links, and other detailed task and funnel behavior can be tracked with additional planning and page-specific javascript tags or URL parameters.

Web analytics is fundamental to all web properties, but is also commonly overlooked during the development process.  In order to determine the impact of the redesigned property, getting the proper code in place early in the project is essential.  The basic requirement is a snippet of code often referred to as a base tag.  Depending on the analytics package and the company policies, this is either a direct copy/paste or include to the page header or page footer.  This code is the engine that communicates page activity to the analytics package.  For advanced reporting and specialty metrics, a web analytics professional can provide code to be included within the content of the page to provide additional performance metrics.

Getting the basics is easy.  Often it only requires mentioning that you want the tracking installed, and in some cases, it is standard and automatically included with no effort at all.  For this reason, tracking is often taken for granted, and taking web analytics for granted is a huge mistake.  I have seen many cases at companies large and small where sites have gone live with no tracking because it wasn't mentioned in the business requirements. Without metrics, the product manager was not able to demonstrate the success of the product.

Add tracking requirements to the standard requirements template to be used in every web project and open discussions with web analytics professionals in your company at the start of the project.  This will ensure you can avoid KPI pitfalls when work is complete.

Portal Database

Make the most of the web portal database behind the application, used to deliver information to the page.  These databases run the application functionality of website, processing transactions, recording changes to user profiles, error messages delivered, logins, etc.  Because of this, they hold a wealth of information on how the site is being used.  While web analytics delivers web content and behavior details, the web portal database delivers information on adoption and usage at a transaction level.

The types of metrics you can expect to gain from the web portal database include: percent of users logged in in the last 90 days, login frequency, login duration, average transactions per person, total transactions, revenue, etc.   

The most common issue is overlooking the requirements for the performance indicators (KPIs) during the design and development of the database.  To be sure that the portal database will deliver the metrics you need, take an hour at the start of a project to brainstorm all possible performance indicators that my be needed in the future and discuss with the IT Architect and DBA on our team.  It is much easier to include these in the database design at the start than it is to make a change along the way.

Summary

The key to success is timing a three-step process: (1) consider the data you will need at the beginning of the redesign, (2) collect product management requirements early and (3) coordinate with DBA and Web Analytics professionals throughout the course of the project.  Early and continual involvement will ensure you are collecting good data from the start and can report your success measure KPIs along the way.