Cahyo drop in {here}

{CTE} Mendapatkan Top Parent dari sebuah Hirarki

cahyo — Mon, 20 Jun 2011 04:10:00 GMT

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 :)

{SSRS} Fitur baru untuk Reporting Services di SQL Server 2008 R2 (August and November CTP)

cahyo — Wed, 10 Feb 2010 13:46:00 GMT

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 :

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

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.

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.

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.

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 :)
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.

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:

Enhanced Business Intelligence Development Studio

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

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.

Other RDL Expression Language enhancements
Expression untuk mengetahui format rendering (=Globals!RenderFormat.Name)
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.
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.
Proses baca/tulis variabel di report ada secara otomatis dan rapi. Ini untuk memudahkan kita mengelola variabel di report yg kita buat sendiri.
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 :)
Ada dynamic page breaks, dan juga kita bisa mereset kembali nomor halaman pada page breaks

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.

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 :

Map and spatial data visualization

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

Report Builder 3.0

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

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

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.

AJAX Report Viewer

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

Report Manager

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

RDL Expression Language enhancements

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

References:

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

MDX - SSRS - Time Dimension Parameter Desc Order

cahyo — Wed, 28 Oct 2009 03:24:00 GMT

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:

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.

DW - DimTime for Production Calendar Hierarchy 4-4-5

cahyo — Wed, 07 Oct 2009 04:04:00 GMT

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.

[T-SQL] DW Constraint Generator

cahyo — Fri, 17 Jul 2009 09:54:00 GMT

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

The way I used CTE....

cahyo — Fri, 17 Apr 2009 08:40:00 GMT

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.

Date Script Collections

cahyo — Fri, 17 Apr 2009 04:06:00 GMT

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.

Proclarity - Little important notes for decomposition tree

cahyo — Thu, 15 Jan 2009 04:36:00 GMT

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

MDX - Data suatu periode dan periode sebelumnya (PeriodsToDate,Cousin,Ancestor)

cahyo — Thu, 18 Dec 2008 10:36:00 GMT

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.

SSRS - Next month expression

cahyo — Tue, 16 Dec 2008 10:47:00 GMT

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)

SSAS - Implementasi ValueType pada DimAccount

cahyo — Tue, 04 Nov 2008 10:10:00 GMT

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:

Currency ("#,#.00")
Unit ("#")
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:

pastikan anda sudah menambahkan atribut value type pada DimAccount.dim pada solusi SSAS dan set property AttributeHierarchyVisible ke false supaya tidak kelihatan
kita set properti visible dari measure yang akan diformat ke false. misal nama measurenya [measure].[real ori]
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 ;

Reset identity tanpa truncate table

cahyo — Thu, 25 Sep 2008 03:32:00 GMT

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.

Mencari hari ke - n dalam suatu bulan

cahyo — Mon, 21 Jul 2008 11:47:00 GMT

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)