DATA WAREHOUSE QUESTIONS

pdf

School

Marshall University *

*We aren’t endorsed by this school

Course

653

Subject

Information Systems

Date

Oct 30, 2023

Type

pdf

Pages

Uploaded by KidSnow15259

CONCEPTUAL DESIGN SALES TRANSACTION PRODUCT CUSTOMER

LOGICAL DESIGN SALES TRANSACTION SALES ID SALE DATE CUSTOMER ID PRODUCT ID QUANTITY SOLD TOTAL AMOUNT CUSTOMER CUSTOMER ID FIRST NAME LAST NAME EMAIL PHONE NUMBER PRODUCT PRODUCT ID PRODUCT NAME CATEGORY PRICE MANUFACTURER

PHYSICAL DESIGN SALES TRANSACTION SALES ID(PK) SALE DATE CUSTOMER ID(FK) PRODUCT ID(FK) QUANTITY SOLD TOTAL AMOUNT CUSTOMER CUSTOMER ID(PK) FIRST NAME LAST NAME EMAIL PHONE NUMBER PRODUCT PRODUCT ID(PK) PRODUCT NAME CATEGORY PRICE MANUFACTURER

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

3. OLAP stands for Online analytical processing . You may use online analytical processing (OLAP) software to evaluate company data from many angles. Business intelligence and data analysis are the primary uses of OLAP. OLAP is applicable to- Data analysis: OLAP enables users to analyze complex data by providing a multidimensional data view. Users may slice and dice data along many dimensions to get insights. You could analyze sales data in a sales database by geography, product, time period, and other factors. Data visualization : The most advanced OLAP interfaces offer a variety of common business visualization tools, such as bar charts, pie charts, and time series, as well as more complex layouts, such as scatterplots, maps, tree maps, cartograms, matrices, grids, etc., to enhance the pivot table view. Applications for business intelligence (BI): OLAP (Online Analytical Processing) is the technology that underlies many BI applications. OLAP is a potent data discovery tool that allows for endless report viewing, intricate analytical computations, and scenario planning for "what if" scenarios (budget, forecast). Budgeting and financial analysis : To analyze financial data, keep budgets, and evaluate the financial health of a business, many finance departments use OLAP. OLAP software may provide data on revenue, expenses, and profitability. Decision support : OLAP is widely used in decision support systems. OLAP technologies may be used by decision-makers to assess several situations and make wise decisions based on multidimensional data analysis. 2. WHAT IS OLTP? OLTP stands for Online transactional processing . Online booking, ticketing, reservation systems, and recordkeeping software like that used in health records, inventory management, and production scheduling are examples of systems that employ OLTP. ATMs, financial transaction systems, and online banking applications are also included. Financial services : OLTP is used in the banking industry for tasks including ATM transactions, cash transfers, balance inquiries, and account maintenance. In the financial sector, OLTP systems are utilized for the quick and precise processing of stock trading, order execution, and transaction settlements.

Healthcare : Real-time data processing is necessary in healthcare institutions for patient records, appointment scheduling, and invoicing. OLTP systems guarantee the accuracy and currentness of patient data. Retail and e-commerce : OLTP systems are essential for managing real-time transactions like sales, inventory changes, and order processing in retail and e-commerce enterprises. These technologies ensure that clients may purchase goods, check inventory availability, and complete their payments without any delays. 4. ETL? Extract, transform, and load, or ETL, is a commonly used method for businesses to aggregate data from several systems into a single database, data store, data warehouse, or data lake. The ETL process consists of five phases, including extract, clean, transform, load, and analyze. The three main steps are extract, transform, and load. EXTRACTION : Data extraction involves moving or exporting raw data from the source locations to a staging place. A variety of structured and unstructured data sources are available for data extraction by data management teams. SQL servers, websites, and ERP systems are just a few of these sources. Transform: The raw data is processed in the staging area. For its intended analytical use case, the data is changed and consolidated in this place. The following tasks may be involved in this phase. • Filtering, extraction, removing duplicate records, confirming, and authenticating the data. • Making computations, translations, and summaries based on raw data. • Eliminating, encrypting, or safeguarding data that is subject to industry or regulatory regulations. • Formatting the data into separate tables or joining them together to conform to the target data warehouse's schema. LOAD : This stage involves loading the transformed data into the target data warehouse from the staging location. This often includes loading all data at once, followed by incremental data loads on a regular basis, and, less frequently, full refreshes that erase and replace all of the data in the warehouse. In most firms, ETL procedures are automated, well defined, ongoing, and batch-driven. ETL is frequently carried out after business hours, when the data warehouse's and the source systems' traffic is at its lightest.

The ETL (Extract, Transform, Load) process is a critical component of data warehousing. It involves extracting data from various sources, transforming it into a suitable format, and loading it into a data warehouse for analysis and reporting. Here's a detailed explanation of each step: 1. **Extract**: - **Extraction**: In this phase, data is extracted from heterogeneous sources such as databases, flat files, web services, or APIs. - **Connectivity**: ETL tools establish connections with these sources, which can be structured or unstructured data. 2. **Transform**: - **Data Cleaning**: Raw data is often inconsistent and may contain errors. ETL processes clean and validate data to ensure accuracy. - **Data Integration**: Data from different sources is integrated and combined into a unified format, often a data warehouse-specific schema. - **Data Transformation**: This step involves various operations like filtering, sorting, aggregating, and joining data to make it suitable for analysis. - **Data Enrichment**: Additional data may be added from reference sources to enhance the quality and context of the data. 3. **Load**: - **Loading Data Warehouse**: The transformed data is loaded into the data warehouse. The data can be loaded incrementally (only new or changed data) or in bulk (entire datasets). - **Indexes and Keys**: Indexes, primary keys, and foreign keys are applied to optimize query performance. - **Data Validation**: Data loaded into the warehouse is typically validated to ensure it matches the predefined data quality standards. ETL tools and processes play a crucial role in maintaining data quality and consistency in a data warehouse. The data warehouse, once populated, serves as a central repository for business intelligence and reporting, enabling data analysts and decision-makers to extract insights from the consolidated data. It's important to note that ETL is evolving with technologies like ELT (Extract, Load, Transform), where data is extracted and loaded into the data warehouse before transformation. This approach leverages the processing power of modern data warehouses and can be more suitable for handling large volumes of data.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version