Data Tables

A Data Table holds the data of exactly one table: one database table or view, one Excel sheet, one CSV file, or one SQL query. Each table you load becomes its own Data Table, named after that table. To combine tables, add the Data Tables to a data model.

Before you start

• Connect a data source first. See Connect data.
• You need Build permission on the workspace items you work with. See Members and roles.
• The Free plan allows 10 Data Tables. See Plans and limits.

Load tables from a data source

1. Open Data Tables in the sidebar.
2. Click Add tables. The Load tables page opens.
3. Under Data source, pick the data source.
4. Pick a Schema.
5. Tick the tables or views you want. You can pick up to 50 tables at a time. Each row shows Table or View and an estimated row count.
6. For each table, choose a Mode: Import or Realtime. To set every table at once, use Apply to all.
7. Click Load N tables (for example Load 3 tables).

You can also start from the Data sources page: each data source has Browse data and Load tables. Browse data shows the schemas, tables and columns, and Preview 100 rows shows a sample before you load.

After the load, Fresco BI shows each new Data Table and starts its first refresh. From there:

• Click Create a data model from these data tables to put them in a new data model. Enter a Data model name (up to 100 characters), then click Create data model.
• Or click Open the data table list.

Note

Data is loaded as is. You build relationships, measures and calculations later, in a data model.

Import or Realtime

• Import copies the data into Fresco BI. It is fast. The copy is updated on the refresh schedule of a data model. See Refresh.
• Realtime reads the source each time a visual runs a query. It has no refresh schedule.

Rules:

• A Data Table uses only one mode.
• Realtime needs the Enterprise plan. If the connection does not support it, the option shows “This connection does not support Realtime”.
• See also Connect data.

To build a Data Table from your own SQL, use Explore.

Columns that cannot be loaded

A column with a data type that Fresco BI does not support is skipped. You see “N columns skipped (unsupported type)”. A Data Table can have up to 10,000 columns.

Upload an Excel or CSV file

Upload a file from the Data sources page. See Connect data for how to add a data source.

Excel (.xlsx):

• Up to 100 MB, no macros, no password.
• Up to 100 sheets, each with up to 200 columns.

1. Choose Upload an Excel file and pick the file.
2. In the sheet list, tick the sheets you need. You can load up to 20 sheets at a time. The first 20 are ticked for you.
3. Optional: type a Data Table name for a sheet. If you leave it empty, the sheet name is used. A name has 1 to 120 characters.
4. Click Load the picked sheets.

Each sheet becomes its own Data Table.

Tip

To update data from a newer version of the same file, turn on Replace an earlier file and pick the earlier file. A sheet with the same name updates the same Data Table: its name, columns and reports stay. Other sheets become new Data Tables. Replace a file only with a file of the same type.

CSV:

• UTF-8, up to 100 MB.
• Comma, semicolon or tab as the separator.
• Up to 200 columns and 5,000,000 rows.

1. Choose Upload a CSV file and pick the file.

One CSV file becomes one Data Table.

Find a Data Table

The Data Tables page lists the Data Tables of the workspace. For each one it shows Name, Data source, Last refresh, Created by and Created.

• Type in the search box to filter by name.
• Sort by last refresh or by name (A→Z).
• Use Filter by data source to show the Data Tables of one data source. The list starts with All data sources. Below it the sources are grouped by connector type: All PostgreSQL (for example) shows every source of that type, and each source of the type is listed under it.

Data Tables made with SQL in Explore are listed on the Explore page instead. See Explore.

Browse the data

Click a Data Table to open it. The page has these tabs:

• Data: the rows and columns.
• Data models: the data models that use this Data Table.
• Refresh history: past refreshes. See Refresh.

On the Data tab:

• The grid shows at most the first 50,000 rows.
• If you see “No data yet, refresh the data table”, the Data Table has not been refreshed yet. Refresh it from its data model.

Edit a column

1. On the Data tab, click a column. The Selected column panel opens.
2. Change what you need:
   • Column name: 1 to 128 characters.
   • Format: a number format, for example #,##0.00.
   • Default summarization: Don’t summarize, Sum, Minimum, Maximum, Average, Count or Count (distinct).
   • Hide in report view.
3. Click Save.

Data type is read-only. The data type changes when the table is loaded again.

Data types: Whole number, Decimal, Double, Fixed decimal, Text, True/False, Date, Date/time (UTC), Date/time, Time.

Data profile

The same panel shows a Data profile of the column: Rows, Distinct values, Blank, Blank rate, Duplicate rate, Minimum, Maximum, Average, Distribution and Most common values.

Other actions

The Data Table page header has:

• Share: share the Data Table with other people.
• Try a chart: try a quick chart on the data.
• Calculated column: coming soon. To add a computed column today, use a data model or Explore.

Rename or delete a Data Table

Rename:

1. Open the Data Table.
2. Click the ⋯ button, then Rename.
3. Enter a name of 1 to 200 characters.
4. Click Save.

You can also rename from the row icons on the Data Tables page.

Delete:

1. Open the Data Table.
2. Click the ⋯ button, then Delete data table.
3. Click Move to Trash.

Reports in this workspace that use the Data Table go to the Trash too. You can restore it within 30 days. See Trash.

Caution

You cannot delete a Data Table when:

• a refresh is running,
• a data model uses it (remove it from the data model first),
• a report in another workspace uses it,
• an Explore table is built from it (delete or change that table first).

Common questions

Can one Data Table hold several tables? No. Each Data Table holds one table. Combine Data Tables in a data model.

Where is the Refresh button? On the data model, not on the Data Table. See Refresh.