Data models

A data model combines one or more Data Tables. In it you relate the tables, write measures, set row-level security and the refresh schedule. Every report is built on a data model.

Creating a Data Table does not create a data model. You create data models yourself.

Before you start

• Load your data into Data Tables. See Data Tables.
• To create or edit a data model, you need Build permission. Without it the model opens with the Read only tag.

Create a data model

1. Open Data models in the sidebar.
2. Click New data model.
3. Enter a Name (up to 100 characters).
4. Optional: enter a Description (up to 2,000 characters).
5. Under Data Tables, pick the Data Tables. You can pick up to 20.
6. Click Create.

Tip

Right after you load tables, you can click Create a data model from these data tables on the load result. See Data Tables.

Add or remove Data Tables

The Data Tables of a data model are listed in the Data pane on the Model tab, under Tables.

Add:

1. In the Data pane, click Add data table.
2. Pick the Data Table.
3. Click Add.

Remove:

1. In the Data pane, click the ⋯ button next to the table (Actions for table …), then Remove data table.
2. Read the list of tables, measures and relationships that leave the data model.
3. Click Remove data table.

Caution

Measures that live on the removed tables are deleted. Other measures that used those tables show an error until you fix them.

The model editor

The editor has these tabs:

• Model: the diagram of tables and relationships.
• Measures: see Measures.
• Refresh: Refresh now, the latest refresh of each Data Table and the refresh schedule. See Refresh.
• Row-level security: see Row-level security.

On the Model tab, the side pane has three parts: Data (tables, columns and hierarchies), Relationships and Properties.

On the diagram:

• Drag a table header to move the table.
• Drag the empty canvas to move around. Use the zoom buttons to zoom from 50% to 150%.
• Click Auto layout to arrange the tables.
• Use Saved layouts to keep an arrangement: Save layout, Update layout or Delete layout. A data model has at most 20 saved layouts.

Note

When you move a table by hand, the position stays on this screen only. Save a layout to keep it.

Relationships

A relationship joins a column of one table to a column of another table, so that filters on one table also filter the other.

Detect relationships

1. Click Detect relationships.
2. Fresco BI suggests relationships from the foreign keys in the source and from column names.
3. Click Add on a suggestion, or Add all.

Create a relationship

1. Click New relationship. You can also drag a column onto the matching column of another table.
2. Under From table (many), pick the table and its Column.
3. Under To table (one), pick the table and its Column.
4. Pick the Cardinality: Many to one (*:1), One to one (1:1), One to many (1:*) or Many to many (*:*).
5. Pick the Cross-filter direction: Single or Both.
6. Optional: tick Apply security filter in both directions. It shows only when the direction is Both.
7. Leave Relationship is active ticked, unless you want the relationship off.
8. Click Apply.

Rules:

• The two columns must be in different tables and have the same data type.
• A one-to-one relationship always filters both ways.
• On the one side, the key column must be unique and have no blanks. The data is checked when you save the draft. If the check fails, you see “Not saved: a relationship key on the one side repeats values or has blanks”.
• To check an existing relationship, select it and click Save and check on data.

Note

If two active relationships would give two filter paths between the same tables, Fresco BI turns one off. The relationship shows “Turned off to avoid two filter paths between the same tables”.

Manage relationships

Click Manage relationships to see every relationship in a table: From (table: column), To (table: column), Cardinality, Cross filter and Active. In this list you can:

• edit a relationship,
• delete several relationships at once,
• set the cross filter of the selected relationships,
• click Autodetect to find more relationships.

Table and column properties

Pick a table or a column in the Data pane, then open Properties.

Column properties

• Hide from the field list
• Summarize by
• Format
• Display folder
• Description

Mark as date table

A date table lets you filter and group by date in a consistent way.

1. Pick the table.
2. Under Mark as date table, pick the Date column.
3. Click Check.
4. Click Mark.

The date column must be unique, have no blanks and have every day from its first to its last date. To undo, click Unmark.

Auto date/time

When Auto date/time is on, every date column gets a Date Hierarchy: Year, Quarter, Month, Day. It does not apply to tables marked as date table or to columns related to one.

If you turn it off, the Year, Quarter, Month and Day levels are removed when you save. Visuals that use them show a missing field.

Hierarchies

1. Pick the table.
2. Click New hierarchy.
3. Enter the Hierarchy name.
4. Under Levels (top to bottom), click Add level and pick a column. Repeat for each level. Use Up and Down to change the order.
5. Click Add hierarchy.

Each column is used once, at most 10 levels, all from the same table.

Calculated columns

A calculated column is computed when queried from the numeric columns of the table. You can use Table[Column], numbers and + - * /.

1. Pick the table.
2. Under Calculated columns, click New calculated column.
3. Enter the Column name (up to 128 characters).
4. Enter the Expression (up to 4,000 characters). Example: Orders[Quantity] * Orders[Unit price].
5. Click Check expression.
6. Click Add calculated column.

Tip

To use a new calculated column in another calculated column, save the draft first.

Groups and bins

Pick a column, then:

• New group puts values of the column into named groups. Enter the Column name, a Group name and the Values in the group. Tick Put the other values in an Other group if you want. Click Create column.
• New bins splits a number column into ranges. Pick the Bin type: Size of bins or Number of bins (1 to 1,000). Click Create column.

Decimal number columns cannot be grouped. Use bins for them.

Save, validate and publish

Changes go into a draft. Reports keep using the last published version until you publish.

1. Click Save draft.
2. Click Validate. The Model check shows any issues.
3. Click Publish, then confirm. Reports on this model use this version.

Validate and Publish work only after the draft is saved. If errors block publishing, Fresco BI lists them under Cannot publish yet.

Caution

If someone else saved the model while you were editing, you see The draft changed. Click Reload draft to continue. Unsaved changes on your screen are lost.

Version history

Click Version history to see the saved versions of the data model. Each version shows its number, the author, and the Current draft or Published tag. You can view versions. You cannot restore one.

Rename or delete a data model

On the Data models page, each data model has Rename and Delete in the Actions column.

Delete moves the data model to the trash. You can restore it from the trash. See Trash.

Caution

You cannot delete a data model while reports use it. Delete those reports, or move them to another data model, first.

Limits

Item	Limit
Data Tables in a data model	20
Measures in a data model	2,000
Relationships in a data model	2,000
Hierarchies in a data model	200
Levels in a hierarchy	10
Saved layouts in a data model	20
Groups in a group column	100
Bins in a bins column	1 to 1,000

Common questions

Can the same Data Table be in two data models? Yes. Each data model has its own relationships, measures and security roles.

Do I need to refresh after I change the model? No. Changing the model never reloads data. Refresh only loads new data from the source.