• 100 Queen St W, Brampton, ON L6X 1A4, Canada
  • +1-800-961-0337
START DATE END DATE CLASS TIMINGS MODE LOCATION ACTION
10/29/2018 10/31/2018
  • VLT
Live Online
11/26/2018 11/28/2018
  • VLT
Live Online

55232: Writing Analytical Queries for Business Intelligence Course 

55232: Writing Analytical Queries for Business Intelligence three-day teacher drove course is tied in with composing TSQL questions with the end goal of database revealing, investigation, and business knowledge. In particular, this course introduces TSQL inside the setting of information examination – as such, making importance from the information instead of exchange situated information level application advancement. 

The course begins with a concise discourse of levels of estimation and quantitative research philosophy, and incorporates these ideas into each TSQL theme introduced. The objective is to give a reliable, coordinate, and deliberate learning way for RDBMS information recovery for use in systematic instruments, for example, SQL Server Reporting Services, PowerBI, R, and Excel. 

55232: Writing Analytical Queries for Business Intelligence Audience Profile 

55232: Writing Analytical Queries for Business Intelligence course is proposed for data laborers and information science experts who try to utilize database revealing and examination apparatuses, for example, Microsoft SQL Server Reporting Services, Excel, Power BI, R, SAS and different business insight instruments, and wish to utilize TSQL questions to proficiently recover informational collections from Microsoft SQL Server social databases for use with these devices. 

55232: Writing Analytical Queries for Business Intelligence At Course Completion 

In the wake of finishing 55232: Writing Analytical Queries for Business Intelligence course, understudies will have the capacity to: 

  • Recognize free and ward factors and estimation levels in their own particular investigative work situations. 
  • Distinguish factors of enthusiasm for social database tables. 
  • Pick an information accumulation level and informational collection configuration proper for the planned examination and apparatus. 
  • Utilize TSQL SELECT questions to create prepared to-utilize informational indexes for investigation in devices, for example, PowerBI, SQL Server Reporting Services, Excel, R, SAS, SPSS, and others. 
  • Make put away techniques, perspectives, and capacities to modularize information recovery code. 

55232: Writing Analytical Queries for Business Intelligence Course Outline 

Module 1: Introduction to TSQL for Business Intelligence 

55232: Writing Analytical Queries for Business Intelligence module examines composing diagnostic inquiries versus value-based DML inquiries, and depicts the regular design of a business knowledge condition. It talks about the part of SELECT questions in recovering information for investigation from social databases. It acquaints the example database with be utilized as a part of the course, and starts an introduction of the SELECT query.Lessons 

  • Two Approaches to SQL Programming 
  • TSQL Data Retrieval in an Analytics Environment 
  • The Database Engine 
  • SQL Server Management Studio and the CarDeal Sample Database 
  • Distinguishing Variables in Tables 
  • SQL is a Declarative Language 
  • Prologue to the SELECT Query 

Lab : Lab 1 

  • Make a database graph 
  • Make and execute fundamental SELECT inquiries 
  • Subsequent to finishing this module, understudies will have the capacity to: 
  • Portray the reason for expository inquiries 
  • Portray the capacity of TSQL information recovery in an investigation/business knowledge condition 
  • Portray the essential elements of the database motor 
  • Talk about TSQL as a definitive dialect 
  • Recognize factors of enthusiasm for database tables 
  • Compose essential SELECT questions 

Module 2: Turning Table Columns into Variables for Analysis: SELECT List Expressions, WHERE, and ORDER BY 

55232: Writing Analytical Queries for Business Intelligence module covers the ID of and connection between levels of estimation and segment information sorts. It proceeds with an exchange of the SELECT inquiry and includes the WHERE and ORDER BY provisions. 

Lessons 

  • Transforming Columns into Variables for Analysis 
  • Section Expressions, Data Types, and Built-in Functions 
  • Section nom de plumes 
  • Information sort changes 
  • Worked in Scalar Functions 
  • Table Aliases 
  • The WHERE condition 
  • Request BY 

Lab : Lab 2-Write inquiries utilizing: 

  • Section and table false names 
  • Particular 
  • WHERE 
  • Request BY 
  • Worked in capacities 
  • Express and understood information sort change 

In the wake of finishing this module, understudies will have the capacity to: 

  • Actualize section articulations in SELECT inquiries 
  • Actualize section and table false names 
  • Portray information sorts and Implement information sort changes 
  • Actualize worked in capacities 
  • Utilize WHERE and ORDER BY statements in SELECT questions 

Module 3: Combining Columns from Multiple Tables into a Single Dataset: The JOIN Operators 

Module 3 talks about making single datasets for investigation by consolidating comes about because of different database tables utilizing JOIN. 

Lessons 

  • Essential Keys, Foreign Keys, and Joins 
  • Understanding Joins, Part 1: CROSS JOIN and the Full Cartesian Product 
  • Understanding Joins, Part 2: The INNER JOIN 
  • Understanding Joins, Part 3: The OUTER JOINS 
  • Understanding Joins, Part 4: Joining more than two tables 
  • Understanding Joins, Part 5: Combining INNER and OUTER JOINs 
  • Consolidating JOIN Operations with WHERE and ORDER BY 

Lab : Lab 3-Write SELECT questions utilizing: 

  • Internal join 
  • Left, right, and full join 
  • Joins of more than two tables 
  • Join administrators, notwithstanding WHERE and ORDER BY 
  • In the wake of finishing this module, understudies will have the capacity to: 
  • Talk about and portray the calculated stream of JOIN operations 
  • Actualize INNER and OUTER JOIN operations on at least two tables 
  • Portray the request of operations of SELECT statements 
  • Consolidate JOIN operations with WHERE and ORDER BY 

Module 4: Creating an Appropriate Aggregation Level Using GROUP BY 

55232: Writing Analytical Queries for Business Intelligence module covers the total of quantitative section esteems crosswise over gathering factors with the end goal of gathering astute examinations as well as changing the granularity of a dataset. 

Lessons 

  • Recognizing required collection level and granularity 
  • Total Functions 
  • Gathering BY 
  • HAVING 
  • Request of operations in SELECT inquiries 

Lab : Lab 4-Write inquiries utilizing: 

  • Total capacities 
  • Total capacity with HAVING 
  • Total capacity with GROUP BY and HAVING 
  • Total capacity with GROUP BY, HAVING, WHERE, and ORDER BY 
  • Subsequent to finishing this module, understudies will have the capacity to: 
  • Depict push granularity of result sets 
  • Talk about and execute total capacities to accomplish required line granularity 
  • Utilize GROUP BY to compute total esteems for gatherings 
  • Utilize HAVING to channel records in the outcome set by total esteem 
  • Join GROUP BY and HAVING with WHERE and ORDER BY 

Module 5: Subqueries, Derived Tables and Common Table Expressions 

55232: Writing Analytical Queries for Business Intelligence module covers the utilization of subqueries, inferred tables, and regular table articulations in SELECT inquiries as strategies for making transitional outcome sets. 

Lessons 

  • Non-associated and connected subqueries 
  • Determined tables 
  • Basic table articulations 
  • Lab : Lab 5-Write questions utilizing: 
  • Non-corresponded subqueries 
  • Corresponded subqueries 
  • Determined tables 
  • Basic table articulations 
  • Subqueries, determined tables, and regular table articulations and different subjects 
  • Subsequent to finishing this module, understudies will have the capacity to: 
  • Depict and talk about the justification of making middle of the road comes about sets inside SELECT inquiries 
  • Execute non-corresponded and related subqueries 
  • Execute determined tables 
  • Execute Common Table Expressions 
  • Make transitional to cutting edge TSQL questions to recover result sets for examination 

Module 6: Encapsulating Data Retrieval Logic 

55232: Writing Analytical Queries for Business Intelligence module examines the embodiment of information recovery rationale in sees, table-esteemed capacities, and put away techniques. It likewise portrays situations in which these strategies are helpful for creating datasets for examination. At long last, it portrays the database security issues included, and procedures for making and utilizing these database objects while keeping up current authorization sets on source information. 

Lessons 

  • Perspectives 
  • Table-esteemed capacities 
  • Put away systems 
  • Making objects for read-get to clients 
  • Making database represents explanatory customer devices 

Lab : Lab 6 

  • Make a SQL login 
  • Make a database client and dole out required consents 
  • Make a database pattern for perspectives, works, and put away procs 
  • Make a view 
  • Make a table-values work 
  • Make a put away technique 
  • Permit a client with read-just access to utilize sees, table-esteemed capacities, and store techniques 
  • In the wake of finishing this module, understudies will have the capacity to: 
  • Distinguish situations in which sees, table-esteemed capacities, and put away techniques essentially information recovery 
  • Look into sees, table-esteemed capacities, and put away systems 
  • Make sees, table-esteemed capacities, and put away strategies 
  • Portray the security prerequisite for making database objects 
  • Actualize sees, table-esteemed capacities, and put away methods for clients with read-just access to source information 

Module 7: Getting Your Dataset to the Client 

55232: Writing Analytical Queries for Business Intelligence module covers normal strategies for making datasets delivered by SELECT inquiries accessible to systematic customer apparatuses, for example, SQL Server Reporting Services, PowerBI, Excel, and R. It talks about running questions specifically from the customer device, notwithstanding sending out datasets to content documents which would then be able to be gotten to by the customer instrument. 

Lessons 

Interfacing with SQL Server and Submitting Queries from Client Tools 

Interfacing and running SELECT inquiries from: 

  • Exceed expectations 
  • PowerBI 
  • RStudio 
  • Sending out datasets to documents utilizing 
  • Results sheet from SSMS 
  • The bcp utility 
  • The Import/Export Wizard 

Lab : Lab 7 

  • Recovering the aftereffects of a view in Excel 
  • Running a specially appointed SELECT inquiry from Excel 
  • Running a specially appointed inquiry from PowerBI 
  • Running a specially appointed inquiry from RStudio 
  • Utilizing the Import/Export wizard to compose the aftereffects of an inquiry to a content document 
  • In the wake of finishing this module, understudies will have the capacity to: 
  • Portray the properties of database association strings 
  • Run questions from, and return results to, Excel, PowerBI, and RStudio 
  • Fare question results to outer content documents utilizing the SSMS comes about sheet, the bcp utility, and the Import/Export Wizard

Before going to 55232: Writing Analytical Queries for Business Intelligence course, understudies must have: 

  • Setting learning of information examination and business knowledge situations. For instance, a comprehension of a business related business knowledge venture or need. 
  • Fundamental information of the Windows working framework and its center usefulness, including document framework route. 
  • Essential comprehension of the reason for social database administration frameworks, for example, SQL Server.

Awards