If possible, you need to get rid of the massive GROUP BY (and also reduce the number of expensive joins required); that means using a subquery on CDBF.

I've taken my best stab at it below, but you will likely have to touch it up to get it to work properly.

Btw, you do know that "DTA" stands for Don't Trust Anything it says, don't you 🙂

DECLARE @FromDate datetime = '1-march-2012'

DECLARE @ToDate datetime = '31-march-2012'

SELECT

DT.MO_OF_YR_NM [Month],

DT.MO_OF_YR [month_No],

DT.YR [Year],

DT.MIC_WK_OF_MO_NM [Week],

DT.DT [Date],

CD.CSTMR_NM [Organisation],cd.CSTMR_KEY,

PD.BS_LN_NM [Business Line],

ED.EMPE_NM [Sales Executive],

CDBF.[Total no of Lines],

CDBF.[Total No Of Unpaid Lines],

CDBF.[Total Amount Due]

FROM DT_DIM DT with (NOLOCK)

INNER JOIN (

SELECT

CDBF2.FCT_DT_KEY, CDBF2.AR_BHVR_KEY, CDBF2.CNTRCT_DD, CDBF2.CSTMR_KEY,

COUNT(DISTINCT CDBF.CSTMR_KEY) [Total no of Lines],

(CASE WHEN SUM(CDBF.BLNC)>0 THEN COUNT(DISTINCT CDBF.CSTMR_KEY) ELSE 0 END) [Total No Of Unpaid Lines],

SUM(CDBF.BLNC) [Total Amount Due]

FROM CSTMR_DUE_BLNC_FCT CDBF2 WITH (NOLOCK)

INNER JOIN DT_DIM DT2 WITH (NOLOCK) ON

DT2.DT_KEY = CDBF2.FCT_DT_KEY

GROUP BY

CDBF2.FCT_DT_KEY, CDBF2.AR_BHVR_KEY, CDBF2.CNTRCT_DD, CDBF2.CSTMR_KEY

) AS CDBF ON DT.DT_KEY=CDBF.FCT_DT_KEY

Inner Join CSTMR_DIM CD with (NOLOCK) ON CD.CSTMR_KEY=CDBF.CSTMR_KEY

Inner join AR_BHVR_DIM ABD with (NOLOCK) on ABD.AR_BHVR_KEY = CDBF.AR_BHVR_KEY

Inner join PD_DIM PD with (NOLOCK) on PD.PD_KEY = ABD.PD_KEY

Inner Join EMPE_DIM ED with (NOLOCK) ON ED.EMPE_KEY=ABD.EMPE_KEY

WHERE DT.DT>=@FromDate AND DT.DT<=@ToDate

--AND CD.CSTMR_NM IN (@Organisation)

--AND PD.BS_LN_NM IN (@BusinessLine)

--AND ED.EMPE_NM IN (@SalesExecutive)

ORDER BY DT.MO_OF_YR