Download sample code
Apabila Anda pernah melihat atau membuat sebuah aplikasi Business Intelligence (BI), biasanya terdapat sebuah dimensi yang namanya DimDate atau DimTime yang berguna untuk menganalisis sebuah measure berdasarkan tanggal atau waktu.
Untuk membuatnya tentu bermacam-macam cara yang bisa kita lakukan, dan yang paling saya *** adalah dengan menggunakan SSIS dan Script Component. Kenapa? Karena dengan script component yang notabene menggunakan VB atau C# sebagai programming language-nya memberikan keleluasan pada saat kita ingin membuat format penulisan tanggal dan bahasa yang digunakan sesuai dengan kebutuhan atau permintaan user.
Sebagai ilustrasi bisa saja kita mengisi sebuah kolom dengan format [nama hari], [tanggal] [bulan] [tahun] dalam Bahasa Indonesia seperti Senin, 19 Januari 2009 atau Bahasa Inggris seperti Monday, January 19 2009. Hal ini dimungkinkan dengan adanya library CultureInfo yang memudahkan format penulisan tanggal.
Spesifikasi
Dalam contoh berikut, saya akan membuat sebuah dimensi waktu sederhana dengan nama DimDate dan men-generate datanya dalam setahun berdasarkan variable Tahun yang diisikan dengan input user ataupun otomatis mengikuti tahun berikutnya yang akan berjalan. Column yang terdapat pada DimDate adalah DateKey sebagai primary key, FullDate yang berisi tanggal, MonthName yang berisi nama bulan, DayName yang berisi nama hari, YearNumber, MonthNumberOfYear, DayNumberOfMonth yang masing-masih berisi tahun, bulan, dan tanggal.
Membuat Database
Pertama, kita buat dulu sebuah database dengan nama SampleDB ditambah dengan table DimDate didalamnya
Membuat Projek SSIS
Buka Business Intelligence Development Studio (BIDS), buat sebuah SSIS Project baru dengan nama SampleDBETL
Tambahkan sebuah data source ke SampleDB
Ubah nama package.dtsx dengan DimDate.dtsx
Tambahkan koneksi ke SampleDB pada connection manager DimDate.dtsx
Variable
Variable akan digunakan pada langkah selanjutnya yang isinya adalah tahun pada DimDate. Demi flexibilitas saya biasanya membuat dua variable. Yang pertama adalah UserModifiedYear yang dapat diisi oleh user apabila ingin men-generate data untuk tahun tertentu dan LogicalYear yang berisi tahun berikutnya apabila user tidak mengisi tahun pada UserModifiedYear.
LogicalYear sangat berguna pada saat DimDate ingin dijadikan sebuah job di SQL dan job nya akan di-schedule setahun sekali untuk men-generate data tahun berikutnya. Untuk mengisi nilai pada LogicalYear tentu kita dapat menggunakan expression. Pertama set properti EvaluateAsExpression untuk variable LogicalYear dengan True, lalu isi expression dengan @[User::UserModifiedYear] == 0 ? YEAR(DATEADD("yy",1,GETDATE())) : @[User::UserModifiedYear]. Expression tersebut akan mengisi nilai LogicalYear dengan tahun berikutnya apabila UserModifiedYear berisi 0, dan berisi nilai UserModifiedYear apabila UserModifiedYear tidak berisi 0.
Control Flow
Di Control Flow kita buat sebuah Execute SQL Task yang isinya menghapus data di DimDate untuk tahun yang akan di-generate dan DataFlowTask untuk men-generate datanya.
Data Flow
Di dataflow tambahkan script component yang tipenya adalah Source
Double click Script Component. Pada tab Inputs and Outputs, expand Output 0 > Output Columns lalu tambahkan 6 kolom sesuai dengan meng-klik tombol AddColumn.
| Nama Kolom | Tipe Data | Panjang |
| FullDate | DT_DATE | |
| MonthName | DT_STR | 20 |
| DayName | DT_STR | 20 |
| YearNumber | DT_I4 | |
| MonthNumberOfYear | DT_I4 | |
| DayNumberOfMonth | DT_I4 | |
Pada tab Script, tambahkan User::LogicalYear pada properti ReadOnlyVariables, pilih ScriptLanguage yang akan digunakan, apakah VB atau C#, lalu klik tombol Edit Script
Tambahkan script berikut pada sub/void CreateNewOutputRows() untuk mulai men-generate data DimDate
| VB |
| Public Overrides Sub CreateNewOutputRows()
Dim culture As New System.Globalization.CultureInfo("id-ID")
Dim CurrentDate As New Date(Variables.LogicalYear, 1, 1) While CurrentDate.Year = Variables.LogicalYear
Output0Buffer.AddRow()
Output0Buffer.FullDate = CurrentDate
Output0Buffer.MonthName = String.Format(culture, "{0:MMM}", CurrentDate)
Output0Buffer.DayName = String.Format(culture, "{0:dddd}", CurrentDate)
Output0Buffer.YearNumber = CurrentDate.Year
Output0Buffer.MonthNumberOfYear = CurrentDate.Month
Output0Buffer.DayNumberOfMonth = CurrentDate.Day
CurrentDate = CurrentDate.AddDays(1)
End While Output0Buffer.SetEndOfRowset()
End Sub |
| C# |
| System.Globalization.CultureInfo culture = new System.Globalization.CultureInfo("id-ID");
DateTime CurentDate = new DateTime(this.Variables.LogicalYear, 1, 1);
while (CurentDate.Year == this.Variables.LogicalYear)
{
Output0Buffer.AddRow();
Output0Buffer.FullDate = CurentDate;
Output0Buffer.MonthName = String.Format(culture, "{0:MMMM}",CurentDate);
Output0Buffer.DayName = string.Format(culture, "{0:dddd}",CurentDate);
Output0Buffer.YearNumber = CurentDate.Year;
Output0Buffer.MonthNumberOfYear = CurentDate.Month;
Output0Buffer.DayNumberOfMonth = CurentDate.Day;
CurentDate = CurentDate.AddDays(1);
}
Output0Buffer.SetEndOfRowset(); |
Tutup script editor, lalu klik OK untuk menutup Script Component Editor
Tambahkan OLE DB Destination ke dalam Data Flow, lalu hubungkan Script Component dan OLE DB Destination
Double clik OLE DB Destination, pilih SampleDB pada OLE DB connection manager, dan pilih [dbo].DimDate] sebagai table tujuan. Klik tab Mappings untuk me-map kolom pada Script Component dan kolom pada OLE DB Destination lalu klik OK untuk menutup OLE DB Destination Editor
Selesai! Apabila kita jalankan dan mengisi nilai UserModifiedYear dengan 2009, maka akan terbentuk 365 baris data untuk setiap hari yang ada di tahun ini.
