Web Analytics Reports Automation with ODBC Driver

WebTrends ODBC (Open Database Connectivity) Driver can be used to retrieve WebTrends Report database. ODBC is a universal database access API. One of the advantages of the WebTrends ODBC Driver is that it can be used directly from the several supported applications:

• Xcelsius from Business Objects
• Microsoft Access
• Microsoft Excel
• Microsoft Query
• DataLinks
• ADO programming interface

You can install WebTrends ODBC driver from WebTrends Ondemand login. You can find this driver from Administration>>Install Components.
In this post we’ll talk about retrieving data directly into Excel Sheet without using any paid tool. Although in market some tools are available to directly refresh the data from WebTrends profiles to excel sheet. One of the tools is Datalinks but it is somewhat costly so for mid size companies or for anyone who try to pull data directly from WebTrends profiles can use the interface of ODBC driver with Excel sheet.

Creating a Data Source Name Connection
The DSN connection allows you to use the ODBC driver to connect to a WebTrends Analytics Report database. Connections to a Report database are profile- and template-specific. Before you create the connection, make sure you have already created a profile for the report data you want to access.

1. To create a DSN Connection to a Report database: 1. Select Start > Settings >Control Panel > Administrative Tools.
2. Double-click Data Sources (ODBC).
3. Click the System DSN tab.
4. Click Add.
5. Select WebTrends ODBC Driver from the list and click Finish. The DSN Configuration dialog box opens.
6. In the Data Source Name text box, type a name for your data source.
7. In the Host Server Name text box, specify the host name for the computer where the WebTrends UI server resides. If you are connecting to WebTrends On Demand, the host name is always ondemandui.webtrends.com.
8. In the Port text box, specify the WebTrends UI server port. By default, the WebTrends UI server uses port 7099 in software installations. If you use WebTrends On Demand set the port to 80. If you use WebTrends On Demand with SSL enabled, set the port to 443.
9. If you are connecting to WebTrends On Demand, type the Account Name for a WebTrends On Demand User Account in the Account Name text box. If you are connecting to a WebTrends software installation, do not change the default name provided.
10. If you use a Windows domain login to connect to WebTrends, click With Windows NT authentication using the network login ID (software only).
11. If you want to connect to the database using a WebTrends user name and password that you specify, click With WebTrends authentication using a user name and password entered by the user and type the user name and password in the text boxes provided. This user name and password pair must be defined in the Users settings of the WebTrends Administration Console (software only).
12. Click Connect to populate the Profile list.
13. Select a profile from the Profile list. This connection only allows you to query data associated with the selected profile.
14. Select a template from the Template list. This connection only allows you to query reports in the selected template.
15. Click OK.
16. Click OK again to save your settings.

You now have the ability to select which profile and template that you want this particular data source to connect to by using the pull down lists in the following screen. Click OK once you have made your selection.

• In Excel go to Data –> External Data –> New Database Query. It will open up a data connection box. Choose new connection and OK. Then find the WebTrends ODBC driver on the list and select it. (For Microsoft Excel 97 – 2003)
• If you are using Excel 2007 then go to Data –> From Other Sources –> From Data Connection Wizard–> Choose ODBC DSN. It will open up a data connection box. Choose new connection and OK. Then find the WebTrends ODBC driver on the list and select it. (For Microsoft Excel 2007)
• Next you’ll have to fill out your connection details. If you’re using On Demand they should be filled in, if select the Profile. You’re using your own software then you can fill in the default login URL and your username and password to connect. Click “refresh” and choose your profile and template from the drop-downs. Click OK to save.
• Now Excel will present you with a list of the available reports. The names will approximate the names you are familiar with from the normal interface, but they’ll be a bit different. Choose the report you want and click next. You can only pick ONE report. The ODBC connection doesn’t do Joins.
• Continue through the wizard. You can filter and sort on pretty much whatever you want.
• When you finish the wizard Excel will crunch for a while and then it will ask you
where you want to place the data in your spreadsheet. Choose a cell and the data will automatically appear.
• If you click in the data area and then click on the exclamation point in the
External Data toolbar or click on Data and then Refresh Data Excel will reach back out to WebTrends and pull down fresh data.