Semantic Model? Isn’t SQL enough?
What is the semantic model? Why should you use it in your product or solution when you already have SQL? Continue reading.
A picture is better than a thousand words. Here is a simple semantic model visualized in GoodData:
Technically speaking, the semantic model brings the following technical concepts to SQL model (and query language):
- Shared definition of relationships. The semantic model defines shared relationships between datasets. These relationships are implicitly used in queries. In short, your users won’t need to write joins and remember the names of the primary and foreign key columns. They also don’t need to remember in what dataset a column (attribute or fact) lives.
- Measures. Measures represent a number that your users work with (e.g., Revenue, Shipping Cost, Interest Rate, Conversion Rate, etc.). Your users want to group-by and filter the metrics by other columns (attributes). The semantic model generates the correct metric for each specific aggregation and filter combination. Your users need to define the combination of attributes, filters, and measures (a plain list of elements). The long (and boring) SQL queries are generated automatically for them.
Your users work with numbers, not queries.
SQL doesn’t have a good, reusable representation for an aggregated number.
For example, your user wants to aggregate REVENUE (ORDER_QUANTITY * PRODUCT_PRICE) by CUSTOMER_CITY, and PRODUCT_CATEGORY and compute the share of a certain product category revenue on the total revenue in a particular city. Here are the data in GoodData’s Analytical Designer.
With the semantic model above, the user drags and drops the measures (REVENUE, REVENUE BY CITY, and REVENUE PRODUCT_CATEGORY % IN CITY) and attributes (CUSTOMER_CITY and PRODUCT_CATEGORY) to the canvas. The semantic model already contains the definitions of the measures.
In SQL, your user must write the query below with joins and window functions, etc. He or she needs to know the names of the tables, primary and foreign key column names, etc.
SQL queries aren’t reusable; you’ll need many
Let's continue with our example. Your user wants a slightly different report — number of orders and revenue by customer’s state and order status.
With the semantic model, your users only need the measures that have been already defined in the example above. They reuse the REVENUE metric with a different combination of attributes (CUSTOMER_STATE and ORDER_STATUS)
There is no reuse in the SQL query. Your users must write a new query from scratch.
This applies to any other report. With the semantic model, your users don’t need to create anything new. They reuse the handful of existing measures. In SQL, they need to create a new query per each new report.
Semantic model is easy to use for common users.
Looking at the examples above, it is clear that the SQL queries are far more complex than the semantic model measures. Your users must know many SQL concepts like aggregation, joins, window functions, etc. They must also understand the structure of the underlying database.
This isn’t the case for the semantic model. Semantic model’s query generator and shared relationships shield your users from these complexities. The reusability allows you to create the more complex measures upfront for your users who reuse them in their (potentially simpler) expressions.
Semantic model provides resiliency.
The semantic model makes your user’s queries and measures very resilient to the underlying physical data model (SQL tables) changes. The measure definitions don’t contain the joins, so they don’t need to be changed when the physical columns move between tables or when the primary or foreign keys change. The measures, reports, and results aren’t affected when you replace a 1:N relationship with M:N (bridge tables), etc.
Try it yourself, it’s free
Do you want to play with a semantic model implementation? Sign up for the GoodData FREE, and go through the short getting started tutorial. You can also join the community if you need support or want to discuss your specific needs.
