日期:2014-05-18 浏览次数:20708 次
--建立测试环境
create database TestSSAS
go
use TestSSAS
go
--事实表
create table Logs(sId varchar(20),sWebsiteId varchar(20),sTime datetime,sLeaveTime datetime,sIp varchar(20),sCount int)
insert into Logs select '1','542','2008-11-18 09:18:35.000','2008-11-18 14:51:29.000','61.183.248.218','87'
insert into Logs select '2','542','2008-11-18 09:38:36.000','2008-11-18 17:04:23.000','61.144.207.115','128'
insert into Logs select '3','543','2008-11-18 09:42:35.000','2008-11-18 10:36:46.000','61.183.248.218','5'
insert into Logs select '4','552','2008-11-18 16:45:19.000','2008-11-18 16:45:21.000','61.144.207.115','4'
insert into Logs select '5','551','2008-11-18 16:45:54.000','2008-11-18 16:45:55.000','61.144.207.115','5'
insert into Logs select '7','549','2008-11-18 16:46:58.000','2008-11-18 16:46:59.000','61.144.207.115','3'
insert into Logs select '8','548','2008-11-18 16:47:15.000','2008-11-18 16:47:16.000','61.144.207.115','4'
insert into Logs select '5','551','2008-11-19 16:45:54.000','2008-11-19 16:45:55.000','61.144.207.115','15'
insert into Logs select '7','549','2008-11-19 16:46:58.000','2008-11-19 16:46:59.000','61.144.207.115','13'
insert into Logs select '8','548','2008-11-19 16:47:15.000','2008-11-19 16:47:16.000','61.144.207.115','14'
go
--事实表对应视图
create view v_Fac_logs as
select sid,swebsiteid,convert(varchar(10),stime,120) as date,sIP,sCount from Logs
go
--维度表
create table dim_datetime (date varchar(10))
insert dim_datetime
select '2008-11-15' union
select'2008-11-16' union
select'2008-11-17' union
select'2008-11-18' union
select'2008-11-19'
go
--事实表抽取的维度,这里用视图实现
create view dim_Ip as
select distinct sip from Logs