35、连接SAS数据仓库与其他系统

连接SAS数据仓库与其他系统

1. 数据仓库结构概述

在数据处理中，数据仓库的结构至关重要。许多数据仓库采用典型的关系结构，通过主键和外键将表连接在一起，这种结构可以用实体 - 关系图（ERD，也称为模式）来表示。

1.1 关系结构示例

以大学学生注册数据库的 ERD 为例，图中的每个框代表一个数据表，框内列出了表中的变量。例如，“Lecturer” 表包含与讲师相关的变量，其中 “Employee_ID” 是主键。而在 “Course” 表中，“Employee_ID” 作为外键，用于跟踪分配给课程的讲师。连接 “Lecturer” 表的 “Employee_ID” 主键和 “Course” 表的 “Lecturer_ID” 外键的线的形状表明这是一种一对多的关系，即每个讲师可能对应多门课程。

1.2 关系结构与业务数据库

大多数业务生产数据库也采用关系结构，但这些数据库是为支持特定业务流程而定制的，例如大学学生注册流程。而数据仓库则设计了不同的关系结构，以支持分析师使用转换后的数据进行分析。与业务环境中管理生产数据库相比，在数据仓库环境中加载数据需要执行更多的 ETL 操作。不过，有业务数据库 ETL 经验的人会发现数据仓库工作虽有所不同，但也很熟悉。

1.3 星型模式结构

除了关系结构，还有一种专门为数据仓库优化的结构——星型模式（也称为立方体结构）。与关系数据库不同，数据仓库中分析师的主要工作是查询数据，而不是编辑数据。星型模式就是为了完全优化数据查询而开发的，不考虑数据编辑。

2. 星型模式概念理解

2.1 星型模式原理示例

为了理解星型模式，我们以股票经纪公司为例。假设我们想查询某只股票的收盘价，传统的关系查询需要在包含所有上市公司的表和每日股票价格表之间进行关联，这会花费一些时间。但如果我们创建一个数据集市，将 2018 年公司跟踪的所有股票数据整合到一个巨大的 Excel 表格中，这个表格包含了 2018 年的每一天、公司跟踪的每一家有股票的公司以及每天每只股票的收盘价。这样，查询时就无需进行表连接，也不需要使用主键或外键，查询速度会非常快。

2.2 星型模式特点

星型模式虽然与上述示例不完全相同，但遵循了一些相同的原则：
- 包含非常大且长的表，查询效率高。
- 尽量减少查询中的连接操作。
- 时间周期的公式（如最大值、平均值等）已经预先计算好，查询时无需实时运行公式，只需查找预先计算好的指标。
- 时间周期非常重要，不同的时间周期需要不同的计算数据。例如，如果要按天、月、季度和年聚合指标，就需要进行四组计算。

2.3 星型模式设计示例

我们以一个假设的数据仓库为例，该仓库用于研究美国退伍军人退伍后的生活质量（QoL）。仓库包含以下数据：
- 年度 BRFSS 健康调查数据：每年在线发布，包含退伍军人身份、健康状况和行为信息以及州的 FIPS 代码。
- 人口普查薪资数据：关于美国各州退伍军人薪资的人口普查数据，可在线获取。
- 州医院数据：来自美国医院目录（AHD）的各州医院数据，可在线获取。

由于我们希望能够比较各州之间的差异，且这些数据集都包含州变量，因此分析师可能会进行大量的州级查询。基于此，我们可以创建一个 “state_fact” 事实表，该表除了自身的主键外，完全由外键组成。同时，我们还需要三个维度表：“brfss_dim” 用于 BRFSS 数据，“census_dim” 用于人口普查数据，“ahd_dim” 用于 AHD 医院数据。

以下是星型模式设计的一些特点：
| 表格名称 | 特点描述 |
| ---- | ---- |
| state_fact | 仅包含变量的键 |
| time_dim | 包含月份、季度和年份的变量，但不包含周、日或更短时间周期的变量 |
| census_dim 和 ahd_dim | 提供地理区域的汇总统计信息，如各州退伍军人薪资统计和各州医院数量 |
| brfss_dim | 其中的汇总统计信息（如 “rate_vets”）是从个人级别的 BRFSS 数据集计算得出的 |

2.4 星型模式查询示例

假设我们想查看佛罗里达州退伍军人的百分比以及该州的医院数量，并选择按年查询。应用程序将执行以下操作：
1. 使用 “state_fact” 表过滤出与佛罗里达州和年份相关的行。
2. 由于退伍军人比率数据在 “brfss_dim” 表中，医院数量数据在 “ahd_dim” 表中，因此只使用这些表与 “state_fact” 表进行连接，而不执行与 “census_dim” 表的连接。

这个简单的示例展示了星型模式如何显著加快查询处理时间。虽然 SAS 不像 IBM Cognos 那样专门用于提供星型模式，但 SAS 可以对大数据进行 ETL 操作，创建用于星型模式软件的大表。

3. 连接非 SAS 数据存储

3.1 SAS 的 ODBC 连接能力

在 SAS 的早期，其环境相对封闭，原始数据主要通过静态媒体将 .txt 和 .csv 文件加载到 SAS 服务器。而现在，SAS 具备了开放数据库连接（ODBC）能力，程序员可以使用 SAS/ACCESS 组件打开与其他数据库的 ODBC 连接，查询并提取数据到 SAS 环境中。不过，SAS 大学版不提供此功能。

3.2 SQL 视图的理解

在 SQL 中，打开与远程 SQL 数据库的连接是常见操作，并且有一套创建视图的命令。视图是一种不修改底层数据，而是提供数据视图的选择查询。

假设我们是美国人口普查的 SQL 程序员，想要将另一个 SQL 数据库中的数据转移到自己的环境中，需要完成以下步骤：
1. 建立连接 ：首先，建立与远程 SQL 数据库环境的连接，通过 SQL 界面导航找到要查询的表，这些表包含我们想要转移的数据。
2. 创建选择视图以准备数据转移查询 ：使用 SQL 界面创建选择查询视图，类似于 SAS 中 “PROC REPORT” 的演示。例如，远程 SQL 系统的表包含所有美国州的数据，但我们只需要佛罗里达州的记录，并且只需要源表中的几列。

graph LR
    A[建立连接] --> B[找到要查询的表]
    B --> C[创建选择视图]
    C --> D[准备数据转移]

通过以上步骤，我们可以更好地理解如何在 SAS 环境中连接非 SAS 数据存储，并利用 SQL 视图进行数据转移。

4. SAS 与非 SAS 数据存储连接的实际操作

4.1 使用 PROC SQL 进行数据转移

在 SAS 中，我们可以使用 PROC SQL 来实现与非 SAS 数据存储的连接和数据转移。虽然还有其他方法，如使用 PROC DBLOAD 从 SAS 向 Microsoft SQL 转移数据，但这里我们重点介绍 PROC SQL。

以下是使用 PROC SQL 连接到非 SAS 数据存储并进行数据操作的一般步骤：
1. 建立连接 ：使用 SAS/ACCESS 组件通过 ODBC 连接到目标数据库。这通常需要在 SAS 中配置连接信息，如数据库驱动程序、服务器地址、用户名和密码等。
2. 编写 SQL 查询 ：在 PROC SQL 语句中编写查询语句，用于从目标数据库中选择所需的数据。例如，我们可以使用 SELECT 语句选择特定列和行的数据。
3. 执行查询并导入数据 ：运行 PROC SQL 语句，将查询结果导入到 SAS 数据集。

以下是一个简单的示例代码：

PROC SQL;
    CONNECT TO odbc AS my_conn (DSN=my_dsn USER=my_user PASSWORD=my_password);
    CREATE TABLE my_sas_dataset AS
        SELECT *
        FROM CONNECTION TO my_conn
            (SELECT column1, column2
             FROM remote_table
             WHERE condition);
    DISCONNECT FROM my_conn;
QUIT;

4.2 数据过滤与视图创建

在使用 PROC SQL 进行数据查询时，我们可以对数据进行过滤，类似于在 SQL 中创建视图的操作。例如，我们有一个包含多个州数据的数据集，我们可以只选择其中一个州的数据进行查看，但不改变原始数据集。

以下是一个示例代码：

PROC SQL;
    CREATE VIEW my_view AS
        SELECT *
        FROM my_sas_dataset
        WHERE state = 'Florida';
QUIT;

在这个示例中，我们创建了一个名为 “my_view” 的视图，它只包含佛罗里达州的数据。当我们查询这个视图时，实际上是在查询原始数据集中符合条件的部分，但原始数据集保持不变。

4.3 数据转移与保存

在将数据从非 SAS 数据存储转移到 SAS 环境后，我们可能需要将结果保存为新的数据集。可以使用以下代码将视图中的数据保存为新的数据集：

PROC SQL;
    CREATE TABLE new_dataset AS
        SELECT *
        FROM my_view;
QUIT;

这样，我们就将视图中的数据保存为一个新的 SAS 数据集 “new_dataset”。

5. 总结与应用场景

5.1 不同结构的优势总结

• 关系结构 ：适用于需要频繁编辑和查询数据的业务场景，如业务生产数据库。它通过主键和外键将表连接起来，能够很好地支持复杂的业务流程。
• 星型模式 ：专门为数据仓库优化，适合分析师进行大量的查询操作。它通过减少表连接和预先计算指标，提高了查询速度。

5.2 应用场景分析

• 业务数据库 ：在业务生产环境中，关系结构的数据库能够满足日常业务的需求，如订单管理、客户信息管理等。
• 数据仓库 ：对于需要进行数据分析和决策支持的场景，星型模式的数据仓库能够快速提供所需的信息，如市场趋势分析、销售业绩分析等。

5.3 未来发展趋势

随着数据量的不断增长和数据分析需求的增加，数据仓库和数据处理技术将不断发展。未来，我们可能会看到更多的混合结构和优化算法，以提高数据处理的效率和准确性。同时，与其他技术的集成，如人工智能和机器学习，也将为数据分析带来更多的可能性。

以下是不同结构在不同场景下的应用对比表格：
| 结构类型 | 适用场景 | 优势 |
| ---- | ---- | ---- |
| 关系结构 | 业务生产数据库 | 支持复杂业务流程，方便数据编辑和查询 |
| 星型模式 | 数据仓库 | 提高查询速度，适合数据分析 |

通过对 SAS 数据仓库与其他系统连接的深入了解，我们可以根据不同的业务需求选择合适的结构和方法，实现数据的高效处理和分析。无论是关系结构还是星型模式，都有其独特的优势和应用场景，我们需要根据实际情况进行合理的选择和运用。同时，掌握 SAS 与非 SAS 数据存储的连接技术，能够让我们更好地整合不同来源的数据，为业务决策提供更有力的支持。