微软BI开拓者

处理周期：1个小时
每次数据：10万

每个小时的第十分钟读取前一个小时数据。利用游标逐行数据处理，然后写入事实表。

我试过用SSIS处理数据，但是没弄明白，有些我需要的处理不能实现，我用脚本组件自己写的程序处理，发现太慢，不如游标快。

我不知道有什么好办法，老是觉得我的方法不对！

我没学过数据仓库，现在还一知半解。请大家指点一二。

在取原始记录时，有没有时间标志，如果有，可以用between 批量处理吧！

把每小时的记录取到一个临时表中，然后再做ETL

我的处理方法是不是很低能？

和用游标有区别吗？  逐行处理不还是那些行吗

从一个大的表中与从一个小的表中不一样的，还有，你做ETL的条件有什么，我认为一般情况可以不用游标，有些地方CASE就可以搞定的！

把用游标的代码发上来看下，专门这样谈，谈不到问题的点上的！

什么叫ETL的条件:default1:
我要处理出来的有：地区，叶面类型，来源站点，网站用户公司编号，网站目录，所属行业，当前页数，搜索引擎，小时等等

DECLARE    @key_id bigint
DECLARE @site_id bigint            --站点编号
DECLARE    @user_id bigint --用户编号
DECLARE    @date_id  int--日期编号
DECLARE    @reftype_id int--来源类型
DECLARE    @refsite_id bigint
DECLARE    @hour_id int
DECLARE    @p_id bigint
DECLARE @pt_id int
DECLARE @ipno bigint
DECLARE @ad_id int
DECLARE @add_time datetime
DECLARE @from_id int
DECLARE @keyword_id bigint
DECLARE @fromSite bigint
DECLARE @join_url bigint
DECLARE @visit_no int
DECLARE @pages_no int
DECLARE @is_old int
DECLARE @is_cookie int
DECLARE @settle_no int
DECLARE @suserId int
DECLARE @isMoni int
DECLARE @address_id int
DECLARE @fatherId int

DECLARE @ly_guest int
DECLARE @ly_items int
DECLARE @ly_ad int
DECLARE @ly_type int
DECLARE @ly_appid int

DECLARE @ref_site varchar(50)
DECLARE @ref_name varchar(50)
DECLARE @ref_keyword varchar(200)
DECLARE @url_join varchar(500)

DECLARE    @is_true  bigint

DECLARE @vyear varchar(50)
DECLARE @vmonth varchar(50)
DECLARE @vday varchar(50)
DECLARE @vhour varchar(50)
DECLARE @hour varchar(50)

DECLARE @true_site int   
DECLARE @addsn int   


DECLARE @talkid bigint
DECLARE @Userid bigint

DECLARE @etl_date varchar(50)
DECLARE @etl_state int

DECLARE @will_etldate varchar(50)

    Set  @vyear = LTRIM(str(Year(getdate())))
    Set  @vmonth = LTRIM(str(Month(getdate())))
    Set  @vday = LTRIM(str(Day(getdate())))
    Set  @vhour = LTRIM(str(DATEPART (hour, getdate())))
    set @hour=@vyear+'-'+@vmonth+'-'+@vday+' '+@vhour+':00:00'

SELECT top 1 @etl_date=[etl_date],@etl_state=[etl_state]
FROM [Analytics_DB].[dbo].[ETL_Note]
where etl_type=8
order by etl_date desc

set @will_etldate =DATEADD(hour, 1, @etl_date)

if @etl_state=2 and DATEDIFF(hour, @will_etldate,@hour)>0 --上一次执行完毕 并且不是当前小时
begin
INSERT INTO [Analytics_DB].[dbo].[ETL_Note]
          ([etl_date],[etl_year],[etl_month],[etl_day],[etl_hour],[etl_state],etl_type)
    VALUES
          (DATEADD(hour, 1, @etl_date),LTRIM(str(Year(@will_etldate))),LTRIM(str(Month(@will_etldate))),LTRIM(str(Day(@will_etldate))),LTRIM(str(DATEPART (hour, @will_etldate))),1,8)
--定义游标
DECLARE Employee_OpenPV CURSOR FOR
SELECT [key_id],[site_id],[user_id],[ipno],[pt_id],[visit_no],[pages_no],[ref_type],[ref_site],
[ref_name],[ref_keyword],[url_join],[ad_id],[suser_id],[is_moni],[start_time],address_id,ly_guest,ly_items,ly_ad,ly_type,ly_appid,father_id
FROM [Analytics_DB].[dbo].[OpenPV] with (nolock)
where DATEDIFF(day, start_time,@will_etldate)=0 and DATEPART(hour, start_time)=DATEPART (hour, @will_etldate) and is_moni=0 and pt_id<>2
OPEN Employee_OpenPV

FETCH NEXT FROM Employee_OpenPV
INTO @key_id,@site_id,@user_id,@ipno,@pt_id,@visit_no,@pages_no,@reftype_id,@ref_site,@ref_name,@ref_keyword,@url_join,
@ad_id,@suserId,@isMoni,@add_time,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId
WHILE @@FETCH_STATUS = 0
BEGIN
--检查站点编号
SELECT @true_site=count(*) FROM [Analytics_DW].[dbo].[Stat_Site] where site_id=@site_id
If @true_site=0
Begin
INSERT INTO [Analytics_DW].[dbo].[Error_PV]
          ([key_id],[site_id],[user_id],[ipno],[pt_id],[visit_no],[pages_no],[ref_type],[ref_site],[ref_name]
          ,[ref_keyword],[url_join],[ad_id],[suser_id],[is_moni],[add_time],[address_id],[ly_guest],[ly_items],[ly_ad]
          ,[ly_type],[ly_appid])
    VALUES(@key_id,@site_id,@user_id,@ipno,@pt_id,@visit_no,@pages_no,@reftype_id,@ref_site,@ref_name,@ref_keyword,@url_join,
@ad_id,@suserId,@isMoni,@add_time,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid)
End
Else
Begin
    Set  @vyear = LTRIM(str(Year(@add_time)))
    Set  @vmonth = LTRIM(str(Month(@add_time)))
    Set  @vday = LTRIM(str(Day(@add_time)))
    Set  @vhour = LTRIM(str(DATEPART (hour, @add_time)))
    set @hour=@vyear+'-'+@vmonth+'-'+@vday+' '+@vhour+':00:00'
    --处理小时编号
    set @hour_id=@vhour
    if @fatherId is null
    set @fatherId=8

    --处理日期编号
    SELECT top 1 @date_id=[date_id] FROM [Analytics_DW].[dbo].[Stat_Date] where d_year=@vyear and d_month=@vmonth and d_day=@vday
        if @date_id is null
            set @date_id=1
    if @isMoni=1
    begin
    set @p_id=1
    set @refsite_id=1
    set @from_id=1
    set @keyword_id=1
        INSERT INTO [All_OpenPV]
                       (key_id,[talk_id],[site_id],[user_id],[ip_no],[date_id],[hour_id],[reftype_id],
                        [refsite_id],from_id,keyword_id,[p_id],[pt_id],[ad_id],[suser_id],[is_moni],add_time,address_id,ly_guest,ly_items,ly_ad,ly_type,ly_appid,father_id)
                 VALUES
                       (@key_id,0,@site_id,@user_id,@ipno,@date_id,@hour_id,@reftype_id,@refsite_id,@from_id,@keyword_id,@p_id
                       ,@pt_id,@ad_id,@suserId,@isMoni,@hour,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId)
    end
    else
    begin
        --处理页面编号
        set @p_id=1
        SELECT top 1 @p_id=[p_id] FROM [Analytics_DW].[dbo].[Stat_LyPage] where p_name=@url_join  and site_id=@site_id
            if @p_id is null
                set @p_id=1
         --处理来路站点编号
        set @refsite_id=1
        SELECT top 1 @refsite_id=[refsite_id] FROM [Analytics_DW].[dbo].[RefSite] where refsite_name=@ref_site  and site_id=@site_id
        if @refsite_id is null
                set @refsite_id=1

        --处理搜索引擎
        if @ref_keyword is null or @ref_keyword=''
        begin
            set @from_id=1
            set @keyword_id=1
        end
        else
        begin
        set @from_id=1   
        SELECT top 1 @from_id=[from_id] FROM [Analytics_DW].[dbo].[Stat_FromWeb] where from_name=@ref_name
        if @from_id is null
                set @from_id=1   
        end
        set @keyword_id=1
        --处理来自广告转换 搜索引擎 关键词转换
        if @pt_id=1 or @pt_id=2
        begin
            if @keyword_id=1 and @ad_id=1
                begin
                    select top 1 @ad_id=ad_id,@fatherId=father_id from [Analytics_DB].[dbo].[OpenPV] with(nolock)
                        where is_moni=1 and ipno=@ipno and add_time > DATEADD(day, -1, @add_time) order by key_id desc
                    if @ad_id<>1
                    begin
                        set @from_id=1
                        set @keyword_id=1
                    end
                    else
                    begin
                    set @ref_name=null
                    select top 1  @ref_name=ref_name from [Analytics_DB].[dbo].[OpenPV] with(nolock)
                        where is_moni=0 and ipno=@ipno and add_time > DATEADD(day, -1, @add_time) order by key_id desc
                    set @from_id=1   
                    SELECT top 1 @from_id=[from_id] FROM [Analytics_DW].[dbo].[Stat_FromWeb] where from_name=@ref_name
                    if @from_id is null
                        set @from_id=1
                    end
                end
        end

        --处理留言站点编号
        set @is_true=null
        if @pt_id=2 and @ref_site<>'9.txooo.com'
        begin
         SELECT top 1 @is_true=site_id FROM [Analytics_DW].[dbo].[Stat_Site] with(nolock) where siteurl=@ref_site
            if @is_true is not null
                set @site_id=@is_true
        end

    --写入PV
    INSERT INTO [All_OpenPV]
                   (key_id,[talk_id],[site_id],[user_id],[ip_no],[date_id],[hour_id],[reftype_id],
                    [refsite_id],from_id,keyword_id,[p_id],[pt_id],[ad_id],[suser_id],[is_moni],add_time,address_id,ly_guest,ly_items,ly_ad,ly_type,ly_appid,father_id)
             VALUES
                   (@key_id,0,@site_id,@user_id,@ipno,@date_id,@hour_id,@reftype_id,@refsite_id,@from_id,@keyword_id,@p_id
                   ,@pt_id,@ad_id,@suserId,@isMoni,@hour,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId)

End
end

    --break
    --获取下一条
    FETCH NEXT FROM Employee_OpenPV
    INTO @key_id,@site_id,@user_id,@ipno,@pt_id,@visit_no,@pages_no,@reftype_id,@ref_site,@ref_name,@ref_keyword,@url_join,
@ad_id,@suserId,@isMoni,@add_time,@address_id,@ly_guest,@ly_items,@ly_ad,@ly_type,@ly_appid,@fatherId
END

--关闭游标
CLOSE Employee_OpenPV
DEALLOCATE Employee_OpenPV
--执行完毕
UPDATE [Analytics_DB].[dbo].[ETL_Note]
  SET [etl_state] = 2,[endtime] = getdate()
WHERE etl_date=@will_etldate and etl_type=8
end

可以用seelct filed into #tmptable from tABLE 的方式来把各种数据分成每个类，再对每一个类用SQL代码处理，我想会快些吧，我是外行人！
在有些地方处理可以用case 语句来分类！

今天公司网速真差劲，在你的帖子里转了好几圈就是打不开。郁闷！
闲话少说，关于ETL，就是抽取、转换、加载的过程。至于本质的东西，可以看咱版超级版主发的精华帖子。
不过，我对于你说的有些处理不能实现很好奇，能不能说一下是什么处理？

不是不能实现，应该是不好实现或我不会实现
比如：查询地区，一个地区有个ip段，某一行的数据大于最小ip小于最大ip确定地区。这个好像没有匹配的工具组件。

每一个页面地址我要分析出叶面类型，网站路径，是否分页，网站客户的编号，哪个行业，等等

我想知道你这个地区是个维度表吧？这个维度表的结构是什么样的？

举个例子吧：
比如你的表结构是这样的
主键  地区名  ipPart1  ipPart2  ipPart3
  1    地区一    192      168        2
  1    地区二    192      168        3

这样你可以把源数据派生几列对应这三列出来，就可以精确定位是哪个地区的了。

这个是我的想像的表结构。

地区维度表：id,title两个字段

要通过在ip库中查询出当前ip是国内哪个地区的地区编号

ip库标的字段有：
ip1 初始ip
ip2 结束ip
dqid 地区编号 与维度表id相对应
大于ip1小于ip2来确定dqid 写入事实表

ip1,ip2都是数值型  不是192.168.123.14

我打算再用手写sql代码了  不用SSIS了  不会用:default8:

你说的ip库是原始数据里的一个表还是又是一个维度表？

怎么遇到点困难就放弃了呢:default17:

我觉得是你的数据仓库设计的问题。个人看法。

ip库是原始数据

我也觉得设计的有问题  我没弄过数据仓库  边学边弄

现在才一亿数据  怕以后崩溃

我建了一个事实表  十多个维度表  这些表大部分都是周期更新的（一个小时）

事实表字段都是int  都是维度标的外键
维度表不超过5个字段

数据仓库是只进不出的，只要有足够的空间就可以了。我做的项目里一个月的数据就好几千万，不必担心。

  关于地区表的设计，我觉得你可以把ip和地区合成一张表。但是你的ip段的数据的具体情况我不清楚，不知道是不是像我刚才举得例子那样。

可以提考虑使用表分区

能写语句实现的话最好不要用工具。

10W数据 1h 太久了吧