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

%3CLINGO-SUB%20id%3D%22lingo-sub-1458412%22%20slang%3D%22en-US%22%3EWhat%20is%20the%20best%20practices%20to%20implement%20business%20logic%20in%20SQL%20database%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1458412%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20am%20currently%20struggling%20to%20understand%20where%20and%20how%20to%20implement%20business%20logic%20%26amp%3B%20aggregations%20in%20SQL%20database%20in%20an%20optimal%20way%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECurrently%20I%20am%20using%20the%20following%20ETL%20process%20which%20takes%20forever%20and%20is%20quite%20costly.%20Therefore%20I%20would%20appreciate%20to%20get%20some%20tips%20on%20how%20to%20optimize%20it%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EETL%20process%3A%3CBR%20%2F%3EA.%20%3CU%3ESQL%20Azure%20database%3A%3C%2FU%3E%3C%2FP%3E%3CP%3E1-%20Temporary%20tables%3A%20Store%20recent%20data%20(last%206%20weeks)%3CBR%20%2F%3E2-%20Incremental%20load%20of%20data%20from%20the%20Temporary%20tables%20into%20the%20dbo%20tables%3CBR%20%2F%3E3-%20dbo%20tables%20(Fact%20tables)%3A%20Store%20historical%20sales%20data%20(Last%203%20years)%3C%2FP%3E%3CP%3E4-%20Daily%20View%3A%20Built%20on%20top%20of%20dbo%20tables.%20It%20implements%20business%20logic%20on%20daily%20data%20(aggregates%20data%20%26amp%3B%20Join%20data%20with%20the%20dimension%20tables)%3C%2FP%3E%3CP%3E5-%20Weekly%20View%3A%20Built%20on%20top%20of%20Daily%20View%20and%20another%20dbo%20table.%20It%20calculate%20weekly%20rolling%20average%20and%20other%20heavy%20calculations%3CBR%20%2F%3E%3CBR%20%2F%3EB.%20%3CU%3EPower%20BI%3A%3C%2FU%3E%3CBR%20%2F%3E6-%20Query%20the%20Daily%20View%20%26amp%3B%20Weekly%20View%20into%20one%20single%20PBIX.%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1458412%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBusiness%20Intelligence%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EData%20Warehouse%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New 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