SriniTera

Differences between Database and USER: A Database or a User can be assigned PERM Space If the Database Store is assigned 10 GB of PERM, that means it can hold up to 10 GB of Permanent Tables.  If the User Nancy is assigned 10 GB of PERM, that means she can hold up to 10 GB of Permanent Tables. A Database or a User can be assigned Spool Space If the Database Store is assigned 10 GB of Spool, that means all users under Store can each run 10 GB queries. If the User Nancy is assigned 10 GB of Spool, that means she can run up to 10 GB queries, and any user created under Nancy will default to 10 GB queries. In Teradata, the only difference between a database and a user is that a user can login and run queries. Roles and Teradata Access rights: Roles simplify database administration by assigning access rights to tables and other objects, and then groups of people with similar job functions (or roles) can access these objects. It is as simple as creating different roles for

#Two most popular kinds of Teradata Utilities that must read: Block level utilities: FastLoad performs Inserts into empty Teradata tables in 64K blocks. MultiLoad loads to populated Teradata tables in 64K blocks. FastExport – Exports data off of Teradata at the 64K block level. Row level utilities: BTEQ – The first Teradata query tool. It imports and exports data and should be used when < 500,000 rows. BTEQ is an excellent report writer.    TPump loads into Empty or Populated Tables and is a lot like MultiLoad except TPump does all of the work at the row level and NOT the block level. It is called TPump because you can turn up or down the pump to increase or decrease the load quantities. How data is stored in Teradata: Teradata stores data in tables much like an Excel spreadsheet.  Each row has many columns. The difference is how Teradata processes the data.  The rows are not stored together like an Excel spreadsheet. Each row is physically separate

1. Difference between Teradata and Other RDBMS? The following advantages Teradata  is having over other RDBMS: - Parallel processing - Shared nothing architecture - Super fast data retrieval - TASM-Teradata active control system controls traffic efficiently - Teradata VIEWPOINT gives DBA and users their own view of data - Excellent data processing options-JOINS, Secondary Indexes, Hash index, Portioned Tables for range queries, 2. Draw the picture of Teradata warehouse? Refer the diagram 3. What are the differences between Star schema and Snow flake schema? Star Schema: In a star schema every dimension will have a primary key. In a star schema, a dimension table will not have any parent table. Whereas in a snowflake schema, a dimension table will have one or more parent tables. Hierarchies for the dimensions are stored in the dimensional table itself in star schema. Whereas hierarchies are broken into separate tables in snowflake schema. These hierarchies

ODS : Operational data source Teradata Load Server:  Where data received from ODS is stored in Teradata load server. RDBMS: This is the database, which interacts with Tearadata Load server.

Usage-1 It returns a rows randomly from Teradata database. Syntax for SAMPLE function: SAMPLE [WITH REPLACEMENT]    [RANDOMIZED ALLOCATION]                  [WHEN <condition>  THEN]                  {<number-of-rows> | <percentage>}    […,<number-of-rows> | <percentage>]                  [ELSE {<number-of-rows> |    <percentage }   END] Usage 2 It allows the user to get absolute number of rows or percentage of rows. Usage 3 It also allows the rows to get from multiple samples How to get random rows from a table: SELECT * from sample_table SAMPLE 5; How to get certain percentage of rows: SELECT * from sample_table SAMPLE .30; How to get multiple SAMPLE data from a single tables with out duplicates: SELECT * from sample_table SAMPLE .20, SAMPLE .30 ORDER BY 1,2; SQL query to get 3 samples with duplicates: So 'WITH REPLACEMENT" you will get duplicate rows: SELECT ST_

Simple SQL query to create a view in Teradata: CREATE View Employee_V AS SELECT      Employee_No            ,First_Name            ,Last_Name            ,Dept_No FROM Employee_Table ; - A view we create to restrict access to certain columns -To restrict access to certain derived columns -To restrict access to join tables -To restrict access to certain rows View is basically in a semantic layer. It supports to Presentation layer. Some time a view can change column names, a view can derive new columns , also aggregate columns. Simple rules for views: - A view should not contain Order By -All aggregate columns must have ALIAS -All derived column must have ALIAS