Models

In DoordaOnline, models serve as curated datasets that combine data from one or more tables within the same database. They are designed to anticipate and address common analytical questions, providing users with a streamlined starting point for their queries. Think of models as derived tables or specialised saved questions tailored for reuse.

Benefits of Using Models

• Enhanced Usability: Models allow you to update column descriptions and customise metadata, making data more accessible and understandable for users.

• Improved Searchability: They appear prominently in search results and are highlighted when users initiate new questions, promoting their reuse.

• Organised Structure: Models reside within collections, keeping them separate from complex database schemas and ensuring a tidy workspace.

• Efficient Performance: By persisting models, you can cache their results, leading to faster loading times and improved performance.

Creating a Model

To create a model in DoordaOnline:

1. Initiate a New Model:

   • Click on the “+ New” button in the upper right corner.

   • Select “Model” from the dropdown menu.

2. Choose a Query Method:

   • Opt for either the notebook builder or a native query (SQL). Using the notebook builder allows DoordaOnline to automatically populate some metadata; with SQL, you'll need to input metadata manually.

3. Select and Configure Data:

   • Choose the relevant data tables.

   • Define the necessary joins, filters, and calculations to shape your dataset.

4. Save the Model:

   • Assign a descriptive name and, if desired, a description to your model.

   • Save it within an appropriate collection for easy access.

Adding Metadata to a Model

Enhancing your model with metadata improves its usability:

• Display Name: Set a user-friendly name for each column.

• Description: Provide context or explanations for columns to aid user understanding.

• Column Type: Specify the data type (e.g., text, number, date) to enable appropriate filtering and visualisation options.

• Visibility Settings: Decide whether a column should appear in table views, detail views, or both.

Adding comprehensive metadata ensures that users can effectively explore and interpret the data within the model.

Utilising Models in Queries

Models can serve as foundational datasets for new questions:

• Starting a New Question: When posing a new question, select the relevant model as your data source. This approach provides a refined dataset, eliminating the need to navigate complex schemas.

• Referencing in SQL Queries: In the SQL editor, reference a model similarly to a table:

  SELECT * FROM {{#model-name}}
  

Replace model-name with the actual name of your model. citeturn0search1

Managing Models

• Editing a Model: To modify a model's query, navigate to the model, click on the downward arrow next to its name, and select “Edit query definition”. After making changes, save to overwrite the existing model.

• Version History: DoordaOnline maintains a version history for models, allowing you to view changes and revert to previous versions if necessary.

• Deleting a Model: If a model becomes obsolete, you can move it to the trash or delete it permanently. Note that deleting a model will affect any questions that utilise it as a data source.

By effectively leveraging models in DoordaOnline, you can create intuitive, reusable datasets that simplify data analysis and promote consistency across your organisation.