Text is not a member list
Split before filtering
Count the fact once
A bridge holds the many-to-many
Version the split rule
Tags packed in one field are not dimension members yet
An order field contains “livestream, short video, store.” An equality filter for livestream misses the row, because the field is not equal to livestream. A contains filter also matches livestream replay and any other value that shares those characters. If the field is split into three rows before counting orders, this order becomes three orders. Keep the raw text if you need it. Do not treat it as members of a dimension.
One fact row is one business event. Tags and orders are many-to-many. A star schema filters facts by dimension members, not by a substring. Either build a bridge, one row per order and tag, or declare that the field is searchable text and is not valid for metrics.
The split rule changes the members
Separators may be commas, ideographic commas, or spaces. Values may have padding, different case, or synonyms. Whether livestream and livestream room are one member belongs in a map, not in a model’s guess. When the split rule changes, historical members change, so version the rule. An empty field can mean no tags or unknown. Do not explode a blank member and then let every filter count it.
Order inside the string is a separate question. “Short video, livestream” can match the same set as two bridge rows, but “first tag” exists only in the text order. If the business does not honor order, do not offer a primary tag unless another field actually stores one.
Counts and amounts stay at fact grain
An order of 100 with two tags can show 100 under each tag. The sum across tags must not be presented as company sales. Company sales still count the order once. A bridge answers “amount of orders that have this tag.” It does not answer “sum of bridge rows.” Put that on the metric: amount containing a tag, versus order amount.
Kimball’s grain declaration decides whether rows may be added. If the metric does not say how duplicates are prevented, a query will sum the exploded rows. Two campaigns will each claim the order, and the total will exceed the store.
Two paths
Path one is a bridge plus dimension members. Filters, permissions, and counts land on members. It suits tags that participate in official metrics. The cost is maintaining the split and the reconciliation. Path two leaves the raw field beside the fact for display or search. Metrics never group by it. It suits tags that are still notes.
The wrong path is a mix: contains in one report, equality in another, and a sum of exploded rows in a third. Those are three metrics.
Counterexamples and acceptance
Counterexamples: an equality filter misses “livestream, short video”; a contains filter counts livestream replay as livestream; tag amounts add up to more than store sales; a blank tag becomes a real channel. Prepare one dual-tag order, one blank, and one near-synonym. Check that the livestream amount counts the order once, and that the sum of every tag is not presented as total sales.
BuildTable.ai can be a candidate semantic layer for marking a multi-value field as a bridge or as text. This article does not show that the current release already splits tags or blocks double counting. Verify it on the field you have.
Public references
Build an AI-ready data foundation
Contact us to discuss your data modeling scenario and deployment support.
Contact us