This document will show you how to restore a SBS Analytics database to a local machine and connect to it via Microsoft Excel over ODBC. This document is intended mostly for hosted customers who do not have direct access to their database, though non-hosted customers may also find some information useful
This document assumes that you have the following software and artifacts available:
- Microsoft Windows*
- PostgreSQL 8.3 or newer installed.
- Microsoft Excel with Microsoft Query (included in all MS Office distributions)
- PostgreSQL ODBC driver installed.
- An Analytics database backup in .dmp form
Restoring the database
Users who are more familiar with Postgres and psql may find it quicker to use the command line. For instructions, please refer to http://www.postgresql.org/docs/8.3/static/backup-dump.html#BACKUP-DUMP-RESTORE.
If you feel more comfortable using the GUI, then the pgAdmin tool is for you. Start the application, and double click on the localhost server. You will be prompted for the password for the postgres user. This is the password you chose when installing Postgres locally.
Next, right click on "Databases" and select "Create New".
You can give the database whatever name you like. All other settings can be left alone.
Then, right click on your new database and select 'Restore...':
Point the file browser to the location of your .dmp file. You will need to change the file type view to show All Files.
After clicking OK, the restore will commence and the database will be populated with the data from the dump file. Afterwards, you should see the Analytics schema and data installed in your database:
Connect with Excel and Query
The Open Database Connectivity interface is designed to allow applications to programatically access databases. For our purposes, it gives us the ability to dynamically import raw Analytics data from the database directly into an Excel spreadsheet. From there, you can apply any level of charting, quantitative analysis, and data manipulation you need to suit your business needs. This section will offer a quick overview to getting your Analytics data into Excel.
First, open a new spreadsheet. Under the Tools menu, you'll see an option to "Import External Data". Highlight that option, and select "New Database Query".
Choose "New Data Source" and click OK. Then you will be prompted to enter details about the Postgres connection. Give the connection a name, and choose the PostgreSQL Unicode driver you installed previously. Then you will want to enter the connection details for the database you configured in the previous step:
After you click OK, you should be connected to your database! Then, Microsoft Query will be launched. We won't get into the many options available in Query. Instead, we'll skip to running a SQL query and importing the data into Excel. Click the SQL button, enter your query, and click OK:
After the query completes, select File > Return Data to Microsoft Office Excel, and the results of your query will be exported to Excel!