您如何设计OLAP数据库? [英] How do you design an OLAP Database?

问题描述

我需要一个思维过程来设计OLAP数据库...

I need a mental process to design an OLAP database...

本质上对于标准关系而言(宽松):

Essentially for standard relational it'd be (loosely):

Identify Entities
Identify Relationships
Identify Properties of Entities

对于每个属性:

Ensure property can be related to only one entity
Ensure property is directly related to entity

对于OLAP数据库，我了解术语，动机和结构；但是，我不知道如何将我的关系模型分解为OLAP模型.

For OLAP databases, I understand the terminology, the motivation and the structure; however, I have no clue as to how to decompose my relational model into an OLAP model.

推荐答案

确定尺寸(或依据) 这些都是您可能要分析/分组报告的依据.源数据库中的每个表都是一个潜在的维.如果可能的话，维度应该是分层的，例如您的日期维度应具有年，月，日层次结构，类似的位置应具有例如国家，地区，城市层次结构.这将使您的OLAP工具可以更有效地计算聚合.

Identify Dimensions (or By's) These are anything that you may want to analyse/group your report by. Every table in the source database is a potential Dimension. Dimensions should be hierarchical if possible, e.g. your Date dimension should have a year,month,day hierarchy, Similarly Location should have for example Country, Region, City hierarchy. This will allow your OLAP tool to more efficiently calculate aggregations.

确定措施 这些是客户希望查看的KPI或实际数字信息，通常可以汇总这些信息，因此，源数据库中的任何非标志，非关键数字字段都是一种可能的度量.

Identify Measures These are the KPI's or the actual numerical information your client wants to see, these are usually capable of being aggregated, therefore any non flag, non key numeric field in the source database is a potential measure.

以星型模式排列，在事实"表的中央带有度量"，并与适用的维"表建立FK关系.度量应存储在最低维度层次结构级别.

Arrange in star schema, with Measures in the center 'Fact' table, and FK relations to applicable Dimension tables. Measures should be stored at the lowest dimension hierarchy level.

标识事实表的谷物"，这实际上是持有的详细程度".它通常由报告要求，报告来源中可用的数据粒度和报告解决方案的性能要求确定.您可以随时进行识别，也可以在确定所有重要数据后将其作为最后一步.我倾向于最后一步，以确保我的事实表之间的纹理保持一致.

Identify the 'Grain' of the fact table, this is essentially the 'level of detail' held. It is usually determined by the reporting requirements, the data granularity available in the source and performance requirements of the reporting solution.You may identify the grain as you go, or you may approach it as a final step once all the important data has been identified. I tend to have a final step to ensure the grain is consistent between my fact tables.

最后一步是确定尺寸不断变化的尺寸以及这些尺寸的要求.例如，如果客户维度包含其地址的一个元素并且他们移动，那么该如何处理.

The final step is identifying slowly changing dimensions, and the requirements for these. For example if the customer dimension includes an element of their address and they move, how is that to be handled.

这篇关于您如何设计OLAP数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！