February 2006 - Posts
The design of a data warehouse database and online analytical processing (OLAP) cubes is fundamentally different than a transactional processing
database (OLTP). The data warehouse is specifically designed to facilitate super fast query times and multi-dimensional analysis. The following table
summarizes the major differences between OLTP and OLAP system design.
OLAP vs OLTP
|
|
OLTP System
Online Transaction Processing
(Operational System)
|
OLAP System
Online Analytical Processing
(Data Warehouse)
|
|
Source of data
|
Operational data; OLTPs are the original source
of the data.
|
Consolidation data; OLAP data comes from the
various OLTP Databases
|
|
Purpose of data
|
To control and run fundamental business tasks
|
To help with planning, problem solving, and
decision support
|
|
What the data
Reveals
|
A snapshot of ongoing business processes
|
Multi-dimensional views of various kinds of
business activities
|
|
Inserts and
Updates
|
Short and fast inserts and updates initiated by
end users
|
Periodic long-running batch jobs refresh the data
|
|
Queries
|
Relatively standardized and simple queries
Returning relatively few records
|
Often complex queries involving aggregations
|
|
Processing
Speed
|
Typically very fast
|
Depends on the amount of data involved; batch
data refreshes and complex queries may take
many hours; query speed can be improved by
creating indexes
|
|
Space
Requirements
|
Can be relatively small if historical data is
archived
|
Larger due to the existence of aggregation
structures and history data; requires more indexes
than OLTP
|
|
Database
Design
|
Highly normalized with many tables
|
Typically de-normalized with fewer tables; use of
star and/or snowflake schemas
|
|
Backup and
Recovery
|
Backup religiously; operational data is critical to run the business,
data loss is likely to entail
significant monetary loss and legal liability
|
Instead of regular backups, some environments
may consider simply reloading the OLTP data as a
recovery method
|
Dari milis SQLServer-Indo ada pertanyaan tentang bagaimana agar setiap data yang masuk kedatabase kita tidak mengalami ketergantungan dengan regional setting di pc client.
contoh select statmentnya :
cm.CommandText = "SET NOCOUNT ON" + vbNewLine + _
" UPDATE [Trans Arisan Sub Table] SET StatusBayar = 'Lunas', [Tgl Terima]='" + CStr(Format(Me.Tgl_Terima, "yyyy-mm-dd")) + "', [Jenis Bayar]='" + CStr(Me.Jns_bayar) + "'," + _
" [Kode Rekening]='" + CStr(Me.Kode_Rekening) + "'" + _
" WHERE [No Nota Sub] = " & CStr(Me.No_Nota_Sub) + vbNewLine + _
" UPDATE [trans arisan] SET optr = '" + GetUserID$ + "', [last update] = getdate() WHERE [No Nota] = " + CStr(Me.[No Nota])
Buat teman teman yang bisa menggunakan inline SQL statement, di codenya tentunya pernah mengalami masalah ini dimana untuk format tanggal dan decimal akan sangat bergantung dengan format data di berdasarkan regional setting di PC Client. jika regional setting kita dengan server berbeda maka data datetime atau decimal akan berbeda pula formatnya khususnya ketika kita melakukan conversi ke string seperti pada statement query diatas CStr(Me.Kode_Rekening)
Untuk mengatasi masalah ini bisa dilakukan dengan cara melakukan passing parameter terhadap inline query yang dimaksud. Dengan menggunakan SQL parameter object.
Syntax
Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)
Kita bisa masukkan type yang kita inginkan untuk parameternya sehingga tidak tergantung pada regional setting. Ini berlaku juga untuk tipe data numeric decimal.
Contoh penggunaan SQL parameter dengan VBA:
' Open command object with one parameter
Set cmdByRoyalty = New ADODB.Command
' Parameter yang akan dipasing di tulis sebagai @Namavaliable di query yang kita buat
cmdByRoyalty.CommandText = "UPDATE [Trans Arisan Sub Table] SET StatusBayar = 'Lunas', [Tgl Terima]=@TglTerima"
cmdByRoyalty.CommandType = adCmdText
' Get parameter value and append parameter
Set paramtglterima = cmdByRoyalty.CreateParameter("@TglTerima", adDatetime, adParamInput)
cmdByRoyalty.Parameters.Append paramtglterima
prmByRoyalty.Value = Me.Tgl_Terima
' Create recordset by executing the command
Set cmdByRoyalty.ActiveConnection = Cnxn
Set rstByRoyalty = cmdByRoyalty.Execute
Lebih jelasnya tentang sql parameter bisa dilihat disini:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthappendx.asp
Sebagai masukan dan best pratice lebih baik gunakan store procedure dan passing value menggunakan sql parameter object. Selain akan mudah dimaintain akan lebih baik juga dari sis performance yang dihasilkan.
Untuk membandingkan pentingnya kita menggunakan Store procedure, bisa di lihat juga di artikel yang pernah saya tulis beberapa waktu yang lalu di sini :
http://blogs.netindonesia.net/kiki/articles/6299.aspx
Hari ini saya ikut menghadiri SQL user group meeting, pembicaranya adalah mas mca, sama mas basir.
acaranya dihadiri oleh kurang lebih 20 peserta anggota millis SQL indo. materinya tentang BI dan Service broker
acara ini akan diadakan tiap bulan di sekretariat SQL-indo di Ms Indonesia.
Buat yang ingin tahu apa yang dimaksud dengan EAI dan bagaimana
menggunakan Biztalk untuk menyelesaikan masalah EAI, saya sudah upload
ebook tentang EAI dengan biztalk. buat yang minat bisa baca di link
ini
http://groups.yahoo.com/group/xml-indonesia/files/integrationpatterns_biztalk.pdf
Wah saya sedang ketemu excitment baru nih, pas tadi pagi mas hermawan telp saya kasih tau ada milis yang bicara masalah XML, Biztalk, Web Logic dst yang mungkin lebih banyak bicara masalah integrasi dan midle tier teknoligi membuat saya sangat tertarik. saya coba gabung ternyata membernya juga lumayan ada sekita 400 orang. hanya saja dari penjelasan moderatornya milis ini tidak seramai dulu ketika di canangkan (didirikan sejak tahun 2001)
Menurut mas kukuh sang moderator milis ini, terakhir agak kurang ramai karena sering dapat junk mail sehingga mode emailnya jadi moderated. dan ini tidak terkontrol sehingga membernya banyak yang unsubscibe. mudah mudahan milis ini bisa lebih aktif lagi apalagi dari segi teknology banyak sekali yang bisa didiskusikan seperti biztalk yang sampai hari ini sudah banyak sekali enhancement khususnya Biztalk 2006
Milisnya adalah http://groups.yahoo.com/group/xml-indonesia/
Mungkin ada yang pernah mengenerate XML dari SQL, di SQL 2000 ada fasilitas untuk mengasilkan query dalam format XML.
Statement Sama dengan perintah select biasa. Statementnya lebih dikenal dengan SELECT ... FOR XML
Contoh :
SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID
FOR XML Auto
Perintah SELECT … For XML ini terdiri dari beberapa mode yaitu ROW, AUTO dan Explicit
Berikut perbedaaan masing masing mode:
1.Raw Mode
RAW Mode melakukan transformasi setiap row data dari query dan menyimpan hasilnya dalam sebuah XML element dengan nama element merupakan nama dari setiap Row. Setiap column yang memiliki nilai Not Null akan di mapping kedalam attribute yang memiliki nama yang sama dengan nama columnnya.
|
SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID
FOR XML RAW |
Out put dari query diatas adalah :
|
<row CustomerID="ALFKI" OrderID="10643" OrderDate="1997-08-25T00:00:00"/>
<row CustomerID="ANATR" OrderID="10308" OrderDate="1996-09-18T00:00:00"/>
<row CustomerID="ANATR" OrderID="10625" OrderDate="1997-08-08T00:00:00"/> |
2. AUTO mode
AUTO mode akan mengembalikan hasil query berupa nested XML elements. Sama seperti RAW mode, setiap column yang memiliki nilai not NULL akan di map sebagai sebuah attribute yang diberi nama sama dengan column name ataupun aliasnya. XML yang dihasilkan akan merupakan representasi dari hubungan Header and detail dari relasi join yang dilakukan oleh Query
|
SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName,
Orders.OrderID, Orders.OrderDate, Orders.EmployeeID
FROM Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Employees.EmployeeID ASC
FOR XML AUTO |
Hasil XMLnya:
|
<Employees EmployeeID="1" LastName="Davolio" FirstName="Nancy">
<Orders OrderID="10258" OrderDate="1996-07-17T00:00:00" EmployeeID="1"/>
<Orders OrderID="10270" OrderDate="1996-08-01T00:00:00" EmployeeID="1"/>
</Employees>
<Employees EmployeeID="2" LastName="Fuller" FirstName="Andrew">
<Orders OrderID="10248" OrderDate="1996-07-04T00:00:00" EmployeeID="5"/>
<Orders OrderID="10249" OrderDate="1996-07-05T00:00:00" EmployeeID="6"/>
</Employees> |
Salah satu kekurangan dari mode AUTO dan RAW adalah pada kalanya kita akan memerlukan root dari XML document yang kita miliki, hal ini tidak menjadi masalah di VB6. untuk itu mengatasi kebutuhan ini maka kita bisa menggunakan mode EXPLISIT
3. EXPLISIT Mode
Pada EXPLICIT mode, hasil XML document yang dihasilkan oleh query dapat diatur sesuai dengan querynya. Perintah Select yang terpisah yang menggunakan UNION All stament dapat menghasilkan striktur Nasted XML Document yang sesuai dengan levelnya.
|
SELECT 1 as Tag, NULL as Parent,
c.CustomerID as [Customer!1!CustomerID],
NULL as [Order!2!OrderID]
FROM Customers c
UNION ALL
SELECT 2 as Tag, 1 as Parent,
c.CustomerID as [Customer!1!CustomerID],
o.OrderID as [Order!2!OrderID]
FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT |
Hasil XMLnya adalah :
|
<Customer CustomerID="ALFKI">
<Order OrderID="10643">
<Order OrderID="10692">
...
</Customer>
<Customer CustomerID="ANATR">
<Order OrderID="10308">
<Order OrderID="10625">
...
</Customer> |
Untuk dapat lebih jelas mengenai contoh-contohnya dapat dilihat di SQL Book Online
Demikian sedikit sharing dari saya semoga bermanfaat, kalo ada tanggapan atau hal yang menarik untuk didiskusikan saya tunggu ya.