Sudah sangat amat lama sekali rasanya saya tidak menulis blog. Padahal banyak sekali ide/bahan saat sedang menemui kasus2 aneh di klien. Dari pada blognya sepi mending saya ngeblog jawaban pertanyaan di milis mumpung lagi g males nulis :D

Tulisan kali ini berisi tentang bagaimana cara mendapatkan Top Parent dari sebuah hirarki.

Berikut adalah contoh data berikut skrip yang dapat dipelajari.

 declare @Source table(
    ID char(4),
    ParentID char(4)
)

insert into @Source values
('as01',NULL),
('as02','as01'),
('as03','as02'),
('as04','as03')

;WITH CSource
AS
(
   SELECT e.ID,e.ParentID,
        0 AS Level, e.ID as TopParent
    FROM @Source AS e
    WHERE e.ParentID IS NULL
    UNION ALL
    SELECT e.ID,e.ParentID,
        Level + 1, d.TopParent
    FROM @Source AS e
    INNER JOIN CSource AS d
        ON e.ParentID = d.ID
)

SELECT ID,TopParent
FROM CSource
WHERE ID='as04'

Logikanya ada pada bagian yang dicetak tebal.

Jika ingin mencari Parent pada level tertentu juga bukanlah hal yang sulit. Berikut contoh jika ingin mendapatkan Parent Level 1(Level kedua karena dimulai dari 0).

WITH CSource
AS
(
    SELECT e.ID,e.Parent,
        0 AS Level, cast(NULL as CHAR(4)) as Root1
    FROM @Source AS e
    WHERE e.Parent IS NULL
    UNION ALL
    SELECT e.ID,e.Parent,
        Level + 1, case when Level + 1=1 then e.ID else d.Root1 end
    FROM @Source AS e
    INNER JOIN CSource AS d
        ON e.Parent = d.ID
)

SELECT *
FROM CSource
--WHERE ID='as04'

Cukup mudah bukan?

Sampai jumpa pada coretan selanjutnya :)

Jadi gak sabar nungguin final release dari SQL Server 2008 R2 especially untuk Reporting Servicesnya.

Banyak peningkatan2 dan penambahan fitur yang membuat proses development report menjadi jauh lebih simple dan efisien.

 

November CTP :

 

1. Report Part Gallery

Komponen2 yang ada di report kita seperti tablix, matrix, chart, dll bisa kita publish ke server dan kita pakai berulang2 untuk report yg lainnya baik untuk development menggunakan BIDS maupun report builder 3.0

 

2. Shared Datasets

Datasets yg kita buat bisa di share seperti halnya datasource .

Fitur ini yg saya nanti2kan karna sering kali saya harus membuat datasource yg sama berulang2 untuk report yg saya buat. Dataset ini biasanya berupa dataset untuk parameter. Ketika ada perubahan untuk satu dataset, saya juga harus merubah dataset yg sama di report2 yg lain. Sungguh hal yg membuang banyak waktu dalam proses development report.
 

3. Enhanced SharePoint Integration for SharePoint 2007 and 2010

Adanya dukungan untuk multiple SharePoint Zones, the SharePoint Universal Logging service, dan Report Parts.

Selain itu, kita bisa mengquery List yang ada di SharePoint menggunakan query designer.  Salah satu data source yg mungkin nanti akan sering saya gunakan selain datasource menggunakan query SQL dan MDX.

 

4. New Data Visualisation Report Items

Ini fitur yang menjengkelkan buat saya :D karna beberapa bulan sebelum ini saya harus memutar otak untuk menghasilkan hal serupa dan setelah mau saya blog bagaimana caranya ternyata eh ternyata malah dijadikan fitur tambahan di SQL Server R2.

 




5. Data Bars : Sama halnya dengan databar yg ada di excel. Skala panjang bar berdasarkan prosentasenya terhadap nilai maksimal data pada grup tempat komponen ini. Sebelum ini,untuk menghasilkan hal serupa saya menggunakan gauge dan menghilangkan semua componennya kecuali komponen range untuk barnya. Dengan itu, saya tidak hanya bisa menentukan batas maksimum bahkan bisa menentukan batas minimum dan membuat databar yg mulai dari tengah kemudian barnya ke arah kanan dan kiri untuk nilai positif dan negatif. Untuk databars di R2 ini saya belum mencoba apakah bisa digunakan untuk hal serupa :)
    
6. Sparklines : Sebuah grafik garis  yg biasanya digunakan untuk melihat gambaran tren naik turun suatu data. Dahulu kala saya membuat sparkline ini di dalam suatu matrix menggunakan line chart kemudian membuang semua komponennya seperti axis, legend , dll kecuali line chartnya itu sendiri.

 

7. Indicators : Ikon kecil ini biasa digunakan untuk menggambarkan status suatu data. Pada jaman reporting services sebelumnya untuk membuat indicators kita harus menempelkan suatu gambar di dalam komponen kemudian menentukan gambarnya di value expression dari kompenen gambar tersebut.

 

Contoh data visualisation tricky yg pernah saya buat:

 

 


8. Enhanced Business Intelligence Development Studio

BIDS Mendukung format report dan report projects untuk versi 2008 dan 2008 R2

 

9. Aggregates of Aggregates

Salah satu hal yg cukup sulit kita lakukan di versi sebelumnya adalah melakukan agregasi di dalam agregasi. Sekarang di 2008 R2 kita bisa menggunakan expression seperti =Avg(Sum(Sales, "Month"), "Year") untuk mendapatkan rata2 penjualan perbulan langsung di komponen reporting services tanpa harus menghitungnya terlebih dahulu di level dataset.

 

10. Other RDL Expression Language enhancements
11. Expression untuk mengetahui format rendering (=Globals!RenderFormat.Name)
12. Penamaan halaman, termasuk penamaan worksheets untuk report yg diekspor ke format excel. Sebelumnya worksheets di excel hanya bisa kita beri nama sesuai nama report. Kalau kita menggunakan page break untuk membuat report yg kita eskpor ke excel bisa menjadi lebih dari 1 worksheet tidak ada menu atau properties untuk merubah nama worksheet tersebut.
13. Group.DomainScope cukup berguna untuk sinkronisasi data secara visual antar grafik / sparklines dan  tablix yang berbeda bahkan jika data hanya tersedia pada group yg berbeda.
14. Proses baca/tulis variabel di report ada secara otomatis dan rapi. Ini untuk memudahkan kita mengelola variabel di report yg kita buat sendiri.
15. Kita bisa memutar teks 270 Derajat di dalam kolom. Saya sempat mencari2 untuk hal ini di versi sebelumnya dan ternyata sekarang ada di versi R2 :)
16. Ada dynamic page breaks, dan juga kita bisa mereset kembali nomor halaman pada page breaks

 

17. New 2010 SOAP Endpoint

SOAP yang ada saat ini yaitu 2005/2006 masih didukung, tapi di 2010 dukungan dijadikan satu baik untuk mode native maupun SharePoint integrated dan menambahkan fungsionalitas untuk area baru seperti shared datasets, cache refresh plans, and report parts.

 

18. Improved Browser and Standards Mode Support

Saya pernah melihat reporting services 2008 R2 didemokan oleh Om MCA saat mengikuti monthly meeting sql server user groupnya singapore lewat firefox dan tampilannya tidak berantakan.

 

August CTP :

 

1. Map and spatial data visualization

Fitur baru untuk visualisasi data spatial.Visualisasi data ini juga bisa menggunakan bing maps.

 

2. Report Builder 3.0

Report builder yang baru ini punya banyak fitur yang hampir mirip dengan proses development report menggunakan BIDS.

3. Bisa digunakan untuk membuat embedded datasource credentials, relative references, dan subreport references ketika tehubung ke sebuah report server.
4. Hasil dari dataset juga dichache ketika terhubung ke report server sehingga mempercepat preview report ketika kita melakukan perubahan2 terhadap layout/design report.

 

5. ATOM data feeds

Report yang kita buat sekarang juga tersedia dalam format ATOM yang bisa kita ambil dengan menggunakan PowerPivot a.k.a. Gemini atau tools lain yg bisa kita gunakan untuk membaca ATOM.

 

6. AJAX Report Viewer

Navigasi yang kita lakukan di report viewer akan lebih lancar karna versi 2008 R2 nantinya menggunakan teknologi AJAX

 

7. Report Manager

Interaksi kita ke report manager nantinya akan terasa seperti di SharePoint dan akan ada menu2 tambahan.

 

8. RDL Expression Language enhancements

Penambahan dukungan untuk fungsionalitas dan data geospatial, juga untuk 3 tipe fungsi lookup yaitu Lookup, LookupSet dan MultiLookup.

 

References:

1. Monthly meeting SQL Server User Group Singapore. Thx to Om MCA(Choirul Amri) as the speaker and thx for the walk :)
2. http://blogs.msdn.com/sqlrsteamblog/archive/2009/11/09/sql-server-2008-r2-november-ctp-what-s-new-in-reporting-services.aspx
3. http://blogs.msdn.com/sqlrsteamblog/archive/2009/08/11/sql-server-2008-r2-august-ctp-what-s-new-in-reporting-services.aspx
4. http://blogs.msdn.com/robertbruckner/archive/2009/08/11/rs-maps-with-spatial-data-and-bing-maps.aspx

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.

Saat sedang membantu teman kantor yang progress projectnya dah mau kelar saya sedikit terkejut dengan DW(Datawarehouse) yang beliau bikin. Ternyata dari awal project sampai project hampir selesai dia belum memasang constraint(foreign key dan unique key) di DW. Awalnya memang sengaja tidak beliau pasang dengan alasan agar tidak lemot mengingat data yang dihandle cukup besar dengan resource yang kurang. Tetapi saya kurang setuju dengan alasan tersebut.

Constraint di DW sangatlah penting apalagi saat proses development untuk memastikan data yang masuk ke DW adalah data yang clean dan tidak redundan.

Akhirnya beliau minta saya untuk memasang constraint yang dibutuhkan di DW. Pada mulanya saya bermaksut membuat constraint yg dibutuhkan satu2 secara manual. Tapi beliau menyarankan untuk membuat generator saja mengingat jumlah constraint yang dibutuhkan lumayan banyak.

Untuk membuat generator ini tentunya ada beberapa aturan yang harus diperhatikan yaitu standarisasi penamaan tabel dan kolom pada DW. pada kesempatan kali ini saya sudah menyesuaikan generator yg telah saya buat dengan standar dari AdventureWorksDW dimana nama tabel diawali dengan kata Dim dan Fact untuk tabel dimensi dan fact. kemudian untuk kolom primary key dan foreign key di belakang nama kolomnya ada tambahan kata2 Key

Karena dibutuhkan 2 tipe constraint yaitu constraint untuk foreign key dan unique key maka saya membuat ke dua generatornya secara terpisah.

Berikut adalah script untuk men-generate constraint foreign key:

DECLARE @MainTable NVARCHAR(50)
DECLARE @ReferenceTable NVARCHAR(50)    
DECLARE @sqlFK varchar(max)
DECLARE @ColumnName NVARCHAR(50)
DECLARE @Count int
DECLARE @dummy int

DECLARE curCreateFK CURSOR  
FOR
    SELECT so.name,
           sc.name
    FROM   sysobjects so,
           syscolumns sc
    WHERE  so.id = sc.id
      AND  so.type = 'U'
      AND  so.name LIKE 'Fact%'
      AND  RIGHT(sc.name,3) = 'Key'
    ORDER BY
           so.name,
           sc.colorder
OPEN curCreateFK
    
FETCH NEXT FROM curCreateFK INTO @MainTable, @ColumnName
    
    
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Count = 0
    SELECT @Count = count(so.name)
    FROM   sysobjects so,
           syscolumns sc
    WHERE  so.id = sc.id
      AND  so.type = 'U'
      AND  so.name LIKE 'Dim%'
      AND  sc.isnullable=0
      AND  sc.name = @ColumnName
    
    IF @Count = 1
    BEGIN
        SELECT @ReferenceTable = so.name
        FROM   sysobjects so,
               syscolumns sc
        WHERE  so.id = sc.id
          AND  so.type = 'U'
          AND  so.name LIKE 'Dim%'
           AND  sc.isnullable=0
          AND  sc.name = @ColumnName

        SET @sqlFK =  'ALTER TABLE [dbo].[' + @MainTable + ']  WITH CHECK ADD  CONSTRAINT [FK_' + @MainTable +'_'+@ColumnName+'] FOREIGN KEY(['+@ColumnName+'])
        REFERENCES [dbo].[' + @ReferenceTable + '] ([' + @ColumnName +'])
        '

    END
    ELSE
    BEGIN
        IF @Count=0
        BEGIN
            IF @ColumnName LIKE '%Date%'
            BEGIN
                SET @sqlFK = 'ALTER TABLE [dbo].[' + @MainTable + ']  WITH CHECK ADD  CONSTRAINT [FK_' + @MainTable +'_'+@ColumnName+'] FOREIGN KEY(['+@ColumnName+'])
        REFERENCES [dbo].[DimTime] ([TimeKey])
                '
            END
            ELSE
            BEGIN
                -- Do it manually for different column name
                PRINT 'Match Not Found: ' + @MainTable + '-' + @ColumnName +'
                '
                SET @sqlFK=''
            END
            
        END
        ELSE
        BEGIN
            -- Found more than 1 primary key with the same name
            PRINT 'Double: ' + @MainTable + '-' + @ColumnName +'
            '
        END
        
    END

    BEGIN TRY           
        EXEC (@sqlFK)
        PRINT 'Success ' + @sqlFK
    END TRY
    BEGIN CATCH
        PRINT 'Already Exists ' + @sqlFK
    END CATCH      
    
    FETCH NEXT FROM curCreateFK INTO @MainTable, @ColumnName
END

CLOSE curCreateFK

DEALLOCATE curCreateFK

 

Dan ini generator untuk unique constraintnya:

 DECLARE curTable CURSOR FOR
SELECT so.name
FROM   sysobjects so
WHERE  so.type = 'U'
    AND  so.name LIKE 'Fact%'
ORDER BY
    so.name

DECLARE @tablename varchar(50),@UQ_Query varchar(max),@columnname varchar(50),@columncount int

OPEN curTable
FETCH NEXT FROM curTable INTO @tablename
WHILE @@fetch_status=0
BEGIN
    SET @columncount=0
    SET @UQ_Query='ALTER TABLE ['+@tablename+'] ADD CONSTRAINT [UQ_'+@tablename+'] UNIQUE ('
    DECLARE curColumn CURSOR FOR
    SELECT sc.name
    FROM sysobjects so,
        syscolumns sc
    WHERE  so.id = sc.id
        AND RIGHT(sc.name,3) = 'Key'
        AND so.name LIKE @tablename
    ORDER BY
        sc.colorder

    OPEN curColumn
    FETCH NEXT FROM curColumn INTO @columnname
    WHILE @@fetch_status=0
    BEGIN
        SET @columncount=@columncount+1
        IF @columncount=1
        BEGIN
             SET @UQ_Query=@UQ_Query+@columnname
        END
        ELSE
        BEGIN
            SET @UQ_Query=@UQ_Query+','+@columnname
        END
        
        FETCH NEXT FROM curColumn INTO @columnname
    END
    CLOSE curColumn
    DEALLOCATE curColumn
    
    --Exception Goes Here
    IF @tablename='FactInternetSales' OR @tablename='FactResellerSales' OR @tablename='FactInternetSalesReason'
    BEGIN
        SET @UQ_Query=@UQ_Query+',SalesOrderNumber,SalesOrderLineNumber'
    END
    
    SET @UQ_Query=@UQ_Query+')'
    
    BEGIN TRY
        EXEC (@UQ_Query)
        PRINT 'Success '+@UQ_Query
    END TRY
    BEGIN CATCH
        PRINT 'Already Exists '+@UQ_Query
    END CATCH
    
    FETCH NEXT FROM curTable INTO @tablename
END
CLOSE curTable
DEALLOCATE curTable

Dalam tulisan kali ini saya ingin berbagi pengalaman saya tentang penggunaan CTE. dan pengalaman ini membuat saya CTE minded ...

I love CTE 

Sebelumnya saya sudah mengajarkan jurus ini kepada beberapa teman. Pada umumnya mereka bilang jurus ini ampuh. Tapi bagi saya jurus ini MTV Ampuh Banget.

 

Saya akan berbagi tentang jurus ini dengan menggunakan skenario pembuatan report untuk data penjualan.Pada report ini kita akan melihat penjualan untuk 1 bulan, penjualan pada bulan sebelumnya, penjualan dari awal kuartal sampai bulan sekarang, penjualan dari kuartal sebelumnya, dan penjualan dari awal tahun sampai bulan skarang.

Supaya lebih sederhana dan mudah dimengerti saya buat tabel Sales dengan 3 kolom saja yaitu Product, SalesDate dan Quantity. Berikut script untuk tabelnya:

CREATE TABLE #Sales (
    Product varchar(20),
    SalesDate datetime,
    Quantity int
)

Setelah itu kita buat beberapa data dummy data untuk table tersebut

INSERT INTO #Sales
    SELECT 'CD',cast('20080101' AS datetime),1 UNION
    SELECT 'DVD','20080102',1 UNION
    SELECT 'Flash Disk','20080103',1 UNION
    SELECT 'Hard Disk','20080104',1 UNION

    SELECT 'CD','20080201',2 UNION
    SELECT 'DVD','20080202',2 UNION
    SELECT 'Flash Disk','20080203',2 UNION
    SELECT 'Hard Disk','20080204',2 UNION

    SELECT 'CD','20080301',3 UNION
    SELECT 'DVD','20080302',3 UNION
    SELECT 'Flash Disk','20080303',3 UNION
    SELECT 'Hard Disk','20080304',3 UNION

    SELECT 'CD','20080401',4 UNION
    SELECT 'DVD','20080402',4 UNION
    SELECT 'Flash Disk','20080403',4 UNION
    SELECT 'Hard Disk','20080404',4 UNION

    SELECT 'CD','20080501',5 UNION
    SELECT 'DVD','20080502',5 UNION
    SELECT 'Flash Disk','20080503',5 UNION
    SELECT 'Hard Disk','20080504',5

Skarang kita sudah punya sumber data. Berikutnya adalah bagian query untuk reportnya. Disinilah saya akan menunjukkan cara saya memanfaatkan CTE yang ada pada SQL Server 2005 ke atas. Pada query ini saya menggunakan range periode bulan,kuartal dan tahun seperti pada postingan saya sebelumnya. Dan querynya seperti ini :

DECLARE @pRequestPeriod char(6)
SET @pRequestPeriod='200805'

DECLARE @pDate datetime
SET @pDate=@pRequestPeriod+'01'

DECLARE @startMonth datetime,@endMonth datetime,@startPrevMonth datetime,@endPrevMonth datetime,
    @startQTM datetime,@endQTM datetime,@startPrevQTM datetime,@endPrevQTM datetime,
    @startYTM datetime,@endYTM datetime,@startPrevYTM datetime,@endPrevYTM datetime,@startPrevMoY datetime,@endPrevMoY datetime;

SET @startMonth=datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'
SET @endMonth=dateadd(day,-1,dateadd(month,1,@startMonth))
SET @startPrevMonth=datename(year,dateadd(month,-1,@pDate))+'-'+datename(month,dateadd(month,-1,@pDate))+'-01'
SET @endPrevMonth=dateadd(day,-1,@startMonth)

SET @startQTM=datename(year,@pDate)+'-'+cast(((datepart(month,@pDate)-1)/3)*3+1 AS varchar(2))+'-01'
SET @endQTM=dateadd(day,-1,dateadd(month,1,datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'))
SET @startPrevQTM=datename(year,dateadd(month,-3,@pDate))+'-'+cast(((datepart(month,dateadd(month,-3,@pDate))-1)/3)*3+1 AS varchar(2))+'-01'
SET @endPrevQTM=dateadd(day,-1,dateadd(month,1,datename(year,dateadd(month,-3,@pDate))+'-'+datename(month,dateadd(month,-3,@pDate))+'-01'))

SET @startYTM=datename(year,@pDate)+'-01-01'
SET @endYTM=dateadd(day,-1,dateadd(month,1,datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'))

--disinilah CTE beraksi

;WITH
Sales(Product,SalesDate,Quantity) AS (
    SELECT * FROM #Sales
),
SalesMonth(Product,MonthQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startMonth AND @endMonth
    GROUP BY Product
),
SalesPrevMonth(Product,PrevMonthQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startPrevMonth AND @endPrevMonth
    GROUP BY Product
),
SalesQTM(Product,QTMQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startQTM AND @endQTM
    GROUP BY Product
),
SalesPrevQTM(Product,PrevQTMQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startPrevQTM AND @endPrevQTM
    GROUP BY Product
),
SalesYTM(Product,YTMQuantity) AS (
    SELECT Product,sum(Quantity)
    FROM Sales a
    WHERE SalesDate BETWEEN @startYTM AND @endYTM
    GROUP BY Product
),
Result(Product,MonthQuantity,PrevMonthQuantity,QTMQuantity,PrevQTMQuantity,YTMQuantity) AS (
    SELECT a.Product,
    b.MonthQuantity,c.PrevMonthQuantity,d.QTMQuantity,e.PrevQTMQuantity,f.YTMQuantity
    FROM (SELECT DISTINCT Product FROM Sales) a
        LEFT JOIN SalesMonth b ON a.Product=b.Product
        LEFT JOIN SalesPrevMonth c ON a.Product=c.Product
        LEFT JOIN SalesQTM d ON a.Product=d.Product
        LEFT JOIN SalesPrevQTM e ON a.Product=e.Product
        LEFT JOIN SalesYTM f ON a.Product=f.Product
)

SELECT * FROM Result

Output:

 

 

 

 

Gmn? Bingung kan  ... Intinya adalah CTE disini berfungsi seperti temporary table, hanya saja kita lebih mudah untuk membaca querynya karena code relatif lebih sedikit dibandingkan dengan menggunakan temporary table dengan mendeklarasikannya terlebih dahulu. Coba anda bayangkan jika SalesMonth,SalesPrevMonth,SalesQTM,SalesPrevQTM,dan Sales YTM dibuat menggunakan temporary variabel, untuk membuat coding mudah terbaca anda pasti membutuhkan lebih dari 2x jumlah baris code jika anda tidak menggunakan CTE. Inilah mengapa saya *** CTE karena banyak kasus yang jauh lebih rumit dari yang ini dapat diselesaikan dengan coding yang sedikit dan mudah dibaca.

Seringkali saya menghadapi kasus pembuatan report untuk menampilkan data 1 bulan , 1 kuarter atau 1 tahun dan berbagai macam variasinya. Untuk menyelesaikannya diperlukan script untuk mendapatkan range tanggal sesuai dengan periode yang diminta. Beberapa baris script di bawah ini adalah beberapa koleksi saya ketika membuat report menggunakan periode-periode tersebut.

DECLARE @pDate datetime
SET @pDate='20090417'

DECLARE @startMonth datetime,@endMonth datetime,@startPrevMonth datetime,@endPrevMonth datetime,@startNextMonth datetime,@endNextMonth datetime,
    @startQTM datetime,@endQTM datetime,@startPrevQTM datetime,@endPrevQTM datetime,
    @startYTM datetime,@endYTM datetime,@startPrevYTM datetime,@endPrevYTM datetime,@startPrevMoY datetime,@endPrevMoY datetime;

SET @startMonth=datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'
SET @endMonth=dateadd(day,-1,dateadd(month,1,@startMonth))
SET @startPrevMonth=datename(year,dateadd(month,-1,@pDate))+'-'+datename(month,dateadd(month,-1,@pDate))+'-01'
SET @endPrevMonth=dateadd(day,-1,@startMonth)
SET @startNextMonth=dateadd(month,1,@startMonth)
SET @endNextMonth=dateadd(day,-1,dateadd(month,1,@startNextMonth))

SET @startQTM=datename(year,@pDate)+'-'+cast(((datepart(month,@pDate)-1)/3)*3+1 AS varchar(2))+'-01'
SET @endQTM=dateadd(day,-1,dateadd(month,1,datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'))
SET @startPrevQTM=datename(year,dateadd(month,-3,@pDate))+'-'+cast(((datepart(month,dateadd(month,-3,@pDate))-1)/3)*3+1 AS varchar(2))+'-01'
SET @endPrevQTM=dateadd(day,-1,dateadd(month,1,datename(year,dateadd(month,-3,@pDate))+'-'+datename(month,dateadd(month,-3,@pDate))+'-01'))

SET @startYTM=datename(year,@pDate)+'-01-01'
SET @endYTM=dateadd(day,-1,dateadd(month,1,datename(year,@pDate)+'-'+datename(month,@pDate)+'-01'))
SET @startPrevYTM=datename(year,dateadd(year,-1,@pDate))+'-01-01'
SET @endPrevYTM=dateadd(day,-1,dateadd(month,1,datename(year,dateadd(year,-1,@pDate))+'-'+datename(month,dateadd(year,-1,@pDate))+'-01'))
SET @startPrevMoY=dateadd(year,-1,@startMonth)
SET @endPrevMoY=dateadd(day,-1,dateadd(month,1,@startPrevMoY))

 

Keterangan:

- @startMonth,@endMonth ==> untuk mendapatkan range tanggal 1 bulan

- @start[Prev|Next]Month,@end[Prev|Next]Month ==> untuk mendapatkan range tanggal bulan sebelum/berikutnya

- @startQTM,@endQTM,@startPrevQTM,@endPrevQTM ==> untuk mendapatkan range tanggal awal kuarter ini/sebelumnya sampai tanggal terakhir bulan. Contoh : jika @pDate='20090523' maka @startQTM='20090401' dan @endQTM='20090531'

- @startYTM,@endYTM,@startPrevYTM,@endPrevYTM ==> untuk mendapatkan range tanggal awal tahun ini/sebelumnya sampai tanggal terakhir bulan. Contoh : jika @pDate='20090523' maka @startYTM='20090101' dan @endYTM='20090531'

 - @startPrevMoY,@endPrevMoY ==> untuk mendapatkan range tanggal bulan yang sama pada tahun sebelumnya.

Kemaren sore saya ada meeting dengan klien untuk menjelaskan fitur Business Inteligence yang dimiliki oleh Proclarity(PPS).

Pada saat saya menjelaskan tentang Decomposition Tree, saya menghadapi suatu permasalahan.
Ketika saya menambahkan filter tahun di background pada setup panel maka munculah filter tersebut di atas data view pane.
Akan tetapi, saya tidak mendapatkan output terfilter seperti filter yang telah saya buat dan filter tersebut akan hilang ketika saya menekan tombol apply.

Setelah saya baca kembali file GettingStartedGuide.pdf untuk proclarity saya menemukan "a litte important note":

NOTE : Because changes that you make in a Decomposition
Tree do not show in the Setup Panel or in a grid view,
we recommend closing the Setup Panel and viewing
the Decomposition Tree in a full screen (with no
grid).

Intinya adalah Decomposition tree tidak menggunakan setup panel seperti pada bentuk view yang lain untuk mengatur measure dan dimension yang akan di tampilkan pada data view pane.
yah..walaupun sepele tapi hal seperti ini sangatlah penting untuk diperhatikan. Apalagi untuk meeting dengan klien :D

Dasar decomposition tree yang aneh. Yang laen pada pake setup panel eh dianya gak mau >:P

Laporan di suatu perusahaan biasanya tidak hanya menampilkan data bulanan tapi juga data periode seperti 3 bulanan atau tahunan. Selain itu biasanya dibutuhkan *** data periode sebelumnya sebagai tolak ukur performa perusahaan. Pada kesempatan kali ini saya akan sedikit menjelaskan beberapa sintaks MDX yang digunakan untuk mendapatkan data dalam periode tertentu dan data pada periode sebelumnya.

Ada 3 fungsi MDX yang akan saya bahas kali ini, yaitu : PeriodsToDate,Cousin, dan Ancestor.

Untuk contoh pertama saya membuat calculated member untuk mendapatkan data penjualan satu kuarter. Berikut sintaksnya:

CREATE MEMBER CURRENTCUBE.[MEASURES].[SalesQTD]
AS AGGREGATE(
        PeriodsToDate(
            [Time].[Calendar].[Quarter],
            [Time].[Calendar].CurrentMember
        ), [Measures].[Sales]
    ),
VISIBLE = 1  ;

Untuk contoh yang pertama ada 2 fungsi MDX  yang saya gunakan yaitu AGGREGATE dan PeriodsToDate.

AGGREGATE digunakan untuk mengagregasi data pada cube dengan menggunakan default aggregation function measure yang di AGGREGATE. Jika [Measures].[Sales] mempunyai aggregation function SUM maka AGGREGATE di atas akan berjalan sebagai SUM, jika aggregation function [Measures].[Sales] AverageOfChildren maka AGGREGATE akan berfungsi seperti AverageOfChildren.

PeriodsToDate digunakan untuk mengenerate member dari awal periode sampai member saat ini(current member). Pada contoh di atas saya menggunakan kuarter pada hierarchy calendar punya dimensi time. Misal current member untuk dimensi time adalah 15 Maret 1988 maka fungsi PeriodsToDate diatas akan menghasilkan member dari 1 Januari 1988 sampai dengan 15 Maret 1988.

Untuk contoh kedua saya membuat calculated member untuk mendapatkan data periode sebelumnya. Pada contoh kali ini adalah kuarter sebelumnya. Berikut Sintaksnya:

CREATE MEMBER CURRENTCUBE.[MEASURES].[SalesQTD]
AS AGGREGATE(
        PeriodsToDate(
            [Time].[Calendar].[Quarter],
            Cousin([Time].[Calendar].CurrentMember,Ancestor([Time].[Calendar].CurrentMember,[Time].[Calendar].[Quarter]).Prevmember)
        ), [Measures].[Actual]
    ),
VISIBLE = 1  ;

Untuk contoh kedua ada beberapa perbedaan pada sintaks MDXnya yaitu penggunaan fungsi Cousin dan Ancestor. Inti dari penggunaan kedua fungsi ini adalah untuk mendapatkan member pada kuarter sebelumnya. Jadi apabila current member adalah 15 Juni 1988 maka member untuk kuarter sebelumnya adalah 15 Maret 1988. Sehingga fungsi PeriodsToDate akan mendapatkan member-member pada kuarter sebelumnya. Berikut adalah penjelasan kedua fungsi tersebut:

Ancestor digunakan untuk mendapatkan level di atas current level. Fungsi ini hampir sama dengan fungsi ParentMember. Perbedaannya adalah ParentMember untuk mendapatkan member 1 level di atas current member pada suatu hierarchy sedang kan Ancestor bisa mendapatkan tidak hanya 1 level tetapi bisa 2,3/x level diatas current member. Pada contoh di atas saya menggunakan ancestor untuk mendapatkan member di level quarter kemudian dengan fungsi PrevMember saya mendapatkan quarter sebelumnya.

Cousin digunakan untuk mendapatkan member yang satu level dengan current member tetapi dengan parent yang berbeda. Jadi pada contoh diatas setelah saya mendapatkan quarter sebelumnya dengan menggunakan Ancestor saya menggunakan fungsi Cousin untuk mendapatkan member yang satu level dengan current member tetapi dengan parent kuarter sebelumnya.

Pas lagi bikin report untuk project saya dihadapkan pada suatu kasus dimana terdapat label yang mencetak bulan selanjutnya dari bulan report yang diinputkan pada parameter.

Mungkin kita semua pernah mengalami kasus serupa pada SQL dan memang bukan hal yang sulit untuk mendapatkan next month. Begitu juga dengan expression di SSRS, untuk mendapatkan next month adalah hal yang relatif mudah. Berikut akan kita lihat perbedaan sintaks untuk mendapatkan next month dengan menggunakan SQL dan expression di SSRS.

 SQL Version:

DECLARE @pDate datetime
SET @pDate='20081201'
SELECT datename(month,dateadd(month,1,@pdate))

Expression(SSRS) Version:

=MonthName(dateadd("M",1,Parameters!pDate.Value).Month)

Coba anda perhatikan Kolom-kolom DimAccount dari AdventureWorksDW berikut:

    [AccountKey] [int] IDENTITY(1,1) NOT NULL,
    [ParentAccountKey] [int] NULL,
    [AccountCodeAlternateKey] [int] NULL,
    [ParentAccountCodeAlternateKey] [int] NULL,
    [AccountDescription] [nvarchar](50) NULL,
    [AccountType] [nvarchar](50) NULL,
    [Operator] [nvarchar](50) NULL,
    [CustomMembers] [nvarchar](300) NULL,
    [ValueType] [nvarchar](50) NULL,
    [CustomMemberOptions] [nvarchar](200) NULL,
 

Disana terdapat kolom valuetype yang menurut persepsi saya :D digunakan untuk menentukan format value pada suatu account.

Sudah beberapa minggu saya googling untuk  mencari contoh implementasi/penggunaan kolom ini pada SSAS dan sialnya gak ketemu-ketemu :(. Alhamdullilah karena petunjuk dari yg di atas akhirnya saya menemukan sendiri cara menggunakannya :)

 Masalahnya adalah dimensi account ini punya 3 macam value type dengan 3 format yang berbeda:

1. Currency ("#,#.00")
2. Unit ("#")
   
3. Percent ("Percent")

Jadi ada account yang sifat datanya uang,unit dan percent. Jika kita tidak melakukan sesuatu untuk value type di SSAS pastilah ketika dibrowse datanya maka yang muncul adalah format sesuai dengan measure account tersebut.Jika format measurenya "Currency" ya semua account munculnya berformat "Currency".
Tentunya hal ini akan fatal untuk user karena jika melihat data percent akan muncul nilai nol koma sekian dan tidak ada tanda percent.

Untuk itulah kita memerlukan sedikit :) script untuk mengimplementasikan kolom value type ini

Berikut adalah langkah2 untuk peimplementasiannya:

1. pastikan anda sudah menambahkan atribut value type pada DimAccount.dim pada solusi SSAS dan set property AttributeHierarchyVisible ke false supaya tidak kelihatan
2. kita set properti visible dari measure yang akan diformat ke false. misal nama measurenya [measure].[real ori]
3. membuat calculated measure pada cube. karena nama measure asli [measure].[real ori] kita bisa memberi nama calculated measure ini [measure].[real]. berikut script calculated measurenya:

CREATE MEMBER CURRENTCUBE.[MEASURES].[real]
 AS [measure].[real ori],
FORMAT_STRING = case
when [Account Operating].[Account Operating].Properties( "Value Type" )="Percent" then "Percent"
when [Account Operating].[Account Operating].Properties( "Value Type" )="Unit" then "#"
else "#,#.00" --ini tipenya currency
end,
VISIBLE = 1  ;

 Pada desain DW biasanya kita menggunakan identity untuk PK suatu dimensi. Untuk beberapa tabel dimensi yang load datanya dilakukan secara initial load, kita akan men-truncate table tersebut supaya ketika kita insert datanya lagi nilai key kembali ke nilai awal karena jika kita menggunakan perintah delete, ketika kita memasukkan data maka nilai key akan bertambah sesuai nilai key yg terakhir walaupun data sudah dihapus.

Permasalahan yang terjadi adalah kita tidak akan bisa menggunakan perintah truncate untuk tabel yang kita tambahkan contraint foreign key. Untuk mengatasi masalah ini nilai identity bisa direset menggunakan DBCC. Sehingga untuk menggantikan perintah truncate pada tabel yang mempunyai foreign key kita bisa menggunakan perintah-perintah berikut:

 

Delete from DimTable

pertama kita hapus dulu isi tabel baru setelah itu kita reset identitynya dengan menggunakan dbcc seperti berikut.

DBCC CHECKIDENT ('DB.dbo.DimTable',reseed,1)

1 pada perintah di atas berarti kita mereset nilai awal identity ke 1.

Menjawab pertanyaan di milis sekaligus untuk meramaikan sqlserver-indo.org kita ^_^

 

Pertanyaan dari hendra.tala.a@gmail.com: 

bagaimana caranya untuk mendapatkan bahwa tanggal 14-07-2008 adalah senin ke 2 dalam bulan ini, meskipun 14-07-2008 berada pada minggu ke 3

minta tolong pakar sql

terima kasih

 

Jawaban :

declare @date datetime,@dayname varchar(10),@datenumber int,@daycount int

set @date='20080714'
set @dayname=(select datename(dw,@date))
set @datenumber=(select datepart(d,@date))
set @daycount=0

while (@datenumber>0)
begin
set @datenumber=@datenumber-7
set @daycount=@daycount+1
end

select case
        when @dayname='Monday' then 'Senin'   
        when @dayname='Tuesday' then 'Selasa'   
        when @dayname='Wednesday' then 'Rabu'   
        when @dayname='Thursday' then 'Kamis'   
        when @dayname='Friday' then 'Jumat'   
        when @dayname='Saturday' then 'Sabtu'   
        when @dayname='Sunday' then 'Minggu'   
       end
        +' ke '+cast(@daycount as char)

 

 Quiz: Algoritma di atas sebenarnya sangat sederhana. Tanpa menggunakan looping pun bisa diselesaikan. Kuncinya ada pada angka 7, penjumlahan, pengurangan dan pembagian. Ada yang mau menjawab?

 Jawaban Quiz:

DECLARE @Date datetime SET @Date = '2008-02-09'

SELECT datename(dw,@date)+' ke '+cast((DATEPART(day,@Date)-1)/7+1 AS char)