发表时间: 2007年5月1日 0:20:50 发贴人:www2002
请教MDX 难题
维度：
时间（year，month，date）
订单（ordernumber）
产品（productid,productname)
事实表：
（ordernumber,date,productid,amount(实收款)）
在事实表中，同一订单号可以有多条记录，其amount值可以是负值,表示调整或取消.
求在某一时间段内,各产品的有效订单数和实收款合计. 其中有效订单为该时间段内
实收款合计>0的订单.
例:
在事实表中有如下记录:
ordernumber date productid amount
1 2007-01-01 p1 50
1 2007-01-05 p1 -50
2 2007-01-01 p2 100
2 2007-02-02 p2 200
1 2007-02-02 p1 100
显示时间段2007-01-01至2007-02-02的计算结果应为:
产品 有效订单数 实收合计
p1 1 100
p2 2 300
想象的算法如下.目前在事实表和订单表中,各有60万条记录,运行时间6分钟,客户不能接受,且结果不对.
敬请指教,万分感谢.
With Member [Measures].[有效订单数]
as
distinctcount(filter([订单].[ordernumber].members,
sum(([订单].[ordernumber].currentmember,
[Measures].[amount])>0))
select [Measures].[有效订单数],[Measures].[amount] on 0
[订单].[productid].members on 1
from [Cube1]
where ([时间].[date].&[2007-01-01]:[时间].[date].&[2007-02-02])
发表时间: 2007年5月1日 7:40:19 发贴人:suntt
感觉你说的与你给出的示例并不符合:
With Member [Measures].[有效订单数]
as
distinctcount(filter([订单].[ordernumber].members,
sum(([订单].[ordernumber].currentmember,
[Measures].[amount])>0))
[Measures].[amount])>0 条件有必要吗?
应该是:[Measures].[amount]
发表时间: 2007年5月2日 7:15:44 发贴人:www2002
谢谢回复。
我的思路是这样的：
sum(([订单].[ordernumber].currentmember,[Measures].[amount]) 是计算当前订单的amount的合计，其结果用来判断该订单是否为有效订单。结果>0的为有效订单。
filter([订单].[ordernumber].members,sum(([订单].[ordernumber].currentmember,
[Measures].[amount])>0 ）用来过滤出有效订单。
distinctcount() 计算有效订单的个数。
注意，不是[Measures].[amount]>0 而是
sum(([订单].[ordernumber].currentmember,[Measures].[amount])>0
不知这个思路对不对，请指教。
现在这个查询的结果是正确的，只是效率太低。应该有更好的方法。
发表时间: 2007年5月2日 8:38:47 发贴人:www2002
抱歉，上面的查询打错了，在1轴的维度应该是“产品”。改正如下：
With Member [Measures].[有效订单数]
as
distinctcount(filter([订单].[ordernumber].members,
sum(([订单].[ordernumber].currentmember,
[Measures].[amount])>0))
select [Measures].[有效订单数],[Measures].[amount] on 0
[产品].[productid].members on 1
from [Cube1]
where ([时间].[date].&[2007-01-01]:[时间].[date].&[2007-02-02])
另外，为何不允许修改已发的帖子呢？犯了错误应该允许改正嘛，改了还是好好同志嘛.
发表时间: 2007年5月2日 15:02:53 发贴人:The
客户真的需要在任意时间段上计算这个量吗?
将要发生的事情始终远比已经发生的事情重要.
生活是一种体验和责任.
发表时间: 2007年5月2日 15:03:57 发贴人:The
另外,
你看看是不是打上了SP2.
貌似SP2在不少这种累加计算上提高不少性能....
据说在某种情况下提高了几十倍
你可以试试
将要发生的事情始终远比已经发生的事情重要.
生活是一种体验和责任.
发表时间: 2007年5月2日 15:06:34 发贴人:The
如果:按照客户的要求,
只需要在每个月或者其他很有规律的时间段上计算这个量的话,
可以考虑把这个量做成非动态的.
将要发生的事情始终远比已经发生的事情重要.
生活是一种体验和责任.
发表时间: 2007年5月2日 21:47:22 发贴人:suntt
可以考虑把这个量做成非动态的.
同意The斑竹看法,大量的动态的,速度肯定要慢很多
发表时间: 2007年5月3日 2:15:50 发贴人:www2002
可是用T-SQL直接在数据库中查询, 运行时间不到0.2秒即获得相同结果. 难道olap 或MDX效率差这么多? 感觉不应该呀.
是不是有更好的方法处理这类问题.
发表时间: 2007年5月3日 7:26:31 发贴人:www2002
真抱歉, 发现还有打字错误:
sum(([订单].[ordernumber].currentmember,[Measures].[amount])>0
应该为
sum([订单].[ordernumber].currentmember,[Measures].[amount])>0
现在问题已初步解决，把
distinctcount(filter([订单].[ordernumber].members,
sum([订单].[ordernumber].currentmember,[Measures].[amount])>0))
改为
count(filter([订单].[ordernumber].members,[Measures].[amount]>0))
查询结果相同 但查询时间缩短至5秒，仍在测试中...
由于对cube的结构和查询机制缺少理性认识， 还不能理解为什么
sum([订单].[ordernumber].currentmember,[Measures].[amount])>0
和
[Measures].[amount]>0
在我的查询中作用相同, 请两位仁兄赐教.
发表时间: 2007年5月3日 7:37:42 发贴人:www2002
另外，虽然查询时间已降至5秒，仍比T-SQL查询慢100多倍，困惑。
发表时间: 2007年5月3日 20:01:14 发贴人:The
www2002 wrote:
真抱歉, 发现还有打字错误:
sum(([订单].[ordernumber].currentmember,[Measures].[amount])>0
应该为
sum([订单].[ordernumber].currentmember,[Measures].[amount])>0
现在问题已初步解决，把
distinctcount(filter([订单].[ordernumber].members,
sum([订单].[ordernumber].currentmember,[Measures].[amount])>0))
改为
count(filter([订单].[ordernumber].members,[Measures].[amount]>0))
查询结果相同 但查询时间缩短至5秒，仍在测试中...
由于对cube的结构和查询机制缺少理性认识， 还不能理解为什么
sum([订单].[ordernumber].currentmember,[Measures].[amount])>0
和
[Measures].[amount]>0
在我的查询中作用相同, 请两位仁兄赐教.
sum([订单].[ordernumber].currentmember,[Measures].[amount])>0
和
[Measures].[amount]>0
结果一样,
个人认为有2个原因:
1.distinctcount-->count
2.sum被去掉了.
将要发生的事情始终远比已经发生的事情重要.
生活是一种体验和责任.
发表时间: 2007年5月3日 20:13:01 发贴人:The
关于distinctcount的一些资料:
(1):
processing of distinct count partitions is quite different from processing of regular partitions. Same goes for querying. Records stored in distinct count partitions as stored sorted. You can even see that for processing such partition Analysis Server will send different SQL query, it will ask data from SQL Server to come in sorted order.
摘自:
https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1220445&;SiteID=1;
(2):
To better understand how distinct count impacts aggregation size, consider the following example. You have a measure group partition with an aggregation that summarizes sales amount by product category and year. You have ten product categories with sales for ten years, producing a total of 100 values in the aggregation. When you add a distinct count of customers to the measure group, the aggregation for the partition changes to include the customer key of each customer who has sales for a specific product category and year. If there are 1,000 customers, the number of potential values in the aggregation increases from 100 to 100,000 values, given that every customer has sales for every product category in every year. (Note that the actual number of values would be less than 100,000 due to natural data sparsity. At any rate, the value is likely to be a number much greater than 100.) While this additional level of detail is necessary to efficiently calculate the distinct count of customers, it introduces significant performance overhead when users request summaries of sales amount by product category and year.
摘自:SSAS2005PerfGuide.doc
请见:
http://www.windbi.com/showtopic.aspx?page=end&;topicid=104
(3):
下面是一个说明2005里distinccount较之2000增强的页面:
http://blogs.conchango.com/christianwade/archive/2005/04/03/Distinct-Count-in-Analysis-Services-2005.aspx另外:
改文下面的一个回复推荐阅读:
Additionally, you can also use Aggregate function, visual totals mode and subselects - they will all work fine with DistinctCount measures, as well as with many-to-many dimensions, semiadditive measures, measures expressions etc.
将要发生的事情始终远比已经发生的事情重要.
生活是一种体验和责任.
发表时间: 2007年5月3日 20:16:59 发贴人:The
去掉sum,结果一样,
个人认为原因是:
sum([订单].[ordernumber].currentmember,[Measures].[amount])
里面的[订单].[ordernumber].currentmember
本身就代表当前执行上下文中的[订单].[ordernumber]当前成员.
这种情况下,一般都省掉currentmember.
同时也避免引起AS引擎的额外开销.
将要发生的事情始终远比已经发生的事情重要.
生活是一种体验和责任.
发表时间: 2007年5月4日 11:31:37 发贴人:www2002
多谢The, 你讲的很正确. 我把查询成为
sum([订单].[ordernumber].[ordernumber],iif([Measures].[amount]>0,1,0))
仅1秒就算出来了. 看来sum和iif 比count和filter更快些
发表时间: 2007年5月4日 17:07:35 发贴人:The
恭喜楼主解决问题
将要发生的事情始终远比已经发生的事情重要.
生活是一种体验和责任.
发表时间: 2007年5月5日 2:54:12 发贴人:www2002
多谢仁兄们的帮助
发表时间: 2007年5月8日 14:56:38 发贴人:流水
呵呵,都是牛人哈,这么快就解决问题了!
在庞大多维数据量的统计查询中,mdx还是要比sql效率高的多的,前面主要是使用了distinctcount大大影响了效率.
读万卷书,行万里路,做一件事.
发表时间: 2007年5月8日 15:30:21 发贴人:windbi
这个帖子,有些牵涉到内部机制了
有启发性
微软BI开拓者