Star schema

Build each dimension and fact

Once a plan exists, every dimension and fact is built the same way: confirm where its data comes from, create its table, then generate its load procedure. You can walk one item through the steps yourself, or let Kenseme build every remaining item in the background.

In the app: Data Model › Schemas › your source schema › Star Schema tab › a plan

[screenshot]
A plan item in Step A with the A Source, B Table, C Procedure step indicator at the top and the drafted source view below.

Read the plan dashboard

The header shows source → plan → destination and how many items are complete, for example 3/8 complete.

Under Plan Items, items are split into Dimensions and Facts, in build order: DimDate first, then the other dimensions A–Z, then facts by execution group. Each card shows:

  • A status icon: an empty circle (not started), a clock (in progress), or a green check (complete).
  • The name and type badge, for example SCD2 or Transaction.
  • A Group n badge on facts that build after other facts.
  • Progress so far, for example View ✓ Table ✓.
  • The source, for example View needed, Direct source, or the fact’s grain.
  • For facts, Waiting on: followed by the dimensions that must be built first.
  • Once complete, View created, Table created, and Procedure created badges.

Click Start Building → (or Continue Building →) on a card to open that item at the step it has reached.

The three steps

A step indicator shows A Source, B Table, and C Procedure, with a check on each step that is done. Click a completed step to go back to it. Back to Plan returns to the dashboard at any point. Your progress is saved after each step.

Step A: Source

Every item needs one source: an existing table or view in the source schema, or a new view that combines several tables.

When one table is enough, you see Verify the source table or view has the columns you need:

  1. Check the table picked in Source, or pick another from Tables and views from source schema.
  2. Review Source Columns: Column, Type, Nullable, and PK.
  3. Under Looks right?, choose Yes, this table has everything I need, or No, I need a different source.
  4. Click Confirm & Next >.

If the plan named a source table that doesn’t exist in the schema, a message says so and asks you to pick the correct one.

When the item combines several tables, Kenseme drafts a source view for you:

  1. The step opens with Drafting source view… while AI writes the view SQL from the item’s design instruction.
  2. Read Agent notes, if any, and expand Design Instruction to see what the AI was asked to build.
  3. Check View Name. The view is created in the source schema.
  4. Review and edit View SQL. Nothing is created until you click the button in step 6.
  5. If the draft isn’t right:
    • Type Extra guidance for the AI (optional) and click Regenerate with AI.
    • Or click Write SQL myself and write the CREATE VIEW statement yourself.
    • Or click Pick a single existing table instead.
  6. Click Create View & Next >.

If the draft fails, The view draft failed explains why. You can regenerate or write the SQL yourself.

Step B: Table

Step B shows the item’s Source, Destination, and Load Strategy, then the Destination Columns the table will have. The Origin column tells you where each comes from:

  • SurrogateKey: the surrogate key, for example DimCustomerKey, added when the plan adds surrogate keys.
  • Source: a column carried over from the source.
  • Audit: a housekeeping column the load strategy needs, such as load timestamps or history tracking columns.

Click Create Table & Next >. The table is created in the plan’s destination schema.

Step C: Procedure

Step C generates the procedure that loads the table (a Python load function for Databricks).

  1. For a fact, review Dimension Lookups. It shows how the procedure turns each source key into a dimension’s surrogate key:
    • Single-Key Lookups: FK Column, Source NK, Dimension (with a role: badge for role-playing dimensions), Dim NK, and SCD2 Aware.
    • Composite-Key Lookups: each Source Column paired with its Dim Column.
  2. If a fact’s dimensions aren’t built yet, you see Build these dimensions before generating this fact's procedure: with the list. Build those first.
  3. Click Generate Procedure. The result appears under Generated (n lines).
  4. Click Done to return to the dashboard. The item is now complete.

If the source changed since the item was designed, a banner says The source schema changed since this item was designed — its prompts may be stale. Click Refresh Prompts before generating. See Keep a plan in step with its source.

Build the date dimension

DimDate has no source table and no A/B/C steps. It is generated from settings:

  1. Click Start Building → on DimDate.
  2. Set Range Start and Range End.
  3. Check Preset. Keys use the format int yyyymmdd, for example 20260703.
  4. Optionally tick Include fiscal calendar and set Fiscal year starts in month (1–12).
  5. Click Generate Script. The create-and-populate script appears under Generated: <name>.
  6. Click Done.

Generating again replaces the earlier script.

Build the whole plan at once

Instead of stepping through each item, you can build everything that isn’t complete yet in the background.

  1. On the dashboard, click Build Pipeline.
  2. Review the Build Pipeline dialog:
    • Errors under Fix these before building the pipeline: block the build.
    • Warnings are for your information.
    • The groups to build, in order. Items already complete are shown in green and skipped.
    • A count, for example Will build 5 item(s) across 2 group(s); 3 already complete will be skipped.
  3. Leave Generate the master procedure after a successful build ticked to finish with the master procedure.
  4. Click Start build.

A progress card shows which group is building and how many items have finished. Each item card shows Queued, its current step, Built, Skipped, or Failed. Hover over Failed to see the reason and click Retry to open that item’s steps. Click Stop to stop the build. You can leave the page; the plan list shows a Building… badge until the build finishes.

To build a single group, click Build group next to it in Recommended Build Order.

Build runs use AI for source views and count toward your AI usage.

Note: Bridge items are always built by hand. The bulk build skips them and says so.

Add an item by hand

  1. Click + Add Item Manually.
  2. Fill in Item Name (for example DimCustomer) and Category: Dimension, Fact, or JunkDimension.
  3. For a dimension, pick the Dimension Type (SCD1 or SCD2). For a fact, pick the Fact Type (Transaction, AccumulatingSnapshot, PeriodicSnapshot, or Factless).
  4. Pick the Source Table. It is optional for a junk dimension.
  5. Pick the Business Key Columns and, for an SCD2 dimension, the SCD2 Tracked Columns.
  6. Optionally describe the Grain (optional), for example One row per order line.
  7. For a fact, add Dimension Lookups: for each, the source FK column, the dimension, and the surrogate key column. Click Add lookup for more.
  8. Click Add Item.

A toast confirms the item was added. Any warnings appear in an Item added with warnings dialog.

Edit a dimension lookup

  1. On the dashboard, expand Bus Matrix.
  2. Click the X in a cell. Role-playing lookups show their role, for example X (ShipDate).
  3. Change the Fact FK Column, the Dimension Surrogate Key Column, or the Unknown Member Key (used when no dimension row matches; default -1).
  4. Click Save Changes.

Header colours in the plan’s bus matrix show build status: green for complete, orange for in progress, and grey for not started.