Chapter 41
Your Nexus Query Chameleon
“Anything you can do needs to be done, so pick up the tool of your choice and get started.”
Table of Contents Chapter 41 – Your Nexus Query Chameleon
– Watch the Video on the new Nexus Super Join Builder
– How to Customize your System Tree View
– Introducing the new Nexus Super Join Builder
– Define your Joins and tell Nexus to “Add and Remember Me”
– Nexus knows what Tables Join together
– Nexus Presents Tables and their columns in Color
– Nexus Builds your SQL Automagically
– Nexus can Cube a Table and Join to Everything Possible
– The Cube SQL created Automagically
– Manipulate the Columns with the Columns Tab
– Single Click and ORDER BY using the Sort Tab
– Using the Joins Tab of Nexus
– The SQL Tab reflects the changes we make in all other Tabs
– WHERE Tab shows Tables Indexes
– The Answer Set Tab shows the Results
– The Metadata Tab shows Metadata
– Nexus Makes a View look like a Table
– Nexus Joins Views to other Views in seconds
– Nexus can Cube a View and Join to all other related Views
– Nexus Cubes Views in Seconds
– The Cube SQL created on Views is done Automagically
– Views with the Underlying Indexes of the Base Tables
– WHERE Tab shows Views Underlying Base Table Indexes
– After an Answer Set Returns you can do many things
– After an Answer Set Returns Perform OLAP Calculations
– After an Answer Set Returns you can Graph and Chart
– Bisualize Builds Dynamic Charts
– Saving an Answer Set in another Format
– Sandbox – How to Create a Sandbox (1 of 5)
– How to Compress the Tables in an Entire Database
– How to Compress A Single Table
– Let Nexus Build your Load Scripts with SmartScript
– SmartScript Building a FastLoad
– Convert Teradata DDL to Another Database Vendor
– Replicate Data from One Teradata System to Another
– Compare and Synchronize with SmartSync
– Compare Data on Two tables with SmartSync
– Compare Data and See the Results with SmartSync
– Synchronize the Data with SmartSync
– Play Games and Learn Teradata SQL
– Play Games and Learn Teradata Architecture
The Old Nexus Logo
This was the original logo of the Nexus, but after partnering with Microsoft to build the next generation Query Tool they created the new logo for us (seen on the next page).
The New Nexus Logo
Download the Nexus for a free trial at www.CoffingDW.com
Microsoft created the new logo for us to represent the Nexus as an Enterprise tool.
Watch the Video on the new Nexus Super Join Builder
Tom Coffing has been one of the first teachers of Teradata data warehousing in remote places like Johannesburg Africa. In Africa, after Apartheid Tom trained both black and white students together which was a first time experience for many of the students.
Click on the link below or place it in your browser and watch the video on the Nexus Super Join builder.
http://www.coffingdw.com/TbasicsV12/Nexus.wmv
How to Customize your System Tree View
Introducing the new Nexus Super Join Builder
The Super Join Builder has 9 Tabs that are all there for your single query. Manipulate through the tabs and make changes to your query. The Super Join Builder does amazing things!
Define your Joins and tell Nexus to “Add and Remember Me”
Drag two tables inside Nexus and define the Join Columns. Click on “Add and Remember Join” and its in the Join Menu permanently. Check out the cool Join Menu on the next page.
Nexus knows what Tables Join together
How would you like to NEVER write another Join again? The Nexus makes that possible with the Nexus Super Join Builder. Just Right Click on a table and the menu tells you what other table(s) are eligible in the join. Then when you click on the next table Nexus writes the Join SQL automatically.
Nexus Presents Tables and their columns in Color
All Three tables we wish to join appear now with their columns and data types. Each table is a different color so when users utilize the other Tabs they see the columns in color to indicate which table there from. The Join SQL is already built, but we can use the other tabs to customize the SQL. The next picture will show the SQL already built!
Nexus Builds your SQL Automagically
By clicking on the SQL Tab you can see the SQL generated by the Nexus. All we did was click on the Objects Tab to select the tables and then we clicked on the columns we wanted on our report. In seconds the Nexus built this beautiful SQL.
Nexus can Cube a Table and Join to Everything Possible
When you click on the left side of a table in the Objects Tab and select CUBE the Nexus will create the SQL to join every table in the entire relationship together. Watch what happens on the next slide.
Nexus can Cube a Table and Join to Everything Possible
After hitting Cube with all Columns all tables in the relationship are joined.
The Cube SQL created Automagically
Manipulate the Columns with the Columns Tab
Rearrange the columns in the SELECT list by clicking and dragging. Control Click highlights multiple columns and Shift Click highlights an entire range of columns.
Single Click and ORDER BY using the Sort Tab
Double-Click or drag any column to sort by that column(s) in ASC or DESC mode.
Using the Joins Tab of Nexus
The Joins Tab shows all Join Conditions and allows you to change them to Outer Joins.
The SQL Tab reflects the changes we make in all other Tabs
This is the SQL generated after we changed our Join to a Right Outer Join in the Joins Tab and in the Sort Tab dragged columns Claim_Id and Street for our ORDER BY.
WHERE Tab shows Tables Indexes
The Answer Set Tab shows the Results
To get here we put the Sales_Table inside the Super Join Builder. Then we selected the columns we wanted and then went to the Analytics Tab. There we built our RANK report and pressed Execute. Then (above) we received the Answer Set.
The Answer Set Tab shows the Results
To get here we put the Sales_Table inside the Super Join Builder. Then we selected the columns we wanted and then went to the Analytics Tab. There we built our RANK report and pressed Execute. Then (above) we received the Answer Set.
The Answer Set Tab shows the Results
The Answer Set Tab shows the Results
The Metadata Tab shows Metadata
The Metadata tab allows a company to create Metadata for each table or object. This can be a word document, Power Point slide or whatever the company decides.
Nexus Makes a View look like a Table
Views in the Super Join Builder look like tables and can join to other views in a menu-driven fashion. Views looking like tables is brilliant and quite beneficial.
Nexus Joins Views to other Views in seconds
Views looking like tables is brilliant and quite beneficial. We can even join views to other views in seconds and pick the columns we want on our report.
Nexus can Cube a View and Join to all other related Views
When you click on the left side of a View in the Objects Tab and select CUBE the Nexus will create the SQL to join every View in the entire relationship together. Watch what happens on the next slide.
Nexus Cubes Views in Seconds
After hitting Cube with all Columns all Views in the relationship are joined.
The Cube SQL created on Views is done Automagically
Views with the Underlying Indexes of the Base Tables
We have just placed a view called Employee_V inside the Super Join Builder. We have selected all the columns. Now watch what happens when we go to the WHERE Tab.
WHERE Tab shows Views Underlying Base Table Indexes
After an Answer Set Returns you can do many things
After the Answer Set returns the Nexus allows you to manipulate the results further. You can perform OLAP calculations, Rank and many more.
After an Answer Set Returns Perform OLAP Calculations
We just performed OLAP on the answer set. Now the report comes (above in yellow), but what is amazing is that all calculations were performed internally by Nexus.
After an Answer Set Returns you can Graph and Chart
After the Answer Set returns the Nexus allows you to manipulate the results further. You can use Bisualize to Graph and Chart your answer set dynamically.
Bisualize
Bisualize Builds Dynamic Charts
Saving an Answer Set in another Format
You can save an Answer Set directly to Excel or to many different formats.
Sandbox – How to Create a Sandbox (1 of 5)
Sandbox – Join Answer Sets from different Systems (2 of 5)
Sandbox – Join Answer Sets from different Systems (3 of 5)
Sandbox – Join Answer Sets from different Systems (4 of 5)
Sandbox – Join Answer Sets from different Systems (5 of 5)
How to Compress the Tables in an Entire Database
How to Compress the Tables in an Entire Database
How to Compress A Single Table
How to Compress a Single Table
Compression Reports
Let Nexus Build your Load Scripts with SmartScript
SmartScript Building a FastLoad
SmartScript Building a FastLoad
The Teradata DBA Launchpad
Convert Teradata DDL to Another Database Vendor
Replicate Data from One Teradata System to Another
Compare and Synchronize with SmartSync
Compare Data on Two tables with SmartSync
Compare Data and See the Results with SmartSync
Synchronize the Data with SmartSync
Compare DDL with SmartSync
Play Games and Learn Teradata SQL
Just go to the DBA button and then select Tera-Trivia. You will have the option of playing Teradata Basics or Teradata SQL games. These are very advanced games designed to test and teach your Teradata skills.
Play Games and Learn Teradata Architecture
Just go to the DBA button and then select Tera-Trivia. You will have the option of playing Teradata Basics or Teradata SQL games. These are very advanced games designed to test and teach your Teradata skills.
Watch the Video on India
Tom Coffing has taught Teradata classes in China, India, Africa, Malaysia, Europe, Canada, Mexico and throughout the United States. Tom has taught over 1,000 live Teradata classes in his 20 years of Teradata experience.
Click on the link below or place it in your browser and watch the video on Tera-Tom's trip to India