Database Management Systems

In dimensional modeling, fact tables link to dimension tables using surrogate keys - artificially generated keys that are independent of source system keys. Surrogate keys provide stability, performance, and handle slowly changing dimensions better than natural or compound keys.

Casual dimensions (also called degenerate dimensions) are dimension attributes stored in the fact table that have no corresponding dimension table. They explain why a fact exists - such as invoice number, order number, or transaction ID - and provide context without requiring a separate dimension table.

Factless fact tables contain no measures - only foreign keys to dimensions. They are used to track events or represent relationships that occur, such as student attendance, product promotions, or patient visits. The existence of a record indicates the event happened.

A 'wrinkle of time' refers to the latency delay between when data changes in operational systems and when those changes appear in the data warehouse. This lag can occur due to ETL batch windows, processing time, or intentional delays. It's a key data freshness metric.

Derived data is data that is calculated from other data and stored in the data warehouse. Examples include totals, ratios, percentages, or any computed values. Unlike raw data which is copied directly, derived data involves computation before storage.

When integrating acquired company data, the primary challenge is data quality - different formats, standards, duplicates, and inconsistencies must be cleansed and transformed before loading into the enterprise warehouse. Network issues, security concerns, and raw data size are secondary to ensuring clean, consistent integration.

Metadata in a data warehouse contains descriptive information about the structure, meaning, and lineage of data fields. This includes field names, data types, source systems, transformation rules, and business definitions. End users access metadata repositories or data dictionaries to understand field details without needing to examine ETL scripts or technical documentation.

Shrunken dimensions are created through the aggregation process in dimensional modeling. When a dimension table is summarized or aggregated at a higher level (such as aggregating from daily to monthly), it becomes a shrunken dimension. This technique reduces dimension size and improves query performance for summary-level analysis.

Stovepipe data marts are built independently for specific departments or business functions without integration with the enterprise data warehouse or other data marts. They are called independent data marts because each mart operates in isolation, creating data silos that can lead to inconsistent definitions and redundant data across the organization.

Bitmapped indexing is highly efficient for data warehouse applications because it uses bitmaps to represent indexing information, making it ideal for low-cardinality columns (like gender, status, yes/no fields) and complex multi-column queries common in analytical processing. B-tree indexing is better suited for OLTP systems with high-cardinality data, while vector indexing and join indexing are not standard indexing techniques used in data warehouses.

Dormant data refers to data that has been loaded into the data warehouse but is never accessed or used by any queries or reports. This wastes storage space and ETL processing resources. Organizations should identify dormant data through usage monitoring and consider archiving or removing it to optimize warehouse performance and reduce costs.

In the Corporate Information Factory (CIF) architecture, Near Line Storage (NLS) holds the highest volume of data. NLS stores archival and historical data that's not actively used but must be retained. Data warehouses contain current and historical data for analysis but have less volume than NLS. ODS (Operational Data Store) holds current operational data. Data staging areas are temporary. Near Line Storage is designed for cost-effective storage of massive historical data volumes.

In top-down data warehouse architecture (Bill Inmon's approach), the enterprise data warehouse is created first as a single, integrated repository. Then, departmental data marts are derived from the warehouse. This ensures consistency across the organization and avoids data silos. Option B is incorrect because it contradicts the top-down principle. Option C describes independent data marts (bottom-up). Option D is wrong because data marts ARE created in top-down architecture.

In bottom-up architecture (Ralph Kimball's approach), data marts are created first to address specific business needs. These data marts can then be integrated to form an enterprise data warehouse. This is faster to implement initially but can lead to consistency challenges. Option A describes top-down, not bottom-up. Option C suggests independent marts with no integration, which isn't a complete architecture. Option D contradicts the bottom-up approach.

MOLAP (Multidimensional OLAP) uses pre-computed cubes for fast query performance. However, scalability is POOR, not good. MOLAP struggles with large datasets because cubes require extensive pre-computation and storage. Processing overhead for large datasets is high because all aggregations must be pre-calculated. Dimensions are often restricted (commonly to 10 or fewer) due to cube explosion. User interface is good because pre-computed results enable instant responses. Option C is actually NOT true about MOLAP - it's the limitation.