Aster Data Database Administration Training

Customized Onsite Training

2
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors
Overview

In this Aster Data Database Administration training class, participants will learn Aster Data from a DBA’s perspective with a focus on what’s most important to perform.

This course is designed for all users of Aster Data with a special focus for DBAs.

Goals
  1. Gain the knowledge to be able to make strategic decisions regarding your Aster Data environment.
Outline
  1. The Aster Data Architecture
    1. What is Parallel Processing?
    2. Aster Data is a Parallel Processing System
    3. Each vworker holds a Portion of Every Table
    4. The Rows of a Table are Spread Across All vworkers
    5. The Aster Data Architecture
    6. The Queen Node
    7. The Worker Node
    8. The Loader Node
    9. The Backup Node
    10. The Aster Architecture Interconnect
    11. Backup and Loader Nodes Do Not use the Interconnect
    12. The Aster Architecture has Spare Nodes
    13. The Aster Architecture Allows Flexibility based on Need
    14. Aster Data Provides Four Fundamental Hardware Strengths
    15. Replication Failover
    16. Aster Data Database Administration Course Outline
    17. Data is Compressed on Data Transfers
    18. Aster Utilizes Dual Optimizers
    19. Aster Allows a Hybrid of SQL and MapReduce
    20. MapReduce History
    21. What is MapReduce?
    22. What is SQL:MR?
    23. Sessionize: An Example of SQL:MR
    24. Support for Mixed Workload Management and Prioritization
  2. Administrative Operations Overview
    1. Cluster Management
    2. Cluster Administration
    3. Database Administration
    4. Bulk Utilities
    5. Aster Database Management Console (AMC)
    6. Aster Database Event Engine
    7. nCluster Command Line Interface (ncli)
    8. Aster nCluster Terminal (ACT)
    9. ODBC and JDBC Connections to Third Party Tools
    10. Aster Database Loader
    11. Aster Database Backup
    12. Logging Into the AMC
    13. Overview of the AMC: The Dashboard Tab
    14. Overview of the AMC: The Processes Tab
    15. Overview of the AMC: The Nodes Tab
    16. Overview of the AMC: The Admin Tab
  3. Fact and Dimension Tables
    1. Aster Tables are defined as Fact or Dimension when Created
    2. Fact Table
    3. A More Detailed Look at the Fact Table Distribution
    4. Aster Data Database Administration Course Outline
    5. Dimension Table are Replicated
    6. A Dimension Table is often Replicated across vworkers
    7. Aster Data has Fact and Dimension Tables
    8. Aster Tables are defined as Fact or Dimension when Created
    9. Fact and Dimension Tables can be Hashed by the same Key
    10. Distribution Key Rules
    11. Aster Data Uses a Hash Formula
    12. The Hash Map Determines which vworker will own the Row
    13. The Hash Formula, Hash Map and vworker
    14. Placing rows on the vworker
    15. Placing rows on the vworker Continued
    16. A Review of the Hashing Process
    17. Like Data Hashes to the Same vworker
    18. Distribution Key Data Types
    19. Run ANALYZE to COLLECT STATISTICS on a Table
    20. Some Examples of ANALYZE
    21. What Columns to Analyze
  4. The AMC in Detail
    1. Dashboard Tab
    2. Processes Tab
    3. Processes Tab (Hover over the ID)
    4. Processes Tab Detail by Clicking on the ID
    5. Processes Tab: Query Timeline
    6. Processes Tab: Sessions
    7. Nodes Tab: Node Overview
    8. Nodes Tab: Hardware Stats
    9. Nodes Tab: Hardware Config
    10. Nodes Tab: Hardware Config
    11. Admin Tab: Cluster Management
    12. Admin Tab: Events
    13. Aster Data Database Administration Course Outline
    14. Admin Tab: Executables
    15. In the Admin Tab, we clicked on the Executables Tab.
    16. Admin Tab: Logs
    17. Admin Tab: Configuration>Cluster Settings
    18. Admin Tab: Configuration>Workload>Service Classes
    19. Admin Tab: Configuration>Roles and Privileges
    20. Setting up IP Pools
    21. Setting up IP Pools in the AMC
    22. Setting up IP Pools in the AMC (continued)
    23. Remove Nodes
    24. Check Node Hardware Configuration
    25. Configure Cluster Settings
    26. Cluster Settings
    27. Sparkline Graph Scale Units
    28. Graph Scaling
    29. Internet Access Settings
    30. Aster Support Settings
    31. QoS Concurrency Threshold Configuration
    32. Roles and Privileges
    33. View the List of Available AMC User Privileges
    34. View the List of Available AMC User Privileges (continued)
    35. Create an AMC User
    36. Check Current AMC Privileges
    37. Edit AMC Privileges
    38. Restarting Your Aster Database
    39. Procedure for Restarting the Aster Database
    40. Procedure for Restarting the Aster Database
    41. Activate Aster Database
    42. Activate Aster Database (continued)
    43. Situations that Require Activation
    44. Activate Aster Database: The Procedure
    45. Aster Data Database Administration Course Outline
    46. Balance Data
    47. Balance Process
    48. Cluster Management from the Command Line
    49. Check Cluster Status
    50. Soft Shutdown
    51. Soft Startup
    52. Free Space Occupied by Defunct Vworkers
    53. Passwordless Root SSH
    54. Passwordless Root SSH (continued)
    55. Passwordless Root SSH (continued)
    56. Passwordless Root SSH (continued)
    57. Passwordless Root SSH (continued)
    58. Passwordless Root SSH (continued)
  5. How Aster Processes Data
    1. When a Table is Created, a Table Header is Created
    2. Every vworker has the Exact Same Tables
    3. All Aster Tables are spread across All vworkers
    4. The Table Header and the Data Rows are Stored Separately
    5. A vworker Stores the Rows of a Table inside a Data Block
    6. To Read Rows, a vworker Moves the Data Block into Memory
    7. A Full Table Scan Means All vworkers must Read All Rows
    8. The "Achilles Heel", or Slowest Process, is Block Transfer
    9. Each Table has a Distribution Key
    10. A Query Using the Distribution Key uses a Single vworker.
    11. As Rows are Added, a Data Block will Eventually Split
    12. A Full Table Scan Means All vworkers Read All Blocks
    13. Distribution Key Query uses One vworker
    14. Each vworker Can Have Many Blocks for a Single Table
    15. A Full Table Scan Means All vworkers Read All Blocks
    16. Quiz: How Many Blocks Move into vworker Memory?
    17. Aster Data Database Administration Course Outline
    18. Answer: How Many Blocks Move into vworker Memory?
    19. Quiz: How Many Blocks Move Using the Distribution Key?
    20. Answer:How Many Blocks Move Using the Distribution Key?
  6. Four Options for Aster Data Table Design
    1. There are Four Options to Aster Table Design
    2. Straight up Distribute by Hash
    3. Straight up Distribute by Hash: Problems
    4. Straight up Distribute by Replication
    5. Partition the Table with Logical Partitioning
    6. This Partitioned Table Sorts Rows by Month of Order_Date
    7. An All vworkers Retrieve By Way of a Single Partition
    8. You can Partition a Table by Range or by List
    9. A Partitioned By List Example with Three Tactical Queries
    10. Aster Data Multi:Level Partitioning
    11. Aster Allows for Multi:Level Partitioning
    12. SQL Commands for Logical Partitioning as One Table
    13. What Partitions are on my Table?
    14. What does a Columnar Table look like?
    15. A Comparison of Data for Normal Vs. Columnar
    16. A Columnar Table is best for Queries with Few Columns
    17. Quiz: How Many Blocks Move to vworker Memory?
    18. Answer: How Many Containers Move to vworker Memory?
    19. When to use a Columnar Table
  7. Understanding the Event Engine
    1. Monitor Events with the Event Engine
    2. Monitor Events with the Event Engine
    3. Event Engine Overview
    4. Manage Event Subscriptions
    5. Create or Edit Event Subscriptions
    6. Aster Data Database Administration Course Outline
    7. Create or Edit Event Subscriptions (continued)
    8. Create or Edit Event Subscriptions (continued)
    9. Upgrades of Event Engine
    10. View Event Subscriptions
    11. Supported Events
    12. Supported Events (continued)
    13. Supported Events (continued)
    14. Remediations
    15. Automatic Cluster Shutdown on Disk Full Event
    16. Automatic Cluster Shutdown on Disk Full Event (continued)
    17. Event Engine Best Practices
    18. Test the Event Engine
    19. Test the Event Engine: Disk Level Events
    20. Test the Event Engine: Disk Level Events (continued)
    21. Troubleshoot Event Engine Issues
    22. Monitor the Aster Database with SNMP
  8. How Joins Work Inside the Aster Engine
    1. Aster Join Quiz
    2. Aster Join Quiz Answer
    3. The Joining of Two Tables
    4. Aster Moves Joining Rows to the Same vworker
    5. Because of the Join Rule: Dimension Table are Replicated
    6. The Two Different Philosophies for Table Join Design
    7. What Could You Do If Two Tables Joined 1000 Times a Day?
    8. Fact and Dimension Tables can be Hashed by the same Key
    9. Joining Two Tables with the same PK/FK Distribution Key
    10. A Join With Co:Location
    11. A Performance Tuning Technique for Large Joins
    12. The Joining of Two Tables with an Additional WHERE Clause
    13. Aster Performs Joins Using Three Different Methods
    14. Aster Data Database Administration Course Outline
    15. The Hash Join
    16. The Merge Join
    17. Nested Loop Joins
  9. Temporary and Analytic Tables
    1. Aster has Three Types of Data
    2. Create a Permanent Table Using Create Table AS (CTAS)
    3. Create a Logically Partitioned Table and Populate It
    4. Create a Temporary Table with using Create Table AS (CTAS)
    5. A Temporary Table in Action
    6. A Temporary Table That Uses an Insert/Select
    7. Create an Analytic Table Using an Insert/Select
    8. Create an Analytic Table Using CREATE TABLE AS (CTAS)
    9. Operations that Invalidate an Analytic Table
    10. If an Analytic Table is Invalid
    11. Tera:Tom History
  10. Backing up the System
    1. Manage Backups
    2. Add a New Backup Manager to the AMC
    3. Start a Backup
    4. Backup Manager Commands
    5. Backup Manager Commands (continued)
    6. Backup Manager Commands (continued)
    7. Backup Manager Commands (continued)
    8. Monitor and Manage Backups: Starting and Stopping
    9. Monitor and Manage Backups: Status and Availability
    10. Recovering the Queen Node: Queen Replacement
    11. Recovering the Queen Node: Queen Replacement Script
    12. Recovering the Queen Node: Queen Replacement (continued)
    13. Recovering the Queen Node: Queen Replacement (continued)
    14. Aster Data Database Administration Course Outline
    15. Queen Replacement Best Practices
  11. Configuring the Aster Database Connector
    1. Set up Host Entries for all Nodes
    2. Set up Host Entries for all Nodes (continued)
    3. Set up Host Entries for all Nodes (continued)
    4. Configure Hosts
    5. Configure Hosts (continued)
    6. Configure Hosts (continued)
    7. Configure Hosts (continued)
    8. Setting up the Connector: Networking
    9. Setting up the Connector: Client Software
    10. Setting up the Connector: Performance
    11. load_from_teradata Syntax
    12. load_from_teradata Syntax (continued)
    13. load_from_teradata Syntax (continued)
    14. load_from_teradata Syntax (continued)
    15. load_from_teradata Syntax (continued)
    16. load_to_teradata Syntax
    17. load_to_teradata Syntax (continued)
    18. load_to_teradata Syntax (continued)
    19. load_to_teradata Syntax (continued)
    20. load_to_teradata Syntax (continued)
    21. Cancelling load_to_teradata
    22. Procedure for Setting START_INSTANCE
    23. Procedure for Setting START_INSTANCE (continue)
    24. Procedure for Setting START_INSTANCE (continue)
    25. Procedure for Setting START_INSTANCE (continue)
    26. Troubleshooting the Connector
    27. Running Joins in Aster to Teradata
    28. Building Remote Views
    29. Aster Data Database Administration Course Outline
    30. Create Table in Aster Example
  12. Aster Modeling Rules
    1. Modeling Rules for Aster Data
    2. Three Principles that Govern the Modeling Rules
    3. Modeling Rule 1: Dimensionalize your Model
    4. A Dimensional Model is called a "Star Schema"
    5. To Read a Data Block, a vworker Moves the Block to Memory
    6. A Dimensional Model Moves Less Mass into Memory
    7. Which Move From Disk to Memory Would You Choose?
    8. Vworkers transfer their Fact Table into Memory in Parallel
    9. Modeling Rule 2: Use Columnar
    10. Which Move From Disk to Memory Would You Choose?
    11. Let's Discuss Modeling and Joins at the Simplest Level
    12. Let's Discuss Modeling and Joins at the Simplest Level
    13. Let's Discuss Joins at the Simplest Level
    14. Modeling Rule 3: Distribute your Tables Based on Joins
    15. The Two Different Philosophies for Table Join Design
    16. Facts are Hashed and most often the Dimension is Replicated
    17. Fact and Dimension Tables can be Hashed by the same Key
    18. Joining Two Tables with the same PK/FK Primary Index
    19. A Join With No Redistribution or Duplication
    20. Aster Hates Joining Tables with a Different Distribution Key
    21. Aster Hates to Redistribute by Hash to Join Tables
    22. Modeling Rule 4: Replicate Dimension Tables
    23. Modeling Rule 5: Partition Your Tables
    24. Modeling Rule 6: Make Fact Tables Skinny
    25. Modeling Rule 6: Make Fact Tables Skinny Example
    26. Modeling Rule 7: Index Your Tables
    27. The B:Tree Index
    28. Which Columns Might You Create an Index?
    29. Aster Data Database Administration Course Outline
    30. Answer: Which Columns Might You Create an Index?
    31. Modeling Rule 8: Denormalize based on Your Environment
    32. Modeling Rule 8: Denormalize based on Your Environment
  13. Tera:Tom's Top Tips
    1. Tera:Tom's Top Tips
    2. Tera:Tom's Top Tips # 2
    3. Tera:Tom's Top Tips #3
    4. Tera:Tom's Top Tips # 3 Rewritten
    5. Tera:Tom's Top Tips #4
    6. When the GROUP BY Column is NOT the Distribution Key
    7. Example of GROUP BY Column is NOT the Distribution Key
    8. Tera:Tom's Top Tips #5
    9. Tera:Tom's Top Tips #6: Use EXPLAIN
    10. Query Plan and Estimates
    11. Explain Plan Showing a Hash Join
    12. Explain Plan Showing a Merge Join
    13. Explain Plan Showing a Nested Loop Join
  14. Indexes
    1. There are Only Three Types of Scans
    2. Guidelines for Indexes
    3. An Index Syntax Example
    4. The B:Tree Index
    5. Which Columns Might You Create an Index?
    6. Answer: Which Columns Might You Create an Index?
    7. A Visual of an Index (Conceptually)
    8. A Query Using an Index Uses All vworkers
    9. Multicolumn indexes
    10. A NUSI BITMAP Theory
    11. A NUSI Bitmap in Action
    12. Aster Data Database Administration Course Outline
    13. Indexes on Expressions
    14. Indexes on Extracts of Dates
    15. GiST Indexes
    16. Five Operational Tips for Efficient Indexing
    17. REINDEX
    18. createCompressedIndexOnCompressedTableByDefault Flag
  15. SQL:H
    1. Introduction to SQL:H
    2. Configuring SQL:H Aster
    3. Aster 5.10 or Earlier SQL:H Configuration
    4. Loading from HCatalog:Syntax
    5. Loading from HCatalog:Syntax (continued)
    6. Loading from HCatalog:Syntax (continued)
    7. Displaying the HCatalog
    8. Displaying the HCatalog (continued)
    9. Using SQL:H to Create Views
    10. Displaying the HCatalog (continued)
    11. Using SQL:H to Create Views
    12. Eliminating Partitions in SQL:H Views
    13. Conversions
    14. Tips for Working with SQL:H
    15. Tips for Working with SQL:H (continued)
    16. Troubleshooting SQL:H
    17. Troubleshooting SQL:H (Additional Errors You Might See)
  16. Workload Management
    1. Introduction to Workload Management
    2. Admission Control
    3. Managing Concurrency Using ncli
    4. Configuring Admission Limits with AMC
    5. Aster Data Database Administration Course Outline
    6. Resource Management
    7. Resource Management (continued)
    8. Workload Settings via the Command Line
    9. Priority and Weight
    10. Resource Allocation
    11. Memory Soft Limit Percent
    12. Memory Hard Limit Percent
    13. Automatic Query Cancellation
    14. Workload Policies
    15. Setting Up Workload Rules
    16. Setting Up Workload Rules (continued)
    17. Setting Up Workload Rules (continued)
    18. Setting Up Workload Rules (continued)
    19. Setting Up Workload Rules (continued)
    20. Workload Predicates
    21. Workload Predicates (continued
    22. Workload Predicates (continued)
    23. Monitoring Queries
    24. Best Practices
    25. Troubleshooting
  17. Aster Windows Functions
    1. Cumulative Sum
    2. Cumulative Sum: Major and Minor Sort Key(s)
    3. The ANSI CSUM: Getting a Sequential Number
    4. The ANSI OLAP: Reset with a PARTITION BY Statement
    5. PARTITION BY only Resets a Single OLAP not ALL of them
    6. ANSI Moving Sum is Current Row and Preceding n Rows
    7. How ANSI Moving SUM Handles the Sort
    8. Quiz: How is that Total Calculated?
    9. Answer to Quiz: How is that Total Calculated?
    10. Aster Data Database Administration Course Outline
    11. Moving SUM every 3:rows vs. a Continuous Sum
    12. Moving Average
    13. Quiz: How is that Total Calculated?
    14. Answer to Quiz: How is that Total Calculated?
    15. Quiz: How is that 4th Row Calculated?
    16. Answer to Quiz: How is that 4th Row Calculated?
    17. Partition By Resets an ANSI OLAP
    18. Moving Average Using BETWEEN
    19. Moving Difference using ANSI Syntax
    20. Moving Difference using ANSI Syntax with Partition By
    21. RANK Defaults to Ascending Order
    22. Getting RANK to Sort in DESC Order
    23. You can use Window Functions in Expressions
    24. RANK() OVER and PARTITION BY
    25. DENSE_RANK() OVER
    26. PERCENT_RANK() OVER
    27. PERCENT_RANK() OVER with 14 rows in Calculation
    28. PERCENT_RANK() OVER with 21 rows in Calculation
    29. RANK With ORDER BY SUM()
    30. COUNT OVER for a Sequential Number
    31. Quiz: What caused the COUNT OVER to Reset?
    32. Answer to Quiz: What caused the COUNT OVER to Reset?
    33. The MAX OVER Command
    34. MAX OVER with PARTITION BY Reset
    35. The MIN OVER Command
    36. Quiz: Fill in the Blank
    37. Answer to Quiz: Fill in the Blank
    38. The Row_Number Command
    39. Quiz: How did the Row_Number Reset?
    40. Quiz: How did the Row_Number Reset?
    41. NTILE
    42. Aster Data Database Administration Course Outline
    43. NTILE Using a Value of 10
    44. NTILE With a Partition
    45. CUME_DIST
    46. CUME_DIST With a Partition
    47. LEAD
    48. LEAD With Partitioning
    49. LAG
    50. LAG with Partitioning
    51. FIRST_VALUE
    52. FIRST_VALUE After Sorting by the Highest Value
    53. FIRST_VALUE with Partitioning
    54. LAST_VALUE
    55. NTH_VALUE
    56. NTH_VALUE With Partition
    57. SUM(SUM(n))
  18. SQL:MapReduce
    1. MapReduce History
    2. What is MapReduce?
    3. What is SQL:MapReduce?
    4. SQL:MapReduce Input
    5. SQL:MapReduce Output
    6. Subtle SQL:MapReduce Processing
    7. Aster Data Provides an Analytic Foundation
    8. Path Analysis
    9. Text Analysis
    10. Statistical Analysis
    11. Segmentation (Data Mining)
    12. Graph Analysis
    13. Transformation of Data
    14. Sessionize
    15. Aster Data Database Administration Course Outline
    16. Tokenize
    17. SQL:MapReduce Function… nPath
    18. nPath SELECT Clause
    19. nPath ON Clause
    20. nPath PARTITION BY Expression
    21. nPath DIMENSION Expression
    22. nPath ORDER BY Expression
    23. nPath MODE Clause has Overlapping or NonOverlapping
    24. nPath PATTERN Clause
    25. Pattern Operators
    26. Pattern Operators Order of Precedence
    27. Matching Patterns Which Repeat
    28. nPath SYMBOLS Clause
    29. nPath RESULTS Clause
    30. Adding an Aggregate to nPath Results
    31. Adding an Aggregate to nPath Results (Continued)
    32. SQL:MapReduce Examples: Use Regular SQL
    33. SQL:MapReduce Examples: Create Objects
    34. SQL:MapReduce Examples: Subquery
    35. SQL:MapReduce Examples: Query as Input
    36. SQL:MapReduce Examples: Nesting Functions
    37. SQL:MapReduce Examples: Functions in Derived Tables
    38. SQL:MapReduce Examples: SMAVG
    39. SQL:MapReduce Examples: Pack Function
    40. SQL:MapReduce Examples: Pack Function (Continued)
    41. SQL:MapReduce Examples: Pivot Columns
    42. Workshop: Create This Table
    43. Login to your GNOME Terminal
    44. Login to your Linux
    45. Using the GNOME Terminal Unzip the bank_web_data.zip
    46. Use the Function ncluster_loader to Load the Bank Data
    47. Aster Data Database Administration Course Outline
    48. Run this nPath Map Reduce Function on your Table
    49. nPath in Action
    50. Operators at their Simplest
    51. Pattern
    52. Accumulate
    53. Accumulate With All Pages
    54. Accumulate: nPath with a WHERE Clause
    55. SQL:MapReduce Examples: Path Generator
    56. SQL:MapReduce Examples: Path Generator (Continued)
    57. SQL:MapReduce Examples: Path Generator (Continued)
    58. SQL:MapReduce Examples: Path Generator (Continued)
    59. SQL:MapReduce Examples: Path Generator (Continued)
    60. SQL:MapReduce Examples: Linear Regression
    61. SQL:MapReduce Examples: Linear Regression (Continued)
    62. SQL:MapReduce Examples: Linear Regression (Continued)
    63. SQL:MapReduce Examples: Naive Bayes
    64. SQL:MapReduce Examples: Naive Bayes (Continued)
    65. SQL:MapReduce Examples: Naive Bayes (Continued)
    66. SQL:MapReduce Examples: Naive Bayes (Continued)
    67. SQL:MapReduce Examples: Naive Bayes (Continued)
    68. SQL:MapReduce Examples: Naive Bayes (Continued)
    69. SQL:MapReduce Examples: Naive Bayes (Continued)
    70. SQL:MapReduce Examples: Naive Bayes (Continued)
    71. Join Aster, Teradata and Hadoop Tables; feed into MapReduce
    72. Run Both of these Examples Together and Compare
    73. Run this nPath Map Reduce Function
    74. nPath in Action
    75. Another nPath Example
    76. Finding Out What Functions You Have Installed
    77. Workshop 1: Fill in the x's
    78. Answer Workshop 1: Fill in the x's
    79. Aster Data Database Administration Course Outline
    80. Workshop 2: Fill in the x's
    81. Answer Workshop 2: Fill in the x's
    82. Answer Workshop 2: You Could Have Used a GROUP BY
    83. Workshop 3: Add to the Query
    84. Workshop 3: Answer to Add to the Query
    85. Workshop 4: Fill in the x's
    86. Answer to Workshop 4: Fill in the x's
    87. Workshop 5: Find that Customer
    88. Answer to Workshop 5: Find that Customer
    89. Workshop 6: Change the MapReduce Function
    90. Answer to Workshop 6: Change the MapReduce Function
    91. Workshop 7: Build the MapReduce Function
    92. Answer to Workshop 7: Build the MapReduce Function
    93. Best Answer to Workshop 7: Build the MapReduce Function
    94. Workshop 8: Build the Accumulate in the Result
    95. Answer to Workshop 8: Build the Accumulate in the Result
    96. SQL:MapReduce Examples: Linear Regression (Continued)
    97. Workshop 9: Build the Subquery
    98. Answer to Workshop 9: Build the Subquery
    99. Workshop 10: Do Your First Join
    100. Answer to Workshop 10: Do Your First Join
    101. Answer to Workshop 10: Do the Join Using a New Syntax
    102. Workshop 11: Super Join the Tables
    103. Answer to Workshop 11:Super Join the Tables
    104. Answer to Workshop 11: Super Join the Tables
    105. Workshop 12: Sessionize the Data
    106. Answer to Workshop 12: Sessionize the Data
    107. Workshop 13: What is this Query Doing?
    108. Answer to Workshop 13: What is this Query Doing?
    109. Workshop 14: Using ilike
    110. Answer to Workshop 14: Using ilike
    111. Aster Data Database Administration Course Outline
    112. Answer to Workshop 14: Using ilike
    113. Workshop 15: What are the First Two Pages Visited?
    114. Workshop 15: What are the First Two Pages Visited?
    115. Workshop 16: Advanced: First Two Pages Visited?
    116. Answer to Workshop 16 Advanced: First Two Pages Visited?
    117. Workshop 17: Can You Clean Up the Results?
    118. Answer to Workshop 17: Can You Clean Up the Results?
    119. Answer to Workshop 17: Format the Date
    120. Workshop 18: Build a Churn Table
    121. Workshop 18: Run the Query Before Building to Test
    122. Workshop 18: A Better Example
    123. Answer to Workshop 18: Build a Basic Churn Table
    124. Workshop 18: Create the Churn Table with a Better Example
    125. Multi:Case
    126. The Multi:Case Function
    127. The Multi:Case Function in Nexus
    128. The Multi:Case Function Mixing and Matching
    129. The Multi:Case Function Mixing and Matching
    130. SQL:MapReduce Examples: cFilter
    131. SQL:MapReduce Examples: cFilter (Continued)
    132. SQL:MapReduce Examples: Linear Regression (Continued)
    133. SQL:MapReduce Examples: cFilter (Continued)
    134. SQL:MapReduce Examples: Linear Regression (Continued)
    135. SQL:MapReduce Examples: cFilter (Continued)
    136. SQL:MapReduce Examples: cFilter (Continued)
    137. SQL:MapReduce Examples: cFilter (Continued)
    138. SQL:MapReduce Examples: cFilter (Continued)
    139. SQL:MapReduce Examples: cFilter (Continued)
    140. SQL:MapReduce Examples: cFilter (Continued)
    141. SQL:MapReduce Examples: cFilter (Continued)
    142. CFILTER in Action with Bank_Web_Clicks
    143. Aster Data Database Administration Course Outline
    144. CFILTER in Action
    145. CFILTER using Nexus
    146. nPath Error
  19. Time and Date
    1. Date, Time, and Timestamp Keywords
    2. Add or Subtract Days from a date
    3. The to_char command
    4. A Summary of Math Operations on Dates
    5. Using a Math Operation to find your Age in Years
    6. Find What Day of the week you were Born
    7. Date Related Functions
    8. The EXTRACT Command
    9. EXTRACT from DATES and TIME
    10. EXTRACT with DATE and TIME Literals
    11. EXTRACT of the Month on Aggregate Queries
    12. A Side Title example with Reserved Words as an Alias
    13. Implied Extract of Day, Month and Year
    14. DATE_PART Function
    15. DATE_TRUNC Function
    16. DATE_TRUNC Function using TIME
    17. Aster NOW() Function
  20. Aster Windows Functions
    1. Cumulative Sum
    2. Cumulative Sum: Major and Minor Sort Key(s)
    3. The ANSI CSUM: Getting a Sequential Number
    4. The ANSI OLAP: Reset with a PARTITION BY Statement
    5. PARTITION BY only Resets a Single OLAP not ALL of them
    6. ANSI Moving Sum is Current Row and Preceding n Rows
    7. How ANSI Moving SUM Handles the Sort
    8. Aster Data Database Administration Course Outline
    9. Quiz: How is that Total Calculated?
    10. Answer to Quiz: How is that Total Calculated?
    11. Moving SUM every 3:rows vs. a Continuous Sum
    12. Moving Average
    13. Quiz: How is that Total Calculated?
    14. Answer to Quiz: How is that Total Calculated?
    15. Quiz: How is that 4th Row Calculated?
    16. Answer to Quiz: How is that 4th Row Calculated?
    17. Partition By Resets an ANSI OLAP
    18. Moving Average Using BETWEEN
    19. Moving Difference using ANSI Syntax
    20. Moving Difference using ANSI Syntax with Partition By
    21. RANK Defaults to Ascending Order
    22. Getting RANK to Sort in DESC Order
    23. You can use Window Functions in Expressions
    24. RANK() OVER and PARTITION BY
    25. DENSE_RANK() OVER
    26. PERCENT_RANK() OVER
    27. PERCENT_RANK() OVER with 14 rows in Calculation
    28. PERCENT_RANK() OVER with 21 rows in Calculation
    29. RANK With ORDER BY SUM()
    30. COUNT OVER for a Sequential Number
    31. Quiz: What caused the COUNT OVER to Reset?
    32. Answer to Quiz: What caused the COUNT OVER to Reset?
    33. The MAX OVER Command
    34. MAX OVER with PARTITION BY Reset
    35. The MIN OVER Command
    36. Quiz: Fill in the Blank
    37. Answer to Quiz: Fill in the Blank
    38. The Row_Number Command
    39. Quiz: How did the Row_Number Reset?
    40. Aster Data Database Administration Course Outline
    41. Quiz: How did the Row_Number Reset?
    42. NTILE
    43. NTILE Using a Value of 10
    44. NTILE With a Partition
    45. CUME_DIST
    46. CUME_DIST With a Partition
    47. LEAD
    48. LEAD With Partitioning
    49. LAG
    50. LAG with Partitioning
    51. FIRST_VALUE
    52. FIRST_VALUE After Sorting by the Highest Value
    53. FIRST_VALUE with Partitioning
    54. LAST_VALUE
    55. NTH_VALUE
    56. NTH_VALUE With Partition
    57. SUM(SUM(n))
  21. The Fundamental SQL Commands That Work on Aster
    1. BETWEEN is Inclusive
    2. BETWEEN Works for Character Data
    3. LIKE uses Wildcards Percent '%' and Underscore '_'
    4. LIKE command Underscore is Wildcard for one Character
    5. GROUP BY Vs. DISTINCT: Good Advice
    6. The Five Aggregates of Aster Data
    7. GROUP BY when Aggregates and Normal Columns Mix
    8. GROUP BY Delivers one row per Group
    9. GROUP BY Dept_No or GROUP BY 1 the same thing
    10. Limiting Rows and Improving Performance with WHERE
    11. WHERE Clause in Aggregation limits unneeded Calculations
    12. Keyword HAVING tests Aggregates after they are Totaled
    13. Aster Data Database Administration Course Outline
    14. Keyword HAVING is like an Extra WHERE Clause for Totals
    15. Getting the Average Values per Column
    16. Getting the Average Values per Column
    17. Average Values per Column for All Columns in a Table
    18. A two:table join using Non:ANSI Syntax
    19. A two:table join using Non:ANSI Syntax with Table Alias
    20. Aliases and Fully Qualifying Columns
    21. A two:table join using ANSI Syntax
    22. Both Queries have the same Results and Performance
    23. Quiz: Can You Finish the Join Syntax?
    24. Answer to Quiz: Can You Finish the Join Syntax?
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

74,839

Students who have taken Live Online Training

15,233

Organizations who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.41

Students rated our trainers 9.41 out of 10 based on 5,189 reviews

Contact Us or call 1-877-932-8228