Official Recommendation for Subject Model Usage

This document introduces the model structures recommended by FineBI.

Building a model structure is bound for the dimension table and fact table.

Dimension table: A dimension table describes and stores detailed information about business data attributes. Dimension is the perspective from which you analyze problems. For example, a product dimension table may contain columns such as product ID, product name, and product category. The dimension table provides dimensional attributes for grouping, filtering, and analyzing business data.

Fact table: A fact table contains indicator fields used to measure facts. The fact table is the core table in the subject model, which contains indicator fields related to business processes, such as sales quantity, sales amount, and profit. The dimension table provides context information by establishing a modeling relationship with the fact table. Each record in the fact table represents a business event or fact.

In this document, dimension tables are identified as orange and fact tables as blue.

One Fact Table and Multiple Dimension Tables

Star Structure

The star model is a central fact table (like a star) surrounded by multiple dimension tables (like rays of the star).

The fact table is the core, and the dimension table provides contextual information related to the facts. Each dimension table has a 1-to-N relationship with the fact table, connected by shared key values, like the rays of a star connecting to its center. This structure allows you to flexibly slice and analyze data according to different dimensions, like observing stars from angles. The star model is simple and easy to understand. Besides, this model also has relatively high query performance.

Snowflake Structure

The snowflake model is similar to the star model, as it also includes fact tables and dimension tables. However, there are differences in the organization of the dimension table. In the snowflake model, the dimension table is subdivided into multiple normalized tables, forming a multi-level structure similar to a snowflake unfolding.

For example, Regional Dimension Table is provided with contextual information by three tables: Country Dimension Table, Province Dimension Table, and City Dimension Table.

Multiple Dimension Tables and Multiple Fact Tables

In FineBI, multiple fact tables cannot share multiple dimension tables.

For example, in the following figure, Sales Fact Table and Stock Fact Table share two dimension tables Brand Dimension Table and Category Dimension Table.

In FineBI, you cannot directly establish a relationship shown in the following figure. In this case, you can solve the problem with the following methods.

Method one: Perform Join on multiple dimension tables to merge them into one dimension table and establish modeling relationships with multiple fact tables.

Note:

 If the dimension table does not contain a field that can be used as a merging basis, you can also directly perform a crossjoin merge.

Method two: Perform Join on multiple fact tables to merge them into one fact table and establish modeling relationships with multiple dimension tables.

Multiple Fact Tables

Scenario One: Complete Key Value

If the key value in one of two fact tables that establish the modeling relationship is complete, an N:N modeling relationship can be directly established.

Generally, the products in stock are the most complete, and there will be records of the products as long as they are in stock. However, in Sales Fact Table, there will only be a record of a product when it is sold.

Therefore, the key value Product Key in Stock Fact Table is complete. In this case, you can establish an N:N modeling relationship between these two tables.

Scenario Two: Incomplete Key Value

If the key values of both fact tables that establish the modeling relationship are incomplete, their key values contain data that the other one does not have. In this case, directly establishing an N:N modeling relationship may lead to incorrect calculation results.

The following example provides the solution.

Presale Fact Table and Return Fact Table establish the modeling relationship through Product Key. However, both tables contain products that the other table does not have, which means that Product Key in both tables is incomplete. In this case, you can extract the common dimensions from both fact tables, concatenate them vertically, and remove duplicate values to obtain a complete dimension table.

Then, establish a 1:N modeling relationship between this dimension table and the two fact tables.

You may want to use multiple fields as the matching basis when creating a subject model.

For example, in the following case, the Department and Region fields need to be used together as the merging basis.

You can add an auxiliary field Merging Basis to each of these two tables.

Click Formula Column and enter the formula Department+", "+Region, as shown in the following figure.

When building a subject model, use Merging Basis as the matching field.