You are here: Home Data Modeling Location Dimension

---

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: Figure 1.8

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	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

---

Data Warehouse and Data Mart       Product Dimension Example

---