What is the best practices to implement business logic in SQL database?

Copper Contributor

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


0 Replies