Dashboards : One Chart & One SQL Query Only ?
The first idea when building reporting and dashboards is to directly connect a charting library with a set of SQL queries to the database but is that enough?
There are wonderful libraries available for all kinds of cool data visualizations, such as HighCharts, which can display data both scientifically and beautifully; amCharts, known for their extensive capabilities to represent data as hard facts or compelling stories; and others like Google Charts, D3.js, and Charts.js.
Once we choose our library, we “just” need to access the database, pull out the data, perform calculations and aggregations, implement security and privacy constraints, and introduce a user-friendly, drag-and-drop editor for creating new reports and insights as needed. Additionally, we must integrate these into the existing software, stay updated with browser upgrades and customer requests, enhance performance, and voilà!
That “just” word there all of a sudden seems to be a huge endeavor and thinking about the end to end software and business logic life-cycle from requirements to final integration testing doesn’t make it any simpler.
This article will dive in to:
- What is a Semantic Layer ?
- Leveraging the Benefits of Semantic Layers in Data Visualization
- Counter Argument
- Use Case of Semantic Layer - PropTech Industry
- Wrapping Up
What is a Semantic Layer ?
A semantic layer is a data model made for analytics that sits between your underlying SQL data and your analytics end-users leading to a consistent and trusted access to your data.
In 2018 already, Gartner pointed out the importance of a semantic layer in their report “How to Use Semantics to Drive the Business Value of Your Data”:
Unprecedented levels of data scale and distribution are making it almost impossible for organizations to effectively exploit their data assets. Data and analytics leaders must adopt a semantic approach to their enterprise data assets or face losing the battle for competitive advantage.
At icCube, we believe semantic layers are the missing link between business users and the value locked away in the underlying databases and that using semantic layers offers several advantages over connecting directly to production databases and will help companies to address today’s challenges.
Leveraging the Benefits of Semantic Layers in Data Visualization
Business-Friendly
A semantic layer offers streamlined access to data by providing an abstract, “digestible”, normalized representation of data familiar to end-users. Unlike direct queries to databases, which require developers to navigate the complexities of underlying data structures, semantic layers present data in a structured format using familiar business concepts, simplifying data retrieval and manipulation.
This data model structure is particularly useful when, for instance, the need for hierarchical structures used for various drilldowns across dashboards is often needed.
Consistency
Maintaining consistency in data interpretation across various visualization tools and dashboards is essential for effective decision-making. Semantic layers enforce standardized data definitions and business terms, ensuring consistency in data interpretation and analysis. In contrast, direct queries to databases may result in discrepancies due to variations in query implementations. For example, if data from different databases shows regions but the names of the columns are different, i.e. "Continent" in one and "Region" in another, it can confuse users making it harder for them to understand and trust the data and dashboard. Another example would involve calculations or measures that might not be immediately clear from the database column name alone, such as “amount”; you can therefore provide explicit labels for your data that speak to the end user.
Semantic layers therefore serve to reduce errors and discrepancies across data visualizations and dashboards, promoting trusted decision making processes.
Advanced Analytics
An analytical data model is better suited for advanced analytics as it is specifically designed to handle complex computations efficiently and accurately. Whether you need to perform market share evaluations, multidimensional regressions, calculation on data with many-to-many relations, vector/matrix operations, or other types of intricate calculations, an analytical server offers reliable execution that surpasses complex analytical data processing direct queries to databases can usually efficiently manage.
Enhanced Performance
Production databases are designed and optimized for executing fast transactions. Therefore, direct analytical queries to production databases often lead to performance bottlenecks, particularly when handling large datasets or complex queries.
Semantic layers, on the other hand, are designed and optimized for fast analytical queries. They are typically hosted in a separated environment providing query performance improvement and overall system responsiveness.
Enhanced Security
Semantic layers form the basis of your security setup, enabling precise control over access. For instance, in a geographical hierarchy like Continents, Countries, and Cities, users might access country-level data but not city-specific information. When combined with other dimensions using a cross-product approach, this creates a detailed security structure that can go up to the cell-level detail. By centralizing data access, organizations can enforce strict policies effectively, empowering developers to implement a strong security definition, mitigating the risk of unauthorized data access or breaches.
Maintenance
Having ad-hoc SQL queries spread across 10s and 100s charts and possibly much more is the source of a maintenance nightmare and is quite similar to the Excel spreadsheets issue. Any change to the underlying production data is possibly breaking one or more of these queries. Unfortunately, there is no real possibility to know which query (deployed who knows where) is using what data.
A semantic layer is minimizing this risk. The implementation of the layer can be adapted (when required) when changing the underlying data without changing its exposed interface meaning no dashboard will ever be broken.
Cross-Data Source Integration & Querying
Data often lies in diverse sources. An analytical data model serves as an intermediary between disparate data sources, enabling data harmonization in a single, central place. This gathering capability simplifies the process of combining data from multiple sources for comprehensive analysis and visualization.
Cost Efficiency
Direct queries to databases may incur additional costs, as some databases are subject to fees per query volume or result. Analytical servers offer a strategic and cost-effective solution as it loads data into an intermediary storage removing the need for repetitive queries to databases, thereby reducing overall operational expenses.
Reduced Dependency on IT
Empowering customer-facing teams to independently create dashboards without requiring technical or database expertise fosters greater autonomy and agility.
AnalyticsOps / Data Governance
In 2021, David Menninger from Ventana Research, in his paper “Analytics Ops : The Last Mile of Data Ops” predicted that :
By 2024, one-third of organizations will adopt an analytic operations approach similar to, and integrated with, their data operations processes to enhance responsiveness and agility.
Certainly, semantic layers are a central part of AnalyticsOps and data governance. For example, this makes it easier to adjust underlying data structures without breaking any existing dashboards meaning less time and effort required to maintain and manage those dashboards. AnalyticsOps is beyond the scope of this post but interested readers may refer to this post for more details.
Testing
Testing is crucial for the success of any software development project, including analytics and dashboard projects. Having a dedicated system/environment for the analytics solution based on a universal semantic layer allows for dedicated tests which are pivotal for achieving agility. Once implemented, it enables the automated validation of dashboards, reports, and other Business Intelligence (BI) content for accuracy, user experience, security/authorization, performance, and regressions. This results in fewer errors and fosters greater trust in the analytics infrastructure that is essential for decision makers who rely on it for their daily operations.
Counter Argument
In scenarios where displaying real-time, flat, raw, transactional data is needed, such as monitoring operational activities or tracking live transactions, direct queries to the database can be favored, as a powerful analytical engine is not strictly necessary.
In icCube, you can do both, populate a chart with data coming from a semantic layer or a direct SQL database query :)
Use Case of Semantic Layer - PropTech Industry
STREETS a leading Swiss fully-SaaS Real Estate portfolio management platform has leveraged a semantic layer bringing modern, unprecedented, and therefore competitive data-driven insights into their PropTech solution.
More specifically, STREETS has developed a semantic layer to provide advanced analytics and modeling capabilities matching their sophisticated underlying data model requirements and complex user-defined data security.
Wrapping Up
The adoption of semantic layers in data visualization workflows offers numerous benefits when it comes to performing advanced analytics over direct queries to databases. From a business-friendly data model and enhanced performance to improved data consistency and security, semantic layers provide a robust foundation for building insightful and efficient visualizations.
By leveraging analytical servers, organizations can unlock the full potential of their data assets while reducing data complexity and operational costs.
This ultimately leads to more informed decision-making and deeper data insights across digital dashboards powered by a trusted analytics platform.
Curious about how a semantic layer can boost your analytics and dashboards? Reach out to us — we'd love to talk!
_