Different Granularity KPIs in a single table

• etl2016

  Default port

  Points: 1459

  More actions

  January 12, 2016 at 8:17 am

  #303630

  Hi,

  I have a scenario to load an Aggregate table. Let's take Adventure works for case-study.

  I am required to populate 1 record in Agg table for EACH line-of-business PER each day, say:

  Date InternetSales RetailSales 3rdLineofBusiness3rd_LOB_Amount

  20160101 300.20 514.23 Flavor125.00

  20160101 300.20 514.23 Flavor2 89.00

  20160101 300.20 514.23 Flavor3 76.30

  From above sample, it is evident that 3rd line of business has lower granularity and to accommodate that, we are inviting repetitions for InternetSales and RetailSales. Third column and fourth column are actually telling the sales story of the same line of business (but classified into three areas)

  Is there a way to avoid this repetitions, and have only 1 record for each date and still accounting for 3rd line of business's granularity of 3?

  any denormalisation?

  thank you

• Hugo Kornelis

  SSC Guru

  Points: 64748

  More actions

  January 12, 2016 at 5:02 pm

  #1851450

  I don't understand most of the terms you use about your business, but based on the sample data I think your table design is not normalized.

  You'll want to have two tables. One with Date, InternetSales, and RetailSales; the second with Date, 3rdLineofBusiness, and 3rl_LOB_Amount.

  ---

  Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
  Visit my SQL Server blog: https://sqlserverfast.com/blog/
  SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/