Notes for FIT5195: Business intelligence and data warehousing

发表于 更新于

好家伙这个一上来我就听不懂……当初没分到信息管理现在遭罪……

S1 Intro

这门课是基于intro to db的，默认大家都是SQL老手了。同样的，BI和数据仓库也是在DB之上的概念。

Operational DB

企业拿来操作日常行为的数据库（买卖，预约 etc， 用来支持商业行为）

  • 使用集中于事务
  • 便于决策制定
  • 但是决策分析能力不够

数据仓库

相比Operational DB加强了决策能力。

数据仓库是数据库的多维视图，具有聚合和预计算的摘要

建立数据仓库

通过将Operational DB转换为数据仓库来创建数据仓库

转换包括一系列数据操作步骤

所有的数据转换称为Extract-Transform-Load ETL

使用数据仓库

使用数据仓库的手段是从数据仓库提取数据以进行进一步的数据分析

从数据仓库中提取数据的查询是在线分析工具或OLAP

OLAP会捕获原始数据，该原始数据可以使用任何Business Intelligence（BI）工具格式化。
在OLAP中，对数据进行集中处理的数据最为重要。BI工具可用于进一步的表示和可视化
接收原始数据的BI工具可以任何形式表示数据：报告，图形，仪表板等

数据分析的需要

BI

“商务智能”（BI）是指描述用于捕获，收集，集成，存储和分析数据的综合，内聚和集成的工具集和过程，其目的在于生成和呈现信息以支持业务决策。
BI是一个框架，可以使企业将数据转换为信息，信息知识和知识能力不足。

一般而言，BI提供以下框架：

  • 收集和存储运营数据
  • 将运营数据汇总为决策支持数据
  • 分析决策支持数据以生成信息
  • 向最终用户显示此类信息以支持业务决策
  • 制定业务决策，从而生成更多收集，存储和不久的数据（重新启动流程）
  • 监视结果以评估业务决策的结果，从而可以收集，存储和存储更多数据
  • 高度准确地预测未来的行为和结果

在实践中，第一点是收集和存储操作数据，这不会影响到操作系统的持久性；而恰恰相反，它会损害操作系统的功能。
但是，BI系统将使用操作数据作为信息的输入材料。
其余的过程和结果将前面的点解释为面向生成知识，并且它们是BI系统的重点

S2 星形架构 Star Schema

星形架构

了解星型架构及其对 Power BI 的重要性 - Power BI | Microsoft Docs

Star Schema 是多维视图的一种设计表示形式。是数据建模技术用于将多维决策支持数据映射到关系数据库中。

对于Star Schema的开发主义者来说，关系建模技术是一种错误的解决方案：ER和归一化，无需多余的数据库结构即可处理复杂的数据分析。

Star Schema由三个部件组成：

  1. Facts事实（可度量数值，表达特定商业领域或活动）
  2. Dimensions维度（其他属性，提供对Facts额外的视角）
  3. Attributes属性（包含在维度表中）

转化（ETL）流程

从ER图转为数据仓库（星形架构）

首先得有个分析点

双列表格法

创建星型模式时，您需要想象要分析的数据由两列组成。

第一列是类别（例如A，B，C，D），第二列是统计数字（例如 B）。

第二列（例如F）必须在所有两列表中保持一致。

多个Facts下措施：

双列表中的第二列是数字事实度量值（例如F列），实际上可以是多列（称它们为：F1，F2， F3）

只要所有这些列（例如F1，F2， F3）涉及所有类别（例如A，B，C，D）。

SQL

  1. 先建立各个维度表
  2. 将各个维度的行写入维度表
  3. 建立tempfact表：select where A.id=B.id将各个事实表源的列 as到tempfact表（即完成表建立又同时数据转移）
  4. 增加与源数据类型不同的列到tempfact表（例如源中DATE，事实表中NUMBER）
  5. 为4中的新列写入数据
  6. 最后建立fact表：使用group by从tempfact里把需要的列取出

S3 桥接表 Bridge Tables

好鸡儿难日常听不懂……

Bridge Tables

用来连接俩维度表（其中只有一个连接事实）

到Fact source路径上存在N:N关系（可能N:N被分为1:N和N:1）

you first need to identify the attributes required to calculate the fact measures, then circle the tables contain those attributes as a fact source. After that, you identify where the attributes needed to create the dimensions, and assess the relationship between those tables with the fact source table. If there is a many-to-many relationship and the dimension does not contribute directly to the calculation of the fact measure, a bridge table is needed

数据清理

好像这个直接默认是已掌握内容……orz

开始的时候，先问问自己

  1. How many records in the operational database?
  2. How many records in the data warehouse?
  3. What kind of data is in the operational database?
  4. How do the tables look like in the data warehouse?

先做data explorations

先对所有的operational database的table都select count(*)一遍

对比数据仓库和operational database

  1. 一致性检查：根据每个变量的合理取值范围和相互关系，检查数据是否合乎要求，发现超出正常范围、逻辑上不合理或者相互矛盾的数据
  2. 无效值和缺失值的处理

S4 Multifact

就讲讲怎么在一个DW里放多个Fact来更好地表达measure

image-20210119153311615image-20210119153321174

我们需要在Fact表中存放一个avg参数么？NO： Average of an Average

在FACT中存放平均值不是个好主意，那我们应该如何处理这些问题？

我们可以存放总数（total xxx）以及数量（number of xxx）

S5 DW Architecture

Data Warehouse Architecture

根据Tut来看讲的主要是multifact

Level of Aggregations

聚合程度，最次是Level0就基本上是从Operational DB里拿的字段，然后1然后2这样往上越大越浓缩就是精华

lower down level方法：

  1. 加个新DIM （）
  2. Replace an existing DIM with a higher granularity DIM（Fact的measure值也被分割）

S6 Temporal DW

Temporal (or historical) aspect of records is incorporated into the data warehouse

记录的时间（或历史）被合并到数据仓库中，比如 书籍价格随时间的变化

也叫做SCD（Slowly Changing Dimensions，缓慢改变的维度）

with Bridge Table

使用Bridge的话可以像加个弱实体那样加个DIM

像这样，其中BookID 和俩Date形成composite key

如果Book和BookPrice是1：1关系那就可以并到一起

例如image-20210223223650881变image-20210223223700052

Temporal Attributes

Temporal attribute是一个属性，其值具有生命周期

例如，每个书价都有生命期（即时间限制），即BookPriceDIM表中的StartDate和EndDate

Temporal Dimensions

Temporal Dimensions是其中具有特定生命周期的维度表

例如图中BranchHistoryDIM（MerchandisePriceDIM算Temporal attribute）

Slowly Changing Dimensions

主要讲SCD的几个种类，定义前面说了。值得一提的是这个Slowly也是很重要的，对于快速改变的值，比如股票价格，车辆位置，需要的是实时(realtime) DW，或者说流式(stream)DW

Type 0

实际上并不在DIM上存历史数据

建立数据仓库时，DIM存储记录的“原始或初始”的值

例如：book的全价记录在BookDIM中

Type 1

和T0一样实际上并不在DIM上存历史数据

只记录最新值

例如：Book最新价格将记录在BookDIM中

Type 2

从主DIM跟踪历史

例如：更改Book价格后，会创建具有相同详细信息的“另一本书”，有新的BookID和新价格

Type 3

简化版T2

仅保留当前（Cur）和前一个（Prev）的值，而不保留整个历史记录

例如：仅记录书的最后两个价格

基本原理：

  • 假设不需要分析完整的历史记录
  • 大多数分析将以当前价格和最多一个过去价格（例如， 与趋势比较

Type 4

创建一个新DIM来维护attribute 变更的历史记录

如前面（with Bridge Table那里）的BookPriceDIM

主要优势：同一本书无需具有其他BookID。 此外，保留更改的全部历史记录。

Type 6

T2和T3的结合。同一本书不需要单独的ID（同T3），但是会保留整个历史记录（同T2）

Composite key {BookID, StartDate, EndDate}

如果由于Date，BookDIM和BookSalesFACT之间存在M:M关系

Sol1：给BookDIM加个新的surrogate key

Sol2：把StartDate和EndDate加到FACT->与TimeID混乱

Sol3：加个辅助性的表（或Bridge Table）在BookSalesFACT和BookDIM之间->也乱

结论：Sol1最佳

S7 Snowflake Schema

给Star的DIM分个层级（Hierarchy）就变Snowflake了

注意1:M关系，不能整倒了

Determinant Dimensions

显性维度

在Star schema下检索的所有数据都必须使用此DIM，否则检索的数据将变得毫无意义。例如PetrolType

差不多就是做Schema之前问题里都提到的一个要素吧

S8 OLAP

熟悉的SQL

基础

COUNT([distinct] A)

SUM([distinct] A)

AVG([distinct] A)

MAX(A)

MIN(A)

GROUP BY

进阶

CUBE：扩展GROUP BY子句以在单个查询中生成交叉表格式的信息。

ROLLUP：扩展GROUP BY子句以生成从最详细到总计的越来越细粒度的聚合。

GROUPING：与SELECT语句一起使用，以显示有关聚合级别的信息以及每个聚合级别的相关小计。

Partial CUBE：GROUP BY expr1, CUBE (expr2, expr3)

Partial ROLLUP：GROUP BY expr1, ROLLUP (expr2, expr3)

高阶

RANK( ) OVER ([query_partition_clause] order_by_clause)

DENSE_RANK( ) OVER ([query_partition_clause] order_by_clause)

DENSE对于同rank重复情况下只加一

RANK() OVER (PARTITION BY order_by_clause)

例如select RANK() OVER (PARTITION BY channel_desc ORDER BY SUM(amount_sold) DESC) AS RANK_BY_CHANNEL from XXX

如果需要显示多个属性的排名，则需要对聚合进行分区（PARTITION BY），以便可以为每个指定的属性显示适当的排名

select rank() from XXX 外面再套个select * from () where rank<5来实现TopN

ROW_NUMBER函数为分区中的每一行分配一个唯一的编号（依次从1开始，由ORDER BY定义）。有点像sequence。用来手动排rank

ROW_NUMBER() OVER ([query_partition_clause] order_by_clause)

PERCENT RANK

percent_rank() over (order by sum(f.revenue) desc) as “Percent Rank”

Cumulative Aggregates

SUM(SUM(XXX)) OVER (ORDER BY aaa,bbb ROWS UNBOUNDED PRECEDING)

UNBOUNDED表示无界，PRECEDING前

解析函数SUM为每一行定义一个窗口，该窗口从分区的开头（UNBOUNDED PRECEDING）开始，默认情况下在当前行结束。
在此示例中，需要嵌套SUM，因为我们正在对本身就是SUM的值执行SUM。
嵌套聚合在分析聚合函数中经常使用

SUM(SUM(XXX)) OVER (PARTITION BY aaa ORDER BY aaa,bbb ROWS UNBOUNDED PRECEDING)

Moving Aggregate

(AVG(SUM(XXX)) OVER (ORDER BY aaa,bbb ROWS 2 PRECEDING)

(AVG(SUM(XXX)) OVER (ORDER BY aaa,bbb ROWS 2 PRECEDING)

用两个的平均表现moving

S9 BI

Drill Down

Drill Up (a.k.a. Roll Up)

Drill Through

Drill Across

EXAM

考试就是1-9的内容。较难，不熟练会时间来不及（就是我）。

考试内容和模拟考形式完全一致