I'm having big difficulties with an existing OLAP where the end user uses Excel to pivot the data. The OLAP has 6 dimensions where  5 of the dimension have from 34 to 2266 rows and the last dimension, has around 275.000 rows. The fact table has about 165.000  rows and around 2.500 rows are added each month.

The OLAP has few calculated members, some YTD and MAT calculations. Those measures are the most important for the client, along with attributes from most of the dimensions. When attributes from the  biggest dimension, the one with 275.000 rows are added to pivot table, it can take up to few hours to execute.

I just recently heard about the non_empty_behavior function and added that to the members, and a query that before took around 3-5 hours took only few seconds. Later, I found out that there are a lot of data missing. There are few things I think is a bit odd about this, and that is probably because I don't fully understand the behaviour of the non_empty_behavior, but when I do add the non_empty to my calculated member in SSAS f.exs. [MAT - Number Of Packages], I get the correct sum value for, but in the detail view it's missing. In the attached files(September_incorrect.pdf), there is 2.060 in grand total but the detail view only show the one for september, 496 units.
How come the grand total is showing when the detail data are missing?

What are my alternatives here, is there any other function I could add to my calculated measures in SSAS in order to speed up the exectution time without missing any data?

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

SELECT {
		[Measures].[Number Of Packages Sold],
		[Measures].[MAT - Number Of Packages Sold],
		[Measures].[Year to date - Number Of Packages Sold]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , 
NON EMPTY CrossJoin(
					Hierarchize({DrilldownLevel({[Product].[Product Information - By Name].[All]})}), 
					Hierarchize({DrilldownLevel({[Product].[Strength - number].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS 
FROM (
		SELECT ({[Product].[Product Information - By Name].&[Product A - 141945 - 0,18 - mg - 100 - stk], 
				 [Product].[Product Information - By Name].&[Product B - 141945 - 0,17999999 - mg - 100 - stk]}) ON COLUMNS  
		FROM [MDIA])
WHERE ([Dates].[Date hierarchy].[Year].&[2009].&[3. quarter].&[2009 - September]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
 
 
--This is example of one of my calculated member from SSAS 
CREATE MEMBER CURRENTCUBE.[Measures].[MAT - Number Of Packages Sold]
 AS AGGREGATE(LastPeriods(12,[Dates].[Date hierarchy].currentmember),[Measures].[Number Of Packages Sold]), 
/*NON_EMPTY_BEHAVIOR = { [Number Of Packages Sold]}, */
VISIBLE = 1  ;