从数据到信息
从信息到洞察

Excel 一亿行大数据分析实践(总结篇)

 

背景

本文是我在电子表格大会分享内容的文字总结,如果你是第一次阅读,可以通过下文了解一下背景知识。
Excel 你能分析这么多数据吗?(活动预告)

测试目的本次测试目的并非与其他数据分析方法对比优劣、而是尝试介绍一种完全基于EXCEL的本地化大数据集处理方式。

 

分析师处理大数据集时常用方式
Excel 一亿行数据分析实践(总结篇)

 

本次演示的方式
Excel 一亿行数据分析实践(总结篇)

 

这种方式的优点

  • 低成本。减少工具间的切换成本,直接使用Excel作为存储和分析工具。
  • 展现灵活。展现端继续使用Excel,发挥它灵活、自定义程度高的优势。
  • 便于交付。其他方式得到的结果为了便于交付,还要导出为Excel,而现在整个分析流都在Excel内部完成。
  • 结果可交互。PowerPivot相当于一个存储了源数据的OLAP引擎,通过控制切片器等外部筛选条件,可以迅速、动态的查看结果,使用其他方法,可能需要返回分析端改变计算条件重新导出。

测试项目一:数据导入和耗时

向Excel导入大数据,有两种方式:

  1. PowerPivot导入,直接导入,不支持数据转换和清洗操作。
  2. PowerQuery导入,在导入前可以对数据做预处理。

本次使用的测试数据集共有19列,有多列需要进行格式转换和日期提取操作,使用第一种方式,需要导入后在PowerPivot内部进行,使用方式二可以在载入前完成,很明显的是,对于方式二,预处理步骤越多,加载时间会越长。下图展示了不同量级不同导入方式的耗时情况(单位:秒)
Excel 一亿行数据分析实践(总结篇)

 

为了直接对比PowerQuery和PowerPivot的加载效率,增加了一个*号方式,这种方式不对数据做任何清洗转换,直接加载到模型,与PowerPivot步骤相同。

 

通过导入过程观察到的现象

对比前两行结果,PowerQuery的数据导入效率与PowerPivot不分伯仲。

PowerQuery没有数据量的限制,而PowerPivot不到导入超过2G的文件。

清洗步骤和数据量的增多,都会显著增加PowerQuery的导入时间,比如一亿行数据,即使三个简单的清洗步骤,用时已经超过了30分钟

 

结论

PowerPivot导入方式使用的是Access连接器,受限于Access文件本身的限制,不能导入超过2G的数据,这也说明,PowerPivot数据存储能力超过了Access。

PowerQuery是轻型ETL工具,处理大数据集性能不强。

如果尝试使用Buffer函数缓存数据,会发现这个缓存过程非常漫长,实际上,Buffer函数并不适合缓存大数据集,因为无法压缩数据,内存可能会很快爆掉。

 

测试项目二:文件压缩比率

 

Excel 一亿行数据分析实践(总结篇)

影响文件压缩比率的因素,主要是数据集本身的特征和PowerPivot引擎的性能

结论

数量级越大,压缩比率越高。

同一数据量级,清洗步骤越多,最终文件会越大,并且随着数据量的增加,这种现象会越明显。

 

测试项目三:数据分析的效率 – 简单分析

我们真正关心的内容是,Excel能否快速、高效的对大数据集开展分析。

简单分析定义的场景:逐月统计有多少位顾客发生了购买。做法是把年和月拖入透视表行字段,将CustomerKey拖入值区域,修改值汇总方式为统计不重复值。

测试发现,即便使用一亿行数据,这个计算过程的用时也很短,小于1s。于是我增加了一点难度,加入两个切片器对结果做交叉筛选,计算用时仍然小于1s,看来PowerPivot处理这类分析比较轻松,最终此项测试没有计时。

Excel 一亿行数据分析实践(总结篇)
测试项目四:复杂分析效率 – 新客户统计

 

Excel 一亿行数据分析实践(总结篇)统计新客户数量,逻辑是:逐月计算当月产生购买的顾客中,有多少是新客户(第一笔购买发生在当月)。为了获取PowerPivot引擎的计算用时,测试在DAX Studio内完成,同时为了模拟透视表的计算结果,需要对原度量值的写法做一点改动。

EVALUATE
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( '1亿'[Order`Date (年)] ),
        VALUES ( '1亿'[Order`Date (月索引)] )
    ),
    "newcustomers",
    VAR currentcustomer =
        CALCULATETABLE ( VALUES ( '1亿'[CustomerKey] ) )
    VAR oldcustomer =
        FILTER (
            currentcustomer,
            CALCULATE (
                MIN ( '1亿'[Order`Date] ),
                ALLEXCEPT ( '1亿', '1亿'[CustomerKey] )
            )
                < CALCULATE ( MIN ( '1亿'[Order`Date] ) )
        )
    RETURN
        COUNTROWS ( EXCEPT ( currentcustomer, oldcustomer ) )
)
ORDER BY
    '1亿'[Order`Date (年)],
    '1亿'[Order`Date (月索引)]

Excel 一亿行数据分析实践(总结篇)
计算用时(毫秒)
Excel 一亿行数据分析实践(总结篇)

二次运算的用时指的是首次运算结束后,不清空缓存再次执行重复计算所花费的时间。相比第一次运算,节约时间在30%左右。原因是DAX的两个引擎中,有一个可以缓存计算结果,被缓存的内容可以在之后被公式内部调用,也可以跨公式调用。

结合这个知识,对DAX的表达式进行优化,可以获得更好的性能表现,下面是新客统计优化之后的写法,我们来对比计值时间的变化。

EVALUATE
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( '1亿'[Order`Date (年)] ),
        VALUES ( '1亿'[Order`Date (月索引)] )
    ),
    "newcustomers",
    VAR currentcustomer =
        CALCULATETABLE ( VALUES ( '1亿'[CustomerKey] ) )
    VAR oldcustomer =
        FILTER (
            currentcustomer,
            CALCULATE (
                MIN ( '1亿'[Order`Date] ),
                ALLEXCEPT ( '1亿', '1亿'[CustomerKey] )
            )
                < CALCULATE ( MIN ( '1亿'[Order`Date] ) )
        )
    RETURN
        COUNTROWS ( EXCEPT ( currentcustomer, oldcustomer ) )
)
ORDER BY
    '1亿'[Order`Date (年)],
    '1亿'[Order`Date (月索引)]

Excel 一亿行数据分析实践(总结篇)
优化后计算用时(毫秒)

Excel 一亿行数据分析实践(总结篇)
可以看出引擎的缓存起到了显著效果,二次计算直接调用首次运算的结果,计算时间不随数据量的增加而增加。

以一亿行数据集的结果为例,对比算法优化前后的用时:
Excel 一亿行数据分析实践(总结篇)

 

测试项目四:复杂分析效率 – 流失客户统计

与新客的呈现方式相同,依然是逐月计算当月的流失客户,不同的是流失客户的定义更为复杂。流失天数允许用户自定义,被判定流失的客户需同时满足以下两个条件:

  1. 所有在当月之前最后一次购买的日期+自定义流失天数,落在当前时间区间内。
  2. 当月如果发生购买,第一次购买日期不能早于判定流失的日期。

流失客户公式和计算结果
Excel 一亿行数据分析实践(总结篇)计值流如此复杂的一个公式,PowerPivot会耗时多久呢,我只用了一亿行数据的文件做测试,

结果是首次计算4093ms,二次计算1720ms。

 

说明

以上测试模拟了透视表的呈现布局,而且你可以加入切片器改变公式的上下文条件,迅速得出特定产品、特定商户和特定促销活动的新客户以及流失客户,非常方便。时间统计基于少量的测试结果,存在一定偶然性,仅供参考。

测试环境电脑配置也是影响计算性能的重要因素,以上进行的所有测试都基于台式机,在做现场分享的时候,我在笔记本电脑上重新运行了一遍流失客户公式,两个环境的用时如下:
Excel 一亿行数据分析实践(总结篇)结合平时其他测试,我的笔记本执行同样的计算,用时平均在台式机的两倍左右。两台电脑的配置如下
Excel 一亿行数据分析实践(总结篇)

提升CPU主频、核心数、1、2、3级缓存;内存的大小和频率都会提升引擎的性能表现。

 

总结

对于本地化大数据集的分析,本文提供了一种新的可能,严格来讲,2010年的时候你已经可以使用,只不过彼时它羽翼未丰,计算性能和稳定性难堪大任。

而现在,你已经见识到了这套工具(PowerPivot+PowerQuery)的能力,无论大数据还是复杂运算,Excel公式和VBA已经无法望其项背。

一般说来,积累通常是好事,财富可以通过积累不断增加、写作能力可以通过积累不断增强,但在某些知识领域,迭代是如此的快速和彻底,以至于底层的方法论都将被淘汰掉,过去的知识成为此刻的负担,你最好尽快丢掉过去,拥抱未来。

原创内容 转载请联系作者授权:PowerBI极客 » Excel 一亿行大数据分析实践(总结篇)

说点什么

avatar
  订阅本文评论  
提醒