维度表的建立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 年龄范围

*/

转载于:https://www.cnblogs.com/jianjialin/archive/2012/06/10/2544441.html