Model Setup advice

Hi All,

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.

Thanks.

@Nurry90 You should try SSIS.

2 Likes

I second @AntrikshSharma‘s recommendation. Also, a SSAS cube might help with working with the amount of data for reporting purposes or if you want to do any data mining.

1 Like

I have no idea how to use either of these? I have just taught myself how to use Power BI and basic SQL. Not sure if I have the time to go through another program. Is it difficult to set up?

Or any other suggestions?

Hi @Nurry90

In Power Query, even if you refrence Query 1 in Query 2. While evaluating, Query 2, Query 1 will be Recalculated as explained in below article.

One option is to use DataFlows if you have access to Power BI Service.

Second option is to create views inside SQL for Product and Store tables , something like “Select Distinct Product from Sales”

This way you don’t need to do manual extraction or referencing. In case some transformations are required, you can look for ETL tools like SSIS, ADF, Knime as advised by others. Take help from your IT team.

Thanks
Ankit J

Hi @Nurry90 , did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @ankit,

Thank you for your help. If I were to create a “View” in SQL, how would I connect these new tables to my model? I still need to learn how to do this, so any quick guidance is appreciated :slight_smile:

Hi @Nurry90

That Magic happens inside Power Query. If you select query and go to Advanced Editor, there you can change source from Excel to SqL . You can get examples Online and this Forum also.

if not Once you have Views created, you can post them here and we can help you.

Thanks
Ankit J

Thank you all for your suggestions, looks like I have a good bit more learning ahead of me!