I am currently working on a project that involves approx. 23m records per month that I will have to append to a Master Table that I have already created. What I am trying to do is set up some variables that will take the daily data from the previous day and create a temporary monthly file and append it to the Master Table to be used for insights into how the current month is trending vs history. But at month end, delete that temporary file (that is appended to my Master Table) and extract a full month of data to append to Master Table for final month end reporting. Since I am still in testing phase the below script is my two main tables I would have to do it to both tables. I don't want to left join the two together. The first table is wSAP_FACT_V, the second is Wsap_Dly_Trans_GL_V. I join them by the Dly_Trans_Row_Id so that I can bring in additional details.
The question is, how do I create a variable so that I can bring in the data from each table by day, then at month end by full month, store it, then call the saved QVD into another app for optimizing the data this way I can transform, and create other fields based on the data on the backend so that front end users will not have to create master items on the front end. Like I said before, 1 month of data is approx. 23m each and I want to make sure that the final app doesn't have any latency issues.
Below are the scripts that I am using to create my original QVDs so that I don't have to hit live databases during the Transform process.
Any help would be greatly appreciated.
Thank you,
Chris
wSAP_FACT_V:
select * from FINMAP_PRD_SECVM.wSAP_FACT_V
WHERE WSAP_POST_MTH='08/01/2020' or WSAP_POST_MTH='09/01/2020';
STORE * FROM wSAP_FACT_V INTO 'LIB://EQUIP/EQUIP_REPORTING_QVD/wSAP_FACT_V.QVD'(QVD);
DROP TABLE wSAP_FACT_V;
Wsap_Dly_Trans_Gl_V:
select Dly_Trans_Row_Id,
Vz2_Segmt_Cd,
If(IsNull(Vz2_Segmt_Cd), 'MISSING', Vz2_Segmt_Cd) AS DLY_TRANS_GL_SEGMT_CD2,
PS_MKT_CD,
SHIP_TO_CD,
GL_ACCT_NUM,
ITEM_CD
from Finmap_Prd_Allvm.Wsap_Dly_Trans_Gl_V
WHERE post_dt >='2020-08-01' and post_dt<='2020-09-30';
STORE * FROM Wsap_Dly_Trans_Gl_V INTO 'LIB://EQUIP/EQUIP_REPORTING_QVD/Wsap_Dly_Trans_Gl_V.QVD'(QVD);
DROP TABLE Wsap_Dly_Trans_Gl_V;
