All organisations need to report on their data in one form or another and reasons for reporting could be regulatory, accounting, competitive advantage or everyday business functions. This ultimately leads to the requirement of storing the data in a repository for future referencing.

However, few organizations report efficiently and correctly from their data records leading to heavy fines or restrictions on business ability to provide its services. Some are more publicised than others.

Once a compatible data repository has been selected and implemented by the organisation to store it’s data, the next task is accessing the data from the chosen storage platform. A structured or semi structured data storage platform such as a data warehouse is often used and SQL is the language which is used to interrogate such a platform.

Data modeling refers to both the Enterprise Data warehouse design and the semantic data modeling for business reporting use. Data warehouse design involves taking data from the various sources and organizing them into dimensions and facts. This organization allows the star schema to be implemented which enables efficient analytical reporting.

A semantic data model is usually an additional reporting or analytical layer built over a data storage platform. An example could be; a large volume of data from various sources is collected into a data lake. This data is available to use but would be very problematic for a business user to understand and analyse correctly. The data goes through transformation processing on the Transformation Data Layer and is then moved to a final layer called the Semantic data layer where the data is organised according to business reporting requirements. A semantic data model can also be built on other data storage repositories.

SQL queries are declarative statements which allow users to manage and interrogate structured data in a data storage application.

A frequent client requirement is the ability to self serve their business intelligence reports. This means a business user has the ability to view or build reports without technical knowledge and only the confidence that the data being shown is the correct data and an accurate representation of the business information. This can be achieved through a data model. Understanding business questions that need to be answered is a key requirement covered during a data modeling exercise and the data model is expected to cover likely business scenarios. This data model then serves as a foundation to delivering this business “self-serve” requirement.

In this post, I am focusing on the data/semantic data used for Business Intelligence reports.

When a data warehouse is designed, it should ideally offer a plug and play type of reporting service where a user is able to connect a reporting application to the database tables and generate the reports required without additional builds or manipulation. The user can see the fields appropriately named, drags the fields onto their reporting canvas and display the information required. This type of capability is usually driven by a dimensional model – Star Schema or Snowflake Schema introduced by Ralph Kimball- See The Datawarehouse Toolkit.

To achieve this client Utopia where the user is able to self serve, it is fundamental that the business complex data structure is understood and modeled appropriately. However, due to organization sizes , proliferation of data and other business strategies, data is not always stored in a Datawarehouse incorporating the dimensional model and data is made available on different platforms such as a data lake, graph, NoSQL, data mesh to meet business need.

This ability to provide data on different platforms, some of which require different querying languages, means it has become important to create semantic data models which can achieve this self serve objective.

Furthermore, self serve reporting is impossible to achieve when relying on SQL queries for BI reporting. SQL requires the user to have technical knowledge of the data structure and relationships as well as the required keys.

While a SQL query can be used to retrieve data from multiple tables, only a subset of the fields in the various tables can be retrieved into the View, Stored Procedure or Function which is used for a business intelligence report. Conversely, a semantic data model is able to provide all columns in each table for the user to make a selection at the time of use. This is a very important benefit which allows the data model to be reused with no rework or rebuilding of queries.

A data model is built to ensure it can answer the business questions and can be reused, which helps reduce cost and time to deliver reports.

When a data model is incorporated into the data solution of a business, it brings with it a range of benefits.

Some Benefits of having a data model include;

• Self service
• Single source of truth
• All measures in one place
• Unified reporting
• Conformed fields – unified meaning of business terms

Reporting which is reliant on SQL queries is often an indication of data which has not been modeled to cater to the business area requirements. This leads to siloed reporting, field definitions which are not uniformly agreed across the enterprise and reduced governance of data assets.

It is therefore important to have the business data managed and modeled appropriately for business use.

Do you have a data model or are you still relying on SQL queries for your data reporting needs?