Optimized Data Analysis and Reporting
with a Data Warehouse for AMS
Digitalization plays a crucial role in gaining competitive advantages. For informed decisions to be made within the company, it is essential that data is visible, retrievable, and analyzable.
AMS has numerous statistical data that provide valuable insights into jobs, loads, and complaints.
However, deeper analyses, securing data over a longer period, and optimal access are only possible after a restructuring of the data. The comprehensive solution is the implementation of a Data Warehouse* in combination with an ETL process* (Extract-Transform-Load).
Your Benefits
Centralized and Unified Data Storage
A common storage location for AMS data and external data records facilitates access.
Improved Strategic Planning
Permanent storage of data allows for analyses over longer periods.
Real-Time Analyses and Reports
Faster decision-making through standardized data retrieval.
Optimized Analyses & Reporting
Connection to Business Intelligence tools with dashboard view possible at any time.
What is a Data Warehouse?
What benefit does it have? A data warehouse is a central system for storing and managing data, specifically designed for data retrieval for analysis, and reporting purposes. It stores data from various sources (e.g., AMS) and enables comprehensive insights into historical data to analyze trends over longer periods. The structure of a data warehouse is optimized to support fast and efficient queries – using a snowflake schema.
Why is an ETL Process necessary?
Data warehouses serve as the foundation for business intelligence tools that allow companies to visualize data, create reports, and conduct deeper analyses of their business processes. The ETL process (Extract, Transform, Load) is crucial for data integration. It ensures that data from all source systems (AMS, etc.) is brought into a suitable format and universally readable in the data warehouse.
What is a Snowflake Schema?
A snowflake schema is a data model used in data warehouses to organize data: It is characterized by a normalized data structure where fact tables are connected to subordinate dimension tables. A fact table for AMS loads can, for example, be connected to appropriate dimension tables for material, treatment type, and date. This division leads to a data structure that reduces storage requirements and allows for quick retrieval.
An Overview of the Functions of AMSETL
The data warehouse stores the following historical data from AMS:
Job Items
Loads (treatments / work processes)
Complaints Storage from external data sources is also possible.
The following dimension tables are used in the data warehouse:
Time / Date / Hour / Minute / Second
Year / Month / Quarter / Week / Weekday
Action template · Complaint / Reason
Contact person · Conversion norms
Cost center
Currency
Customer / Location / Department / Employee
Equipment / Equipment type
Industry · Treatment / (Treatment) instruction
Job / Job item / Status / Type
Load / load status
Material / Material type
Pre-treated / Nitrided / Status
Item sequence
Transport / Unit
Work plan
Work process / Work process load
The timing rhythm of data exports from AMS to the data warehouse can be set in advance:
daily
weekly
monthly
The export always represents a snapshot of the data at the time of export. If data is overwritten multiple times between two export times, the system only considers the last change. For the initial export, it can be set how far back in the past the data should be transferred (e.g., all items from the last 3 months). After that, only the data that has changed since the last status or that has been newly added will be exported.
The following fact tables are used in the data warehouse:
Complaint costs / Complaint processing
Assignment of job items / Production jobs for loads
Assignment of job items or production jobs to process work steps
job item costs / job item processing / job item sales
Load costs / Load processing
Work process load costs or work process load processing
The following Business Intelligence tools can be connected to the data warehouse by default:
Power BI
QLIKS
ARBUBA BI (more upon request)
Without the connection to a BI tool, the data can be evaluated in SQL tables
TTC Informatik GmbH provides you with the complete data warehouse solution upon request: From exporting AMS data (and data from external data sources) in the ETL process to storage in a data warehouse to connecting to a Business Intelligence tool for optimal data evaluation.
Contact us! We will create the optimal data warehouse solution for you!
Required Licenses:
AMSETL
Technical Requirements
Sufficient storage space is necessary. If a third-party DBMS is used, requirements / storage needs must be coordinated with the administrator of the existing DBMS. The Data Warehouse runs on a Firebird database. Therefore, the system for evaluating the data (e.g., BI tool) must be compatible with Firebird.
