CIS 611 Enterprise Database Systems and Data Warehouse with OLAP(3-0-3) |
||
| Course Content |
|
|
|
Dec 3, 2025: Research Paper Presentations: ACM SIGMOD Research Paper Presentation Schedule Final Exam Scheduled on: Check the Final Exam Schedule for the CIS611 Class Time Below University Final Exam Schedule Final Exam Info: See the Links for the Final Subjects ONLY here !! (All the links to the rest of the lecture notes are disabled) The Topics to Focus on for the Final Multi-Dimensional DW Schema/Model, DW Star Scheme Implementation, Multi-Dimenstional CUBE, CUBE Generation/Implementation, Analysis of Cube, OLAP (Cube) Operators 1-2 Basic MDX (Multi-Dimensional Query) Using Cube Operators Bitmap index, Inverted Index Cost Analysis and Comparison of Join Evaluation with B+ Tree vs Hash Index Cost Analysis (Calculation) of Join Algorithms - Index Nested Loop Join, Block Nexted Loop Join, Sort Merge Join, Hash join Comparison of Block Nexted Loop Join, Sort Merge Join, Hash join for the Given Different Buffer Memory Sizes Total Query Processing Cost in a Query Operation Tree and Optimization Index Strategies for Optimization of Multi-Dimensional Query Processing over DW Star Schema Query Processing Optimization for Columnar Store, Column Store vs Row Store for Query Processing If Time Allowed to Cover, RDBMS Concurrency Control with implementation of ACID, Transaction with Multiple Concurrent Users, Problems in Isolation Level The question patterns would be similar to those of the midterm, problem solving with simple examples. Many of them require calculation of the cost analysis of the algorithm of an operation for a given table or a simple SQL query execution. Hand Written One Regular Page Note (Front and Back) is Allowed to the Final Printed Copy and Pasted Lecture Notes Are Not Allowed in the One Page Note 16. August 25, 2025: For All the Midterm Information: See the Links for the Midterm Subjects ONLY here !! (All the links to the rest of the lecture notes are disabled) Closed Book and One Page Note is NOT Allowed for the Midterm The Midterm Will Be at 4:00PM (Instead of 4:30PM) - 5:45PM to Allow More Time. Subjects to Focus on for the Midterm of CIS611: RDBMS Architecture, SQL Server Query Processing Mechanism in Phases, Relational Algebra, Per Given SQL, Build SQL Query Execution Steps in SQL Operators Defined by Relational Algebra View/Inline View, View Processing/Implementation Mechanisms Dynamic External Hash Index Algorithms, B+ Tree Index Insertion/Deletion Algorithm, Size of B+ Tree Clustered or Unclustered B+ Tree: Given a Data Table and Max Fill Factor, Analysis of B+ Tree Size in Blocks to Determine the Level of the B+ Tree and Total B+ Size Cost Analysis (Calculation) of External Sort Algorithms for Optimization If Time allowed to Cover, Cost Analysis and Comparison of Join Evaluation with B+ Tree vs Hash Index The Midterm/Final Exam Question Info: 5-6 Small Problem Solving Questions for Short Answers. No T/F Questions, No Multiple Choice Questions For Example, Given a Data Analytic Question in Text or a SQL Query, Build a Query Execution Operator Steps 15. August 25, 2025: There Will be Random Quizzes to Check the Attendance ! Only 5 - 10 Mins Will Be Allowed for Each Quiz. Those Who Come in Class Late After 5 Mins, They Will NOT Be Given a Quiz ! 14. August 25, 2025: The Lab Submission Link and the Deadline of Each Lab Will Be Created on the Class BlackBoard ! Wait For the Lab Submission Links Are Created on Blackboard for Each Lab 12. August 25, 2025 Only the registered students can access the course blackboard. If you have a problem with your blackboard access, please contact the registrar or CSU Tech Support to resolve the issue ! Center-for-elearning Technical Support Faculty does not control your registration and the course blackboard access in the CSU systems. 11. August 25, 2025 TA Info: TA: Email: 2886995@vikes.csuohio.edu Office Hours: Mon, Wed 2:00 PM - 4:00 PM Send email to TA ahead to set up a time slot to let TA know that you are coming Location: Big Data Lab: FH 305 or a ZOOM Meeting ZOOM Meeting ID: 305 141 9324 Passcode:4nh4RS Zoom Link Send Email to TA ahead to let TA know when you are coming or set up an ZOOM meeting appointment in another time. If you have questions in Labs or Grading your Labs, Send an email to TA or Talk to him During his TA Office Hours or Schedule a Zoom meeting with him Dr. Chung's Office Hours: Tues and Thursday 1:30PM - 3:30PM Office: FH 222 or Zoom Meeting Email: s.chung@csuohio.edu Send Me Email to Set Up a Meeting or a Zoom Meeting. Meeting ID: 859 3867 6332 Zoom Meeting Link 9. August 25, 2025: For the Most Updated Installation Guides of 2022 MS SQL Server Package in detail, see the Lab Section of CIS430/530 ! CIS430/530 Site for Installation Guides of 2022 MS SQL Server Package Read the Installation Guides FIRST before starting downloading ! See the Lab section for More details ! 7. August 25, 2025 Lab Submission: The Lab Submission Link and the Deadline of Each Lab Will Be Posted on the Class BlackBoard ! Wait For the Lab Submission Links Are Created on Blackboard for Each Lab The Output of each lab is your report in Doc file that shows your screen captures of your data processing steps for analytics and the results Each of your screen capture must show each step and the result returned by the server in the SAME window in your System to prove that your lab is done correctly in YOUR SYSTEM !! 1. Submit your Lab in zip file including 1) your lab report in .doc and 2) all the source files, preprocessed input files, outputs on Blackboard for a timestamp as a proof. 2. If You did an Extra Credit Part, Mention about What Part is Done for Extra Credit at the Heading of the Front Page of Your Report in Bigger and Bold Font ! 6. August 25, 2025: For the installation guides of MS SQL Server in detail, see the Lab Section ! Read the Installation Guides FIRST before starting downloading ! See the Lab section for More details ! 4. August 25, 2025: If you can't install MS SQL Server, Go to the Oracle site below to download and install Oracle database server 11g Standard. http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html Refer Online Oracle documents Oracle9i/Database Installation Guide 3. August 25, 2025: Lab Assignment 0 : Prep for Lab Assignments Download and Install 1) Visual Studio, 2) 2022 MS SQL Sever Package (Developers Version), and 3) its Client - MS SQL Management Studio (SSMS) and Install on Your Laptop For the most recent updated Installation Instructions: For any questions on this site or creating your account in this site, send email to TA or Jackie ! See Lab Section for Instructions to to Download SQL Server and Visual Studio on Azure Potal Site SQL Server Installation Guide: See Lab Section for the Detail :br> See Lab0 Section for Instructions to Download and Install SQL Server and Visual Studio on Azure Potal Site SQL Server Installation Guide: See Lab Section for the Detail :br> 1.Download and Installation of MS SQL SERVER Package : Read Instructions Carefully Below ! Any SQL Server Developer Version (with Service Packs recommended if available) Make Sure to Install a Matching Version of Visual Studio first before installing SQL Server (Please see installation Guide in the Lab section for the detail) Read the installation Guides in the Lab0 Section FIRST before Starting to download ! 2. After everything is set up correctly, Run SQL Server Management Studio - a Client for your SQL Server (It is Under Microsoft SQL Server of your Start menu of your Window) to Play with Your SQL Server 3. If your SQL Server is not running (You don't see the green arrow on your server name on the Top Left corner of your SQL Server Management Studio), Start the server manually in your SERVICE list - Search with "Service" to find the list. (Read the General Installation Instuctions for this in the Lab0 Section !) 2. August 25, 2025: For New Students, You Have to Create Your Account in Azure Potal site to Download How to Create Your Account to Login the Azure Potal Site to Download MS SQL Server 1. Go to www.portal.azure.com Azure Potal Site The students MUST use a vikes.csuohio.edu or csuohio.edu email address! Note that the students should use CSUID@vikes.csuohio.edu, not f.lastname@vikes.csuohio.edu. So, for example, 2345678@vikes.csuohio.edu, rather than m.jones@vikes.csuohio.edu. This is where your students can download their software. 1. Please clear the cache, cookies and browsing history. Then using Internet Explorer or Microsoft Edge, please enable the In-private browsing by pressing CTRL + Shift + P on the Keyboard or, for Chrome CTRL + Shift + N. 2. Go to https://azureforeducation.microsoft.com/devtools This site uses cookies for analytics, personalized content and ads. By continuing to browse this site, you agree to this use. Learn moreat azureforeducation.microsoft.com then click the blue sign in button. 3. Enter your email address then hit Next. 4. Enter the password then Sign In. 5. Once signed in, it should take you to the Student Verification page. Please enter your school email address twice and a verification link will be sent to your email. 6. Copy and paste the link to a new tab then hit continue. 7. Please agree to the Terms and Conditions. After completing the above mentioned steps, you should be able to access the Azure portal. Send an email to TA or Dr. Jackie Woldering (j.woldering@csuohio.edu) for any questions 1. August 25, 2025 The class webpage can be reached from blackboard as well: Class Webpage Semester Schedule: See University's Official Academic Calendar for the Semester and the Final Exam Schedules Final Exam schedule for Fall 2025: Monday Dec 8 at 4:00PM - 6:00PM Final Exam Info: See the Links for the Final Subjects ONLY here !! (All the links to the rest of the lecture notes are disabled) For Midterm Info: See the Links for the Midterm Subjects ONLY here !! (All the links to the rest of the lecture notes are disabled) |
|
For Most Updated Guides and Information to Install 2022 MS SQL Server Package, Please read on CIS430/530 Lab Section ! CIS430/530 Lab Section Lab0 : Read all the instruction Guides below FIRST ! New Changed Installation Guides As of Summer 2023: Step1: Create an Account with CSU ID in the Azure Portal at Azure Potal Site Step2: Login Azure Portal then Education Tab => Software to Download and Install Visual Studio 2022 Step3: Download a Server Package for 2022 MS SQL Server + Analysis Service + Integration Service Directly from Microsoft Downloading Site MS SQL Server 2022 Developer Edition has all the feature as Enterprise Edition has but it must be used for non-production environment. Where to Go, What/How to Download, and How to Install a SQL Server 2022 Developer Version with Analysis Service and Integration Service: SSDT (SQL Server Data Tool): Read Carefully the Detail Instructions Below FIRST !! Note that Visual Studio 2022 Needs to be Installed First Before Installing 2022 SQL Server. Note that Visual Studio 2019 Needs to be Installed First Before Installing 2019 SQL Server. For CIS611 Lab3 and Final Project Later, Install ALL with Machine Learning Features as below: 2022 Server Package: Note that You need to Choose Multidimensional and Data Mining Mode instead of Tabular Mode in Step 13 below for CIS611 Lab3 and Project Later. How to Restore a Sample DW AdventureWork Github site for Sample DW AdventureWork Data and Restoring Script Instructions for 2019 Servers: After Install All the Servers and Client then Import a Sample Data Warehouse Named "Adventure Work DW 2019" or Adventure Work DW 2022 For the Instruction on How to Import a Sample DW 2019 AdventureWork, See at the end of the 2019 SQL Server Instruction below: As of 2020, Only the SQL Server 2019 Developer Edition is Avaliable to Download Free at ! (You Can No Longer Choose 2017 SQL Server Anymore !) Nothe that 2019 SQL Server Stand alone ONLY WITHOUT Analysis Service and Integartion Service ! You Can Download 2019 SQL Server Only Developer Package Either From: 1. From Azure Potal Site , Choose(or Search) Education with Your Azure Account Credential Created with Your CSU ID and PW: Azure Potal => Education Menu Site Or 2. Download MS SQL Server Package with Analysis Service (For DW OLAP Server) and Integration Service (Data Mining Server with ML) Directly from at: Microsoft Downloading Site 2019 SQL Server Package (Not Available in the Microsoft site anymore): Note that You need to Choose MultiDimensional and Data Mining Mode instead of Tabular Mode in Step 13 below for CIS611 Lab3 and Project Later. Installing SQL server with Analysis Service in Tabubar Mode is Only for SQL Query Purpose After Download, How to Install SQL Server 2019 Developer/Enterprise Edition (with Analysis Service and Integration Service in SSDT(SQL Server Data Tool)) Note that Mixed Mode was chosen in this Guide. Instead, You can Choose Window Authentification Note that You need to Choose MultiDimensional and Data Mining Mode in Step 19 for CIS611 Lab3 and Project Later. Download and Installation Guides: Where to Go to Download a 2019 SQL Server Stand ALONE: 1. From MS AZURE Site => Education menu: Azure Potal Site Or 2. Microsoft Download site for 2022 SQL Server package with Analysis Service, Integration Service all together as a Package: General Installation Guides for SQL Server:Read First before Starting Installation ! General Things to Know for SQL Server Installation: Any SQL Server Standard/Developers/Enterprise Version recommended. No Express Version ! 1. Make Sure to Install a Matching Version of Visual Studio first before installing SQL Server Note that Visual Studio 2019 Needs to be Installed First Before Installing 2019 SQL Server. Read the installation Guides in the Lab0 Section FIRST before Starting to download ! 2. After everything is set up correctly, Run SQL Server Management Studio - a Client for your SQL Server (It is Under Microsoft SQL Server of your Start menu of your Window) to Play with Your SQL Server 3. If your SQL Server is not running (You don't see the green arrow on your server name on top left corner of your SQL Server Management Studio), Start the server manually in your SERVICE list - Search with "Service" to find the list. (Read the General Installation Instuctions for this in the Lab0 Section !) See 2019 SQL Server Installation Guides for Installation of The Client Software - MS SQL Server Management Studio (SSMS) Together. It will be installed together at the end of installation of 2019 SQL Server. How to Use the MS SQL Client - MS Sql Server Management Studio (SSMS) to Connect to Your SQL Server: How to Use SQL Server Client - MS Sql Server Management Studio (SSMS) NEW POST ! Guide for How To Use a Client - SSMS If You Need to Download the Client Software SSMS Alone Seperately from the MS Download site, then Install SSMS after Installing a SQL Server. (See the new instruction for 2019 SQL Server Installation Above) ! Download and Install the Client: SQL Server Management Studio (SSMS) Download SSMS If you want to use MS SQL Server with Naive Client - SQLCMD (Command Line Client): For NEW Mac AFTER 2021: How to Install MS SQL Server on Mac: Instructions for the NEW Version of MAC OS AFTER 2021: The new M1/M2 chips or Apple Chip in the recent MAC does not support any VM installlation for MS SQL Server since it has issues with VM. The best way is to install Docker as it is free for individual and easy to configure. Please see the Instruction for Docker below. Newly Changed Docker Installation Procedure for MAC: How to Install 2022 MS SQL Server on Docker and Client Software (Azure Data Tool) on New Mac OS (By TA Dharmik Rajesh Kurlawala) ******************** IMPORATANT NOTE !! DO NOT COPY and Paste blindly the set up command from the Instruction !! You need run that one docker command with YOUR OWN USER ID and Password !!!!! The "PASSWORD = YOU CAN ENTER YOUR OWN" and "MSSQL_USER = ... " lines are just there to let you know that you can substitute your own values for the PASSWORD and MSSQL_USER environment variables if you wish. Those are the credentials you'll use when connecting to the database. IMPORTANT NOTES !! Those Docker instructions work on Linux. However, copy-pasting from the PDF might add some line breaks to your command, so I'd suggest you copy-paste the following version into a terminal: docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD=MyPassword123#" -e "MSSQL_PID=Developer" -e "MSSQL_USER=SA" -p 1433:1433 -d --name=sql mcr.microsoft.com/azure-sql-edge For OLD Mac BEFORE 2021, Follow instructions to download and install directly to your Mac OS How to insall SQL Server on a New Mac Or You will need to download VM and install Window10 on the VM. Within the Window VM, you can download and set up SQL Server exactly the same as the regular set up instructions. Install VM(Virtual Machine) and Window 10 on Mac and then Install SQL Server on the Window VM: How to Install SQL Server on Mac: 1. Install Mac OS X Host VM(Virtual Machine) from Oracle VM Oracle VM Download 2. Install Window 10 OS on the VM on your Mac then 3. Install MS SQL Server on the Window VM. If you want to install a SQL Server on Linux, download and install PostgreSQL directly to your Linux system. PostgreSQL to download FQAs on Installation of SQL Server as of 2020 Q1: I am wondering if I can just download PostgreSQL alone directly on Linux, or if I also have to create a Virtual Machine and install a SQL Server on it like the Mac users do? A: Yes, you can download a PostgreSQL directly on Linux. Only MAC needs a VM to set up to provide Win OS for a MS SQL server because None of SQL servers is based on MAC OS. Q2:For Lab Outputs, Do I need the VM for a standardized SQL Server lab output as instructed on the clas webapge, or can I just download PostgreSQL by itself and use that instead? A: If you are using PostgreSQL, it’s Client is not SSMS, it has its own a Client interface, which will be downloaded and installed together with a POSTGreSQL server on Linux directly, where you can write SQL/DDL/DML to Submit for the labs. The lab output will be the screen captures of the PostgreSQL Client results. No, you don't need VM for this. Q3: I’ve created an account and database on Azure portal site since I am a Mac user. Microsoft does offer a tool to write SQL queries on Mac by connecting to Azure SQL database – Azure Data Studio. Can we use that for lab submissions instead of SSMS on virtual box? A: Yes. You can connect to Azure directly from MAC witout VM with the client tool. Q4:I realized that my system username and my Laptop name were different than my name after I installed SQL server and SSMS (SQL Server Management Studio). So I finally changed the laptop name (and username) to my name and reinstalled the SQL server and SSMS management studio. I have been trying to uninstall the server and then install it again. But it is not connecting to the server, it's showing SQL Server Connection errors saying there is no SQL Server by the name. A: I never recommended to change your computer name after you installed SQL Server and SSMS. Do Not Change Your Host Name after You Installed SQL Server and SSMS ! Your SQL server was already named after your old computer name at the installation time, so as SSMS. If you changed your computer name after installing SQL server, you are calling troubles You have to change your SQL Server name to your new computer name and propagate it into SSMS as well. If the issue still persists, the problem can be fixed by uninstalling SQL Server with other related services installed together and SSMS then freshly reinsatll SQL Server and SSMS to pick up the changed computer name as SQL Server instance name You will have to remove your SQL Server and all the other Services installed along together with the SQL Server and the SSMS through Add/Remove program in Window then reinstall all freshly again to pick up your new host name for a SQL Server instance name and propagate it to SSMS at the installation time. Most of the times when uninstallation is needed, students often uninstall the SQL Server only. But there are other services that gets installed along with the SQL server. You need to remove each of those services from Add/Remove Programs and functions in Window. That will allow the user to freshly reinstall the SQL server as a new instance. Azure Cloud What To Do If My Laptop Died In the Middle of Lab Due: Creating a Database Server Instance on AZURE Cloud: How to Apply an Azure Free Student Account to Create a Database Server in Azure Cloud Service Quick Tutorial: How to Set Up and Create a Database on Azure Cloud Quick Tutorial: How to Use SSMS to Connect to and Query Azure SQL Database or Azure SQL Managed Instance Other Azure Tutoral Sites: Quick Tutorial: How to Create a SQL Database on Azure Cloud Quick Tutorial: Getting Started : How to Set Up and Create a Database on Azure Cloud Tutorial: How to Create a SQL Database on Azure Cloud If you have any questions or help on the insatllation and set up, please send email to TA ! FAQs and Azure Query Editor Set Up Important Notes on Use of Azure Cloud Server See the CIS430 Lab Section for the detailed Instruction The Azure cloud server was suggested as the LAST resolution, it is not recommended as a primary system for this course. It was suggested as an emergency resolution ONLY when you don't have your own computer system to download, install and set up your own database server or when there is any accidental non-recoverable crash of your system. We don't control the MS Azure account at all, which means we cannot help you with the unusual issue on MS Azure student credit accounts or a trouble to obtain the free student credit account. That is something you have to resolve with Microsoft Azure team, it is NOT something we can help with. Azure Cloud DB Server is just a backup plan when you don't have any computer to download (from Education site of Azure portal site and Azure portal site is NOT Azure Cloud Service) and set up your own database server and a client for CIS430. 2GB storage is needed when you actually download the server physically in your laptop to install. You don't download and install the database server physically by yourself when you request to use an Azure Cloud database server since it is already installed and running on their Azure Cloud site. You only need to go to the site to use their client site or download their client: Query Editor to connect and login to your database server running on Azure Cloud site. So there are NO installation issues when you use a database server you requested on Azure Cloud Server. Don't wait for MS Azure site to fix your problem. Looks like MS changed $100 free student credit policy partially, instead, they provide $200 free credit to try their new COSMO DB on Azure Cloud. I do not recommend it in this course since Cosmo DB is for Semi-structured DB for Big Data. Please use your server you installed and set up on your laptop for CIS611. If you have any questions or help on the insatllation and set up, please send email to TA ! Prep for MS Analysis Service with OLAP Server for Lab3: If you want to install and set up ahead for Lab3, See Lab3 Section Below for the Detail Instructions ! Note that Visual Studio 2019 Needs to be Installed First Before Installing 2019 SQL Server. Where to Go and What/How to Download SQL Server 2019 Installer Note that You Have to Choose Multidimensional Mode instead of Tabular Mode when Installing SQL Server for Cube building and MDX Query Execution Later PrepLab 0: Prep for Lab1 for Those Who Have NOT Taken CIS430/530: Install and Set Up a SQL Server For a Fall Semester, Due By the End of the Weekend of the Second Week of the Fall Semester For a Summer Semester, Due By the End of the Weekend of the First Week of the Summer Semester For Summer CIS611, Part 1 and Only Part2_1 of PrepLab0 are Required ! For More Useful Guides for Installation of SQL Server and PrepLab0, See CIS430/530 Lab Section at: CIS 430/530 Lab Section For Part 1 on Creating/Populating a Database: Class Note_0: How to Create Correct Database Scheme from E-R Diagram Class Note_0: SQL Review :Chapter 4 (See the Script in Chapter 4 to Create the Company Database Scheme for PrepLab !!) For More Useful Guides, See CIS430/530 Lab2_2 Section at CIS 430/530 Lab Section: Scroll down to the Lab2_2 Section For View: Simple Examples of How to Create, Select, and Alter View Note you have to use Alter View in MS SQL instead of Replace View statement Good Examples of How to Create, Select, and Alter View For Part2_1: Class Note_15_1: Example of Stored Procedure Example of Creation and Execution of Stored Procedure and Cursor in MS SQL Syntax SQL Review: Notes for All the Lab Outputs: 1. Make Sure to Create a Database Named Company_YourName and Show the Execution in Screen capture. 2. Make Sure to Insert Yourself in the Employee Table with Your First and Last Name. You Can Make up Other Column Info. 3. Make Sure to Insert All 4 Departments in Department table. 4. Make Sure to Show Your Table Contents with Select * From your_table_name; (with the Screen capture of the Select Query Results) in your report right after Inserting all the data. Create Database named in the following rule: Company_3Characters of Your First Name Followed by 3 Characters of Your Last Name with Each First Letter in Capital For Example, Your Name is Sunnue Chung, Create Database Named: Company_SunChu To Name Your .sql Files, use only the first 4 characters of your first name followed by the first 4 characters of your last name with Each First Character in Uppercase. For Example, Your Name is Sunnie Chung, SunnChung_CreateTable.sql Notes for Lab Output Report: 1. Make Sure to Show Your SQL Code and the Executed Result by Your Server in the SAME Window in a Screen Capture ! 2. Make Sure to Show your Server Name in the SAME Window with Your Query and the Result ! 3. If Your Lab Report Does Not Show Enough Information about Your Server Name, Your Userid Folder Name, Your Sql File Names Are Related to Your Name, I Will Assume that You Are Hiding Someone Else's System Information for Cheating. How to Create CIS611 Lab Reports Important Instructions When Creating Your Lab Report: Screen Captures Should Show each Query and the Server Response together in a Window of Your SSMS and Your SQL Server Labs: The Lab Submission Link and the Deadline of Each Lab Will Be Posted on the Class BlackBoard ! Wait For the Lab Submission Links Are Created on Blackboard for Each Lab If You did an Extra Credit Part, Mention about What Part is Done for Extra Credit at the Front(Cover) Page of Your Report in Bigger and Bold Font ! Always Follow the Deadline of Each Lab Assigned on the Class Blackboard. The Deadlines mentioned on the Class Webpage Are Tentatively Scheduled at the Beginning of Each Semester. Please Identify Your Course When You Ask Me in Email ! Lab0 on Prediction on the Game Data for Motivation for OLAP Server: (Due By The End of the Third week Friday of the Semester) With the Given Table - Global Video Game Sales data (below) from the Data Warehouse of a Global Game Retail Company, Analyze the Game Sales Data (Without Using Any Database Server/Data Warehouse OLAP Server) to Predict the following: 1. Which Computer Game Genres, Platforms Will Be the Best to Focus to Invest/Develop for Next Year (in this data set next year is 2017) Globally and For Each Region. To be able to predict, you need to analyze sales data with followings 1. Import the data file to Excel to perform operation steps to obtain results of Group By with Aggregation to see total sales by Genres, Platforms respectively per each year during last 10 years. 2. Save the result of each result into a CSV file for any visualization tool or Excel to Visualize in Graph to Compare total sales of each Genres, Platform during last 10 years to find any useful intelligence to Predict 3. Report the facts found from your visualization of each analysis result and discuss why you predict that the specific Genres and Plaform are best to invest in for the next year. You may use Excel only for this Lab0 to see the required data processing steps to be done manually. You can choose any tool for analysis and visulization -- Even Excel should be enough for this task to analyze and visualize the analyzed results. Hint: One easy way to create analyzed data is that you can do all the calculation per each year and each genre , (each year and each platform as well), in SQL with Group by and Aggregation Functions in SQL server. To create a graph, you need each aggregated total per each year and each genre, (each year and each platform as well), saved in one file (this can be done by storing the SQL result in a table), then saved the table as an excel or csv file for a graph. SQL Review for Goup By with Aggregation: Lab Assignment 1: Lab Assignment 1 on Implementing in Query Execution Steps in a Relational Algebra to Build a Simple Query Processor The Part 3 is for Extra Credits of Part 2. Part 3 will Replace the Required Part 2 and It will be counted as Part 2 (70% of Lab1) plus the Extra 50% of the Part 2, so total 105% for Part 3 NOTE: For the Part 2 of Lab1, You are NOT Supposed to use any SQL to SQL Server to implement except for loading (reading) two base table Employee and Department tables as input and creating a new intermediate table as output of each step. More FAQs on Lab1: Q: We have a below note in this Lab on Simple Query Execution – Part2, For the Part 2 of Lab1, You are NOT Supposed to use any SQL to SQL Server to implement except for loading (reading) two base table Employee and Department tables as input But in the implementation of the steps we have: For each operator, create a new table with the given output table name specified in the execution steps to write the output of the operation. Can we write JDBC call to store(create) each new table generated by each operatot dynamically? A: Yes, you can create each intermediate output table in your database server using JDBC/ODBC calls dynamically. That is more realistic applications. Or alternatively, you can directly write to an intermediate output file in CSV per each step then read from the CSV file in the next step without creating intermediate tables in a database server then create a final output table in a database server to show the final result. Q: For Part 1, Do we need to insert all the new tuples and write SQL from Q1 - Q3? A: Yes, write SQL for each Q1 - Q3 and show the Execution steps of each SQL Lab Assignment 2 on Building an Inverted Index For this Lab, you can assume that Phase 1 is done by Big data processing. The output of Phase 1 is given below in .csv file. For Part 1, You can use (modify) any "word Count" Program (Various versions of Word Count program for text processing are available on line). You can use any program/script language for Lab2. You might need to write Store Procedures as needed. You don't need to use Table Function if you create a Table in SQL Server in your program. The input file of Lab2: UnionAdressTable.csv was generated by big data (web data) processing (Lab1 of CIS593 Big data) to extract information from the website with the collection of the US Preseident State Union Addresses at: Infoplease site of Collection of US Preseident State Union Addresses FAQs: Q: Are We supposed to scrap the webpage data from the infoplease website given in lab2 document and save the extracted data in csv file right? A: No, Lab2 is not supposed to process the raw data webpages. It assumes that webpage processing has been done in the Big Data processing team ahead to extract all the info from each web site to the CSV file. The given csv file is the input for Lab2 from which the inverted index needs to be built from the texts of the input file. Stored Procedure, Table Function Review for Lab1: Lab Assignment 3: Lab Assignment3 on DW with OLAP For Lab3, note that there are different data sets in different years of Adventure Work Sample Data Warehouse. If you are using 2014 or 2017 Adventure Work DW, There are only up to 2010 Data for AdventureDW 2014 and up to 2014 Data for AdventureDW 2017. So, Change the current year 2008 to the current year 2010 or 2014 of your data set in the Question 3 and 4. Instructions for Installation and Set Up MS Business Intelligence Platform: Analysis Service (Multidimensional DW) and Integration Service in SSDT Data Tool Step 1: Installation for 2019/2017 SQL Server with Analysis Service for Multidimensional OLAP and Integration Service in SSDT for Data Mining with ML Note that You Have to Choose Multidimensional Mode instead of Tabular Mode when Installing SQL Server for Cube building and MDX Query Execution Later For CIS611 Lab3 and Final Project Later, Install ALL with Machine Learning Features as below: Note that You need to Choose Multidimensional and Data Mining Mode instead of Tabular Mode in Step 13 below for CIS611 Lab3 and Project Later. Step 2: Import2022/2019/2017 Sample Data Warehouse Adventure Works: Set Up Guides for Where/What/How to Import and Set Up a Sample Data Warehouse Adventure Work DW Important Notes to Know First !: 1. You Have to Download AdventureWorksDW (Data Warehouse) full database backups 2019 or 2017 Full Database Backup.zip from the MS GitHub Site below !! 2. Note that You have to Choose Data Warehouse for OLAP ! Not OLTP (Online Transaction Processing) Database backups ! 3.Download Sites: You can download from either one of two Microsoft AdventureWork Sample Database sites below Read the ReadMe file First ! Sample DW 2022, 2019, 2017, 2014 Versions: Step 3: Design and Deploy DW Cube from the Sample Data Warehouse Adventure Work Set Up Guide for What/How to Set Up for BI Project with Multidimensional Analysis Service (DW Cube with OLAP and MDX): Using 2022/2019/2017 Visual Studio with MS Integration Service and 2022/2019 SQL Server with Multi-Dimensional Analysis Service Using Sample Data Warehouse(2022/2017/2017) Try Deploying with a different Selection option for Impersonation Information if You are a Getting Deploying Error because of User Credentials. It may be depending on your installation set up for that ! FAQs: If you are getting errors while deploying a cube, or executing MDX, see the tutorials above to find the reasons and resolutions of those errors ! Project Tutorials for Business Intelligence with Multidimensional Analysis Service (DW Cube with OLAP and MDX): Tutorials for BI Project with Data Mining Using 2017 SSDT Data Mining Tool Lecture Notes_6_5: SQL Server 2012 Tutorials - Analysis Services Data Mining More Guilds for Old Versions: **** (It still HELPS A LOT since it is still the same as in the new version !) 2016/2014 Analysis Service and SSDT for Business Intelligence: Trouble Shooting Error Fixes: MDX (Multi-Dimensional Query) Tutorials:See MDX Tutorial Section in DW with OLAP Cube in the Class Lecture Note Section Data Mining Tutorial Using Analysis Service and SSDT with DMX (Data Mining Query) : Full Microsoft Documentation Sites: Lab Assignment3 on DW with OLAP Using Pentaho: Lab Assignment4 on B+ Tree, Hash Index, or Bitmap Index: This is a programing assignment. You have to implement B+ tree Insertion Algorithm using Tree. Implement B+ Tree Insertion based on the Lecture Notes (B+ Tree Insertion Algorithm II) given here. Start from an empty tree, insert data shown in the first tree in page 2 in the note. Follow by inserting 28, 70, 95 in that order. Changes in the B+ Tree Insertion Algorithm I and Algorithm II for Lab4: 1. Middle Key Goes Up When Split 2. The Same Index Key Appears at the Last Key of Left Child Node in a Leaf Node. See the Textbook Examples below. That is, For the insertion rule of the data values equal to the internal node keys, Follwing the example shown in the textbook to place the data values that are equal to the keys of the internal nodes. It means that insert the data values equal to the internal node keys to the Last key of the corresponding leaf node block that is pointed by left pointer of the internal key. Another Option for Lab4: Implement one of Hash Index based on the Lecture Notes Chap 17. Start from an empty hash table, insert data shown in the lecture notes in that order. |
| Project |
For the Class Size <= 30: The Recommended Group Size is 2 Members per group, but a 3-person Group is Allowed for a Bigger Scale (in Complexity) Project. If the Size of the Class is Too Big (35 - 55), 3-4 Person Project Group Will Be Allowed As Long As the Project Scope is Big Enough for 3-4 Persons For 3-4 Person Group with a BI Project, it is fine as long as the amount of work and the complexity level of the project is bigger than Lab3. The 3-4 Person Group should make efforts to plan to discover hidden knowledges which cannot be discovered in a simple MDX through multiple phases of analytic methods or Data mining techniques. IMPORTANT DATES for Group Project: Project Presentation Schedule Will Be Sent To Your CSU Email for Sign Up One week Before the Presentation Important Submission Instructions for Three Phases of Final Group Project: Submit Your Group Proposal (in Minimum 3 Pages) on BlackBoard Group Proposal Should Include Data Description, Investigate on Systems/Tools to Use for Your BI Platform, Goal of Business Analysis, Data Analysis Plan in Detail Your Project Status Report Should Show the Following Tasks Done: Platform Setting/Configuration Procedure if it is new, Your Data Contents, Your Cube Design, Deployment, and the Intermediate Outputs of Data Anaysis in Progress One Submission Per Group Is Required ! EACH Memeber Name and ID SHOULD BE LISTED in the COVER ! List Your First and Last Name, ID Only ! Exactly as Appeared in the CSU CampusNet. DO NOT Use Your Middle Name. If We Can't Find You by Your Name appeared on Your Project Report and Presentation Schedule. Your Project will be Considered as Missing with 0. Final Group Project Report Submission Instructions: Submit Group Project Presentation and Final Report in a Zip File By the End of Friday of Your Presentation Week ! Remember you have to include the source file of your Project Report in doc and Presentation slides in pptx ! If your data file is too big to upload, Submit your zip file with your Data file on your ONE drive and Send email to me and TA attached the data file on One Drive. Submit a Zip file that includes: All of your presentation slides (both in .ppt and .pdf) and Your Group Final Project Report (in doc) with Platform/System Set up Procedures/Instructions, Executions Steps, all the source codes, scripts, all the intermediate outputs, and final output files on Blackboard by the end of Friday of your presentation week. Include the Problems/Error Encountered and Your Resolutions in Your Report One Submission Per Group Required. Submit a Zip File that Includes All the required Source Files, Input, Output Files, and Final Report (in Doc file) and Presentation Slides (in pptx). Your Final Project Report Should Include the Set Up Procedure /Configuration Detail of Your Platform/System/Packages as well as Source Codes and Intermediate Results in files. The Report Should Explain Each Step of Your Project Tasks with the Screen Captures and Results. If you don't show/include any of the required contents in your report and presentation, I will ASSUME that your group submitted a Copy of Somebody's Github Codes from the Web. You can choose your group project from the following projects:
|
| Class | Chapter / Topic / Specific Objectives / Activities |
| 1 |
|
| 2-5 |
|
| 6-7 |
|
| 7-8 |
|
| 9-12 |
|
| 12-13 |
|
| 14-15 |
|
| 16 |
|
| 17 |
Presentation on Industry Research Papers For recent indistry research papers since 2017, Find in the listed conference sites in the Project Section above ! |
==> Completion of Homeworks/Labs is required for obtaining a passing grade.
| This
is a tentative scale and |
Letter |
Quality Points |
|
||
| A |
> 93% |
A: Outstanding (student's performance is genuinely excellent) | |||
| A- |
90% - 93% |
||||
| B+ |
87% - 90% |
||||
| B |
82% - 87% |
B: Very Good (student's performance is clearly commendable but not necessarily outstanding) | |||
|
|
B- |
80% - 82% |
|||
|
|
C |
75% - 80% |
C: Good (student's performance meets every course requirement and is acceptable; not distinguished) | ||
| D | 65%-75% | D: Below Average (student's performance fails to meet course objectives and standards) | |||
|
|
F |
<65% |
F: Failure (student's performance is unacceptable) | ||
|
ADA Adherence. If you need course adaptations or accommodations because of a disability, if you have emergency medical information to share with me, or if you need special arrangements in case the building must be evacuated, please make an appointment with me as soon as possible. My office location and hours are listed on top of this syllabus. If you need further information, please contact the ACCESS office, phone number 687-5106. |
Programming standards
|
|
|