Star schema

Finish and deploy a star schema plan

When your dimensions and facts are built, the last stage is to check the plan, generate the master procedure that runs every load in order, and get the scripts into your database. All of this happens from the plan dashboard toolbar.

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

[screenshot]
A completed plan dashboard: every item with a green check, the toolbar, and the "Master procedure … generated" confirmation.

The toolbar

Button What it does
Build Pipeline Builds every item that isn’t complete, in the background. See Build each dimension and fact.
Validate Plan Checks the plan and shows the results.
Download All Scripts Downloads every generated script as one zip.
Edit Plan Settings Changes the plan’s name, naming conventions, and AI guidance.
Regenerate Procedures Regenerates every completed item’s load procedure.
View Artifacts Opens the plan’s Pipeline on its mappings page.
Generate Master Procedure Creates the procedure that runs every load in order.

The last three appear once the plan has a Pipeline.

Validate the plan

Click Validate Plan. The Plan Validation dialog lists errors and warnings, for example a fact whose lookup points at a dimension that no longer exists. The same checks run before Build Pipeline and Generate Master Procedure, and errors block both.

Generate the master procedure

The master procedure calls every item’s load procedure in execution-group order: dimensions first, then facts.

  1. Click Generate Master Procedure.
  2. If validation finds errors, the Plan Validation dialog opens instead. Fix them and try again.
  3. If the Pipeline’s execution groups no longer match the plan’s dimension lookups (for example, after you edited a lookup), Refresh Execution Groups asks to update them. Click Refresh.
  4. If items aren’t built yet:
    • Any dimension or fact that isn’t built stops you with Plan Not Complete. Build those items first.
    • If only the date dimension or bridge items are left, Incomplete Items lets you click Generate Anyway.
  5. A toast confirms the name and line count. A green banner reads Master procedure <name> generated — n lines orchestrating m procedure(s). Click Open the pipeline to see it.

Build Pipeline can generate the master procedure for you at the end of a full build.

Regenerate load procedures

Click Regenerate Procedures after you change something that affects the loads, such as a dimension lookup or a Pipeline mapping. Kenseme regenerates every completed item’s procedure in build order and shows one toast per item. The date dimension and bridge items are skipped.

Generate the master procedure again afterwards if the set of procedures changed.

Download all scripts

  1. Click Download All Scripts.
  2. Optionally pick an environment under Resolve object names for environment (optional). Kenseme then fills in that environment’s schema names inside the scripts. Leave it blank to keep the raw placeholders; the README explains how to replace them.
  3. Click Download.

You get one zip named starschema_<plan>_<yyyyMMdd>.zip. It contains a README.md with the run order, then numbered scripts in dependency order (dimensions before facts). For each item:

  • NN_<item>_view.sql: the source view, when the item has one.
  • NN_<item>_table.sql: the table DDL.
  • NN_<item>_load.sql: the load procedure. For Databricks it is NN_<item>_load.ipynb.

The zip also includes the date dimension script and the master procedure (NN_<name>_master.sql or .ipynb) when they exist.

Deploy to your database

Kenseme doesn’t run anything against your database. Two ways to deploy:

  • From the zip. Run the scripts in their numbered order, as the README describes.
  • From schema versions. The tables, procedures, and master procedure live in the destination schema, and source views in the source schema. Create a version of each schema, then generate Create Objects and Load Script from it. This keeps the deployment tied to a version you can compare and roll back. See Generate a script from a version.

Tip: Keep the plan, not the downloaded scripts, as the source of truth. If you edit a generated script by hand, regenerating overwrites the edit in Kenseme. Change the plan, lookups, or Pipeline mappings instead, and regenerate.

Change things after the plan is complete

  • Re-open any item from its card and repeat a step. Regenerating a procedure replaces the old one.
  • Edit dimension lookups in Bus Matrix, then Regenerate Procedures and Generate Master Procedure.
  • Fine-tune column mappings and load strategies in the Pipeline (View Artifacts).
  • Run a drift check when the source schema changes. See Keep a plan in step with its source.