BI系统用户访问数据分析

商业智能（Business Intelligence，简称：BI），又称商业智慧或商务智能，指用现代数据仓库技术、线上分析处理技术、数据挖掘和数据展现技术进行数据分析以实现商业价值。
——百度百科

文章目录

• 前言
• 一、痛点梳理
• 二、案例分享
• • （一）获取BI系统用户访问日志
  • （二）设计开发DWD
  • （三）设计ADS
  • （四）可视化样例
• 总结

前言

分析BI系统的用户访问数据分析报表设计开发流程及个人的思考，供各位小伙伴参考。

一、痛点梳理

1. 不清楚BI系统中各报表的访问情况，哪些报表使用率高，哪些报表没人看。
2. 不清楚哪些部门哪些人员使用报表的频率高，哪些部门哪些人员很少看报表。
3. 不清楚BI系统中用户的注册激活情况。

二、案例分享

（一）获取BI系统用户访问日志

xxx_log

（二）设计开发DWD

1. dwd_xxx_tf_dm （xxx用户操作事务事实表）
   以天为分区，记录每个用户每天在BI系统上的浏览数据。
2. dwd_xxx_tf_dm_ini （xxx用户操作事务事实表）
   将历史的日志数据利用动态分区形成事实表历史分区数据，案例中dwd直接关联维度表冗余维度，实际生产中可以考虑在下游进行关联。

-- #####################################################
-- name    : dwd_xxx_tf_dm.sql
-- desc    : xxx用户操作事务事实表
-- version : xxx_BI_2.0.1
-- source  : xxx_log; dim_aaa_ds; dim_bbb_ds;
-- create  : zhangliushi 20220101
-- modify  :
-- #####################################################USE dwd;SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.exec.max.dynamic.partitions = 1000;
SET hive.exec.max.dynamic.partitions.pernode = 1000;-- 记录BI用户日志数据;
CREATE EXTERNAL TABLE IF NOT EXISTS dwd.dwd_xxx_tf_dm
(etl_id            STRING    COMMENT 'ETL代理ID',bi_user_id        STRING    COMMENT 'BI用户ID',user_email        STRING    COMMENT '用户邮箱',employee_code     STRING    COMMENT '雇员代码',employee_name     STRING    COMMENT '雇员名称',first_dept        STRING    COMMENT '一级部门',second_dept       STRING    COMMENT '二级部门',third_dept        STRING    COMMENT '三级部门',fourth_dept       STRING    COMMENT '四级部门',fifth_dept        STRING    COMMENT '五级部门',visit_time        STRING    COMMENT '访问时间',menu_full_path    STRING    COMMENT '访问菜单全路径',etl_time          STRING    COMMENT 'ETL时间',first_menu        STRING    COMMENT '一级菜单',second_menu       STRING    COMMENT '二级菜单',third_menu        STRING    COMMENT '三级菜单',fourth_menu       STRING    COMMENT '四级菜单'
)
COMMENT 'xxx用户操作事务事实表'
PARTITIONED BY (pt_d VARCHAR(10) COMMENT '天分区')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION 'cosn://bpit-zzz-123456/dwd/dwd_xxx_tf_dm'
TBLPROPERTIES('orc.compress'='ZLIB')
;-- 动态分区插入数据;
INSERT OVERWRITE TABLE dwd.dwd_xxx_tf_dm
PARTITION (pt_d)
SELECTUUID()                                                                           AS etl_id,t1.user_id                                                                       AS bi_user_id,t1.user_email                                                                    AS user_email,t2.employee_code                                                                 AS employee_code,t2.employee_name                                                                 AS employee_name,t2.first_dept                                                                    AS first_dept,t2.second_dept                                                                   AS second_dept,t2.third_dept                                                                    AS third_dept,t2.fourth_dept                                                                   AS fourth_dept,t2.fifth_dept                                                                    AS fifth_dept,FROM_UTC_TIMESTAMP(DATE_FORMAT(t1.create_date, 'yyyy-MM-dd HH:mm:ss'), 'PRC')    AS visit_time,t1.menu_full_path                                                                AS menu_full_path,DATE_FORMAT(CURRENT_TIMESTAMP,'yyyy-MM-dd HH:mm:ss')                             AS etl_time,t3.first_menu                                                                    AS first_menu,t3.second_menu                                                                   AS second_menu,t3.third_menu                                                                    AS third_menu,t3.fourth_menu                                                                   AS fourth_menu,DATE_FORMAT(FROM_UTC_TIMESTAMP(DATE_FORMAT(t1.create_date, 'yyyy-MM-dd HH:mm:ss'), 'PRC'),'yyyy-MM-dd')    AS pt_d
FROM
(SELECTmenu_full_path,user_id,user_email,create_dateFROM ods.xxx_logWHERE pt_d = '2022-02-16'AND user_email != 'admin'
) t1
LEFT OUTER JOIN
(SELECTemployee_code,employee_name,first_dept,second_dept,third_dept,fourth_dept,fifth_dept,employee_emailFROM dim.dim_aaa_dsWHERE pt_d = DATE_SUB('${pt_d}',1)
) t2
ON t1.user_email = t2.employee_email
LEFT OUTER JOIN
(SELECTmenu_full_path,first_menu,second_menu,third_menu,fourth_menuFROM dim.dim_bbb_ds
) t3
ON t1.menu_full_path = t3.menu_full_path
;

3. dwd_xxx_user_login_sf_ds（xx用户登录周期快照事实表）
   案例中dwd直接关联维度表冗余维度，实际生产中可以考虑在下游进行关联。

-- #####################################################
-- name    : dwd_xxx_user_login_sf_ds.sql
-- desc    : xx用户登录周期快照事实表
-- version : xxx_BI_2.0.1
-- source  : ods_xxx_bi_users; dim_xxx_ds;
-- create  : zhangliushi 20220323
-- modify  :
-- #####################################################USE dwd;-- 记录BI用户登录情况, 创建和修改时间;
CREATE EXTERNAL TABLE IF NOT EXISTS dwd.dwd_xxx_user_login_sf_ds
(etl_id                STRING    COMMENT 'ETL代理ID',bi_user_id            STRING    COMMENT 'BI用户ID',user_email            STRING    COMMENT '用户邮箱',employee_code         STRING    COMMENT '雇员代码',employee_name         STRING    COMMENT '雇员名称',first_dept            STRING    COMMENT '一级部门',second_dept           STRING    COMMENT '二级部门',third_dept            STRING    COMMENT '三级部门',fourth_dept           STRING    COMMENT '四级部门',fifth_dept            STRING    COMMENT '五级部门',id_activation_flag    STRING    COMMENT '账号激活标志',id_cancel_flag        STRING    COMMENT '账号注销标志',last_login_time       STRING    COMMENT '最近一次登录时间',id_create_time        STRING    COMMENT '账号创建时间',id_modify_time        STRING    COMMENT '账号修改时间',etl_time              STRING    COMMENT 'ETL时间'
)
COMMENT 'xx用户登录周期快照事实表'
PARTITIONED BY (pt_d VARCHAR(10) COMMENT '天分区')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
LINES TERMINATED BY '\n'
STORED AS ORC
LOCATION 'cosn://bpit-zzz-123456/dwd/dwd_xxx_user_login_sf_ds'
TBLPROPERTIES('orc.compress'='ZLIB')
;INSERT OVERWRITE TABLE dwd.dwd_common_bi_user_login_sf_ds
PARTITION (pt_d = '${pt_d}')
SELECTUUID()                                                                               AS etl_id,t1.id                                                                                AS bi_user_id,t1.user_email                                                                        AS user_email,t2.employee_code                                                                     AS employee_code,t2.employee_name                                                                     AS employee_name,t2.first_dept                                                                        AS first_dept,t2.second_dept                                                                       AS second_dept,t2.third_dept                                                                        AS third_dept,t2.fourth_dept                                                                       AS fourth_dept,t2.fifth_dept                                                                        AS fifth_dept,IF(t1.last_login_date IS NULL, '未激活', '已激活')                                   AS id_activation_flag,IF(t1.active IS TRUE, '账号可用', '账号已注销')                                      AS id_cancel_flag,FROM_UTC_TIMESTAMP(DATE_FORMAT(t1.last_login_date, 'yyyy-MM-dd HH:mm:ss'), 'PRC')    AS last_login_time,FROM_UTC_TIMESTAMP(DATE_FORMAT(t1.create_date, 'yyyy-MM-dd HH:mm:ss'), 'PRC')        AS id_create_time,FROM_UTC_TIMESTAMP(DATE_FORMAT(t1.write_date, 'yyyy-MM-dd HH:mm:ss'), 'PRC')         AS id_modify_time,DATE_FORMAT(CURRENT_TIMESTAMP,'yyyy-MM-dd HH:mm:ss')                                 AS etl_time
FROM
(SELECTid,user_email,active,last_login_date,create_date,write_dateFROM ods.ods_xxx_bi_usersWHERE pt_d = DATE_SUB('${pt_d}',1)
) t1
LEFT OUTER JOIN
(SELECTemployee_code,employee_name,first_dept,second_dept,third_dept,fourth_dept,fifth_dept,employee_emailFROM dim.dim_xxx_dsWHERE pt_d = DATE_SUB('${pt_d}',1)
) t2
ON t1.user_email = t2.employee_email
;

（三）设计ADS

1. 依赖xxx用户操作事务事实表形成用户操作统计表
2. 依赖xx用户登录周期快照事实表形成用户登录统计表
3. 依赖xx用户登录周期快照事实表和依赖xxx用户操作事务事实表形成注册用户访问统计表

注：样例中没有建DWS，实际生产可以考虑建DWS表，在DWS关联维度。

（四）可视化样例

总结

商业智能的概念在1996年最早由加特纳集团（Gartner Group）提出，加特纳集团将商业智能定义为：商业智能描述了一系列的概念和方法，通过应用基于事实的支持系统来辅助商业决策的制定。商业智能技术提供使企业迅速分析数据的技术和方法，包括收集、管理和分析数据，将这些数据转化为有用的信息，然后分发到企业各处。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！