正确聚合 SSAS 中一对多关系的值
所以我有一个货件的维度数据库：
一次货件包含许多包裹。 一个包裹包含很多费用。
因为这是有维的，而且费用是我最感兴趣的数据，所以我已将 3 个表及其多对一关系展平为一个费用事实表。这是单次发货的快速快照，其中包含两个包裹，每个包裹重 1 磅。
为了制作包裹重量可查询我需要将其推至电荷颗粒。这会导致聚合问题。每个包裹的包裹重量为 1 磅，因此货件的重量应为 2 磅。我找不到以这种方式聚合重量作为衡量标准的方法。我对 SSAS 和 OLAP 非常陌生，并且真的不知道接下来要尝试什么。任何建议将不胜感激。
So I have a dimensional database for shipments:
A shipment contains many packages.
A package contains many charges.
Because this is dimensional and the charges are the data I'm most interested in, I've flattened the 3 tables and their many to one relationships into a single charges fact table. Here's a quick snapshot of a single shipment that contains two packages each weighing 1 lb.
In order to make package weight queryable I've needed to push it down to the charge grain. This causes problems with aggregation. A package weight of 1 lb applies to each package, and should result in 2 lbs for the shipment. I can't find a way to aggregate the weight as a measure in this fashion. I'm very new to SSAS and OLAP and don't really know what next to try. Any suggestions would be greatly appreciated.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
问题在于数据建模的方式。 （事实）表的最低粒度级别是“费用金额”...“包裹重量”金额处于较高的粒度级别（包裹级别）。
您有两种选择...
在您的 ETL 中，您可以为每个包裹的不同费用金额分配 1 磅（请参见下面的屏幕截图）。这样，当您按“发货”或“包裹”对该事实表进行切片时，您将分别看到 2 磅或 1 磅。
从该事实表中提取“包裹重量”字段，并将其放入粒度为 的事实表中包级别。
The problem is the way you have the data modeled. The lowest level of granularity of your (fact) table is "charge amount"..."package weight" amount is at a higher level of granularity (the Package Level).
You have two options...
In your ETL, you can allocate the 1 lbs across the different charge amounts for each package (see screenshot below). That way when you slice this fact table by Shipment or Package, you will see 2lbs or 1lbs, respectively.
Pull the "package weight" field out of this fact table and put it in one where the granularity is at the package level.