问题描述
我有2个事实表，每个事实表都有一个度量值组，即生产和生产订单.生产具有较低粒度(在组件级别)的生产信息，生产订单具有较高级别(具有表头数量的订单级别等)的信息.
I have 2 fact tables with a measure group each, Production and Production Orders. Production has production information at a lower granularity (at the component level) productionorders has information at a higher level (order level with header quantities etc.).
我在productionorderid的两个表之间创建了代理键链接.一旦我将Prod ID(来自productiondetailsdim)添加到数据透视表中，它就会计算出实际数量(来自产品订单度量值组)，并且我无法合并两个度量值组中的数量.
I have created a surrogate key link between the two tables on productionorderid. As soon as I add Prod ID (from productiondetailsdim) to the pivot table it blats out the actual qty (from prod order measure group) and I cannot combine the qty's from the two measure groups.
如何设计两者之间的正确关系?请参阅我的暗淡使用图. Production Details是链接两个事实表的暗淡部分，目前DimProductionDetails与Production具有事实关系.我不确定与生产订单之间应该有什么关系(目前有很多关系).
How can I design the correct relationship between the two? Please see my dim usage diagram. Production Details is the dim that links the two fact tables, at the moment DimProductionDetails is in a fact relationship with Production. I'm not sure what the relationship should be with Production Order (it is currently many to many).
请查看两个表之间的示例数据:
Please see example data between the two tables:
我必须能够复制此行为:
I have to be able to duplicate this behaviour:
推荐答案
是否要让生产订单度量值组的全部实际数量在每个产品旁边重复?如果是这样，则多对多关系是正确的.我怀疑一旦我解释了多对多工作原理后，您就会发现问题所在.
Do you want the full actual qty from prod order measure group to repeat next to each product? If so a many-to-many relationship is right. I suspect once I explain how that many-to-many works you will spot the problem.
当您从生产详细信息"维度中按产品划分生产订单度量值组的全部实际数量时，它将在公共维度上的两个度量值组之间进行运行时联接.因此，例如，如果订单245295的日期为2015年1月1日，而订单245295的生产详细信息的日期为1/8/2015，则运行时联接将丢失该订单的行，而实际数量将显示为空.因此，比较两个度量值组上使用的所有维度，并确保相同顺序的所有行的那些通用维度具有相同的维度键.例如，如果日期不同，则在DSV中创建一个命名查询，该查询仅从生产事实表中选择与订单事实表匹配的维列.然后从该命名查询创建一个新的度量值组，并将该新度量值组用作多对多维度中的中间度量值组. (维度使用情况"标签中的当前多对多"单元应该说出新度量值组的名称，而不是现有的生产度量值组.)
When you slice full actual qty from prod order measure group by product from the Production Details dimension it does a runtime join between the two measure groups on the common dimensions. So for example, if for if order 245295 has a date of 1/1/2015 while the production details for order 245295 have dates of 1/8/2015 then the runtime join will lose rows for that order and actual qty will show as null. So compare all the dimensions used on both measure groups and ensure all rows for the same order have the same dimension keys for those common dimensions. If for example dates differ then create a named query in the DSV that selects just the dimension columns from the production fact table which match the order fact table. Then create a new measure group off that named query and use the new measure group as the intermediate measure group in your many to many dimension. (The current many to many cell in the dimension usage tab should say the name of the new measure group not the existing Production measure group.)
如果您希望实际的数量度量仅在订单级别显示，而在产品级别为空，则尝试以下操作.将多对多关系更改为常规关系，然后在对话框中选择事实表如何连接到维度，将维度属性更改为ProductionOrder_SK(这不是维度的键)，然后在事实表.然后，在生产订单度量值组上单击鼠标左键，然后转到属性"窗口，并将IgnoreUnrelatedRelationships设置为false.这样，按工作中心或按生产明细"维度中位于谷物下方的属性对实际数量进行切片将显示为空.
if you want the actual qty measure to only show when you are at the order level and be null at the product level then try the following. Change the many-to-many relationship to a regular relationship and in the dialog where you choose how the fact table joins to the dimension change the dimension attribute to ProductionOrder_SK (which is not the key of the dimension) and choose the corresponding column in the fact table. Then left click on the Production Order measure group and go to the Properties window and set IgnoreUnrelatedRelationships to false. That way slicing actual qty by work center or by an attribute that is below grain in the Production Details dimension will show as null.
这篇关于SSAS-关系/粒度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！