数据仓库常见问题：架构、建模、元数据等（20～40字）

Question

List.doc

需积分: 0 114 浏览量更新于2024-01-20 收藏 375KB DOC 举报

身份认证购VIP最低享 7 折!

30元优惠券

"BI Question List.doc"中提供了关于数据仓库（Data Warehouse）的一系列问题和相关的概念和方法论。接下来，我们将对这些问题进行总结和分析。首先，BI（Business Intelligence）是指通过收集、整理、分析和解释数据，为企业决策提供支持和指导的一种技术和方法。数据仓库是BI的核心基础设施，它是一个集中存储、整合和管理企业数据的系统。其中，数据仓库具有以下特点： 1. 集成性：数据仓库将来自不同系统和部门的数据整合到一个统一的数据存储中，使得企业可以从数据仓库中获取全面、一致的数据。 2. 非易失性：数据仓库中的数据一般是只读的，并不会随着时间的推移被修改或删除，保证了数据的稳定性和可信度。 3. 主题性：数据仓库是以主题为导向进行建模和分析的，而不是按照业务过程或应用系统进行划分。 4. 历史性：数据仓库保存了企业的历史数据，可以通过时间维度进行分析和比较，寻求数据的发展趋势和模式。此外，数据仓库与OLTP（Online Transaction Processing）和OLAP（Online Analytical Processing）的区别也是BI领域的重要问题。OLTP是指面向日常业务操作的数据库系统，强调高并发性和数据的快速写入和更新。而OLAP则是针对决策支持和分析的数据库系统，强调数据的多维分析能力和查询性能。数据仓库的架构是设计和构建数据仓库时需要考虑的重要问题。通常，数据仓库的架构主要包括数据源、数据抽取（Extraction）、数据转换（Transformation）和数据加载（Loading）四个阶段。同时，数据仓库的架构还要考虑数据存储和索引、数据访问和查询工具、安全性等方面的问题。在数据仓库项目中，数据建模是关键环节之一。数据建模主要涉及到如何设计和组织数据仓库中的数据表、维度表、事实表以及它们之间的关系。传统的关系型数据建模方法（Relational Data Modeling）和维度化数据建模方法（Dimensional Data Modeling）是两种常用的方法，分别适用于不同的数据仓库场景。此外，数据仓库中经常会遇到的问题之一是“Late-Arriving Dimensions and Late-Arriving Facts”，即维度和指标数据出现延迟的情况。针对这种情况，需要设计相应的处理方法，保证数据的准确性和一致性。数据仓库与数据集市（Data Mart）是两个相似但又有区别的概念。数据仓库是整个企业的数据集成和管理平台，而数据集市则是针对特定业务或应用领域的数据仓库子集。数据集市通常具有更小的规模和更专注的业务目标。元数据（Metadata）是指描述数据的数据，它包括了数据的定义、属性、源头和关系等信息。元数据管理是数据仓库建设和维护过程中的重要任务，可以通过元数据管理工具来实现。最后，ODS（Operational Data Store）是一个支持日常业务操作的数据存储系统。ODS与数据仓库不同，它更加注重对实时数据的处理和分析，用于支持企业的实时决策和运营。综上所述，BI Question List.doc中的问题涵盖了数据仓库的核心概念、方法和技术。数据仓库作为BI的基础设施，对于企业的决策和业务分析具有重要的意义。通过深入理解和应用数据仓库的相关知识，可以提高企业的数据分析能力和决策水平。

资源详情

资源推荐

4. Data Warehouse Build Lifecycle

5. How to do data modeling in DWH project? (How to design a DW?)

According to Kimball’s DWH theory, there are four steps to design dimensional

model.

Step 1. Select the Business Process / Subject Area

Business Process is major operational activities or processes supported by a

source system, from which data can be collected for the analytic purposes of

the data warehouse. Such as raw materials purchasing, orders, shipments,

invoicing, inventory, and general ledger .

[Subject area in a data model is a layer where we describe the business

process using the entities and relationships. So each subject area will

capture a business process.]

Step 2. Declare the Grain

Choose the Grain of the central fact table.

e.g. If a fact record is each daily product sales total in each store: Grain is

sales by product by store by day

Step 3. Choose the Dimensions

Choose the dimensions that apply to each fact table row. Four sub steps

here:

Step 3a: Identify Primary Dimensions

Choose the primary dimensions from fact grain e.g. product, store, day (or

date)

Step 3b: Identify grain of dimension table

Ensure that each dimension table grain is NOT lower than the central fact

table grain: e.g. the store dimension should have one row for each store.

Each store may have departments but the store dimension’s row should

represent only the store and not the department.

Step 3c: Identify all dimensional Attributes

For each dimension choose only SINGLE VALUED attributes.

e.g. if Region is an attribute of the store dimension then it should have one

and only one value for each store.

Step 3d: Identify Dimension Hierarchies and attributes of levels in

Hierarchies

Step 4. Identify the Facts

Step 4a: Choose Facts

Choose each fact for the fact table making sure that the fact is relevant and

also has the same grain has the fact table

e.g. for SALES fact table, typical facts would be price, quantity sold and

sales amount as these are all dimensioned by product, by store, by day.

Step 4b: Connect Fact to dimension tables by surrogate keys

6. Relational Data Modeling Vs. Dimensional Data Modeling?

In relational models data is normalized to 1st 2nd or 3rd Normal form.

In dimensional models normally data is denormalized.

7. How to handle Late-Arriving Dimensions and Late-Arriving Facts?

Late-Arriving Dimensions: Late-arriving dimensions is a situation that dimension

records arrive after fact records. That means dimension record come late and fact

data come early.

There are two methods to handle late arriving dimensions:

1). Create one dummy record in dimensional table and join with fact table (Normally

we use –1 as dimension key for the dummy record). Once we receive this data the

dummy record in the Dim table will be updated, and then process the facts again.

2). Place the transactional data in staging table till we receive the dim records. Re

keying processed will be used to get the key from dim table

Re keying process: Generally in ETL process first we will load dim records and then

go for facts. At the time of loading fact records keying will be done with dim tables.

The unkeyed records will be kept in staging table and remaining data will be stored

in target table. Next day at the time of keying process the unkeyed data will also

used for keying is called as re-keying. Re-keying process will be continued till we

get the key from dimensional tables

Late-Arriving Facts: Late-arriving facts is a situation that you need to insert or

update the historical fact data.

Data Warehouse Design Tip: Use both EFFECTIVE_START_DATE and

EFFECTIVE_END_DATE to support the SCD Type 2 table.

ETL Design Tip: Lookup the warehouse surrogate key based on the nature key and

the specific date passed from the fact staging table.

E.g. Using a customer-purchase scenario , suppose we receive today

a purchase record that is several months old. If we have Type 2 SCDs, we just

need follow below 3 steps to handle this case:

Step 1. For each dimension, find the corresponding dimension record in effect at

the time of the purchase.

Step 2. Using the surrogate keys found in the each of the dimension records from

Step 1; replace the natural keys of the late-arriving fact record with the surrogate

keys.

Step 3. Insert the late-arriving fact record into the correct physical partition of the

database containing the other fact records from the time of the late-arriving

purchase.

剩余25页未读，继续阅读

王景远

粉丝: 31
资源: 12

数据仓库常见问题：架构、建模、元数据等（20～40字）

面试测评问题表.doc

BI 基础知识测试以及答案.doc

select bk.bookname,get_borrower_name(bk.bookid) as borrower_name from books bk join borrow_info bi on bk.bookid = bi.bookid where bk.in_out = true and bi.return_date is null and get_borrower_name(bk.bookid) = '张三';怎么修改可以使它返回多行数据

python 用随机方法模拟213Bi衰变为209Bi的级联过程。 画出反应链中各原子的数量随时间的变化过程。

powerbi = datetime.date(datetime.localnow())

bi = np.floor(np.arange(n) / batch_size).astype(np.int)

那请解释List.Generate的作用,并给出几个简单例子

You are given two arrays a and b,both of length n Your task is to count the number of pairs of integers (i,j) such that 1≤i<j≤n and ai⋅aj=bi+bj .

確定，如何使用css selector精确定位“确定”

bi = np.floor(np.arange(n) / batch_size).astype(np.int)是什么意思

powerbi安装教程

smartbi安装教程

power BI 下载

【图像融合】基于matlab深度学习医学图像融合【含Matlab源码 8038期】.md

【图像配准】基于matlab SIFT图像配准【含Matlab源码 1007期】.md

最新资源

python 用随机方法模拟213Bi衰变为209Bi的级联过程。画出反应链中各原子的数量随时间的变化过程。

確定
，如何使用css selector精确定位“确定”