Need SQL examples to validate historical data population

[SarahGT]

Hello All,

I'm new to this site and pretty new at using SQL. Here is the scenario: In trying to improve Rpting performance a DBA has taken commonly used attributes from DIMENSION_TABLE_Z, which are ID fields, and has added them to FACT_TABLE_A. The DBA's actions created and populated the ID's historically for the FACT_TABLE_A. I need to come up with a query to test if all the historical data was loaded properly. For instance, if I've added ORDER_ID to the FACT_TABLE, and populated it with historical data from the ORDER_ID field in DIMENSION_TABLE Z how would I test to see if all the fields were loaded using SQL?

 

[JamesLean]

SELECT * FROM fact_table
WHERE order_id IS NULL

??

--James

 

[PHV]

Something like this ?
SELECT Z.*
FROM DIMENSION_TABLE_Z Z LEFT JOIN FACT_TABLE_A A ON Z.ORDER_ID = A.ORDER_ID
WHERE A.ORDER_ID IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SarahGT]

Do you think this will be an efficient way to test considering the records are being populated into the fact table are in the millions?

Could I export to excel data from the Dimension table(SOURCE) and export the same data from the fact table(TARGET) and add an excel calculation to check for possible missed data? I believe I've seen someone do a VLOOKUP before, would this work? If so what is the logic behind this? I do apologize for my lack of knowledge.

-Sarah

 

[SarahGT]

PHV,
If I run your query and A.ORDER_ID has no NULL values in the FACT_TABLE will I get any results back from the query?

-Shawn

 

[PHV]

No, this query returns all rows from DIMENSION_TABLE_Z with an ORDER_ID not in the FACT_TABLE.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SarahGT]

Okay, so the test would be successful if no rows were returned considering the fact that all A.ORDER_ID was loaded properly from the source DIMENSION_TABLE_Z.ORDER_ID? In the event that the test fails I would get back Z.ORDER_IDs that do not exist
in the FACT TABLE. In other words please clarify query results if the test passes and the query results if the test fails? I do appreciate your responses and I am very thankful.

-SarahGT

 

[JamesLean]

You haven't really defined what you actually want to test?

If you just want to make sure that all rows in fact_table have been populated with some value then my original query will do that (if any results are returned then some rows haven't been populated).

If you need to check that any value that has been populated into fact_table actually appears in the dimension_table then you need to reverse PHV's query:

SELECT f.*
FROM fact_table f LEFT JOIN dimension_table d ON f.order_id = d.order_id
WHERE d.order_id IS NULL

Again, if any rows are returned then you have values in fact_table that are not in dimension_table.

--James