An SQL Server data mart with all data (as a result of milestone #2) is provided to you; please use it as the starting point for this milestone. The data mart file is provided as a backup file (bak) and please restore it on your own SQL Server (also a detached database is provided if you prefer this way). Examine the data mart structure and records to get familiar with the metadata and data. First, create an OLAP cube based on the data mart using SSDT-BI, and then deploy and process the cube in SSAS. Make sure the cube can answer the queries in the next step. Take screenshots of cube design and deployment steps. Last, using SSMS to sign in to SSAS, and write MDX queries against the cube you created to answer the following questions (tasks). Write the query in SSMS directly and display the results in SSMS as well. 1) How many courses (sections) are taught by part time (adjunct) instructors, by each semester and department? What is the total actual enrollment (total of head counts) in all course sections by academic year? Break down the numbers by undergraduate and graduate. What is the total actual credit hours generated (for each class section, the credit hours generated = credit hour per section * number ofstudents in the sector, for example, IT 4713 hybrid in fall 2016 generates 33-3n all online IT course sections by academic year? Assuming all courses have 3 credit hours. What is the average class size for different level of classes (drill down to "Level2") by department? 2) 3) 4) 5) Who taught the most students (total of headcounts, or actual enrollment, in all sections, in all years in the data mart)? List the top 10 faculty members with their numbers, ordered by number of students (headcounts) Bonus 1 point What is the average withdraw rate (defined as:1 [actual enrollment [original enrollment for each course (across all semesters and all sections). List the top five high withdraw rate courses to alert the college. An SQL Server data mart with all data (as a result of milestone #2) is provided to you; please use it as the starting point for this milestone. The data mart file is provided as a backup file (bak) and please restore it on your own SQL Server (also a detached database is provided if you prefer this way). Examine the data mart structure and records to get familiar with the metadata and data. First, create an OLAP cube based on the data mart using SSDT-BI, and then deploy and process the cube in SSAS. Make sure the cube can answer the queries in the next step. Take screenshots of cube design and deployment steps. Last, using SSMS to sign in to SSAS, and write MDX queries against the cube you created to answer the following questions (tasks). Write the query in SSMS directly and display the results in SSMS as well. 1) How many courses (sections) are taught by part time (adjunct) instructors, by each semester and department? What is the total actual enrollment (total of head counts) in all course sections by academic year? Break down the numbers by undergraduate and graduate. What is the total actual credit hours generated (for each class section, the credit hours generated = credit hour per section * number ofstudents in the sector, for example, IT 4713 hybrid in fall 2016 generates 33-3n all online IT course sections by academic year? Assuming all courses have 3 credit hours. What is the average class size for different level of classes (drill down to "Level2") by department? 2) 3) 4) 5) Who taught the most students (total of headcounts, or actual enrollment, in all sections, in all years in the data mart)? List the top 10 faculty members with their numbers, ordered by number of students (headcounts) Bonus 1 point What is the average withdraw rate (defined as:1 [actual enrollment [original enrollment for each course (across all semesters and all sections). List the top five high withdraw rate courses to alert the college
