MDX：计算前 5 个操作的平均操作时间和随时间的变化

发布于 2024-08-17 21:40:57 字数 1287 浏览 1 评论 0原文

我有一个“动作”立方体。维度是“时间”和“动作ID”，测量是“动作数量”和“总时间”以及计算出的测量“平均动作时间”。我正在尝试按平均时间计算前 5 个操作，并显示与前一天相比的变化。我可以在两个单独的查询中执行此操作：

SELECT {[Measures].[Avg Action Time]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1];

和：

WITH MEMBER [Measures].[Change] AS
([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]),
FORMAT_STRING = 'Percent'
SELECT [Measures].[Change] on COLUMNS,
NON EMPTY [Time].[2005].[1].children on ROWS
FROM [Actions];

但我不知道如何将它们合并到一个 MDX 查询中。我尝试过：

WITH MEMBER [Measures].[Change] AS
([Time].CurrentMember, [Action ID].CurrentMember, [Measures].[Avg Action Time]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Action ID].CurrentMember, [Measures].[Avg Action Time]),
FORMAT_STRING = 'Percent'
SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1];

但变化百分比总是无穷大，所以它显然没有计算出正确的东西。正确的查询是什么？

原文

I have an "Actions" cube. The dimensions are "time" and "action ID" and the measurements are "number of actions" and "total time" and a calculated measurement "average action time".
I am trying to calculate the top 5 actions by avg time, and the show the change from the previous day.
I can do this in two separate queries:

SELECT {[Measures].[Avg Action Time]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1];

and:

WITH MEMBER [Measures].[Change] AS
([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]),
FORMAT_STRING = 'Percent'
SELECT [Measures].[Change] on COLUMNS,
NON EMPTY [Time].[2005].[1].children on ROWS
FROM [Actions];

But I Can't figure out how to combine them into one MDX Query.
I tried:

WITH MEMBER [Measures].[Change] AS
([Time].CurrentMember, [Action ID].CurrentMember, [Measures].[Avg Action Time]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Action ID].CurrentMember, [Measures].[Avg Action Time]),
FORMAT_STRING = 'Percent'
SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1];

but change percentage is always infinity, so it clearly isn't calculating the right thing.
What is the correct query?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

千秋岁 2024-08-24 21:40:57

问题是我的事实表中没有足够的数据。
该查询适用于不同的时间范围。

WITH MEMBER [Measures].[Change] AS 
([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]),
FORMAT_STRING = 'Percent'
SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1].[2];

The problem was that I didn't have enough data in my fact table.
This query works for me for a different time range.

WITH MEMBER [Measures].[Change] AS 
([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]),
FORMAT_STRING = 'Percent'
SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1].[2];

回复收藏 0 原文

~没有更多了~