Post reply

Data preparation for reporting

  • August 29, 2020 at 4:05 am

    #3784578

    Hi team,

    I was asked to provide metrics on different attributes at customer level for the period of last 12 month.

    Volume expectation of unique customer id : ~2 mil

    Metrics field count : 40

    And each field requires calculation from 1-2 sub tables for the same period

    Suggestions i have to implement this in sp and schedule it to run on first of every month.

    Option 1:

    Create Temp table and populate it with unique customer id (~2mil) and basic details and keep customer id as indexed

    Create separate temp table for each field calculation and perform aggregation on customer id and indexed on cust id

    Later extract result using left join

    Select custid, metric1, metric2.. metric40  from temp1

    left join temp2 on temp1.custid = temp1.custid

    .. this goes for 40 temp table left join

    Issues: 40 temp tables, 40 left joins. I cant use table variables due to data volume.

    Option2:

    Create Temp table with cust id , metrics field value as null and populate it with unique customer id (~2mil) and basic details and keep customer id as indexed

    Using cte to do aggregation and update temp table with metric result

    Like this do for rest 40 fields

    Final 1 select stmt from customer temp table

    Issues: update time temp table

    Option3 :

    Any other

    Assumptions:

    Reasons for temp table or CTE - Sub tables need to filter records for the specific period as they contain very huge amount of transaction data

    • This topic was modified 3 years, 8 months ago by  Mia2022. Reason: Correcting question

  • August 30, 2020 at 4:10 am

    #3784665

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

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply