data warehousing homework help

Boost your journey with 24/7 access to skilled experts, offering unmatched data warehousing homework help

Trusted by 1.1 M+ Happy Students

4.4Trust Pilot

4.4Edu Reviewer

5App Review

4.8Student

4.2Sitejabber

Recently Asked data warehousing Questions

Expert help when you need it

Q1:PK DimDate DateKey FullDate EnglishDayName EnglishMonthName CalendarYear Quarter DayOfWeek IsWeekend int date time varchar(10) varchar(10) int int int boolean The following criteria will be used to grade the assignment: • Codes are run with detail comment, accurate, and answers are correct. ***********NOTE ********* Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!/nAssignment 3 - ETL for Simple Dimension Tables Summer 2023 It must be submitted through the "Assignment 3 - ETL for Simple Dimension Tables" in Assignment Submission Folder provided in the D2L. It is important that you specify your name in the SQL Server Integration Service DTSX file. The attachment name should follow the following convention: BUSA532_ASSIGNMENT3_FIRSTNAME_LASTNAME.DTSX. Overview: Now that we have learned to create dimensional model for enterprise data warehouse. The next phrase is ETL process that we are going to load our data from OLTP database to our OLAP database. In this assignment, your goal is to load data from DateKey CSV file into your Date dimension table in OLAP by using SSIS. Business scenario: Please install AdventureWorks database, read the AdventureWorks case study, and analyze the ERD OLTP schema before starting this assignment. The CEO of AdventureWorks wants a data warehouse to keep track of sales performance. The figure below shows the date dimension table of AdventureWorks's OLAP. Import CSV file from D2L, then create SSIS package solution that can perform ETL process to load data into your Date dimension table (10 points).See Answer
Q2:PK SalesPerformanceFact FK Sales Personkey FK OrderDateKey TotalTaxAmount TotalSubTotal HighestTaxAmountForOrder DimDate DateKey Full Date int datetime int int int int int SalesPersonDim SalesPersonKey BusinessEntityID Marital Status Gender SalesQuota int int varchar(25) varchar(25) int/nAssignment 4 - ETL for Simple Enterprise Data Warehouse Summer 2023 It must be submitted through the "Assignment 4 - ETL for Simple Enterprise Data Warehouse" in Assignment Submission Folder provided in the D2L. It is important that you specify your name in the SQL Server Integration Service DTSX file. The attachment name should follow the following convention: BUSA532_ASSIGNMENT4_FIRSTNAME_LASTNAME.DTSX. Overview: Now that we have learned to load data into Date dimension in assignment 3. In assignment 4, we are going to repeat the ETL process for other dimension and fact tables. In this assignment, you goal is to load data from OLTP database to create dimension and fact table using SSIS. Business scenario: Please install AdventureWorks database, read the AdventureWorks case study, and analyze the ERD OLTP schema before starting this assignment. The CEO of Adventure Work Inc. wants a data warehouse to keep track of sales' performance. The figure below shows tables of Adventure Work Inc.'s OLAP. Three fact metrics that he wants to have for his data warehouse: TotalTaxAmount, TotalSubTotal, and HighestTaxAmountForOrder. Create SSIS package solution that can perform ETL process to load data into OLAP database (10 points). Please note that Sales PersonDim should handle Slowly Changing Dimension issues. The following criteria will be used to grade the assignment: • Codes are run with detail comment, accurate, and answers are correct. *** NOTE Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!See Answer
Q3:Q1. Consider a relation s(A,B) with the following characteristics: • s has 700,000 tuples with 70 tuples per disk block • s has an index on attribute A with index height x=2 (i.e., the index has nodes on 3 levels) • The values that the attribute A takes in relations are integers which are uniformly distributed in the range 1 ... 200. [Uniformly distributed there are roughly 3500 tuples for each A value.] (1.a) Assuming that the index on A is a secondary index, estimate the number of disk block accesses needed to compute the query GA-18(S). (1.b) What would be the estimated cost if the index were a clustering index?See Answer
Q4:Q2. Give the optimal estimate for the disk-IO cost of r JOINA-B S using (2.a) Sort-merge join, assuming that the two relations are sorted on the join attributes and r's join attribute is a key for r. (2.b) Block nested loop join Assume r has 2,000 tuples with 20 tuples per block; s has 4,000 tuples with 4 tuples per block; and the main memory buffer for this operation is 22 blocks.See Answer
Q5:Q3. Compute the cost of r JOINA-B S using the method of index loops with an index of height 2 on B of s. (So the index has nodes on 3 levels.) Do the computation for the clustering index case. Assume that r occupies 4,000 blocks with 20 tuples per block, s occupies 10,000 blocks with 5 tuples per block, and the amount of main memory available is 402 blocks. Assume that at most 5 tuples in s match each tuple in r. 04 Supporo wo have the following relations: P(ARC) and S(DPF)See Answer
Q6:Q4. Suppose we have the following relations: R(A,B,C) and S(D,E,F). Assume that R uses 500 blocks on disk, and S uses 6,000 blocks. Assume that you got the best possible hash function and there are no overflows for the hash. Assuming that there are 102 blocks of memory space for query processing, what is the least cost (in terms of disk block IOS) for partition hash join for the query R JOINC=D S?See Answer
Q7:Q5. Consider a relation s(A,B) having 700,000 tuples such that 70 tuples fit into one disk block. Suppose you can use 7 disk blocks in the buffer for sorting. Give an estimate of the number of block IOs needed for external merge sort on s.See Answer
Q8:Q6. Consider the following query Q over R(A,B,C) and S(D,E,F): SELECT A,F FROM R, S WHERE C-D AND A < 1000 AND E='e' Assume there is a clustering index on E of S, an index on D of S, an index on C of R, and a clustering index on A of R. Assume the indices have equal height. Assume R and S have roughly equal sizes (in both number of blocks and number of records), and assume that OE='e (S) is twice the size of OA<1000(R). 6.a Draw the canonical query tree for the query Q. 6.b Transform the canonical query tree for Q into a final query tree that is efficient to execute. 6.c Describe the best query evaluation plan (with minimal cost) for Q for the information given. 6.d Draw another left-deep query tree whose cost is higher than the one given in 6.c. 6.e Give one example instance of relational algebra rewriting, for each rewriting rule that was used in the trees of 6.a and 6.b.See Answer
Q9:Q7. Consider a database containing two relations: R(A,B,C) and S(D,E,F). For each of three (your choice) of the 12 rewriting rules for relational algebra expressions, give an example pair of relational algebra expressions involving R and S such that the rewriting rule can be used to rewrite one expression to the other expression, and give another example expression pair to show the rewriting rule cannot be used since some of the conditions for the rule are not satisfied.See Answer
Q10: Summer 2023 It is important that you specify your name in the assignment document/attachment: Your submitted files should contain one SQL file for part 1, and a DTSX file part 2. If you use Flat File or Excel Data Source for Part 2, please include the file also. It is important that you specify your name in the assignment document/attachment: The attachment name should follow the following convention for Part I: BUSA532 MIDTERM FIRSTNAME LASTNAME PART1.SQL The attachment name should follow the following convention for Part II: BUSA532_MIDTERM_FIRSTNAME_LASTNAME PART2.DTSX PART I: Use Adventure Work OLTP logical schema to build data warehouse tables in SQL: CEO of Adventure Work Inc. wants to keep track of performance of their stores. Particularly, he wants to know the total number of products produced quarterly and total number of products sold quarterly. In addition, he wants to know yearly net profit margin (calculated by using product list price divided by product standard cost). Provide 4-step dimensional model in the SQL comment section (4 points) and create data warehouse solution in SQL for the dimensional model that you propose (26 points). PART II: Use Adventure Work OLTP's Production and Sales schema to build a data warehouse consisted of two data marts. (You can find instructions to install the database in Topic 2 if you haven't installed yet). (70 points). Your database should meet these requirements: DimDate table has DateKey values ranging from 20100304 to 20140602 (YYYYMMDD integer data type). If you use Flat File or Excel Data Source to import date key data, include the data source file in the submission also (5 points). DimDate table should have IsMartin LutherKing (BOOLEAN data type), Week Number Of Year (INT data type), DayNumberOf Year (INT data type), and Quarter (INT data type) attributes (15 points). - DimProduct should contain product ID, product name, category name, discontinued, and Current Value (10 points). Dim Product should be able to handle Type 2 SCD issues (5 points). FactlessProductSelling Table has 2 foreign keys - SellStartDateKey and ProductKey (10 points). SalesPerformance has 2 foreign keys – DateKey and ProductKey (10 points). Correct ETL process to create 3 fact measures for SalesPerformance (15 points) The following table and graph show you the data description of fact measures and ERD of the data warehouse: Fact Measure TotalProductOrdered TotalProductSpecialOffer MostProductOrdered Indicator Data Description Total number of products ordered each day Total amount of product special offer each day Display Boolean value if the product was ordered the most FactlessProductSelling Table FK SellStartDateKey FK ProductKey PK DateKey DimDate int IsMartinLutherKing boolean WeekNumberOfYear int DimProduct PK int ProductKey int FK DateKey DayNumberOfYwar int Quarter int FK Product Key SalesOrderID ProductID int int ProductName varchar(25) CategoryName varchar(25) Discontinued boolean CurrentValue varchar(25) SalesPerformance TotalProduct Ordered int int int int TotalProductSpecial Offer money MostProductOrderedIndicator boolean The following criteria will be used to grade the midterm: • Codes are detailed, accurate, and SSIS solution package are correct. ***NOTE Please feel free to consult your instructor by email or phone, if you have questions or need assistance!!See Answer
Q11:Assignment 5 - ETL for Periodic Snapshot Enterprise Data Warehouse Your goal is to load data from OLTP database to create dimension and periodic snapshot fact table using SSIS. Business scenario: Please install Adventure Works database, read the AdventureWorks case study, and analyze the ERD OLTP schema before starting this assignment. The CEO of Adventure Work Inc. wants a data warehouse to keep track of monthly sales' performance. The figure below shows tables of Adventure Work Inc.'s OLAP. Three fact metrics that he wants to have for his data warehouse: Monthly TotalTaxAmount, Monthly TotalOrders, and MonthlyTotalProductSold. Create SSIS package solution that can perform ETL process to load data into OLAP database (10 points). Please note that DimCustomer does not handle Slowly Changing Dimension issues. DimMonth FactSnapshotTournament PK MonthKey int FK MonthKey int EnglishMonthName varchar(10) FK CustomerKey int CalendarYear int MonthlyTotalOrders int Quarter int MonthlyTotalTaxAmount int MonthlyTotalProductSold int DimCustomer PK CustomerKey int PersonType varchar(25) CustomerFirstName varchar(25) CustomerLastName varchar(25) The following criteria will be used to grade the assignment: Codes are run with detail comment, accurate, and answers are correct. Need to submit Source code file and output Screenshots of final answers.See Answer
Q12:Assignment 6 - ETL for Accumulating Snapshot Enterprise Data Warehouse Summer 2023 It must be submitted through the "Assignment 6 - ETL for Accumulating Snapshot Enterprise Data Warehouse" in Assignment Submission Folder in the D2L. It is important that you specify your name in the SQL Server Integration Service DTSX file. The attachment name should follow the following convention: BUSA532_ASSIGNMENT6_FIRSTNAME_LASTNAME.DTSX. Overview: We have learned to load data into periodic snapshot fact table in assignment 5. In assignment 6, your goal is to load data from OLTP database to create dimension and accumulating snapshot fact table using SSIS. Business scenario: Please install AdventureWorks database, read the AdventureWorks case study, and analyze the ERD OLTP schema before starting this assignment. The CEO of Adventure Work Inc. wants a data warehouse to keep track of order's lifecycle. The figure below shows tables of Adventure Work Inc.'s OLAP. Three fact metrics that he wants to have for his data warehouse: TotalOrderQuantity, TotalOrderDueQuantity, and TotalOrderShipQuantity. Create SSIS package solution that can perform ETL process to load data into OLAP database (10 points). Please note that DimCustomer does not need to handle Slowly Changing Dimension issues. DimDate PK DateKey int FullDate date AccumulatingFactOrderPerformance PK FactKey int DimCustomer FK OrderDateKey int PK CustomerKey int - FK DueDateKey int CustomerFirstName varchar(25) FK Ship DateKey int CustomerLastName varchar(25) FK CustomerKey int TotalOrdeQuantity int TotalOrderDueQuantity int TotalOrderShipQuantity int The following criteria will be used to grade the assignment: · Codes are run with detail comment, accurate, and answers are correct. ** ** *NOTE Please feel free to consult your instructor by email or phone, if you have questions or need assistance !!See Answer

TutorBin Testimonials

I found TutorBin Data Warehousing homework help when I was struggling with complex concepts. Experts provided step-wise explanations and examples to help me understand concepts clearly.

Rick Jordon

TutorBin experts resolve your doubts without making you wait for long. Their experts are responsive & available 24/7 whenever you need Data Warehousing subject guidance.

Andrea Jacobs

I trust TutorBin for assisting me in completing Data Warehousing assignments with quality and 100% accuracy. Experts are polite, listen to my problems, and have extensive experience in their domain.

Lilian King

I got my Data Warehousing homework done on time. My assignment is proofread and edited by professionals. Got zero plagiarism as experts developed my assignment from scratch. Feel relieved and super excited.

Joey Dip

Popular Subjects for data warehousing

You can get the best rated step-by-step problem explanations from 65000+ expert tutors by ordering TutorBin data warehousing homework help.

TutorBin helping students around the globe

TutorBin believes that distance should never be a barrier to learning. Over 500000+ orders and 100000+ happy customers explain TutorBin has become the name that keeps learning fun in the UK, USA, Canada, Australia, Singapore, and UAE.