Data Warehousing Question & Answer

---

( Suggestion : Find the question by search page and keep refreshing the page for updated content )

---

Q.1. This is a subjective question have to write your answer in the
below.

You are employed by a retail corporation as a @data analyst. The business wishes to use OLAP technology to analyze its sales data
multidimensional schema for the scetato that is presented and give a brief justification of each dimension, fact, and hierarchy that was employed.
You are welcome define the attributes, keys and names of the dimensions and tact as per your best judgement

Answer:-

To effectively analyze the sales data using OLAP technology, a multidimensional schema can be designed with appropriate dimensions, facts, and hierarchies. Based on my best judgment, here’s an example of a multidimensional schema for the given scenario:

Dimension: Time
Attributes: Date, Month, Year, Quarter, Weekday
Hierarchy: Year > Quarter > Month > Date > Weekday
Key: Time_ID
Justification: The Time dimension allows for analyzing sales data based on various time-based attributes. It enables the business to analyze sales trends, seasonality, and identify patterns based on different time periods.

Dimension: Product
Attributes: Product_ID, Product_Name, Category, Brand, Supplier
Hierarchy: Category > Brand > Product_Name
Key: Product_ID
Justification: The Product dimension enables analysis of sales based on different product attributes. It allows the business to identify the performance of various product categories, brands, and specific products in terms of sales.

Dimension: Location
Attributes: Location_ID, Store_Name, City, State, Country
Hierarchy: Country > State > City > Store_Name
Key: Location_ID
Justification: The Location dimension facilitates analysis of sales based on different geographic attributes. It helps in understanding sales performance across different regions, cities, and individual stores.

Dimension: Customer
Attributes: Customer_ID, Customer_Name, Age, Gender, Occupation
Hierarchy: Gender > Age > Occupation > Customer_Name
Key: Customer_ID
Justification: The Customer dimension allows for analyzing sales based on customer-related attributes. It helps in understanding the purchasing behavior of different customer segments, identifying target demographics, and evaluating customer loyalty.

Fact: Sales
Attributes: Sales_Amount, Units_Sold, Profit
Key: Time_ID, Product_ID, Location_ID, Customer_ID
Justification: The Sales fact table contains the quantitative measures associated with sales, such as the sales amount, units sold, and profit. It is the central fact table that links all the dimensions together, enabling analysis based on different combinations of dimensions.

By employing this multidimensional schema, the retail corporation can leverage OLAP technology to analyze sales data from various perspectives. They can gain insights into sales trends over time, identify top-selling products in different locations, understand the impact of customer attributes on sales, and make informed business decisions based on the analysis of multidimensional data.

---

Q.2. This is a subjective question,

sales for Japan and India in 2019 by store zip code, month, and division subtotals. You can assume any facts in sales fact table and associate respective dimensions
b) Write a single query? summarize (SUM) store sales for subtotals for year quarter and month and India between 2019 and 2020 by year A month Generate parton

a.) single query to summarize (SUM ,MIN ,COUNT) sales for japan and india in 2019 by zip code, month and division. Generate all subtotals. You can assume any facts in sales_fact and associate respective dimensions.
c). Write a single query to summarize sum of store sales for Japan and India in 2019 by store-zip and month. Generate only subtotals for store zip. month and grand total without combinations of store zip and month.

Answer:-

Sales OLAP Schema Design

User
This is a subjective question have to write your answer in the
below.

Here’s a single query to address each part of the question:

a) To summarize (SUM, MIN, COUNT) sales for Japan and India in 2019 by zip code, month, and division, generating all subtotals:

sql
Copy code
SELECT
Country,
Store_Zip,
Month,
Division,
SUM(Sales_Amount) AS Total_Sales,
MIN(Sales_Amount) AS Min_Sales,
COUNT(*) AS Transaction_Count
FROM
SalesFactTable
WHERE
Country IN (‘Japan’, ‘India’)
AND EXTRACT(YEAR FROM Time) = 2019
GROUP BY
Country,
Store_Zip,
Month,
Division
Explanation:

The query retrieves data from the SalesFactTable, filtering for the countries Japan and India and the year 2019.
The SUM function calculates the total sales, the MIN function calculates the minimum sales, and the COUNT function counts the number of transactions.
The results are grouped by Country, Store_Zip, Month, and Division, generating subtotals for each combination.

b) To summarize store sales for India between 2019 and 2020 by year quarter and month, generating subtotals for each quarter and month:

sql
Copy code
SELECT
Country,
EXTRACT(YEAR FROM Time) AS Year,
CONCAT(EXTRACT(YEAR FROM Time), ‘-‘, EXTRACT(QUARTER FROM Time)) AS Year_Quarter,
Month,
SUM(Sales_Amount) AS Subtotal
FROM
SalesFactTable
WHERE
Country = ‘India’
AND EXTRACT(YEAR FROM Time) BETWEEN 2019 AND 2020
GROUP BY
Country,
Year,
Year_Quarter,
Month
Explanation:

The query retrieves data from the SalesFactTable, filtering for the country India and the years 2019 and 2020.
The EXTRACT function is used to extract the year and quarter from the Time column.
The CONCAT function combines the year and quarter values into a Year_Quarter column.
The results are grouped by Country, Year, Year_Quarter, and Month, generating subtotals for each quarter and month.

c) To summarize the sum of store sales for Japan and India in 2019 by store-zip and month, generating subtotals for store zip, month, and grand total without combinations of store zip and month:

sql

SELECT
Country,
Store_Zip,
Month,
SUM(Sales_Amount) AS Subtotal
FROM
SalesFactTable
WHERE
Country IN (‘Japan’, ‘India’)
AND EXTRACT(YEAR FROM Time) = 2019
GROUP BY
GROUPING SETS ((Country, Store_Zip, Month), (Country), ())
Explanation:

The GROUPING SETS clause allows us to specify multiple grouping levels and a grand total level.
By using (Country, Store_Zip, Month), (Country), (), we specify the grouping sets for store zip and month as well as the grand total level.
This query will generate subtotals for each combination of Country, Store_Zip, and Month, as well as subtotals by Country and the grand total without the combinations.

---

Q.3. This is a subjective question hence, you have to write your answer in the Text field given below.

State True or False for the following Statements.

Gelow

1. Backing up the data warehouse is not necessary under any conditions because you can recover data from the source
4.Normally, data flows from the data staging area to the data warehouse repository

5.It is more important to include unstructured data than structured data in a data warehouse

6. Type 2 changes for slowly charging dimensions relate to correction of errors

7. The key of the fact table is dependent on the keys of the dimension tables.

2.Slice-and-dice is the same as the rotation of the columns and rows in presentation of data

8. Maintaining metadata in a modern data warehouse is just for documentation.

10. It is a good practice to drop the indexes before the initial load. 36939-84206-2023/07/drop

9.Consolidated data marts such as Profitability should be built before other data marts.

8. ROLAP servers handle larger volumes of data compared to MOLAP servers. Consolidated data marts such as Profitability should be built before

Answer:-

1. False. Backing up the data warehouse is necessary to ensure data integrity and recoverability in case of data loss or corruption. The source data alone may not be sufficient for recovery.
2. 2. True. Data typically flows from the data staging area to the data warehouse repository during the ETL (Extract, Transform, Load) process.
3. 3. False. In a data warehouse, structured data (such as transactional data) is usually more important and commonly used for analysis compared to unstructured data (such as text documents or images). However, there may be cases where including unstructured data becomes important for specific analysis requirements.
4. 4. False. Type 2 changes for slowly changing dimensions refer to maintaining historical values and tracking changes over time, not just correcting errors.
5. 5. True. The fact table’s primary key is composed of foreign keys from the associated dimension tables. The fact table’s data is linked to the dimension tables through these keys.
6. 6. False. Slice-and-dice refers to the exploration and analysis of data by selecting and filtering specific dimensions and measures. It is different from rotating columns and rows in data presentation, which is called pivoting.
7. 7. False. Maintaining metadata in a modern data warehouse is essential for various purposes, including data lineage, data governance, data quality management, and query optimization, in addition to documentation.
8. 8. True. Dropping indexes before the initial load can improve the load performance as indexes can slow down the data loading process. Indexes can be recreated after the initial load is completed.
9. 9. False. Consolidated data marts, such as Profitability, are typically built after building individual data marts. Consolidated data marts integrate data from multiple data marts to provide a broader view of the organization’s performance.
10. 10. False. There is no direct correlation between the type of OLAP server (ROLAP or MOLAP) and the volume of data it can handle. Both ROLAP and MOLAP servers can handle large volumes of data. The decision to build consolidated data marts before others depends on the specific requirements and priorities of the organization.
    

    ---

    
    
    
    

    Q.4. ABC University implemented their attendance information system in the form of a Star SchemaDate Dimension: date key (16 bytes), day one), day of the week (int), month(int), quanterint), year(int)

    Facility Dimension: facility key (20 char), street (20 char), city (20 char), state or province (20 char), country (20 chan9-84200-20

    Course Dimension: Course code(20 chan), Course Name(40 char), Discipline (40 char)

    Student Dimension: Student Id(int), Student Name(30 char), Degree(16 char)

    Faculty Dimension: Faculty Id(int), Faculty Name(30 char), Department (16 char)

    The university has 250,000 students on rolls and 4 classes are conducted every day. There 20 working days in Assume that there are 10,000 faculty, 100 facility locations, and that one year data is stored in operational store monthly partitions

    Note: You can state your assumptions and constraints to support your calculation

    1) Estimate the total size of the fact table in Gigabytes
    2) What is surrogate key and state the features of Surrogate keys?

Q.5. (i)Match the following correctly 1 strategic information.

Nature of olap

Nature of oltp

2 operational system. 3 order processing. Used for decision making

4 aggregated sales of a

Product.

5 Repetitive access.

6. Adhoc acess.

Day to day operations Olap application Oltp application

Answer:-

Matching the given descriptions correctly:

Strategic information – Olap application
Operational system – Oltp application
Order processing – Nature of OLTP
Aggregated sales of a product – Nature of OLAP
Repetitive access – Day to day operations
Adhoc access – Used for decision making

Q.5.(ii) list the application system or operational systems can be used to build the enterprice data warehouse (EWD) for hospitals.

Answer:-

There are several application systems or operational systems that can be used to build the enterprise data warehouse (EDW) for hospitals. These systems are designed to handle different aspects of hospital operations and can contribute data to the EDW. Some of the key application systems used in hospitals for EDW development include:

Electronic Health Record (EHR) System: The EHR system is a central application in hospitals that stores patient medical records, including demographics, medical history, diagnoses, treatments, medications, and test results. Integrating the EHR system with the EDW allows for comprehensive data analysis and reporting.

Laboratory Information System (LIS): The LIS manages and stores laboratory test orders, results, and associated data. Integrating the LIS with the EDW enables analysis of laboratory data for research, quality improvement, and decision-making purposes.

Radiology Information System (RIS): The RIS handles radiology workflows, scheduling, and image management. It stores radiology reports, images, and related data. Integrating the RIS with the EDW allows for a holistic view of radiology data for analytics and reporting.

Pharmacy Information System (PIS): The PIS manages medication ordering, dispensing, administration, and inventory control. Integrating the PIS with the EDW provides insights into medication usage patterns, adverse drug events, and pharmacy-related metrics.

Patient Registration and Scheduling Systems: These systems handle patient registration, appointment scheduling, and demographics. Integrating these systems with the EDW ensures comprehensive patient data availability and supports analytics related to patient flow and resource management.

Financial and Billing Systems: Financial and billing systems capture information related to patient billing, insurance claims, revenue, and expenses. Integrating these systems with the EDW allows for financial analysis, cost management, and revenue cycle optimization.

Human Resources Information System (HRIS): HRIS manages employee data, including staff profiles, training records, certifications, and performance evaluations. Integrating HRIS with the EDW can provide insights into workforce management, staffing patterns, and employee productivity.

Quality Management Systems: Quality management systems capture data related to quality metrics, patient safety incidents, adverse events, and compliance with regulatory standards. Integrating these systems with the EDW facilitates quality improvement initiatives and performance monitoring.

It’s important to note that the specific application systems used for an EDW in hospitals may vary depending on the organization’s size, infrastructure, and requirements.

Q.6 ) Identify 3 business questions which can be answered using the model? Write some simple joins which can answer the question (especially aggregate values)? Date Dimension, Course Dimension, Facility Dimension Student Attendance Fact Date Key (FK) Student Key (FK) Course Key (FK) Instructor Key (FK) Facility Key (FK) Attendance Count Student Dimension Instructor Dimension

Answer:-

Business Questions:

1. Which courses have the highest average attendance rate?
Join Query:
“`sql
SELECT c.CourseName, AVG(f.AttendanceCount) AS AvgAttendance
FROM StudentAttendanceFact f
JOIN CourseDimension c ON f.CourseKey = c.CourseKey
GROUP BY c.CourseName
ORDER BY AvgAttendance DESC
“`

2. How many students attended each course on a specific date?
Join Query:
“`sql
SELECT c.CourseName, f.AttendanceCount
FROM StudentAttendanceFact f
JOIN CourseDimension c ON f.CourseKey = c.CourseKey
JOIN DateDimension d ON f.DateKey = d.DateKey
WHERE d.Date = ‘2023-06-30’
“`

3. What is the overall attendance rate for each instructor?
Join Query:
“`sql
SELECT i.InstructorName, SUM(f.AttendanceCount) AS TotalAttendance
FROM StudentAttendanceFact f
JOIN InstructorDimension i ON f.InstructorKey = i.InstructorKey
GROUP BY i.InstructorName
ORDER BY TotalAttendance DESC
“`

Note: The given join queries assume that the dimension tables (CourseDimension, DateDimension, and InstructorDimension) contain the necessary information for joining with the fact table (StudentAttendanceFact). Adjustments may be required based on the actual structure and attributes of the tables.

---

For More Updates Join Our Channels :

---