So you'd like to bring your data into the Numetric platform. You've come to the right place. This article will give you everything you need to get your data up and running in Numetric.
As we discussed in the Getting Started article, there are three main methods to bring your data into Numetric:
- Manually upload (or connect via Dropbox or Google Drive) a flat file such as a CSV or Microsoft Excel file
- Use one of our pre-built data connectors to connect to common business systems (including many popular database systems)
- Push data directly to the Numetric platform with our secure API
Using CSV or Excel Files
CSV and Excel files are common formats for data you've exported from another system. It's simple to bring these types of files into Numetric. You can do this either manually by uploading the file, or by placing the file in a Dropbox or Google Drive folder. Before we go over these two approaches, we'll first provide a quick overview of the required format for these flat files.
Required format for CSV and Excel Files
The file structure for flat files is very simple. There are just four requirements, and these apply to both CSV and Excel files. If you've exported your data from another system, it's very likely to already adhere to these requirements, but we'll state them just in case:
- The file is smaller than 1 GB. Files larger than 1GB must be uploaded through one of the other methods
- The file is in tabular format. This means the data is arranged in columns and rows
- The file's first row contains the column or field names (CSV files only)
- Each subsequent row contains a separate record of data
With each of these four properties, your to-be-imported data should look like either of these:
Flat Files from Cloud Storage
A manual upload of flat files is fairly simple, but sometimes it’s inconvenient to repeat that manual process every time you need to update the Source Table from your external data source. By linking a Google Drive or Dropbox account to your Sources, you can update your Source Table by simply updating the file on your Google Drive or Dropbox account. This approach still requires you to manually place a new file inside the cloud folder each time it needs to be updated, but you can schedule updates to have Numetric check for updated files.
Using the Numetric API
Manual uploads and cloud storage connections provide quick, convenient methods to bring your data into Numetric, but they still require a fair amount of work to update a data source. One way to overcome these limitations is to use our secure, public API. This method allows you to push data to Numetric as frequently as you'd like using a regular process (e.g., a scheduled script that pushes new data to Numetric's servers each night). It also allows you to import data sources that are larger than the 1GB limit imposed on manual file uploads.
Source Tables pushed to Numetric via the API will show up in Sources under the "Numetric API" source, and will behave in the same way as any other Source Table added using other methods.
APIs are the most customizable option to import a data source and keep it up-to-date. An API connection is the most popular method when a constant connection to the data is important. In summary, the Numetric API does require some technical know-how, but if this is your preferred method, we can help you get there.
The Numetric API is carefully documented at our developer portal, and rather than restate the instructions here, we'll simply direct you (or your favorite developer-friend) to the getting started page, where you'll get connected in just a few simple steps. And of course, if you don't have a developer or analyst handy or can't spare the resources just let us know,
we'll be glad to help.
Numetric Data Connectors
Numetric has developed automated Data Connectors that integrate with many popular business services.
A connector is not as configurable as the Numetric API, but they do allow Source Tables to be automatically updated from an external data source. If you use one of these common business applications or services, connectors provide a simple way to ensure that Numetric always has the most current data.
Numetric currently supports the following Data Connectors:
- Maria DB
- Microsoft SQL Server
- My SQL
- NetSuite (beta)
- Quickbooks Desktop (beta)
Numetric also supports SQL connectors to connect your SQL database to Numetric. Adding SQL connections to Numetric requires a publicly accessible host IP address, Port (optional), a database name, as well as a username and password. Numetric will provide you with a list of our IP addresses to ensure that our data connection is not blocked by a firewall.
Using a SQL connector will allow you to upload tables and views from your SQL servers, taking advantage of the work you have already done to organize your data.
Managing Source Tables
Managing Source Tables is a simple yet crucial step to your success with Numetric -- after all, if your Source Tables don’t contain the most up-to-date data, then you aren’t getting the most out of your Tables, Datasets, or Workbooks.
All of your current Sources are displayed on the left sidebar of the screen. When a Source is selected, each Source Table available from that Source is displayed to the right with the active Source Tables displayed at the top, and the inactive (not selected for import) Source Tables listed alphabetically below. Because your data source may find more Source Tables than you actually need, you have the ability to enable or disable your available Source Tables by toggling the Enabled switch next to the title of the Source Table. When a Source Table is marked as Enabled, Numetric will ingest the Source Table data, and make it available in Tables. When a Source Table is marked as Disabled, the Source Table is no longer active and all data from the Source Table is deleted from Numetric.
When you disable a Source Table that is currently used in a Workbook or Dataset, Numetric will inform you of any Datasets or Workbooks using information from that Source Table. You will then have to indicate that you also want to delete the associated Workbooks or Datasets before you can disable your Source Table. This is done to prevent any errors from occuring in the associated Datasets or Workbooks. If you do not want to delete the associated Workbooks or Datasets, you will need to remove any connections to the Source Table prior to disabling it.
Once a Source Table is added to Numetric, it goes through a process called Ingestion. Ingestion is simply the process of uploading the file to the Numetric servers. Because there is no file size constraint on API and data connector feeds, the size of the table being uploaded, and the speed of the connection will directly impact the time it takes to ingest. This process could take anywhere from a few seconds to a few hours to complete. Some very large tables could take days to fully ingest.
Once your data source has successfully ingested into Numetric, you officially have a usable Source Table. In the event that an ingestion fails, an error alert will appear next to the Source Table title, which will now appear red.
If this has occurred, you can attempt to manually initiate a new ingestion of that Source Table by clicking the refresh button located in the far right column. If the ingestion fails again, please contact Numetric support so we can help you troubleshoot the issue.
Update Schedule for Sources
After the initial ingestion, Numetric will check all active Source Tables for updates on a regular schedule. This schedule can be set for each Source Table individually or for all Source Tables associated within a given Source.
To set the update schedule for all active Source Tables within a specific Source, click on the Update Schedule field located in the top right corner of the Source screen.
This will open a sidebar menu where you can enable/disable Auto Updates, and customize the update schedule. Changing the date and time in the First Update Time fields will allow you to determine the initial date and time off which future updates will be scheduled. Once the First Update Time field is complete, you can set the future update schedule under the Update Every heading. Here you can set how frequently Numetric will update all Source Tables associated with this Source. The fields will let you set the number of minutes, hours, days, weeks, or months after the First Update Time Numetric will check for updates (the shortest time being 10 minutes).
Note: We recommend that the shortest you set your update schedule is 2-3 times the average update time for your Source Tables. Setting the update schedule shorter than the time it takes your Source Tables to update will not result in a quicker upload time. If a new update is requested while the Source Table is still updating, the new request will be ignored. Depending on the size of your Source Table, it could take anywhere from minutes to multiple hours to update.
Additionally, you can do a one-time refresh of your Source Tables. By clicking on the Refresh icon located to the right of the Update Schedule you can select either Refresh Table List or Update Enabled Tables.
Refresh Table List will refresh the schema of your source database, updating the list of Source Tables, number of rows and columns, data types, and updated views that are available to pull into Numetric.
Update Enabled Tables will manually run an update all of the Source Tables that are marked as Enabled for that specific Source. This will operate just as a regularly scheduled update, and will not conduct a full refresh of the data source. This update will not interrupt any future scheduled updates.
Updating Schedule for Source Table
Individual Source Tables inherit their update schedule from the Source Update Schedule. To set the update schedule for an individual Source Table click the Settings icon located in the far right column of the Source Table information.
This will open the Table Options sidebar menu where you can enable/disable Auto Updates, and customize the update schedule for that specific Source Table. Changing the date and time in the First Update Time fields will allow you to determine the initial date and time off which future updates will be scheduled. Once the First Update Time field is complete, you can set the future update schedule under the Update Every heading. Here you can set how frequently Numetric will update this Source Table. The fields will let you set the number of minutes, hours, days, weeks, or months after the First Update Time Numetric will check for updates (the shortest time being 10 minutes).
Note: We recommend that the shortest you set your update schedule is 2-3 times the average update time for your Source Table. Setting the update schedule shorter than the time it takes your Source Table to update will not result in a quicker upload time, rather it could slow down your update time. Depending on the size of your Source Table, it could take anywhere from minutes to multiple hours to update.
Additionally, you can do a one-time refresh of any Source Table. By clicking on the Refresh icon located far right column of the Source Table, and selecting Update. This will conduct an unscheduled refresh of that Source Table, and will not interrupt any future scheduled updates.
Scheduled updates are not available for manually uploaded files. To update a manually updated file, click the upload icon located in the far right column of the Source Table information.
This will open a file explorer menu where you can select the file to update the Source Table.
Verify Source Connection
Once your Sources are set up it is important to verify that all of your source data is showing up in Numetric correctly, this is most easily accomplished by a simple row count. To do this, verify that the number of rows your data source is sending matches the number of rows in the corresponding Source Table in Numetric. We recommend this basic row count audit be conducted after the first few updates of every Source Table to verify that the connection is functioning correctly. Once your Source Tables are connecting properly we recommend quarterly row count audits to uncover any issues that may arise in your Sources connections. If at any time there is a discrepancy between the number of rows, please contact Numetric support for help in troubleshooting the issue.
Don't Stop Now
Adding sources of data to Numetric is just the beginning of the data management process. Once you've added all of the sources of data from your various systems, databases, and files, the next step is to filter, clean, and transform the raw data available from those sources into an easy to read Tables.