Dimension Tables

Dimension Table:

Dimension table is one that describe the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are sometimes called lookup or reference tables.

Location Dimension:

In a relational data modeling, for normalization purposes, country lookup, state lookup, county lookup, and city lookups are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called LOCATION DIMENSION for performance and slicing data requirements. This location dimension helps to compare the sales in one region with another region. We may see good sales profit in one region and loss in another region. If it is a loss, the reasons for that may be a new competitor in that area, or failure of our marketing strategy etc.

Example of Location Dimension:

Example of Location Dimension

 

Country Lookup:

Country CodeCountry NameDateTimeStamp
USAUnited States Of America1/1/2005 11:23:31 AM

State Lookup:

State CodeState NameDateTimeStamp
NYNew York1/1/2005 11:23:31 AM
FLFlorida1/1/2005 11:23:31 AM
CACalifornia1/1/2005 11:23:31 AM
NJNew Jersey1/1/2005 11:23:31 AM

County Lookup:

County CodeCounty NameDateTimeStamp
NYSHShelby1/1/2005 11:23:31 AM
FLJEJefferson1/1/2005 11:23:31 AM
CAMOMontgomery1/1/2005 11:23:31 AM
NJHUHudson1/1/2005 11:23:31 AM

City Lookup:

City CodeCity NameDateTimeStamp
NYSHMAManhattan1/1/2005 11:23:31 AM
FLJEPCPanama City1/1/2005 11:23:31 AM
CAMOSHSan Hose1/1/2005 11:23:31 AM
NJHUJCJersey City1/1/2005 11:23:31 AM

Location Dimension:

Location Dimension IdCountry NameState NameCounty NameCity NameDateTimeStamp
1USANew YorkShelbyManhattan1/1/2005 11:23:31 AM
2USAFloridaJeffersonPanama City1/1/2005 11:23:31 AM
3USACaliforniaMontgomerySan Hose1/1/2005 11:23:31 AM
4USANew JerseyHudsonJersey City1/1/2005 11:23:31 AM

 

Next⇒ Product Dimension

Data Modeling Standards | Modeling Data

Data Modeling standardization has been in practice for many years and the following section highlight the needs and implementation of the data modeling standards.

Standardization Needs | Modeling data:

Several data modelers may work on the different subject areas of a data model and all data modelers should use the same naming convention, writing definitions and business rules.

Nowadays, business to business transactions (B2B) are quite common, and standardization helps in understanding the business in a better way. Inconsistency across column names and definition would create a chaos across the business.

For example, when a data warehouse is designed, it may get data from several source systems and each source may have its own names, data types etc. These anomalies can be eliminated if a proper standardization is maintained across the organization.

Table Names Standardization:

Giving a full name to the tables, will give an idea about data what it is about. Generally, do not abbreviate the table names; however this may differ according to organization’s standards. If the table name’s length exceeds the database standards, then try to abbreviate the table names. Some general guidelines are listed below that may be used as a prefix or suffix for the table.

Examples:

Lookup – LKP – Used for Code, Type tables by which a fact table can be directly accessed.
e.g. Credit Card Type Lookup – CREDIT_CARD_TYPE_LKP

Fact – FCT – Used for transaction tables:
e.g. Credit Card Fact – CREDIT_CARD_FCT

Cross Reference – XREF – Tables that resolves many to many relationships.
e.g. Credit Card Member XREF – CREDIT_CARD_MEMBER_XREF

History – HIST – Tables the stores history.
e.g. Credit Card Retired History – CREDIT_CARD_RETIRED_HIST

Statistics – STAT – Tables that store statistical information.
e.g. Credit Card Web Statistics – CREDIT_CARD_WEB_STAT

Column Names Standardization:

Some general guidelines are listed below that may be used as a prefix or suffix for the column.

Examples:

Key – Key System generated surrogate key.
e.g. Credit Card Key – CRDT_CARD_KEY

Identifier – ID – Character column that is used as an identifier.
e.g. Credit Card Identifier – CRDT_CARD_ID

Code – CD – Numeric or alphanumeric column that is used as an identifying attribute.
e.g. State Code – ST_CD

Description – DESC – Description for a code, identifier or a key.
e.g. State Description – ST_DESC

Indicator – IND – to denote indicator columns.
e.g. Gender Indicator – GNDR_IND

Database Parameters Standardization:

Some general guidelines are listed below that may be used for other physical parameters.

Examples:

Index – Index – IDX – for index names.
e.g. Credit Card Fact IDX01 – CRDT_CARD_FCT_IDX01

Primary Key – PK – for Primary key constraint names.
e.g. CREDIT Card Fact PK01- CRDT-CARD_FCT_PK01

Alternate Keys – AK – for Alternate key names.
e.g. Credit Card Fact AK01 – CRDT_CARD_FCT_AK01

Foreign Keys – FK – for Foreign key constraint names.
e.g. Credit Card Fact FK01 – CRDT_CARD_FCT_FK01

 

Data Modeling Frequently Asked Interview Questions And Answers – Part 1

The following data modeling questions and answers are conceptual questions that are asked during the data modeler interview.

 1. What is data modeling?

A data model is a conceptual representation of business requirement (logical data model) or database objects (physical) required for a database and are very powerful in expressing and communicating the business requirements and database objects. The approach by which data models are created is called as data modeling.

2. What does data model contain?

Logical Data Model: Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition, business rule, etc

Physical Data Model: Table, Column, Primary key Constraint, Unique Constraint or Unique Index, Non Unique Index, Check Constraint, Default Value, Foreign Key, comment etc.

Please refer http://www.learndatamodeling.com/diff_lpdm.php

3. What is a logical data model and logical data modeling?

A logical data model is the version of a data model that represents the business requirements (entire or part of an organization). This is the actual implementation and extension of a conceptual data model. Logical Data Models contain Entity, Attributes, Super Type, Sub Type, Primary Key, Alternate Key, Inversion Key Entry, Rule, Relationship, Definition etc. The approach by which logical data models are created is called as logical data modeling.

4. What is a physical data model and physical data modeling?

Physical data model includes all required tables, columns, relationship, database properties for the physical implementation of databases. Database performance, indexing strategy, and physical storage are important parameters of a physical model. The important or main object in a database is a table which consists or rows and columns. The approach by which physical data models are created is called as physical data modeling.

5. What is the difference between a logical and physical data model?

Please refer http://www.learndatamodeling.com/diff_lpdm.php

 6. What is a table (entity)?

Data stored in form of rows and columns is called as table. Each column has datatype and based on the situation, integrity constraints are enforced on columns.

7. What is a column (attribute)?

Column also known as field is a vertical alignment of the data and contains related information to that column.

8. What is a row?

Row also known as tuple or record is the horizontal alignment of the data.

9. What is ER (entity relationship) diagram or ERD?

ER diagram is a visual representation of entities and the relationships between them. In a data model, entities (tables) look like square boxes or rectangular boxes, which contain attributes and these entities, are connected by lines (relationship).

10. What is a primary key constraint?

Primary key constraint is imposed on the column data to avoid null values and duplicate values. Primary Key=Unique + Not Null. Example: social security number, bank account number, bank routing number

11. What is a composite primary key constraint?

When more than one column is a part of the primary key, it is called as composite primary key constraint.

12. What is a surrogate key?

In normal practice, a numerical attribute is enforced a primary key which is called as surrogate key.  Surrogate key is a substitute for natural keys. Instead of having primary key or composite primary keys, the data modelers create a surrogate key; this is very useful for creating SQL queries, uniquely identify a record and good performance.

13. What is a foreign key constraint?

Parent table has primary key and a foreign key constraint is imposed on a column in the child table.  The foreign key column value in the child table will always refer to primary key values in the parent table.

14. What is a composite foreign key constraint?

When group of columns are in a foreign key, it is called as composite foreign key constraint.

15. What are the important types of Relationships in a data model?

Identifying, Non-Identifying Relationship, Self-Recursive relationship are the types of relationship.

16. What is identifying relationship?

Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line. If the referenced column in the child table is a part of the primary key in the child table, relationship is drawn by thick lines by connecting these two tables, which is called as identifying relationship.

17. What is non-identifying relationship?

Usually, in a data model, parent tables and child tables are present. Parent table and child table are connected by a relationship line. If the referenced column in the child table is a not a part of the primary key and standalone column in the child table, relationship is drawn by dotted lines by connecting these two tables, which is called as non-identifying relationship.

18. What is self-recursive relationship?

A standalone column in a table will be connected to the primary key of the same table, which is called as recursive relationship.

19. What is cardinality?

One to One, One to many, and many to many are different types of cardinalities. In a database, high cardinality means more unique values are stored in a column and vice versa.

 20. What is a conceptual data model and conceptual data modeling?

Conceptual data model includes all major entities and relationships and does not contain much detailed level of information about attributes and is often used in the initial planning phase. Data Modelers create conceptual data model and forward that model to functional team for their review. The approach by which conceptual data models are created is called as conceptual data modeling.

21. What is an enterprise data model?

Enterprise data model comprises of all entities required by an enterprise. The development of a common consistent view and understanding of data elements and their relationships across the enterprise is referred to as Enterprise Data Modeling. For better understanding purpose, these data models are split up into subject areas.

22. What is relational data modeling?

The visual representation of objects in a relational database (usually a normalized) is called as relational data modeling. Table contains rows and columns.

23. What is OLTP data modeling?

OLTP acronym stands for ONLINE TRANSACTIONAL PROCESSING. The approach by which data models are constructed for transactions is called as OLTP data modeling. Example: all online transactions, bank transactions, trading transactions.

24. What is a constraint? What are the different types of constraint?

Constraint is a rule imposed on the data.  The different types of constraints are primary key, unique, not null, foreign key, composite foreign key, check constraint etc.

25. What is a unique constraint?

Unique constraint is imposed on the column data to avoid duplicate values, but it will contain NULL values.

 26. How many null values can be inserted in a column that has unique constraint?

Many null values can be inserted in an unique constraint column because one null value is not equal to another null value.

27. What is a check constraint?

Check constraint is used to check range of values in a column.

28. What is index?

Index is imposed on a column or set of columns for fastest retrieval of data.

29. What is a sequence?

Sequence is a database object to generate unique number.