Datawarehouse Architecture

Datawarehouse Architectures are mostly either data marts that represent different areas or one large enterprise warehouse (EDW) with all the data that are generated and collected by the enterprise. After we decide on that, we decide on the schema type. Datawarehouses choose essentially between star and snowflake schema.

Data Marts usually follow star schema and Data warehouse are designed into snowflake schema. These are some considerations on which one to use:

• Ease of maintenance and change: Snowflake schema is better. Due to less redundancy
• Ease of use: queries are more complex queries and tougher to understand in snowflake schema.
• Query performance: More foreign keys and more query execution time in snowflake schema
• For more detailed understanding visit snowflake vs star

A data model defines the relationships between data objects (data-to-data relationships). Likewise, a process model defines the relationships between processes (process-to-process relationships).

What is a data model?

A data model is an abstraction of some aspect of the real world (system)

Why a data model?

• Helps to visualize the business
• A model is a means of communication
• Models help elicit and document requirements
• Models reduce the cost of change
• Model is the essence of DW architecture based on which DW will be implemented

Steps in data modeling:

1. Problem scope and definition
2. Requirement gathering
3. Analysis
4. Logical database design
5. Deciding database
6. Physical database design
7. Schema generation

Data integration architecture focuses on ensuring data quality, particularly for reference and master data. Data integration identifies the database of record for reference and master data where a “golden” version of this data is maintained and controlled. Data integration architecture defines how other systems may read this data or subscribe to synchronized copies of the golden version of this data.

Just as data integration architecture defines how data flows across applications, so meta data architecture defines the managed flow of meta data — how meta data is created, integrated, controlled and accessed. The meta data repository is the core of any meta data architecture. Meta data architecture is the design for integration of meta data across software tools, repositories, directories, glossaries and data dictionaries. While the focus of data integration architecture is to ensure the quality, integration  and effective use of reference, master and business intelligence data, the focus of meta data architecture is to ensure the quality, integration and effective use of meta data.

Levels of modeling

Conceptual modeling

Logical modeling

Physical modeling

Modeling techniques

Entity-Relationship modeling

• Traditional modeling technique
• Technique of choice for OLTP
• Suited for corporate data warehouse

Dimensional modeling

• Analyzing business measures in the specific business context
• Helps visualize very abstract business questions
• End users can easily understand and navigate the data structure

Introduction to types of Datawarehouse