维度表的建立1. 基础维度表的设定.

我们需要手动建立的维度如下.

DimDevice.

USE [J_ITVDW]

if object_id('dimDevice') is not null
drop table dimDevice
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
insert into DimDevice([DeviceID],[DeviceName],[PlatFormID],[PlatFormName], OrderKey)
values(-1,'Unknow',-1,'Unknow',999)
SET IDENTITY_INSERT [DimDevice] Off

DBCC CHECKIDENT ([DimDevice],reseed,0)

insert into DimDevice([DeviceName],[PlatFormID],[PlatFormName], OrderKey)
values
('TV',1,'STBClient',0),
('iPad',2,'MobileClient',0),
('PC',3,'WebClient',0),
('iPhone',2,'MobileClient',0),
('Android Pad',2,'MobileClient',0)

select * from DimDevice

 

DimAge.

USE [J_ITVDW]

if object_id('dimAge') is not null
drop table dimAge
go
CREATE TABLE [dbo].dimAge(
    [AgeID] [int] NOT NULL primary key identity(1,1),
    [AgeName] [nvarchar](50) NULL,
    AgeAttr1 [int] NULL,
    AgeAttr2 int NULL,
    [OrderKey] int default(0),
    [CreateTime] [datetime] NULL default(getdate())
    )

SET IDENTITY_INSERT dimAge ON
insert into dimAge([AgeID],[AgeName],AgeAttr1,AgeAttr2, OrderKey) values(-1,'Unknow',-1,-1,999)
SET IDENTITY_INSERT dimAge Off

DBCC CHECKIDENT (dimAge,reseed,0)

insert into dimAge([AgeName],AgeAttr1,AgeAttr2, OrderKey)
values
('儿童',1,6,0),
('少年',7,17,0),
('青年',18,40,0),
('中年',41,65,0),
('老年',66,120,0)

select * from dimAge

 

DimUser

if object_id('DimUser','U') is null
create table DimUser(
    UserID int primary key identity(1,1),
    UserSourceID int,
    UserName nvarchar(50),
    Status int,
    AgeID int,
    AgeName nvarchar(10),
    SamUserStatus int,
    SamUserStatusName nvarchar(10),
    OpenAccountFromID int,
    OpenAccountFromName    nvarchar(10),
    AgentID int,
    AgentName nvarchar(50),

    IsCurrent int,
    CreationTime datetime,
    CreateTime datetime default(getdate())
)
/*
四个维度.
SamUserStatus  用户账户状态.
OpenAccountFromID 开户来源
AgentID 所属代理商
AgeID 年龄范围

*/