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

Excel' de tekrarlayan kayitlari bulmak,

T-SQL kullanarak bir tabloda ki kayitlari bulmak icin bir cok yontem var, bunlardan bir tanesini de buraya yollamistim. Ama zaman zaman bu tekrarlayan kayitlari henuz SQL ortamina gelmeden bulmamiz gerekir. IT birimlerinde calisan herkesten muhakkak bir kullanicisi bir Excel listesi ile gelip tekrarlayan kayitlari bulmak icin yardim istemistir. Benim de basima siklikla gelen bu durum icin asagidaki Excel formulunu yazmistim.

A sutununda bulundan anahtar'a gore tekrarlayan kayitlari, B sutununa yerlestirtigimiz su formulle bulup gerekirse silebiliriz.

=IF(COUNTIF(A:A,A2)=1,A2,IF(COUNTIF(A$1:A2,A2)=1,A2,"zzzBenBirTekrarKayitim"))

kolay gelsin.

DENSE_RANK ile Gruba duyarli siralama

Karsilastigim sorun soyleydi.

Elimde Ulke, Kullanici Adi, Tarih seklinde erisim istatistikleri var, ve bunlari bir duzene sokmam ve bir grafikte gostermem gerekiyor. Ornegin Subat ayinda kac rapor goruntulenmis, hangi ulkeden goruntulenmis, hangi kullanici tarafindan goruntulenmis.

Basit bur group by ile count aliyoruz ve grafigimiz hazir ama soyle bir problem var. Cok fazla kullanici var hepsini grafikte gostermek mumkun degil o zaman aktif kullanicilari grafikte gosteryeim daha az aktif olanlari ise toplayim DIGERLERI seklinde gostereyim.

Bunun icin kullanicilari ulkelerine ve ay'a gore gruplamam ve erisim sayilarina gore siralamam gerekiyor. Iste burada Dense_Rank, grup bazinda siralam yapmamiza yardim ediyor.

BOL 'a bakalim.
DENSE_RANK ( ) OVER ( [ <> ] < order_by_clause > )

Evet burada
[ <> ] Group by'a ekledigimiz kolonlari,
< order_by_clause > Siralamayi yapmak icin.

Soyleki, aktif 20 kisi gozuksun digerleri bir ararda gozuksun


Select
xAy,
xUlke,
CASE
WHEN (DENSE_RANK() OVER (PARTITION BY xAy, xUlke order by COUNT(*) desc) ) <=20 THEN xKullanici ELSE xUlke +'-DIGERLERI' xKullanici, COUNT(*) ErisimSayisi
FROM
ErisimBilgisi
GROUP BY
xAy,
xUlke,
xKullanici

Kolay Gelsin,

Excel Pivot Table'da Veri Kaynagini Degistirmek

Varsayalim, bir zaman harici bir veri kaynagindan beslenen bir Excel Pivot Table olusturdunuz. Bir zaman sonra bu veri kaynaginin IPsini , ismini degistirmek zorunda kaldiniz. Ve beklemediginiz bir sey oldu, meger sizin unuttudugunuz, Pivot Table mailden maile dolasarak sagina soluna grafikler eklenerek bircok insan icin operasyonel bir arac haline gelmis ve IP degisikligi ile bir cok insan bu araci kullanamamaya baslamis.

Surpriz ve basagrisi.

Asagidaki scripti kullanicilara dagitip Pivot Table'lardaki data source alanini degistirmek icin yazdim. Not: Calismasi icin Macro Guvenlik Seviyesinde VBScriptlere izin verilmesi lazim.

Kolay Gelsin

'Replace DataSource Name of an Excel Pivot Table
'By Erdal Akbulut
'on 15.01.08

' Get File
Set ObjCDO = CreateObject("UserAccounts.CommonDialog")
InitFSO = ObjCDO.ShowOpen
If InitFSO = False Then
Wscript.Echo "Script Error: Please select a file!"
Wscript.Quit
Else
fName = ObjCDO.FileName
End If
'Clean up
Set ObjCDO = Nothing
Set InitFSO = Nothing

'Open It in Excel
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open(fName)

'Save as XML
fXmlName = fName & ".xml"
objWorkBook.SaveAs fXmlName ,46
objWorkBook.Close True

'Clean up
Set objWorkBook = Nothing
Set objExcel = Nothing

'Open XML as Text File for Input
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile (fXmlName, 1, True)

'Get File Content and Replace Server Name
sFileContents = objTextFile.ReadAll
sFileContents = Replace (sFileContents, "OLDSERVERNAME" , "NEWSERVERNAME")

'Clean up
objTextFile.Close
Set objTextFile = Nothing
Set objFSO = Nothing

'Open XML as Text File for Output
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile (fXmlName, 2, True)

'Output XML File with New ServerName
objTextFile.Write(sFileContents)

'Clean up
objTextFile.Close
Set objTextFile = Nothing
Set objFSO = Nothing

'Open XML in Excel
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open(fXmlName)

'Save as XLS with New Name
fNewName = fName & "_New.xls"
objWorkBook.SaveAs fNewName
objWorkBook.Close True

'Clean up
Set objWorkBook = Nothing
Set objExcel = Nothing

'Delete XML file
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.GetFile (fXmlName)
objTextFile.Delete

'Clean up
Set objTextFile = Nothing
Set objFSO = Nothing

SQL 'de sira numarasi ROW_NUMBER()

SQL Server 2005 ile gelen ROW_NUMBER() fonksiyonu ile SQL Server 2000'de zaman zaman bas agritan sorgu sonuclarina sira numarasi verme isini kolaylikla cozmek mumkun.
Ornegin asagidaki ornekte. Sorgu sonucu UserID alanina gore siralanirken UserID alanina gore
verilmis bir sira numarasida sonuclara eklenecek.

Select
ROW_NUMBER() OVER(ORDER BY UserID) SiraNumarasi,
UserID,
UserName
from
User

Bu fonksiyonun guzel yanlarindan biri de sira numarasini yarattiktan sonra sonuclari baska bir alani kullanarak siralayabilecek olmaniz. yukaridaki cumleye eklenecek "order by UserName" eki ile sonuclariniz UserName gore sirali gelirken SiraNumarasi alani ise UserID'ye gore olusturulacak.

Select
ROW_NUMBER() OVER(ORDER BY UserID) SiraNumarasi,
UserID,
UserName
from
User
order by
UserName

Oldukca kullanisli

Delete Top(1) ve tekrarlayan kayitlar (duplicates)

SQL 2000'de bulunmayan ama SQL 2005'e eklenen yeni bir ozellik de Delete cumlecikleri icinde TOP (1) yapisini kullanarak, kaynak tablodan belli sayida kayiti silebilmek.
Bunu nerede mi kullanabiliriz?

Ben soyle bir yapida kullandim.

SQL tablolarinda olusan onemli problemlerden biri duplicate kayitlar. Eger tablomuz dogru keyleri bulundurmuyorsa bu problemle karsilasma olasiligimiz artar. Bu kayitlari bulmak kolay olsa da bunlari temizlemek her zaman kolay olmuyor. Duplicate kayitlari genelde ara tablolari kullanarak temizlerdim bu sefer soyle bir yol izledim.

Ornegin Musteri ve Urun iliskisini tutan bir tabloda olusan tekararlayan kayitlari silmek istiyorum.
Bunun icin soyle bir yontem izledim

1. Tekrarlayan kayitlari bul
2. Bu kayitlar icin bir Cursor ac
3. Delete Top(1) yapisi ile tekrarlayan kayitlardan ilkini sil
4. Tekrarlayan kayit kalmayana kadar devam et.

Bunun icin asagidaki scripti yazmistim.

Bu script donguler kullandigindan buyuk tablolarda performansi dusuk olacatir. Alternatif olarak Tabloya bir idendity kolon ekleyerek daha hizli olarak tekrarlayan kayitlari bulmaniz mumkun, ancak eger tablo yapilari ile oynayamiyorsaniz asagidaki script bir alternatif olabilir.

Declare @CustomerID varchar(6)
Declare @ItemNO varchar(11)


DECLARE xCursor CURSOR FOR

Select CustomerID,ItemNo from Tablo
Group by CustomerID,ItemNo
Having count(*)>1
ORDER BY CustomerID

OPEN xCursor
FETCH NEXT FROM xCursor
INTO @CustomerID,@ItemNo
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE (Select Count(*) from Tablo Where CustomerID = @CustomerID and ItemNO = @ItemNo ) > 1
Delete Top(1) From
Tablo
Where
CustomerID = @CustomerID and
ItemNO = @ItemNo

FETCH NEXT FROM xCursor
INTO @CustomerID,@ItemNo
END

CLOSE xCursor
DEALLOCATE xCursor