DW: Teradata Data warehousing Pictorial Diagram

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.

Comments

Post a comment

Popular posts from this blog

SQL for Quantile Function in Teradata

Image
A Quantile is used to divide rows into a number of categories or grouping of roughly the same number of rows in each group.
Quantile Function The percentile is the QUANTILE most commonly used in business. This means that the request is based on a value of 100 for the number of partitions

SELECT Product_ID1, Sales_Date1, Daily_Sales1
,QUANTILE(100, Daily_Sales1 ) AS "Quantile1"
FROM Sales_Table
WHERE Product_ID1 < 3000
AND Sales_Date1 > 1000930 ;

The calculation is percentile for every row in the Sales table on Daily sales.
Read more

3 Uses of SAMPLE function in Teradata

Image
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_ID         ,CC_ID         ,SSID  FROM  sample_table  SAMPLE WITH REPLACEMENT 8, 8,…
Read more

3 Types of Temporal Tables in Teradata

Image
What is a Temporal Database Definition - Temporal – the ability to store all states of a given set of data (a database row), as part of the query select a point in time to reference the data.

Examples:
What was this account balance (share price, inventory level, asset value, etc) on this data?What data went into the calculation on 11/3/07, and what adjustments were made in 2008?On this historic data, what was the service level (contract status, customer value, insurance policy coverage) for said customer? Three Types of Temporal Tables 1. Valid Time tables When a fact is true in the modeled reality. User specified times. 2. Transaction time Tables When a fact is stored in the database. System maintained time, no user control.
3. Bitemporal Tables Both Transaction Time and Valid Time
User defined Time User can add time period columns, and take advantage of the added temporal operators Database does not enforce any rules on user defined time columns.
Temporal Query Provides list of mem…
Read more