By Richard Sharkey – Principal BI Engineer
Understanding and capturing user requirements in any technical domain is tricky and in the world of business intelligence and analytics; this is no exception. As a data consultancy, it is critical for us to truly understand user requirements. It is also equally important that the users and stakeholders understand what they are going to get as an output. Carry on reading to find out how we use sun models to capture client requirements.
What is an “analytical requirement”?
It’s a broad term, but these are common sentiments we see when we ask users “what do you need to understand from your data that will drive value for your business?”:
For synvert TCM (formerly Crimson Macaw), our goal is to create solutions that can allow us to answer these questions easily, consistently, timely and with a strongly understood definition that gives users the confidence to make bold data-driven decisions.
Building Analytical Models
Ultimately, capturing user requirements informs data model design i.e. how to structure the data and define the data processing rules to make these requirements a reality. There are various schools of thought, but at synvert TCM we really value the Dimensional Modelling approach. Ralph Kimball and Margy Ross made this a popular approach.
If you have ever heard the words “facts”, “dims”, “dimensions” or “measures” when hearing discussion about data models; they were probably talking about Dimension Modelling.
In simple terms, Dimensional Modelling is the approach of storing measurable information pertaining to specific business process events into “fact” tables and storing reference information describing those events into “dimension” tables. Combined we end up with a concept of a “star schema” where multiple dimension tables can join to a fact table to describe the events.
For synvert TCM using Dimension Models for servicing analytical requirements provide the following benefits:
Before Sun Models
So, before we dive into what Sun Models are, let’s think about some other mechanisms that we can use to capture analytical requirements:
- Enterprise Bus Matrix
- Report Wireframes
Enterprise Bus Matrix
The Enterprise Bus Matrix is a tabular capture format that shows the required intersections between “measures” and “dimensions”. It can be quite informative and shows where conformed dimensions will emerge across different business processes.
The limitation of the Enterprise Bus Matrix is that it isn’t very user-friendly, and it doesn’t really capture useful information such as “what attributes related to the product will I see?” or “can I drill up or down the customer group hierarchy?” since the information isn’t typically captured. Trying to capture this information in one place in a tabular format becomes very unwieldy and can disengage the user since it becomes cluttered and hard to read.
Report Wireframes are super-valuable for understanding how a user will use the analytical requirements to build reports and dashboards. We can understand what modelling considerations we need to make based on how they wish the visualisations to interact and navigate.
However, a report-centric approach to assessing requirements can produce narrow thinking. Lots of very useful information may never end up on a report or dashboard. Therefore, those requirements can be missed when only thinking about specific report-type outputs.
Reporting requirements is a valuable input into the requirements process. However, it should not be the only lens through which requirements reach rationale.
An Entity Relationship Diagram (ERD) [see the “star schema” image further up] is essentially a technical design artefact to inform a BI Engineer what columns they need to be populate to fulfil the requirements. ERDs are complicated and not easy to follow for a business user. Especially when the number of dimensions increases, and therefore can be disengaging as a mechanism to discuss and document requirements. There are also other pieces of useful information that are not captured such as:
- How to arrange hierarchies?
- Slowly Changing Dimension configuration
- What dynamic calculation fields we require in the BI tool? For Example, “Returns Rate %” is a calculation that has to happen at run time and cannot be stored in a table since it will return different results based on filters applied.
Enter, Sun Models
Sun Models are niche. They aren’t something many people have heard of before and googling often doesn’t produce many results. Sun Models originated from Professor Mark Whitehorn at Dundee University, but you will find similarities to Starnet or the modelling of Matteo Golferelli.
The idea behind Sun Models is to capture and visualise user requirements without having to discuss the intricacies of data modelling and other complex data terminology. Whereas some technical users may understand technical language, it is better to have a consistent format that is understood by technical and non-technical users alike.
Sun Models are very easy to understand for data consumers, analysts, senior management and C-Suite. It also gives them confidence about what they should expect to see as a deliverable.
In this example Sun Model, we can see requirements captured for tracking Sales events in a retail business. This is a common data model.
Similar to the earlier mechanisms discussed we are capturing measures and dimensions; however, we now have a much richer context to work with. We can see 8 measures that grouping/slicing by 4 core dimensions, each of which has multiple attributes. Let’s check some out.
In the below image for the Product dimension, we can see the following:
- 12 Product Attributes
- 2 alternate Hierarchies driven from Product Range
The user wants to be able to drill up and down different hierarchical definitions when looking at various measures. This now means that the BI Engineer creating and populating the data model knows what columns will go into the
dim_product table, but also how they should be arranged in the BI tool as well as creating any semantic logic required in the BI Tool to create the desired drill up and drill down capability.
In the below Customer dimension we can see the following:
- 11 customer attributes
- 1 hierarchy for customer location
- Definitions for slowly changing dimensions
You will notice numbers populated in the circles. This is an extension of the sun model format that synvert TCM has created. It defines requirements for Slowly Changing Dimensions.
The numbers translate to the Slowly changing behaviour “type” that the user wishes to see:
0 = Type 0 – Take the original value and ignore any changes
1 = Type 1 – Show the current value
2 = Type 2 – Show the value based on the time of the event
3 – Type 3 – 2 attributes for the original and current
6 = Type 6 – (1+2+3 combined) 2 attributes one for the current and one based on the time of the event
For information on Slowly Changing behaviour types check out this link.
The information captured for the date dimension also covers another extension to the format that synvert TCM created and that is to show which dimensions are “role-playing”.
In the example, you can see that there is a requirement to group dates based on the Order Date and Despatch date. The Sun Model format captures this requirement effortlessly.
In the example below we can see that there are 8 measures. 3 of the measures are dynamically calculated and would not be captured in the ERD. Therefore, the developer understands that there is a requirement to build those dynamic calculations in the BI tool in order to fulfil the user requirements.
One of the great things about Sun Models is that as well as giving the user an easy-to-understand representation of the requirements, it translates perfectly into a star schema design. We can then represent this as an ERD. This represents the target model that is populated by the data transformation work and is a great tool for the development team.
Inputs into Sun Models
synvert TCM use various information sources to populate Sun Models. Some include:
- Interviews and Workshops with users and stakeholders
- Existing reporting and dashboards
- Industry-standard metrics and dimensions for the given business process
- System Data Profiling
Hopefully, you are more informed about how Sun Models can help in the requirement gathering and design translation process. At synvert TCM we think Sun Models are fantastic! They are one of many tools we use in our requirement gathering and design iteration process. Tune in to future blogs where we will cover how Sun Models fit into the wider process we employ and drive our rapid prototype mechanism that helps further bring user requirements to life.
If you want to know more and don’t want to wait for the next blog then get in touch with us here!