Dataware Housing Question and Answers

Dataware Housing Question and Answers

---

( Suggestion :  keep refreshing the page for updated content & Search Questions Using Find )

---

Q1)The ABC fashion clothing and accessories brand founded in year 2005 having 3000 stores across 10 countries. They wanted to design enterprise data warehousing and business intelligence system so asked Wipro team to provide the high level solution. As part of the team, you need to design high level architecture for the below requirements given by the customer files End Test Implemat centralize DW and Data marts for Sales, Finance, and Inventory Reports and dashboards will be created for these 3 data marts. Provision for Ad-hoc reporting, analytics and pdf export Multiple source systems available: Magento DB, ERP systems, Salesforce, Enterprise apps CSV and other flat Implement master data management (MDM) Archive 10 years old data into storage Tools recommended: SQL Server/MYSQL DW, SSIS, SSRS, Power BI or TABLEAU Draw end to end DW/BI architecture and show appropriate technologies for each stage in architecture diagram Explain the below terms related to above requirement • Sources Staging area Extract, transform and loading Reporting /Visualization Provide the mapping of the given tools for each laver.

Ans:

---

---

Q.3 A)a) Consider a in three Time, Department and Region. The time dimension regions are North, South and Central: Title, dep company having stores across several locations and tracks sales and profit information the data is categorized is 2022, the department dimensions are Sales and Rentals, and the Region Central North South Total Rental Profit Sales Profit Total Profit 80000 85000 165000 235000 100000 55000 13500 45000 235000 100000 D265000 500000, i) By using above database write a query using ROLLUP to get the following output Time Region Department Profit 2022 Central Rental Sales 80000 85000 2022 2022 Central Central [NULL] 165000 2022 North Rental 100000 2022 North Sales 135000
B)b. With the help of example illustrate in detail the steps in Horizontal Partitioning of fact table.

Ans: A)To achieve the desired output using the ROLLUP operator in SQL, you can write a query like this:

SELECT
CASE
WHEN Time IS NULL THEN ‘Total’
ELSE Time
END AS Time,
CASE
WHEN Region IS NULL THEN ‘Total’
ELSE Region
END AS Region,
CASE
WHEN Department IS NULL THEN ‘Total’
ELSE Department
END AS Department,
SUM(Profit) AS Profit
FROM
TableName
GROUP BY
ROLLUP (Time, Region, Department)
HAVING
(Time IS NOT NULL OR Region IS NOT NULL OR Department IS NOT NULL)
ORDER BY
Time, Region, Department;

 

B)Horizontal partitioning, also known as sharding, is a database design technique used to improve performance and manageability of large fact tables in a data warehouse or a distributed database system. It involves dividing a large fact table into smaller, more manageable partitions based on a specific criterion. Each partition contains a subset of the data, making it easier to query and maintain.

Let’s illustrate the steps in horizontal partitioning of a fact table with an example:

Example Scenario: Suppose you are managing a data warehouse for an e-commerce company that stores sales data. Your fact table, named sales_fact, contains millions of records of individual sales transactions. To improve performance and manageability, you decide to horizontally partition this fact table based on the sales date.

Step 1: Determine the Partitioning Key The first step in horizontal partitioning is to identify a suitable partitioning key. In this case, we’ll use the sales_date column as the partitioning key since it’s a natural choice for time-based data.

Step 2: Define Partition Ranges Next, you need to define the partition ranges. You can do this based on time intervals (e.g., monthly or yearly) or any other criteria that make sense for your data and query patterns. For this example, let’s partition the data by year.

Step 3: Create Partitioned Tables Create separate tables for each partition range. In our case, you would create multiple tables, each containing data for a specific year. For example:

• sales_fact_2020
• sales_fact_2021
• sales_fact_2022

Each of these tables will hold data for sales transactions that occurred within their respective years.

Step 4: Load Data into Partitioned Tables Move the data from the original sales_fact table into the appropriate partitioned tables. You can use ETL (Extract, Transform, Load) processes or database features to achieve this. Data for sales transactions in 2020 goes to sales_fact_2020, data for 2021 goes to sales_fact_2021, and so on.

Step 5: Update Queries and Applications Any queries or applications that used to reference the original sales_fact table need to be updated to reference the appropriate partitioned table based on the query’s date range. This ensures that queries only access the relevant data, improving query performance.

Step 6: Maintenance and Backup Regularly maintain and optimize your partitioned tables. This may involve archiving old partitions, optimizing indexes, and ensuring that backup and recovery procedures are in place for each partition.

Step 7: Query Routing (Optional) In a distributed database system, you might need to implement query routing mechanisms to direct queries to the correct partitioned tables. This is essential for ensuring that queries are processed efficiently across multiple servers or nodes.

Step 8: Monitoring and Performance Tuning Continuously monitor the performance of your partitioned tables and adjust partitioning strategies if necessary. You may also need to rebalance data across partitions to ensure even distribution and optimize query performance.

Horizontal partitioning can significantly improve the performance and manageability of large fact tables, especially in scenarios where data is time-based or can be logically partitioned based on other criteria. However, it requires careful planning and ongoing maintenance to ensure that the system operates efficiently.

 

---

---

Q4)a) Suppose you are about to start your commute by road from your home to your workplace on a examples explain that how big data can help you in this situation to reach to your workplace safely on time? b. Given table displays the records of graduate students from different streams who has appeared agency for various posts at district level Record ID Gender 1 M Pass Science 2 F Pass Commerce 3 F Fail Arts 4 F Pass Arts 5 M Pass Science 6 F Pass Commerce 7 M Fail Science 8 M Pass Science 9 F Pass Science 10 M Fail Arts Build bitmap indices for the Gender, Entrance Exam status and Course Stream attributes of the given table. 1) Find out the records of the Males of science stream who has passed the Entrance Exam by performing appropriate operations.

Ans: a) How Big Data can help you during your commute to reach your workplace safely and on time:

1. Traffic Prediction and Routing: Big Data analytics can collect and process real-time traffic data from various sources such as GPS devices, traffic cameras, and mobile apps. By analyzing historical traffic patterns and current conditions, it can provide you with accurate traffic predictions. This information can help you choose the best route to avoid congestion and arrive at your workplace on time.
2. Weather Forecasting: Big Data can incorporate weather data into your commute planning. It can provide you with up-to-date weather forecasts and warnings, helping you prepare for adverse weather conditions. Knowing about rain, snow, or storms in advance can influence your route choice and departure time.
3. Public Transportation Data: For those using public transportation, Big Data can provide real-time updates on bus or train schedules, delays, and crowdedness. This information allows you to plan your commute effectively, reducing waiting times and ensuring you catch your transit connections.
4. Vehicle Maintenance: If you’re driving, Big Data can monitor the health of your vehicle through connected sensors. It can alert you to potential maintenance issues before they become serious problems, reducing the risk of breakdowns during your commute.
5. Accident Detection and Alerts: Big Data can process information from traffic cameras and accident reports to detect accidents or road closures along your route. It can then reroute you to avoid these incidents, ensuring you reach your workplace without unnecessary delays.
6. Personalized Recommendations: Big Data can learn from your commuting habits and preferences over time. It can suggest optimal departure times and routes tailored to your needs, taking into account your desired arrival time and traffic conditions.

b) Building Bitmap Indices:

To build bitmap indices for the Gender, Entrance Exam status, and Course Stream attributes, you can create three separate bitmaps where each row corresponds to a record in the table. Each column in these bitmaps represents a unique value for the respective attribute. A ‘1’ in a column indicates the presence of that attribute value for the corresponding record, while ‘0’ indicates absence.

Here’s how you can build the bitmaps:
1)Gender Bitmap:
Record ID M F
1 1 0
2 0 1
3 0 1
4 0 1
5 1 0
6 0 1
7 1 0
8 1 0
9 0 1
10 1 0

2)Entrance Exam Status Bitmap:
Record ID Pass Fail
1 1 0
2 1 0
3 0 1
4 1 0
5 1 0
6 1 0
7 0 1
8 1 0
9 1 0
10 0 1

3)Course Stream Bitmap:
Record ID Science Commerce Arts
1 1 0 0
2 0 1 0
3 0 0 1
4 0 0 1
5 1 0 0
6 0 1 0
7 1 0 0
8 1 0 0
9 1 0 0
10 0 0 1

To find out the records of Males from the Science stream who have passed the Entrance Exam, you can perform the following logical operations on the bitmaps:

• For Gender (Male): Select rows with ‘1’ in the ‘M’ column.
• For Course Stream (Science): Select rows with ‘1’ in the ‘Science’ column.
• For Entrance Exam (Pass): Select rows with ‘1’ in the ‘Pass’ column.

Perform a logical AND operation between these three resulting sets to get the desired records. In this case, you would retrieve the records with Record IDs 1, 5, and 8. These are the Male students from the Science stream who have passed the Entrance Exam.

---

---

Q5)A) Trip Tider the following two tables. “Trip Table” and “Borower Table Table- SL No. Tour Number Trip_Number Cost Trip Duration 111 455 1500 2 1214 455 2000 3 131 319 4000 204 4 141 319 2500 2 5 151 786 3000 3 Borrower Table SL No Borrower Number Status State Amount Date 1 23 Open Kerala 15000 2015-04-01 2 45 Close Karnataka 10000 2017-08-11 3 77 Pending Bihar 25000 2016-07-31 4 34 Approved Gujarat Telangana 98000 2016-06-25 97 Rejected Close 45000 2019-08-01 6 43 Haryana 75000 2019-12-09 7 12 Open Rajasthan 63000 2022-11-14 i) Required Output for Trip Table: 10-Related Cutting PARTITION BY clause to get output as below for both tables 14M 44-2025 ) 2323 S. No. Tour Number Trip Number Cost Avg Trip Cost 1500 1750 121 455 2000 1750 3 131 319 4000 3250 34 141 319 2500 3250 5 151 WO786 3000 ii) Required Output for Borrower Table: SL. No Borrower Number Status State Amount Date Row Num 1 97 Rejected Telangana 45000 2019-08-01 1 2 34 Approved Gujarat 98000 2016-06-25 1 3 77 Pending 4 12 Open 5 45 Close Rihar 25000 2016-07-31 Rajasthan 63000 2022-11-14 Kamataka 10000 2017-08-11 1 2 1 6 43 Close Haryana 75000 2019-12-09 2 7 23 Open Kerala 15000 2015-04-01 1

Ans:

a. To create a partition function in SQL that partitions a table into 12 monthly partitions based on a datetime column, you can use the following example:

sql
— Create a partition function
CREATE PARTITION FUNCTION MonthlyPartitionFunction(datetime)
AS RANGE RIGHT FOR VALUES (
‘2023-01-01’,
‘2023-02-01’,
‘2023-03-01’,
‘2023-04-01’,
‘2023-05-01’,
‘2023-06-01’,
‘2023-07-01’,
‘2023-08-01’,
‘2023-09-01’,
‘2023-10-01’,
‘2023-11-01’,
‘2023-12-01’
);

This partition function defines 12 ranges, each representing a month of the year. You can adjust the date values to match your specific year or date range.

b. To create a table that uses the above partition function for partitioning based on the `datecol` column, you can do the following:

sql
— Create a partition scheme
CREATE PARTITION SCHEME MonthlyPartitionScheme
AS PARTITION MonthlyPartitionFunction
TO ([PRIMARY], [Partition_Jan], [Partition_Feb], [Partition_Mar], [Partition_Apr], [Partition_May], [Partition_Jun], [Partition_Jul], [Partition_Aug], [Partition_Sep], [Partition_Oct], [Partition_Nov], [Partition_Dec]);

— Create the sales order table with partitioning
CREATE TABLE SalesOrder
(
OrderID INT PRIMARY KEY,
OrderDate DATETIME,
CustomerID INT,
— Other columns
)
ON MonthlyPartitionScheme (OrderDate);

In this example, we create a partition scheme called `MonthlyPartitionScheme` that uses the `MonthlyPartitionFunction` to determine how the data will be distributed across partitions. The `SalesOrder` table is created with the `OrderDate` column as the partitioning column, and it uses the `MonthlyPartitionScheme` for partitioning.

Now, the data will be automatically partitioned into 12 partitions, one for each month of the year, based on the values in the `OrderDate` column.

c. Integrating unstructured data with structured data in a data warehouse for a product-based company can be a good practice depending on the specific requirements and use cases. Here are some considerations:

*Advantages:*

1. *Holistic Insights:* Combining structured and unstructured data allows for a more comprehensive view of the business. For instance, sentiment analysis on customer reviews (unstructured) can provide insights into product performance.

2. *Enhanced Decision-Making:* It enables data-driven decision-making by providing a 360-degree view of the business. Structured sales data combined with unstructured social media data can help in understanding customer behavior.

3. *Competitive Advantage:* Utilizing unstructured data like customer feedback can lead to product improvements and innovations, giving a competitive edge.

4. *Data Enrichment:* Unstructured data can be used to enrich structured data. For example, adding metadata to product images or extracting key information from text documents.

*Challenges:*

1. *Data Quality and Integration:* Unstructured data is often messy and requires robust data cleansing and integration processes.

2. *Scalability:* Handling large volumes of unstructured data can be resource-intensive, requiring advanced storage and processing solutions.

3. *Privacy and Compliance:* Unstructured data may contain sensitive information, necessitating stringent data privacy and compliance measures.

4. *Analysis Complexity:* Analyzing unstructured data may require specialized tools and skills, such as natural language processing (NLP) for text data.

*Example:*

Consider a product-based company that sells consumer electronics. They can integrate structured sales data with unstructured data sources like customer reviews from social media, emails, and surveys. Analyzing sentiment in these reviews can provide insights into customer satisfaction and product quality. This information can guide product development and marketing strategies.

In conclusion, integrating unstructured data with structured data can be beneficial for a product-based company, but it requires careful planning, data management, and the right tools to extract meaningful insights while addressing the associated challenges.

---

---

---

For More Updates Join Our Channels :