Data Models (Business Views)

Overview

Data modeling is the process of creating “business views” in order to make self-service analytics possible for everyone in the organization. At a high level, this is where you give database tables and attributes user-friendly names (e.g. F_NM becomes First Name), specify relationships between entities (i.e. joins), and create re-usable calculations. This allows SQL to be automatically generated when users drag-and-drop fields from multiple entities on reports. Taking the extra steps to enter user-friendly labels and calculations means that users won’t have to do this on every report they create. The following sections will walk you through the process of creating a new model.

1) Connect to Your Data

Every data model reads from a data source. You can create one ahead of time under Administration > Data Sources, or choose Add New Data Source from the data source dropdown while building the model. A model can also be built on top of Datasets by choosing Datasets as the data source.

See also: Administration > Data Sources

2) Create a Data Model

Data Models are managed from the Data Models screen of the Administration menu. Each model in the list has actions to Edit, Rename, Clone, Delete, and (for admins) set Security.

New Data Model

Click New Data Models in the top-right of the Data Models screen (or Create One if the list is empty). The editor opens with an empty model; select the data source to build from in the Data Source pane on the left.

You can also start a model from the Data Sources screen by clicking a data source, which opens a new model with that source already selected.

Create from dbt

Use the dbt data transformation tool to provide the metadata layer. FlexIt matches the dbt project to an existing data source by its connection details. See the FlexIt dbt guide for details.

Data Catalog

Authors can browse every data model in the organization from Data Catalog in the portal sidebar, and export it to CSV.

Data Model Editor

The Data Model editor has three panes:

1. Data Source – select a data source, then drag entities or attributes from here into the Data Model pane
2. Data Model – this is your metadata model. Drag items from the Data Source pane into here, give them nice labels and descriptions, create joins/relationships, calculations, SQL entities, and more.
3. Data Model Viewer – when you click on items in the Data Model, this is where you edit their properties (e.g. label, aggregation, formatting). Click Apply to keep your changes.

The editor header has these buttons:

• dbt Config – the dbt connection for models created from dbt. See Connecting to dbt.
• Semantic Layer – generate a semantic model for other BI tools from this model. See Semantic Layer.
• Visual Editor – view and edit joins as an entity relationship diagram
• Settings – model-wide settings such as Global Filters, Field Groups, AI Context, and Natural Language Query Filters. Click Edit Specs at the bottom to view or edit the whole model as JSON, for example to export, import, or bulk-edit it.
• Save and Close

Building a Metadata Model

The main tasks involved in building the model are dragging in relevant Data Source items, giving them nice labels, descriptions, formatting, and then creating joins/relationships, calculations, SQL entities. These tasks are detailed in the following sections.

Bringing in Data Source items

Drag-and-drop items from the Data Source pane into the Data Model pane.

Tip - Use CTRL or SHIFT-click to select multiple items at once. Selecting several items in the Data Model pane lets you edit their shared properties together.

Double-click an item to rename it, or press Delete to remove it.

Joins (i.e. Entity Relationships)

Once you’ve brought items into the Data Model pane, click Visual Editor to open the Entity Relationships diagram. In its top-right, use Smart Join to automatically detect relationships between entities and Auto-organize to neatly arrange the diagram. Right-click on entities or relationships to create, edit, or delete relationships. Here’s what it looks like after a Smart Join:

You can also manage an entity's joins from the Relationships tab of the Data Model Viewer.

Editing item properties

Click an entity or attribute in the Data Model pane to edit it in the Data Model Viewer. Right-click on items for quick access to more functions, including Calculate, Refresh, Dependencies & Migration, security options, and Who has permission to this?.

Tip - Right-click on an item, or the entire model, and use Smart Auto-naming to give user friendly names to your entities/tables and attributes/columns.

Entity – The Properties tab lets you edit the label and description, or hide the entity. The Source Info tab shows details about the source table, and the Relationships tab is where you create joins to other entities.

Attribute – Click an attribute/column to edit its properties or view its database Source Info:

• Label – name that users see
• Group Label – puts the field in a folder with this name
• Description – information that appears when a user hovers over the info icon of a field
• Data Type – String, Number, or Date
• Aggregation – Sum, Avg, Min, Max, Count, Count Distinct, or None. Advanced... lets you use different aggregations at different grouping levels.
• Time Grain – for date fields, the default grain: Original Value, Day, Week, Month, Quarter, or Year
• Format – Number, Date, Currency, Percent, or Text, with options for decimal places, display units, separators, negative values, prefix/suffix, date pattern, and how to show nulls
• Sort – ascending, descending, or none
• Sort by Other Field – sort by a field that is not visible on the report. For example, sort by Person Id but show only Person Name.
• Hidden – hides the field from users
• Drills – use this if a field should always drill to a specific report. You may have a generic report with the lowest level of detail and lots of columns that you want to be the destination when clicked on from any source report.
• Masks – replace values that match a condition with a mask value
• Certification – mark the field as certified, ongoing, or invalid, with a note

Calculations

Right-click on any entity or attribute in the Data Model pane and choose Calculate to create a calculated field. This is highly recommended for common calculations that are not already a field/column in the database table. Each value in a calculation can be a constant, a field from the model, or a field from the report.

The available Calculation Type depends on the field:

Calculation Type	Measures	Dimensions
Math	✓
If..Then..Else	✓	✓
Percent	✓
Rank	✓
Window	✓
Text		✓

Math

Use math calculations for basic operations such as addition, subtraction, multiplication, and division. Click Add Item to use as many fields or constants as you would like in the calculation. Click Add Operation to add operators and parentheses. The screenshot below shows a basic math calculation:

If..Then..Else (Case Logic)

Equivalent to a case statement in SQL, the If..Then..Else calculation allows you to create complex field calculations based on specified logic. You can create new fields that group attributes (e.g. If items are “dog, cat, fish” then “Animal” Else If items are “tree, bush” then “Plant”), or new metrics (e.g. “Prior Year Golds” as If year=year-1 Then [Gold Medals] Else 0). Click Add Item to add more ELSE IF THEN lines to the statement.

Percent, Rank, and Window

Calculate a measure's percent of total, its rank, or a windowed Sum, Avg, or Count, either across all rows (All (Total)) or within each value of a dimension.

Text

Build a text field with Substring, Left, Right, Replace, or Concat.

SQL Entities

To use a custom SQL query as an entity, right-click the model and choose New SQL Entity, then click Edit Sql in its Properties tab. Run the query to bring in its columns.

3) Secure the Data Model

After you save the data model, it is only accessible by the “Admin” role by default. To allow other groups, roles, or users to use this model for reporting and dashboarding, click Security on the model in the Data Models list, then Add Users & Groups. Check Read-only to let them report on the model without editing it.

Within the model, right-click to apply finer-grained security: Object Level Security on entities, Column Level Security on attributes, and Row Level Security to filter rows by user. Who has permission to this? shows the users who can access an item.

Dependencies & Migration

Right-click the model, an entity, or an attribute in the Data Model pane and choose Dependencies & Migration to see which reports and dashboards depend on it. The Data Model Exposures window lists each item's Type, Name, Location (folder), Dependencies, Owner, and Last Modified. Click the link icon next to a name or location to open it in a new window, or the envelope icon to message the owner.

The Dependencies column shows the number of issues for each item. Click it to see every report item, the data model field it maps to (Meta Id), and its status. A broken link icon means the field is no longer in the model, or its entity or column has been renamed or removed. Click Refresh to check again after changing the model.

Migrate to Another Data Model

To move reports and dashboards from this model to a different one:

1. Click Migrate. A Migration Status column with checkboxes appears.
2. Select the items to migrate, or click Toggle Select to select them all, then click Migrate again.
3. Choose the data model to migrate to.
4. If the selected items use fields that the new model doesn't have, the Replace Missing Fields window opens. See Replace Missing Fields.
5. Click Start Migration.

FlexIt first saves a backup of the listed items, named auto_migration_backup_meta_ followed by the old model's ID and a timestamp, which you can restore from Backup and Restore. It then updates each item, including fields, filters, and filter formulas, to point at the new model. For dashboards, only cards built on the old model are migrated. The Migration Status column shows whether each item succeeded.

Fields whose IDs differ only by upper/lower case are matched automatically.

You can also move a single report to another model by changing its data model in the report's More Settings. The migrated report opens as a new, unsaved copy.

Replace Missing Fields

When you migrate to a model that is missing some of the fields a report uses, the Replace Missing Fields window lists each missing field. Pick a replacement field from the new model for each one. FlexIt suggests a match where it can find one. Fields you leave blank are removed from the report.

When you migrate from Data Model Exposures, click a field's label to see which reports use it, with links to each report and its folder, the owner, and the last modified date.