- A food delivery company (like Zomato or Swiggy) wants to analyze order performance across various cities, restaurants, cuisines, time periods, and customer segments. Draw star schema for the same.
- A large retail chain wants to analyze sales performance across various stores, product categories, and time periods. Draw star schema for the same
- For a super market chain, consider the dimensions namely product, store, time, promotion. The schema contains the three facts namely units_sales, dollar_sales and cost_dollars. Design a star schema and calculate the maximum number of base fact table records for the values given below: Time period: 5 years, Stores: 300 reporting daily sales, Product: 40000 products in each store( about 4000 sell daily in each store. Promotion: a sold item may be in only one promotion in a store on a given day.
4. Consider a multinational organization dealing in apparels, cosmetics and jewellery. It has got branches at several locations. Consider 4 branches. Draw a cube to represent this information. Consider time as one of the dimensions. The CEO would like to study about the sales across various branches and segments.
- Information of sales of cosmetics across all the branches for all the months
- Information of sales of cosmetics and clothing for the month of july
- Information of sales for the year 2023
Apply appropriate OLAP operation and redraw the cube.
5. Consider a multinational technology company dealing in software, hardware, and consulting services. It has branches in multiple countries. Consider 5 branches. Represent this information using a cube. Consider time as one of the dimensions. The CEO would like to study the revenue generated across various branches and product categories.
- Information of revenue generated from software across all the branches for all quarters of the year
- Information of revenue generated from software and hardware for the second quarter
Information of total revenue generated for the year 2023 Apply appropriate OLAP operations and redraw the cube.
6. Consider a global e-commerce company that deals in electronics, fashion, and home appliances. The company operates in 5 major regions: North America, South America, Europe, Asia, and Australia. Time is recorded in years and quarters. The CFO wants to analyze profit margins across regions and product categories.
Frame the following using a data cube and apply appropriate OLAP operations:
- Information about profit margins from fashion category across all regions for each quarter of the year.
- Information about profit margins from electronics and home appliances for Q1 and Q2.
Information about total profit margin for the year 2022
7. A manufacturing company has a huge sales network. To control the sales, it is divided in regions. Each region has multiple zones. Each zone has different cities. Each sales person is allocated different cities. The objective is to track sales figure at different granularity levels of region and also to count number of products sold. Products are categorized as high end and low end products. Draw star schema for the same
8. Construct the data warehouse dimensional model(Snow flake) for a wholesale furniture Company. The data warehouse analyst has to analyze the company’s situation at least with respect to the Furniture, Customer and Time. Moreover, the company needs to analyze furniture with respect to its type, category, and material, the customer with respect to their location, by considering at least cities, regions and states. The company is interested in learning the quantity, income and discount of its sales
9. The college wants to record the Marks for the courses completed by students using the dimensions: I) Course, II)Student, III) Time & a measure “Aggregate marks”. Create a cube and perform following OLAP operations: (1) Roll-up (2) Drill-down (3) Slice (4) Dice
10. Store dimension consists of 250 stores, Time dimension consists of 1825 records and product dimension consists of 2000 records, the metric of fact table is total sales. Calculate the total number of records in the fact table