Didalam laporan ini disusun sebuah ETL dengan membuat kembali factfinance yang ada,fact finance yang akan dibuat menggunakan konsep INCREMENT.tetapi sayangnya increment ini tidak bisa berjalan karena koneksi yang tidak bisa diputus sewaktu execute package, tetapi bisa berjalan sewaktu execute task.
Konsep yang digunakan untuk metode increment tersebut adalah:
CSV/QUERY - VIEW -Table-FactTable
Create Database
Create Database disini digunakan untuk membuat baru database yang nantinya akan digunakan untuk membuat view dan table.
Drop Database
Query yang digunakan adalah:
Drop Database tugas4
Create Database
Query yang digunakan adalah:
CREATE DATABASE tugas4
Set Database Option
Query yang digunakan adalah:
ALTER DATABASE tugas4 
SET RECOVERY SIMPLE,
ANSI_NULLS ON,
ANSI_PADDING ON,
ANSI_WARNINGS ON,
ARITHABORT ON,
CONCAT_NULL_YIELDS_NULL ON,
QUOTED_IDENTIFIER ON,
NUMERIC_ROUNDABORT OFF,
PAGE_VERIFY CHECKSUM,
ALLOW_SNAPSHOT_ISOLATION ON ;
CREATE VIEW
Viiew disini yang dimaksudkan untuk mengambil data secara update, karena sifat view ini adalah akan berubah isinya sesuai dengan query yang ada didalamnya.
View DimTIme
View DimTime disini isinya diambil dari CSV, dikarenakan tidak ada yang sesuai dengan query OLTP yang ada.
View DimOrganization
create view DimOrganization as
Select DISTINCT HD.DepartmentID as OrganizationKey,
HD.DepartmentID as ParentOrganizationKey,
AVG(P.BusinessEntityID)*100 as PercentOwnership,
HD.Name as DepartmentName,
SC.CurrencyCode as CurrencyID
from
AdventureWorks2008R2.HumanResources.Department as HD,
AdventureWorks2008R2.Person.Person as P,
AdventureWorks2008R2.Sales.Currency as SC
group by HD.DepartmentID,HD.Name,SC.CurrencyCode
View DimScenario
create view DimScenario as
Select DocumentNode as ScenarioKey,DocumentSummary as Scenario from AdventureWorks2008R2.Production.Document
View DimAccount
CREATE VIEW DimAccount AS
select BusinessEntityID as Accountkey,
(select BusinessEntityID as ParentAccountKey from AdventureWorks2008R2.Person.Person PP where PP.BusinessEntityID = P.BusinessEntityID )as ParentAccountKey,
(select PE.EmailAddress as ParentAccountAlternateKey from AdventureWorks2008R2.Person.EmailAddress as PE where P.BusinessEntityID = PE.BusinessEntityID)as ParentAccountAlternateKey,
(select P1.Firstname+' '+P1.lastName from AdventureWorks2008R2.Person.Person P1 where P1.BusinessEntityID = P.BusinessEntityID)as AccountDescription,
(select P2.PersonType from AdventureWorks2008R2.Person.Person P2 where P.BusinessEntityID = P2.BusinessEntityID)as AccountType,
(select P3.PhoneNumber from AdventureWorks2008R2.Person.PersonPhone P3 where P3.BusinessEntityID = P.BusinessEntityID) as Operator,
(select SC.CreditCardID from AdventureWorks2008R2.Sales.CreditCard SC,AdventureWorks2008R2.Sales.PersonCreditCard SPC where SPC.BusinessEntityID = P.BusinessEntityID and SPC.CreditCardID = SC.CreditCardID)as CustomMember,
(select SC.CardNumber from AdventureWorks2008R2.Sales.CreditCard SC,AdventureWorks2008R2.Sales.PersonCreditCard SPC where SPC.BusinessEntityID = P.BusinessEntityID and SPC.CreditCardID = SC.CreditCardID) as ValueType,
(select P4.EmailPromotion from AdventureWorks2008R2.Person.Person P4 where P.BusinessEntityID = P4.BusinessEntityID) as CustomMemberOption
from AdventureWorks2008R2.Person.Person P
View Dim DepartmentGroup
create view DimDepartmentGroup as
select
DepartmentID as DepartmentGroupKey,
DepartmentID as ParentDepartmentGroupKey,
Name as DepartmentName
from
AdventureWorks2008R2.HumanResources.Department
DATA FLOW TASK
Data flow task adalah sekumpulan alur per masing-masing control flow,secara aturan bahwa Data Flow termasuk didalam control Flow.
Berikut adalah beberapa Script Yang digunakan untuk membuat table dari semua Dimensi.
| Dim Scenario | CREATE TABLE [dbo].[DimScenario]( [ScenarioKey] [int] IDENTITY(1,1) NOT NULL, [ScenarioName] [nvarchar](50) NULL ) ON [PRIMARY]; |
| Dim Account | CREATE TABLE [dbo].[DimAccount]( [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](200) NULL, [ValueType] [nvarchar](50) NULL, [CustomMemberOptions] [nvarchar](200) NULL ) ON [PRIMARY]; |
| Dim Department Group | CREATE TABLE [dbo].[DimDepartmentGroup]( [DepartmentGroupKey] [int] IDENTITY(1,1) NOT NULL, [ParentDepartmentGroupKey] [int] NULL, [DepartmentGroupName] [nvarchar](50) NULL ) ON [PRIMARY]; |
| Dim Organization | CREATE TABLE [dbo].[DimOrganization] ( [OrganizationKey] [int] IDENTITY(1, 1) NOT NULL, [ParentOrganizationKey] [int] NULL, [PercentageOfOwnership] [nvarchar] (16) NULL, [OrganizationName] [nvarchar] (50) NULL, [CurrencyKey] [int] NULL ) ON [PRIMARY]; |