What Are Dimension Tables?

ATTOM Cloud Database Design

ATTOM Cloud is your data warehouse for property data. The majority of its data is stored in a SQL Server database, with some components being implemented using Elastic, and Azure Cube. The SQL Server database largely follows a star schema, with a smattering of a snowflake schema. Both these schemas store data in a combination of fact tables and dimension tables.

Dimension Tables

Within ATTOM Cloud, the dimension tables can be identified as those starting with "Dim". Examples include DimCounty, DimGeographyType, and DimPool

Fact Tables

The remaining tables are fact tables. Examples of fact tables include AVM, BuildingPermits, ForeclosureDetails, PropertyFeatures, and RETransctions

Views

Sometimes a solution pack includes views, which combine fact tables and dimension tables to deliver data in a format suited to that particular use-case. This includes filtering out rows of data from those tables that are not relevant to the use-case.

Datasets and Fact Tables

There is not a one-to-one relationship between datasets and fact tables

  • Some datasets share a common table. e.g. Addresses and Property Characteristics make up the PropertyFeature table
  • Some datasets have multiple tables. e.g. Building Permits consists of the following tables - BuildingPermits, BuildingPermitClassifier, and BuildingPermitStatus
  • The fact tables used for datasets will often have dimension tables associated with them. e.g. Building Permits also makes use of DimBuildingPermitClassifier and DimBuildingPermitStatus

Solution Packs and Fact Tables

Each solution pack contains a list of the datasets included

Each solution pack contains a list of the fact tables included. This is largely based on the datasets that have been included.

Datasets and Dimension Tables

Not all datasets are stored as fact tables. There are some datasets that are stored as dimension tables.

  • Some datasets are stored as dimension tables. Examples include County (DimCounty), and School Profile (DimSchool)
  • Some datasets are represented by multiple dimension tables. e.g. FEMA Flood consists of the dimension tables DimFEMAFirmPanel and DimFEMAFlooHazard

If a dataset is stored in a dimension table, these dimension tables will also be listed in the solution pack as SQL objects.

Fact Tables and Dimension Tables

The most common use of Dimension tables within ATTOM Cloud is to normalize the data. An example of this can be found in the PropertytFeature table. The PropertyType field is a code assigned to each property that defines the primary use made of the property. This is stored as a code. An associated dimension table - DimPropertyType then holds information specific to each unique value of PropertyType.

Finding The Dimension Tables for a Dataset

Each ATTOM Cloud dataset has a data dictionary that outlines all the fields contained in the tables that make up the dataset. If a field in a table uses a dimension table to details about values in that field, there are two columns in the data dictionary that identify the dimension table used, and the field in the dimension table that should be used when joining the data in the two tables.

The data dictionary will often have a tab for each dimension table that explains the layout of the dimension table

Note: All of the dimension tables used by a fact table are automatically included with any solution pack.

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.