OpenData

OpenData is an optional TrackAbout feature that lets you write your own SQL queries to retrieve TrackAbout data from a simplified, read-only view of your TrackAbout database.

Providing a Required Static IPv4 Address

To get access to your OpenData database, you must first provide TrackAbout with a static IPv4 address. IPv6 addresses are not accepted.

Please read the following article to learn the difference between a static and a dynamic IP address: Static vs. dynamic IP address

Static means "unchanging". In order to provide access to OpenData, you must give TrackAbout your static IPv4 address so that we may add a rule to our firewall to allow you to connect.

If the IP address you provide TrackAbout is not static, or changes for any reason, you will lose the ability to connect to OpenData.

Your SQL queries must always originate from this static IP address.

If you are unsure whether your company has a static IP address, you will need to ask your IT department or your company's Internet Service Provider (ISP). Most business-class Internet accounts include at least one static IP.

If you are certain your company has a static IP address, but you're not sure what it is, try the following:

• Be on a computer or device on your corporate network (wired or WiFi).

• Open a browser and Google What's my IP (or click that link, which will Google it for you).

Most users of OpenData intend to automate data retrieval into some other local database. Make sure the computer that is doing the retrieval exits onto the Internet using the same static IP address.

Connecting to Your Database

Once you are registered for OpenData, TrackAbout will communicate the following important connection details by email:

• Azure SQL Server network address

• Database name to connect to

• SQL username

• SQL password

You are responsible for securely storing these credentials. If you lose the password, TrackAbout will have to reset and securely send you the new password. This may be disruptive to services you have created which are actively querying the database.

Your database is hosted in Microsoft Azure SQL Database.

There are many tools that can connect to Azure SQL Database. We recommend Microsoft's SQL Server Management Studio (SSMS). SSMS is a standalone, free product from Microsoft. Also see the article Azure SQL Database: Use SQL Server Management Studio to connect and query data.

How to Connect Using SQL Server Management Studio

PLEASE READ THIS: You MUST explicitly specify the database name in SSMS on the Connection Properties tab of the connection dialog. Failing to follow this instruction is the number one reason customers call with problems connecting to OpenData. Having to enter the database name explicitly is different from how highly privileged users who have access to the master database are used to connecting. You will not have access to the master database in our environment, and therefore you MUST explicitly specify your database name.

Open the Connect to Server dialog.

Choose SQL Server Authentication from the Authentication dropdown.

Enter the provided username and password.

Click Option, then the Connection Properties tab and put your database name in the Connect to database field.

 

Click Connect

Expand the tree in Object Explorer. You should see the following:

Try a Query

• Right-click on your database and select New Query

• SELECT TOP 10 * FROM opendata.assets

You should get 10 rows back.

Limitations and Expectations

Allowed Connections

It is TrackAbout's expectation that customers will be connecting to their database only from within their company network from their known static IP address, and not from several different geographic locations having different IP addresses. We can work with you if you have several static IP addresses from where you wish to query, but we cannot support dynamic (changing over time) IP addresses, such as those provided to consumer-class or home Internet services.

Read-Only

The data views are read-only by design. You cannot write to this data. Writing is not a planned feature.

Query Volume

It is our expectation that you will not abuse the database by re-running very large or expensive queries frequently. The database platform has finite resources, and abuse will impact other users. TrackAbout will be monitoring usage and will throttle or slow down users who abuse their databases.

Local data caching (copying data to a local database or temporary table) is recommended if you need to re-query the same data repeatedly.

Acceptable Use

OpenData is intended to be used by a small handful of data professionals within your organization, generally to create reports, visualizations or other business intelligence value.

OpenData is NOT intended to be called from multiple devices, multiple geographic locations or apps, in general.

If you wish to use the data made available in OpenData more broadly, for example in a context like a mobile app, we recommend you extract the needed data on a periodic basis (and please be mindful of the load you may be creating), transform and load the data into another data system that is more appropriate for your use case.

Dates are UTC

All dates in TrackAbout are stored in the Universal Coordinated Time (UTC) time zone. You likely will want to convert these times to the time zone of your business headquarters where you do your billing or reporting.

Microsoft added two very useful new commands to SQL that make time zone conversions easy. We highly recommend reading up on:

AT TIME ZONE

SWITCHOFFSET

Frequently Asked Questions