The Datasets view is where you connect to and import your data into Knarr. In this view, you can see the tables that make up your data model and how they are connected to one another.
You can have multiple datasets in a single project and each dataset contains 1 data model, which is a collection of tables that are typically linked together (though they don't necessarily have to be).
Data Model Overview
Knarr links tables based on like field names, so if your tables aren’t connected, rename the key fields to match. The field values have to match exactly, including upper/lower case, or Knarr won't know they are the same values, so you may need to do some string manipulation in order to match your fields properly.
Notice how all of these tables are linked by the ProductID field.
Adding a table
To add a table, click the “Add Table” button, which will bring up the window below and prompt you for the data you’d like to add. If you haven’t already added a data source, click on the "Set up a new source" link to add one. In the example below, we use Google Sheets. You can access your data providers by clicking on your profile picture and selecting "Data Sources".
Regardless of your source, the way Knarr ingests data is through SQL queries, which makes for a consistent experience across all of your data providers and sources.
Note you can only have one query per table, that query can include joins of other tables so long as the base data resides in the same data source. Joins across data sources can be achieved through associating the data via like field names.
Once you add your data source, depending on which source you choose (specific source info is in our Connecting to Data support section), you'll be prompted to either enter a URL or choose a schema and/or database.
NB: we require a URL for Google Sheets because we don't want to ask you for full access to your Google Drive; we try to only ask for the data and authorizations we absolutely need to run the platform.
Your schema will be generated and displayed in the left panel as a list of tables you can choose from. Click any of the tables to get the columns within the tables. To insert any table, click the SQL -> button to the right of the table.
Note that since you can only load one table at a time, clicking the SQL -> button will overwrite your current SQL code. If this happens, you can click into the SQL box and press Ctrl + Z on your keyboard to reverse the changes.
You can use the SQL window to create whatever transformations you need to, from aliasing fields to WHERE clauses or joins.
You can reload the entire model by clicking the "Refresh Data" button at the top, or you can refresh just one table by right-clicking on the table and pressing "Reload".
Removing a table
To remove a table, simply click on the table you would like to remove, then click the trash can icon in the top right corner or you can right-click on a table and click "Delete". This will refresh the pad with the latest data and remove the selected table.
Tips & Tricks
- If you have multiple key fields, Knarr will automatically create a synthetic join of those fields, but that may not be exactly what you’re looking for, so it is almost always better to create a surrogate key or use field aliases in your data rather than letting Knarr guess at what you want
- Your table and field metadata can be really useful to determine how well your data connect together, so check this when you load your data in to make sure everything is lining up as expected