Building Fact Models and Dimensions: Associations, Measures, and Deployment
Learn the practical steps and design considerations for building dimensions and fact models in Business Builder, including associations, measure definitions, currency/unit handling, and the deployment and troubleshooting workflow.
Explanation
Once the conceptual purpose of Business Builder is understood, the practical work of a Datasphere consultant centers on constructing dimensions and fact models correctly so that consumption models produce accurate, performant results in SAP Analytics Cloud. This lesson focuses on the implementation mechanics and common configuration decisions. A Dimension in Business Builder is built by selecting a source view (typically from Data Builder or a replicated/federated table) and mapping its key column as the dimension's business key. Attributes (descriptive columns) are added and can be flagged for display, and hierarchies can be defined if the dimension supports parent-child or level-based structures (for example, a Cost Center dimension with a hierarchy for cost center groups). Time dimensions often use Datasphere's built-in time data support, letting consultants avoid manually building calendar tables. A Fact Model requires at least one dimension association and one or more measures. Associations are the mechanism connecting a fact model's foreign key columns to a dimension's business key; getting this association right is critical because an incorrect association (wrong cardinality assumption, or joining on a non-unique key) can silently multiply rows in the resulting consumption model, inflating totals such as revenue or quantity. Consultants should verify the grain of the underlying fact source view before mapping associations—one fact row per transaction line is the typical expectation for a sales fact model. Measures are defined on numeric columns from the fact source, and this is also where currency and unit-of-measure handling is configured. Business Builder allows a measure to be tagged with a currency or unit reference column so that SAC and other consumers correctly aggregate and format monetary or quantity measures (for example, showing amounts converted into a reporting currency using Datasphere's currency conversion capabilities, when configured). Consultants must confirm that the underlying source data actually carries a currency code column if currency-aware measures are expected; without it, currency conversion features will not function correctly. Deployment is an explicit action separate from saving. After saving a dimension or fact model, the object must be deployed, which triggers Datasphere to validate object consistency (data type matches on association keys, existence of underlying views, no circular references) and to create the deployed runtime artifact. Deployment errors are the most common troubleshooting scenario at this level: typical causes include changed source view column names or types after a fact model was already built, dimensions not yet deployed themselves (a fact model generally cannot deploy successfully if its associated dimension has not been deployed), or removed columns in an underlying replicated table breaking a previously working association. Consumption Models are then built on top of one or more deployed fact models, letting the modeler choose measures, dimensions, and expose the result for SAC live or import connections, or for other SQL/ODBC-based consumption. At this stage, consultants often add calculated business measures, restrict which technical columns are exposed, and set default variables to guide business users toward correct usage. Performance considerations become relevant here: overly broad consumption models exposing many unused dimensions increase query complexity in SAC stories, so consultants should scope consumption models tightly around specific reporting use cases rather than building one giant universal model. For intermediate practitioners, the key skill is diagnosing why a deployment fails or why numbers look wrong (usually a grain/association issue) rather than just knowing how to click through the UI.
Real project scenario
A consultant is asked to fix a sales dashboard in SAC where total revenue appears roughly double the expected value. Investigating the underlying Business Builder Fact Model, they discover the association between the Sales Fact and the Customer Dimension was built against a customer view that had duplicate rows per customer (due to a slowly changing dimension source with multiple historical records), causing each fact row to join against multiple dimension rows and inflate revenue. The fix involves correcting the source view in Data Builder to expose only the current customer record per key, then redeploying the dimension and fact model.
Common mistakes
• Building fact model associations without first verifying that the referenced dimension's business key is truly unique, causing row multiplication in consumption models. • Trying to deploy a fact model before its associated dimensions have been successfully deployed, resulting in confusing dependency errors. • Omitting a currency or unit reference column mapping on monetary/quantity measures, so currency conversion or formatting does not work as expected in SAC. • Building one large, all-purpose consumption model instead of use-case-scoped models, leading to slow queries and confusing variable prompts for business users. • Not re-deploying downstream fact models and consumption models after a change to an upstream dimension or source view, leaving stale metadata in production.
Best practices
• Always validate dimension key uniqueness in the source view before building associations in a fact model. • Deploy objects in dependency order: dimensions first, then fact models, then consumption models, and re-deploy the full chain after upstream changes. • Scope consumption models narrowly to specific reporting or planning use cases rather than building one large universal model. • Confirm currency/unit reference columns exist in source data before configuring currency-aware or unit-aware measures. • Use test queries or sample data previews after each deployment step to catch row-multiplication or missing-data issues early, before promoting to production spaces.
Interview angle
Expect scenario-based questions such as diagnosing inflated totals in a report, which tests understanding of association cardinality and dimension key uniqueness rather than rote UI steps. Be prepared to explain the deployment dependency order (dimensions before fact models before consumption models) and to describe how currency/unit-aware measures are configured, while being clear about what depends on source data quality versus what Business Builder itself can control.