SSAS一对一关系 [英] SSAS One to Many Dimensional Relationship

问题描述

大家对SSAS的疑问。我试图在OLAP SSAS多维数据集环境中定义一对多关系。但是，我在定义主键时遇到了问题。示例表如下。前三个表之间的关系很容易定义（TradeDate，NYMEX Trades& NYMEX Contract）。但是，我的目标是在NYMEX合同和模型合同之间建立一对多的关系。换句话说，来自1个NYMEX合约的数据将在Model Contract数据集中多次使用。

Question in SSAS for y'all. I am attempting to define a One to Many relationship in an OLAP SSAS cube environment. However, i'm running into problems with defining a primary key. Example tables are below. Relationships between the first 3 tables are easily defined (TradeDate, NYMEX Trades & NYMEX Contract). However, my goal is to create a one to many relationship between NYMEX Contract and Model Contract. In other words, the data from 1 NYMEX Contract will be utilized multiple times in the Model Contract dataset.

表：TradeDate

• TradeDate（PK ）


• 年份


• 月
  等...

表：NYMEX交易（无PK）

• ContractName


• TradeDate


• 价格

表：NYMEX合约

• 合约名称（PK）

表：模型合同

• ModelContractName（PK）


• ContractName

我需要在NYMEX合约和模型合约之间建立一对多的关系...但是我的PK在错误的列中示范合同。有什么想法吗？

I need to create a one to many relationship between NYMEX Contract and Model Contract...however my PK is on the wrong column in Model Contract. Any ideas?

为了帮助澄清-我正在尝试定义尺寸->尺寸关系。表模型合同是一个查找表。

To help clarify - i am attempting to define a dimension --> dimension relationship. The Table "Model Contract" is a lookup table.

推荐答案

您所拥有的就是一个多对多关系的示例您的事实数据（交易）和维度数据（模型合同）之间。看看：尺寸关系。您的案例很少见，因为模型合同只能分配给一个合同名称，但仍然属于多对多案例。这是在维建模中使用桥表处理的，在SSAS中，它们称为中间事实表。像这样设置DSV后：

What you have is an example of a many-to-many relationship between you fact data (Trades) and your dimension data (Model Contract). have a look at this: dimension relationship . Your case is a rare case since model contracts can only be assigned to one Contract Name but it still falls under the many-to-many case. This is handled using a bridge-table in dimensional modeling and in SSAS they call it an intermidiate fact table. Once you have your DSV setup like this:

然后，您将在项目中创建尺寸，然后为ModelContract创建度量组以将其用作中间事实表。然后，您可以选择多对多关系来设置多维数据集的维度用法：

You would then create your dimensions in your project and then create a measure group for ModelContract to be able to use it as an intermediate fact table. You would then setup the dimension usage for the cube choosing a many-to-many relationship:

>

请注意，应始终对尺寸使用代理键。它们具有几个优点，例如事实表中的存储空间，尺寸变化缓慢，源系统去耦，...

As a side note, you should always use surrogate keys for your dimensions. They have several advantages such as storage space in you fact table, slowly changing dimensions, source system decoupling,...

这篇关于SSAS一对一关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！