SSAS-> MDX->如何创建一个按日期期间（即周）返回 topcount 的查询？

发布于 2024-12-28 12:35:24 字数 701 浏览 3 评论 0原文

我最近收到一个请求，要求创建一个查询，以按每周收入返回前 100 位客户。显然，我可以轻松地返回前 100 名客户作为一个整体，但当我添加周属性时，我的查询超时。我只是想知道是否有人遇到过这个问题或者是否能够生成类似的查询？

我的查询无需一周即可工作。：

     SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS  ) }, 100, [Measures].[Revenue])  ON ROWS 
FROM [DW]

我对周的查询完全超时了。：

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS * [Date Link].[Week].[Week].ALLMEMBERS ) }, 100, [Measures].[Revenue])  ON ROWS 
FROM [DW]

原文

I have received a recent request to create a query that returns the top 100 customers by revenue per week. I can obviously and easily return the top 100 customers as a whole but when I add in the week attribute, my query times out. I was just wondering if some out there has run into this or has been able to produce a comparable query?

My query that works without week present. :

     SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS  ) }, 100, [Measures].[Revenue])  ON ROWS 
FROM [DW]

My query with week present that just times out everything completely. :

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS * [Date Link].[Week].[Week].ALLMEMBERS ) }, 100, [Measures].[Revenue])  ON ROWS 
FROM [DW]

分享到QQ

分享到微博

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

发布评论

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

月牙弯弯 2025-01-04 12:35:24

这不会为您提供前 100 名客户以及他们排名最高的一周吗？因此，客户 A 可能在第一周排名第一，在第二周排名第五，依此类推，但您总共会获得 100 行而不是每周 100 行？

尝试类似的方法，

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY 
{
[Date Link].[Week].[Week].ALLMEMBERS *
{
TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS ) }, 100, [Measures].[Revenue])  
}
}
ON ROWS 
FROM [DW]

我认为您可能需要对周成员使用 GENERATE 而不是 *，不记得了，并尝试在我的脑海中执行此操作；）

Wouldn't this give you the top 100 customers and the week they were top in? So customer A could be in 1st position in week one, 5th position in week two, and so on, but you'd get 100 rows in total not 100 per week?

Try something like

SELECT NON EMPTY { [Measures].[Revenue] } ON COLUMNS, 
NON EMPTY 
{
[Date Link].[Week].[Week].ALLMEMBERS *
{
TopCount ( { ([Customer].[Customer Id].[Customer Id].ALLMEMBERS * [Customer].[Name].[Name].ALLMEMBERS ) }, 100, [Measures].[Revenue])  
}
}
ON ROWS 
FROM [DW]

I think you may need to use GENERATE instead of * with the week members, can't remember and trying to do this off the top of my head ;)

回复收藏 0 原文

~没有更多了~