THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

DateTool dimension: an alternative Time Intelligence implementation

Year-To-Date and Difference-Over-Previos-Year (or Year-Over-Year Growth) are among the most required features of any user. Some OLAP client (like ProClarity) offers features that try to solve this problem client-side, but I don’t like this approach given that you might have a server-side feature doing that (so you don’t discriminate Excel users).

One trivial approach is creating a calculated measure for each combination of measure and date-calculation. While this works, it results in a multiplication of the number of measures, making the cube hard to use. Analysis Services offers a feature called Time Intelligence Wizard that creates some calculated members on dedicated Date attributes. This works in MDX but has several limitations:

· It limits the measures on which the calculation are applied (each time you add a new measure, you need to update the wizard-generated MDX scripts)

· It only applies to selected hierarchies – it doesn’t work, for example, if you cross Month and Years on rows and columns.

· It doesn’t work well with Excel 2007 after you install Analysis Services 2005 SP2 – see my rants here.

While the first two issues can be solved by using the same architectural approach of Time Intelligence Wizard, simply writing a different MDX Scripts, solving the third issue (Excel 2007 compatibility) requires a different architecture. For the sake of Google/MSN Live desperate users, I’m going to describe “how to make calculated members working on non-measures dimension with Analysis Services 2005 SP2 and Excel 2007”. I hope that Microsoft will consider a similar approach on a future version of Time Intelligence Wizard…

You can download the solution I'm going to describe on SQLBI.EU web site, under DateTool Project.

The issue

Let’s start describing the Excel 2007 SP2 issue. I created a small subset of Adventure Works sample cube. Calendar Date Calculations is the wizard-generated attribute. With BIDS browser (built using OWC 11) you can put this dimension on the columns of a pivot table with Calendar hierarchy placed on rows, and you can select any set of members from the Calendar Date Calculations attribute. In this example I unchecked one of the members generated by Time Intelligence Wizard.

FIG01 - Calendar Date PivotTable OWC11

With Excel 2007 the same cube is usable only if you enable the “Show calculated members from OLAP server” on the PivotTable Options dialog box (it’s unchecked by default).

FIG02 - PivotTable options for Show Calculated Members

Now we can generate a PivotTable similar to the one generated with BIDS browser.

FIG03 - Excel 2007 PivotTable Time Intelligence Wizard

Unfortunately, we cannot select single members from the Calendar Date Calculations attribute. We only have an “all or nothing” option based on the PivotTable Options settings we’ve seen above. This is the resulting Excel 2007 PivotTable.

FIG04 - Excel 2007 calculated members cannot be unchecked

The solution

I defined a dedicated dimension for time-related calculated members. Each formula has its own “real” members, instead of calculated ones. This solves the Excel 2007 issue at the price to require a dimension process instead of a simple MDX deployment (changing MDX Scripts for new calculated members wouldn’t require cube reprocessing). Since I don’t want a cube space growth, I simply put the dimension in the cube without a relationship with any measure group.

FIG05 - Dimension Usage for DateTool dimension

This is the real tricky and most non-intuitive part of the game. The DateTool dimension is seen as a regular dimension by any client, but it can be changed and reprocessed without any need to reprocess any measure group (because it has no relationship!).

The DateTool dimension could contain a single attribute with all time-related calculated members. However, I prefer an approach that uses two sets of different calculated members, which are orthogonal and might be crossed together. For example, I would put Year-To-Date calculation in one attribute and Year-Over-Year Growth in another one, so that I can obtain the Year-Over-Year Growth of a Year-To-Date calculation without the need to create a dedicated calculated member. In other words, I use these two sets to separate aggregations from comparisons formulas.

In practice, I would need two independent dimensions, and I do that from a relational point of view. I define two views in a separate schema on my data source (I could also use a separate Data Source View with self-contained named queries, but I prefer using relational views as a general way to decouple relational schema from multidimensional one – reasons for this would fill a whole dedicated article).

CREATE SCHEMA DateTool
GO

CREATE VIEW [DateTool].[DateAggregation] AS
SELECT 0 AS ID_Aggregation, 'Regular' AS Aggregation
UNION ALL
SELECT 1 AS ID_Aggregation, 'Year To Date' AS Aggregation
UNION ALL
SELECT 2 AS ID_Aggregation, 'Last 12 Months' AS Aggregation
UNION ALL
SELECT 3 AS ID_Aggregation, 'Total Current Year' AS Aggregation
GO

CREATE VIEW [DateTool].[DateComparison] AS
SELECT 0 AS ID_Comparison, 'Regular' AS Comparison
UNION ALL
SELECT 1 AS ID_Comparison, 'Previous Year' AS Comparison
UNION ALL
SELECT 2 AS ID_Comparison, 'Diff. Over Previous Year' AS Comparison
UNION ALL
SELECT 3 AS ID_Comparison, 'Diff. % Over Previous Year' AS Comparison
GO

 

Since I want to build a single dimension with two attributes, I create another view to build a junk dimension with the desired cardinality.

CREATE VIEW [DateTool].[DateTool] AS
SELECT 
    a.ID_Aggregation, 
    s.ID_Comparison, 
    CAST( ID_Comparison AS VARCHAR ) + ' - ' + CAST( ID_Aggregation AS VARCHAR ) AS Description
FROM DateTool.DateAggregation a
CROSS JOIN DateTool.DateComparison s
GO

At this point I can import these views in the Data Source View. I specify Primary Keys and Relationships manually because they can’t be inferred by relational metadata (they don’t exist at that level).

FIG06 - DateTool Data Source View

The resulting DateTool dimension is very simple.

FIG07 - DateTool Data Dimension

At this point there is the necessary MDX Script that solves all DateTool members. This single part would require another post or two and I don’t have time to explain that now, but you can see the complete AdventureWorks based solution downloading it from SQLBI.EU.

The interesting part is the result provided by Excel 2007 with this solution. Here is an example of the Calendar hierarchy crossed with a selection of the calculated members available in the Aggregation attribute of the DataTool dimension.

FIG08 - Excel 2007 DataTool.Aggregation sample

With this dimension, there are no limitations like those involved by calculated members (see the previous example made using Time Intelligence Wizard). In the previous PivotTable there is a member unchecked, like you can see in the following picture.

FIG09 - Excel 2007 DataTool.Aggregation selection

Just using this technique you could translate each calculated measure generated by Time Intelligence Wizard into a “regular” dimension fully usable by Excel 2007 users. The presence of two independent attributes makes the user able to build more complex PivotTables like the following one: the highlighted column displays the Year Over Year Growth over the Year To Date value.

FIG10 - Excel 2007 DataTool.Aggregation cross YTD-grow

Undoubtedly, such a feature is comfortable more to advanced users that to inexperienced one, since often a rename of the resulting report is required to better describe query results.

Finally, one of the limitations of Time Intelligence Wizard is that generated formulas work on a limited part of the cube: only one time hierarchy and only a selected set of measures. With the MDX Scripts I used, there are no similar constraints, making the cube easier to navigate. In the following example, I put years on columns and months on rows, displaying the Year-To-Date value and the difference with the previous year for selected data.

FIG11 - Excel 2007 Year cross months

Unfortunately, with this approach we lose compatibility with other clients, like the Browser provided by BIDS and SQL Server Management Studio. A PivotTable like the previous one would not display any data: the different way Excel 2007 and OWC 11 manage dimensions metadata produces this discrepancy between results obtained by difference OLAP clients. I am not sure if this can be solved just changing MDX Scripts definition – I made many tests until now and I described the best compromise I obtained until now, but I’d like to get feedback about issues, possible improvements and eventually alternative architectural solutions.

If you have time to take a look at the solution, please send me your feedback and/or any other comments at marco.russo (at) sqlbi.eu or using the comments for this post.

Published Sunday, September 02, 2007 4:41 PM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

SQLBI - Marco Russo said:

I fixed an issue of the DateTool dimension I presented in a previous post . The DateTool was unusable

September 9, 2007 4:18 PM
 

SQLBI - Marco Russo said:

Today I discovered that DefaultMember might result in a member other thant the default dimension member.

October 1, 2007 5:35 PM
 

rptodd said:

So where's the MDX to go with this?  I'm not sure if this is our solution or not, so I don't really want to struggle through the MDX just to test it out.  I wish I could just bust it out, but I still find that stuff confusing.

Thanks Marco!

April 3, 2008 10:05 PM
 

Marco Russo (SQLBI) said:

You can download the demo project from http://www.sqlbi.eu/datetool.aspx

Marco

April 4, 2008 4:09 AM
 

Jason said:

Thanks Marco for the post!  These types of tutorials are exactly what the BI community needs.  

Jason

September 9, 2008 3:04 PM
 

SQLBI - Marco Russo said:

I was tempted to start this post by this sentence “Analysis Services 2008 is not a new full release of

December 8, 2008 10:28 AM
 

gene said:

Marco...great post.  saved me some time.

January 15, 2009 5:15 PM
 

Alberto Ferrari said:

The security model of SSAS prevents us to apply security limitations to calculated members. We can hide

February 5, 2009 5:18 AM
 

Bruce Lester said:

Marco, thanks for posting this solution.  Your documentation is excellent.  

Bruce Lester

February 26, 2009 2:36 PM
 

Luke said:

Marco...thank you very much for your post.  You did an excellent job explaining this behavior of AS and Excel.  It is also nice to have an example of code for something we are trying to accomplish with our data calculations.  One question.  In your overwrites of calculations using [Quarter of Year] and [Month of Year] you used StrToMember and Key properties to link the attribute hierarchies .  Is there a significant performance increase doing this instead of using exists() or is this just another way to do it?  Or am I missing something?

March 4, 2009 2:59 PM
 

Marco Russo (SQLBI) said:

Luke, I don't get your point. We overwrite calculations using StrToMember because we want to be able to use those attributes that are outside a natural hierarchy. I don't get how you would use EXISTS functions to do the same. If you have an alternative MDX Script to get the same result, please send it to me and I'll check it.

Thanks

Marco

March 4, 2009 4:24 PM
 

Luke said:

Marco,  Thank you for your response.  You have created an elegant solution and I'm trying to apply it to my cube.  I should a stated that I'm not working with the Adventure Works cube.  My cube has attribute relationships similar to the diagram here:

http://www.sqljunkies.com/WebLog/mosha/archive/2007/06/07/katmai_ctp_attribute_relationship_tab.aspx

Using the following code I'm able to get a specific [Month] at the intersection of [month of year] and [year]

exists([Date].[Month].[Month]

      ,([Date].[Month Of Year].currentmember,[Date].[Year].currentmember)).Item(0)

I was just wondering if using the Key properties was better from a performance perspective.  I'm still getting up to speed on MDX, so please excuse any oversight on my part.

Thanks... Luke

March 4, 2009 5:45 PM
 

Marco Russo (SQLBI) said:

Now I understand - it is interesting, it could be more efficient but I never tried to model it in this way.

I don't have time these days - if you make some performance test, please keep me updated.

Thank you

Marco

March 6, 2009 11:35 AM
 

Luke said:

Marco, Thank you again for your response.  Could not tell that much difference in performance.  Ended up using exists() because getting to a specific [date] just using key properties of [day of year] and [year] was challenging because our [date] has an integer key that includes month(YYYYMMDD).

Again, I really appreciate that you shared your knowledge and code here. Your technique to scope a set of calculated members using except() was especially informative. Thank you...

Luke Pargiter

March 6, 2009 4:09 PM
 

Sumeet said:

Hello Marco,

Many thanks for putting this up.  This is the best thing since sliced bread.  I work for wine.com and this really saved me.  I don't want to put my email address here but if you call me at 925-577-1924, I would love to send you a bottle of wine.  At any rate, there is some of the best creative work I have seen.  Well Done indeed.

Regards,

Sumeet

April 10, 2009 8:22 PM
 

Marco Russo said:

Hi Sumeet,

thank you for your appreciation :)

You can write me at marco.russo (at) sqlbi.com and you can give me any information you don't want to publish here.

Best,

Marco

April 11, 2009 5:01 AM
 

Simon L-Deslauriers said:

hi Marco,  thank you for the DateTool dimension.  I used it in all of my clients.  

However, one of them have a particulary complex set of calculated member.  

Long story short : I need to make the Aggregate member being calculated AFTER other Calc.  I do need to change the SOLVE_ORDER or the "physical" member of the aggregate dimension in order to have my calculation done correctly.  I am not able to do that.

any help?

thanks!

April 14, 2009 12:41 PM
 

Simon L-Deslauriers said:

sorry, error in last post : we should read "I do need to change the solve_order OF the "physical" members of the aggregate dimension (YTD, MTD and WTD) ....

April 14, 2009 12:42 PM
 

Marco Russo (SQLBI) said:

You could simply move the MDX Script of the involved calculation - they are executed in the order they've been written... Of course, when there are no inter-dependencies into the MDX Script... Doesn't it work for you?

April 14, 2009 4:57 PM
 

Mikkel said:

Does this work with office 2003 as well?

May 23, 2009 10:20 AM
 

Marco Russo (SQLBI) said:

It should work witk Excel 2003 (and OWC11) as well.

May 23, 2009 10:26 AM
 

Mikkel said:

That was fast! Thanks, then this is exatly what I was looking for.

May 23, 2009 10:39 AM
 

Michael said:

Hi,

First off, thanks for this magnificent tool Marco!

I have a little problem with aggregation abbreviations below the year level, i.e. quarter to date, month to date. How do I get it to roll up on the above levels also? If I make a quarter to date, it only rolls up to the quarter, but not the year?

Thanks in advance

Best Regards,

Michael

June 9, 2009 5:06 AM
 

Mark said:

HI Marco,

We are just getting started with SSAS 2008.  I upgraded the datetool solution and it works perfectly there.  Then I tried to incorporate the same technique in our cube and I ran into this error with the MDX:  Error 6 MdxScript(AGF1) (43, 36) The level '[All Periods]' object was not found in the cube when the string, [Date].[Month of Year].[All Periods], was parsed. 0 0

Everywhere All Periods is mentioned this error is thrown.  Any thoughts on where to find the root cause of this issue?

Thanks

mark

June 9, 2009 4:47 PM
 

Marco Russo (SQLBI) said:

To Michael: a "quarter to date" should be an aggregation at a quarter level, what do you mean when you want to roll-up to the year level?

To Mark: The [All Periods] member should be adapted to your cube - what is the member name at the [All] level of the dimension hierarchy/attribute? Use that name instead of "All Periods" in the MDX script.

June 9, 2009 5:02 PM
 

Mark said:

Marco, Nice!  That worked -- I wasn't understanding where the [All Periods] reference was, but I finally found it in the the Dimension Browser.  

I looked at another solution that also provided YTD % change, etc. but it involved a lot more code for the mdx and the components of it were still grayed out in Excel because it created the members of the shell dimension through MDX.  This is a really powerful tool, I can't say enough for how excited I am about it.

Excellent work!  I can't thank you enough.

Mark

June 10, 2009 12:32 PM
 

Marco Russo (SQLBI) said:

Mark, you're welcome!

June 10, 2009 12:37 PM
 

Ken said:

GREAT work here.. so glad you had the time to post such a well written example.  

I am attempting to use this in my cube.... a few questions if you have time:

1. When I add

   ( [DateTool].[Aggregation].[Month To Date] )

         = Aggregate(

                    { [DateTool].[Aggregation].DefaultMember } *

                      PeriodsToDate(

                                   [Dates].[Time].[MONTH NAME],

                                   [Dates].[Time].CurrentMember

                    ) );

below the year to date definition under calcs... when i try to process the database I get an error that:

Error 25 MdxScript(Sales Cube) (57, 5) The level '[Month To Date]' object was not found in the cube when the string, [DateTool].[Aggregation].[Month To Date], was parsed.

Does not make sense.. I am defining the object??

2:  Is there a way to build a True Month to date value.. meaning JUST show the current month up to whatever TODAY is...Same question for year to date.. I want to stop the calc at "today"

Again .. thanks for the great work... and your answers to the questions have been very helpful... hope you enjoyed the wine...

-Ken

June 10, 2009 6:15 PM
 

Marco Russo (SQLBI) said:

Ken,

1) you have to define the member 'Month to Date' in the SQL view DateTool.DateAggregation

2) more complex issue, because what do you want to do for the previous years? If you want the same timeframe, you should define a dedicated member with a calculation that uses the range starting from the Jan 1st (or the beginning of the period) to the current day, whatever the selection is (and what do you do if the end user deselect some months?)

June 10, 2009 6:20 PM
 

Ankit Mittal said:

Hi Marco,

I recently learned how to use SSAS 2005, in order to meet our clients requirements. In the same process, we created 300 measures in the AS cube and appx. 200 measures required "Year-Over-Year" analysis.

I used "Time Intelligence" feature of SSAS but we ran into performance issues, though report is quite bulky in nature but still "Year-Over-Year" analysis takes unacceptable amount of time.

Kindly let me know what you think about "Time Intelligence" feature of SSAS and provide me some other approach if possible.

Thanks

Ankit

June 16, 2009 1:11 AM
 

Marco Russo (SQLBI) said:

Ankit,

DateTool has been created just to workaround Time Intelligence limitations. It creates calculated members and it creates too many members - DateTool is cleaner in this way, even if MDX Scripts is smaller but more complex.

Time Intelligence wizard is good to start and in simple projects, but is not good in large projects, just my opinion, of course!

Marco

June 16, 2009 1:39 AM
 

Andrei Rjeousski said:

Hi Marco,

Just wondering if you ever worked/created a cube that used similar concept to DateTool for creating Math Calculations (in addition to TimeCalcs)?

I have cube like that working with great success on SSAS 2005 but am having some issues porting it to 2008 as it seems that the order the calculations are done have changed in 2008.

Let me know.

Thanks,

Andrei

June 16, 2009 2:51 PM
 

Marco Russo (SQLBI) said:

I never created a Math Calculations dimensions, but I understand it could be useful in some cases.

It seems strange the problem you described about migration from 2005 to 2008 - do you have more details?

Marco

June 16, 2009 7:08 PM
 

Andrei Rjeousski said:

Marco,

Please see this thread for more information:

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/389a2328-bcba-4eff-bec2-5bbc97ffccb2

I was able to come up with a query against AdventureWorks that would return different results when ran against 2005 and 2008 servers, but am unable to find any documentation on changes that would cause it to run this way. I believe that 2005 version runs more correct as it gives accurate results.

If you would like to contact me for more information please email arjeousski at gmail dot com

Thanks,

Andrei

June 17, 2009 12:14 PM
 

Ankit Mittal said:

Hi Marco

Thanks a lot for such prompt reply and I will try DateTool on my project for sure.

Actually while analyzing cube's performance I came to a conclusion that query results much faster when it has high cache hits.

So is there any way in SSAS I can increase the amount of cache and keep it available all the time?

Thanks

Ankit Mittal

June 17, 2009 10:47 PM
 

Ankit Mittal said:

Marco

There is one more problem with our SSAS implementation, it creates many "SQlDmpr" files only on production server. It seems that every time a report hits cube, it creates one SQLDmpr file but this never happen on my local and any internal machines.

It's a kind of strange problem but any suggestion on this will be of great help.

Thanks

Ankit Mittal

June 17, 2009 10:56 PM
 

Marco Russo (SQLBI) said:

By default, SSAS already has much of the memory available for cache. You might want to warm-up the cache (ie after a process) by using some script that makes the necessary query to warm-up the cache.

Marco

June 18, 2009 4:30 AM
 

Giancarlo Ferrara said:

Hi Marco.

Try to set the standard format used for saving as Excel97-2003.

This forces excel to use the old pivottable format and in 2003 the calculated members works.

Note that you can seve the excel file also in 2007 format: is just the standard save option that must be modified.

June 18, 2009 6:44 AM
 

Ken said:

When I add a row to the agg view with a description that is slightly larger such as 'Total Current Quarter'

I can not get the datetool dim to process...even after I remove and re-add the agg view...remove and add the dim to the cube?

I get this error...  

Error 45 MdxScript(Sales Cube) (85, 5) The level '[Total Current Quarter]' object was not found in the cube when the string, [Time Calculations].[Time Periods].[Total Current Quarter], was parsed. 0 0

I added this code for quarter (i had to change your naming conventions)

   ( [Time Calculations].[Time Periods].[Total Current Quarter] )

         = ( [Time Calculations].[Time Periods].DefaultMember,

             Ancestor( [Dates].[Time].CurrentMember,

                       [Dates].[Time].[Quarter] ) );

Unless I remove everything and re-add it will not work?

any ideas?

Thanks

June 18, 2009 9:31 AM
 

Marco Russo (SQLBI) said:

Giancarlo,

the user that had this problem has been updated SQL Server and Excel just to get Excel 2007 PivotTable functionality - it was really the worst thing to suggest the rollback to the previous version of the PivotTable :)

Marco

June 18, 2009 6:03 PM
 

Marco Russo (SQLBI) said:

Andrei,

I've seen the thread you mentioned - I don't have time in these days to make some test, but I've seen other people interested to the issue and I'll update you on this as soon as I will have any new info about it.

Marco

June 18, 2009 6:05 PM
 

Marco Russo (SQLBI) said:

Ken,

when you create the view [to populate the attribute] for the first time, you implicitly set the the maximum length of the name of that attribute. At this point, if you later add a string longer than others, you need to check that the size of key/name used in SSAS is large enough, otherwise the name of new items will be truncated if longer than the previous maximum size.

Another solution might be to cast these columns to a NVARCHAR/VARCHAR type with a longer size just at the beginning.

Marco

June 18, 2009 6:09 PM
 

Ken said:

The issue I was having was also that when I added a new row to the view it would NOT be found when I tried to process the database..

I found that I have to process the time calc dimension separately then I can process the database...

Great tool... and thanks for being so responsive... nice to see.

-Ken

June 23, 2009 5:00 PM
 

Ken said:

We are seeing the time calcs not working for a dimension that has a parent / child hierarchy (recursive relationship) when isAggregratable is set to False.. if it is set to True it seems to work.. but that breaks other things... Any way around or thoughts?

Thanks

-Ken

June 24, 2009 1:40 PM
 

Marco Russo (SQLBI) said:

Not a good thing - having IsAggregatable set to False might have a lot of side effects. I didn't tested MDX queries of DateTool in such a scenario.

But why do you need IsAggregatable set to False?

June 24, 2009 1:46 PM
 

Ken said:

We are providing a unary operator....from a column that has a + or - in it..  

To be honest I don't really know what isAgg will turn on or off..

Is there any info on what it will or will not do?

-Ken

June 24, 2009 2:25 PM
 

Marco Russo (SQLBI) said:

Ken,

the IsAggregatable only removes the first level in the hierarchy (the one with the "All" element).

June 24, 2009 5:09 PM
 

Ankit Mittal said:

Marco

I have implemented DateTool in my project and it has given tremendous performance boon. Thanks a lot!

Actually in my report there are more than 300 measures and all of them do not need Year-Over-Year calculations. So Is it possible to restrict DateTool on those measures where it is applicable?

Ankit

June 25, 2009 2:24 PM
 

Marco Russo (SQLBI) said:

You can use the SCOPE in the MDX Script to limit the Year-over-Year calculation, but you have to choose whether to leave the cell blank or with another value (but what? you will be in a cell corresponding to a Year-over-Year calculation, after all... a blank cell might be the better idea...)

June 25, 2009 5:34 PM
 

Ankit Mittal said:

Thanks Marco, but I will use multiple query approach in one report that is much faster then getting all 300 measures in one query.

As I told you there are 100 measures that don't need Year-over-Year calculations, so it is much better to get it in separate query with a different grain.

Thanks for your help.

Ankit  

June 25, 2009 10:26 PM
 

John said:

Mark

I know I am getting into your Datatool concept well after you published it.

What I do not understand is the need for your Cross month and cross quarter MDX script?  What is the logic for this code?  Why is this needed?  

August 19, 2009 11:33 AM
 

Marco Russo (SQLBI) said:

If I remember well, it is needed to handle the cross of a month (attribute with 12 distinct values, not related to any year) with a year. The same for quarter.

Look at the different Month attributes in the sample: one attribute, visible only in hierarchies, has 12 values per year; the other attribute (which is visible as AttributeHierarchy) has 12 values, without relationships with Year attribute.

Marco

August 19, 2009 11:40 AM
 

John said:

Thank You I now undestand it now  I appreciate the quick response

August 19, 2009 1:33 PM
 

Ken said:

Have you seen any issues with memory?  Users accessing a medium size cube and heavily using the datetool logic seem to be getting lots of

"Memory error: Allocation failure: Not enough storage is available to process this command."  I know that is usually a RAM issue but in testing I am the only one on the box, it ONLY has AS on it and has a ton of RAM and storage.  I turned off the server setting to ignore the time estimates etc.. which gets by the first error.. the "Running OLAP query... " is in the bottom of excel for a LONG time then eventually dies.. other pages withOUT the datetool in use seem to be normal...

There are two fact tables with a combined row count of < 3mil so these are not huge cubes....

ANY thoughts here?  

August 24, 2009 11:51 PM
 

Ken said:

Upon further investigation if I have Comparison in there (as column) all works well.. the issue seems to be in DateAggregation... if I add that .. it goes to lunch.. if I add it as a filter all is well..

The trace just has the request..then Query SubCube lines forever... or until I cancel the request... I renamed DateAgg.. to Time Periods .. here is the command that fails..

SELECT {[Measures].[Physical Cases Shipped],[Measures].[SART Shipped]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(CrossJoin(CrossJoin(Hierarchize({DrilldownLevel({[Ship To].[Bill To Name].[All]})}), Hierarchize(DrilldownMember(CrossJoin({[Ship To].[Ship To Name].[All],[Ship To].[Ship To Name].[Ship To Name].Members}, {([Products].[Brand].[All])}), {-{[Ship To].[Ship To Name].&[BI-MART]}}, [Products].[Brand]))), Hierarchize({[Time Calculations].[Comparison].[Comparison].Members})),

Hierarchize({[Time Calculations].[Time Periods].[Time Periods].Members})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON

ROWS  FROM [Sales Cube] WHERE ([Customers].[NAM Market].&[NAM NORTHWEST],[Dates].[Time].[Month].&[200908]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Any help would be greatly appreciated..

-Ken

August 25, 2009 12:35 AM
 

Marco Russo said:

Ken, it's hard to say whay could be the issue - are you using SQL 2005 or 2008?

August 25, 2009 2:13 PM
 

Ken said:

2008 SP1...

I have looked and can not figure out why this would be an issue with this xlsx... I am stumped.. I even rebuilt the spreadsheet from scratch .. got the same issue..  

The data is filtered by a single day.. so there is not much data to get..  could that cause the issue?

Thanks for the reply..

-Ken

August 25, 2009 4:26 PM
 

Marco Russo said:

As I said, it's hard to think what could be.

You should try to debug MDX Script to understand where it is going in loop and aventually trying to look at it using MDX Studio (http://www.mosha.com/msolap/mdxstudio.htm).

August 25, 2009 4:36 PM
 

David said:

Marco, I have several role playing date dimensions, so to implement this approach I would need to create calculations for both, but still using the same set of views, correct?

September 10, 2009 6:05 PM
 

Marco Russo said:

David, if you use only one DateTool dimension, you have to create longer MDX Script that works only if no more than one (date) role dimension is selected at a level other than (all).

Otherwise, you could create separate DateTool dimensions (role dimensions...) but in my opinion it would become unmanageable and not understandable by end users.

September 10, 2009 6:55 PM
 

David said:

Marco, thanks for the quick reply. I was going to venture down the path of creating multiple DateTool dimensions for each date role simply because that is what I had done when I implemented my time calculations using the pattern that David Shroyer documented in 2007.

I have implemented your DateTool for one of my role dimensions and it works great. Though still a novice with MDX, I will see if I can extend the MDX so that it can be applied to the other date roles.

Also, after reading the sample chapter from your Expert Cube development book I placed an order with Amazon as this looks like a winner!

September 11, 2009 11:28 AM
 

David said:

Marco - I simply duplicated the MDX for each date role and that seems to work as expected. I hope that was the correct approach.

September 14, 2009 11:26 AM
 

JMac said:

Great solution and thanks for making this available.  I'm new to MDX so tweaking your work to fit my dimension took me some time.  Now that I've got it to work, my distinct count isn't rolling up correctly to the highest attribute in my hierarchy (Year-Quarter-Month-Day).  I commented out your code for semester since I don't have one.  Every other dimension and measure works fine, just not the distinct count.  In the browser with year and datetool aggregate as the dimensions and distinct count in the measures, none of the aggregates show.  I can post the code if it helps.  Thanks M.

October 27, 2009 2:17 PM
 

JMac said:

Found your article on distinct count (The many-to-many revolution: http://www.sqlbi.com/Portals/0/Downloads/M2M%20Revolution%201.0.93.pdf) and was able to very easily get that to work, but still see the same results.  It makes me suspect that I must have overlooked something when I modified your code in calcuations.  I hope this isn't too much code but here it goes:

/*

The CALCULATE command controls the aggregation of leaf cells in the cube.

If the CALCULATE command is deleted or modified, the data within the cube is affected.

You should edit this command only if you manually specify how the cube is aggregated.

*/

CALCULATE;    

// ------------------------------------------------------------------------

//

//    Aggregation - Time.[Calendar Time]

//

// ------------------------------------------------------------------------

SCOPE (

   [Time].[Calendar Time].MEMBERS,

[Time].[Calendar Date].MEMBERS );          

   ///////////////////////////////////////////////////////////////////////////////////////

   ( [DateTool].[Aggregation].[Year To Date] )

         = Aggregate(

                    { [DateTool].[Aggregation].DefaultMember } *

                      PeriodsToDate(

                                   [Time].[Calendar Time].[Calendar Year],

                                   [Time].[Calendar Time].CurrentMember

                    ) );          

   ///////////////////////////////////////////////////////////////////////////////////////

// NOTE: In AdventureWorks there are missing members in the first year

//       Last 12 Months returns wrong results for the second calendar year

//       Using a date dimension populated with all members for each year

//       would solves this problem

   ( [DateTool].[Aggregation].[Last 12 Months] )

         = Aggregate(

                    { [DateTool].[Aggregation].DefaultMember } *

                    { ParallelPeriod(

                                      [Time].[Calendar Time].[Calendar Year],

                                      1,

                                      [Time].[Calendar Time]

                      ).NextMember : [Time].[Calendar Time].CurrentMember

                    } );          

   ///////////////////////////////////////////////////////////////////////////////////////

//    ( [DateTool].[Aggregation].[Last 12 Months Moving Average] )

//          = Avg(

//                     { [DateTool].[Aggregation].DefaultMember } *

//                     { ParallelPeriod(

//                                       [Time].[Calendar Time].[Calendar Month],

//                                       12,

//                                       [Time].[Calendar Time]

//                       ).NextMember : [Time].[Calendar Time].CurrentMember

//                     } );          

   ///////////////////////////////////////////////////////////////////////////////////////

//    ( [DateTool].[Aggregation].[Last 3 Months Moving Average] )

//          = Avg(

//                     { [DateTool].[Aggregation].DefaultMember } *

//                     { ParallelPeriod(

//                                       [Time].[Calendar Time].[Calendar Month],

//                                       3,

//                                       [Time].[Calendar Time]

//                       ).NextMember : [Time].[Calendar Time].CurrentMember

//                     } );          

   ///////////////////////////////////////////////////////////////////////////////////////

   ( [DateTool].[Aggregation].[Total Current Year] )

         = ( [DateTool].[Aggregation].DefaultMember,

             Ancestor( [Time].[Calendar Time].CurrentMember,

                       [Time].[Calendar Time].[Calendar Year] ) );

   ///////////////////////////////////////////////////////////////////////////////////////

   // Tuple (All years, All Months) is the default number (keeps compatibility with OWC11)

   ( [Time].[Calendar Year].[All Periods], [Time].[Calendar Month].[All Periods],

     Except( [DateTool].[Aggregation].[Aggregation].MEMBERS, [DateTool].[Aggregation].DefaultMember ) ) = [DateTool].[Aggregation].DefaultMember;          

END SCOPE;          

// ------------------------------------------------------------------------

//

//    CROSS SEMESTERS/QUARTER/MONTHS

//          Redirection on Calendar Hierarchy

//

// ------------------------------------------------------------------------

// The FREEZE preserves the sum for all months, that otherwise would be

// the sum of each month *after* aggregation like YTD (so it doesn't multiply

// the same month value many times in different aggregations)

SCOPE ( [Time].[Calendar Month].[All Periods],

//       [Time].[Month Name].[All],

       [DateTool].[Aggregation].AllMembers,

       [DateTool].[Comparison].AllMembers );            

   FREEZE;          

END SCOPE;          

// ------------------------------------------------------------------------

//

//    CROSS SEMESTERS/QUARTER/MONTHS

//          Redirection on Calendar Hierarchy

//

// ------------------------------------------------------------------------

////

//// CROSS SEMESTERS

////

//SCOPE ( [Time].[Semester of Year].[Semester of Year].Members,

//        [Time].[Semester].[All],

//        [DateTool].[Aggregation].AllMembers,

//        [DateTool].[Comparison].AllMembers );  

//

//    ///////////////////////////////////////////////////////////////////////////////////////

//    // It should be scoped do:

//    // SCOPE ( { [DateTool].[Aggregation].[Year To Date], [DateTool].[Aggregation].[Last 12 Months] } );

//    // but since it is not supported, we use the Except function

//    SCOPE ([Time].[Calendar Year].[Calendar Year].Members,

//           Except( [DateTool].[Aggregation].Members, {[DateTool].[Aggregation].DefaultMember} ) );

//        THIS = Root( StrToMember( "[Time].[Semester].&["

//                                  + [Time].[Calendar Year].CurrentMember.Properties( 'Key' )

//                                  + "]&["

//                                  + [Time].[Semester of Year].CurrentMember.Properties( 'Key' )

//                                  + "]" ) );

//    END SCOPE;

//    ///////////////////////////////////////////////////////////////////////////////////////

//    

//    // The FREEZE is necessary to fix the result against possible modifications made by script

//    // for lower levels of attribute hierarchy

//    FREEZE;

//END SCOPE;

//

// CROSS QUARTERS

//

SCOPE ( [Time].[Calendar Quarter].[Calendar Quarter].Members,

       //[Time].[Quarter].[All],

       [DateTool].[Aggregation].AllMembers,

       [DateTool].[Comparison].AllMembers );            

   ///////////////////////////////////////////////////////////////////////////////////////

   // It should be scoped do:

   // SCOPE ( { [DateTool].[Aggregation].[Year To Date], [DateTool].[Aggregation].[Last 12 Months] } );

   // but since it is not supported, we use the Except function

   SCOPE ([Time].[Calendar Year].[Calendar Year].Members,

          Except( [DateTool].[Aggregation].Members, {[DateTool].[Aggregation].DefaultMember} ) );          

       THIS = Root( StrToMember( "[Time].[Calendar Quarter].&["

                                 + [Time].[Calendar Year].CurrentMember.Properties( 'Key' )

                                 + "]&["

                                 + [Time].[Calendar Quarter].CurrentMember.Properties( 'Key' )

                                 + "]" ) );          

   END SCOPE;          

   ///////////////////////////////////////////////////////////////////////////////////////

   // The FREEZE is necessary to fix the result against possible modifications made by script

   // for lower levels of attribute hierarchy

   FREEZE;          

END SCOPE;          

//

// CROSS MONTHS

//

SCOPE ( [TIme].[Month].[Month].Members,

       [Time].[Calendar Month].[All],

       [DateTool].[Aggregation].AllMembers,

       [DateTool].[Comparison].AllMembers );            

   ///////////////////////////////////////////////////////////////////////////////////////

   // It should be scoped do:

   // SCOPE ( { [DateTool].[Aggregation].[Year To Date], [DateTool].[Aggregation].[Last 12 Months] } );

   // but since it is not supported, we use the Except function

   SCOPE ([Time].[Calendar Year].[Calendar Year].Members,

          Except( [DateTool].[Aggregation].Members, {[DateTool].[Aggregation].DefaultMember} ) );          

       THIS = Root( StrToMember( "[Time].[Calendar Month].&["

                                 + [Time].[Calendar Year].CurrentMember.Properties( 'Key' )

                                 + "]&["

                                 + [Date].[Month].CurrentMember.Properties( 'Key' )

                                 + "]" ) );          

   END SCOPE;          

   ///////////////////////////////////////////////////////////////////////////////////////

   // The FREEZE is necessary to fix the result against possible modifications made by script

   // for lower levels of attribute hierarchy

   FREEZE;          

END SCOPE;          

// ------------------------------------------------------------------------

//

//    Comparison - Date.Calendar

//

// ------------------------------------------------------------------------

SCOPE (

   [Time].[Calendar Time].MEMBERS,

[Time].[Calendar Date].MEMBERS );          

   ///////////////////////////////////////////////////////////////////////////////////////

   ( [DateTool].[Comparison].[Previous Year],

     [DateTool].[Aggregation].Members )

         = ( [DateTool].[Comparison].DefaultMember,

             ParallelPeriod( [Time].[Calendar Time].[Calendar Year],

                             1,

                             [Time].[Calendar Time].CurrentMember ) );          

END SCOPE;          

SCOPE ([DateTool].[Comparison].[Diff. Over Previous Year]);          

   THIS = IIF( IsEmpty( [DateTool].[Comparison].DefaultMember )

                        OR IsEmpty( [DateTool].[Comparison].[Previous Year] ),

               NULL,

               [DateTool].[Comparison].DefaultMember

               - [DateTool].[Comparison].[Previous Year] );          

   NON_EMPTY_BEHAVIOR(THIS) = [DateTool].[Comparison].DefaultMember;          

   FORE_COLOR(THIS) = IIF( [DateTool].[Comparison].[Diff. Over Previous Year] < 0, 255, 0);            // 255 = RED

END SCOPE;          

SCOPE ([DateTool].[Comparison].[Diff. % Over Previous Year]);          

   THIS = IIF( IsEmpty( [DateTool].[Comparison].[Diff. Over Previous Year] )

                        OR IsEmpty( [DateTool].[Comparison].[Previous Year] ),

               NULL,

               [DateTool].[Comparison].[Diff. Over Previous Year]

               / [DateTool].[Comparison].[Previous Year] );          

   NON_EMPTY_BEHAVIOR(THIS) = [DateTool].[Comparison].DefaultMember;          

   FORMAT_STRING(THIS) = 'Percent';          

   FORE_COLOR(THIS) = IIF( [DateTool].[Comparison].[Diff. % Over Previous Year] < 0, 255, 0);            // 255 = RED

END SCOPE;          

///////////////

// Tuple (All years, All Months) is the default number (keeps compatibility with OWC11)

( [Time].[Calendar Year].[All Periods], [Time].[Month].[All Periods],

 Except( [DateTool].[Comparison].[Comparison].MEMBERS, [DateTool].[Comparison].DefaultMember ) ) = [DateTool].[Comparison].DefaultMember;          

///////////////////////////////////////////////////////////////////////////////////////

//CREATE MEMBER CURRENTCUBE.[MEASURES].[Calculated Member]

// AS count(nonempty([DimName].[HierarchyName].[LevelName].members*[Measures].[MyMeasure])),

//VISIBLE = 1  ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Alt Distinct Players]

AS count(nonempty([Player].[Player].[Player].members*[Measures].[total visit]),

ExcludeEmpty),

FORMAT_STRING = "#,#",

VISIBLE = 0  ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Distinct Players]

AS COUNT(CROSSJOIN({[total visit]}, Descendants([Player].[Player].CurrentMember

, [Player].[Player].[Player])), ExcludeEmpty),

FORMAT_STRING = "#,#",

VISIBLE = 1  ;

October 28, 2009 2:50 PM
 

asd_2000 said:

I have implemented the DateTool in a cube but need to create an SSRS report that uses DateTool calculations using the cube as datasource. Is it possible to make selections to display only certain DateTool dimension members? In excel it is trivial but dont see how to accomplish this on SSRS 2008!

Thanks

BTW your expert cube design book is awesome!

November 16, 2009 6:33 PM
 

Marco Russo (SQLBI) said:

If you are using the designer of Reporting Services query, you should be able to edit the MDX directly in a text window - to select individual members you should drag&drop those single elements, otherwise you can write the set definition directly into the query (in ROWS or COLUMNS axis definition).

November 23, 2009 12:36 PM
 

Nick said:

Thanks!

December 21, 2009 1:21 PM
 

Ankit Mittal said:

Hi Marco,

How can I implement classic Percentile functionality in SSAS?

Any help will be greatly appreciated.

Thanks

Ankit

January 25, 2010 9:38 PM
 

Marco Russo (SQLBI) said:

You can build calculated member in MDX script to calculate Percentile, but the problem is that performance are bad if you have a large amount of data (and percentile is calculated at a leaf level (or almost at the leaf level).

A better way is to calculate it on the client on small set of data. For example, client tools like Proclarity already have charts that do that.

Usually I define calculation of ABC segments (which are derived by percentile calculation) for transactions so that you can use this new attribute on columns/rows/filters of a PivotTable. These calculation is made in ETL and is against a fixed reference (i.e. products sold in each year).

January 26, 2010 2:31 AM
 

Melinda said:

I have implemented your datetool and it works great.  The only issue is that Prior Year needs to be Prior year up through the same day as current year.  For instance, if we are thru week 2 of March in the current calendar year, I would like the Prior Year calc to show the Prior Year of March through week 2 as well.  How can I do this?  I am using a Fiscal calendar so I am using the PeriodsToDate calc and not YTD.  Any ideas would be greatly appreciated.

January 26, 2010 6:38 PM
 

Melinda said:

Here's the Scope statement I have.  The commented out section was what I originally had before I tried to get only the same period LY.  I thought if I used the PeriodsToDate function I could get the same period-to-date for LY data.  

SCOPE (

   [Date DM].[Fiscal - YQMWD].MEMBERS,

[Date DM].[Date].MEMBERS );

   ( [FiscalCalcs].[Fiscal Comparison].[LY],

     [FiscalCalcs].[Aggregation].Members )

         /*= ( [FiscalCalcs].[Fiscal Comparison].DefaultMember,

             ParallelPeriod( [Date DM].[Fiscal - YQMWD].[Fiscal Year],

                             -1,

                             [Date DM].[Fiscal - YQMWD].CurrentMember )

            );*/

         = IIF( [Date DM].[Fiscal - YQMWD].CurrentMember=0, NULL,

       Aggregate(

       { [FiscalCalcs].[Fiscal Comparison].DefaultMember } *

       PeriodsToDate(

             [Date DM].[Fiscal -YQMWD].CurrentMember.Level,

             ParallelPeriod( [Date DM].[Fiscal - YQMWD].[Fiscal Year],

                    1,

                    [Date DM].[Fiscal - YQMWD].CurrentMember) )

                    ) );

END SCOPE;

January 26, 2010 6:45 PM
 

Marco Russo (SQLBI) said:

At first sight my question is - why you don't use this:

AGGREGATE( PeriodsToDate(...), [FiscalCalcs].[Fiscal Comparison].DefaultMember )

Instead that cross-joining the PeriodsToDate with Fiscal Comparison default member...

January 26, 2010 7:00 PM
 

Ankit Mittal said:

Hi Marco,

Thanks for the reply.

Do you have any example of Percentile calculation in calculated members? The performance may not be a bigger issue as we do not have huge amount of data.

And I read that SSAS provide Excel function - Percentile(), do you think that can help in resolving this issue.

January 26, 2010 11:40 PM
 

Melinda said:

Thanks Marco.  I tried your suggestion and I get #VALUE! in Excel 07.  I am very much a novice at MDX.  Any more help you could be would be greatly appreciated!!  Here's the code I used.  If it is easier to use your DateTool example and use that code, I can translate.  Thanks soooo much!!!

SCOPE (

   [Date DM].[Fiscal - YQMWD].MEMBERS,

[Date DM].[Date].MEMBERS );

   ( [FiscalCalcs].[Fiscal Comparison].[LY],

     [FiscalCalcs].[Aggregation].Members )

=      Aggregate(

                      PeriodsToDate(

                             [Date DM].[Fiscal - YQMWD].CurrentMember.Level,

                             ParallelPeriod( [Date DM].[Fiscal - YQMWD].[Fiscal Year],

                             1,

                             [Date DM].[Fiscal - YQMWD].CurrentMember) ),

                     [Fiscal Calcs].[Fiscal Comparison].DefaultMember );

END SCOPE;

January 27, 2010 4:26 PM
 

David Crandall said:

This just made me look like an absolute star at work! Just wanted to let you know how very much I appreciate it!!!

February 2, 2010 3:47 PM
 

Melinda said:

Marco,

HELP!!!  I am still not able to get the Prior year same time period to work.  If we are thru 2nd week in March for instance, I would like the LY number to be only last year up through the 2nd of week of March as well to the same day last year.  Does that make sense?  

I have tried just writing MDX queries against the cube to see if I can get this to work.  Any ideas???  I am stumped.  I am not sure how you tell it to only include up thru say day 260 in the prior year to get the LY number.  If I have to I can have a MTD LY calc, QTD LY calc, etc.  I am just not even sure how to create a calculated member to get this.

Please anyone that done this and give me any help would be GREATLY appreciated!!!

Our release of our cube is stuck on this.  The end users feel they can't even use it without the Period to date comparisons to LY.  HELP!!!

February 8, 2010 5:13 PM
 

Marco Russo (SQLBI) said:

Melinda,

I'm pretty busy these days - sorry if I don't have time to look at all the details to write the MDX you need.

At an higher level, you should write the day number in some attribute of the Date dimension and using it at the leaf level of a hierarchy to be able to get the desired value. In other words, you should translate the calendar date into its corresponding working day and then operating the MDX calculation over a hierarchy made using that attribute instead of the calendar date.

Sorry for not able to give you more time.

Marco

February 8, 2010 5:31 PM
 

Melinda said:

Thanks Marco! I appreciate any time you can give to help me through this.

I will try adding that working day number to the leaf level of the hierarchy.  Do you mean that each year would have a day 260 instead of using the date?  Correct?

Then conceptually, would you then aggregate say YTD up through that day 260, and then do a parallelperiod on that?? Or conceptually how would you get the prior year up through day 260 of the prior year?  

I have successfully got QTD, and prior year QTD for instance but it takes the whole prior year for that quarter, not just up through say day 260.  How do you limit it?

Here's what I have tried, if this helps at all.  

with

  member [Measures].[2010 Sales Amt] as

     '([Date DM].[Fiscal - YQMWD].CurrentMember, [Measures].[Sales Amt])'

  member [Measures].[YTD Sales] as

     'Sum( PeriodsToDate([Date DM].[Fiscal - YQMWD].[Fiscal Year]),[Sales Amt])'

  member [Measures].[QTD Sales] as

     'Sum( PeriodsToDate([Date DM].[Fiscal - YQMWD].[Fiscal Quarter]),[Sales Amt])'

  member [Measures].[Prior Year YTD] as

     'Sum(PeriodsToDate([Date DM].[Fiscal - YQMWD].[Fiscal Year]

                 ,ParallelPeriod( [Date DM].[Fiscal - YQMWD].[Fiscal Year],

                                  1,

                                  [Date DM].[Fiscal - YQMWD].CurrentMember)),   [Sales Amt])'

  member [Measures].[Prior Year QTD] as

     'Sum(PeriodsToDate([Date DM].[Fiscal - YQMWD].[Fiscal Quarter]

                ,ParallelPeriod([Date DM].[Fiscal - YQMWD].[Fiscal Year]

                     ,1

                     ,[Date DM].[Fiscal - YQMWD].CurrentMember)),[Measures].[Sales Amt])'    

  member [Measures].[End Bal] as

     '(ClosingPeriod( [Date DM].[Fiscal - YQMWD].[Fiscal Day],[Date DM].[Fiscal - YQMWD].CurrentMember),[Measures].[Sales Amt])'          

select

   {  [Measures].[2010 Sales Amt]

     ,[Measures].[YTD Sales]

     ,[Measures].[QTD Sales]

     ,[Measures].[Prior Year YTD]

     ,[Measures].[Prior Year QTD]

     ,[Measures].[End Bal]} on columns

  ,{[Date DM].[Fiscal - YQMWD].[Fiscal Month]} on rows

from

   [DEV DDS SKU]

February 8, 2010 7:14 PM
 

Marco Russo (SQLBI) said:

Yes, the idea is to use the day instead of the date in a hierarchy of the date dimension (you can also have the date attribute too - the day number should be hierarchically above the date, not below).

The only issue is that using ParallelPeriod could be not the better idea, because it works moving between hierarchical levels using relative distance (from ancestor, from siblings, and so on).

I would use the range syntax using the StrToMember function - it might be not the best in terms of performance, but at the end you will use it only for the final calculation (like the sum of the single days) and it should be fast enough.

Marco

February 9, 2010 2:53 AM
 

GS said:

Hi Marco;

Following implementation of this method for time intelligence I have encountered some issues for certain MDX queries as follows:

"The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples"

Have you encountered anything similar? The query runs fine prior to the implementation of the time intelligence scripts.

Thanks.

February 14, 2010 3:45 PM
 

Marco Russo (SQLBI) said:

I've never seen this message!

It seems something related to a very large cube space, but I've found this KB article for this message: http://support.microsoft.com/kb/940019

Please let me know if you find how to solve the issue!

Marco

February 14, 2010 4:00 PM
 

GS said:

Re writing the query has resolved the tuple error. However I do seem to be getting the same issue that Ken previously mention in that certain queries which ran in sub 10 seconds on our cube prior to using the aggregations are now taking upwards of 15minutes. The profiler trace sits on a query subcube entry and doesn't appear to do any more.

A bit of digging about seems to point towards the use of NON EMPTY in the query in question. This again ties with Ken's post.

I'm wondering if Ken got his issue resolved, and if so can possibly shed somelight on the issue i'm having. I would have possibly thought it was the NON_EMPTY_BEHAVIOUR but the query is only using a defined measure.

Any help would be greatly appreciated.

Cheers,

G

February 16, 2010 11:06 AM
 

Marco Russo (SQLBI) said:

GS, are you using SQL2005 or SQL2008?

February 16, 2010 11:09 AM
 

GS said:

SQL 2005.

I'm still looking into the performance issues but commenting out the FREEZE statements do make a difference. I am however aware that these are absolutely necessary in the solution.

I'll keep at this and hopefully identify the root cause. If I find anything i'll be sure to post back.

February 17, 2010 6:25 AM
 

MIchael said:

I ran into an issue using Date Tool with calculated measures. The problem seems to be pretty common (based on some research I did online).  Here is a good link, which describes it: http://sqlserverpedia.com/blog/sql-server-bloggers/scope-and-calculated-members.  Basically if I have calculated measures defined, which I want to use with a DateTool dimension, I get an error: "A set has been encountered that cannot contain calculated members.".  Even though the link above contains a suggestion on how to work around this issue, I still don't fully understand how to do this with DateTool.  The idea is to replace this:

SCOPE({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]});

   this=1;

END SCOPE;

with this:

({[Measures].[RegularMeasure],[Measures].[CalculatedMeasure]})=1;

But how do I also cross this with the DateTool dimension, so that I can do YTD, etc. on my calculated measures?

Any suggestions are appreciated!

February 18, 2010 11:44 PM
 

Marco Russo (SQLBI) said:

Michael,

you should try to collapse all the scopes into a single tuple, assigning that tuple.

For example:

SCOPE (A, B);

 SCOPE (C, D);

   (E, F) = 1;

 END SCOPE;

END SCOPE;

becomes:

(A, B, C, D, E, F) = 1;

Marco

February 19, 2010 6:08 PM
 

Michael said:

Thank you, Marco!  This is exactly what I need and it works perfectly with the DateTool.

February 22, 2010 6:01 PM
 

Eric L. said:

Hello M. Russo, & thank you for this very helping way to manipulate data on a time axis.

Hope this one doesn’t sound too naive: is it logical that “% Diff” member of the Comparison attribute doesn’t work with any calculated measure ? Or shall I assume that a bad script implementation is the cause of a wrong calculation ?

More precisely : “Diff” works fine for calculated measure A (for example, an average, or an aggregation over attribute members of another dimension), but “% Diff” is totally wrong, just when script formula makes one expect a simple ratio between Diff and previous year.

What point am I missing ?

Thanks,

Eric L.

April 6, 2010 9:19 AM
 

Marco Russo said:

Eric, are you talking about the calc measures that are in the sample script or about your own calc measures? In the first case, let me know what are wrong calculations. In the second case, the problem is the way you create them - look at the sample MDX and you'll see that all calc measures are created with NULL and then assigned in a SCOPE statement (or at least it should be so!). The critical part is choosing the right order for SCOPE definition.

Marco

April 6, 2010 9:26 AM
 

Mark said:

I have experienced an "interesting" performance problem while using the "Date Tool" technique described here.  I have a fairly complex query and the [Date] along with [Time Calculation] is used as one of the slicers in the WHERE clause of MDX.  The query is pretty fast for YTD calculations, but is about 4 times slower for any sort of "Rolling X Month" aggregation.  Below is my simplified query. The goal is to return FOR EACH QUESTION a list of TOP 25% of the employees by [Score]:

WITH

SET [Questions_To_Return] AS [Questions].[Type_Question].[Question].members

SET [Top_Quart] AS

Generate(

[Questions_To_Return],

[Questions_To_Return].currentmember *

TopCount (  

NONEMPTY ([Employee].[Employee].[Employee].members

) as [Members_Total] --<== Note SET Alias (this is the set from which to get the TOP 25%)

, Int(((25 * [Members_Total].Count) + 99) / 100)  --<== Trick way to get 25%

,[Measures].[Score]

)

)

MEMBER [Measures].[DW_QuestionID] AS [Questions].[Type_Question].currentmember.member_key

SELECT

{

[Measures].[DW_QuestionID],

[Measures].[Score]

}  ON 0,

[Top_Quart]

ON 1

FROM [MyCube]

WHERE (

[Date].[Calendar].[Date].&[20100331],

[Time Calculations].[Calendar YTD]

//[Time Calculations].[Rolling 3 Months]

)

;

As I mentioned above, replacing [Time Calculations].[Calendar YTD] with [Time Calculations].[Rolling 3 Months] in the WHERE clause results in significantly decreased performance.

Final piece of info.  In my cube script, these time periods are defined as follows (I can't use SCOPE because I have calculated Measures):

   (Measures.AllMembers, [Time Calculations].[Calendar YTD]) =

       Aggregate(  

           CrossJoin({[Time Calculations].[Current Period]},

               PeriodsToDate([Date].[Calendar].[Year],

               [Date].[Calendar].CurrentMember)

                   )

           );

   (Measures.AllMembers, [Time Calculations].[Rolling 3 Months]) =

       Aggregate(  

               CrossJoin(

                   {[Time Calculations].[Current Period]},

                      ParallelPeriod([Date].[Calendar].[Month],3, [Date].[Calendar].CurrentMember).NextMember

                      : [Date].[Calendar].CurrentMember

               )

        );

I appreciate any suggestions as to why such difference in performance.

Thank you!

April 8, 2010 11:11 AM
 

Marco Russo (SQLBI) said:

Mark, it's hard to say what is the issue.

You should try to comment out all other calcs in the script and see it the same performance problems occur.

It might depend on apparently unrelated calculations - for this reason you should try to comment out everything else.

Let me know.

Marco

April 12, 2010 5:14 PM
 

Chad Dotzenrod said:

Could you clarify (or hint) what exactly is required to allow multiple date roles to work with the utility dimension.  

April 30, 2010 10:39 AM
 

Chad Dotzenrod said:

I think I got it.  I duplicated the aggregate and comparison sections for each date role and then I modified the intitial scope sections to include the all level for the non related date roles.  Something like this:

/* Time Intelligence - [Snapshot Date] */

// ------------------------------------------------------------------------

//

//    Aggregation Attribute Hierarchy - [Snapshot Date].[Calendar Time]

//

// ------------------------------------------------------------------------

SCOPE (

   [Snapshot Date].[Calendar Time].MEMBERS,

   [Snapshot Date].[Date].MEMBERS,

   [Activity Date].[Calendar Time].[All],

   [Activity Date].[Date].[All],

   [Account Date].[Calendar Time].[All],

   [Account Date].[Date].[All],

   [Actual Close Date].[Calendar Time].[All],

   [Actual Close Date].[Date].[All],

   [Created On Date].[Calendar Time].[All],

   [Created On Date].[Date].[All],

   [Estimated Close Date].[Calendar Time].[All],

   [Estimated Close Date].[Date].[All]

    );   ...

April 30, 2010 11:52 AM
 

Michael D. said:

We are currently using the Date Tool in our cubes and it is working extremely well.  We are using the cubes to create dashboards in PerformancePoint 2007, but have encountered a problem.  

The end users have asked that we provide them with a multiselect on the Corporate departments so they can analyze expenses.  Unfortunately when they select multiple departments, the % Diff is not calculating correctly.  At first we thought it was the way PerformancePoint was creating the MDX, however we have a nother report where we use calculated measures to find the % Diff between Actual and Budget and the report shows the correct numbers.

Do you have a solution for multiselects and Comparison?

May 11, 2010 3:00 PM
 

Marco Russo (SQLBI) said:

Can you repro the issue by using Excel?

If not, can you intercept the generated MDX query (using SQL Profiler)?

May 11, 2010 6:13 PM
 

Michael D. said:

Since PerformancePoint Autogenerates the MDX it is easy to grab on the query.  Here is the query for a single select:

WITH

SET [HierSet06356a1426494838b49a5bc21a13d4da] AS HIERARCHIZE( { [Account].[Detailed Operating].[All], [Account].[Detailed Operating].[DPS LV1].&[3LABFR], [Account].[Detailed Operating].[DPS LV1].&[4INGFR], [Account].[Detailed Operating].[DPS LV1].&[5PKGFR], [Account].[Detailed Operating].[DPS LV1].&[6OTHFR] } )

SET [HierSetBreakSort06356a1426494838b49a5bc21a13d4da] AS ORDER([HierSet06356a1426494838b49a5bc21a13d4da], ( [DateTool].[Aggregation].&[0], [DateTool].[Comparison].&[0] ), BDESC)

SET [HierSetSort06356a1426494838b49a5bc21a13d4da] AS ORDER([HierSet06356a1426494838b49a5bc21a13d4da], ( [DateTool].[Aggregation].&[0], [DateTool].[Comparison].&[0] ), DESC)

MEMBER [Account].[Detailed Operating].[MinLevel06356a1426494838b49a5bc21a13d4da] AS MIN([HierSet06356a1426494838b49a5bc21a13d4da], [HierSet06356a1426494838b49a5bc21a13d4da].CurrentMember.Level.Ordinal)

MEMBER [Account].[Detailed Operating].[MaxLevel06356a1426494838b49a5bc21a13d4da] AS MAX([HierSet06356a1426494838b49a5bc21a13d4da], [HierSet06356a1426494838b49a5bc21a13d4da].CurrentMember.Level.Ordinal)

SET [LevelSet06356a1426494838b49a5bc21a13d4da] AS ORDER([Account].[Detailed Operating].Levels([Account].[Detailed Operating].[MinLevel06356a1426494838b49a5bc21a13d4da]).Members, ( [DateTool].[Aggregation].&[0], [DateTool].[Comparison].&[0] ), BDESC)

SELECT

HIERARCHIZE( { [DateTool].[Aggregation].&[0], [DateTool].[Aggregation].&[1] } ) * HIERARCHIZE( { [DateTool].[Comparison].&[0], [DateTool].[Comparison].&[1], [DateTool].[Comparison].&[2], [DateTool].[Comparison].&[3] } )

ON COLUMNS,

NON EMPTY IIF ([Account].[Detailed Operating].[MinLevel06356a1426494838b49a5bc21a13d4da] = [Account].[Detailed Operating].[MaxLevel06356a1426494838b49a5bc21a13d4da], [HierSetBreakSort06356a1426494838b49a5bc21a13d4da],GENERATE([LevelSet06356a1426494838b49a5bc21a13d4da],{INTERSECT([HierSetSort06356a1426494838b49a5bc21a13d4da], DESCENDANTS([Account].[Detailed Operating].CURRENTMEMBER))}))

ON ROWS

FROM [Operating Statement]

WHERE ( [Time].[by FY Period].[Fiscal Period Description].&[05-10], [Company].[Company].&[14], [Location].[Location].&[28], [Department].[Summary Operating].[SOS Level 4].&[OP_PROF]&[GM]&[2DC]&[1100], [Measures].[Actual Dollars (in K)] )

and here it is when it is used in a multiselect:

WITH

SET [HierSetfbe74d68448840b59d8589a0364c62c7] AS HIERARCHIZE( { [Account].[Detailed Operating].[All], [Account].[Detailed Operating].[DPS LV1].&[3LABFR], [Account].[Detailed Operating].[DPS LV1].&[4INGFR], [Account].[Detailed Operating].[DPS LV1].&[5PKGFR], [Account].[Detailed Operating].[DPS LV1].&[6OTHFR] } )

SET [HierSetBreakSortfbe74d68448840b59d8589a0364c62c7] AS ORDER([HierSetfbe74d68448840b59d8589a0364c62c7], ( [DateTool].[Aggregation].&[0], [DateTool].[Comparison].&[0] ), BDESC)

SET [HierSetSortfbe74d68448840b59d8589a0364c62c7] AS ORDER([HierSetfbe74d68448840b59d8589a0364c62c7], ( [DateTool].[Aggregation].&[0], [DateTool].[Comparison].&[0] ), DESC)

MEMBER [Account].[Detailed Operating].[MinLevelfbe74d68448840b59d8589a0364c62c7] AS MIN([HierSetfbe74d68448840b59d8589a0364c62c7], [HierSetfbe74d68448840b59d8589a0364c62c7].CurrentMember.Level.Ordinal)

MEMBER [Account].[Detailed Operating].[MaxLevelfbe74d68448840b59d8589a0364c62c7] AS MAX([HierSetfbe74d68448840b59d8589a0364c62c7], [HierSetfbe74d68448840b59d8589a0364c62c7].CurrentMember.Level.Ordinal)

SET [LevelSetfbe74d68448840b59d8589a0364c62c7] AS ORDER([Account].[Detailed Operating].Levels([Account].[Detailed Operating].[MinLevelfbe74d68448840b59d8589a0364c62c7]).Members, ( [DateTool].[Aggregation].&[0], [DateTool].[Comparison].&[0] ), BDESC)

MEMBER [Location].[Location].[ Aggregation] AS 'AGGREGATE( EXISTING { [Location].[Location].&[28], [Location].[Location].&[29] } )', SOLVE_ORDER = 0

SELECT

HIERARCHIZE( { [DateTool].[Aggregation].&[0], [DateTool].[Aggregation].&[1] } ) * HIERARCHIZE( { [DateTool].[Comparison].&[0], [DateTool].[Comparison].&[1], [DateTool].[Comparison].&[2], [DateTool].[Comparison].&[3] } )

ON COLUMNS,

NON EMPTY IIF ([Account].[Detailed Operating].[MinLevelfbe74d68448840b59d8589a0364c62c7] = [Account].[Detailed Operating].[MaxLevelfbe74d68448840b59d8589a0364c62c7], [HierSetBreakSortfbe74d68448840b59d8589a0364c62c7],GENERATE([LevelSetfbe74d68448840b59d8589a0364c62c7],{INTERSECT([HierSetSortfbe74d68448840b59d8589a0364c62c7], DESCENDANTS([Account].[Detailed Operating].CURRENTMEMBER))}))

ON ROWS

FROM [Operating Statement]

WHERE ( [Time].[by FY Period].[Fiscal Period Description].&[05-10], [Company].[Company].&[14], [Location].[Location].[ Aggregation], [Department].[Summary Operating].[SOS Level 4].&[OP_PROF]&[GM]&[2DC]&[1100], [Measures].[Actual Dollars (in K)] )

the Hierachy sets is because we have specified ordering on the Current Year.

May 12, 2010 9:38 AM
 

Michael D. said:

I was thinking it may be cleaner if I just took the sort off.  Here is the multiselect:

WITH

MEMBER [Department].[Sales Hierarchy].[ Aggregation] AS 'AGGREGATE( EXISTING { [Department].[Sales Hierarchy].[SLS Level 3].&[OP_PROF]&[OH]&[1200], [Department].[Sales Hierarchy].[SLS Level 3].&[OP_PROF]&[OH]&[1300] } )', SOLVE_ORDER = 0

SELECT

HIERARCHIZE( { [DateTool].[Aggregation].&[0], [DateTool].[Aggregation].&[1] } ) * HIERARCHIZE( { [DateTool].[Comparison].&[0], [DateTool].[Comparison].&[1], [DateTool].[Comparison].&[2], [DateTool].[Comparison].&[3] } )

ON COLUMNS,

NON EMPTY HIERARCHIZE( { [Account].[Expense Category].[All], [Account].[Expense Category].[EXP LV1].&[40000], [Account].[Expense Category].[EXP LV1].&[EXPFR] } )

ON ROWS

FROM [Expense Category]

WHERE ( [Company].[Company].&[14], [Location].[Location].[Total], [Time].[by FY Period].[Fiscal Period Description].&[05-10], [Department].[Sales Hierarchy].[ Aggregation], [Measures].[Cost Per Cases Sold] )

and here is the single select

SELECT

HIERARCHIZE( { [DateTool].[Aggregation].&[0], [DateTool].[Aggregation].&[1] } ) * HIERARCHIZE( { [DateTool].[Comparison].&[0], [DateTool].[Comparison].&[1], [DateTool].[Comparison].&[2], [DateTool].[Comparison].&[3] } )

ON COLUMNS,

NON EMPTY HIERARCHIZE( { [Account].[Expense Category].[All], [Account].[Expense Category].[EXP LV1].&[40000], [Account].[Expense Category].[EXP LV1].&[EXPFR] } )

ON ROWS

FROM [Expense Category]

WHERE ( [Company].[Company].&[14], [Location].[Location].[Total], [Time].[by FY Period].[Fiscal Period Description].&[05-10], [Department].[Sales Hierarchy].[SLS Level 2].&[OP_PROF]&[OH], [Measures].[Cost Per Cases Sold] )

Thanks for the help.

May 12, 2010 9:43 AM
 

Marco Russo (SQLBI) said:

Uhm - I'm worried it could be the WHERE condition - can you try the same query using Excel 2007? Excel uses SUBCUBE and if it works (I don't have time to test it now) then the difference might be related to the WHERE vs SUBCUBE usage. And it could be an issue, I should have time to think about it - however, let me know if Excel makes the same mistake (and send me the MDX query in that case)

May 13, 2010 4:42 AM
 

Michael D. said:

Marco,

Thanks for your help.  It appears the Date Tool works well with Excel 2007.  The data appears to be correct.  Here is the MDX generated from Excel 2007:

SELECT

NON EMPTY CrossJoin(Hierarchize({[DateTool].[Aggregation].[Aggregation].Members}), Hierarchize({[DateTool].[Comparison].[Comparison].Members})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,

NON EMPTY Hierarchize({DrilldownLevel({[Account].[Expense Category].[All]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  

FROM (

SELECT

({[DateTool].[Aggregation].&[2], [DateTool].[Aggregation].&[1]})

ON COLUMNS  

FROM (

SELECT

({[Department].[Summary Operating].[SOS Level 4].&[OP_PROF]&[OH]&[6500]&[6510], [Department].[Summary Operating].[SOS Level 4].&[OP_PROF]&[OH]&[6500]&[6520], [Department].[Summary Operating].[SOS Level 4].&[OP_PROF]&[OH]&[6500]&[6530]})

ON COLUMNS  

FROM [Expense Category]))

WHERE ([Company].[Company].&[14],[Location].[Location].[Total],[Time].[by FY Period].[Fiscal Period Description].&[05-10],[Measures].[Actual Dollars]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Not sure how to proceed going forward since the client likes the look and capabilities of PerformancePoint.

May 17, 2010 10:31 AM
 

Giancarlo said:

Thanks Marco, this tool is really useful.

I wonder if you can help me.

I have two hierarchy defined: Year-Month-Day and Year-Week-Day.

While I was able to extend the example using two different datetool dimension (Datatool and Weekly Datetool), I wonder why the scope on different hierarchy does not work (as i suppose it must work).

i.e., also if I have two different scope blocks:

SCOPE([Time].[Y-M-D].Members,[Time].[Daykey]..Members);

and

SCOPE([Time].[Y-W-D].Members,[Time].[Daykey]..Members);

the measures are incorrect (the second scope screw up the first scope).

Do you have any suggestion?

June 20, 2010 3:44 AM
 

Marco Russo said:

Giancarlo,

the DateTool I published doesn't have two hierarchies on Date time. However, I implemented it for some customers.

I just remember that there is a complex issue of dependency among attribute relationship. You don't have to define two DateTool dimensions, it is sufficient having one that adapts its behavior to the existing Date dimensions.

Let me know if it helps - I cannot publish the work I done and I don't have time now to reproduce it with AdventureWorks (I need to finish the book about PowerPivot I'm writing).

Marco

June 20, 2010 6:29 AM
 

Giancarlo said:

Thanks, Marco.

It is this interdependency that make me crazy.

Ate the end I have decided to use two different dimension due  time constraints.

The funny stuff is that the previous year works fine.

It is the YTD that fails (and also a measures that I added, the previous period).

I have also tried to remove that dependency, scoping to just month and week (I do not need days) but without results.

If you can give me some hints, I will send you the adventureworks with this extension.

June 21, 2010 1:35 PM
 

Marco Russo (SQLBI) said:

Giancarlo,

write me an email to marco (dot) russo (at) sqlbi (dot) com

I'll send you some MDX that might be helpful (it is not something I can publish here, it's not English and not generic as it should be).

Marco

June 21, 2010 6:23 PM
 

Luis said:

Amazing serendipity! I too am struggling to get relative date calcs to work with Yr-Mo and Yr-Wk hierarchies, and eventually with multiple date dimensions (they are role playing). Working with SCOPE is not at all what I imagined...there is not much info I could find to look at just to get an idea of how to structure the scoping...I  have tried multiple approaches...I even tried having an outer scope for just the calculation member and inner scopes for the Wk and Mo hierarchies...I am actually getting data but the YTD on one of them is terribly overstated. I have also extended your solution to have not only the aggregation and comparison members but one that allows them all to be put on one line (hence the reference to 'unified'.

Any ideas on this?

SCOPE([Date Calculations].[Unified].[Year To Date]);

//scope for Fiscal Yr-Mo

SCOPE ([Date of Appointment].[Fiscal Week Hierarchy].[all],

 EXCEPT(DESCENDANTS([Date of Appointment].[Fiscal Yr Hierarchy].[all]

   ,[Date of Appointment].[Fiscal Yr Hierarchy].[Fiscal Date], SELF_AND_AFTER )

,[Date of Appointment].[Fiscal Yr Hierarchy].[all] ));

 THIS = aggregate(

     { [Date Calculations].[Unified].DefaultMember } *

     PeriodsToDate(

        [Date of Appointment].[Fiscal Yr Hierarchy].[Fiscal Year],

        [Date of Appointment].[Fiscal Yr Hierarchy].CurrentMember

     )

 );

END SCOPE;

//scope for Fiscal Yr-Week

SCOPE ([Date of Appointment].[Fiscal Yr Hierarchy].[all],

 EXCEPT(DESCENDANTS([Date of Appointment].[Fiscal Week Hierarchy].[all]

   ,[Date of Appointment].[Fiscal Week Hierarchy].[Fiscal Date], SELF_AND_AFTER )

,[Date of Appointment].[Fiscal Week Hierarchy].[all] ));

 THIS = aggregate(

     { [Date Calculations].[Unified].DefaultMember } *

     PeriodsToDate(

        [Date of Appointment].[Fiscal Week Hierarchy].[Fiscal Year],

        [Date of Appointment].[Fiscal Week Hierarchy].CurrentMember

     )

   );

END SCOPE;

END SCOPE;

Thanks,

Luis

June 22, 2010 2:27 PM
 

Marco Russo said:

Luis,

as I said to Giancarlo, if you write directly to me I will send you some MDX that is not generic enough to be published - I'll prepare an updated version of DateTool one day, but in this period I don't have time to.

Marco

June 22, 2010 5:30 PM
 

Giancarlo said:

Thanks Marco.

I find a way to solve the issue. I am investigating why, while i am able to drill down to day in month hierarchy, i am not to able to drill down to Day of the week in the week hierarchy.

When I fix this issue, if you are interested,I will send you an updated version. Your comments are always appreciated.

June 23, 2010 1:49 AM
 

Marco Russo said:

Giancarlo, sure send me the updated version by email.

Thanks!

Marco

June 23, 2010 2:04 AM
 

Giancarlo said:

No thank to you Marco.

To share your knowledge with us.

I already sent you the week version with all the comments and changed.

But i forgot to write you that I have changed the deployment database to not screw up the original.....

June 23, 2010 5:58 AM
 

Giancarlo Ferrara said:

Very Strange.

Some days ago I have changed the implementation of Date calculation with this implementation (before it was already with calculated members but not working with Excel 2007.

Today the customer called me that a set of Excel worksheets were not working anymore, failing with a message like 'cube or dimensions where no more accessible, contact your system administrator'.

All these worksheets had 4 or 5 attributes of the same dimensions + one attribute of an other dimension and just one measure. No time calculation (i.e. Actual value).

When I have executed the some MDX directly, i.e. i executed this:

SELECT NON EMPTY CrossJoin(CrossJoin(CrossJoin(CrossJoin(CrossJoin(Hierarchize(AddCalculatedMembers({DrilldownLevel({[Clienti Spedizione].[Canale].[All]})})), Hierarchize(AddCalculatedMembers({DrilldownLevel({[Clienti Spedizione].[Cod CDG].[All]})}))), Hierarchize(AddCalculatedMembers({DrilldownLevel({[Clienti Spedizione].[Desc CDG].[All]})}))), Hierarchize(AddCalculatedMembers({DrilldownLevel({[Clienti Spedizione].[Cod Cliente].[All]})}))), Hierarchize(AddCalculatedMembers({DrilldownLevel({[Tipi Transazioni].[Sistema Origine].[All]})}))), Hierarchize(AddCalculatedMembers({DrilldownLevel({[Clienti Spedizione].[Cliente].[All]})}))) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS  FROM (SELECT ({[Data Competenza].[Anni].&[2009],[Data Competenza].[Anni].&[2010]}) ON COLUMNS  FROM [FioritalDWH]) WHERE ([Analisi].[Analisi].&[Report Vendite],[Measures].[Quantita KG - Transazioni])

i receiveth the following error:

The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.

- I have not any security based on any measure;

- If I remove the datetool calculations it works;

- If i change the order of crossjoin, leaving all the Customers attributes ("Clienti" in italian) in sequence and moving "[Tipi Transazioni].[Sistema Origine].[All]" as first or last element of the list (the first excel column or the last excel column)it works

Someone has any ideas why?

Giancarlo

July 6, 2010 4:10 PM
 

Douglas said:

Hello Marco

Thank you for sharing your knowledge.

I have got a question that make me think a lot during the last weeks.

I managed to use the DateTool (aggregation and comparison) and fit them into my needs, basically I extended the solution by adding currency calculation to the Aggregation dimension, I have done this because in the company I work for we have two formulas for exchange rates, one is for the monthly data and other for the YTD data, so the DateTool solution fixed nicely my troubles.

Here comes the ugly thins, most of the members of the Aggregation dimension works seamlessly but YTD, yes the simple YTD member does not work while the YTD_EUR (accumulated ytd at the accumulated average exchange rate) works without troubles.  Can someone explain why could this happen?

Best regards

--

Douglas

July 30, 2010 10:22 AM
 

Michael D. said:

Marco,

Thanks for your help on my earlier issue, unfortunately, I have run across another one.

The client's security requirements has resulted in use needing to add cell level security.  We have a security table that lists the dimensions for only those people who need security.  We then use a count measure for the cell level security:

( STRTOMEMBER( "[User].[Network Username].["+Username() + "]"), [Measures].[Fact User Security Count] ) > 0

once this applied, the [Diff. Over Previous Year] member retruns N/A.  testing the scope, I have tried to use basic math ( 1+1, 1-1, 10-1, 1-10 )and have discovered that any number that is zero or negative returns N/A while positive numbers appear correct.

Do you have any thoughts on what may be causing this?  Thanks.

August 2, 2010 2:54 PM
 

Michael D. said:

Marco,  I just wanted to let you know that a solution was found (with the help of Microsoft's escalation team.  We had to change our cell security to:

[Users].[Network User ID].currentmember = STRTOMEMBER("[Users].[Network User ID].&["+ UserName () +"]")

I thought I would post in case anyone else runs into the problem.

August 19, 2010 10:17 AM
 

Marco Russo (SQLBI) said:

Thanks Michael for the update - I'm very late on other projects and I didn't have time to make some test.

Nice to know you already solved the issue.

Marco

August 19, 2010 10:22 AM
 

Thara said:

Need help. We are trying to create calculation for Last 6 months/Last 3 months. Time dimension hierarchy is Year-Quarter-Month-Date. Using the below formula.

( [DateTool].[Aggregation].[Last 6 Months] )

         = Aggregate(

                    { [DateTool].[Aggregation].DefaultMember } *

                    { ParallelPeriod(

                                      [Post Date].[Calendar].[Month],

                                      6,

                                      [Post Date].[Calendar]

                      ).NextMember : [Post Date].[Calendar].CurrentMember

                    } )

The values are showing up correctly if we drill down to Month level, but at the year level & quarter level, it’s just summing up all the descendants/children values.

Is there anything that I’m missing? Please suggest.

September 8, 2010 2:47 PM
 

Nick said:

Thanks a ton!  Is there a way to do this in PowerPivot with DAX?

September 27, 2010 4:59 PM
 

Marco Russo said:

Not exactly. In the current version of PowerPivot, you might define a "tool dimension", but each DAX formula must be aware of that and it is not possible to write a "generic" YTD formula that is automatically applied to any existing measure, like you can do in SSAS by using MDX Script.

September 27, 2010 6:10 PM
 

McLoven said:

Marc, great work on the many-to-many solution.  I'm using your cross-time example to group my customers based on a monthly snapshot fact table.  Very flexible approach to group my customers, but I want to combine your date tool example together in the same cube.  When I apply the comparison to the results of the cross-time, ka-plooe!  YOY difference results are not based on the date snapshot role dimension, but looks like it just uses the date dim only.  I'm guessing I have to tweek the mdx to reflect that?  

Given the introduction of PowerPivot, do you recommend it as an approach instead?

Thanks M.

October 11, 2010 6:08 PM
 

Marco Russo (SQLBI) said:

Probably the MDX Script should be fixed to work in such scenario. Unfortunately I don't have so much time in this period and I already have a long pipeline of questions about DateTool to be answered!

In case you need an help, the best thing would be to reproduce the issue with a simple project and then sending it to me. You can reach me at marco(dot)russo(at)sqlbi[dot]com. I cannot promise to anwers soon (paid engagements have the precedence) but sooner or later I will do it.

October 11, 2010 6:16 PM
 

Patrik said:

Hi,

Thanks for a excellent tool. Our users want to use the value filter "top 10" in excel. But it seems that it doesn't work

with the R12 value. Instead is use the current month value insted.

Is there a workaround for this problem?

Thanks Patrik

December 29, 2010 10:27 AM
 

Marco Russo (SQLBI) said:

December 29, 2010 10:50 AM
 

Helmut Knappe said:

Hi Marco,

thank you for your DateTool - trying to implement at a client. They need various previous year values and multi-select capability as well. However for multi-select on the Date dimension like in the following query:

select [DateTool].[Comparison].members on columns,

[Product].[Product Categories].[Category].members on rows

from [Adventure Works]

where {[Date].[Calendar].[Quarter].&[2003]&[1],[Date].[Calendar].[Quarter].&[2003]&[2]}

we keep getting errors with the Currentmember function. Do you have a solution for this situation as well?

Helmut

March 19, 2011 4:49 PM
 

Marco Russo (SQLBI) said:

The multi-select is not compatible with my implementation for DateTool. The problem cannot solved easily. You might support some calculation, but it's very hard to support the cross-attributes (i.e. month on rows and years on columns) with multi-select in the where condition.

I never tried the complete implementation - I suppose there is a way to make it working, but you have to evaluate all the possible cases. Removing cross-attributes support would make it easier to implement.

Marco

March 19, 2011 7:26 PM
 

Helmut Knappe said:

Hi Marco,

thank you for your fast response. I thought that there would be a way following Mosha's suggestions http://www.mosha.com/msolap/articles/mdxmultiselectcalcs.htm, using Existing to go through the selected members and aggregate them. So I tried

SCOPE (

   [Date].[Calendar].MEMBERS,

[Date].[Date].MEMBERS );    

/////////////////////////////////////////////////////////////////////

   ( [DateTool].[Comparison].[Previous Year],

     [DateTool].[Aggregation].Members )

         = Aggregate ( Existing ( [DateTool].[Comparison].DefaultMember,

             ParallelPeriod( [Date].[Calendar].[Year],

                             1,

                             [Date].[Calendar].CurrentMember ) ) );

but it did not help. Did I make a mistake here, or why does this not work?

Thanks, Helmut

March 20, 2011 11:54 AM
 

Marco Russo (SQLBI) said:

As I said, the problem is more complex - you should make an evaluation case by case for each measure.

Sorry, no time to check it deeply by now! :)

Marco

March 20, 2011 2:40 PM
 

Helmut Knappe said:

Hi Marco,

rereading Mosha's blog I have changed the calculation to

SCOPE (

  [Date].[Calendar].MEMBERS,

[Date].[Date].MEMBERS );    

/////////////////////////////////////////////////////////////////////

  ( [DateTool].[Comparison].[Previous Year],

    [DateTool].[Aggregation].Members )

        = Aggregate ( Existing

          [Date].[Date].MEMBERS ,

          ( [DateTool].[Comparison].DefaultMember,

            ParallelPeriod( [Date].[Calendar].[Year],

                            1,

                            [Date].[Calendar].CurrentMember ) ) );

This seems to work for the Date hierarchy ...

select [DateTool].[Comparison].members on columns,

[Product].[Product Categories].[Category].members on rows

from [Adventure Works]

where ([Date].[Date].&[550]:[Date].[Date].&[650])

... gives the correct results.

Thanks, Helmut

March 27, 2011 10:38 AM
 

Marco Russo (SQLBI) said:

Nice - have you tested it by crossing month/years in rows/column?

Just to know if we might try to consider it as a general solution.

Marco

March 27, 2011 5:09 PM
 

Helmut Knappe said:

Hi Marco,

now it seem that I found something more generic:

SCOPE ( [Date].[Calendar].MEMBERS );    

   /////////////////////////////////////////////////////////////////    ( [DateTool].[Comparison].[Previous Year],

     [DateTool].[Aggregation].Members )

         = Aggregate ( Existing  [Date].[Calendar].[Date].MEMBERS,

           ( [DateTool].[Comparison].DefaultMember,

             ParallelPeriod( [Date].[Calendar].[Year],

                             1,

                             [Date].[Calendar].CurrentMember ) ) );

   /////////////////////////////////////////////////////////////////    ( [DateTool].[Comparison].[Previous Date],

     [DateTool].[Aggregation].Members )

         = Aggregate ( Existing  [Date].[Date].MEMBERS, ( [DateTool].[Comparison].DefaultMember,

             [Date].[Date].Currentmember.Lag(370) ) );  

END SCOPE;  

This works for the following query in all variants of WHERE clauses:

select [DateTool].[Comparison].members on columns,

[Product].[Product Categories].[Category].members on rows

from [Adventure Works]

where

--([Date].[Date].&[550]:[Date].[Date].&[650])

--{[Date].[Calendar].[Quarter].&[2003]&[1],[Date].[Calendar].[Quarter].&[2003]&[2]}

{[Date].[Calendar].[Month].&[2004]&[1],[Date].[Calendar].[Month].&[2004]&[2]}

Interestingly compared to the previous version I have left off the [Date].[Date].MEMBERS part of the Scope and did only use

[Date].[Calendar].[Date].MEMBERS in the Existing Set.

still it does do the correct calculation in the

where ([Date].[Date].&[550]:[Date].[Date].&[650])

Is this because the Date.Date attribute points to the same cells as the [Date].[Calendar].[Date] level?

Helmut

April 2, 2011 3:43 PM
 

Marco Russo (SQLBI) said:

What's the rationale of Lag(370) ?

April 2, 2011 6:39 PM
 

Helmut Knappe said:

Lag(370) refers to a different scenario, in which the user requires a custom previous year date which cannot be calculated using ParallelPeriod. Hence I tested the Lag function for this purpose.

Helmut

April 3, 2011 2:43 PM
 

Marco Russo (SQLBI) said:

Ok thanks - sooner or later I will work on a new release of the DateTool and I will check your suggestion too.

Thanks,

Marco

April 3, 2011 5:16 PM
 

Mary Pettit said:

Hi Marco,

First I want to thank you for the DateTool.  A lot of time and effort has been spent creating it.  Your Expert Cube Development Book is excellent!  The issue I have is how to implement a calculation using Date Tool that will give me the prior year up through the same day as current year using SSAS 2005.  I am able to get YTD for the entire prior year but don't know how to limit it to a specific date in a fiscal calendar.  If needed, I can calculate Day of Year for the date in my ETL tool if that would work better.  What function(s) should be used and how should the MDX be constructed?  Any help would be greatly appreciated.

Thanks much,

Mary

April 26, 2011 12:33 PM
 

Marco Russo (SQLBI) said:

Mary,

thanks for the compliments!

You should use PeriodsToDate instead of Ytd function in MDX.

Take a look at the help and let me know if you need further help:

http://msdn.microsoft.com/en-us/library/ms144925.aspx

Marco

April 26, 2011 3:39 PM
 

Nagaraju E said:

Hi Marco,

I am experiencing issue with the same kind of above scenario. As posted in the 3rd screenshot above in your tutorial, suppose if my measure not having any value for current date for a particular quarter and if it has value under Year to Date for may be other quarter. In that case if you want to hide the quarter for those situations, how can you apply f

ilter on that.

I have created calculated members on top of 1 field, the detailed explaination i have posted at

http://nagaraju-dwh.blogspot.com/2011/06/filtering-records-using-named-set.html

can you please go through this and suggest me. I am desperately needed some help for the above issue

June 14, 2011 3:02 AM
 

Marco Russo (SQLBI) said:

You have at least two options:

1) include your "aggregate(...)" calculation in a IIF statement that check the value of your current measure, returning NULL in case the current date is NULL too

2) create a further SCOPE that override with NULL all the dates that shouldn't be displayed - this could be faster, the use of IIF in step 1) may affect performance.

Marco

June 14, 2011 4:10 AM
 

Nagaraju E said:

Marco,

Thanks for the suggestion. I tried first one it worked, but consuming some what more time in generating the data.

I need your help in trying the 2nd suggestion. Can you please help me in modifying the SCOPE statement to override the NULL. My SCOPE statement is as follows

Scope(

      {

        [Measures].[Budget Amount - Fact Budget PL],

        [Measures].[Budget Percentage Sales],

        [Measures].[Actuals],

        [Measures].[Actual Percentage Sales]

      }

)

June 14, 2011 7:16 AM
 

Marco Russo (SQLBI) said:

I would need the whole model to write the exact formula - in general you should write something like (replacing the date member with something that in your cube correspond to the current day):

Scope(

     {

        [Measures].[Budget Amount - Fact Budget PL],

        [Measures].[Budget Percentage Sales],

        [Measures].[Actuals],

        [Measures].[Actual Percentage Sales]

      },

    Date.Date.&[20110614] :  NULL

);

THIS = NULL;

END SCOPE;

June 14, 2011 7:29 AM
 

Nagaraju E said:

Marco,

Below are the calculated members i have written

-------------------------------------------------------------

               ( [Dim Calendar].[Year -  Period Dim Calendar Calculations].[Last Year],    

   [Dim Calendar].[Year].[Year].Members ) =

IIF(  ISEMPTY([Dim Calendar].[Year -  Period Dim Calendar Calculations].DefaultMember), NULL,

Aggregate(

            { [Dim Calendar].[Year -  Period Dim Calendar Calculations].DefaultMember } *

       ParallelPeriod(

                           [Dim Calendar].[Year -  Period].[Year],1,

                           [Dim Calendar].[Year -  Period].CurrentMember

            )

)

)

-------------------------------------------------------------

               ( [Dim Calendar].[Year -  Period Dim Calendar Calculations].[Last Year To Date],    

   [Dim Calendar].[Year].[Year].Members ) =

IIF(  ISEMPTY([Dim Calendar].[Year -  Period Dim Calendar Calculations].DefaultMember), NULL,

 Aggregate(

            { [Dim Calendar].[Year -  Period Dim Calendar Calculations].DefaultMember } *

     YTD(  ParallelPeriod(

                           [Dim Calendar].[Year -  Period].[Year],1,

                       [Dim Calendar].[Year -  Period].CurrentMember

            )

)

)

)

-------------------------------------------------------------

 ( [Dim Calendar].[Year -  Period Dim Calendar Calculations].[Year to Date],    

   [Dim Calendar].[Year].[Year].Members ) =

IIF(  ISEMPTY([Dim Calendar].[Year -  Period Dim Calendar Calculations].DefaultMember), NULL,

 Aggregate(

            { [Dim Calendar].[Year -  Period Dim Calendar Calculations].DefaultMember } *

            PeriodsToDate(

                           [Dim Calendar].[Year -  Period].[Year],

                           [Dim Calendar].[Year -  Period].CurrentMember

            )

 )

)

-----------------------------------------------------------------

I have only above 3 calculated members and 1 SCOPE statement as mentioned in my last post.

June 14, 2011 8:13 AM
 

Nagaraju E said:

Marco,

Can you help me overwriting the scope statement with NULL value. I have posted the calculated members and scope statment above.

And my dim calender leaf level is based on month not on day.

June 20, 2011 2:24 AM
 

Marco Russo (SQLBI) said:

If you want to convert a 0 into a NULL, you can simply write an IIF statement, like IIF( Measures.MyCalc = 0, NULL, Measures.MyCalc), but this might affect performance.

Another technique is to hide 0 by using FORMAT_STRING, which is faster even if it doesn't work with all the clients (but it should be working with Excel 2007 or higher versions), like FORMAT_STRING = "#;#;;" (see documentation for complete syntax).

June 20, 2011 4:46 AM
 

Nagaraju E said:

Marco,

As suggested by you above i have included my aggregate statement inside IIF and it works.

Consider if my measure getting drilldown at 2 levels and if i want to check nulls only for 2nd level, how can i do that

June 30, 2011 7:09 AM
 

Marco Russo (SQLBI) said:

You can write a SCOPE that only operates at the 2nd level of a regular hierarchy (not a parent-child one) and in the scope you can define the FORMAT_STRING desired.

June 30, 2011 2:47 PM
 

Roman said:

Hello,

Thanks for your work.

I have 5 Time dimensions in my cube. How can I use DateTool for all of them? Should I to create calculated dimension for each of them and do a typing in Calculations using each CUBE dimension (one Time dimension is in base)?

Regards,

September 12, 2011 12:04 PM
 

Marco Russo (SQLBI) said:

Roman, the problem is how to recognize which time dimension is used and to which you want to apply the DateTool dimension.

The easiest way is to create 5 DateTool dimensions, one for each Time dimension, but this would result very hard to use for the end user.

Another approach is to define a priority for the time dimensions, so that you apply the DateTool to the first dimension if a selection is made there, otherwise test the second and so on. However, this approach seems hard to implement and might have sever performance issues.

In general, I would ask why there are 5 Time dimension in a cube. In case you have 5 dates, you might want to build one visible Time dimension, one DateTool dimension that is extended with one attribute that selects which Time dimension you really want to use. This would avoid the ambiguity of more than one Time dimensions used - at the same time, this might be a limitation you don't want to have.

It really depends on your requirements...

Marco

Marco

September 12, 2011 12:10 PM
 

Roman said:

My structure is

Time (SLA Delivery Time)

Time (Expected Delivery Time)

Time (Profiled Time)

Time (Completed Time)

Time (Actual Delivery Time)

Is there a way to determine which Dimension is selected in another axis and use it in calculations?

Thanks for quick responce.

September 12, 2011 1:05 PM
 

Marco Russo (SQLBI) said:

You should take a look at the AXIS() function.

Tomas Piasevoli has a chapter of his book dedicated to this:

http://www.amazon.com/gp/product/1849681309/?tag=se04-20

Marco

September 12, 2011 1:59 PM
 

MartinIsti said:

I've just recently begun applying this kind of time intelligence logic in my solutions and today I found 2 annoying issues. One I was able to solve the other I cannot yet.

1) let's say you have 2 normal (not calculated) measures: Revenue and Profit. You create a calculated measure:

[Measures].[Profit %] = [Measures].[Profit] / [Measures].[Revenue]

Easy and straightforward. Then you'd like to see the value of it for the previous year so you define a ParallelPeriod member in the DateTool dimension and Scope it for all the MeasureGroups you have by this expression:

Scope({

        MeasureGroupMeasures("Measure Group 1 Name")

       ,MeasureGroupMeasures("Measure Group 2 Name")

   })

The problem is that even if you define a Measure Group for the calc it's just not included. And if you define a separete scope for that then the previous ones are not valid any more. Putting all of them into one set still don't work. After some fruitless research on the net and tries by myself it seems that this is the solution for this (using the fact that [Measures] behaves very much like a dimension:

  Scope({[Measures].AllMembers})

2) I have a [Calendar] hierarchy in my Date dimension and I want to limit the scope for the DateTool calcs so that a previous corresponding period (e.g. it is December 2010 for December 2011) calculation doesn't show up for future dates (since this month's /Dec 2011/ value is next Decembers previous). I haven't managed to implement that filter/scope. Because if I use this

 Scope(NULL:StrToMember('[Date].[Calendar].[Month].&[' +

 Format(Now(),"yyyyMM") + ']'));

then I'll miss the Year level because it limits the scope to the Month level of the hierarchy. If I want to have a scope for the Year level then it's the same dimension so it can't be done.

There should be a simple solution (I hope) I think I just lose the thread somewhere and overcomplicate it instead of going simple.

If any of the above details are not clear enough due to my incorrect composition of the problem just let me know and I rephrase it!

It would be good to find a solution for 2) because it looks pretty bad in PerformancePoint to have data for next December in a report... currently I limited the my source data table in a view to have data only up to today but it's possible to have a fact table that contains bookings for future dates.

December 15, 2011 5:03 PM
 

Raj said:

I am using time intelligence and one of the challenge is to calculate working day based on Region.

When I se following it works, but then wherever there is no value for measure it returns null.

([Time Calculations].[Time Calculations].[Non-Holiday Count]=

       Sum(

          ([COB DATE SGK].[DATE SGK].[DATE SGK]),

          IIf(NOT ISEMPTY(([RGN HLDY FLAG SGK].[IS BUSINESS DAY].&[1.],[Time Calculations].[Current Date])),1,Null)));

Alternatively to ignore dimension level lookup, I am using default member, but it crashes if I use big dimension like front office.

([Time Calculations].[Time Calculations].[Non-Holiday Count]=

       Sum(

          ([COB DATE SGK].[DATE SGK].[DATE SGK]),

          IIf(NOT ISEMPTY(([RGN HLDY FLAG SGK].[IS BUSINESS DAY].&[1.][Front Office].[FO Location Hierarchy].Defaultmember,[Time Calculations].[Current Date])),1,Null)));

December 23, 2011 1:29 PM
 

Marco Russo (SQLBI) said:

Raj, I'm not sure about the model you used, but for the scenario you describe the best thing would be a separate measure group with Date and Region dimensions - one row for each region and date containing just 1 for non-holiday days, and you would get the working days with a simple tuple without using the SUM function.

December 24, 2011 12:06 AM
 

Paul Wallington said:

Hi Marco,

This is a great article. I am currently using the original example in the post (where the Date Calculations are greyed out in Excel).

I have compared this to your process, so have a couple of observations:-

Neither techniques work well for Role Playing Date Dimensions (in that multiple Date Tools will need to be created for each Role Playing Dimension)

Your technique doesn't appear to work at all with Many-to-One Currency Conversions.

Have you had any experience in applying your technique to either Role Playing Dimensions or Currency Conversions ?

Thanks

Paul

March 5, 2012 6:42 AM
 

Marco Russo (SQLBI) said:

Paul,

the problem with role-playing dimension is establishing on which dimension DateTool should work. Once the rule is defined, it has to be implemented in MDX (not an easy task, but it can be done).

Probably some other arrangement can be done to support Currency Conversions, but I never had to do that, so I don't have a ready to use example.

Thanks,

Marco

March 5, 2012 2:51 PM
 

Jason said:

When writing a query how do you seperate measures that you only want for a single period (current) and those that you want for all time periods.  This is something that we do on nearly every report we write, but measures like POS store counts are really only useful the current week.  They are also very expensive to generate, so if there is an easy way to keep them from being generated for all selected time periods that would be great.  

March 20, 2012 3:23 PM
 

Marco Russo (SQLBI) said:

My first guess is using SCOPE to activate measures only for subsections of the cube that makes them relevant (ie certain hierarchies or part of them).

March 24, 2012 2:35 AM
 

Jason said:

Is there a way to overwrite the scope on the aggregations?  I tried the code below, but it doesn't work.  This is a problem when for some measures we want an aggregate, and others we want the value 4 periods ago.  I really like how this solution works, but it's a deal killer if I can't get this worked out.

------------------------------------------------------------------------

[Time Tool].[Aggregation].[Last 4 Periods] =

  Aggregate

   (

       {[Time Tool].[Aggregation].DefaultMember}

     *

       {LastPeriods(4,[time].[Retailer].CurrentMember)}

   );      

------------------------------------------------------------------------

CREATE

 MEMBER CURRENTCUBE.[MEASURES].[Valid Store Count] AS NULL

  ,FORMAT_STRING = "#,##"

  ,ASSOCIATED_MEASURE_GROUP = 'Store Level Weekly' ;

SCOPE

 [Valid Store Count];

 SCOPE

  [Time Tool].[Aggregation].[Aggregation].[Current]

;

   THIS =

     Sum([Measures].[Replenishable]);

   SCOPE

     [Time Tool].[Aggregation].[Aggregation].[Last 4 Periods];

     THIS =

       Sum

       (

         [Time].[retailer].CurrentMember.Lag(3)

        ,[Measures].[Replenishable]

       );

   END SCOPE;

 END SCOPE;

END SCOPE;

April 9, 2012 4:11 PM
 

Gary Sklar said:

Marco:  This works well for Excel.  However I get the same values for both Regular and Year To Date when querying from SSMS or SSRS.  Can you please provide a suggestion to address this.  Thanks so much.

April 20, 2012 4:29 PM
 

Marco Russo (SQLBI) said:

It should work - I used it in several projects, I don't know what isn't working in your case.

April 21, 2012 6:39 AM
 

Gary Sklar said:

Being fairly novice, I was including the DateTool attribute in the tuple.  I was able to capture the query from Excel and saw that DateTool was being cross joined.

April 27, 2012 5:20 PM
 

David Laplante said:

Hello Marco,

Thanks for sharing your knowledge!  I have tried to implement you DateTool dimension in my financial cube although in a simple scale.  In Aggregations, I have defined 3 members: [Regular], [Opening Balance] and [Closing Balance].

Opening and closing balances are a life-to-date like so:

SCOPE ([Opening Balance]);

   This = Aggregate({NULL:[Calendar].[By Calendar Year].CurrentMember.PrevMember}, [Measures].[GL Transaction Amount]);

END SCOPE;

and:

SCOPE ([Closing Balance]);

   This = Aggregate({NULL:[Calendar].[By Calendar Year].CurrentMember}, [Measures].[GL Transaction Amount]);

END SCOPE;

It is working fine however, I'm getting very bad response time and my cube is fairly small with only 2 years of data and fact table containing 450 000 rows.

Creating the same calculations as calculated measures and using the same calculation as in the datetool dimension produces query responses below 1 second.

Is there anything that I am missing? Have you ever tried using your DateTool dimension to get Life-To-Date values ?

May 7, 2012 8:09 PM
 

Marco Russo (SQLBI) said:

Have you tried using

SCOPE ([Opening Balance]);

  This = Aggregate({NULL:[Calendar].[By Calendar Year].CurrentMember.PrevMember}, ([Measures].[GL Transaction Amount], [Regular]));

END SCOPE;

SCOPE ([Closing Balance]);

  This = Aggregate({NULL:[Calendar].[By Calendar Year].CurrentMember}, ( [Measures].[GL Transaction Amount], [Regular] ) );

END SCOPE;

May 23, 2012 4:40 PM
 

hDt said:

hi Marco.

my sales calendar is a bit tricky, as it caters for leapyears by adding a week 53 about every 5 years. 'out of the box' your solution works great, except for these weeks, as it obviously cannot find week 53 for Last Year.

I cannot use parallelperiod on the Year level, for eg, week 53 for year 2012 actually uses week 1 of 2012 when comparing with 'lastyear'...

adding the extra week is reported on by lagging 52 weeks always (well I actually have a datelastyear column in my source table, but it always works out at 365 days difference, or 52 weeks) so I got the comparison part to work by using the week level in parallelperiod and changing the scope as follow:

SCOPE (

   [Date].[Sales Calendar].[Week].MEMBERS,

   [Date].[Date].MEMBERS );        

   ///////////////////////////////////////////////////////////////////////////////////////

   ( [DateTool].[Comparison].[Previous Year],

     [DateTool].[Aggregation].Members )

         = ( [DateTool].[Comparison].DefaultMember,

             ParallelPeriod( [Date].[Sales Calendar].[Week],

                             52,

                             [Date].[Sales Calendar].CurrentMember ) );      

END SCOPE;    

The above works fine when using DateComparison on its own. And if I use DateAggregation on its own it also works fine. But when I browse my cube using both at the same time then the aggregations become inaccurate, and I'm assuming it's because of the difference in scope?

I've tried many different ways to try align the scopes but cannot get them to cater correctly for this extra week.

thanks for any help.

July 2, 2012 3:17 AM
 

Marco Russo (SQLBI) said:

It's hard to help you in this scenario without having the complete data model and MDX script - and even in this case, this would require a lot of time that it's hard to find in my spare time! :)

You should make some test by comparing the result isolating other parts of your MDX Script and trying to understand how the calculation is done when you intersect Comparison and Aggregation in the DateTool dimension.

July 4, 2012 1:49 PM
 

Rajakanna said:

Hi, We have implemented this solution in SSAS 2008 which works fine with Excel 2007, but the calculated members are greyed out out in Excel 2010. Any advice will be really helpful. Thanks.

July 25, 2012 6:26 AM
 

Thorsten said:

Hi,

I'm not so familar with MDX. Cane someone explain the follwing part of the script to me:

//

// CROSS QUARTERS

//

SCOPE ( [Date].[Quarter of Year].[Quarter of Year].Members,

       [Date].[Quarter].[All],

       [DateTool].[Aggregation].AllMembers,

       [DateTool].[Comparison].AllMembers );  

   ///////////////////////////////////////////////////////////////////////////////////////

   // It should be scoped do:

   // SCOPE ( { [DateTool].[Aggregation].[Year To Date], [DateTool].[Aggregation].[Last 12 Months] } );

   // but since it is not supported, we use the Except function

   SCOPE ([Date].[Year].[Year].Members,

          Except( [DateTool].[Aggregation].Members, {[DateTool].[Aggregation].DefaultMember} ) );

       THIS = Root( StrToMember( "[Date].[Quarter].&["

                                 + [Date].[Year].CurrentMember.Properties( 'Key' )

                                 + "]&["

                                 + [Date].[Quarter of Year].CurrentMember.Properties( 'Key' )

                                 + "]" ) );

   END SCOPE;

   ///////////////////////////////////////////////////////////////////////////////////////

   // The FREEZE is necessary to fix the result against possible modifications made by script

   // for lower levels of attribute hierarchy

   FREEZE;

END SCOPE;

July 1, 2013 9:04 AM
 

Marco Russo (SQLBI) said:

@Rajakanna: the problem is described here:

http://sqlblog.com/blogs/marco_russo/archive/2007/03/07/ssas-2005-sp2-breaks-excel-calculated-member-selection.aspx

It has been fixed in Excel 2010 + SQL Server 2008 R2

@Thorsten: I suggests you this book: http://www.amazon.com/gp/product/0471748080/?tag=se04-20

July 17, 2013 9:29 AM
 

Meik Truschkowski said:

The DateTool is a cool feature, but after implementing my drilltrough actions (made the ExecuteDrillthroughAndFixColumns function of the Analaysis Services Stored Procedure Project from Codeplex http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home) do not work anymore. Each drillthrough row seems to be “joined” with both unrelated dimensions (aggregation and comparison) and (4 * 4)² = 256 rows are returned instead of a single one. Is it a known problem, or am I the first who combined these two approaches? Is there a workaround?

August 26, 2013 1:55 AM
 

Meik Truschkowski said:

My problem occures on every drillthrough, even the "default" drillthrough (double-click in Excel) where there is no specific action defined and no function used!

August 26, 2013 2:31 AM
 

Marco Russo (SQLBI) said:

Meik,

the drillthrough cannot work properly with unrelated dimensions, but it seems strange to me that it duplicates the returned values in the default drillthrough. Unfortunately I don't have the time to test it now.

Let me know your findings, thanks.

Marco

August 26, 2013 4:12 PM
 

Meik Truschkowski said:

Hi Marco,

Seems, that the problem can be solved by setting "IgnoreUnrelatedDimensions" to "True" for all measuregroups where a drillthrough is performed on. At least that works, or are you expecting other side effects?

Regards, Meik

August 27, 2013 5:56 AM
 

Marco Russo (SQLBI) said:

No, I don't expect side effects - I will investigate more on it if I will find the time!

Thanks for the feedback!

August 27, 2013 4:51 PM
 

Dejan Milic said:

"Not a good thing - having IsAggregatable set to False might have a lot of side effects. I didn't tested MDX queries of DateTool in such a scenario."

Hi Marco, I confirm that this has a bad side effect with one of the client tools we are using. It adds a hidden grand total measure which calls an AGGREGATE() function on the datetool dim attributes

Regards, Dejan

October 3, 2013 10:52 AM
 

Marco Russo (SQLBI) said:

Hi Dejan, thanks for the feedback!

October 3, 2013 11:23 AM
 

Nick said:

Hi all,

I'm not that experienced with MDX, but yet....

I'm a bit confused. I use one time dimension with two simple hierarchies and implemented the date tool way and the traditional (create members) way.

Where the traditional approach works fine for both hierarchies (regardless off the non filter drawback), the date tool way only scopes to the last hierarchy scoped in my script. Although the calculations seem correct for this hierarchy, why is it neglecting the first scoped hierarchy. When I delete the latter hierarchy the first one works fine again.

The traditional way does this fine and I use the same scopes for the date tool way. Yet to no avail. The calculations are only calculated against my last scoped hierarchy. Does anyone know why this is happening ?

Regards,

Nick

October 25, 2013 11:19 AM
 

Vidya said:

Hi Marco,

This is an excellent solution. An Engineer in our team had implemented this solution in 2010. However, now we are seeing major performance issues with it.

It's very slow when we pivot on various dimensions. Also we use 2 types of Dates(e.g. Business Date, Booked To Date) along with this Time Interval Dimension.

I was wondering if it is possible to link this to the Fact Table and Partition by the Aggregations?

Or do you have any suggestions on how I could improve the performance. It's taking about 10 min to load. I have been researching warm-up cache and Usage-Based Aggregation Design options. Is there any other route I could take.

Thanks for your help.

March 28, 2014 9:51 AM
 

Marco Russo (SQLBI) said:

Vidya,

I think that the perf issue is caused by non empty calculation (you should see a lot of these events in the profiler).

This would require a deeper analysis of data model and MDX script - it does not depend directly on DateTool but is probably caused by a sum of factors.

You can ask us consulting for more assistance on this type of issues (also remotely) - more info here: http://www.sqlbi.com/consulting/

Thanks,

Marco

March 28, 2014 12:28 PM
 

Vidya said:

Thanks for the response Marco.

Unfortunately, I will not be able to take any remote support. I'm looking at the cube design currently.

I'm trying to implement Usage-based aggregations for better performance. Do you have any other suggestions on what I could re-analyze to get better performance.

March 31, 2014 1:18 PM
 

Marco Russo (SQLBI) said:

I don't think you will get any improvement by using aggregations - if the problem is in the non-emtpy calculation, you have to optimize MDX Script. Start from SQL Profiler, analyze the events generated during the query in Analysis Services and try to apply some change to MDX Script to locate which calculation affects the performance so badly.

Marco

March 31, 2014 1:39 PM
 

Vidya said:

example of a slow query-

Column - booked to date (hierarchy from year->day)

Row - Attributes from 2 different dimensions, Product & Sales Agents

Filter - Business Date (single date)

Time Interval Aggregation - YTD

Measure - Gross Dollars, this is a straight forward calculation. its a summary of 2 columns from two different fact tables.

So when i build the above report, everything comes back within 30seconds, but when I pull Time Interval Aggregation, its taking more than 60min and timing out.

This is what i have for YTD -

//To Date Measures

SCOPE (

   [Business Date].[Business Date].Members,

   [Business Date].[Date].Members);    

//Year to Date  

   ( [Time Interval].[Aggregation].[YTD] )

         = Aggregate(

                    { [Time Interval].[Aggregation].DefaultMember } *

                      PeriodsToDate(

                                   [Business Date].[Business Date].[Year],

                                   [Business Date].[Business Date].CurrentMember

                    ) );  

Pls let me know if you have any suggestions on the above calculation.

March 31, 2014 1:52 PM
 

Marco Russo (SQLBI) said:

Vidya, it's hard to say what's going on without a more detailed analysis with the Profiler. If the YTD calc is the only having issues, you should understand what is that reason for too many non-empty calculations.

March 31, 2014 2:56 PM
 

Vidya said:

Hi Marco,

I'm trying to analyze the SQL Server Profiler. I see that the query is very slow due to Comparisons MDX.

This is what I have for PY, could you let me know if you have any suggestions on re-writing this. I read that CASE stmts are slower than IIF stmts. Do you think replacing it with IIF will help the performance? Or any other suggestions?

//Prior Period Measures

SCOPE (

   [Business Date].[Business Date].Members,

   [Business Date].[Date].Members,

   [Booked To].[Booked To].Members,

   [Booked To].[Date].Members);    

//Prior Year

   ( [Time Interval].[Comparison].[PY],

     [Time Interval].[Aggregation].Members )

         = ( [Time Interval].[Comparison].DefaultMember,

           CASE

WHEN ([Business Date].[Business Date].CurrentMember.Level IS [Business Date].[Business Date].[Calendar Name]) THEN

[Business Date].[Business Date].CurrentMember

WHEN (([Business Date].[Business Date].CurrentMember.Level IS [Business Date].[Business Date].[Week])

                       OR ([Business Date].[Business Date].CurrentMember.Level IS [Business Date].[Business Date].[Date])) THEN

LinkMember(

ParallelPeriod([Business Date].[Day Of Year].[Year],

1,

LinkMember([Business Date].[Business Date].CurrentMember,

[Business Date].[Day Of Year])),

[Business Date].[Business Date])

ELSE

ParallelPeriod( [Business Date].[Business Date].[Year],

1,

[Business Date].[Business Date].CurrentMember )

END,

           CASE

WHEN ([Booked To].[Booked To].CurrentMember.Level IS [Booked To].[Booked To].[Calendar Name]) THEN

[Booked To].[Booked To].CurrentMember

WHEN (([Booked To].[Booked To].CurrentMember.Level IS [Booked To].[Booked To].[Week])

                       OR ([Booked To].[Booked To].CurrentMember.Level IS [Booked To].[Booked To].[Date])) THEN

LinkMember(

ParallelPeriod([Booked To].[Day Of Year].[Year],

1,

LinkMember([Booked To].[Booked To].CurrentMember,

[Booked To].[Day Of Year])),

[Booked To].[Booked To])

ELSE

ParallelPeriod( [Booked To].[Booked To].[Year],

1,

[Booked To].[Booked To].CurrentMember )

END );  

April 1, 2014 12:08 PM
 

Marco Russo (SQLBI) said:

Vidya, you should remove any CASE and use SCOPE instead.

Also read this: http://sqlblog.com/blogs/mosha/archive/2007/01/28/performance-of-iif-function-in-mdx.aspx

April 1, 2014 2:26 PM
 

Vidya said:

Hi Marco,

I replaced the above query with below -

Do you suggest a better way to write this. It's already using SCOPE, could you elaborate on how I could replace IIF with SCOPE -

I also partitioned the FACT tables, I got a performance of 10-12min now, which is still not very good.

Pls let me know if you have any suggestions -

Do you think removing unused attributes from the CUBE design would help significantly?

I also noticed that when using Comparison Time Interval, even after caching the data, its taking the same amount of time. Not sure why.

//Prior Period Measures

SCOPE (

   [Business Date].[Business Date].Members,

   [Business Date].[Date].Members,

   [Booked To].[Booked To].Members,

   [Booked To].[Date].Members);    

//Prior Year

 ( [Time Interval].[Comparison].[PY],

     [Time Interval].[Aggregation].Members )

         = ( [Time Interval].[Comparison].DefaultMember,

            IIF(([Business Date].[Business Date].CurrentMember.Level IS [Business Date].[Business Date].[Week])

       OR ([Business Date].[Business Date].CurrentMember.Level IS [Business Date].[Business Date].[Date]),

           LinkMember(

ParallelPeriod([Business Date].[Day Of Year].[Year],

1,

LinkMember([Business Date].[Business Date].CurrentMember,

[Business Date].[Day Of Year])),

[Business Date].[Business Date]),

          IIF([Business Date].[Business Date].CurrentMember.Level IS [Business Date].[Business Date].[Calendar Name],

              [Business Date].[Business Date].CurrentMember,

              ParallelPeriod( [Business Date].[Business Date].[Year],

1,

[Business Date].[Business Date].CurrentMember))

               )

          ,

           IIF(([Booked To].[Booked To].CurrentMember.Level IS [Booked To].[Booked To].[Week])

            OR ([Booked To].[Booked To].CurrentMember.Level IS [Booked To].[Booked To].[Date]),

           LinkMember(

ParallelPeriod([Booked To].[Day Of Year].[Year],

1,

LinkMember([Booked To].[Booked To].CurrentMember,

[Booked To].[Day Of Year])),

[Booked To].[Booked To]),

          IIF([Booked To].[Booked To].CurrentMember.Level IS [Business Date].[Business Date].[Calendar Name],

              [Booked To].[Booked To].CurrentMember,

              ParallelPeriod( [Booked To].[Booked To].[Year],

1,

[Booked To].[Booked To].CurrentMember))

               ) );

April 2, 2014 10:49 AM
 

Marco Russo (SQLBI) said:

Vidya, the short answer is that this calculation is using formula engine heavily and use non empty calculation too much. You should see this in the profiler.

The long answer requires more than chapter in a book. I'm sorry not being able to be more clear in a short comment.

A few books about MDX I can suggest you are:

http://www.amazon.com/dp/B001KU83FI/

http://www.amazon.com/dp/B00ESX1A0O/

Please look at how you have to write SCOPE statements instead of IF/CASE in MDX Script. I don't have a single blog post explaining it, but it will be clear once you learn more about MDX.

Also all the blog post you find in http://sqlblog.com/blogs/mosha/default.aspx are very important lessons for MDX optimization.

Marco

April 2, 2014 12:20 PM
 

Vidya said:

Thanks, I will go through it.

One last question and I appreciate all the quick responses.

I created usage-based aggregations, however I do not see any Time Interval attributes when I look at the requests. Could you let me know why? I do see all the other dimension selections but not Time Interval.

April 2, 2014 3:27 PM
 

Marco Russo (SQLBI) said:

You cannot create aggregations on a attributes that are used to create "virtual" extension of the cube (such as dimensions that are not connected, or calculated members used to extend the cube and perform calculations).

April 2, 2014 11:59 PM
 

Anatol said:

Hello Marco and everyone, my first post here, but I have been enjoying Marco's blog for a long time!

I am looking for the best way to implement the following requirement in the cube. Simplifying, I have a fact table FactPersonDaily( PersonId, EffectiveDateId, HealthStatusId, <a number of measure columes>) and 2 dimensions: EffectiveDate and HealthStatus. The fact table has one record per person per day. Health status can be one of (Healthy, Sick, Dead). Currently the users can analyse all measures by EffectiveDate and HealthStatus.

The new requirement is to give the users ability to analyse data in Excel by HealthStatus 12 months ago. For example:

"How many persons who were Healthy 12 months ago are now Sick?"

"What is the sum of <measure daily cost> of persons who are Healthy today and were healty 12 months ago?" etc.

One solution I have in mind is to add another dimension key HealthStatusId_12MonthsAgo, and add a cube dimension HealthStatus_12MonthsAgo based on the existing dimension HealthStatus. Is there a better way, perhaps with MDX calculations?

April 29, 2014 7:55 AM
 

Marco Russo (SQLBI) said:

Anatol,

I would do some data preparation in ETL, calculating that dynamically in MDX might be very slow. A better engine for this type of Analysis is Tabular, but you have to rebuild your SSAS model. THen, DAX can be very quick doing these calculations dynamically.

But if the delta is a fixed one (12 months) then you can easily manage that in ETL.

Marco

April 29, 2014 2:00 PM
 

Anatol said:

Marco, yes, the delta is fixed = 1 year. The solution I prototyped is creating a new column PriorYearHealthStatusId in the fact table view through a fact table self-join ON A.Date = DATEADD( year, -1, B.Date), and using it as a dimension key for new cube dimension "PriorYearHealthStatus". Is this in line with what you refer to as data preparation in ETL? I will also look at PowerPivot and DAX options, thank you for the suggestions.

Anatol

May 1, 2014 1:06 AM
 

Marco Russo (SQLBI) said:

Not exactly, if you only have the date 12 months ago, you don't have a quick way (in MDX) to obtain those persons who were sick 12 motnhs ago. This is part of the job I would move to ETL, otherwise you have to implement techniques such as Basket Analysis (see http://www.sqlbi.com/articles/many2many) to do this operation (which is slow if you have 1 million of persons or more in Multidimensional).

May 1, 2014 1:16 PM
 

Robert Bakker (Macaw) said:

Hi Marco,

The solution works fine with one of our Excel customers, with one drawback: onze you have chosen a transformation (for instance YTD), all measures will be presented with that transformation. It is possible to hide it in Excel, but not very easily.

Yesterday I was with a customer who uses Executive Viewer on his SQL OLAP Cube. To my surprise this client can display the members different per measure!

Great!

July 2, 2014 6:08 AM
 

Marco Russo (SQLBI) said:

This is an Excel limitation of the PivotTable - but you can overcome this by creating a Set in Excel, which allows you to combine different transformation for different measures. The UI is not what we might dream, but at the end of the day you can obtain the report you want!

Marco

July 2, 2014 6:16 AM
 

Kenny Minnebo said:

Hi Marco,

I've been using your datetool implementation also with several customers, but for the first time I came across something odd.

The format string of the measure itself, is not copied.

Example=

I have a measure formatted with a local currency symbol:

CREATE MEMBER CURRENTCUBE.[Measures].[Local Currency Gross Costs]

AS  Case    When [Measures].[MinCurrency] <> [Measures].[MaxCurrency]

           Then "Multiple Currencies"

           Else [Measures].[Local Actual Cost Value]

   End,

FORMAT_STRING = """" + ([Currency].[Currency ID].allmembers).item([Measures].[MaxCurrency] + 1).membervalue + """ #,##0;"

               + """- " + ([Currency].[Currency ID].allmembers).item([Measures].[MaxCurrency] + 1).membervalue + """ #,##0",

FORE_COLOR = IIF([Measures].[Local Currency Gross Costs] < 0, 255 , 0),

NON_EMPTY_BEHAVIOR = { [Local Actual Cost Value] },

VISIBLE = 1 ,  DISPLAY_FOLDER = 'Local Currency' ,  ASSOCIATED_MEASURE_GROUP = 'Sales Delivered';    

I also have a simple comparison:

SCOPE ( [Date].[by Fiscal - YQPWD].MEMBERS,

   [Date].[Date].MEMBERS );  

---- Year

   ( [DateTool].[Comparison].[Previous Year],

     [DateTool].[Aggregation].Members )

         = ( [DateTool].[Comparison].DefaultMember,

             ParallelPeriod( [Date].[by Fiscal - YQPWD].[Fiscal Year],

                             1,

                             [Date].[by Fiscal - YQPWD].CurrentMember ) );  

When I look at give me the Local Currency Gross Costs of this year and previous year, only this year is formatted. Since I don't know which measure is going to be selected (some are currencies, some are counts, etc) I can't just copy the format string anyway.

You have a workaround for this?

(Should of asked you on the SQL Server Days yesterday :-))

October 2, 2014 5:00 AM
 

Marco Russo (SQLBI) said:

I'm not sure about what is the issue, but I would move the FORMAT_STRING into a scope in the Currency.[Currency ID].MEMBERS level.

October 2, 2014 9:30 AM
 

Marco Russo (SQLBI) said:

Thanks!

December 11, 2014 1:38 PM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement