MDX动态时间的性能问题在多选要求中设置 [英] Performance issue with MDX Dynamic time set in multi-select requirement

问题描述

祝福所有人！我试图抓住这个旧线程
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/55981f4f-09b8-4958-bd8b-820f12d3d583/mdx-calculation-for -multiselect-in-excel？forum = sqlanalysisservices 因为它太接近我正在寻找的东西了！唉，没有人看着它。所以我在这里重新发布
我的问题。

Greetings all! I tried to tack on to this old thread https://social.msdn.microsoft.com/Forums/sqlserver/en-US/55981f4f-09b8-4958-bd8b-820f12d3d583/mdx-calculation-for-multiselect-in-excel?forum=sqlanalysisservices because it is sooo close to what I am looking for! Alas, nobody looked at it. So I am reposting my issue here.

我对MDX很新，并且已经用不同的方式摸索着以平行周期进行YTD计算的方式简单还允许最终用户在选择时间后切片和切块。 虽然我发现了一个漂亮的
使用带有Now（）函数的动态命名集的例子，但它仅适用于YTD，并且当我应用它时所有切片器都显示为灰色。

I am fairly new to MDX and have been fumbling around with different ways to get a YTD calculation with parallel period in as simple fashion that also allows the end user to slice and dice after the time selection is made.  Although I found a nifty example of using a Dynamic Named Set with the Now() function it only works for just YTD and all the slicers are grayed out when I apply it.

我想要的是能够选择年度季度 - 月 - 日的日历层次结构中的任何时间范围，包括跳过几个月或几天，最后得到一个 年初至今，年初至今和之前的美元和百分比。 为了在我对MDX的有限
知识中实现这一点，我修改了另一个示例，使用并行周期遍历到Day级别，看起来像这样：

What I want is to be able to select any time range in a calendar hierarchy for Year-Quarter-Month-Day, including skipping months or days and end up with a  YTD, Prior YTD and Year over Prior in dollars and percent.  To achieve that in my limited knowledge of MDX I modified another example to traverse down to the Day level using parallel period and looks like this:

CREATE
DYNAMIC SET CurrentCube.[Days] as
(existing unorder([Date].[Calendar].[Date]));
 
CREATE
MEMBER CURRENTCUBE.[Measures].[PY$]
AS
Aggregate
(
Generate
(
(
existing nonempty(unorder([Days]))),
{
parallelperiod([Date].[Calendar].[Year],1,[Date].[Calendar].currentmember)}
)
, [Measures].[Sales]
),
FORMAT_STRING = "$#,##0.00;($#,##0.00)", 
NON_EMPTY_BEHAVIOR = { [Sales] }, 
VISIBLE = 1 ,   ASSOCIATED_MEASURE_GROUP= 'Sales';   

然而，在访问此多维数据集的Excel数据透视表中进行的每次调整都增加了10秒！ 它曾经几乎是在此之前的瞬间，当不使用该属性时，它又回到了即时状态。 然后我发现这个例子提到
以上处理动态YTD的多选问题并将其添加到我的计算中。 当我尝试它时，只有4到5秒的延迟，这是更好的！ 但是，我一直在试图修改它以获得先前的
年初至今并且无法让它工作！ 有人可以帮助这个新手MDX'er如何做到这一点？ 她是以前作为YTD的帖子中的代码：

However it added 10 seconds to every adjustment made in the Excel pivot table accessing this cube!  It used to be almost instant before that and when that attribute is not used it goes back to being instant again.  Then I found the example mention above that handled the multi-select issue for a Dynamic YTD and added it to my calculations.  When I tried it, it was only a 4 to 5 second lag which is so much better!  However, I have been fumbling around with trying to modify it for a Prior YTD and can't get it to work!  Can somebody help this neophyte MDX'er with how to do that?  Her is the code from the pervious post that works as a YTD:

-- Dynamic set for selected dates:

Create dynamic set Currentcube.[SelectedDates] as
 [Date].[Calendar].[Date].Members;

-- YTD Sales based on selected dates:

Create Member Currentcube.[Measures].[YTDSales] as
 Aggregate(YTD(Tail([SelectedDates]).item(0)),
 [Measures].[Sales Amount]);

 

提前感谢您提供任何帮助和时间，并感谢Deepak Puri的原始帖子。

 

Thank you in advance for any help and time you have to offer and thanks to Deepak Puri for the original post as well.

零售服务开发商

推荐答案

嗨  Cory Bonallo，

Hi  Cory Bonallo,

您可以尝试使用以下计算的度量并使用日期层次结构作为切片器来查看它是否有效。

You could try to use below calculated measure and use date hierarchy as slicer to see whether it works or not.

这是YTD

sum( PeriodsToDate(  [Date].[Calendar Date].[Calendar Year]
            ,   
             [Date].[Calendar Date].CurrentMember
        ) ,[Measures].[Internet Sales Count]
		 )

这是PREVIOS YTD

This is PREVIOS YTD

SUM(
            PeriodsToDate([Date].[Calendar Date].[Calendar Year],
                ParallelPeriod(
                    [Date].[Calendar Date].[Calendar Year], 
                    1, 
                    [Date].[Calendar Date].CurrentMember)                
),
[Measures].[Internet Sales Count]
            )

和日历日期 是我的日期层次结构与年 - 季 - 月 - 日期，您可以在切片机中使用它来查看它是否有效。

And Calendar Date   is my date hierarchy with Year-Quarter-Month-Date, you could use this in Slicer to see whether it works or not.

最好的问候，

Zoe Zhi

这篇关于MDX动态时间的性能问题在多选要求中设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！