Before We Start

Before configuring the application, let’s start by preparing a list of tables and columns from our database. We need this information to configure:

• Object types: Each table will become one object type in our package.
• Attribute types: Each column with values (e.g., description) will be defined as an attribute type.
• Attribute mapping: Knowing what attributes we have, allows us to quickly and correctly link our source fields to the Dawiso attributes.

Prepare a List of Tables and Columns

First, use a SQL query (with the DISTINCT function if needed) to extract a list of tables and their unique column names from your database. This will give you a list of object types (tables) and attribute types (columns), which you can immediately create in your package configuration.

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME ASC;

SELECT DISTINCT
    COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
  AND LOWER(COLUMN_NAME) NOT LIKE '%ID'
  AND LOWER(COLUMN_NAME) <> 'Name'
ORDER BY COLUMN_NAME;

Note that we excluded all primary keys (PK), foreign keys (FK), and name columns, as these serve as identifiers and are automatically ingested and processed by the system.

• Primary keys uniquely identify each row in a table (all have ID in their name).
• Foreign keys establish relationships between tables by referencing primary keys from other tables.
• Name fields often serve as human-readable labels for objects and are also treated as identifiers.

The result is a clean list focused on the business-relevant attributes captured across the different object types.

Recipe Manager: Example Data

Relations Diagram

In the entity-relationship diagram below, you can see the structure of our example Recipes database:

• Which tables exist,
• The columns they contain,
• And how they relate to one another.

Each table represents a distinct object type with the following parent-child relations:

• | The [Cuisine] object type is a parent to [Recipe].

A [Recipe] can be a parent to a [Recipe Step] or a [Recipe Review]. | A [Cuisine] can be a parent to another [Cuisine]. |

| --- | --- |
| Then we have the [Ingredient Category] which can be parent to [Ingredient] object types. | An [Ingredient Category] can also be a parent to another [Ingredient Category]. |
| Finally, we have the [Dietary Category] object type. | A [Dietary Category] can be a parent to another [Dietary Category]. |

• This information will later be used to define the object hierarchy.

Then, we also have relations between different object types:

• [Recipe] “is recommending” a [Recipe]
• [Recipe step] “requires” an [Ingredient]
• [Dietary Category] “requires” an [Ingredient]
• [Recipe] “has dietary category” [Dietary Category]

Table View

To generate a simplified overview of the data model, we used the SQL queries above to extract a list of unique table and column name pairs.

• Each table in your source system represents either an object type or a relation in your metadata app. The highlighted cells in the table below correspond to object types and will each require a dedicated ingestion query.
• Each distinct column across these tables represents an attribute type that must be defined in the package. To ensure correct ingestion, each column must be properly mapped to its corresponding attribute type.

TABLE_NAME
Cuisine
DietaryCategory
DietaryCategoryIngredient
DietaryCategoryRecipe
Ingredient
IngredientCategory
Recipe
RecipeRecommendation
RecipeReview
RecipeStep
RecipeStepIngredient

COLUMN_NAME
Description
Difficulty
InStock
LastMade

Click here to hide the example.For reference, the following SQL script returns the complete list of all tables and their columns from our example database, including the relation tables that define links between objects:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

The query produces the following result set:

TABLE_NAME	COLUMN_NAME	DATA_TYPE
Cuisine	CuisineID	int
Cuisine	Name	nvarchar
Cuisine	ParentID	int
Cuisine	Description	nvarchar
DietaryCategory	DietaryCategoryID	int
DietaryCategory	Name	nvarchar
DietaryCategory	Description	nvarchar
DietaryCategory	ParentID	int
DietaryCategoryIngredient	DietaryCategoryIngredientID	int
DietaryCategoryIngredient	DietaryCategoryID	int
DietaryCategoryIngredient	IngredientID	int
DietaryCategoryIngredient	DietaryCategoryRecipeID	int
DietaryCategoryIngredient	DietaryCategoryID	int
DietaryCategoryIngredient	RecipeID	int
DietaryCategoryRecipe	DietaryCategoryRecipeID	int
DietaryCategoryRecipe	DietaryCategoryID	int
DietaryCategoryRecipe	RecipeID	int
Ingredient	IngredientID	int
Ingredient	Name	nvarchar
Ingredient	IngredientCategoryID	int
Ingredient	Description	nvarchar
Ingredient	InStock	bit
IngredientCategory	IngredientCategoryID	int
IngredientCategory	Name	nvarchar
IngredientCategory	Description	nvarchar
IngredientCategory	ParentID	int
Recipe	RecipeID	int
Recipe	CuisineID	int
Recipe	Name	nvarchar
Recipe	Description	nvarchar
Recipe	Difficulty	nvarchar
Recipe	LastMade	datetime
RecipeRecommendation	RecipeRecommendationID	int
RecipeRecommendation	RecipeID	int
RecipeRecommendation	RecommendedRecipeID	int
RecipeReview	RecipeReviewID	int
RecipeReview	RecipeID	int
RecipeReview	Name	nvarchar
RecipeReview	Description	nvarchar
RecipeStep	RecipeStepID	int
RecipeStep	RecipeID	int
RecipeStep	Name	nvarchar
RecipeStep	Description	nvarchar
RecipeStepIngredient	RecipeStepIngredientID	int
RecipeStepIngredient	RecipeStepID	int
RecipeStepIngredient	IngredientID	int

Basics of a Metadata Application

Using the information above, we can configure our base package with the necessary object types and attribute types.

Now, we can move onto configuring the data ingestion function. To add data ingestion to your package, you will need these assets:

"hierarchyDefinitions": [ ],
"dataSourceDefinitions": [ ],
"dataIntegrations": [ ]

Asset	Purpose
hierarchyDefinitions	Defines the hierarchy of the object types. For a metadata application, at least two hierarchy definitions will be needed:Native: This hierarchy is based on the structure of the objects in your database, in other words, it reflects reality. This must be done precisely as it allows objects to be ingested properly., Hybrid: The hybrid hierarchy builds on the native one. After objects are ingested, Dawiso can reorganize them in a more business friendly way by, for example, further categorizing the objects into folders.All hierarchy definitions will be selectable in the left-side navigation panel.
dataSourceDefinition	Defines how data is ingested from an external data source into Dawiso. It configures to be executed against the external data source to get metadata from it., The visual template for the data source (data source creation UI definition).
dataIntegrations	Maps what fields get ingested as what attributes in Dawiso.
Let’s begin with defining the application’s object hierarchy using the list of object types that we prepared.