Dimensional Accelerator
The Dimensional Accelerator derives a star schema from the source objects you select: dimensions and facts with their keys, history settings, grain and measures. It reads staging and persistent staging tables, or a Data Vault with point-in-time tables, and derives the star into the target data mart project you pick. The star shows as cards on a canvas, where you shape it with menu verbs and panels before anything is written, and Publish writes the whole star in one save together with the source views that load it. To open it, go to the Accelerator page, which asks how BimlFlex should build your target model, and choose Open Dimensional on the Dimensional card. You can also open it from the Dimensional flow card on Sample Home, from the Dimensional link in the model step of the first build checklist, or from the Dimensional Accelerator entry of the command palette. For the Data Vault counterpart, see the Data Vault Accelerator. For a guided first pass, follow the Dimensional Accelerator Walkthrough.
Opening the Accelerator
The accelerator derives into a target project. A project qualifies when it is a data mart accelerator target, which means its Target Connection is on the Data Mart integration stage. Parent projects are not offered. When exactly one project qualifies, the page selects it and hides the picker. Otherwise, pick the target with the Project: picker in the toolbar.
The target connection of that project is where the derived dimensions and facts live. The dialogs that add standard dimensions show it as their read only Connection field.
When no project qualifies, the walkthrough strip states that it found no eligible projects and that the Dimensional Accelerator needs a Project whose Target Connection is on the Data Mart integration stage. With the strip hidden, the page shows a warning that it could not find any Projects that can feed the Dimensional Accelerator. Give the project whose objects you want to accelerate a target connection on the Data Mart integration stage, then reload the page. With the strip hidden and no project selected, the page reminds you that the Dimensional Accelerator requires a Target Project to be selected.
The walkthrough strip over the canvas guides a first pass in three steps. Close it with its close button, and bring it back with Show guide.
Every change you make on the page is a pending change until you save or publish. If you leave the page with pending changes, BimlFlex asks you to confirm first.
Selecting Sources
Select the source objects for the star in the tree on the left. Add sources on the command bar opens the tree and puts the cursor in its search box. The tree offers objects on four integration stages: Staging Area, Persistent Staging Area, Data Vault and Business Data Vault. An object on a connection of any other stage is left out of the star with a diagnostic.
Every selected object needs a dimensional role, Dimension or Fact. The role comes from the object's accelerator type when that type is a data mart type, and otherwise from the business object type of the business entity the object is linked to. BimlFlex never gives an object a role by default.
- A source that can take a role but has none lands on the canvas as a gray card marked Needs a role, with its columns. Right click the card and pick Make this a dimension or Make this a fact. The card becomes that object in place.
- Satellites, reference satellites, link satellites and point-in-time objects have no dimensional role of their own. They derive through the hub or link they are built over, so select that hub or link. Selecting one of them adds a diagnostic that names the object to select instead.
- An object whose role resolves to a type other than Dimension or Fact is left out with a diagnostic, since only Dimension and Fact targets are derived.
From a Data Vault or Business Data Vault connection, a hub with the Dimension role derives a dimension through its point-in-time table, and a link with the Fact role derives a fact at the grain of the link.
In the second step of the walkthrough, Start from the business model lists the business entities whose linked objects carry a dimensional type. Tick the entities you want and choose Add to add their objects to the selection. When no business entity is typed for the dimensional model, select source objects directly in the tree.
The Derived Star
BimlFlex re-derives the star from your metadata whenever the selection or a pending edit changes, so the canvas is a live preview. Dimension roles derive before fact roles, so a fact finds its dimensions already on the canvas.
From a staging or persistent staging source:
- A dimension gets a generated surrogate key, a business key taken from the source's integration key, and one attribute for each remaining column. A source column that tracks history keeps history on the dimension, and every other new attribute takes the change type of the DmDefaultChangeType setting.
- A fact takes its grain key from the source's integration key. A column that references another object becomes a foreign key to that object's dimension. Numeric columns default to measures. Byte and Boolean columns, which usually carry flags, and every other column default to degenerate attributes.
From a Data Vault source:
- A hub derives its dimension by walking its point-in-time table. The business key comes from the hub's integration key, and every business column of the satellites the point-in-time table covers becomes an attribute. When a hub has several point-in-time tables, the first by name is used and an info diagnostic says so.
- A link derives its fact at the link's grain: the grain key from the link's primary key, one foreign key for each hub the link references, and measures and degenerate attributes from the link satellites. Every hub of the grain must resolve to a dimension, otherwise the whole fact is left out with an error on that hub.
The surrogate key of a dimension is an integer identity key while DmUseIdentityKeys is on, its default, and a hash key when it is off. Dimension and fact names come from the business name of the source through the DmNamingConvention setting, and their schemas from DmSchemaDim and DmSchemaFact, falling back to DmDefaultSchema.
Each card states where it comes from under its name:
- A dimension card names its source, for example from staging Customer or from hub Customer and its point-in-time table, with a Type 2 chip when any column tracks history and a Type 1 chip otherwise. Hover the chip to read which columns track history, and hover the line to read which point-in-time table the dimension derives through.
- A fact card names its grain from the dimensions it references, for example grain: Order, Product, with a chip that counts its measures. Hover the chip to read the measure names.
- A card whose role is an unsaved edit leads with a Pending chip until you save or discard.
The display options button of the toolbar holds four checkboxes. Expanded shows every table expanded in the diagram, and it is on by default. Columns shows or hides the columns of all tables, Data Types shows or hides the data types of the shown columns, and Excluded shows excluded columns. The toolbar also carries Refresh and auto layout, Download Preview for Printing, the zoom buttons and Open Side Bar.
The Dimension and Fact Panels
A single click on a card points the panel in the right drawer at that card. Object Details in the card menu opens the drawer on the Dimension panel for a dimension card or the Fact panel for a fact card. Every change on a panel is a pending edit that re-derives the star.
Both panels carry:
- Role: a Dimension and Fact switch when the source object can take a role. Switching runs Make this a dimension or Make this a fact, and after a switch the drawer follows the card to the other panel.
- Name: writes the source object's business name. The hint under it shows the name the derivation gives the target, for example Derived name: followed by the dimension name under your naming settings.
- Business Entity: links the source object to a business entity, shown when business modeling is available.
- Source Objects: the objects the card derives from, each with an edit button, Edit Object, that opens the object in a dialog.
The dimension panel adds:
- History: Track changes or Track history for every attribute column at once, with each column's own switch beneath. When the columns differ, the panel says they are mixed and lets you pick one value to set them all.
- Business key: the key columns, each with a sentence naming where it comes from, for example from the integration key of its source.
- Unknown and Not applicable rows: the Add Unknown and Not applicable rows checkbox, on by default.
The fact panel adds:
- Grain: the grain key columns, each with the sentence naming where it comes from.
- Measures: a Measure or Attribute switch for each column that can be either.
- Dimensions referenced: the dimension cards the fact's foreign keys point at.
With no card selected, the panel asks you to select a dimension or fact card.
Shaping the Model
You change the model with verbs. Right click a card, a row or an empty part of the canvas to see the verbs that apply there. The Model button on the toolbar lists the same verbs for the selected rows, with the number of rows or tables each verb applies to, and lists the canvas verbs, which need no selection. A verb that applies to none of the selected rows is disabled. Every verb writes pending metadata edits and re-derives the star, so nothing is written to the repository until you save or publish.
Role Verbs
Make this a dimension and Make this a fact sit on the card menu. Each writes the role on the card's source object, and the card changes role in place. A verb is hidden when the card already has that role, when the card has no source object, when the source is a satellite, reference satellite, link satellite or point-in-time object, and on a dimension card that holds attributes moved off another card.
Rename in Place and Move Attribute Back
Double click a dimension or fact card name, or the name of an attribute or measure row, to rename it in place. Type the name and press Enter to write it as the source object's or source column's business name. Escape, an empty value or leaving the input closes it without a change. Rename on the card and row menus opens the same input. A card whose source carries a business entity, a gray card, a key row and a row whose source column carries a business attribute take their names from elsewhere, so a double click there opens Object Details or Column Details instead.
Move Attribute to New Dimension on a dimension attribute row asks for a new dimension name and moves the selected attributes onto that dimension, which gets its own surrogate key and the same business key as its origin. Move Attribute Back on a moved row moves the column back to its source's own dimension.
The History Choice
On a dimension attribute row, Track History turns on history tracking for the selected columns, and Track Changes sets them to change in place. The History field of the dimension panel sets every attribute column of the dimension in one act. A dimension with any history tracking column carries the Type 2 chip.
Measures, Attributes and Exclusions
On a fact row, Set as Measure and Set as Attribute switch a column between a measure and a degenerate attribute. The fact panel's Measure and Attribute switch does the same.
Exclude leaves a column out of the dimensional model, and Include brings an excluded column back. Exclude is not offered on key rows. The Delete key runs Exclude, and the Model menu shows Delete beside it. Delete marks a target only column deleted, and Undo Delete clears the mark.
Link to Dimension
Link to dimension on a fact card whose source sits on a Staging Area or Persistent Staging Area connection opens the Link to Dimension dialog. The dialog shows the Fact and asks for the Dimension, the Fact column and the Key column. Picking a dimension fills both columns when exactly one fact column and key column pair match by name. Link writes the reference on the fact source column, and the fact derives a foreign key to the dimension.
Date Dimension
Add date dimension is a canvas verb: right click an empty part of the canvas, or open the Model menu. When the project already has a date dimension, BimlFlex offers to use it, with Use existing and Cancel. Otherwise the Create Date Dimension dialog opens with these fields:
| Field | Default and rule |
|---|---|
| Business Name | Date |
| Table Name | Follows the business name through the dimension naming convention until you edit it |
| Schema | The dimension schema |
| Connection | The target connection, read only |
| Start Date | The first day of 2000 |
| End Date | The last day of 2049. It must be a later day than the start date |
| First Day of Week | Sunday, or Monday |
| Fiscal Year Starts In | January. A later month adds the FiscalYear, FiscalQuarter, FiscalMonth and FiscalYearQuarter columns |
| Add Unknown and Not applicable rows | Ticked |
| Columns | Twenty three calendar columns, all ticked, with DateKey and Date locked |
DateKey is the integer key in yyyymmdd form and Date is the business key. Create stays disabled while a name or the schema is empty, while the end date is not after the start date, and while another object on the target connection has the same schema and name, which the dialog names. Closing the dialog with changes asks you to confirm before they are discarded.
With a date dimension in the project, every date typed fact column derives a foreign key to DateKey with the column's business name as its role, so each date column of a fact keeps its own key. Date dimension settings on the Date card opens the same dialog as Date Dimension Settings with a Save button, which writes only the fields you change.
Time Dimension
Add time dimension is a canvas verb as well. When the project already has a time dimension, BimlFlex offers to use it. Otherwise the Create Time Dimension dialog opens with Business Name (Time), Table Name, Schema, the read only Connection, Grain (Hour, Minute or Second, with the row count of each, Minute by default), Business Hours Start (08:00), Business Hours End (17:00, after the start) and Columns, fifteen clock columns with TimeKey and Time locked.
With a time dimension in the project, every fact column that carries a time of day derives a foreign key to TimeKey. A time column takes its business name as the role of its key, and a timestamp column OrderDate gives the role OrderTime. Time dimension settings on the Time card opens the dialog as Time Dimension Settings.
Currency Dimension
Add currency dimension is a canvas verb. When the project already has a currency dimension, BimlFlex offers to use it. Otherwise the Create Currency Dimension dialog opens with Business Name (Currency), Table Name, Schema, the read only Connection, Reporting Currency (USD), a Create the exchange rate table checkbox, ticked, that names the table it creates, Add Unknown and Not applicable rows, ticked, and Columns: CurrencyKey (the ISO 4217 numeric code), CurrencyCode (the business key), CurrencyName and IsReportingCurrency.
The currency dimension holds every currency of the ISO 4217 list. The exchange rate table, named Exchange Rate through the fact naming convention, carries RateDate, FromCurrencyCode, ToCurrencyCode and Rate, and shows as a fact card with no source. To reference the currency dimension from a fact, use Link to dimension.
Junk Dimension
Select two or more flag rows of a fact card, degenerate attribute rows whose source is the card's own staging or persistent staging object, and choose Move flags to a junk dimension. The Create Junk Dimension dialog asks for Business Name (the fact's business name followed by Flags), Table Name, Schema, the read only Connection, the Key style and a Name for each flag column. The key style is Identity key with a lookup by default, or Hash of the combination, which needs the HashAlgorithm, HashBinary and RowHashKey configurations on the target connection. The fact then carries one foreign key to the junk dimension in place of its flag columns.
Role Views
A role view gives a reporting model one table per role of a dimension that a fact references through several keys. Add role view on a dimension card opens the Create Role View dialog with Role Name, View Name, Schema, the read only Connection, Prefix column names with the role (ticked) and Columns, with the key columns locked. Add role view is offered on a dimension whose object is stored on the target connection or added there on this page.
The role view shows as its own card beside its dimension, with a chip that names the role. A fact key whose role matches the view draws its edge to the role view card. Role view settings on the role view card opens the dialog as Role View Settings, and Delete on it removes the view.
The Unknown and Not applicable Rows
Every dimension carries an Unknown row with the key -1 and a Not applicable row with the key -2, so a missing or unmatched key in a fact maps to the Unknown row. The rows are on by default. To switch them off for a dimension, untick Add Unknown and Not applicable rows in the Create Date Dimension or Create Currency Dimension dialog, or on the dimension panel. A fact that references a dimension with the rows off raises a warning.
Undo and Redo
Undo and Redo on the command bar step through the pending edits you made on this page. Their tooltips name the verb and the target, for example Undo Exclude on Address.City. Cmd Z undoes and Cmd Shift Z redoes, with Ctrl in place of Cmd on Windows. The keys belong to a form field or a dialog while you type in one.
Each changed field is one step, so a verb that changes several rows records one step for each row. Save, Publish and Discard clear the history.
Diagnostics
The Diagnostics tab on the right edge of the canvas lists what the derivation, the source view generation and the last publish found, with a count. Its summary line counts errors, warnings and info, and the rows list errors first, then warnings, then info. Each row names the object you act on, states the cause and the remedy, and lists related objects in parentheses.
| Diagnostic | Cause | Remedy |
|---|---|---|
| Missing role (warning) | A selected or referenced object has no dimensional role, or it is a satellite, reference satellite, link satellite or point-in-time object | Give the object a role, or select the hub or link it is built over. A referenced object without the Dimension role keeps its reference column as a fact attribute until you set the Dimension role on it |
| Unsupported role (warning) | The role resolves to a type that is neither Dimension nor Fact | Give the object the Dimension or Fact role |
| Unsupported source stage (warning) | The object's connection is not on a Staging Area, Persistent Staging Area, Data Vault or Business Data Vault stage | Select objects on a supported stage |
| Missing integration key (error) | The source has no integration key column, so its dimension or fact has no business key or grain | Mark the business key or grain key column as the integration key |
| Missing hash configuration (error) | Hash surrogate keys are selected but the HashAlgorithm, HashBinary and RowHashKey configurations are not available | Provide the configurations, or use identity keys, which need no configuration |
| Missing point-in-time (error) | A hub has no point-in-time object, so its dimension, or a fact at its grain, cannot be derived | Choose Create point-in-time on the row to create one over the hub and its satellites |
| Multiple point-in-time objects (info) | A hub has several point-in-time objects | None needed: the first by name is used |
| Missing configuration (error) | A source that the view filters to its current row resolves no RowEffectiveFromDate configuration, so the filter cannot be built and the view is not generated | Configure RowEffectiveFromDate for the object, its connection or the project |
| Unsupported dialect (warning) | The source connection has no source view dialect. The SQL Server family and Snowflake are supported | None on the page: the target derives and publishes without its source view |
| Hand edited source SQL (warning) | The SQL of a source view changed outside the generator | None needed: Publish leaves the view, its columns and its mappings untouched and still publishes the target |
| Proxy collision (error) | An object that is not a view occupies the source view name | Rename or remove the object to free the name |
| Date, time or currency dimension without fact (warning) | No selected fact references the dimension, so its view has no staging connection to sit beside | Select a fact with a date or time typed column, or link a fact column to the currency dimension with Link to dimension |
| Unknown and Not applicable rows off (warning) | A fact references dimensions whose Unknown and Not applicable rows are off, so a missing key stays null | Tick Add Unknown and Not applicable rows on the dimension panel |
| Hash keyed dimension (warning) | A dimension keeps a stored key that is not an identity column and not the hash key shape of its target, so the generated load fills it by neither path | Mark the key as an identity column, give it a mapping or an expression of its own, or create it in the hash key shape of its target |
| Junk dimension column changed (warning) | A junk dimension names a fact source column that is gone or has a different shape | Restore the column, or create the junk dimension over the flags as they read |
| Junk dimension with no match (warning) | A junk dimension finds no fact column to key on | None stated: the junk card still shows and no junk key is derived |
| Role playing keys (info) | A dimension is referenced through two or more role keys and a role has no role view | Choose Add role views on the row to open Create Role View once for each missing role |
A missing role row carries Suggest roles when the AI features are enabled, and when its object can take a role, Dimension and Fact buttons that set the role directly. With the AI features enabled, every row also carries Explain, which adds a plain explanation under the row with an AI chip. The explanation covers that diagnostic only, and the message above it stays the record.
Draft with AI
The Draft with AI menu appears on installations with the AI features enabled. Every entry drafts suggestions, and nothing changes your metadata until you accept a suggestion.
- Suggest roles classifies the selected sources as dimensions and facts. It needs selected sources.
- Draft SCD policy drafts history tracking policies for each column of the derived dimensions.
- Review measures & attributes reviews the measure and degenerate attribute designations and the dimension splits.
- Critique this star asks for read only review findings over the previewed star. Findings never write metadata.
The last three need a derived star on the canvas. Draft SCD policy asks you to classify roles first when no selected source resolves as a dimension, and Critique this star when no selected source resolves a dimensional role.
Suggestions land in the Proposals tab on the right edge of the canvas, under Proposed changes. Each row names the object, the change, the reasoning and a confidence, with Accept and Skip. A row that would change a role the object already has carries Check first: changes the current role. A row that turns on history tracking carries Confirm: history will be kept from first load and needs its own Accept. Accept all reviewed accepts every other row still waiting for review. Critique findings show under Review findings with an AI review chip and carry no buttons. An accepted row writes the same pending edits as the matching verb. When you publish with suggestions still waiting for review, the Unreviewed draft proposals confirmation reminds you that unreviewed suggestions are not published, with Publish and Keep reviewing.
Proposing and Critiquing a Rule Set
A rule set models a table's business rules: its inputs, joins, filters and grouping, and one rule for each output column, from which BimlFlex generates the view SQL. The Draft with AI menu carries two rule set verbs, each with a Preview chip.
- Propose a rule set drafts a new view's inputs, joins, filters, grouping and one rule per output column from the selected sources. It needs selected sources. A dialog first asks for the schema, the name and the purpose of the view the proposal creates. The view lands on the connection and project of the first selected source.
- Critique this rule set asks for a reviewed set of changes to the rule set of a modeled view. It is enabled when the selection holds exactly one modeled view.
The proposal shows in the Proposals tab as one table row with the column rules grouped under it, each with Accept and Skip, and Accept all reviewed. Accepting the table row adds the view and its rule set as pending changes, and accepting a column row adds that rule and its output column. A skipped rule stays out until you add it by hand. Save then saves the records and applies the rules of each view an accepted row wrote, so the server generates the view SQL. When the SQL of a view cannot be written, Save shows a warning that names the view and the reason: the view is not found when the objects are read back, the view carries no rule model, or the license refuses the apply. The records already saved stay saved. Discard drops the rule set proposals together with the view, rules and columns they added. To edit the rules one at a time afterwards, use the Rules tab of the view, described in the Rules Editor guide.
Saving and Publishing
The command bar separates two kinds of writes.
Save writes your pending source metadata edits: roles, business names, history choices, measure choices, exclusions, references and the objects the dimension verbs added. The derived star itself stays a preview. Discard drops every pending edit and derives the star from the stored metadata. Changes lists the pending changes.
Publish writes the star. It derives the full star, generates a source view for every derived dimension and fact, and commits the dimensions, the facts, the source views with their column mappings and your pending source metadata edits in one save. A gray card is not published: Publish writes only dimensions and facts. Before it writes, Publish runs a safety checkpoint step, and its failure never blocks the publish. When the save succeeds, BimlFlex reports Publish succeeded, and the Diagnostics tab shows what the publish found.
The source view of a derived dimension or fact sits beside the primary source of its target, on the same connection, project and schema, and carries the column mappings that load the target:
- A staging source gets a view that selects the target columns. A persistent staging source gets a view that keeps the current row of each key.
- A Data Vault dimension gets a view over its point-in-time table, joined to the hub and the satellites, and a link fact gets a view over the link, its hubs and its link satellites.
- The date dimension gets a calendar view with one row per day of its range, the time dimension a clock view with one row per hour, minute or second as its grain sets, and the currency dimension a view of the ISO 4217 list. Each sits beside the source of the first fact that references it. The calendar and clock views carry the Unknown and Not applicable rows when they are on.
- A junk dimension gets a view of the distinct flag combinations of its fact source, and each role view gets its SQL generated over its base dimension.
Source views are generated for the SQL Server family (SQL Server, Azure Synapse, Azure SQL Database, PDW, Microsoft Fabric Warehouse and Microsoft Fabric SQL Database) and for Snowflake. Every publish also stamps each dimension with its Unknown and Not applicable rows choice, so the generated load reads the choice on the object it loads.
Publishing the same star twice updates the same views instead of adding new ones. BimlFlex records a fingerprint of the SQL it generates for each view and detects hand edits: a view whose SQL you changed outside the accelerator is left untouched, with its columns and mappings, and a warning tells you so while its target still publishes.
Settings
These settings govern the derivation. Each name links to its setting page.
| Setting | Default | Governs |
|---|---|---|
| DmDefaultSchema | dm | The schema of derived dimensions and facts |
| DmSchemaDim | Blank | A schema for dimensions in place of the default schema |
| DmSchemaFact | Blank | A schema for facts in place of the default schema |
| DmNamingConvention | Inherit | The naming convention of derived objects |
| DmColumnNamingConvention | Inherit | The naming convention of derived columns |
| DmDefaultChangeType | CHG | The change type of new dimension attribute columns |
| DmUseIdentityKeys | Y | Identity surrogate keys when on, hash keys when off |