Testing data products and analytic models

Replicated tables are raw material. Users consume data products and analytic models, which add filters, joins, calculations, currency logic and access rules. These are the layers where business logic lives, and where most data-layer defects are introduced. This lesson shows how to test them with counts, sums and rule checks.

What an analytic model adds

In SAP Datasphere, an analytic model sits on a fact source (for example billing items joined to product costs) with associated dimensions (customer, product, company code, time), measures (net revenue, cost of goods sold, margin), calculated and restricted measures, and variables. It is what SAC stories and other consumers query. Each of those elements is a rule that can be wrong: a join that drops rows, a restricted measure with the wrong filter, an exception aggregation that sums what should be averaged.

Rule checks with a worked example

Start with a fully known case. Invoice 90001877 bills sales order 1000457 for Great Lakes Fleet Services: 50 × CMC-ALT-100 at 240.00 = 12,000.00 USD, tax-exempt. Standard cost in Detroit is 150.00 USD, so cost of goods sold is 7,500.00 USD and margin 4,500.00 USD (37.5%). Filter the analytic model to this invoice: every measure must show exactly these values. Then run the same check for a Chicago-sourced sale at the 156.00 USD standard, and for a document that should be excluded, such as a pro forma invoice.

The Chicago defect is typical: the product cost join used product and period but not plant, so every sale took the first cost found. Totals for September would have looked plausible, because Chicago volumes are smaller. Only a rule check with a known plant-specific case found it. Then add reconciliation at the model level: total net revenue for 1710 in September in the model must equal the revenue accounts in the journal for the same period, or the documented difference (for example freight posted to a separate account) must be explained.

Testing data products

• Contract: compare the data product's published structure (entities, fields, keys, descriptions) with what consumers rely on.
• Completeness: counts and totals against the source, as in lesson 3, for each entity.
• Semantics: units, currencies, time zones and code lists arrive usable for consumers in Datasphere and in partner platforms.
• Access: a user without permission for company code 1910 cannot see 1910 rows through the product, the model or a share.
• Lifecycle: version and status recorded at test time, as the baseline for regression (lesson 5).

Variables, hierarchies and exception aggregation

Models rarely fail on simple sums. They fail on the features that make them useful. Test each variable with boundary values: a period range that crosses fiscal years, a company code variable left empty, a currency variable switched from USD to MXN once 1910 joins. Test hierarchies with a customer that moves between nodes, and check that totals per node add up to the parent. Test exception aggregation, for example “average margin per customer”, against a hand calculation for three customers, because it behaves differently from a simple sum when filters change.