Cognos 简明教程

Data Warehouse - ETL & Reporting Tools

ETL 工具从所有这些异构数据源中提取数据,转换数据(如应用计算、连接字段和键、删除不正确的字段等),并将数据加载到数据仓库中。

An ETL tool extracts the data from all these heterogeneous data sources, transforms the data (like applying calculations, joining fields, keys, removing incorrect data fields, etc.), and loads it into a Data Warehouse.

Extraction

在 ETL 加载期间需要一个临时存储区域。需要临时存储区域的原因有多种。源系统在特定时间段内才可用于提取数据。此时间段短于总数据加载时间。因此,临时存储区域允许从源系统中提取数据并将其保留在临时存储区域中,直到时间段结束。

A staging area is required during the ETL load. There are various reasons why staging area is required. The source systems are only available for specific period of time to extract data. This period of time is less than the total data-load time. Therefore, staging area allows you to extract the data from the source system and keeps it in the staging area before the time slot ends.

当需要将数据从多个数据源中组合在一起或需要将两个或更多个系统连接在一起时,需要临时存储区域。

The staging area is required when you want to get the data from multiple data sources together or if you want to join two or more systems together.

For example − 你无法执行 SQL 查询来连接来自两个不同物理数据库的两个表。

For example − You will not be able to perform an SQL Query joining two tables from two physically different databases.

不同系统的数据提取时间段会根据时区和运营时间而有所不同。从源系统中提取的数据可以用在多个数据仓库系统、运营数据存储中等等。

The data extractions’ time slot for different systems vary as per the time zone and operational hours. The data extracted from the source systems can be used in multiple Data Warehouse Systems, Operation Data Stores, etc.

ETL 允许进行复杂转换,并要求额外的区域来存储数据。

ETL allows you to perform complex transformations and requires extra area to store the data.

extraction

Transform

在数据转换中,对提取的数据应用一组函数,以将其加载到目标系统中。不需要任何转换的数据被称为直接移动或直通数据。

In data transformation, you apply a set of functions on extracted data to load it into the target system. The data that does not require any transformation is known as a direct move or pass through data.

可以对从源系统中提取的数据应用不同的转换。例如,你可以执行自定义计算。如果需要销售额总和并且数据库中没有这一数据,则可以在转换期间应用 SUM 公式并加载数据。

You can apply different transformations on extracted data from the source system. For example, you can perform customized calculations. If you want sum-of-sales revenue and this is not in database, you can apply the SUM formula during transformation and load the data.

For example − 如果表中不同列中的第一个和最后一个名称,则可以在加载之前使用联接。

For example − If you have the first name and the last name in a table in different columns, you can use concatenate before loading.

Load

在加载阶段,将数据加载到最终目标系统中,它可以是平面文件或数据仓库系统。

During the Load phase, data is loaded into the end-target system and it can be a flat file or a Data Warehouse system.

BI Reporting Tool

业务用户使用 BI(商业智能)工具来创建有关数据仓库中交易数据的基本、中等和复杂报告和使用 Information Design Tool/UDT 创建数据集。可以使用各种 SAP 和非 SAP 数据源来创建报告。

BI (Business Intelligence) tools are used by business users to create basic, medium, and complex reports from the transactional data in data warehouse and by creating Universes using the Information Design Tool/UDT. Various SAP and non-SAP data sources can be used to create reports.

市场上有相当多的 BI 报告、仪表板和数据可视化工具。其中一些如下所述 −

There are quite a few BI Reporting, Dashboard and Data Visualization Tools available in the market. Some of which are as follows −

  1. SAP Business Objects Web Intelligence (WebI)

  2. Crystal Reports

  3. SAP Lumira

  4. Dashboard Designer

  5. IBM Cognos

  6. Microsoft BI Platform

  7. Tableau Business Intelligence

  8. JasperSoft

  9. Oracle BI OBIEE

  10. Pentaho

  11. QlickView

  12. SAP BW

  13. SAS Business Intelligence

  14. Necto

  15. Tibco Spotfire