Apache Ossie.An open standardfor semantic models.

1# Educational subset adapted from the Northstar Premier demo.
2# Source queries, field expressions and booked_revenue are preserved.
3# Root layout updated; ai_context is an added explanatory annotation.
4version: 0.2.0.dev0
5name: northstar_premier_excerpt
6description: Northstar retail orders and booked revenue — educational subset
7datasets:
8  - name: customers
9    source: northstar_living.dim_customer
10    primary_key:
11      - customer_id
12    fields:
13      - name: customer_id
14        datatype: Integer
15        expression:
16          dialects:
17            - dialect: ANSI_SQL
18              expression: customer_id
19        dimension: {}
20      - name: city
21        datatype: String
22        expression:
23          dialects:
24            - dialect: ANSI_SQL
25              expression: city
26        dimension: {}
27  - name: orders
28    source: SELECT o.* FROM northstar_living.fct_orders o JOIN northstar_living.dim_customer c
29      USING(customer_id) WHERE o.status='delivered' AND NOT o.is_test AND
30      o.delivered_at>=DATE '2025-09-20' AND o.delivered_at<DATE '2026-09-20' AND NOT
31      c.is_employee AND c.identity_status='resolved' AND EXISTS (SELECT 1 FROM
32      northstar_living.fct_order_lines l WHERE l.order_id=o.order_id AND
33      l.delivered_qty>0)
34    primary_key:
35      - order_id
36    fields:
37      - name: order_id
38        datatype: Integer
39        expression:
40          dialects:
41            - dialect: ANSI_SQL
42              expression: order_id
43        dimension: {}
44      - name: customer_id
45        datatype: Integer
46        expression:
47          dialects:
48            - dialect: ANSI_SQL
49              expression: customer_id
50      - name: ordered_at
51        datatype: Date
52        expression:
53          dialects:
54            - dialect: ANSI_SQL
55              expression: ordered_at
56        dimension: {}
57  - name: lines
58    source: SELECT l.* FROM northstar_living.fct_order_lines l JOIN
59      northstar_living.fct_orders o ON l.order_id=o.order_id JOIN
60      northstar_living.dim_customer c ON o.customer_id=c.customer_id WHERE
61      o.status='delivered' AND NOT o.is_test AND o.delivered_at>=DATE '2025-09-20' AND
62      o.delivered_at<DATE '2026-09-20' AND NOT c.is_employee AND
63      c.identity_status='resolved' AND l.delivered_qty>0
64    primary_key:
65      - order_line_id
66    fields:
67      - name: order_line_id
68        datatype: Integer
69        expression:
70          dialects:
71            - dialect: ANSI_SQL
72              expression: order_line_id
73        dimension: {}
74      - name: order_id
75        datatype: Integer
76        expression:
77          dialects:
78            - dialect: ANSI_SQL
79              expression: order_id
80      - name: gross_sales_cents
81        datatype: Integer
82        expression:
83          dialects:
84            - dialect: ANSI_SQL
85              expression: gross_sales_cents
86      - name: discount_cents
87        datatype: Integer
88        expression:
89          dialects:
90            - dialect: ANSI_SQL
91              expression: discount_cents
92relationships:
93  - name: lines_to_orders
94    from: lines
95    to: orders
96    from_columns:
97      - order_id
98    to_columns:
99      - order_id
100  - name: orders_to_customers
101    from: orders
102    to: customers
103    from_columns:
104      - customer_id
105    to_columns:
106      - customer_id
107metrics:
108  - name: booked_revenue
109    expression:
110      dialects:
111        - dialect: ANSI_SQL
112          expression: SUM((lines.gross_sales_cents-lines.discount_cents)*0.01)
113ai_context:
114  instructions: Booked revenue uses gross sales less discounts, in currency units. Inspect
115    source filters and do not treat booked revenue as customer margin.
116  synonyms:
117    - booked sales
118    - sales before refunds

1{
2  "version": "0.2.0.dev0",
3  "name": "northstar_premier_excerpt",
4  "description": "Northstar retail orders and booked revenue — educational subset",
5  "datasets": [
6    {
7      "name": "customers",
8      "source": "northstar_living.dim_customer",
9      "primary_key": [
10        "customer_id"
11      ],
12      "fields": [
13        {
14          "name": "customer_id",
15          "datatype": "Integer",
16          "expression": {
17            "dialects": [
18              {
19                "dialect": "ANSI_SQL",
20                "expression": "customer_id"
21              }
22            ]
23          },
24          "dimension": {}
25        },
26        {
27          "name": "city",
28          "datatype": "String",
29          "expression": {
30            "dialects": [
31              {
32                "dialect": "ANSI_SQL",
33                "expression": "city"
34              }
35            ]
36          },
37          "dimension": {}
38        }
39      ]
40    },
41    {
42      "name": "orders",
43      "source": "SELECT o.* FROM northstar_living.fct_orders o JOIN northstar_living.dim_customer c USING(customer_id) WHERE o.status='delivered' AND NOT o.is_test AND o.delivered_at>=DATE '2025-09-20' AND o.delivered_at<DATE '2026-09-20' AND NOT c.is_employee AND c.identity_status='resolved' AND EXISTS (SELECT 1 FROM northstar_living.fct_order_lines l WHERE l.order_id=o.order_id AND l.delivered_qty>0)",
44      "primary_key": [
45        "order_id"
46      ],
47      "fields": [
48        {
49          "name": "order_id",
50          "datatype": "Integer",
51          "expression": {
52            "dialects": [
53              {
54                "dialect": "ANSI_SQL",
55                "expression": "order_id"
56              }
57            ]
58          },
59          "dimension": {}
60        },
61        {
62          "name": "customer_id",
63          "datatype": "Integer",
64          "expression": {
65            "dialects": [
66              {
67                "dialect": "ANSI_SQL",
68                "expression": "customer_id"
69              }
70            ]
71          }
72        },
73        {
74          "name": "ordered_at",
75          "datatype": "Date",
76          "expression": {
77            "dialects": [
78              {
79                "dialect": "ANSI_SQL",
80                "expression": "ordered_at"
81              }
82            ]
83          },
84          "dimension": {}
85        }
86      ]
87    },
88    {
89      "name": "lines",
90      "source": "SELECT l.* FROM northstar_living.fct_order_lines l JOIN northstar_living.fct_orders o ON l.order_id=o.order_id JOIN northstar_living.dim_customer c ON o.customer_id=c.customer_id WHERE o.status='delivered' AND NOT o.is_test AND o.delivered_at>=DATE '2025-09-20' AND o.delivered_at<DATE '2026-09-20' AND NOT c.is_employee AND c.identity_status='resolved' AND l.delivered_qty>0",
91      "primary_key": [
92        "order_line_id"
93      ],
94      "fields": [
95        {
96          "name": "order_line_id",
97          "datatype": "Integer",
98          "expression": {
99            "dialects": [
100              {
101                "dialect": "ANSI_SQL",
102                "expression": "order_line_id"
103              }
104            ]
105          },
106          "dimension": {}
107        },
108        {
109          "name": "order_id",
110          "datatype": "Integer",
111          "expression": {
112            "dialects": [
113              {
114                "dialect": "ANSI_SQL",
115                "expression": "order_id"
116              }
117            ]
118          }
119        },
120        {
121          "name": "gross_sales_cents",
122          "datatype": "Integer",
123          "expression": {
124            "dialects": [
125              {
126                "dialect": "ANSI_SQL",
127                "expression": "gross_sales_cents"
128              }
129            ]
130          }
131        },
132        {
133          "name": "discount_cents",
134          "datatype": "Integer",
135          "expression": {
136            "dialects": [
137              {
138                "dialect": "ANSI_SQL",
139                "expression": "discount_cents"
140              }
141            ]
142          }
143        }
144      ]
145    }
146  ],
147  "relationships": [
148    {
149      "name": "lines_to_orders",
150      "from": "lines",
151      "to": "orders",
152      "from_columns": [
153        "order_id"
154      ],
155      "to_columns": [
156        "order_id"
157      ]
158    },
159    {
160      "name": "orders_to_customers",
161      "from": "orders",
162      "to": "customers",
163      "from_columns": [
164        "customer_id"
165      ],
166      "to_columns": [
167        "customer_id"
168      ]
169    }
170  ],
171  "metrics": [
172    {
173      "name": "booked_revenue",
174      "expression": {
175        "dialects": [
176          {
177            "dialect": "ANSI_SQL",
178            "expression": "SUM((lines.gross_sales_cents-lines.discount_cents)*0.01)"
179          }
180        ]
181      }
182    }
183  ],
184  "ai_context": {
185    "instructions": "Booked revenue uses gross sales less discounts, in currency units. Inspect source filters and do not treat booked revenue as customer margin.",
186    "synonyms": [
187      "booked sales",
188      "sales before refunds"
189    ]
190  }
191}