MDX ve SCOPE

Problem su:

Elimizde bir veri seti var, ve biz bu veri setinden bir measure olursturmak istiyoruz. Ancak soyle bir problem var bu measure farkli seviyelerde (granuality) farkli hesaplaniyor.

Ornegin veri yapimiz soyle olsun.

Product Dim
Business
Commodity
ItemNo

Customer Dim
CustomerID

factTable Item, Date, Customer, Sales

Ve buradan soyle bir yere olasmaya calisalim.

Lets say
---For Item
Select ItemNo, Sum(Sales)*2 as M1
From factTable
Group By ItemNo

--For Customer
Select CustomerID, Sum (Sales) * 3 M2
From factTable
Group By ItemNo

--For atomic level
Select ItemNo, CustomerID, Sales * 4 M3
From factTable


ve bu D1'in toplami da BI sistemimizdeki yeni mesaure'miz olsun.

Ulasmak istedigim yer her ozet seviyesi icin yeni bir Measure (M1,M2,M3) yaratmak degil sadece bir Measure (M1) yaratip bunu secilen seviyeye gore dogru ozet tablodan getirmek.

Soyle bir yaklasimla sonucu cozebildim.

1. Once her bir seviye icin bir Mesaure olusturalim

M1= M1 From Item Level
M2 = M2 From Customer Level
M3 = M3 From factTable

2. Simde de bu ucunu bir araya getircek bir M4 olusturalim

Create Member Measures.M4 As NULL

Scope (Measures.M4)
this = M3
Scope (Item, Measures.M4)
this = M1
end Scope
Scope (Customer, Measures.M4)
this = M2
end Scope
End Scope

boyle biseyler syntax dogru degil yaklasimi gostermek icin psudo olarak yazilmistir.

kolay gelsin,

SQL de Ay araliklari ile ugrasmak, relative month references

Raporlama demek tarih araligi demektir, zaman zaman bu tarih araliklarini dinamik bir sekilde vermek, verilen tarih referansi ile baska bir tarihe ulasmak, gerekebilir.

Subat 2009'dan yola cikip Gecen Yil Onceki Ay mantigi ile Ocak 2008'e ulasmak isteyebiliriz.

Iste bunun icin, Current Year This Month, Current Month Last Year, Previous Month Last Year gibi ise yarar birkac fonksiyon.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetCurrentMonthLastYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS

/******************************************

Name :
fn_GetCurrentMonthLastYear

Description:
Returns the first day of the given month
of previous year of given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'





RETURN(dateadd(month,datediff(month,@basedate,@RefDate)-12,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetCurrentMonthThisYear] Script Date: 02/05/2009 09:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetCurrentMonthThisYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS


/******************************************

Name :
fn_GetCurrentMonthThisYear

Description:
Returns the first day of the given month
given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'


RETURN(dateadd(month,datediff(month,@basedate,@RefDate),@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetFirstMonthLastYear] Script Date: 02/05/2009 09:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetFirstMonthLastYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS

/******************************************

Name :
fn_GetFirstMonthLastYear

Description:
Returns the January 1st of previous year of
given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/


BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'






RETURN(dateadd(year,datediff(year,@basedate,@RefDate)-1,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetFirstMonthThisYear] Script Date: 02/05/2009 09:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetFirstMonthThisYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS


/******************************************

Name :
fn_GetFirstMonthLastYear

Description:
Returns the January 1st of
given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/


BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'






RETURN(dateadd(year,datediff(year,@basedate,@RefDate),@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetPrevMonthLastYear] Script Date: 02/05/2009 09:20:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_GetPrevMonthLastYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS
/******************************************

Name :
fn_GetPrevMonthLastYear

Description:
Returns the first day of Previous Month of the given month
and previous year of the given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'




RETURN(dateadd(month,datediff(month,@basedate,@RefDate)-13,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetPrevMonthThisYear] Script Date: 02/05/2009 09:20:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE FUNCTION [dbo].[fn_GetPrevMonthThisYear]
(@RefDate smalldatetime )
RETURNS smalldatetime
AS
/******************************************

Name :
fn_GetPrevMonthThisYear

Description:
Returns the first day of Previous Month
of the given year.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 29.09.2006
*******************************************/

BEGIN

declare @basedate smalldatetime
set @basedate = '20000101'


RETURN(dateadd(month,datediff(month,@basedate,@RefDate)-1,@basedate) )
END
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetMonthName] Script Date: 02/05/2009 09:20:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- select dbo.fn_GetMonthName ('20071201',3)





CREATE FUNCTION [dbo].[fn_GetMonthName]
(@RefDate smalldatetime,
@ShortenTo int )
RETURNS varchar (20)
AS

/******************************************

Name :
fn_GetMonthName

Description:
Returns the name of the month of given date.
A short version can also be returned.

Use getdate() while calling this function
to get the current date references.

Used by:
Mostly used in dashboards' source queries.

Version Log
1. Created By EA on 02.10.2007
*******************************************/

BEGIN

IF @ShortenTo <=0 Set @ShortenTo = 20


RETURN Left(DATENAME(month, @RefDate),@ShortenTo)
END
GO