We use these table types when we need to do analytical analysis of current data with some historical considerations. Hybrid SCDs provide more flexibility and better performance, but at the cost of simplicity. (Note: The numbering scheme of these SCDs starts with a 0 instead of a 1.) Of these, you’ll see types 0 through 4 the most types 5, 6, and 7 are hybrids of the first five. The SCD comes in many types eight of them are fairly common. This type of table is what Ralph Kimball calls a slowly changing dimension, or SCD. phone number, address, marital status, etc. Some attributes will likely change in the customer’s lifetime – e.g. In the following paragraphs, we’ll examine dimensions classified by how they store (or don’t store) historical data. We can expand the this type of table to hold industry-specific information (date of default, activity, etc.) Slowly Changing Dimensional TablesĪs time passes, dimensions can change their values. gender – The gender of the customer, M (male) or F (female).ĭimension table attributes depend on the business need.marital_status – Is the customer married? Defined as Y (yes) or N (no).date_of_birth – The customer’s date of birth.address_residence – The customer’s residential address.first_name – The customer’s first name.cust_natural_key – The natural key for the customer.id – The dimension table’s primary key.Let’s look at a typical dimensional table, DIM_CUSTOMER. These hold multiple attributes, which can be addressed in several source tables but which refer to the same domain (customer, contract, deal, etc.) Conformed dimensions are used with many facts and should be unique for grain/domain value in the data warehouse. We’ll start with a basic type: the conformed dimension. A Common Dimensional Table: The Conformed Dimension Let’s now have a look at some of the dimension tables you’ll come across in a data warehouse environment. This primary key is the basis for joining the dimension table to one or more fact tables.Ĭompared to fact tables, dimension tables are small in size, easy to store, and have little impact on performance. Dimensional tables have one primary key based on the underlying business key or a surrogate key. They have low cardinality and are usually textual and temporal. Attributes are the columns that we summarize, filter, or aggregate. The main characteristic of dimension tables is their multitude of attributes. (To read more about fact tables, check out these posts on data warehousing, the star schema, the snowflake schema, and facts about fact tables). Fact tables define the measurements dimensional tables give context. fact tables) of the data warehouse (DWH) system, we spend more time on their definition and identification than on any other aspect of the project. Since they give substance to the measurements (i.e. They tell us all we need to know about an event. In this article, we are going to take a closer look at each type of dimensional table.ĭimensional tables provide context to the business processes we wish to measure. Dimensional tables are the interesting bits, the framework around which we build our measurements. When we start a data warehousing project, the first thing we do is define the dimensional tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |