Logic for creating relationships and semantic model

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

The relationships and semantic models created through workflow-based data modeling follow the design rules:

Relationships

1.  The system only creates relationships from fact to dimension. You must mark at least one table as fact. 

2.  If no primary and foreign keys are defined, then the joins will be created depending on the column names. The system will internally check the column equality by comparing the column names (ignore case) and by removing the underscore(_). For example, product_id will be joined with PRODUCTid. Only the columns with the same data types will be joined. 

3. If primary and/or foreign keys are defined, then the system will create joins only between the primary and foreign keys. In addition to rule (2), the system will also use additional sub-string matching while defining the joins. For example, country_code can be joined with country_code_key. 

Semantic models

Based on the defined relationship, the system auto-creates dimensions and measures for semantic model design based on the following rules:

1. The number of dimensions is equal to the number of master tables defined in the system.  

2. All Char and Date field of the master table becomes attributes in respective dimensions.

3. The number of measure groups is equal to the number of facts in the relationship.

4. By default, a maximum of 15 dimensions, 100 attributes, and level count, and a maximum of 8 attributes per dimension are created. 

5. All number fields from fact data become a measure with default summary functions as Sum.

6. All primary key and foreign key fields are ignored. 

7. User can optionally modify the auto-created semantic model design to:

   1. Rename or delete dimensions, attributes, measure groups, or measures 

   2. Select multiple attributes and create hierarchies using them.

   3. Change the summary function of the measure.

   4. Set dimension type and so on.