The data warehouse is a static data repository organized for quick data retrieval and easy data analysis that captures data from the HxGN EAM schema enabling you to combine data from different organizations across your entire enterprise.
The data warehouse consists of several data marts. Data marts are a subset of a data warehouse and are considerably smaller than the data warehouse. Data marts focus on a specific data area (such as inventory, work, etc.) using a fact table associated with one or more dimension tables.
The association between a fact table and its dimension tables is sometimes called
a star schema, because the central fact table is surrounded by its dimension tables
and joined to them via foreign keys.
Data marts can also be loaded into On-Line Analytical Processing (OLAP) cubes to enable fast, interactive access to a variety of possible information in a dimensional model.
The system populates the data warehouse tables using an ETL process that is administered by a database/system administrator who enables the ETL process to run at a regularly scheduled time or executes the process manually.
The following terms will help you become familiar with data warehouse concepts:
|
Term |
Definition |
|---|---|
|
Data mart |
Data marts are subsets of a data warehouse and are generally categorized as one of the following: Transaction data mart - A transaction data mart records individual transactions such as material transactions. There can be many such transactions each day, and the fact table can grow to a very large size. Periodic snapshot - A periodic snapshot summarizes measured values over a period of time (usually by day), and is useful where transaction grain is not required. Accumulating snapshot - Accumulating snapshots track lifecycle activity for entities such as work orders and purchase orders that pass through several stages before being closed or completed. For example, the work order accumulating snapshot records the creation date for each work order, the schedule date for work order start and completion, the actual start and completion date for each work order, the lag times between some of these dates, and work order cost information such as estimated and actual labor and material costs. |
|
Dimension table |
A dimension table is a collection of related fields used for analysis that usually contains text or descriptive fields. An example of a dimension table is an employee dimension table that contains the employee code, name, and shift field information. Another example of a dimension table is a date dimension table containing the date, day of week, day of month, quarter, and year field information. |
|
Extract, Load, and Transform (ETL) Process |
An Extract, Transform, and Load (ETL) process runs on a regular basis (usually nightly) to extract the day’s data from the source database. The process then loads the data into the data warehouse. The data warehouse accumulates/collects data history over time to use as an analytical information source. |
|
Fact table |
A fact table contains the measured fact data for tracking and analysis. Most facts are numeric, and record information such as levels, magnitudes, and costs. Fact table data is summed, averaged, counted, etc., according to the dimension fields selected by the analyst. |
|
Grain |
Grain of fact is the unit of analysis that indicates the level of detail captured in the data warehouse. The grain of fact represents the meaning of a single row within a fact table; for example, the grain of fact indicating the frequency that data is captured for a row in the data warehouse might be hourly, daily, or monthly, etc. |