SSAS使用不规则层次结构在层次结构中的维度级别之间建立多对多关系

时间:2021-08-12 09:55:01

I've got a question about how to create one dimension in SSAS that contains a hierarchy built from two dimensions.

我有一个关于如何在SSAS中创建一个维度的问题,该维度包含从两个维度构建的层次结构。

I have a fact-table: sales

我有一个事实表:销售

and a many-to-many relation between two dimensions: customers bridgecustomersTerritory territory

以及两个维度之间的多对多关系:客户bridgecustomersTrritory领土

And the Territorydimension is at the moment built as a ragged hierarchy containing a ID and a parentID.

Territorydimension目前是一个包含ID和父ID的不规则层次结构。

This works fine if i use the Territory dimension and Customer dimension apart from eachother as dimensions.

如果我使用区域维度和客户维度彼此分开作为维度,这可以正常工作。

But i would like to add the customer at the bottom of the territory hierarchy Is this even possible with the setup that i've got right now?

但是我想在领土层次结构的底部添加客户这是否可以使用我现在拥有的设置?

If i change the Territory to a "normal" hierarchy with a solid number of levels, would that help?

如果我将Territory更改为具有固定数量级别的“正常”层次结构,这会有帮助吗?

//Kriss

// KRISS

1 个解决方案

#1


0  

You cannot have attributes from two dimensions in one hierarchy. And as you use a many-to-many relationship between customer and territory, I assume you need it as a customer can be located in several territories, and there is obviously more than one customer per territory.

您不能在一个层次结构中拥有来自两个维度的属性。当您在客户和地区之间使用多对多关系时,我认为您需要它,因为客户可以位于多个地区,并且每个地区显然有多个客户。

But why do you want to have customers and territory in one hierarchy? You can have them in separate hierarchies, and still use them in one query. In MDX, and hence in most client tools accessing Analysis Services that I know, you can drag more than one hierarchy to the rows or columns.

但是,为什么要在一个层次结构中拥有客户和区域?您可以将它们放在单独的层次结构中,并仍在一个查询中使用它们。在MDX中,因此在大多数访问我所知道的Analysis Services的客户端工具中,您可以将多个层次结构拖到行或列中。

UPDATE To some extent, I can understand the requirement to combine things. In a client tool that I developed, we do not expose the dimensions to the user at all, we are just using the display folders of the attributes/hierarchies. In this way, we can show attributes/hierarchies close together even if they technically need to be in different dimensions due to the modeling requirements. But they are still separate attributes/hierarchies, and users work well with this.

更新在某种程度上,我可以理解组合事物的要求。在我开发的客户端工具中,我们根本不向用户公开维度,我们只是使用属性/层次结构的显示文件夹。通过这种方式,我们可以将属性/层次结构显示在一起,即使它们在技术上需要由于建模要求而处于不同的维度。但它们仍然是单独的属性/层次结构,用户可以很好地使用它。

Yes, if you would want one hierarchy, you would need to integrate the customers into your territory dimension, taking care that you break down a customers measures like sales correctly between the different territories. And if you can do that easily, maybe there is no need for a many-to-man relationship at all.

是的,如果您需要一个层次结构,则需要将客户集成到您的区域维度中,注意您在不同地区之间正确分解客户的销售措施。如果你能够轻松地做到这一点,也许根本不需要多人关系。

#1


0  

You cannot have attributes from two dimensions in one hierarchy. And as you use a many-to-many relationship between customer and territory, I assume you need it as a customer can be located in several territories, and there is obviously more than one customer per territory.

您不能在一个层次结构中拥有来自两个维度的属性。当您在客户和地区之间使用多对多关系时,我认为您需要它,因为客户可以位于多个地区,并且每个地区显然有多个客户。

But why do you want to have customers and territory in one hierarchy? You can have them in separate hierarchies, and still use them in one query. In MDX, and hence in most client tools accessing Analysis Services that I know, you can drag more than one hierarchy to the rows or columns.

但是,为什么要在一个层次结构中拥有客户和区域?您可以将它们放在单独的层次结构中,并仍在一个查询中使用它们。在MDX中,因此在大多数访问我所知道的Analysis Services的客户端工具中,您可以将多个层次结构拖到行或列中。

UPDATE To some extent, I can understand the requirement to combine things. In a client tool that I developed, we do not expose the dimensions to the user at all, we are just using the display folders of the attributes/hierarchies. In this way, we can show attributes/hierarchies close together even if they technically need to be in different dimensions due to the modeling requirements. But they are still separate attributes/hierarchies, and users work well with this.

更新在某种程度上,我可以理解组合事物的要求。在我开发的客户端工具中,我们根本不向用户公开维度,我们只是使用属性/层次结构的显示文件夹。通过这种方式,我们可以将属性/层次结构显示在一起,即使它们在技术上需要由于建模要求而处于不同的维度。但它们仍然是单独的属性/层次结构,用户可以很好地使用它。

Yes, if you would want one hierarchy, you would need to integrate the customers into your territory dimension, taking care that you break down a customers measures like sales correctly between the different territories. And if you can do that easily, maybe there is no need for a many-to-man relationship at all.

是的,如果您需要一个层次结构,则需要将客户集成到您的区域维度中,注意您在不同地区之间正确分解客户的销售措施。如果你能够轻松地做到这一点,也许根本不需要多人关系。