Wednesday, November 23, 2022
HomeBusiness IntelligenceRelational and Dimensional Information Fashions

Relational and Dimensional Information Fashions

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

To overview the fundamentals of knowledge fashions in addition to be taught concerning the means of constructing knowledge fashions and the way GoodData helps this course of, learn our article “What Is a Information Mannequin?”

On this article, we are going to give attention to examples of knowledge fashions, paying particular consideration to right this moment’s most used sorts — relational and dimensional knowledge fashions — with a view to spotlight their use instances and advantages.

What Is a Relational Information Mannequin?

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

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

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

Construct a Relational Information Mannequin

Whereas constructing a relational knowledge mannequin, you possibly can outline all kinds of relationships between relations representing real-world objects, equivalent to one-to-one, one-to-many, and many-to-many. Many-to-many relationships require decomposition, which refers to a means 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 principle tables. The connections between tables in relational databases are made by relational references utilizing major and overseas keys.

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

  • Major: A major key identifies a selected row in a database desk.
  • International: A overseas key refers back to the major 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 knowledge fashions is normalization. Normalization is a means of analyzing relation schemas based mostly on useful dependencies and relational references with a view to lower redundancy and keep away from anomalies. There are a number of regular types (NF) however the first three are the commonest:

  • 1NF (atomicity): Relation is in 1NF if the area of every attribute incorporates atomic values. For instance, we may point out prospects’ 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 Identify and Tackle. To fulfill 1NF on this instance, we should break up the attribute Full Identify into First Identify and Final Identify, and Tackle into Road 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 upon your complete major 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 incorporates details about faculty (e.g., college title, deal with, or contact info), which isn’t associated to college students. On this state of affairs, it’s necessary to make clear which attributes relate to college students versus faculty, 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 is dependent upon attribute Y, and attribute Y is dependent upon attribute Z, then attribute X shouldn’t rely upon attribute Z. If this case exists, splitting the desk into at the least two particular person tables could also be a superb answer. For example, 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 stored.

What Is a Dimensional Information Mannequin?

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

How To Construct a Dimensional Information Mannequin

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

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

Star schema example
Star schema instance

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

A galaxy schema is much like the above talked about schemas, however it has multiple truth desk. It often incorporates at the least two truth tables from two separated dimensional fashions which share the identical dimensional desk.

Galaxy schema example
Galaxy schema instance

To design dimensional knowledge fashions, denormalization is the perfect strategy. Denormalization is a course of which is often utilized on prime of a normalized database/knowledge mannequin. It’s completed by including knowledge duplicates or grouping knowledge. Denormalization is important to extend efficiency and help scalability as a result of the truth that this knowledge mannequin offers with numerous learn operations/queries for analytics functions.

Relational Information Fashions vs. Dimensional Information Fashions

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

Significance and Use Instances

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

Dimensional knowledge fashions are designed to retailer historic knowledge for analytics functions and create knowledge warehouses. You should use them to retailer knowledge (whatever the division or use case it is associated to) that was gained by monitoring totally different processes, equivalent to merchandise bought, numbers of tourists, and so on. Information warehouses created in dimensional knowledge fashions are principally used to learn knowledge.

Benefits and Disadvantages of a Relational Information Mannequin


  • Information is positioned in a single knowledge retailer. It allows every division to tug knowledge from the identical supply relatively than having separate knowledge sources.
  • By normalizing knowledge, you possibly can keep the integrity and accuracy of tables in your knowledge/database mannequin. Accuracy eliminates the potential of knowledge duplication by connecting relations with major and overseas 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 information.
  • This mannequin is very safe. You may restrict customers’ entry by enabling them to work together with solely sure tables which are related to their work.


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

Benefits and Disadvantages of a Dimensional Information Mannequin


  • Dimensional knowledge fashions assist you to join knowledge from totally different knowledge sources.
  • With dimensional knowledge fashions, efficiency is elevated and response time is decreased as a result of denormalization and fewer joins between relations compared to relational knowledge fashions. Comparable knowledge is grouped in a single dimension.
  • Any such knowledge mannequin may be simply arrange for real-time analytics functions.
  • The construction of dimensional knowledge fashions lets you higher perceive your corporation processes. Info is saved in dimension tables as attributes, and truth tables include measures.


  • Designing and managing dimensional knowledge fashions could require extra skilled abilities and the power to know and analyze a big capability of knowledge.

Information Fashions in GoodData

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

With GoodData, you possibly can create dimensional knowledge fashions that meet your wants and preferences. By creating dimensional knowledge fashions, you possibly can design a database to retailer varied knowledge in a centralized place, then design your knowledge in a manner that works finest for you. It permits and helps quicker knowledge retrieval and helps create worthwhile reviews 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 technique to create a dimensional knowledge mannequin in GoodData is thru the LDM Modeler.

Prepared To Get Began?

Check out our GoodData.CN Neighborhood Version and create knowledge fashions to trace your corporation processes. Join sources, create metrics, and design dashboards in accordance with your necessities. Moreover, do not forget to finish this GoodData College Course to be taught extra about GoodData’s answer and skim our documentation.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments