The Atlas Query Add-in for Microsoft Excel
The Atlas Query Add-in for Microsoft Excel is a simple interface to execute queries against web-accessible databases and return results into Excel.
When you open the addin, you are presented with the following introductory screen:
This screen has the following four options:
1. Access Your Data Now: This button takes you into the addin where you can access your online data.
2. Learn How to Use Atlas Query: This button opens a web page with instructions about how to use the addin.
3. Learn about data.world: This button opens a web page at data.world showing information about the services provided by data.world.
4. Learn about airtable.com: this button opens a web page at airtable.com showing information about the services provided by airtable.com.
The Query Card
When you click on "Access Your Data Now", you will see a query card. Query cards have with the following six components:- Query area
- Worksheet name
- Duplicate query button
- Run query button
- Delete button (only visible when there are two or more query cards showing)
- Expand button
1. Query area: You will compose your Structured Query Language (SQL) query in the query area. Although SQL is a standard language for querying relational data, each implementation varies slightly. The SQL variant you will use depends on which connection you are using. More on choosing different connections below.
2. Worksheet name: When you execute a query, the data returned will be placed on a worksheet with the name specified here. If this is blank, data are written to a worksheet named "Query Results."
3. Duplicate query button: Clicking this button will create a copy of the current query card.
4. Run query button: Clicking this button will execute the query.
5. Delete query button: Clicking this button will close the query card.
6. Expand Button: Clicking this button will expand the query card to show additional properties as seen here:
Additional Query Properties
Record Limit: The "Record Limit" property restricts how many records will be returned to Excel. This is independent of the number of records the query specifies. That is, if the "customer" table has 4,000 records and a query requests all customer records, even though 4,000 records will be returned to the add-in server in the cloud, it will only send back the number specified as the record limit. The choice "Maximum" limits the number of records to that which will fit on an excel sheet—more than one million.
Connection: The add-in supports four kinds of connections:
- Demo
- data.world
- Airtable
- MS SQL Server
Demo Database
The add-in comes with access to data from a fictional on-line shoe retailer: Redcat Shoes. The database is held in MS SQL Server 2012 and provides read-only access to the data. This is the default connection and no configuration is required, so you can just click the "run query" button when the add-in opens an you will get the results of the sample query. Here is a database diagram of the demo database.
Data.world
Data.world is home to the world's largest collaborative data community, which is free and open to the public. Data.world provides a platform for executing SQL queries against data that has been loaded to their site. Selecting the "data.world" option from the available connections on a query card will reveal the properties required to execute a query against a data.world data set.
To execute a query against a data.world data set, you will need to specify the data owner and data set name of the data you want to query. You will also need to supply your authentication token, which identifies you to data.world and allows access to any private data sets to which you have access.
The add in defaults to show the Redcat Shoes dataset owned by the data.world user named "atlas-query." This is a publicly available data set with the same schema as the demo data shown above. To access this data you will need a free account at data.world. Once you have created your account, you can get you access token at: https://data.world/settings/advanced. Copy either of the two keys presented and past it into the "Token" property. You will be ready to execute queries on data.world.
A note about specifying your own data.world data sets
To access other data sets on data.world, you will need to specify the data owner and data set as they appear in the URL of the data set on data.world. When you navigate to a data set on data.world, examine the URL in your browser. It will appear something like this:
Here you can see that although the name of the data set is "Redcat Shoes" the name as it appears in the URL is "redcat-shoes." You can also see the owner name in the URL is "atlas-query." Thus, in configuring access to this data set, we have specified atlas-query and redcat-shoes as the data owner and data set.
To access your data, you will need to provide an API key and a Base ID. The API key identifies you and authorizes your access. You Airtable API key is available at https://airtable.com/account. The Base ID identifies the Airtable database that holds the tables you are accessing. The base id can be found by going to the Airtable API page then selecting your base.
Airtble does not provide a fully developed query language; however, one can restrict which rows are loaded from a particular table using a formula. Once you have made an initial import of the table, you will see a list of the fields in the table in the query card as shown below.
You can use the check-boxes to indicate which columns should be included in the result. Each row in the list of fields provides a text box to specify a new name for the column.
This connection is made from the add-in's cloud server. That is, your copy of Excel will communicate with the Atlas Query add-in, the add-in will communicate with its server in the cloud, that server will communicate with your database according to the properties configured here. This means that the SQL Server you are communicating with must be accessible from the web. In most cases, you will need to configure you firewall to allow incoming requests from 143.95.108.142.
Airtable
Airtable is an online data management platform that allows users build and manage data in a collaborative environment. It is designed to be as easy to use as a spreadsheet but also has many of the features common to more comprehensive database management systems. Airtable has a free tier with allows try out and use the system for small projects. Selecting the "data.world" option from the available connections on a query card will reveal the properties required to execute a query against a data.world data set. Selecting the "Airtable" option from the available connections on a query card will reveal the properties required to execute a query against an airtable table.
To access your data, you will need to provide an API key and a Base ID. The API key identifies you and authorizes your access. You Airtable API key is available at https://airtable.com/account. The Base ID identifies the Airtable database that holds the tables you are accessing. The base id can be found by going to the Airtable API page then selecting your base.
Airtble does not provide a fully developed query language; however, one can restrict which rows are loaded from a particular table using a formula. Once you have made an initial import of the table, you will see a list of the fields in the table in the query card as shown below.
You can use the check-boxes to indicate which columns should be included in the result. Each row in the list of fields provides a text box to specify a new name for the column.
MS SQL Server
The MS SQL Server connection is available for you to specify access to your own instance of Microsoft SQL Server.This connection is made from the add-in's cloud server. That is, your copy of Excel will communicate with the Atlas Query add-in, the add-in will communicate with its server in the cloud, that server will communicate with your database according to the properties configured here. This means that the SQL Server you are communicating with must be accessible from the web. In most cases, you will need to configure you firewall to allow incoming requests from 143.95.108.142.
