Dimension Table creation 1. Basic dimension table settings.

The manually created dimension is as follows.

Dimdevice.

 
Use[J_itvdw]
 

 
IfObject_id ('Dimdevice')Is Not Null
 

 
Drop TableDimdevice
 

 
Go
 

 
Create Table[DBO]. [dimdevice] (
 

 
[DeviceID] [Int]Not Null Primary Key Identity(1, 1 ),
 

 
[Devicename] [nvarchar] (50)Null,
 

[Platformid] [Int]Null,
 

 
[Platformname] [nvarchar] (50)Null,
 

 
[Orderkey]Int Default(0 ),
 

 
[Createtime] [datetime]Null Default(Getdate ())
 

 
)
 

 
Set Identity_insert[Dimdevice]On
 

 
InsertIntoDimdevice ([DeviceID], [devicename], [platformid], [platformname], orderkey)
 

 
Values(-1,'Unknow',-1,'Unknow', 999)
 

Set Identity_insert[Dimdevice]Off
 

 
DBCCCheckident ([dimdevice], reseed, 0)
 

 
InsertIntoDimdevice ([devicename], [platformid], [platformname], orderkey)
 

 
Values
 

 
('TV', 1,'Stbclient', 0 ),
 

 
('Ipad', 2,'Leleclient', 0 ),
 

 
('Pc', 3,'Webclient', 0 ),
 

 
('Iphone', 2,'Leleclient', 0 ),
 

('Android pad', 2,'Leleclient', 0)
 

 
Select*FromDimdevice
 

 

Dimage.

Use[J_itvdw]
 

 
IfObject_id ('Dimage')Is Not Null
 

 
Drop TableDimage
 

 
Go
 

 
Create Table[DBO]. dimage (
 

 
[Ageid] [Int]Not Null Primary Key Identity(1, 1 ),
 

 
[Agename] [nvarchar] (50)Null,
 

Ageattr1 [Int]Null,
 

 
Ageattr2Int Null,
 

 
[Orderkey]Int Default(0 ),
 

 
[Createtime] [datetime]Null Default(Getdate ())
 

 
)
 

 
Set Identity_insertDimageOn
 

 
InsertIntoDimage ([ageid], [agename], ageattr1, ageattr2, orderkey)Values(-1,'Unknow',-1,-1,999)
 

Set Identity_insertDimageOff
 

 
DBCCCheckident (dimage, reseed, 0)
 

 
InsertIntoDimage ([agename], ageattr1, ageattr2, orderkey)
 

 
Values
 

 
('Children', 1, 6, 0 ),
 

 
('Juvenile', 7, 17, 0 ),
 

 
('Youth', 18, 40, 0 ),
 

 
('Middle-aged', 41,65, 0 ),
 

 
('Elder', 66,120, 0)
 

 
Select*FromDimage
 

 

Dimuser

 
IfObject_id ('Dimuser','U')Is Null
 

Create TableDimuser (
 

UseridInt Primary Key Identity(1, 1 ),
 

UsersourceidInt,
 

 
Username nvarchar (50 ),
 

StatusInt,
 

 
AgeidInt,
 

Agename nvarchar (10 ),
 

 
SamuserstatusInt,
 

Samuserstatusname nvarchar (10 ),
 

 
OpenaccountfromidInt,
 

Openaccountfromname nvarchar (10 ),
 

 
AgentidInt,
 

Agentname nvarchar (50 ),
 

IscurrentInt,
 

 
Creationtime datetime,
 

Createtime datetimeDefault(Getdate ())
 

 
)
 

/*
 

 
Four Dimensions.
 

Samuserstatus: User Account Status.
 

 
Openaccountfromid account Source
 

Agent of agentid
 

 
Ageid age range
 

 
*/