{"id":55,"date":"2022-02-10T14:22:27","date_gmt":"2022-02-10T14:22:27","guid":{"rendered":"https:\/\/openquery.com\/?p=55"},"modified":"2022-02-28T13:55:56","modified_gmt":"2022-02-28T13:55:56","slug":"what-is-a-dimension-sql","status":"publish","type":"post","link":"https:\/\/openquery.com\/what-is-a-dimension-sql\/","title":{"rendered":"What Is A Dimension SQL?"},"content":{"rendered":"\n

The world of SQL and databases can seem like a huge, untamable monster. With all the different clauses, phrases, and keywords, you\u2019d be forgiven for simply not knowing where to start.<\/p>\n\n\n\n

Whilst SQL can seem like an untamable monster, once you understand the basics, that huge monster starts to shrink.<\/p>\n\n\n\n

In this article, we\u2019ll be taking you through one of the most basic and intrinsic elements of a database: Dimensions.<\/p>\n\n\n\n

In SQL and in databases, dimensions is more of an umbrella term, as there are numerous names for dimensions within a database. These names usually refer to the type of data these dimensions hold or the behavior of the data within a dimension.<\/p>\n\n\n\n

If you\u2019re looking for a definition of a dimension and a brief overview of the types of dimensions, you\u2019re in the right place!<\/p>\n\n\n\n

\"What<\/figure><\/div>\n\n\n\n

Dimension SQL: A Definition<\/strong><\/h2>\n\n\n\n

In the context of SQL, a dimension is a digital structure that categorizes facts and measurements to enable users to answer business questions.<\/p>\n\n\n\n

There are three primary functions of dimensions: to provide grouping, labeling, and filtering of data.<\/p>\n\n\n\n

Within a data warehouse, a dimension gives us the ability to structure and label information that would otherwise be unordered numerical quantities. A dimension is a data set comprised of individual data elements.<\/p>\n\n\n\n

What might the real-world use of a dimension in a data warehouse be?<\/p>\n\n\n\n

Well, some of the most commonly used dimensions are quantities like customers, time, and products.<\/p>\n\n\n\n

Say you work for a clothing retailer and want to analyze the sale of a particular product across the chain of stores.<\/p>\n\n\n\n

Given that each sales channel gathers and stores data of sales of this product, and that this data is stored in the retailer\u2019s data warehouse, you might be asked to answer questions about the sales of the product across time.<\/p>\n\n\n\n

This would be a real-world use of a dimension in a data warehouse. <\/p>\n\n\n\n

As each of the numerical quantities like products, customers, and time labeled as a dimension can be interpreted to give accurate answers to sales questions.<\/p>\n\n\n\n

The Types Of Dimensions<\/strong><\/h2>\n\n\n\n
\"The<\/figure><\/div>\n\n\n\n

Now that we\u2019ve established what a dimension is, it\u2019s time to discuss the types of dimensions. As mentioned above, the types of dimensions relate to the data held within a dimension.<\/p>\n\n\n\n

Slowly Changing Dimensions<\/strong><\/h3>\n\n\n\n

As the name suggests, Slowly Changing Dimensions hold a set of data attributes that you would expect to change slowly over time (a name or address, for example).<\/p>\n\n\n\n

As these attributes change over a period of time, they are combined into a slowly changing dimension data set.<\/p>\n\n\n\n

Within a set of slowly changing dimensions, there are classifications of types as follows:<\/p>\n\n\n\n

Type 0<\/strong>: Attributes that never change (Retain Original)
Type 1<\/strong>: An old value is overwritten by a new value of attributes (Overwrite)
Type 2<\/strong>: A new row is created for a new value with a start and end date\/version (Add New Row)
Type 3<\/strong>: In a new value, a new column is created (Add New Attribute)
Type 4<\/strong>: A table keeps its current value, but the history is of the value is saved in a second table (Add History Table)
Type 5<\/strong>: Is a combination of Type 1 and Type 4 (Combined Approach = 1 + 4)
Type 6<\/strong>: Is a combination of Type 1, Type 2, and Type 3 (Combined Approach = 1 + 2 + 3)
Type 7<\/strong>: Both a surrogate and a natural key are used (Hybrid Approach)<\/p>\n\n\n\n

Conformed Dimension<\/strong><\/h3>\n\n\n\n

A set of data attributes that are physically referenced in multiple database tables are grouped into a Conformed Dimension.<\/p>\n\n\n\n

These references can be the same attribute, structure, definition, or domain value (plus a few more). This means that a conformed dimension is able to cut across multiple facts.<\/p>\n\n\n\n

In the context of a dimension, a conformed dimension is either completely identical or strict mathematical subsets of a detailed, granular dimension.<\/p>\n\n\n\n

Junk Dimension<\/strong><\/h3>\n\n\n\n

A convenient grouping of low-cardinality flags or indicators is known as a junk dimension. <\/p>\n\n\n\n

When creating an abstract dimension, flags and indicators are removed from a fact table and placed within a useful dimensional framework.<\/p>\n\n\n\n

A junk dimension consists of attributes that don\u2019t belong in a fact table, with these attributes usually being text\/various flags like, for example, non-generic comments, or yes\/no indicators.<\/p>\n\n\n\n

Degenerate Dimension<\/strong><\/h3>\n\n\n\n

Degenerate dimensions hold information that is a key, like an invoice number, ticket number, or transaction number. These keys have no attributes and as such do not join with an actual dimension table.<\/p>\n\n\n\n

A degenerate dimension often plays an integral role in a fact table\u2019s primary key<\/a>.<\/p>\n\n\n\n

Role-playing Dimension<\/strong><\/h3>\n\n\n\n

Within the context of a database, it is typically the case that dimensions are recycled for use in multiple applications within the same database.<\/p>\n\n\n\n

As an example, a \u201cDate\u201d dimension could also be used for \u201cDate of Sale,\u201d \u201cDate of Hire,\u201d or \u201cDate of Delivery.\u201d These are referred to as role-playing dimensions.<\/p>\n\n\n\n

Outrigger Dimension<\/strong><\/h3>\n\n\n\n

An Outrigger dimension is usually referred to as a data warehouse \u201cantipattern.\u201d <\/p>\n\n\n\n

This is because a dimension table doesn\u2019t reference another via a foreign key<\/a>. It\u2019s best practice to fact tables that relate to two dimensions.<\/p>\n\n\n\n

Shrunken Dimension<\/strong><\/h3>\n\n\n\n

When a conformed dimension contains a subset of the rows\/columns of the original dimension, this is known as a shrunken dimension.<\/p>\n\n\n\n

Calendar Data Dimension<\/strong><\/h3>\n\n\n\n

A Calendar Data Dimension is a very special kind of dimension that is used to show dates with a granularity of a day.<\/p>\n\n\n\n

Conclusion<\/strong><\/h2>\n\n\n\n

As we\u2019ve mentioned at the beginning of this article, SQL and dimensions can take some time and practice to learn and perfect.<\/p>\n\n\n\n

As the use of databases and the data within them is growing year on year, it stands you in good stead to learn the fundamentals of SQL<\/a> and databases as the application of data is permeating an increasing number of job roles.<\/p>\n\n\n\n

\n
https:\/\/www.youtube.com\/watch?v=tgzQ3IpRZEA<\/a>