Monday, June 13, 2022
HomeBusiness IntelligenceRelational and Dimensional Information Fashions

Relational and Dimensional Information Fashions

A knowledge 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 information modeling: a course of of making visible diagrams utilizing totally different elements to symbolize the info.

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

On this article, we’ll give attention to examples of information fashions, paying particular consideration to right this moment’s most used varieties — relational and dimensional knowledge fashions — as a way to spotlight their use circumstances and advantages.

What Is a Relational Information Mannequin?

A relational knowledge mannequin is an strategy to creating relational databases as a way 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 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 knowledge fashions prioritize the upkeep of information integrity. This follow ensures knowledge safety and consistency that are vital features of information mannequin design, its implementation, and its future utilization for storing, processing, and retrieving knowledge.

Find out how to Construct a Relational Information Mannequin

Whereas constructing a relational knowledge mannequin, you may outline all varieties 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 strategy 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 varieties of keys in a relational knowledge mannequin:

  • Main: 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 will 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 strategy of analyzing relation schemas based mostly on practical dependencies and relational references as a way to lower redundancy and keep away from anomalies. There are a number of regular varieties (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 might point out clients’ addresses. Every tackle 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 Deal with. To fulfill 1NF on this instance, we should cut up the attribute Full Title into First Title and Final Title, and Deal with 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 the 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., school title, tackle, or contact data), which isn’t associated to college students. On this scenario, it’s obligatory 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 depends upon attribute Y, and attribute Y depends upon attribute Z, then attribute X shouldn’t rely upon attribute Z. If this example exists, splitting the desk into no less than two particular person tables could also be a superb answer. For example, we used the desk from the earlier instance earlier than it was cut up into two separate tables. On this case, the relation between pupil and faculty continues to be saved.

What Is a Dimensional Information Mannequin?

A dimensional knowledge mannequin is a kind of database used for knowledge warehousing and on-line analytical processing. This mannequin is part of the core architectural basis of growing extremely optimized and efficient knowledge warehouses as a way 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 varieties of tables: reality tables and dimensional tables. A reality desk shops numeric details about totally different enterprise measures. Dimensional tables, also called dimensions, retailer attributes used to explain objects in a reality desk. A dimension is a set of reference details about a measurable occasion in knowledge warehousing. Main and overseas keys join reality tables and dimensions as they do in relational knowledge fashions.

You’ll be able to construct your dimensional knowledge mannequin based mostly on totally different schemas: star, snowflake, or galaxy. Within the heart of each star schema is a reality 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 cut up into additional tables that are reconnected in hierarchical order.

A galaxy schema is much like the above talked about schemas, nevertheless it has multiple reality desk. It normally incorporates no less than two reality 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 most effective strategy. Denormalization is a course of which is normally utilized on high of a normalized database/knowledge mannequin. It’s performed by including knowledge duplicates or grouping knowledge. Denormalization is critical to extend efficiency and help scalability resulting from 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 information modeling, use circumstances, advantages, and disadvantages.

Significance and Use Circumstances

Relational knowledge fashions retailer current knowledge. Their major function 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 numerous industries. Banks can use them to retailer delicate knowledge 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 knowledge.

Dimensional knowledge fashions are designed to retailer historic knowledge for analytics functions and create knowledge warehouses. You need to 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 offered, numbers of holiday makers, 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 situated in a single knowledge retailer. It allows every division to tug knowledge from the identical supply quite than having separate knowledge sources.
  • By normalizing knowledge, you may keep the integrity and accuracy of tables in your knowledge/database mannequin. Accuracy eliminates the opportunity 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 information) in addition to simplicity, stability, and precision of the info.
  • This mannequin is extremely safe. You’ll be able to restrict customers’ entry by enabling them to work together with solely sure tables which can be related to their work.


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

Benefits and Disadvantages of a Dimensional Information Mannequin


  • Dimensional knowledge fashions will let you join knowledge from totally different knowledge sources.
  • With dimensional knowledge fashions, efficiency is elevated and response time is decreased resulting from denormalization and fewer joins between relations compared to relational knowledge fashions. Related knowledge is grouped in a single dimension.
  • Such a knowledge mannequin will be simply arrange for real-time analytics functions.
  • The construction of dimensional knowledge fashions lets you higher perceive your corporation processes. Data is saved in dimension tables as attributes, and reality tables include measures.


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

Information Fashions in GoodData

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

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

Moreover, GoodData helps dimensional fashions based mostly on any type of dimensional schema. You’ll be able to select from a star, galaxy, or snowflake schema as we talked about above.

Screenshot of GoodData LDM modeler
One approach 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 line with your necessities. Moreover, do not forget to finish this GoodData College Course to study extra about GoodData’s answer and skim our documentation.



Please enter your comment!
Please enter your name here

Most Popular

Recent Comments