Intake

Adaptam Intake supports first-time data setup, identifies potential data problems, and helps you evaluate likely joins between tables. After the initial setup, Intake also checks ongoing data feeds for changes that could break calculations or joins.

Data Loading

Intake sources data from CSV files or databases.

Adding Sources

1. Add source — Allows you to add data from CSV files and/or databases.
2. CSV files — Opens your computer’s file selector so you can choose one or more CSV files.
3. Database — Opens the database connection dialog exampled below so you can specify the source.

Proceeding After Data Loading

Once all of the source tables have been populated, click Proceed to import the data and check it for problems.

1. Proceed — Starts Intake’s checks after the tables have been loaded into the UI.

At this point Intake performs two categories of checks:

• A simple setup check
• A broader pass through the data to identify potential data problems

Simple Setup Check

If no data problems are identified, Intake shows the navigator across the top and suggested best practices in the center. There are many optional setups in Intake, but only one must-fix setup.

1. Loading data — Shows the post-load view when no data problems are found.
2. Navigator — Lets you jump to any Intake step.
3. Duplicate field name fix indicator — Red highlights any tables that have duplicate field names, which must be fixed because formulas require unique field names.
4. Warning indicator — Yellow highlights any table that is missing optional field-description content.
5. Best practices — Provides hints for table setup, joins, and import.
6. Discard — Discards the inputs and starts over.
7. Next — Moves to the next screen, or you can jump directly by clicking another location in the navigator.

Data Problem Identification and Fixing

Intake handles two kinds of data-problem workflows. The first happens the first time a dataset is used, when Intake looks for issues in the imported data itself. The second happens during ongoing updates, when Intake compares the incoming data to the previously configured dataset and flags changes that could break formulas or joins.

First-Time Data Upload

Identifying Problems

1. Warnings — Lists each problem found and provides a checkbox to access supporting detail and one-time fix options.
2. Discard — Discards the inputs and starts over.
3. Next — Moves to the next screen.

Resolution Options

Clicking a warning opens a problem-specific UI such as the one exampled below. It lets you review the issue and make a one-time correction. Those fixes should still be pushed back into the source data when possible.

1. UI specific to data problem — Shows the details for the selected issue and supports one-time resolution.
2. Instructions — Explains the available resolution options for that problem.
3. Problems — Displays the identified data problems in red.
4. Value change — Lets you double-click a red value and change it. If the change fixes the problem without creating a new one, the value turns black.
5. Remove values — Removes the flagged values while keeping the data rows.
6. Remove rows — Removes the rows that contain the flagged values.
7. Accept Problems — Accepts the flagged values and shows the consequences of doing so.

Note: You can double-click directly into a red value to edit it.

Ongoing Data Updates

For updated feeds, Intake performs additional checks so that changed data types, broken joins, and newly added columns do not go unnoticed.

Loading a Previous Mapping

Updating data can be manually triggered or automatically triggered by an IT job. Both use a file created by the previous data loading with all the setup changes, e.g., field name changes, and descriptions, retained but none of the one-time data fixes. Manually doing that is exampled below.

You can start an update by loading a previously saved TAMMAP file.

1. Load Mapping — Loads a previously saved setup for a dataset.
2. Please choose a file — Opens your computer’s file selector so you can locate the TAMMAP file.
3. File selection — Shows the TAMMAP files that can be selected and opened into the setup.

The TAMMAP file retains the saved Intake configuration, including joins, but excludes one-time data fixes that should have been corrected in the source data.

Managing Sources During Updates

1. Add source — Adds more CSV or database sources to the TAMMAP-based setup.
2. Delete Source — Removes part of the TAMMAP configuration.
3. Proceed — Moves through the remaining Intake steps. If this is an update and no data problems are found, you move directly to the final Import step.

During updates, Intake also checks for changed data types and join-breaking changes before import.

Handling Source Problems

If the ongoing upload source data files are not in their previous location or have different names, Intake identifies that as exampled below. It gives options to address those changes at the source or individual table level and set the new path(s).

1. Message in red — Explains the source-level problem and potential fixes.
2. Source level problem — Highlights a source whose data can no longer be found.
3. Change directory — Lets you find one or more data sources in a different location.
4. Delete Source — Removes an entire data source.
5. Individual source problem — Lets you change the path for a specific source element.
6. Change Table Path — Lets you change the file name, folder, or both for a table source.
7. Delete Table — Removes the individual file from the setup.

Once the red source problems are fixed or removed, Proceed becomes available again.

Automated Detection of Updated-Data Issues

Intake automatically identifies updated-data changes that would:

• Break calculations, such as a field changing from a date to text
• Break a join, such as a field changing from a one to a many
• Create other data issues, such as a non-join field changing from a one to a many
• Add new data columns that still need user awareness and setup

1. Warnings — Lists each issue with a checkbox that opens the appropriate one-time fix UI, or in the case of a new column or a new table, directs you back to the table view for setup.

Table View and Configuration

Each imported table has a table view. Non-unique field names are the only must fix change and are designated in red. There is large set of optional configuration changes, e.g., field descriptions and improving field names, which can be made to improve the usability of the data sets by users unfamiliar with the data.

Must-Fix: Duplicate Field Names

Identifying the Problem

1. Navigator air_airlines — Shows which table view is currently displayed.
2. Red highlight — Marks each field that has a duplicate name elsewhere in the dataset. Intake creates field names from source columns by removing database-illegal characters and converting the text to lowercase (to maximize differentiation from all upper case functions).
3. Alter Field Name — Lets you edit a highlighted field name directly until it becomes unique.
4. Add Data Field name suffix — Lets you fix multiple duplicate field names at once by adding a short suffix, usually an underscore plus a brief identifier. Also makes it very easy to see which fields are from the same table and therefore a very desirable practice.

Using a Data Field Name Suffix

1. Data Field name suffix — Adds a short suffix such as _al to every field in the table.
2. Updated field names — Show the suffix applied across the table so the duplicate-name problem is resolved.
3. Show existing suffixes — Displays the suffixes already in use so you can choose a unique one.
4. Navigator indicators — Turn yellow once the red duplicate-name problem has been cleared.

Once the red issues are gone, all remaining Intake steps before the final import are optional.

Table Setup Options

The top of the table setup contains information (e.g., # of rows) and options (table name, description and field name suffixes) to change at the table level.

1. File name — View-only display of the source data name.
2. # of rows — View-only display of the row count in the source data.
3. Table name prefix — Adds a prefix so related tables sort together. A trailing underscore is typical.
4. Choose existing — Applies an existing prefix to the table name.
5. Table name — The table name used inside the application.
6. Show table names — Displays the other table names to help with naming consistency.
7. Table description — Describes what the table contains inside the app.
8. Data Field name suffix — Sets the suffix used for uniqueness and formula readability.
9. Show existing suffixes — Displays all currently used suffixes.

Field Configuration

The Data Field section of the Table specific views allows many improvements to field data. Such as reordering the fields to group related fields together, field renaming for better search (e.g., switch all date fields to start with date then _sale, _invoice, _delivery), adding data descriptions (to aid less data familiar users). And it provides helpful information such as example values, the field data type, and whether the data in unique.

1. Drag and drop — Reorders rows and helps group related fields together.
2. Include — Controls whether the field is exposed to app users.
3. Order — View-only display of field order.
4. Column — View-only display of the source column name.
5. Field name — The editable field name used in formulas.
6. Field Description — Optional descriptive text that improves field search and usability.
7. Examples — Auto-generated examples that you can replace with clearer examples.
8. Type — View-only display of the field data type.
9. Blanks — View-only indicator showing whether the field contains blanks.
10. Unique — View-only indicator showing whether the field contains unique values.
11. View Values — Shows all values or a representative sample when there are too many to list.

Join Setup

Intake supports three ways to evaluate joins:

1. Manually - selecting the tables and join keys and looking at our automated evaluation of the potential joins
2. Automatically — having Intake evaluated all the potential joins and give you an evaluation of all the possible ones.
3. Automatically with intelligent screening — letting Intake screen the possible joins to highly probable joins with an evaluation of each.

Intake supports one-to-one and one-to-many joins. It does not create many-to-many joins in the Intake UI, although advanced users can still write more specialized joins directly in formulas. Intake executes joins as full outer joins so data is not dropped, automatically handles chain joining, and screens out one-to-many chains that would create a many-to-many result. Intake also evaluates composite two-column joins, although those still need manual column combination before they become usable joins in formulas.

Automatic Join Evaluation

Run Possible Joins

1. Edit Joins view — Shows the join landing screen.
2. Run possible JOINS — Evaluates all potential joins between the tables.

Review the Possible Joins

Running the possible joins gives you a summary of all the possible joins (below) from which you can look at an evaluation of any possible joins of interest.

1. #Possible JOINS — Shows how many potential joins were found, including low-value matches.
2. View buttons — Open the detail UI for evaluating each possible join.

See Join Evaluation Detail

Which allows an easy assessment of most join decisions although there is even a greater level of detail accessible if desired.

1. Table — The first table from the current selection.
2. Table(s) — Tables with potential join keys.
3. FIELD TO JOIN — The join key in the first table.
4. MATCH -> — Percentage of rows in the first table with a matching value in the second table.
5. JOIN TYPE — Icon showing the join type.
6. <- MATCH — Percentage of rows in the second table with a matching value in the first table.
7. SECOND TABLE/FIELD TO JOIN WITH — The potential second table and field combination.
8. JOIN STATUS — Shows join status and provides the Join or Disconnect action.
9. VIEW DETAILS — Buttons that open deeper join analysis.
10. VIEW DETAILS button selection — Shows the selected detail view for a specific possible join.
11. Hide unlikely JOINS — Applies additional screening to eliminate unlikely join combinations.

Additional Join Detail

This give you the evaluation of every potential join field and the rationale for any field ruled out as not possible.

1. Table 1 — The first table name.
2. Table 2 — The second table name.
3. FIELD — Join key in the first table.
4. DESCRIPTION — Description for the first-table join key field.
5. DATA EXAMPLES — Example values from the first-table join key field.
6. MATCH -> — Percentage of rows in the first table with a matching value in the second table.
7. JOIN TYPE — Icon showing the join type.
8. <- MATCH — Percentage of rows in the second table with a matching value in the first table.
9. JOIN STATUS — Shows join status and provides the Join or Disconnect action.
10. FIELD — Candidate join fields in the second table.
11. DESCRIPTION — Description for the second-table join key field.
12. DATA EXAMPLES — Example values from the second-table join key field.
13. Many to Many — Flags candidate fields that would create many-to-many joins.
14. Data type mismatch — Flags candidate fields with incompatible data types.

Using Hide Unlikely Joins

In most situations the best practice is to start with the Hide Unlikely Joins which automatically screens out all those very unlikely join candidates to a typically very manageable list with evaluations you need to easily decide joins. You can easily toggle back and forth so you have easy access to the broader list. This tool gets rid of virtually all the technical requirements of deciding joins and makes it easy for anyone.

1. Hide unlikely JOINS — Turns unlikely-join filtering on and off.
2. Table — The first table.
3. Table(s) — The second table or tables with potential join keys.
4. FIELD TO JOIN — The join key in the first table.
5. MATCH -> — Percentage of rows in the first table with a matching value in the second table.
6. JOIN TYPE — Icon showing the join type.
7. <- MATCH — Percentage of rows in the second table with a matching value in the first table.
8. SECOND TABLE/FIELD TO JOIN WITH — The second-table field candidates.
9. JOIN STATUS — Shows join status and provides the Join or Disconnect action.
10. VIEW DETAILS — Opens deeper join detail for the candidate row.
11. Number of likely joins — Shows how the list has been reduced from all possible joins to the likely ones.
12. Double joins — Preserves cases where one field sensibly joins to multiple fields in another table.

Hide Unlikely Joins Works Across All Tables

Allowing easy back and forth between the likely and possible joins at any level of the join evaluation.

1. #Likely JOINS — Shows the count of likely joins.
2. View buttons — Open detail UIs for evaluating those likely joins.

Manual Joins

Step 1: Select the First Join Table

1. Instructions — Direct you to select the first join table.
2. Selection — Click the desired table.

Step 2: Select the First Join Key Field

1. Instructions — Direct you to select the first join key field.
2. Selection — Click the field in the first table.

Step 3: Select the Second Table

1. Instructions — Direct you to select the second table.
2. Table 1 information — Shows the first table, join key field, description, and data examples.
3. Select a second table — Lists the available second-table options.
4. Selection — Click the second table you want to join.

Step 4: Select the Join Field(s) in the Second Table

1. Instructions — Direct you to select one or more joins in the second table.
2. Table 1 information — Shows the first table, selected join key, description, and data examples.
3. Table 2 — Shows the second table name.
4. MATCH -> — Percentage of rows in the first table with a matching value in the second table.
5. JOIN TYPE — Icon showing the join type.
6. <- MATCH — Percentage of rows in the second table with a matching value in the first table.
7. JOIN STATUS — Shows join status and lets you make the join selection.
8. FIELD — Candidate join fields in the second table.
9. DESCRIPTION — Description for the second-table join key field.
10. DATA EXAMPLES — Example values from the second-table join key field.
11. Non-joinable fields — Lists fields that cannot join to the selected first-table key and explains why.

Import

Import is the final Intake step. It becomes available whenever no red problems remain, prepares the dataset for use in the app, and creates the TAMMAP file used for future updates.

1. Import navigator — Shows that you are on the last Intake view.
2. Error/Warnings display — Shows errors in red that must be fixed and warnings in yellow that do not block import.
3. Import button — Opens the system UI for saving the database and the TAMMAP file.

When you import:

1. Your system file browser opens.
2. Save the database to the Documents > Adaptam folder so it is available to the app.
3. Save the TAMMAP file so the same setup can be reused for ongoing updates.