|
eat, sleep, breath, SQL
June 2009 - Posts
-
Pada saat menginstal SQL Server 2008, secara default database disimpan di folder C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA dan log-nya disimpan di folder C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA. Dengan nilai default ini, apabila Anda membuat dabase dengan statement CREATE DATABASE tanpa argument FILENAME, maka file database Anda disimpan di folder tadi.
Untuk mengubah default lokasi-nya dengan menggunakan wizard, buka properties server Anda
Pilih tab Database Settings, ubah default folder pada bagian Database default locations, lalu klik OK. Sekarang setiap kali Anda membuat sebuah database, default folder-nya akan mengarah ke folder yang di-set tadi.
|
-
download sample code
Setelah sebelumnya saya menunjukkan bagaimana melakukan Top N dengan menggunakan Conditional Split dan Script Component, kali ini saya akan menggunakan Top Data untuk melakukannya. Contoh yang digunakan hampir sama dengan contoh sebelumnya, tetapi saya juga akan mengambil Top 21 – Top 40. Sekadar mengingatkan kembali, saya akan mulai step-by-step nya dari awal.
Pertama buat sebuah Integration Services Project menggunakan BI Development Studio dengan nama TopNSSISwithTopData
Setelah Package.dtsx terbuka, tambahkan sebuah OLE DB Connection ke database AdventureWorks
Tambahkan dua buah variable dengan nama Top20 dan Top2140 dengan tipe data Int32
Tambahkan sebuah Data Flow Task ke dalam Control Flow dan beri nama DFT Top 40 Purchase Order
Klik ganda pada DFT Top 40 Purchase Order, lalu tambahkan sebuah OLE DB Source dengan nama Extract Purchase Order
Klik ganda Extract Purchase Order, isi OLE DB connection manager dengan AdventureWorks, pilih SQL command sebagai Data access mode, isi SQL command nya dengan select [PurchaseOrderID], [TotalDue] from [Purchasing].[PurchaseOrderHeader] order by [TotalDue] desc lalu klik OK
Tambahkan Top Data kedalam Data Flow dan beri nama Top 20, lalu hubungkan Extract Purchase Order dengan Top 20
Klik ganda pada Top 20, isi Top dengan 20, dan pilih Send to Rest Output pada Configure Rest Row, lalu klik OK. Dengan konfigurasi ini, Top data akan mengirim 20 data pertama ke Top Output, dan mengirimkan sisa datanya ke Rest Output
Tambahkan sebuah Row Count, lalu hubungkan Top 20 dengan row count tadi. Pilih Top Output pada dialog Input Output Selction, lalu klik OK. Beri nama Top 20 Count pada Row Count
 
Klik ganda Top 20 Count, isi VariableName dengan User::Top20
Tambahkan Top Data dan beri nama Top 21-40, lalu hubungkan Top 20 dengan Top 21 – 40. Isi nilai Top pada Top 21 – 40 dengan 20, lalu klik OK
Tambahkan juga Row Count dan beri nama Top 21 – 40 Count. Hubungkan Top 21 – 40 dengan Top 21 – 40 Count, pilih Top Output pada dialog Input Output Selection. Isi VariableName pada Top 21 – 40 Count dengan User::Top2140
Dan inilah hasilnya pada saat dieksekusi
|
-
Untuk dapat menggunakan Top Data, di dalam komputer Anda harus sudah terinstal SQL Server 2008 beserta SSIS-nya. Setelah men-download Installer, extract file-nya kedalam sebuah folder lalu jalankan Setup.exe
Klik Next pada dialog Welcome
Klik Next pada dialog Confirm Installation
Proses Instalasi berlangsung
Klik Close pada dialog Installation Complete
Buka BI Development Studio dan buat sebuah project Integration Services
Setelah Package.dtsx terbuka, tambahkan sebuah Data Flow Task dari Toolbox
Buka Data Flow Task tersebut dengan meng-klik ganda Data Flow Task atau meng-klik tab Data Flow. Setelah Data Flow tampil, klik kanan pada Toolbox, lalu pilih menu Choose Items.
Pilih tab SSIS Data Flow Items, beri centang pada Top Data lalu klik OK
Top Data akan tampil pada Toolbox dan siap untuk digunakan
|
-
 Beberapa waktu yang lalu saya menulis tentang bagaimana melakukan top clause di SSIS dengan menggunakan bantuan Script Component. Penggunaan script component tersebut tentu dapat memecahkan masalah untuk satu kali. Tapi bagaimana apabila saya membutuhkan banyak transformasi top dalam SSIS? Penggunaan Script Component yang terlalu banyak akan memperlambat proses development dan membuat ukuran file SSIS menjadi lebih besar.
Selain dengan Script Component kita dapat membuat custom component untuk melakukan top clause di SSIS. Custom component tersebut dibuat dengan menggunakan Visual Studio .net, di-compile, di-deploy, dan dapat digunakan pada SSIS. Penggunaan custom component tentu akan memudahkan developer dalam proses development dan menjadikan ukuran file SSIS menjadi lebih kecil.
Top Data adalah custom SSIS component untuk melakukan top clause di SSIS. Dengan Top Data, Anda dapat melakukan pengambilan data teratas dari hasil transformasi, membuang sisa data, atau melakukan transformasi lain terhadap sisa data tersebut. Yang perlu Anda lakukan adalah menginstal-nya, kemudian menambahkannya ke dalam toolbox di Visual Studio, dan Anda sudah dapat menggunakannya. Berikut adalah screen shot Top Data pada saat digunakan:
Berhubung nggak punya machine ia64, maka installer yang tersedia baru untuk x86 dan x64. Installer-nya dapat Anda download disini. Step-by-step instalasinya dapat dibaca disini, dan manualnya disini. Sedangkan untuk Anda yang ingin mengetahui bagaimana component-nya dibuat, dapat kesini.
|
-
download sample code
Setelah dapat menampilkan SSIS sebagai datasource reporting services, kita dapat mulai menggunakannya dalam pembuatan report. Sebelumnya mungkin ada diantara Anda yang bertanya, kenapa SSIS tidak secara default ditampilkan dalam list data source SSRS. Ini dikarenakan pada saat SSIS di-eksekusi, identity yang digunakan adalah account Report Server Windows Service atau account Report Server Web service, dan akan berimbas pada cara kita melakukan konfigurasi security data source package SSIS. Sehingga Microsoft tidak merekomendasikan SSIS digunakan sebagai data source reporting services dalam server production. Berikut adalah note yang saya kutip dari Books Online.
On the server, the SSIS data processing extension is a non-production feature that is off by default. Using the SSIS data processing extension on a production server is not recommended at this time. If you choose to enable the feature and use it on your report server, be aware that at run time, the package will be processed under the security identity of the Report Server Windows service account or the Report Server Web service account. This has implications on how you secure the data sources that the package accesses.
SSIS
Untuk mulai menggunakannya, pertama buat dulu sebuah Integration Services Project. Tambahkan sebuah SSIS package dengan satu Data Flow Task yang didalamnya terdapat satu Data Reader destination. Pada contoh berikut, saya membuat sebuah package dengan nama Product.dtsx yang men-extract data product yang terdapat pada table Production.Product di database AdventureWorks lalu me-load-nya ke dalam sebuah data reader dengan nama ProductReader. Lalu centang semua input column di Advanced Editor ProductReader.
SSRS
Selanjutnya buat satu Report Server Project dan tambahkan sebuah report dengan nama ProductReport.rdl.
Tambahkan sebuah data source di dalam ProductReport.rdl dengan nama ProductDataSource, Pilih SSIS sebagai type connection, dan isi connection string-nya dengan path SSIS package. Apabila package-nya disimpan di file system, gunakan format -file [path], sedangkan untuk package yang disimpan di SQL Server, gunakan format -sql [path].
Selanjutnya tambahkan sebuah dataset dengan nama ProductDataset, dengan datasource dari ProductdataSource. Isi Query dengan nama data reader destination yang terdapat pada Product.dtsx, ProductReader.
Apabila kita buka tab Fields, akan muncul semua field yang terdapat pada input column ProductReader.
Setelah selesai, tambahkan Tablix ke dalam report, isi field-nya dengan field yang terdapat pada ProductDataSet.
Pada saat di-preview, report akan menampilkan data hasil eksekusi Product.dtsx
|
-
Beberapa waktu lalu saya sempat belajar sedikit mengenai IE8, dan salah satu yang menarik perhatian saya pada waktu itu adalah fitur search provider. Dengan fitur tersebut, kita bukan saja dapat melakukan pencarian pada situs yang memiliki search provider, tapi juga dapat menambahkan sendiri search provider untuk situs yang belum memilikinya.
Dan sembari belajar tadi, iseng-iseng saya mencoba membuat search provider untuk SQLServer-Indo. Setelah jadi dan menggunakannya selama beberapa waktu, saya berpikir untuk men-share-nya. Jadi untuk Anda yang ingin menggunakan search provider tersebut, silakan klik tombol yang terdapat pada drop down search provider di kanan atas IE, pilih menu Add Search Providers > SQLServer-Indo Search Provider
Klik tombol Add pada saat muncul dialog Add Search Provider. Anda dapat menjadikannya sebagai default search provider dengan mencentang pilihan Make this my default provider
Apabila berhasil, search provider tersebut akan tampil pada list search provider Anda.
Sebagai tambahan, Search Provider-nya juga dapat digunakan untuk Anda yang menggunakan IE7 atau Mozilla Firefox. Selamat mencoba.
|
-
Lookup merupakan salah satu komponen yang paling sering saya gunakan dalam proses ETL di SSIS. Dengan konfigurasi yang baik, akan membantu mengoptimalkan kinerja proses ETL.
-
Melakukan Full Cache. Dengan melakukan Full Cache berarti penambahan memory pada saat Pre-Execute Package karena semua data di-cache terlebih dahulu, tetapi hal ini akan mengurangi round trip yang ditimbulkan dengan Partial Cache atau No Cache. Yang saya pelajari dari SSIS adalah semakin besar memory akan semakin baik untuk performance, dan dalam case Lookup inilah memory benar-benar dibutuhkan
-
Meng-Ignore Failure (apa yah bahasa Indonesianya?) untuk row-row yang tidak ketemu lookup-nya. Dulu saya mengeset nilai-nya dengan Redirect rows to no match output atau Redirect rows to error output di SSIS 2005, dan ternyata hal ini menurunkan performance, juga memperlambat proses development karena saya harus menambahkan dua buah component, Derived Column dan Union All.
|
-
Ada beberapa hal yang dapat dilakukan agar performance SSIS pada saat melakukan load data ke sebuah table optimum. Berikut adalah konfigurasi yang biasanya saya gunakan:
- Menggunakan Table or view – fast load sebagai Data Access Mode
- Mencentang opsi Keep Identity agar tidak terjadi error pada table yang memiliki column dengan identity
-
Mencentang opsi Keep Null. Apabila tidak dicentang, pada saat me-load data Null SSIS akan menyimpannya sebagai nilai blank dan bukan Null
-
Tidak mencentang opsi Table Lock agar tidak terjadi proses Load data table tidak di-lock
-
Tidak mencentang opsi Check Constraint. Pengecekan constraint akan menurunkan performa karena SSIS akan melakukan pengecekan constraint untuk setiap row yang di-load
-
Apabila ukuran data yang di-insert sangat besar (contohnya pada saat initial load), sebaiknya isi Maximum insert commit size dengan nilai yang tidak terlalu besar. Secara default SSIS 2005 mengisinya dengan nilai kosong, sedangkan SSIS 2008 mengisinya dengan 2147483647. Pada saat data di-load, data disimpan terlebih dahulu di TempDB untuk kemudian di-commit setelah semua data di-load. Agar TempDB-nya tidak membengkak, harus dilakukan commit ke database tiap per beberapa row. Banyaknya row yang di-commit diatur pada opsi ini. Saya selalu mengisinya dengan 1000000.
|
-
Saya ingat setahun yang lalu, sebelum SQL Sever 2008 di-launch, seorang teman baik saya memberikan sebuah DVD tentang fitur-fitur yang terdapat pada SSRS. Salah satu fitur yang membuat saya sangat excited waktu itu adalah bahwa kita dapat menggunakan SSIS sebagai data source-nya. Yang membuat excited adalah fakta bahwa SSIS memiliki performance yang mumpuni untuk melakukan proses ETL. Menggunakan data dari proses ETL SSIS tentunya berarti peningkatan performance pada saat menampilkan report jika dibandingkan dengan query join. Pada saat menginstall SQL Server 2008 saya cukup kecewa mendapati SSIS tidak terdapat dalam list datasource yang disediakan oleh SSRS.
Tahun berganti, dan beberapa hari yang lalu saya harus belajar SSRS untuk keperluan sebuah project. Secara tidak sengaja saya mendapati sebuah artikel di Books Online tentang bagaimana cara menampilkan SSIS dalam list data source SSRS.
Mengaktifkan Ekstensi SSIS pada Data Source Report Designer
Buka file RSReportDesigner.config yang terdapat pada C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
Hilangkan comment (karakter <!--- pada bagian depan dan –-> pada bagian akhir) pada section <data> berikut ini:
<!-- <Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Verqsion=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/> –>
Menjadi
<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
Hilangkan juga comment pada section <Designer> berikut ini:
<!-- <Extension Name="SSIS" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/> –>
Menjadi
<Extension Name="SSIS" Type="Microsoft.ReportingServices.QueryDesigners.GenericQueryDesigner,Microsoft.ReportingServices.QueryDesigners"/>
Setelah selesai, save file.
Mengaktifkan Ekstensi SSIS pada Report Server
Buka file RSReportServer.config yang terdapat di c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer
Hilangkan comment pada entry berikut ini:
<!-- <Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/> –>
Menjadi
<Extension Name="SSIS" Type="Microsoft.SqlServer.Dts.DtsClient.DtsConnection,Microsoft.SqlServer.Dts.DtsClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
Sekarang apabila Anda membuka report designer dan menambahkan data source, Anda akan melihat SSIS dalam list-nya.
|
-
Sepagian ini saya dibikin pusing karena gagal melakukan instalasi SQL Server 2008 di komputer. Error yang aneh karena selama ini saya selalu sukses melakukannya. Apabila saya biasanya melakukan instalasi di mesin x86, pagi ini saya menginstal di mesin x64. Pada awalnya proses instalasi berjalan lancar, sampai pada bagian instalasi Reporting Services, muncul error “Input string was not in a correct format”. Akhirnya proses instalasi selesai dengan tidak mulus. Semua service yang telah diinstal, mulai dari Database Engine, Analysis Services, dan Integration Services dapat dijalankan kecuali Reporting Services. Mungkin memang salah saya juga karena saya menginstal menggunakan media SQL Server 2008 RTM dan bukan SQL Server 2008 SP1 yang sudah tersedia. Setelah beberapa kali gagal dan searching dengan menggunakan koneksi internet yang putus nyambung, akhirnya ketemu juga sumber masalahnya. Ternyata hal ini disebabkan karena performance counter yang corupt dan mengharuskan kita me-rebuild kembali registry performance counter. Untuk melakukannya, saya membuka command prompt, dan meng-execute command lodctr /r. Setelah selesai, saya meng-install ulang SQL Server 2008 dan berhasil dengan mulus.
|
-
Pada saat melakukan proses extract data di SSIS dengan menggunakan OLE DB Source, ada beberapa konfigurasi yang dapat kita lakukan untuk membantu mempercepat proses-nya. - Gunakan OLD DB Provider yang merupakan bawaan database yang bersangkutan. Sebagai contoh, pada saat akan meng-extract data dari SQL Server, terdapat dua provider yang dapat digunakan, yaitu Microsoft OLE DB Provider for SQL Server dan SQL Server Native Client. Ternyata SQL Server Native Client memiliki performance lebih baik jika dibandingkan dengan Mircosoft OLE DB Provider for SQL Server.
- Jangan gunakan Table or view atau Table name or view name variable sebagai Data Access Mode. Apabila dicek dengan menggunakan profiler, ketika package dieksekusi OLE DB Source akan menjalankan statement Select *. Jika struktur table atau view tidak diubah, tentu tidak akan menjadi masalah. Tetapi jika strukturnya diubah, terutama penghapusan satu atau lebih column, akan menyebabkan error pada saat eksekusi.
- Pada saat menggunakan SQL Command atau SQL command from Variable sebagai Data Access Mode, jangan gunakan statement Select *. Bukan saja akan mengakibatkan error seperti yang dipaparkan pada point kedua, tetapi juga akan menurunkan performance. Sebaiknya sebutkan column-column table yang akan di-extract, dan pilih hanya column-column yang dibutuhkan pada proses ETL agar performance-nya tetap terjaga.
- Gunakan NOLOCK table hint agar proses select data tidak mengganggu proses transaksi yang terjadi terhadap table.
|
More Posts
|
|
|