Overview
When designing a dimensional model, the most important thing is to pay attention to the model's topology — the schema and the relationships between dimensions. This guide walks through the key steps for identifying dimensions and their relationships from your data sources.
Step 1: Identify Your Dimensions
Dimension tables usually represent a logically meaningful set of data — a list of products, brands, regions, or cost centers. Start by analyzing your client's data and identifying these sets.
Watch out for:
Inconsistent naming — the same data written in uppercase, lowercase, or with extra codes
Missing unique identifiers — always look for codebooks with unique codes first
If a unique code doesn't exist, analyze members to determine uniqueness manually
Clean dimension data is the foundation of a reliable model. If needed, perform data cleansing before importing into Farseer.
Step 2: Define Relationships Between Dimensions
After identifying your dimension tables, detect the relationships between them:
Expected relationships: N:1 or 1:1 — these fit naturally into star/snowflake topologies
Warning signs: 1:N or N:N relationships generally disrupt the recommended topology and require special attention
When defining relationships, ensure that:
The snowflake topology is maintained
There are no circular references (loops) between dimensions
Dimension branches don't intersect
Note: Some dimensions may have mappings that change over time (slowly changing dimensions). Be aware of this when designing your model.
Step 3: Prepare Your Data Export
Ensure you export data with all necessary relationship information. There are two common approaches:
Method 1: Fragmented Tables
Multiple separate files of master data and fact data, structured as they are in the source database.
Example for Quantity [SKU, POS]:
Quantity data with foreign keys to SKU and POS
SKU codebook
Brands codebook
POS codebook
Regions codebook
Method 2: Inline Relations (Recommended)
A single denormalized table containing both fact and master data — similar to what you'd use for a pivot table in Excel.
Example for Quantity [SKU, POS] — one table with columns:
SKU Code, SKU Name
POS Code, POS Name
Brand, Region, Supplier
Quantity (fact data)
Inline relations are recommended because Farseer can automatically update master data from these relationships during import. Fragmented tables increase the data processing workload but are not a wrong approach.
Step 4: Validate with Pivot Reports
Before importing into Farseer, open your inline relations data in a spreadsheet and create a pivot view. Test aggregations and compare them with existing client reports.
This confirms that:
Data was exported correctly
Aggregations match expected results
The data will display the same way in Farseer after import
Example: Sales Model Data Structure
A typical sales model dataset includes these dimension groups:
Time: Date, Month, Year (and optionally Week)
Product: SKU Code → Brand → Category levels (Cat1, Cat2, Cat3)
Customer: Client Code → Client Groups
Geography: Profit Center → Region → Country
Document type: Returns, write-offs, etc. (optional)
Fact data columns typically include: Quantity, COGS, Sales Price, Discounts, Net Invoiced Value, and Gross Invoiced Value.
Example: Financial Model Data Structure
A typical financial model dataset contains an aggregated general ledger with:
Accounts: GL Account → Account Description (mapped to report line positions if possible)
Cost structure: Cost Center and/or Profit Center
Time: Date or Month/Year
Partners: Optional partner/client/distributor fields
Fact data columns: Credit, Debit (mandatory), and optionally Start Balance, Balance.
Dimension Table Settings
After creating your dimensions in Farseer, configure these key settings:
Default Member
Used during data import. If a dimensional cell in the import has no data or is NULL, Farseer automatically imports that data to the selected default member. If no default member is configured, the import will fail in this scenario.
Autogenerate Dimension Names
When using templates for data entry or performing bulk member registration, this setting ensures all newly created members have unique values automatically.