ETL/DW/CUBE - Performing calculation in ETL/DW based on DYNAMIC dates provided by the user
Performing calculations in ETL/DW to be used in SSAS CUBE
I have two columns that depends on UDF functions in SQL: Performance and ServiceCreditPoint
And here is the example of calculating performance; I pass in an ID, FromDate & EndDate
dbo.fn_cal_sla_performance(2,'01/01/2013','01/26/2013')
And an example of calculating ServiceCreditPoint; I pass in an ID and Performance
dbo.fn_get_Service_Credit_Point(2,Performance)
The question now is how I can have Performance and ServiceCreditPoint in my fact table since performance requires ID, FromDate & EndDate. The FromDate and EndDate are dynamic and supplied by users.
This is a headache for me as the dates are dynamic and I need to view the Performance and ServiceCreditPoint in my SSAS CUBE.
CREATE FUNCTION [dbo].[FN_Get_Service_Credit_Point]
(
@sla_id int,
@Performance decimal(18,3) -- PERFORMANCE
)
RETURNS @SCP TABLE
(
ServiceCreditPoint decimal(18,3) NOT NULL,
ServiceCreditNumber int,
Available varchar(50),
PercentageFrom decimal(18,3),
PercentageTo decimal(18,3),
SLA_ID int,
NoOFServiceCreditPoint int
)
AS
BEGIN
DECLARE @ServiceCreditPoint int
DECLARE @max decimal(18,3)
DECLARE @min decimal(18,3)
SET @ServiceCreditPoint= -1
SET @max = 999999999999999
SET @min = -999999999999999
BEGIN
INSERT INTO @SCP ( ServiceCreditNumber , ServiceCreditPoint,Available,PercentageFrom,PercentageTo,SLA_ID, NoOFServiceCreditPoint )
SELECT TOP 1 [Number], [ServiceCreditPoint],'',PercentageFrom,PercentageTo,SLA_ID , SC.NoOfServiceCreditPoint
FROM [dbo].[tb_service_credit_point]
CROSS APPLY (SELECT COUNT(ServiceCreditPointID) AS NoOFServiceCreditPoint FROM dbo.tb_service_credit_point WHERE SLA_ID = @SLA_ID ) AS SC
WHERE
@Performance >= coalesce([PercentageFrom],0) and @Performance <= coalesce([PercentageTo],1.0e6)
and sla_id = @sla_id
GROUP BY NUMBER, ServiceCreditPoint,PercentageFrom,PercentageTo,SLA_ID,SC.NoOfServiceCreditPoint
order by PercentageFrom desc
RETURN;
END
--SELECT * FROM [dbo].[tb_service_credit_point] WHERE SLA_ID = 2
-- AND @Performance >= coalesce([PercentageFrom],0) and @Performance <= coalesce([PercentageTo],1.0e6)
RETURN;
END
GO
CREATE FUNCTION [dbo].[fn_cal_sla_performaance]
(
@sla_id int,
@FromDate datetime=null,
@ToDate datetime=null
)
RETURNS decimal(18,3)
AS
BEGIN
DECLARE @Total decimal(18,3)
DECLARE @TotalFailed decimal(18,3)
IF @FromDate is not NULL and @ToDate is not NULL
BEGIN
SET @Total = (SELECT count(sla_id) FROM [dbo].[tbl_consolidated_sla_b] where convert(date,start_time) >=@FromDate AND convert(date,end_time) <=@ToDate and sla_id = @sla_id )
SET @TotalFailed = (SELECT count(sla_id) FROM [dbo].[tbl_consolidated_sla_b] where convert(date,start_time) >=@FromDate AND convert(date,end_time) <=@ToDate and sla_id = @sla_id and slastatus='Fail' )
END
ELSE
BEGIN
SET @Total = (SELECT count(sla_id) FROM [RSA_PM].[dbo].[tbl_consolidated_sla_b] where sla_id = @sla_id )
SET @TotalFailed = (SELECT count(sla_id) FROM [RSA_PM].[dbo].[tbl_consolidated_sla_b] where sla_id = @sla_id and slastatus='Fail' )
END
RETURN @Total -(@TotalFailed/ @Total )
END
GO
.
social.technet.microsoft.com/Forums
