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:
Country Lookup:
| Country Code | Country Name | DateTimeStamp |
|---|---|---|
| USA | United States Of America | 1/1/2005 11:23:31 AM |
State Lookup:
| State Code | State Name | DateTimeStamp |
|---|---|---|
| NY | New York | 1/1/2005 11:23:31 AM |
| FL | Florida | 1/1/2005 11:23:31 AM |
| CA | California | 1/1/2005 11:23:31 AM |
| NJ | New Jersey | 1/1/2005 11:23:31 AM |
County Lookup:
| County Code | County Name | DateTimeStamp |
|---|---|---|
| NYSH | Shelby | 1/1/2005 11:23:31 AM |
| FLJE | Jefferson | 1/1/2005 11:23:31 AM |
| CAMO | Montgomery | 1/1/2005 11:23:31 AM |
| NJHU | Hudson | 1/1/2005 11:23:31 AM |
City Lookup:
| City Code | City Name | DateTimeStamp |
|---|---|---|
| NYSHMA | Manhattan | 1/1/2005 11:23:31 AM |
| FLJEPC | Panama City | 1/1/2005 11:23:31 AM |
| CAMOSH | San Hose | 1/1/2005 11:23:31 AM |
| NJHUJC | Jersey City | 1/1/2005 11:23:31 AM |
Location Dimension:
| Location Dimension Id | Country Name | State Name | County Name | City Name | DateTimeStamp |
|---|---|---|---|---|---|
| Location Dimension Id | Country Name | State Name | County Name | City Name | DateTime Stamp |
| 1 | USA | New York | Shelby | Manhattan | 1/1/2005 11:23:31 AM |
| 2 | USA | Florida | Jefferson | Panama City | 1/1/2005 11:23:31 AM |
| 3 | USA | California | Montgomery | San Hose | 1/1/2005 11:23:31 AM |
| 4 | USA | New Jersey | Hudson | Jersey City | 1/1/2005 11:23:31 AM |