SQL Data Warehouse stored procedure location

2018-10-22 21:02:55

We are building a Kimball data warehouse. We have a Staging Database, and Final Datawarehouse.

We have a stored procedure which creates the table CustomerTransaction.

Where is the best location to place the stored procedure? In the Staging Database or the Datawarehouse? Or what is the positives/negatives of each location? I just started creating data warehouses and trying to learn.

create procedure dbo.FactCustomerTransactionImport -- should this be in StagingDB or datawarehouse DB?

as

insert into DWFinal.dbo.DimCustomerTransaction

(

CustomerId,

Quantity,

Price,

Amount

)

select

CustomerId,

Quantity,

Price,

Quantity * Price as Amount

from StagingDB.dbo.Customer