|
SQL as breakfast, BI for lunch and dinner with MDX
October 2009 - Posts
-
Saat sedang membuat sebuah report menggunakan SSRS 2008 saya menemukan sebuah kasus dimana saya perlu membuat parameter dimensi waktu diurutkan secara descending. Parameter disini menggunakan dataset dari cube sehingga querynya menggunakan MDX. By default ketika kita membuat dataset menggunakan cube(SSAS) query untuk parameter akan digenerate secara otomatis. Berikut adalah query hasil generate otomatis untuk parameter bulan: WITH
MEMBER [Measures].[ParameterCaption] AS [Time].[Month Prod].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Time].[Month Prod].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Time].[Month Prod].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
[Time].[Month Prod].ALLMEMBERS ON ROWS
FROM [Comp Cube]
Seperti yang dapat anda baca dari query diatas, saya mengambil data dari atribut [Month Prod] dari dimensi [Time]. Hasil query ini diurutkan secara ascending menggunakan composite key dari atribut [Month Prod] yaitu tahun dan bulan. Contoh dari uniquename dari membernya [Month Prod] adalah [Time].[Month Prod].&[2009]&[10]. Untuk menghasilkan hasil dari query tersebut diurutkan secara descending, anda bisa menggunakan query seperti berikut: WITH
MEMBER [Measures].[ParameterCaption] AS [Time].[Month Prod].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Time].[Month Prod].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Time].[Month Prod].CURRENTMEMBER.LEVEL.ORDINAL
MEMBER [Measures].[DateOrder] as
[Time].[Month Prod].CurrentMember.Properties("Key0") + vba!right("0" +
[Time].[Month Prod].CurrentMember.Properties("Key1") ,2)
SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
ORDER([Time].[Month Prod].[All].Children ,[Measures].[DateOrder],bdesc)
ON ROWS
FROM [Comp Cube] Pada query diatas saya membuat calculated measure bernama [DateOrder] yang menggabungkan key tahun dan bulan yang bisa diakses menggunakan properties key0 dan key1. Setelah itu dengan menggunakan fungsi ORDER kita bisa mengurutkan hasil dari query untuk mendapatkan bulan dengan menggunakan measure [DateOrder] sebagai kriteria untuk dasar pengurutannya.
|
-
Di suatu project BI yang saya kerjakan ada permintaan dari klien agar dimensi waktu yang dibuat nantinya punya hierarchy dengan format kalendar 4-4-5.Kalender 4-4-5 disini berarti dalam satu bulan ada 4-5 minggu. Kala u kita melihat kalender yg sebenarnya, akan ada 1 minggu yang terletak di antara 2 bulan. Pada contoh kasus kali ini, hari jumat adalah hari pertama dalam 1 minggu produksi. Untuk lebih jelas ilustrasinya seperti di bawah ini: - 1 Okt 2009 adalah Minggu ke-4 bulan Sept
- 2 Okt 2009-8 Okt 2009 adalah Minggu ke-1 bulan Okt
- 9 Okt 2009-15 Okt 2009 adalah Minggu ke-2 bulan Okt
- 16 Okt 2009-22 Okt 2009 adalah Minggu ke-3 bulan Okt
- 23 Okt 2009-30 Okt 2009 adalah Minggu ke-4 bulan Okt
- 31 Okt 2009-6 Nov 2009 adalah Minggu ke-5 bulan Okt
Untuk Mendapatkan Minggu tersebut saya menggunakan hari pertama pada suatu minggu sebagai acuan. Sesuai permintaan, hari pertama suatu minggu disini adalah hari jumat bukan hari minggu. Untuk lebih jelasnya sintaks pembuatan tabel dimensi waktu ini adalah sbb: CREATE TABLE DimTime (
TimeKey int IDENTITY PRIMARY KEY,
AlternateKey AS year(TheDate)*10000+month(TheDate)*100+day(TheDate),
TheDate datetime NOT NULL,
Year AS datepart(year,TheDate),
Quarter AS datepart(quarter,TheDate),
QuarterName AS 'Q'+datename(quarter,TheDate)+' '+datename(year,TheDate),
Month AS datepart(month,TheDate),
MonthName AS LEFT(datename(month,TheDate),3)+' '+datename(year,TheDate),
Week AS datepart(week,TheDate),
WeekName AS 'Week '+datename(week,TheDate)+' '+datename(year,TheDate),
DateName AS datename(day,TheDate)+' '+LEFT(datename(month,TheDate),3)+' '+datename(year,TheDate),
WeekDayProd AS datepart(dw,dateadd(day,-5,TheDate)),
FirstWeekDayProd AS dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate),
YearProd AS datepart(year,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate)),
QuarterProd AS datepart(quarter,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate)),
QuarterProdName AS 'Q'+datename(quarter,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate))+' '+datename(year,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate)),
MonthProd AS datepart(month,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate)),
MonthProdName AS LEFT(datename(month,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate)),3)+' '+datename(year,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate)),
WeekProd AS (DATEPART(day,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate))-1)/7+1,
WeekProdName AS 'Week '+cast((DATEPART(day,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate))-1)/7+1 AS char(1))+' '+LEFT(datename(month,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate)),3)+' '+datename(year,dateadd(day,1-datepart(dw,dateadd(day,-5,TheDate)),Thedate))
)
Untuk mencoba mengisi data dan melihat hasil pada DimTime ini anda bisa gunakan script berikut: DECLARE @startdate datetime,@enddate datetime
SET @startdate='20090801'
SET @enddate='20090930'
TRUNCATE TABLE DimTime
WHILE (@startdate<=@enddate)
BEGIN
INSERT INTO DimTime VALUES(@startdate)
SET @startdate=dateadd(day,1,@startdate)
END
SELECT * FROM DimTime t
Saya sudah membuat contoh production calendar hierachy pada SSAS menggunakan DimTime ini. Hierarchynya urut dari tahun-kuarter-bulan-minggu-tanggal. Screenshotnya seperti gambar di bawah:
Kalau kita perhatikan tanda berwarna merah pada gambar di atas adalah tanggal pada bulan september yang dimasukan ke minggu di bulan agustus untuk keperluan kalender produksi ini.
|
More Posts
|
|
|