I. Lampiran Wawancara T : Apa kendala yang dihadapi oleh perusahaan terutama pada bagian export dan import? J : Kendala yang dihadapi oleh perusahaan ini adalah belum efektifnya bagian export dan import dalam menyajikan suatu laporan yang ringkas yang dapat digunakan untuk melakukan analisis data yang dapat digunakan untuk membuat keputusan dan juga data yang ada pada setiap cabang diambil melalui email jadi belum ada integrasi antara cabang dan pusat.
T: Usaha dan cara apa yang telah dilakukan untuk mengatasi masalah tersebut? J : Selama ini kami mengatasinya dengan melihat database yang sudah ada dalam bidang export dan import serta mencatat laporan data tiap bulannya sehingga dihasilkan suatu laporan dan untuk data yang ada di cabang sampai saat ini belum terintegrasi.
T: Apakah usaha yang dilakukan telah menyelesaikan masalah tersebut? J : M asih belum dapat mengatasi permasalahan, sehingga kami membutuhkan suatu aplikasi yang dapat digunakan untuk melihat laporan yang cepat, tepat dan akurat serta dapat mengintegrasikan data antara cabang dan pusat.
T : Dimana saja cabang TM S? J : Pusatnya ada di Jakarta, lalu cabang-cabangnya ada di Surabaya, Semarang, Panjang, Ujung Pandang, Bandung, Banjarmasin, M edan, Bitung, Tanjung Priuk, Bandar Lampung. L1
T : Apa saja layanan-layanan yang ada pada TM S? J : Layanan yang ada di TM S diantaranya yaitu sarana angkutan export-import, Agen Pelayaran, Jasa Bongkar dan Jasa Muat Petikemas, Pelayanan Dokumen EksportImport
T : Bagaimana proses bisnis export dan import di TM S ? J : (hasil wawancara dapat dilihat pada bagian proses bisnis bab 3)
T : Hardware dan Software apa saja yang ada di perusahaan ini? J : Perusahaan ini menggunakan processor pentium IV 2GHz, hard disk 80 GB SATA 7200
rpm, memory DDR2 256 dan 512 MB, Motherboard ASUS, VGA Card Intel,
Sound card Vibra Live, Ethernet Card D-LINK PCI 10/100, Monitor PHILIPS 15 inch, Casing Simbadda 300 watt, Keyboard & Mouse Logitech, Printer HP Laser 1020, Epson LQ 2180, Switch 3Com 100 untuk hardware dan untuk softwarenya sistem operasi Linux, Windows XP Profesional SP 2, development tools Clipper, DBMS Fox pro fox base, Jaringan LAN &wireless, server Novell, modem ADSL.
T : Berapa user yang menggunakan komputer? J : User yang menggunakan komputer ada 130 user dan itu user yang dipusat saja.
L2
II. Lampiran Coding Vb.Net 2005 a. Form Login Imports System.Data.SqlClient Public Class frmLogin Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click Me.Close() End Sub Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click connect() Dim cmd As New SqlCommand cmd.Connection = Conn cmd.CommandType = CommandType.Text cmd.CommandText = "select * from MsUser where Username = '" & txtUsername.Text & "' and Password = '" & txtPass.Text & "'" Dim reader As SqlDataReader reader = cmd.ExecuteReader If txtUsername.Text = "" Then MessageBox.Show("Username must be filled", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information) txtUsername.Focus() ElseIf txtPass.Text = "" Then MessageBox.Show("Password must be filled", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information) txtPass.Focus() ElseIf Not reader.HasRows Then MessageBox.Show("Username and password are not registered", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning) txtUsername.Focus() Else frmUtama.Show() Me.Hide() End If reader.Close() txtUsername.Text = "" txtPass.Text = "" End Sub
b. Form Utama Public Class frmUtama Private Sub LogoutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LogoutToolStripMenuItem.Click 'log.ShowDialog() log.ShowDialog()
L3
End Sub Private Sub AddUserToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddUserToolStripMenuItem.Click frmAddUSer.ShowDialog() End Sub Private Sub ChangePasswordToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ChangePasswordToolStripMenuItem.Click frmChangePswd.ShowDialog() End Sub Private Sub ExitToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExitToolStripMenuItem.Click End End Sub Private Sub ExportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ExportToolStripMenuItem.Click frmExport.ShowDialog() End Sub Private Sub ImportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportToolStripMenuItem.Click frmImport.ShowDialog() End Sub Private Sub ContainerToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) frmChartContainer.ShowDialog() End Sub Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick 'Label1.Text = Now.Hour & ":" & Now.Minute & ":" & Now.Second Label1.Text = Now.ToString("hh:mm:ss tt") End Sub Private Sub frmUtama_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Label1.Text = Now.Hour & ":" & Now.Minute & ":" & Now.Second 'Label1.Text = TimeOfDay Label1.Text = Now.ToString("hh:mm:ss tt") End Sub Private Sub AboutToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AboutToolStripMenuItem.Click frmAbout.ShowDialog() End Sub
L4
Private Sub FaktaExportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FaktaExportToolStripMenuItem.Click frmStarExport.ShowDialog() End Sub Private Sub FaktaImportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FaktaImportToolStripMenuItem.Click FrmStarImport.ShowDialog() End Sub Private Sub JumlahShipperToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahShipperToolStripMenuItem.Click frmChartJumlahShipper.ShowDialog() End Sub Private Sub JumlahContainerToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahContainerToolStripMenuItem.Click frmChartContainer.ShowDialog() End Sub Private Sub JumlahJenisCommodityToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahJenisCommodityToolStripMenuItem.Click frmChartJumlahJenisCommodityExport.ShowDialog() End Sub Private Sub JumlahExportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahExportToolStripMenuItem.Click frmChartJumlahExport.ShowDialog() End Sub Private Sub JumlahConsigneeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahConsigneeToolStripMenuItem.Click frmChartJumlahConsignee.ShowDialog() End Sub Private Sub JumlahContainerToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahContainerToolStripMenuItem1.Click frmChartJumlahContainer.ShowDialog() End Sub Private Sub ImportToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ImportToolStripMenuItem1.Click End Sub
L5
Private Sub JumlahJenisCommodityToolStripMenuItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahJenisCommodityToolStripMenuItem1.Click frmChartJumlahJenisCommodityImport.ShowDialog() End Sub Private Sub JumlahImportToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahImportToolStripMenuItem.Click frmChartJumlahImport.ShowDialog() End Sub Private Sub JumlahAgentToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles JumlahAgentToolStripMenuItem.Click frmChartJumlahAgent.ShowDialog() End Sub End Class
c. Form Add User Imports System.Data.SqlClient Public Class frmAddUSer Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click Me.Close() End Sub Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click connect() Dim cmd As New SqlCommand Dim rs As Integer If txtPassword.Text <> txtConfirm.Text Then MessageBox.Show("Please check your password", "Warning", MessageBoxButtons.OK) txtUsername.Focus() Else cmd.Connection = Conn cmd.CommandType = CommandType.Text cmd.CommandText = "insert into MsUser values('" & txtUsername.Text & "','" & txtPassword.Text & "')" rs = cmd.ExecuteNonQuery MessageBox.Show("Add User has been successfully", "Message", MessageBoxButtons.OK) Me.Close() End If End Sub
L6
d. Form Change Password Imports System.Data.SqlClient Public Class frmChangePswd Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click Me.Close() End Sub Private Sub btnChange_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChange.Click connect() Dim cmd As New SqlCommand Dim rs As Integer If txtNew.Text <> txtConfirm.Text Then MessageBox.Show("Please check your password", "Warning", MessageBoxButtons.OK) txtNew.Focus() Else cmd.Connection = Conn cmd.CommandType = CommandType.Text cmd.CommandText = "update MsUser set [Password] = '" & txtNew.Text & "' where Username = '" & frmLogin.txtUsername.Text & "'" rs = cmd.ExecuteNonQuery MessageBox.Show("Change Password has been successfully", "Message", MessageBoxButtons.OK) Me.Close() End If End Sub
e. Form Chart Container Public Class frmChartContainer Private Sub frmChartContainer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'VwContainer.vw_Container' table. You can move, or remove it, as needed. Me.Vw_ContainerTableAdapter.Fill(Me.VwContainer.vw_Container) End Sub End Class
f. Form Chart Jumlah Agent Public Class frmChartJumlahAgent Private Sub frmChartJumlahAgent_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
L7
'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_JumlahExportByAgent' table. You can move, or remove it, as needed. Me.Vw_JumlahExportByAgentTableAdapter.Fill(Me.TRESNA_DWHDataSet.vw_Juml ahExportByAgent) End Sub End Class
g.
Form Chart Jumlah Consignee
Public Class frmChartJumlahConsignee Private Sub frmChartJumlahConsignee_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_JumlahConsignee' table. You can move, or remove it, as needed. Me.Vw_JumlahConsigneeTableAdapter.Fill(Me.TRESNA_DWHDataSet.vw_JumlahCo nsignee) End Sub End Class
h. Form Chart Jumlah Container Public Class frmChartJumlahContainer Private Sub frmChartJumlahContainer_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_ContainerImport' table. You can move, or remove it, as needed. Me.Vw_ContainerImportTableAdapter.Fill(Me.TRESNA_DWHDataSet.vw_Containe rImport) End Sub End Class
i. Form Chart Jumlah Export Public Class frmChartJumlahExport Private Sub frmChartJumlahExport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_JumlahExportByBranch' table. You can move, or remove it, as needed.
L8
Me.Vw_JumlahExportByBranchTableAdapter.Fill(Me.TRESNA_DWHDataSet.vw_Jum lahExportByBranch) End Sub
j. Form Chart Jumlah Import Public Class frmChartJumlahImport Private Sub frmChartJumlahImport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_JumlahImportByBranch' table. You can move, or remove it, as needed. Me.Vw_JumlahImportByBranchTableAdapter.Fill(Me.TRESNA_DWHDataSet.vw_Jum lahImportByBranch) End Sub End Class
k. Form Chart Jumlah Jenis Commodity Export Public Class frmChartJumlahJenisCommodityExport Private Sub frmChartJumlahJenisCommodityExport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_JumlahJenisCommodityExport' table. You can move, or remove it, as needed. Me.Vw_JumlahJenisCommodityExportTableAdapter.Fill(Me.TRESNA_DWHDataSet. vw_JumlahJenisCommodityExport) End Sub End Class
l. Form Chart Jumlah Jenis Commodity Import Public Class frmChartJumlahJenisCommodityImport Private Sub frmChartJumlahJenisCommodityImport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_JumlahJenisCommodityImport' table. You can move, or remove it, as needed. Me.Vw_JumlahJenisCommodityImportTableAdapter.Fill(Me.TRESNA_DWHDataSet. vw_JumlahJenisCommodityImport) End Sub End Class
L9
m. Form Chart Jumlah Shipper Public Class frmChartJumlahShipper Private Sub frmChartJumlahShipper_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'TODO: This line of code loads data into the 'TRESNA_DWHDataSet.vw_JumlahExportByShipper' table. You can move, or remove it, as needed. Me.Vw_JumlahExportByShipperTableAdapter.Fill(Me.TRESNA_DWHDataSet.vw_Ju mlahExportByShipper) End Sub End Class
n. Form Export Public Class frmExport Private Sub frmExport_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Label1.Text = TimeOfDay Label1.Text = Now.ToString("hh:mm:ss tt") End Sub Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick Label1.Text = TimeOfDay 'Label1.Text = Now.ToString("hh:mm:ss tt") End Sub
Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged If ComboBox1.SelectedIndex = 0 Then Me.ChartControl1.SeriesTemplate.ChangeView(DevExpress.XtraCharts.ViewTy pe.Bar) ElseIf ComboBox1.SelectedIndex = 1 Then Me.ChartControl1.SeriesTemplate.ChangeView(DevExpress.XtraCharts.ViewTy pe.Line) 'ElseIf ComboBox1.SelectedIndex = 2 Then ' Me.ChartControl1.SeriesTemplate.ChangeView(DevExpress.XtraCharts.ViewTy pe.SplineArea) End If End Sub End Class
L10
o. Form Import Public Class frmImport Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick 'Label1.Text = TimeOfDay Label1.Text = Now.ToString("hh:mm:ss tt") End Sub Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Label1.Click 'Label1.Text = TimeOfDay Label1.Text = Now.ToString("hh:mm:ss tt") End Sub
p.
Form About
Public Class frmAbout Private Sub btnOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOk.Click Me.Hide() End Sub
q. Module 1 Imports System.Data.SqlClient Module Module1 Public Conn As New SqlConnection Public Sub connect() If Conn.State = ConnectionState.Closed Then Conn.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Password=password;Initial Catalog=TRESNA_DWH;Data Source=." Conn.Open() End If End Sub Public log As New frmLogin End Module
L11
III. Lampiran Sintax SQL S erver 2005 a. Dim Time USE [TRESNA_DWH] GO /****** Object: Table [dbo].[DimTime] Script Date: 01/22/2010 19:43:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DimTime]( [TimeID] [int] IDENTITY(1,1) NOT NULL, [CalendarDate] [datetime] NULL, [Year] AS (isnull(CONVERT([smallint],datepart(year,[CalendarDate]),(0)),(0))), [Quarter] AS (case when datepart(month,[CalendarDate])>=(1) AND datepart(month,[CalendarDate])<=(3) then 'Q1' when datepart(month,[CalendarDate])>=(4) AND datepart(month,[CalendarDate])<=(6) then 'Q2' when datepart(month,[CalendarDate])>=(7) AND datepart(month,[CalendarDate])<=(9) then 'Q3' when datepart(month,[CalendarDate])>=(10) AND datepart(month,[CalendarDate])<=(12) then 'Q4' else 'MISSING DATE' end), [Month] AS (case when datepart(month,[CalendarDate])=(1) then 'Jan' when datepart(month,[CalendarDate])=(2) then 'Feb' when datepart(month,[CalendarDate])=(3) then 'Mar' when datepart(month,[CalendarDate])=(4) then 'Apr' when datepart(month,[CalendarDate])=(5) then 'May' when datepart(month,[CalendarDate])=(6) then 'Jun' when datepart(month,[CalendarDate])=(7) then 'Jul' when datepart(month,[CalendarDate])=(8) then 'Aug' when datepart(month,[CalendarDate])=(9) then 'Sep' when datepart(month,[CalendarDate])=(10) then 'Oct' when datepart(month,[CalendarDate])=(11) then 'Nov' when datepart(month,[CalendarDate])=(12) then 'Dec' else 'MISSING DATE' end), [Day] AS (isnull(CONVERT([smallint],datepart(day,[CalendarDate]),(0)),(0))), UNIQUE NONCLUSTERED ( [CalendarDate] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
L12
b. Dim Branch USE [TRESNA_DWH] GO /****** Object: Table [dbo].[DimBranch] Script Date: 01/22/2010 19:42:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DimBranch]( [BranchID] [int] IDENTITY(1,1) NOT NULL, [KdBranch] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NamaBranch] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_DimBranch] PRIMARY KEY CLUSTERED ( [BranchID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
c. Dim Agent USE [TRESNA_DWH] GO /****** Object: Table [dbo].[DimAgent] Script Date: 01/22/2010 19:41:46 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DimAgent]( [AgentID] [int] IDENTITY(1,1) NOT NULL, [KdAgent] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NamaAgent] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_DimAgent] PRIMARY KEY CLUSTERED ( [AgentID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
d. Dim Container USE [TRESNA_DWH] GO /****** Object: Table [dbo].[DimContainer] 19:44:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
L13
Script Date: 01/22/2010
GO CREATE TABLE [dbo].[DimContainer]( [ContainerID] [int] IDENTITY(1,1) NOT NULL, [KdContainer] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [JenisContainer] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_DimContainer] PRIMARY KEY CLUSTERED ( [ContainerID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
e. Dim Commodity USE [TRESNA_DWH] GO /****** Object: Table [dbo].[DimCommodity] Script Date: 01/22/2010 19:44:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DimCommodity]( [CommodityID] [int] IDENTITY(1,1) NOT NULL, [KdCommodity] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [JenisCommodity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_DimCommodity] PRIMARY KEY CLUSTERED ( [CommodityID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
f. Dim Consignee USE [TRESNA_DWH] GO /****** Object: Table [dbo].[DimConsignee] Script Date: 01/22/2010 19:44:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DimConsignee]( [ConsigneeID] [int] IDENTITY(1,1) NOT NULL, [KdConsignee] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NamaConsignee] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_DimConsignee] PRIMARY KEY CLUSTERED (
L14
[ConsigneeID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
g. Dim Shipper USE [TRESNA_DWH] GO /****** Object: Table [dbo].[DimShipper] Script Date: 01/22/2010 19:45:08 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DimShipper]( [ShipperID] [int] IDENTITY(1,1) NOT NULL, [KdShipper] [nchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NamaShipper] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_DimShipper] PRIMARY KEY CLUSTERED ( [ShipperID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
h. Fakta Export USE [TRESNA_DWH] GO /****** Object: Table [dbo].[FaktaExport] Script Date: 01/22/2010 19:45:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FaktaExport]( [TimeID] [int] NULL, [BranchID] [int] NULL, [ShipperID] [int] NULL, [ContainerID] [int] NULL, [AgentID] [int] NULL, [CommodityID] [int] NULL, [Jumlahshipper] [float] NULL, [JumlahJenisCommodityExport] [float] NULL, [JumlahContainer] [float] NULL, [JumlahExport] [float] NULL, [TotalExport] [float] NULL ) ON [PRIMARY]
L15
i. Fakta Import USE [TRESNA_DWH] GO /****** Object: Table [dbo].[FaktaImport] Script Date: 01/22/2010 19:45:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[FaktaImport]( [TimeID] [int] NULL, [BranchID] [int] NULL, [ConsigneeID] [int] NULL, [ContainerID] [int] NULL, [CommodityID] [int] NULL, [JumlahConsignee] [float] NULL, [JumlahJenisCommodityImport] [float] NULL, [JumlahContainerImport] [float] NULL, [JumlahImport] [float] NULL, [TotalImport] [float] NULL ) ON [PRIMARY]
j. Ms User USE [TRESNA_DWH] GO /****** Object: Table [dbo].[MsUser] Script Date: 01/22/2010 19:46:18 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MsUser]( [Username] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Password] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_MsUser] PRIMARY KEY CLUSTERED ( [Username] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
k. View Container Export USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_Container] 19:47:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
L16
Script Date: 01/22/2010
GO create view [dbo].[vw_Container] as select JenisContainer, sum(JumlahContainer) JumlahContainer from FaktaExport FE inner join dbo.DimContainer DB ON FE.ContainerID = DB.ContainerID group by JenisContainer
l. View Container Import USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_ContainerImport] Script Date: 01/22/2010 19:47:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [dbo].[vw_ContainerImport] as select JenisContainer, sum(JumlahContainerImport) JumlahContainer from FaktaImport FE inner join dbo.DimContainer DB ON FE.ContainerID = DB.ContainerID group by JenisContainer
m. View Jumlah Consignee USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_JumlahConsignee] Script Date: 01/22/2010 19:48:04 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [dbo].[vw_JumlahConsignee] as select NamaConsignee, sum(JumlahConsignee)JumlahConsignee from dbo.FaktaImport FI INNER JOIN dbo.DimConsignee DC ON FI.ConsigneeID = DC.ConsigneeID GROUP BY NamaConsignee
L17
n. View Jumlah Export by Agent USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_JumlahExportByAgent] Script Date: 01/22/2010 19:48:25 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [dbo].[vw_JumlahExportByAgent] as select top(7) NamaAgent, count(JumlahExport)JumlahExport from FaktaExport FE INNER JOIN dbo.DimAgent DB ON FE.AgentID = DB.AgentID group by NamaAgent
o. View Jumlah Export by Branch USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_JumlahExportByBranch] 01/22/2010 19:48:44 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [dbo].[vw_JumlahExportByBranch] as select NamaBranch, count(JumlahExport)JumlahExport from FaktaExport FE INNER JOIN dbo.DimBranch DB ON FE.BranchID = DB.BranchID group by NamaBranch
Script Date:
p. View Jumlah Export by Shipper USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_JumlahExportByShipper] 01/22/2010 19:49:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[vw_JumlahExportByShipper] AS SELECT
DB.NamaShipper, count(FE.JumlahExport) AS JumlahExport
L18
Script Date:
FROM
dbo.FaktaExport AS FE INNER JOIN dbo.DimShipper AS DB ON FE.ShipperID = DB.ShipperID WHERE NamaShipper = 'BESTARI INDOPRIMA PT.' OR NamaShipper = 'MULTI KERAMIC PT.' OR NamaShipper = 'GARUDA INDONESIA PT.' OR NamaShipper = 'WINDU JAYA UTAMA PT.' OR NamaShipper = 'TEXMACO JAYA PT.' OR NamaShipper = 'BHINEKA KARYA MANUNGGAL PT.' OR NamaShipper = 'JAYA FIBERINDO PT.' GROUP BY DB.NamaShipper
q. View Jumlah Import by Branch USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_JumlahImportByBranch] 01/22/2010 19:49:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create view [dbo].[vw_JumlahImportByBranch] as select NamaBranch, count(JumlahImport)JumlahImport from FaktaImport FE INNER JOIN dbo.DimBranch DB ON FE.BranchID = DB.BranchID group by NamaBranch
Script Date:
r. View Jumlah Jenis Commodity Export USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_JumlahJenisCommodityExport] Date: 01/22/2010 19:50:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE view [dbo].[vw_JumlahJenisCommodityExport] As SELECT NamaBranch, [Month], round(SUM(JumlahJenisCommodityExport)/1000,0) JumlahJenisCommodityExport FROM FaktaExport FE INNER JOIN DimTime DT ON FE.TimeID = DT.TimeID INNER JOIN dbo.DimBranch DB ON FE.BranchID = DB.BranchID GROUP BY NamaBranch,[Month],month(CalendarDate) --ORDER BY NamaBranch,month(CalendarDate)
L19
Script
s. View Jumlah Jenis Commodity Import USE [TRESNA_DWH] GO /****** Object: View [dbo].[vw_JumlahJenisCommodityImport] Date: 01/22/2010 19:50:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
Script
CREATE view [dbo].[vw_JumlahJenisCommodityImport] As SELECT NamaBranch, [Month], SUM(JumlahJenisCommodityImport) JumlahJenisCommodityImport FROM FaktaImport FE INNER JOIN DimTime DT ON FE.TimeID = DT.TimeID INNER JOIN dbo.DimBranch DB ON FE.BranchID = DB.BranchID GROUP BY NamaBranch,[Month],month(CalendarDate) --ORDER BY NamaBranch,month(CalendarDate)
t. Insert ke tabel Fakta Export INSERT INTO [TRESNA_DWH].[dbo].[FaktaExport] ([TimeID] ,[BranchID] ,[ShipperID] ,[ContainerID] ,[AgentID] ,[CommodityID] ,[JumlahShipper] ,[JumlahJenisCommodityExport] ,[JumlahContainer] ,[JumlahExport] ) SELECT DT.[TimeID] ,DB.[BranchID] ,DS.[ShipperID] ,DC.[ContainerID] ,DA.[AgentID] ,DCO.[CommodityID] ,VE.[Jumlahshipper] ,VE.[JumlahJenisCommodity] ,VE.[JumlahContainer] ,VE.[JumlahExport] FROM TRESNA_OLTP.dbo.vw_Export VE INNER JOIN dbo.DimTime DT ON VE.DateOfExport = DT.CalendarDate INNER JOIN dbo.DimBranch DB ON VE.KdBranch =DB.KdBranch INNER JOIN dbo.DimShipper DS ON VE.KdShipper = DS.KdShipper INNER JOIN dbo.DimContainer DC ON VE.KdContainer =DC.KdContainer INNER JOIN dbo.DimAgent DA ON VE.KdAgent = DA.KdAgent INNER JOIN dbo.DimCommodity DCO ON VE.KdCommodity = DCO.KdCommodity
L20
u. Insert ke tabel Fakta Import INSERT INTO [TRESNA_DWH].[dbo].[FaktaImport] ([TimeID] ,[BranchID] ,[ConsigneeID] ,[ContainerID] ,[CommodityID] ,[JumlahConsignee] ,[JumlahJenisCommodityImport] ,[JumlahContainerImport] ,[JumlahImport] ) SELECT DT.[TimeID] ,DB.[BranchID] ,DS.[ConsigneeID] ,DC.[ContainerID] ,DCO.[CommodityID] ,VI.[JumlahConsignee] ,VI.[JumlahJenisCommodityImport] ,VI.[JumlahContainerImport] ,VI.[JumlahImport] FROM LEFT LEFT LEFT LEFT LEFT
TRESNA_OLTP.dbo.vw_Import VI JOIN dbo.DimTime DT ON VI.DateOfImport = DT.CalendarDate JOIN dbo.DimBranch DB ON VI.KdBranch =DB.KdBranch JOIN dbo.DimConsignee DS ON VI.KdCOnsignee = DS.KdConsignee JOIN dbo.DimContainer DC ON VI.KdContainer =DC.KdContainer JOIN dbo.DimCommodity DCO ON VI.KdCommodity = DCO.KdCommodity
L21
IV. Lampiran Aplikasi 1. Form Login Form login ini adalah form yang pertama kali ditampilkan saat aplikasi dijalankan. Form login ini digunakan untuk masuk ke menu utama. Aplikasi ini hanya bisa diakses oleh mereka yang mempunyai hak akses. User yang mempunyai hak akses akan mengisi username dan password yang dimilikinya untuk dapat menggunakan aplikasi ini. Lalu setelah mengisi username = admin dan password = admin, user menekan tombol ok untuk masuk kedalam aplikasi atau cancel untuk membatalkan login dan keluar dari program.
Gambar L.1 Form Login
L22
2. Form Warning dan Message Form Warning ini akan muncul apabila username dan password yang dimasukkan tidak sesuai dengan data yang ada dan pada form message ini akan muncul apabila username dan password tidak dimasukkan.
Gambar L.2 Form Warning dan Message
3. Form M enu Utama Form ini menampilkan menu-menu yang ada didalam aplikasi data warehouse yang terdiri dari menu file, report, chart, schema, admin, dan about. M enu file, report, chart, schema, dan admin masing-masing memiliki submenu.
L23
Gambar L.3 Form M enu Utama
4. Form Add User Form add user digunakan untuk menambah hak akses user baru yang akan mempunyai hak untuk mengakses aplikasi data warehouse ini. User baru dapat mengisi username, password dan confirm password setelah selesai mengisi ketiganya maka dapat menekan tombol add untuk menyimpan data user baru atau cancel untuk membatalkan penambahan user baru.
L24
Gambar L.4 Form Add User
5. Form Change Password Form change password ini digunakan untuk mengganti password yang ada atau lama dengan password yang baru. User dapat mengisi Old Password, New Password dan Confirm Password. Setelah mengisi semuanya maka tekan tombol change untuk merubah password yang lama dan menggantinya dengan password yang baru.
Gambar L.5 Form Change Password
L25
6. Form shipper yang sering melakukan export Form ini adalah form yang menampilkan data yang berasal dari fakta export dalam data warehouse yaitu shipper yang sering melakukan export yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.6 Shipper yang sering melakukan export
L26
7. Form commodity yang sering di export Form ini adalah form yang menampilkan data yang berasal dari fakta export dalam data warehouse yaitu commodity yang sering di export yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.7 Commodity yang sering di export
L27
8. Form container yang sering digunakan pada bagian export Form ini adalah form yang menampilkan data yang berasal dari fakta export dalam data warehouse yaitu container yang sering digunakan yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.8 Container yang sering digunakan
L28
9. Form branch yang paling banyak melakukan export Form ini adalah form yang menampilkan data yang berasal dari fakta export dalam data warehouse yaitu branch yang paling banyak melakukan export yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
. Gambar L.9 Branch yang paling banyak melakukan export
L29
10. Form agent yang paling banyak melakukan export Form ini adalah form yang menampilkan data yang berasal dari fakta export dalam data warehouse yaitu agent yang paling banyak melakukan export yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.10 Agent yang paling banyak melakukan export
L30
11. Form jumlah export Form ini adalah form yang menampilkan data yang berasal dari fakta export dalam data warehouse yaitu jumlah export yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.11 Jumlah export
L31
12. Form total export Form ini adalah form yang menampilkan data yang berasal dari fakta export dalam data warehouse yaitu total export yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.12 Total export
L32
13. Form consignee yang paling sering menerima import Form ini adalah form yang menampilkan data yang berasal dari fakta import dalam data warehouse yaitu consignee yang paling sering menerima import yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.13 Consignee yang paling sering menerima import L33
14. Form jenis commodity yang sering di import Form ini adalah form yang menampilkan data yang berasal dari fakta import dalam data warehouse yaitu jenis commodity yang sering di import yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.14 Jenis commodity yang sering di import
L34
15. Form branch yang paling banyak menerima import Form ini adalah form yang menampilkan data yang berasal dari fakta import dalam data warehouse yaitu branch yang menerima import yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.15 Branch yang paling banyak menerima import
L35
16. Form container yang sering digunakan Form ini adalah form yang menampilkan data yang berasal dari fakta import dalam data warehouse yaitu container yang sering di gunakan bagian import yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.16 Container yang sering digunakan L36
17. Form jumlah import Form ini adalah form yang menampilkan data yang berasal dari fakta import dalam data warehouse yaitu jumlah import yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.17 Jumlah import
L37
18. Form total import Form ini adalah form yang menampilkan data yang berasal dari fakta import dalam data warehouse yaitu total import yang ditampilkan berdasarkan dimensi yang ada pada pivot table.
Gambar L.18 Total import
L38
19. Form Transform
Gambar L19 Form Transform 20. Form About Form about ini menampilkan logo perusahaan dan nama – nama pembuat aplikasi. Untuk keluar dari menu ini tekan tombol ok dan akan kembali ke menu utama.
Gambar L.20 Form About L39
