Unit 3: Data Management and Data Warehousing

3a. Apply data management principles such as planning, development, and implementation to database system design

  • Why is it important to integrate data from various sources into a coherent structure for Business Intelligence (BI) systems?
  • How do principles such as data cleansing, transformation, and validation contribute to maintaining the quality and reliability of data in BI systems?
  • What roles do data governance and security principles play in ensuring the integrity and protection of data within Business Intelligence systems?

Effective data management principles are crucial for the success of Business Intelligence (BI) systems, as they ensure that data is accurate, reliable, and accessible. Proper data management starts with the collection and integration of data from various sources, which must be harmonized into a coherent structure. This integration allows BI systems to provide a unified view of information, facilitating more accurate analysis and reporting. Without rigorous data management practices, discrepancies and inconsistencies can arise, leading to misleading insights and potentially flawed business decisions. Ensuring data accuracy and consistency through well-defined data management principles helps maintain the integrity of BI outputs and supports effective strategic planning.

Another vital aspect of data management in BI systems is data quality control. Principles such as data cleansing, transformation, and validation are essential to rectify errors, standardize formats, and ensure that data meets predefined quality standards. For instance, data cleansing involves removing duplicates and correcting inaccuracies, while data transformation standardizes data formats to ensure compatibility across different systems. Implementing these principles helps prevent data-related issues that could hinder the analytical capabilities of BI systems. High-quality data management practices lead to more reliable and actionable insights, enabling organizations to make informed decisions based on accurate and complete information.

Data management principles also play a significant role in data governance and security within BI systems. Effective data governance involves establishing policies and procedures for managing data throughout its lifecycle, including data access, usage, and compliance with regulations. This ensures that sensitive information is protected and that data management practices adhere to legal and organizational standards. Robust data security measures, such as encryption and access controls, are crucial to safeguarding data from unauthorized access and breaches. By upholding strong data governance and security principles, organizations can maintain trust in their BI systems and protect valuable data assets, ultimately supporting more secure and compliant business operations.

Because BI systems rely on real-time access to very large amounts of data, the design of the database system is critical. A database system is a software application that manages, stores, and organizes data in a structured way. Such systems are developed following widely adopted planning methodologies, and good planning is essential.

Because BI systems rely on real-time access to large amounts of data, data retrieval speed is an important consideration. The designer must evaluate retrieval speeds for appropriateness and take steps to increase retrieval speed to meet design requirements.

To review, see:


3b. Implement a data warehousing structure to centralize data in support of BI

  • What are some of the challenges of data warehouse administration?
  • How is data extracted from the data warehouse?
  • What role does the Database Administrator (DBA) play in ensuring that data from operational systems is effectively extracted, transformed, and loaded into the data warehouse?

The fundamental purpose of a data warehouse is to store data that has been extracted from internal transaction processing systems and from external sources. The data will be reformatted to meet the needs of the BI systems that will use it. The data warehouse will not be integrated with and will not contain operational data from the transaction processing systems. In addition, the data warehouse may or may not be segmented into specialized data marts. Front-end tools for querying, reporting, and data visualization are used to access and analyze the stored data. This architecture supports efficient data management, retrieval, and analysis, enabling organizations to derive actionable insights and make informed decisions.

To support the needs of BI systems, the DBA must ensure that the data stored in operational and transaction processing systems (a type of software that captures, processes, and manages transaction data in real time) can be extracted and moved to the data warehouse supporting the BI system. This extraction process must also allow for the conversion of the operational data into whatever format meets the needs of the warehouse and the BI system.

Extraction systems will only extract and transform the information they are specified for. Extraction systems have no mechanisms for auditing and checking data quality, completeness, or reliability. Such systems exist to automate the process of extracting data from the warehouse, transforming it into the appropriate formats, and loading the data into the BI systems.

To review, see:


3c. Apply effective data modeling techniques like the relational model for BI systems to analyze and define the different data types a business collects and produces

  • How does the relational model facilitate the organization and querying of data in BI systems?
  • What role does normalization play in enhancing the effectiveness of the relational model?
  • Why is it important to define data types and constraints?

Many databases that support BI systems will be relational. Understanding the relational structure and how it interacts with SQL queries is essential. The relational model organizes data into tables with rows and columns, where each table represents a different entity, and relationships between entities are defined through keys. This model facilitates the clear definition of data types, such as numeric, text, and date, and establishes rules for data integrity (the accuracy, consistency, and reliability of data throughout its lifecycle) and relationships. By applying the relational model, businesses can create a structured schema that accurately represents their data, enabling efficient querying and reporting in Business Intelligence (BI) systems. This structured approach helps ensure that data is consistent, accurate, and readily accessible for analysis.

The relational model's emphasis on data normalization, which involves organizing data to reduce redundancy and improve data integrity, further enhances its effectiveness in BI systems. Normalization ensures that each piece of data is stored only once and is linked through well-defined relationships. This minimizes data anomalies and inconsistencies, making it easier to maintain and update the data as needed. By applying normalization principles, businesses can create a robust data model that supports complex queries and analytical processes, allowing BI systems to generate precise insights and reports based on clean and well-organized data.

Data modeling is the first step in database design. This step is sometimes considered a high-level and abstract design phase, also called conceptual design. This phase aims to describe the data contained in the database (entities: students, lecturers, courses, subjects), the relationships between data items (lecturers supervise students; lecturers teach courses), and the constraints on data (student number has exactly eight digits; a subject has four or six units of credit only). 

Effective data modeling with the relational model involves defining clear data types and constraints to ensure that the data collected and produced by a business aligns with its analytical needs. For instance, specifying data types such as integer, varchar, or date helps maintain data consistency and accuracy. Constraints such as primary keys, foreign keys, and unique constraints enforce data integrity and establish relationships between tables. By carefully designing the data model with these techniques, businesses can optimize their BI systems to handle a wide range of data types and ensure that the data supports meaningful analysis and informed decision-making.

To review, see:


3d. Integrate data management and warehousing in BI and analytics projects

  • How does the integration of data management principles into data warehousing solutions impact an organization's ability to derive actionable insights?
  • What are some common tasks involved in the data scrubbing process?
  • How can strong administrative procedures and policies contribute to maintaining the overall quality, integrity, and consistency of data within a data warehouse?

The information in the BI system is critically dependent on the overall quality, integrity, and consistency of the data in the data warehouse. To ensure this data quality, strong administrative procedures and policies are necessary. The integration of data management and data warehousing involves aligning strategies, technologies, and methods to effectively collect, store, manage, and analyze data assets within an organization. 

Data management encompasses the practices and policies for ensuring data quality, integrity, security, and compliance throughout its lifecycle. In contrast, data warehousing focuses on creating centralized repositories for storing and organizing data from multiple sources to support reporting, analytics, and decision-making. 

By integrating data management principles and practices into the design, implementation, and operation of data warehousing solutions, organizations can establish robust frameworks for data governance, metadata management, master data management, and data quality management, enabling them to derive actionable insights, enhance data-driven decision-making, and drive business value effectively. This integration facilitates the seamless flow of high-quality data across the organization, empowering users to access trusted, consistent, and relevant information to support their strategic initiatives and operational processes.

Preparing data for BI analysis is commonly known as data scrubbing or cleaning. Generally, it can be a very long, large, and complex process, but the concepts are fairly straightforward. The process involves identifying and correcting errors, inconsistencies, and inaccuracies in a dataset to ensure its quality and reliability. This could include tasks such as removing duplicate records, fixing data entry errors, standardizing formats, and addressing missing or incomplete values. By applying data scrubbing techniques, organizations can enhance the accuracy and completeness of their data, which in turn improves the effectiveness of data analysis and decision-making. This ensures that the information used for reporting and business intelligence is consistent and trustworthy, ultimately supporting more reliable insights and strategic decisions.

To review, see:


Unit 3 Vocabulary

This vocabulary list includes terms you will need to know to successfully complete the final exam.

  • database
  • data cleansing
  • data integrity
  • data management
  • data normalization
  • data scrubbing
  • data security
  • relational model
  • transaction processing system
Back to 'Study Guide\'