GLOSSARY :
1) FACTLESS FACT TABLE :
Suppose a retail store wants to find out the products that has not been sold for last 2 weeks then this factless fact table will help us in identifying the same.
There are 100 products in a store and all those products are stored in these Factess fact table .
The sales table would have all the daily transactions of all the porducts sold in a aggregated manner.
For EX:
The Factless fact table contains only keys and no measures.If we just do a minus on the factless fact table (table on the right in the sheet) to the SALES table then we would find the number of products being unsold in this period.
2) General Scenarios to test in a DWH testing project :
3)Places where defect can be caught in DIMENSION TABLE :
Scenario :
A bank needs to track a person's residence and the period he has stayed .If he has changed his house frequently then the bank also needs to track the records of the person he has stayed and is staying .
There are times where they might be data discrepancy like a person staying in a particular place (i,e)EFF_D of the stay of the person might be lesser than EXPR_D of the place he has stayed before.To find such records in Teradata one needs to trick the query .The query would find data discrepancies where EFF_D of a succeding record is greater than preceeding record EXPR_D.
2)A person can be ACTIVE only once meaning he can be residing in only one location .Hence there cannot be a person where a person has two ACTV_F='Y' records.
Please refer the data here :
1) FACTLESS FACT TABLE :
Suppose a retail store wants to find out the products that has not been sold for last 2 weeks then this factless fact table will help us in identifying the same.
There are 100 products in a store and all those products are stored in these Factess fact table .
The sales table would have all the daily transactions of all the porducts sold in a aggregated manner.
For EX:
The Factless fact table contains only keys and no measures.If we just do a minus on the factless fact table (table on the right in the sheet) to the SALES table then we would find the number of products being unsold in this period.
3)Places where defect can be caught in DIMENSION TABLE :
Scenario :
A bank needs to track a person's residence and the period he has stayed .If he has changed his house frequently then the bank also needs to track the records of the person he has stayed and is staying .
There are times where they might be data discrepancy like a person staying in a particular place (i,e)EFF_D of the stay of the person might be lesser than EXPR_D of the place he has stayed before.To find such records in Teradata one needs to trick the query .The query would find data discrepancies where EFF_D of a succeding record is greater than preceeding record EXPR_D.
2)A person can be ACTIVE only once meaning he can be residing in only one location .Hence there cannot be a person where a person has two ACTV_F='Y' records.
Select a.*, (a.expr_d - a.derived_expr_d)
from
(
Select ssn_no, actv_f, del_f, eff_d , expr_d,
coalesce ( max(eff_d) over (partition by ssn_no order by ssn_no, eff_d rows between 1 following and 1 following) - 1, cast('12-31-9999'as DATE
format'mm-dd-yyyy')) as derived_expr_d
from tab_name) a
where a.derived_expr_d <> cast('12-31-9999'as DATE format'mm-dd-yyyy')
Please refer the data here :
No comments:
Post a Comment