Part two of our Let’s Get Technical series
Written by Angela Harney, Senior Consultant and Snowflake Data Superhero

Introduction

Semantic Views in Snowflake are logical objects that allow for the centralization of metrics across queries so that business users can build and maintain data models in a consistent and easy manner. A data model provides information about tables and columns and relationships between data. For example, how entities in the data (dimensions) relate to column values (metrics), such as sales, to allow filters on time or types. This enables business users to assess the data with common language rather than knowing technical storage naming details.

Exposing the semantic view to Cortex Analyst improves accuracy by combining LLM reasoning with rule-based definitions to generate SQL against the physical tables directly. Comments can be added for each metric and dimension to help translate into common business language.

Excel spreadsheets users can connect directly to Snowflake and use simple syntax to query the dimensional data business users want to review, either loaded directly to a table in a worksheet or loaded into Power Query. Let’s take a look at how to load data into Power Query.

Note:  This article assumes ODBC for Snowflake is already installed.

Create Semantic View in Snowflake

We will create a semantic view in Snowflake, create a query on the view to check the results, and then query the data from Power Query in Excel.

First, create the following view in Snowflake using the snowflake_sample_data.tpch_sf1 database and schema. This semantic view exposes orders, order details, and customers with common metrics of average order value, product density, and customer count. The slicers provided are customer, year, and order date.

create or replace semantic view sales_order_averages
tables (
orders as snowflake_sample_data.tpch_sf1.orders
primary key (o_orderkey)
with synonyms (‘Sales Orders’),

customers as snowflake_sample_data.tpch_sf1.customer
primary key (c_custkey),

line_items as snowflake_sample_data.tpch_sf1.lineitem
primary key (l_orderkey, l_linenumber)
)
relationships (
orders_to_customers as orders
(o_custkey) references customers,

line_item_to_orders as line_items
(l_orderkey) references orders
)
— define facts
facts (
line_items.line_item_id as concat(l_orderkey, ‘-’, l_linenumber),
orders.count_line_items as count(line_items.line_item_id),
line_items.discounted_price as l_extendedprice * (1 — l_discount)
)
dimensions (
customers.customer_name as customers.c_name with synonyms = (‘Customer Name’),
orders.order_date as o_orderdate,
orders.order_year as year(o_orderdate)
)
metrics (
customers.customer_count as count(c_custkey),
orders.order_average_value as avg(orders.o_totalprice),
orders.average_line_items_per_order as avg(orders.count_line_items)
)

comment ‘Order averages by customer and year’
;

Query Semantic View in Snowflake

Next, query average items ordered per customer by year by executing this query in Snowflake.

select * from semantic_view (
sales_order_averages
dimensions orders.order_year, customers.customer_name
metrics average_line_items_per_order
);

Query Semantic View in Excel Power Query

Finally, load the data from the same query into Power Query in Excel by clicking Data, Get Data, From Other Sources, From ODBC, and selecting the version of Snowflake that you have installed. Under Advanced Options, paste the query above into the SQL Statement box. Provide your user name and password if prompted when you click OK.

Click Transform Data to bring your model into Power Query. Data will be loaded for you to apply advanced functionality such as creating parameters, splitting columns, removing rows or columns, and combining queries.

Monitor Semantic View in Snowflake

You can manage your semantic views in Snowflake by using “show semantic views” and “describe semantic view.”

Conclusion

Being able to create a view in Snowflake and then query it in Power Query in Excel minutes later is a powerful step towards bringing data to business users for effective and direct decision-making.

Unify is a Premier Tier Services Partner for Snowflake. We implement and use Snowflake’s workloads, from Data Warehousing to Data Engineering to Data Science to Data Sharing, to provide insights and business value. Unify’s deep experience with Snowflake across industries and use cases enables you to draw on leading practices for effective cloud initiatives that are executed efficiently. Learn more about our Snowflake solutions.

Missed Part one of our Let’s Get Technical Series? Read it here.