Adatis BI Blogs

Dynamic Range, Time Property Filter = Empty Matrix - A bug?

I think I've found a bug in the way the Excel Add-In generates MDX under certain 'rolling' conditions.  The requirement I have is to be able to forecast at the day level for a rolling 6 months; starting from the current period (which is to be updated each week) running for a period of 180 days (~ 6 months) To prevent requiring 180 columns, a dimension property based filter must be available to select the month in which to forecast.  This will provide a more concise data entry form detailing up to 31 days of the selected month in which to add forecast values. My form is dimensioned up as follows: Dimension Position Employee Filter Time(Month) Filter (Dimension Property) Scenario Filter Location Rows Time (Day) Columns I set up the columns as a dynamic range to ensure that the forecast 'rolls' with changes in current period.  The range was set from current member id + 0 : current member id + 180.  [Current Period is set to 16th September 2008 - today). The simplified MDX that this produces is below: select { Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(0) : Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(-180) } * { [Measures].[Value] } on columns, { descendants([Location].[Standard].[All].[All Locations],,after) } on rows from ( select {[Time].[Month].[All].[September 2008]} on columns from [LocationPlan]) where {[Employee].[Employee].[All].[John Doe]} * {[Scenario].[All Members].[All].[Forecast]} The first element to notice is that the columns have been set to a range using ancestor at the member id level and lag to cover the 180 days: Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(0) : Ancestor([Time].[Base View].[MemberId].&[20080916], [Time].[Base View].[MemberId]).Lag(-180) The next point to highlight is the sub=query that represents the selected time dimension property value (September 2008): {[Time].[Month].[All].[September 2008]} on columns from [LocationPlan]) When you run this in SSMS, the following data set is returned: The Locations appear on the rows, the days appear on the columns - exactly as required. By changing the sub-query filter to October 2008 - the next month in the range, and definitely covered by the -180 day lag (Not sure why the Lead function isn't used here?) - results in a problem, the results returned now are missing the day level columns: The root of this problem is the column expression - if you replace the column expression with a direct lag on the current period member the expected results are returned: select { [Time].[Base View].[MemberId].&[20080916].Lag(0) : [Time].[Base View].[MemberId].&[20080916].Lag(-180) } * { [Measures].[Value] } on columns, { descendants([Location].[Standard].[All].[All Locations],,after) } on rows from ( select {[Time].[Month].[All].[September 2008]} on columns from [LocationPlan]) where {[Employee].[Employee].[All].[John Doe]} * {[Scenario].[All Members].[All].[Forecast]} Now, the only workaround I can come up with is to build the form using a custom MDX formula so I reckon this warrants raising a bug on connect - which I've logged here:

PerformancePoint Server Planning SP1 - Clear Changes After Workflow Action

There's a new workbook property that was introduced in PPS Service Pack 1.  The 'Clear Changes After Workflow Action' effectively splats the change list for the workbook once the assignment has been submitted (either draft or final). The property can only be reached through the Report Properties dialog, and is at the workbook level:                         This property defaults to false which, under certain circumstances can hinder performance.  Whenever you change data on a matrix, the slice that you affected is saved to a change list.  You can view what's on the change list by choosing 'View -> Show Current Changes' from the PPS Add-In for Excel. Here's an example change list; two budget accounts for the same time period and department have been updated to the included new values.         The default behaviour (and the behaviour prior to SP1) is that, for the life of the assignment, the change list is maintained for every cell that is updated  The change list is simply appended to, so you can imagine, on a large workbook with several matrices spanning several filter slices, the change list can become quite large. Submitting the assignment effectively submits the change list for processing by the server, first updating/inserting the appropriate records into the fact table and subsequently re-processing the Analysis Services partition.  It follows then, that the larger the change list, the slower the submission process. Before SP1, this forever growing change list issue was resolved with little user training.  As part of the submission process you would invite your users to manually clear the change list: By 'Clearing Current Changes' you throw away the changes to the cells and have to rely on the data being safe and sound on the server.  This process helped keep the change list to a more manageable size thus gaining on the submission performance. The new 'Clear Changes After Workflow Action' property in SP1, if set to true, will perform the 'Clear Current Changes' step for you automatically.  This helps keep the change list lightweight (providing of course, the user regularly submits).  However, as I have already implied, there is one issue to be wary of; with the property set to clear changes, if your submission fails, the change list is lost and there is a real danger of losing data.