Hi Everyone,
I am currently struggling to understand where and how to implement business logic & aggregations in SQL database in an optimal way
Currently I am using the following ETL process which takes forever and is quite costly. Therefore I would appreciate to get some tips on how to optimize it
ETL process:
A. SQL Azure database:
1- Temporary tables: Store recent data (last 6 weeks)
2- Incremental load of data from the Temporary tables into the dbo tables
3- dbo tables (Fact tables): Store historical sales data (Last 3 years)
4- Daily View: Built on top of dbo tables. It implements business logic on daily data (aggregates data & Join data with the dimension tables)
5- Weekly View: Built on top of Daily View and another dbo table. It calculate weekly rolling average and other heavy calculations
B. Power BI:
6- Query the Daily View & Weekly View into one single PBIX. file
