Why Database Topology Matters
To understand modeling in Farseer, it helps to grasp the basic concepts of relational database design. A database schema defines how data is organized — table names, column types, and relationships between entities through primary and foreign keys.
Farseer's dimensional model builds directly on these concepts, so understanding star and snowflake schemas will help you design better, faster models.
The Star Schema
Snowflake vs Star schema
In a star schema, numerical data (facts) sits in a central table, while descriptive data lives in separate dimension tables connected through foreign key → primary key relationships. The structure visually resembles a star.
Example: A central Sales table might connect to Products, Regions, Customers, and Time dimension tables — each as an arm of the star.
Star schemas are simple and fast for queries because each dimension is only one join away from the fact data.
The Snowflake Schema
Example Dimension Hierarchy
A snowflake schema extends the star by adding relationships between dimension tables themselves. From the "arms" of the star, new links branch out to additional tables — creating a structure that resembles a snowflake.
Example Dimension Hierarchy, Populated
The tables above represent objects in the Dimensions section as they would be defined in Farseer. Let's see how some of these objects are connected:
Product -> Brand (one brand has multiple products [1:n])
Product -> Group 1 -> Group 2 (Devices have multiple groups (from Group 1), and each of these groups has multiple Brands [1:n])
Let's expand the example table we looked at earlier by adding columns for Customer and Quantity.
Example Dimension Hierarchy, expanded
To connect the Product and Customer tables, we need a third table - Quantity. This brings us to the Variable. It consists of rows that link the Product hierarchy to our newly created Customer table.
Snowflake Schema
The deeper the schema, the more powerful the analysis — but in traditional databases, deeper schemas also impact query performance. Farseer solves this by pre-calculating all aggregations based on relationships before any user query, enabling significantly higher performance for displaying, retrieving, and pivoting data.
How This Applies to Farseer
In Farseer, you don't write SQL or manage foreign keys directly. Instead:
Variables serve the role of fact tables — each variable stores one numerical column of data
Dimensions serve the role of dimension tables — they organize your data by categories like products, regions, or time periods
Dimension relationships (parent-child links between dimension tables) create the snowflake structure
When you attach dimensions to a variable in Farseer, you're essentially creating a star or snowflake schema. Farseer then handles all the aggregation and cross-referencing automatically.
Textual Model Notation
To make it easier to describe and communicate Farseer models, you can use a simple textual notation:
Dimensions:
SKU → Brand → Supplier
POS → Region → Country
Variables:
Quantity [SKU, POS]
Price [SKU]
Revenue [SKU, POS] = Price * Quantity
From this notation, you can immediately see:
Two dimension chains: SKU → Brand → Supplier and POS → Region → Country
Quantity is available at the SKU and POS level (and automatically aggregates up to Brand, Region, etc.)
Revenue is calculated from Price and Quantity
This textual notation is especially useful when models grow beyond a few variables — graphical representations quickly become cluttered, while text stays clear and easy to review.
The Golden Rule: No Loops
When designing dimension relationships, the most critical rule is: never create circular references (loops) in your snowflake topology.
Example of a problem: If you have SKU → Brand and POS → Region, adding a link Brand → Region creates a shortcut that disrupts the uniqueness of dimensional sequences. This can lead to illogical aggregation results.
Each dimension chain should be a clean, one-directional path — branches of the snowflake must never intersect.