In the last post we looked at how the Semantic Views make working with Aggregate-sensitive metrics easier.
In this post we will look into how the Semantic View help in ensuring the Metrics calculations are applied at the correct grain.
We will use a simple example of Orders that have Order Lines and each Order can be paid using 1 or more Credit Cards.
Let’s start by creating some sample data:
;;
create or replace table orders (order_number int);
create or replace table order_lines (order_number int, item_id int, quantity int);
create or replace table order_tenders (order_number int, tokenized_card_number string);
create or replace table items(item_id int, item_description string, unit_price int);
create or replace table tenders(tokenized_card_number string, cardholder_name string);
insert into orders values (1), (2), (3);
insert into order_lines values (1, 9999999, 3), (1, 8888888, 2);
insert into items values (9999999, 'Iphone Air', 1700), (8888888, 'Nintendo Switch 2', 499);
insert into tenders values ('9999-9999-9999-9999', 'Saqib Ali'), ('9999-9999-9999-8888', ' Zamir Ali');
insert into order_tenders values (1, '9999-9999-9999-9999');
insert into order_tenders values (1, '9999-9999-9999-8888');
Above we have an orders with 2 Order Lines with Quantity of 3 (iPhone Air) and 2 (Nintendo Switch 2). And this Order was paid using two different Credit Cards.
Now let’s a Semantic View that represents the above relationship and also the metrics required:
CREATE OR REPLACE SEMANTIC VIEW order_details
TABLES (
items AS items PRIMARY KEY (item_id)
, orders AS orders PRIMARY KEY (order_number)
, order_lines AS order_lines PRIMARY KEY (order_number, item_id)
, order_tenders AS order_tenders PRIMARY KEY (order_number, tokenized_card_number)
, tenders as tenders PRIMARY KEY (tokenized_card_number)
)
RELATIONSHIPS (
order_lines (item_id) REFERENCES items
, order_lines (order_number) REFERENCES orders
, order_tenders (tokenized_card_number) REFERENCES tenders
, order_tenders (order_number) REFERENCES orders
)
FACTS (
order_lines.quantity as quantity
)
DIMENSIONS (
items.item_description as item_description
, orders.order_number as order_number
, order_lines.item_id as item_id
, items.unit_price as unit_price
, tenders.cardholder_name as cardholder_name
)
METRICS (
order_tenders.number_of_tenders AS count(tokenized_card_number)
, order_lines.total_quantity AS sum(order_lines.quantity)
, order_lines.total_price AS sum(order_lines.quantity * items.unit_price)
, order_tenders.all_tenders AS array_agg(tokenized_card_number)
, order_tenders.all_card_holders AS array_agg(tenders.cardholder_name)
)
;
Let’s say we are just looking for aggregates at the Order Grain, we can use this query:
SELECT * FROM SEMANTIC_VIEW(
order_details
DIMENSIONS
orders.order_number
METRICS
order_lines.total_quantity
, order_lines.total_price
);
this will output:
Now let’s say we are interested at the Line (Item) level, we can simply add the item dimension to the above query:
SELECT * FROM SEMANTIC_VIEW(
order_details
DIMENSIONS
orders.order_number
, order_lines.item_id -- Adding Line Grain
, items.item_description -- Adding Line Grain
METRICS
order_lines.total_quantity
, order_lines.total_price
);
Notice that the output is at the Item grain by simply adding the Dimension to the Semantic View query.
Now let’s say we want to get all the Credit Card information for each Order. We can use the following Semantic View query:
SELECT * FROM SEMANTIC_VIEW(
order_details
DIMENSIONS
orders.order_number
METRICS
order_lines.total_quantity
, order_lines.total_price
, order_tenders.number_of_tenders
, order_tenders.all_tenders
, order_tenders.all_card_holders
);
This will output:
However now let’s update the above query to add the Line Level grain:
SELECT * FROM SEMANTIC_VIEW(
order_details
DIMENSIONS
orders.order_number
, order_lines.item_id
, items.item_description
METRICS
order_lines.total_quantity
, order_lines.total_price
, order_tenders.number_of_tenders
, order_tenders.all_tenders
, order_tenders.all_card_holders
);
This query will error out since the Credit Cards are at the Order grain, and not at the Order Line Level. We will get the following error message:
Invalid dimension specified: The dimension entity 'ORDER_LINES' must be related to and have an equal or lower level of granularity compared to the base metric or dimension entity 'ORDER_TENDERS'.
This is a powerful feature of Semantic Views. It enforces the grain of the data when querying it. This prevent errorneous results.