C355_L06_6P

pdf

School

Republic Polytechnic *

*We aren’t endorsed by this school

Course

C355

Subject

Information Systems

Date

Nov 24, 2024

Type

pdf

Pages

34

Uploaded by 7uphysics

Report
OFFICIAL (CLOSED) \ NON-SENSITIVE C355 Business Intelligence (2021 Semester 2) School of Infocomm, Republic Polytechnic C355 Business Intelligence Lesson 06 Top & Bottom Sales 1
OFFICIAL (CLOSED) \ NON-SENSITIVE L06 Learning Objectives ▪ Apply the concepts of data table joins to produce information required from different datasets – Inner Join – Left Join (Left Outer Join) – Right Join (Right Outer Join) – Full Outer Join ▪ Data preparation using Conditional If-Else operator ▪ Create Common Filter to apply across pages on same data set 2
OFFICIAL (CLOSED) \ NON-SENSITIVE Today’s Problem The new management of Orion wants to review the performance of the Sales Department by finding out the following information: • Customers who did not place any order • Countries without customers • Sales staff who did not make any sales • Number of staff in each Performance Category 3
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Joins 4
OFFICIAL (CLOSED) \ NON-SENSITIVE Combine columns from two tables ▪ Data often resides in two (or more) tables or datasets. ▪ And, there are requirements to select or combine columns from two (or more) tables or datasets. ▪ SOLUTION: SQL JOIN 5 Left table Right table
OFFICIAL (CLOSED) \ NON-SENSITIVE Joins between Data Tables ▪ To combine two or more tables so as to make available the columns from the two or more tables in one query 6
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Inner Join ▪ Returns all the rows, consisting of selected columns from both tables, that match the JOIN condition. 7 Reference: http://www.w3schools.com/sql/sql_join_inner.asp JOIN : Employee.DepartmentID = Department.DepartmentID Left table Right table
OFFICIAL (CLOSED) \ NON-SENSITIVE Left Join ( Left Outer Join) ▪ Returns all the rows, consisting of selected columns from both tables, from the Left Table, even if there is no match on the right table 8 JOIN : Employee.DepartmentID = Department.DepartmentID Left table Right table Reference: http://www.w3schools.com/sql/sql_join_left.asp
OFFICIAL (CLOSED) \ NON-SENSITIVE Right Join ( Right Outer Join) ▪ Returns all the rows, consisting of selected columns from both tables, from the Right Table, even if there is no match on the left table 9 JOIN : Employee.DepartmentID = Department.DepartmentID Left table Right table Reference: http://www.w3schools.com/sql/sql_join_right.asp
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Full Outer Join (Not Commonly Used) ▪ Returns all the rows, consisting of selected columns from both tables, from both tables, even if there is no match on either table 10 JOIN : Employee.DepartmentID = Department.DepartmentID Left table Right table
OFFICIAL (CLOSED) \ NON-SENSITIVE Choice of Inner and Outer Join ? ▪ Use Inner Join if the keys must be found in both tables ▪ Use Outer Join only if you want a full list in 1 table and some of the keys may not be found in the other table 11 List of customers without country List of country with and without customers Table1: Customers; Table2 : Country List of customers with country List of country with customers List of country without customers List of customers with and without country
OFFICIAL (CLOSED) \ NON-SENSITIVE D ata Analysis & Reporting Techniques 12
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Lab Exercises 1) Inner Join between Product_Dim and TopProducts 2) Using the joined dataset in (1) above to do outer Join to Country_Lookup 3) Use conditional if-else to tag each row as ‘Null’ or ‘Not Null’ so that a count can be done based on this tag data item 4) Create a filter and set it as common filter to apply to different pages using same data source 13
OFFICIAL (CLOSED) \ NON-SENSITIVE Joining of Tables ▪ Both columns in the join condition must be of the same data type and length. ▪ In example below, both columns are of Character Type and Length of 2 14
OFFICIAL (CLOSED) \ NON-SENSITIVE Conditional If - Else Boolean Operator ▪ Use Conditional Boolean Operator If-Else to create new data 15 Note: If-Else cannot be used on aggregated measures
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Common Filter ▪ A filter can be defined on one chart and set as Common Filter ▪ This enables the same filter to be applied on multiple pages that use the same dataset ▪ This can be used on dashboard to show charts with same set of filters or across multiple pages 16
OFFICIAL (CLOSED) \ NON-SENSITIVE Solving Today's Problem Find out more information about the sales of its various products for Marketing team to propose a sustainable marketing campaign. Datasets: • Customers • Orders • Staff_Details • Country_Lookup 17
OFFICIAL (CLOSED) \ NON-SENSITIVE Examine Business Questions 18 1) Who are the customers who do not have any orders? - to find out why they stop their orders since their last order more than 5 years ago 2) Which are the countries which do not have customers yet? - to explore possibility of sales in these countries 3) Who are the sales staff who did not make any sales? - to fire these staff 4) How many sales staff fall under the following categories in their Performance? - to implement the necessary training if needed Staff Sales Volume Performance Category More than $1000.00 High Achiever Between $500.00 - $999.99 Average Achiever Between $0.01 - $499.99 Poor Achiever $0 or missing value To be Removed
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Preparation – Joining Datasets Businsess Question Join Remarks Customers who did not place any Order Left Outer Customers (CustomerID) to Orders (CustomerID) Missing values will be found in Orders table (null total_retail_price) Countries who do not have Customers Right Outer Customers (Customer_Country) to Country_LookUp (Country_Key) Missing values will be found in Customer table (null customer_name) Sales staff and their total sales value Inner Join Staff_Details (EmployeeiD) and Orders (EmployeeID) No missing values as both keys should be matched Sales staff who did not make any sales Left Outer Staff_Details (EmployeeID) to Orders (EmployeeID) Missing values will be found in Orders table (null total_retail_price) 19 Staff_Details Orders Customers Country_ Lookup (Left) (Right)
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Analysis & Reporting Business Question Analysis Technique Reporting Technique Customers who did not place any Order Filter on Total_Retail_Price is missing List table (Customer Name, Total_Retail_Price) Sort Total_Retail_Price Desc Countries who do not have Customers Filter on Customer_Name is missing List table (Country Name, Customer_Name) Sales staff who did not make any sales Create item Total_Retail_Price for each Sales staff Filter on Total_Retail_Price is missing List table (Employee_Name, Total_Retail_Price) Sort Total_Retail_Price Asc Number of employees in each Performance Category Create Create new data item Perf_Catg based on Total_retail_price Bar chart Category: Perf_Catg Measure: Frequency Sort Frequency Desc 20 Staff_Details Orders Customers Country) _Lookup (Left) ( Right )
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Preparation: Create Customer Order Outer Join ▪ Use Left Outer Join as Customers (CustomerID) on left and Orders (CustomerID) on right 21 Missing values will be seen in columns from Orders
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Preparation: Create Customer_Country Outer Join ▪ Right Outer Join as Country_Lookup (Country_Key) on right and Customers (Customer_Country) on Left. 22 Missing values will be seen in columns from Customers
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Preparation: Create Staff Orders Outer Join ▪ Use Left Outer Join with Staff_Details (EmployeeID) on left with Orders (EmployeeID) on right 23 Missing values will be seen in columns from Orders
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Analysis: Countries with no Customers ▪ Use Left Outer Join (Country) with Customers based on CustomerID ▪ Use Advanced Filter ▪ Change the filter condition to Customer Name is missing 24
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Analysis: Staff with no Sales ▪ Use Advance Filter and condition ‘ Total_Retail_Price is missing” ▪ Filter rows with missing Total_Retail_Price 25
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Analysis: Create New Category ( Perf_Catg ) ▪ Create an aggregated data source for the joined Staff_Details_Orders dataset 26
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Data Analysis: Create New Category ( Perf_Catg ) ▪ Based on aggregated joined Staff_Orders dataset, create a new category item Perf_Catg based on item Total_Retail_Price ▪ Into Poor, Average, High Achievers and ‘To be removed’ 27
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Report: Countries with and without Customers ▪ A list table of data items (Country Name and Customer Name) from both datasets 28
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Report: Countries with no Customers ▪ Use Left Outer Join (Country) with Customers based on CustomerID ▪ Using the filter condition “Customer Name is missing” ▪ Only get list with null customer name rows 29 Country_Cust_Join
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Report: Staff with no Sales ▪ Left outer Join (Employee) with Orders on EmployeeID ▪ List with null Total_Retail_Price 30 Employee_Orders_Join
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Report: Number of Staff in each Perf Category ▪ Bar Chart (to show ranking by height) ▪ Category : Perf_Catg; Measure: Frequency (Count) 31
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE What we have learnt ▪ Apply the concepts of data table joins to produce information required from different datasets – Inner Join – Left Join (Left Outer Join) – Right Join (Right Outer Join) – Full Outer Join ▪ Data preparation using Conditional If-Else operator ▪ Create Common Filter to apply across pages on same data set ✓ Recap of Data Preparation, Data Analysis and Data Reporting techniques 32
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Today's Lesson ▪ We have covered BI Enterprise Reporting 33
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
OFFICIAL (CLOSED) \ NON-SENSITIVE Reminder: L07 Pre - Reading Resources Read the following resources and complete the Pre-reading Worksheet C355_L07_PR_WS.docx • Resource A : Bad Data and its Side Effects • Resource B : Data Integration for Data Warehouse (Section 2.4) (Page 3-6) 34
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help

Related Documents

Discussion 3.9 Essay.docx
_wgu_course_c838___managing_cloud_security_exam-8.pdf
According to Ries a business that is in its early stage and which follows a proven business model is
404093359- Care home.docx
Chapter 4 Quiz_ Attempt review.pdf
SITXWHS003 - Student Assessment Tasks Hussain ali (1).pdf
Week 2W Compliance with Laws and Regulations.docx
Screenshot_11-2-2024_11745_app.sophia.org.jpeg
EEWK16.docx
AUTHENTICATION AUTHORIZATION DF 3.docx
SITXHRM008 Evaluation Report Template.docx
_wgu_c838-26.pdf