I was wondering what the best steps would be to set up a new model. I want to make mine as automated as possible.
What I have is a SQL database which is updated once per week with sales data. At the minute its approx. 35M rows and counting. I need lookup tables for filtering purposes, one for stores and one for products (all data is available on the sales data query coming from SQL). I have maintained separate excels for these stores and products to capture new products and stores, however this is time consuming and very manual.
Each weekly sales file has approx. 250k rows (each store has a separate line for each product) . There is a lot of copy/paste and remove duplicates to maintain my excel files.
I have tried to reference my sales query, one for the stores and a separate for products (approx. 8500 products and 370 stores). However when I load the data it takes too long as it goes through row by row on the 35M Sales file rows.
Does anyone have any suggestions on how to make this more automated?
Apologies for the overload of info, just trying to make everything as clear as possible.