SEnukeX SEO Software

Monday, June 13, 2011

ETL konsep Fact Finance- AdventureWorks DW 2008

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];

Blogged with the Flock Browser

No comments: