How To Use Teradata Warehouse Miner With Aster

Learn Data Science
Not applicable

Teradata Warehouse Miner (TWM) is a PC-based application that allows users to perform data profiling and data mining in-database via a user-friendly GUI interface. As of version 5.4.0 TWM can now run on top of the Aster Database as well - allowing Aster users access to data profiling and ADS creation functions. 


How To Get and Setup TWM


A express version of TWM is free for download at the Teradata Developer Exchange (you will need to register for free to get access): Teradata Warehouse Miner - Express Edition | Teradata Downloads At the moment TWM is supported only on Windows PCs. 


Once you have installed TWM, you will also need to install the Aster ODBC Driver. You can find the ODBC driver among the Aster Client Tools For Windows: Aster Client Tools for Windows | Teradata Downloads For Aster Express - I'd recommend using the appropriate version (6.1) to ensure compatibility. Depending on your PC you may need either the 64-bit or the 32-bit nCluster ODBC driver. The 32-bit driver is among the "i386" packages while the 64-bit one is among the "x8664" packages. 


Once ODBC driver is set up you will need to setup your Aster DB as an ODBC data source using the Windows ODBC Manager. Search for ODBC on Windows and add Aster as a source then specify your DB credentials.  



Once done, on TWM, you need to activate the connection through "Tools">"Connection Properties" and specify schemas where TWM will be populating its work. I usually just use "twm_demo" as a default schema - but you might also want specific schemas for Metadata, Publish, and Advertise. You can also just specify "public" but that could clutter your DB later. 


(Note: if you are not using "public" you need to create these specific SCHEMAS in Aster DB beforehand, e.g. 'CREATE SCHEMA twm_demo;', etc. or TWM will not be able to find them.)



If this is the first time you are specifying schemas, go to the option "Tools">"Metadata Creation" as well as "Tools">"Advertise Tables Creation" so TWM can create its work tables. Moving forward you can run through the "Maintenance" options so TWM can update these. 



Finally, go to the start menu folder "Teradata Warehouse Miner" and click the option to "Install or Uninstall Aster Library" to install the Aster Profiler SQL-MR and follow the command line prompts to deploy the Profiler.Zip SQL-MR file into your Aster DB (make sure your Aster DB is up and running when you do this). There is also an option called "Load Aster Demo Data" which is a sample data set (a bank) to play with. 


After the above your TWM should be good to go. 


Using TWM with Aster

1. Data Profiling and ADS Creation


Warehouse Miner comes with a plethora of automated profiling and exploration tools complete with some visualizations.


Each of these tools are self-contained GUI-driven apps that run SQL behind the scenes to help you analyze and visualize your Aster tables! 

2. SQL-MR Code Helper


Those looking for another way to secretly be a SQL-MR expert without having the User Guide and Foundation Guide PDFs open on your desktop - look no further than the Variable Creation node on TWM. You can instantly get code examples of all up-to-date SQL-MR commands. 


In addition - those not familiar with the PostgreSQL syntax for some obscure windowing functions and regular expressions - you can either build them with the (initially confusing) GUI then later check the SQL for them too.



3. Project Explorer


TWM allows you to open projects by default -and each analysis you conduct becomes a folder in your project. Over time, especially with the prolific use of the "Free Form SQL" node and some smart SQL scripting - you can come up with rather complex workflows - which are all tagged, curated, and arranged any way you want. It's not the GUI-based workflow that everyone is clamoring for, but it's definitely a step-up from managing all your SQL scripting on a notepad. Best of all - each node can be executed in parallel - without bothering an existing query. Each node opens a separate connection to the DB when running.



If you want to create a slightly different version of a query - simply "clone" a node, rename it, and edit the contents! Nice and neat.


4. Metadata Repository


Best of all - all the above activity in TWM is stored as metadata in the AsterDB itself! This allows easy retrieval of your project regardless of the workstation you use - and also allows sharing of work amongst multiple users - if everyone points to the same metadata repository in their TWM instance. This can be a great way to collaborate on your Aster usage across teams. 



All in all TWM is a great tool that complements the power of Aster with a friendly front-end and data profiling capabilities.