仅聚合某些单元格的计算度量
我试图弄清楚如何创建一个计算度量，该度量仅生成事实表中唯一事实的计数。我的事实表基本上从历史角度存储事件。但我需要过滤掉多余事件的措施。
以销售为例（因为所有有关 OLAP 的材料在示例中总是使用销售）：
事实表存储销售事件。当第一次销售时，它有一个唯一的销售参考，它是事实表中的一列。但是，独特的销售可以修改（添加或退回商品）或完全取消。事实表将对销售的这些更改存储为不同的行。
如果我使用 SSAS 创建计数度量，我会得到所有销售事件的计数，这意味着每次更改都会对一次唯一销售进行多次计数（这在某些报告中是可取的）。不过，我还想要一种能够生成唯一销售计数而不是事件计数的度量，但不仅仅是基于计算唯一销售引用。如果用户按日期过滤，那么他们应该会看到该日期仍然存在的唯一销售（如果销售在该日期之前被取消，则根本不应该在计数中表示）。
我该如何执行此操作在 MDX/SSAS 中？似乎我需要从查询的子集中进行计数查询工作，该查询根据时间维度查找销售的最新更改。 在 SQL 中，它类似于：从 SalesFacts FACT1 WHERE 事件中选择 COUNT(*) <> “已取消”并且时间戳 = (SELECT MAX(时间戳) FROM SalesFact FACT2 WHERE FACT1.SalesRef=FACT2.SalesRef)
MDX 中是否可以有子查询或事件性能如何？
I'm trying to figure out how I can create a calculated measure that produces a count of only unique facts in my fact table. My fact table basically stores events from a historical perspective. But I need the measure to filter out redundant events.
Using sales as an example(Since all material around OLAP always uses sales in examples):
The fact table stores sales EVENTS. When a sale is first made it has a unique sales reference which is a column in the fact table. A unique sale however can be amended(Items added or returned) or completely canceled. The fact table stores these changes to a sale as different rows.
If I create a count measure using SSAS I get a count of all sales events which means an unique sale will be counted multiple times for every change made to it (Which in some reports is desirable). However I also want a measure that produces a count of unique sales rather than events but not just based on counting unique sales references. If the user filters by date then they should see unique sales that still exist on that date (If a sale was canceled by that date if should not be represented in the count at all).
How would I do this in MDX/SSAS? It seems like I need have a count query work from a subset from a query that finds the latest change to a sale based on the time dimension.
In SQL it would be something like:
SELECT COUNT(*) FROM SalesFacts FACT1 WHERE Event <> 'Cancelled' ANDTimestamp = (SELECT MAX(Timestamp) FROM SalesFact FACT2 WHERE FACT1.SalesRef=FACT2.SalesRef)
Is it possible or event performant to have subqueries in MDX?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
在 SSAS 中，创建一个基于唯一交易 ID（销售数量或订单号）的度量，然后在属性窗口中将该度量设置为“DistinctCount”聚合函数。
现在，它应该计算不同的订单号，无论它位于哪个维度切片下。
In SSAS, create a measure that is based on the unique transaction ID (The sales number, or order number) then make that measure a 'DistinctCount' aggregate function in the properties window.
Now it should count distinct order numbers, under whichever dimension slice it finds itself under.
发布的查询可能会重写如下：
The posted query might probably be rewritten like this:
一个简单的答案是在事实视图/dsv 查询中添加一个“销售计数”列，该列为“初始”事件提供 1，为该事件的所有后续修订提供 0，如果该事件被取消则提供 -1 。这种“日志记录”方法可以很好地处理增量事实表加载。
从长远来看，另一种方法可能更有用，那就是拥有一个“事件”维度：然后，您可以公开一个计算度量，该度量是该维度中非空成员在事实表中给定度量上的计数。然而，对于销售来说，这本质上是一个退化维度（基于事实表的维度）并且可能会变得非常大。这可能是不合适的。
有时要求可能更复杂。如果按时间切片，您是否需要知道当时发生的所有不同事件，即使它们后来被取消？这开始变得棘手：Chris Webb 的博客上最近发表了一篇文章，其中他谈到了一个（有点毛茸茸的）解决方案：
http://cwebbbi.wordpress.com/2011/01/22/solving-the-events -in-progress-problem-in-mdx-part-2role-playing-measure-groups/
An simple answer would be just to have a 'sales count' column in your fact view / dsv query that supplies a 1 for an 'initial' event, a zero for all subsiquent revisions to the event and a -1 if the event is cancelled. This 'journalling' approach plays nicely with incremental fact table loads.
Another approach, probably more useful in the long run, would be to have an Events dimension: you could then expose a calculated measure that was the count of the members in that dimension non-empty over a given measure in your fact table. However for sales this is essentially a degenerate dimension (a dimension based on a fact table) and might get very large. This may be inappropriate.
Sometimes the requirements may be more complicated. If you slice by time, do you need to know all the distinct events that existed then, even if they were later cancelled? That starts to get tricky: there's a recent post on Chris Webb's blog where he talks about one (slightly hairy) solution:
http://cwebbbi.wordpress.com/2011/01/22/solving-the-events-in-progress-problem-in-mdx-part-2role-playing-measure-groups/