Fact Table Agreggate with Sum

• rafamaniac

  Old Hand

  Points: 359

  More actions

  October 23, 2022 at 11:24 am

  #4103268

  I need help populate de Fact Table in my DW. i need to sum the quantity and other columns (this is just a small part of the data source) which has to be grouped by Family,SubFamily and SubSubFamily. I generate a surragate key for the sales dimension but when i populate the fact table  i would need to left join with the natural key, this case sales_ID. the problem is that the sum then won't be correct because it can't group by the family since i am using a sales_ID (BK_sales in the Sales Dimension) which is unique for each row. what am i missing here? sorry i am new to DW modeling.

  insert into [dbo].[Fact_Stg_sales] ([FK_Data],[FK_Seller],[FK_sales],[Quantity]) select [BK_Data],ISNULL([SK_seller],0),ISNULL([SK_sales],0),sum(Quantity) as Quantity from [dbo].[Report_CT] t1 left join
  [dbo].[Dim_Stg_seller] s1 on t1.[cod_agv] =s1.[BK_seller] left join Dim_Stg_sales t2 on t1.sales_ID = t2.[BK_sales] left join Dim_Stg_Data t3 on cast(FORMAT(t1.data_ref,'yyyyMMdd') as int) = t3.[BK_Data]  group by [BK_Data],[SK_seller],[SK_sales] order by BK_Data asc

  

  • This topic was modified 2 years, 1 month ago by  rafamaniac.

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  October 24, 2022 at 12:10 pm

  #4103564

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• October 27, 2022 at 8:44 am

  This was removed by the editor as SPAM