Relational and Dimensional Knowledge Fashions


An information mannequin is an summary mannequin that helps to prepare information components and standardize how they’re associated. It reveals relationships between completely different real-world objects. It additionally refers to an output of information modeling: a course of of making visible diagrams utilizing completely different parts to symbolize the info.

To evaluate the fundamentals of information fashions in addition to study in regards to the technique of constructing information fashions and the way GoodData helps this course of, learn our article “What Is a Knowledge Mannequin?”

On this article, we are going to give attention to examples of information fashions, paying particular consideration to in the present day’s most used varieties — relational and dimensional information fashions — to be able to spotlight their use instances and advantages.

What Is a Relational Knowledge Mannequin?

A relational information mannequin is an strategy to creating relational databases to be able to handle information logically by its construction and language consistency. On this mannequin, information is represented within the type of two-dimensional tables. Every desk represents a relation of information values based mostly on real-world objects, consisting of columns and rows often called attributes and tuples.

A table containing basic information such as name and date of birth.
A desk represents a relation of information values based mostly on real-world objects.

Relational information fashions prioritize the upkeep of information integrity. This apply ensures information safety and consistency that are crucial facets of information mannequin design, its implementation, and its future utilization for storing, processing, and retrieving information.

Methods to Construct a Relational Knowledge Mannequin

Whereas constructing a relational information mannequin, you may outline all kinds of relationships between relations representing real-world objects, similar to one-to-one, one-to-many, and many-to-many. Many-to-many relationships require decomposition, which refers to a technique of dividing a relationship into two or extra sub-relations. This course of creates an extra desk with two one-to-many sub-relationships linked to the primary tables. The connections between tables in relational databases are made by relational references utilizing main and international keys.

There are three kinds of keys in a relational information mannequin:

  • Major: A main key identifies a selected row in a database desk.
  • International: A international key refers back to the main key of one other desk.
  • Candidate: A candidate key may be chosen and used as the first key.
Examples of keys in a relational data model
Examples of keys

Picture credit score: Guru99

One other important step of constructing relational information fashions is normalization. Normalization is a technique of analyzing relation schemas based mostly on useful dependencies and relational references to be able to lower redundancy and keep away from anomalies. There are a number of regular varieties (NF) however the first three are the most typical:

  • 1NF (atomicity): Relation is in 1NF if the area of every attribute accommodates atomic values. For instance, we may point out clients’ addresses. Every deal with consists of the road title and quantity, metropolis, and postal code. To fulfill 1NF, it is necessary to maintain them as separate attributes. The next instance has two attributes: Full Title and Tackle. To fulfill 1NF on this instance, we should break up the attribute Full Title into First Title and Final Title, and Tackle into Avenue and Metropolis.
A table with two columns is expanded into 4 columns.
Splitting attributes
  • 2NF: Relation is in 2NF whether it is in 1NF and every non-key attribute should rely on all the main or candidate key based mostly on duplicity elimination within the present relation. For instance, there’s a relation associated to college students and it not solely shops details about every pupil, but in addition accommodates details about college (e.g., college title, deal with, or contact info), which isn’t associated to college students. On this state of affairs, it’s obligatory to make clear which attributes relate to college students versus college, after which accordingly divide one desk into two separate tables.
A table with 5 columns becomes two tables, one with two columns, the other with three.
Dividing a desk into two separate tables.
  • 3NF: Relation is in 3NF whether it is in 2NF and doesn’t have a transitive dependency. That means, if attribute X will depend on attribute Y, and attribute Y will depend on attribute Z, then attribute X mustn’t rely on attribute Z. If this case exists, splitting the desk into at the very least two particular person tables could also be an excellent answer. For instance, we used the desk from the earlier instance earlier than it was break up into two separate tables. On this case, the relation between pupil and faculty continues to be saved.

What Is a Dimensional Knowledge Mannequin?

A dimensional information mannequin is a sort of database used for information warehousing and on-line analytical processing. This mannequin is part of the core architectural basis of creating extremely optimized and efficient information warehouses to be able to create helpful analytics. It supplies customers with denormalized buildings for accessing information from a knowledge warehouse.

How To Construct a Dimensional Knowledge Mannequin

A dimensional information mannequin consists of two kinds of tables: truth tables and dimensional tables. A truth desk shops numeric details about completely different enterprise measures. Dimensional tables, also referred to as dimensions, retailer attributes used to explain objects in a truth desk. A dimension is a set of reference details about a measurable occasion in information warehousing. Major and international keys join truth tables and dimensions as they do in relational information fashions.

You may construct your dimensional information mannequin based mostly on completely different schemas: star, snowflake, or galaxy. Within the heart of each star schema is a truth desk containing measures and international keys of linked dimensions.

Star schema example
Star schema instance

A snowflake schema extends a star schema and accommodates some further dimensions. Dimensional tables are standardized and normalized, leading to dimensions break up into additional tables that are reconnected in hierarchical order.

A galaxy schema is just like the above talked about schemas, but it surely has multiple truth desk. It normally accommodates at the very least two truth tables from two separated dimensional fashions which share the identical dimensional desk.

Galaxy schema example
Galaxy schema instance

To design dimensional information fashions, denormalization is one of the best strategy. Denormalization is a course of which is normally utilized on prime of a normalized database/information mannequin. It’s finished by including information duplicates or grouping information. Denormalization is critical to extend efficiency and help scalability attributable to the truth that this information mannequin offers with numerous learn operations/queries for analytics functions.

Relational Knowledge Fashions vs. Dimensional Knowledge Fashions

Relational information fashions differ from dimensional information fashions in some ways: the method of information modeling, use instances, advantages, and downsides.

Significance and Use Instances

Relational information fashions retailer current information. Their main objective is to mannequin relational databases, that are particularly helpful to establishing and managing an summary of present information. Relational information fashions can help operations for numerous industries. Banks can use them to retailer delicate information about clients’ accounts, simply as distributors can use them to retailer out there objects on their e-commerce retailer. Relational databases are used to learn and write information.

Dimensional information fashions are designed to retailer historic information for analytics functions and create information warehouses. You need to use them to retailer information (whatever the division or use case it is associated to) that was gained by monitoring completely different processes, similar to merchandise offered, numbers of tourists, and so forth. Knowledge warehouses created in dimensional information fashions are principally used to learn information.

Benefits and Disadvantages of a Relational Knowledge Mannequin


  • Knowledge is positioned in a single information retailer. It allows every division to tug information from the identical supply somewhat than having separate information sources.
  • By normalizing information, you may keep the integrity and accuracy of tables in your information/database mannequin. Accuracy eliminates the potential of information duplication by connecting relations with main and international keys. Integrity helps to make sure reliability between relations (to keep away from imperfect and remoted data) in addition to simplicity, stability, and precision of the info.
  • This mannequin is very safe. You may restrict customers’ entry by enabling them to work together with solely sure tables which can be related to their work.


  • Relational information fashions could start to appear complicated as the quantity of information saved in them will increase and its relationships turn into extra sophisticated. Moreover, longer response time whereas querying could happen because of the necessity to be a part of many tables and course of all the info.
  • When utilizing a stay system surroundings, working a brand new question — particularly one that features DELETE, ALTER TABLE, or INSERT — may be dangerous. Minor errors can have an effect on all the system, leading to misplaced time and decreased efficiency.

Benefits and Disadvantages of a Dimensional Knowledge Mannequin


  • Dimensional information fashions mean you can join information from completely different information sources.
  • With dimensional information fashions, efficiency is elevated and response time is decreased attributable to denormalization and fewer joins between relations compared to relational information fashions. Related information is grouped in a single dimension.
  • This kind of information mannequin may be simply arrange for real-time analytics functions.
  • The construction of dimensional information fashions lets you higher perceive your small business processes. Data is saved in dimension tables as attributes, and truth tables comprise measures.


  • Designing and managing dimensional information fashions could require extra skilled abilities and the flexibility to grasp and analyze a big capability of information.

Knowledge Fashions in GoodData

GoodData supplies customers with an analytical platform and allows them to attach information from a number of sources, create numerous metrics, and design dashboards to trace enterprise efficiency.

With GoodData, you may create dimensional information fashions that meet your wants and preferences. By creating dimensional information fashions, you may design a database to retailer numerous information in a centralized place, then design your information in a means that works finest for you. It permits and helps quicker information retrieval and helps create beneficial experiences to enhance and facilitate future enterprise decision-making.

Moreover, GoodData helps dimensional fashions based mostly on any sort of dimensional schema. You may select from a star, galaxy, or snowflake schema as we talked about above.

Screenshot of GoodData LDM modeler
One approach to create a dimensional information mannequin in GoodData is thru the LDM Modeler.

Prepared To Get Began?

Check out our GoodData.CN Group Version and create information fashions to trace your small business processes. Join sources, create metrics, and design dashboards in response to your necessities. Moreover, do not forget to finish this GoodData College Course to study extra about GoodData’s answer and skim our documentation.

Supply hyperlink


Please enter your comment!
Please enter your name here