可视化分析在电力营销审计中的应用

1、    可视化分析在电力营销审计中的应用    厉雨 邵天龙 王浩摘要本文介绍了确定源端业务系统与全业务数据中心数据表对应关系的方式方法，进一步将全业务数据中心数据字典本地化，并讨论如何将查询数据表提取至电子表格，通过应用excel的ado和vba技术对数据进行清洗和转换，得到加工处理过的数据，进而通过bi软件tableau连接至该数据源并进行可视化分析与展现，使内部审计透过可视化发现疑点、分析原因、提出建议。关键词全业务    数据表    数据转换    可视化分析國网公司于2016年初启动

2、全业务统一数据中心建设，将源端业务系统的存量数据及增量数据高度融合，充分共享至贴源历史层（以下简称贴源层）。贴源层采用的数据库为oracle数据库，由此，审计人员想要获取数据除了通过源端业务系统进行前端查询之外，还可以通过访问贴源层来获取数据。只要拥有贴源层相应的访问权限，就可以直接访问贴源层并查询相关数据，相对于审计人员从单一源端系统查询数据更便捷也更强大。本文通过pl/sql软件访问贴源层oracle数据库，pl/sql是一款可以访问、操作、维护oracle数据库的软件，辅以sql语句即可访问贴源层各源端系统及其数据表，并可将查询结果导出为xls、csv等excel文件。在excel中通过

3、ado及vba技术进行多表、跨表数据提取汇总、清洗及数据分析等操作，将tableau链接处理以后的电子表格作为数据源进行数据可视化分析，进而从可视化展示中发现问题，解决问题。一、tableau简介相对于其他bi软件，tableau学习成本低而功能产出却更为强大。tablueu的使用分为三步：链接数据源、制作可视化图表、使用仪表板讲述可视化故事。连接好数据源以后，tableau会智能地将字段转换为度量或维度值，一般会将文本型和日期型字段转换为维度值，用于分类或阐明数据;而数值类型的字段一般会转换为度量值，可根据维度值进行一些聚合计算，如求和、计数、平均值等。有了这些度量及维度值，只需在table

4、au中通过一些基本的拖拽即可做出美观的可视化图表。tabluea会根据用户拖拽的字段智能生成系统最佳匹配的图表。二、结合源端系统在贴源层定位并查询数据（一）贴源层数据字典本地化为方便日后查询数据表以及表字段，首先，将贴源层数据字典本地化。通过sql语句进行数据表all_col_comments及all_tab_comments联合查询，即可查询出表名、表注释、字段名、字段注释等数据表信息。将查询所得数据导出到excel表格，考虑到excel储存能力及计算速度有限，采用将access作为本地数据字典的储存载体。access相比excel不仅数据更为严谨，而且储存量达到2g内存，查询速度也比exc

5、el快。虽然access更适合作为储存载体，但其操作界面相较于excel不够人性化，也不符合大多数审计人员的操作习惯，因此在excel里通过ui设计结合vba语言开发一套前端查询系统。数据字典本地化不占用服务器资源，数据库在本地相对查询速度比在贴源层直接查询要快。此外，这样一份数据字典为审计人员的日常工作带来一定的便利。（二）源端系统html网页结构分析通过源端系统可以查询数据，但是查询方式必须按照前端所设定的固定查询条件进行。如果可以直接在贴源层查询的话，就可以按照自己想要的条件编写sql语句进行查询，从而大大提高数据查询的灵活性。但灵活查询的前提是要梳理清楚源端系统前端页面与贴源层数据表之

6、间的对应关系。为理清源端数据表与贴源层数据表的对应关系，最初笔者采用的方法是字段注释模糊检索法，但此方法无异于大海捞针，不仅工作量大而且缺乏针对性。而采用html网页结构分析法，可以快速找到源端系统与贴源层数据表的对应关系，此方法使得工作量大大缩短，而且更有针对性。（三）查询贴源层数据到本地已知数据表名，接下来的数据查询工作就变得简单很多。通过以下sql语句查询fc_gc数据表中的数据：select * from buf_cms_all.fc_gc，然后将查询结果导出为excel格式的数据。如果数据量过大超出excel的行数限制，可以在上述sql基础上加一个限制条件：rownum<1，0

7、00，000，这样就可以查询1，000，000条以下的记录数。通过html网页结构分析法，很快将发电客户档案里的其他几个表数据也查询到本地excel文件中。三、应用excel ado技术进行数据处理（一）office ado技术介绍ado是微软提供的一项技术手段，为用户访问数据库中的数据提供了api接口。本文基于微软提供的access和excel的驱动程序，得以实现在excel中调用ado接口访问access或excel数据库。ado的工作原理是先创建ado的类，然后通过一串连接字符串，通过所创建的类来实现对数据库的连接。连接excel的字符串如下："provider=microso

8、ft.ace.oledb.12.0;extended properties=excel 12.0; data source="，“data source”等于所要连接的本地excel文件的完整路径。连接access的字符串如下："provider=microsoft.ace.oledb.12.0; data source="“data source”等于所要连接的本地access文件的完整路径。在连接成功的前提下，通过ado提供的execute方法执行sql语句，并得到查询记录集对象。最终通过copy from recordset方法将查询记录集对象输出到本地的e

9、xcel文件中。由于强大的ado接口，可以使用该接口访问excel和access文件。使用sql语句查询本地数据库不仅速度快，且可提供许多聚合计算的函数，既保证审计人员仍然可以使用excel作为数据分析工具，又兼得数据处理的速度与能力。（二）无效数据删除当excel数据量达到数十万甚至百万量级时，数据处理速度就成为审计人员关切的问题。为突破普通筛选以及函数处理速度慢的现状，本文采用ado技术作为无效数据筛查的手段。如在发电客户档案表中，发电方式字段的有效识别符包括01、02、03、04，而在前面查询所得的fc_gc表内却出现了07、11、99以及空白等不应当出现的识别符。在这种情况下，这些无法

10、识别的识别符应作为无效数据删除。在发电客户档案表中，相当一部分字段都出现了这种情况，因此需要逐一检查并排除。通过sql语句排除无效识别符，只需在sql语句里加几个限制查询条件即可，具体sql语句为：select * from fc_gc where gc_type_code =01and gc_type_code =02and gc_type_code =03and gc_type_code =04（三）数据格式化数据格式化指将数据表中的数据进行整理使其样式统一，便于后续数据分析。如在fc_gc表中的客户建档日期字段内出现了空白日期或日期格式不规范的情况。对于空白的日期，应考虑是否作为无效数据

11、进行删除。日期格式出现了“yyyy-mm-dd hh：mm：ss”和“yyyy-mm-dd”两种格式，应根据业务需求，如将日期统一格式化為“yyyy-mm-dd”格式。这一操作同样可通过sql语句中的format函数实现，具体的sql语句为：select format（build_date，yyyy-mm-dd） from fc_gc。通过此方法将fc_gc数据表中的其他日期类字段统一格式化为“yyyy-mm-dd”格式。四、通过tableau对电力销售进行可视化分析使用vba技术对电力销售明细表进行多表及跨表数据提取汇总，将汇总数据作为数据源连接至tableau，最终通过tableau进行数

12、据可视化展示。（一）数据汇总通过源端系统导出的数据为多个excel表格，其中包括国网辽宁公司整体数据以及各地市公司数据。首先使用excel vba技术将辽宁省及各地市数据汇总到一个电子表格内。语句如下：filetoopen = application.getopenfilename（"excel files（*.xl*）， * .xl*"， "请选择文件"， true）if isarray（filetoopen） = false thenexit subelsefor i = 1 to ubound（filetoopen）set wb = workboo

13、ks.open（filetoopen（i）nextend if通过上述语句可以选择多个excel文件并将它们逐个打开，再通过以下语句获取各个工作表的数据至一个汇总表：sheet4.range（"d" & maxrow1）.value = sht.range（"e11"）.valuesheet4.range（"e" & maxrow1）.value = sht.range（"g11"）.valuesheet4.range（"f" & maxrow1）.value = sht

14、.range（"h11"）.valuesheet4.range（"g" & maxrow1）.value = sht.range（"i11"）.value最终，将辽宁省及各地市的售电量、峰平谷电量、应收电费等数据进行汇总，如表1所示。（二）售电量可视化分析将上述数据作为数据源与tableau进行连接。在tableau软件中将“地市”字段转换为地理角色县，tableau即可自动创建各地市的经纬度度量值，进而通过拖拽经纬度创建辽宁省售电量地图，如图1所示。在上述售电量地图中，可以看到tableau创建的辽宁省各地市地图分界线以及各

15、地市名称。每个地市用不同颜色的气泡表示，气泡大小与售电量总和大小成正比。通过该图可以看出，沈阳、鞍山、营口、大连4个地市的售电量总和最大，丹东、阜新等售电量总和相对较小。tableau可以创建的图表类型非常多，不仅仅局限于地图可视化，通过业务分析创建各种类型的可视化图表，还提供了仪表盘工具，可将各种可视化图表放置到仪表盘内进行布局设计。另外，还可通过关联数据创建动态可视化图表，如图2所示。通过一系列可视化图表展示，可直观清晰地对比各地市售电量数据、峰平谷数据、应收电费数据大小、走向趋势等信息，以达到透过可视化找异常、透过异常找原因、透过原因直达病灶解决问题的审计目的。五、tableau与power bi及excel的对比power bi是微软开发的bi软件，power bi的组件较多，其中最主要的有power query、power pivot和power bi desktop。power bi与tableau相比，劣势在于组件多，每种组件的学习成本也相对较高，并且目前国内相关的学习资料相对较少。审计人员想要得心应手地运用还需学习m语言及dax语言。t