MDX issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

MDX issue

Author

Topic

nicklarge
Starting Member

40 Posts

Posted - 2011-10-20 : 12:19:12

Hello

The following MDX returns the counts for customer on program A, sum of the sales for those customers for each day (including sales not attributed directly to Program A), count of customers that are not on program A, along with the total sales amount for customers not participating in program A for each day.

I have 3 issues with the query that I need to resolve.
1. The Non Program A sales dollar amount returns the same as the program A sales dollar count, which is incorrect because the counts of these customers are different between Prog A and non Prog A customer counts, and this is reflected in the output.
Ie. 6/6/2010 Prog A Cust count: 1594
6/6/2010 Prog A All Client Sales: $20,252,513.80
6/6/2010 Non Prog A Cust Count: 12,631
6/6/2010 Non Prog A All Client Sales: $20,252,513.80

2. The customer count is 1 more than the actual customer count, which I am assuming is the All Members attribute. Would that be correct? How can I resolve this?

3. The query time is quite slow: 1m 34 secs. Although this might not be greatly improved, are there any improvements that can be seen in the query that would help (and help me understand MDX a little more) ? Any recommendations would be greatly appreciated.

Thanks, Nick.


WITH SET [All Client Customers] AS
	EXTRACT(
		FILTER(
			([Program].[Program Name].&[All Client Sales]
		*	AddCalculatedMembers([Customer].[Customer Unique Identifier].[Customer Unique Identifier].MEMBERS)
		*	([Process Date].[Calendar Date MDY].&[2010-06-06T00:00:00] 
			: [Process Date].[Calendar Date MDY].&[2010-06-13T00:00:00]  )
			),
			IIF([Measures].[Sales Dollar Amount] = 0, 0, [Measures].[Sales Dollar Amount])
			),
		[Customer].[Customer Unique Identifier])
SET [Program A Customers] AS 
	EXTRACT(
		FILTER(
			[Program].[Program Name].&[Client Program A]
		*	(	([Customer Enrollment Start Date].[Calendar Date MDY].&[1900-01-01T00:00:00]
				 : [Customer Enrollment Start Date].[Calendar Date MDY].&[2010-06-06T00:00:00]),
				([Customer Enrollment End Date].[Calendar Date MDY].&[2010-06-13T00:00:00] 
				 : [Customer Enrollment End Date].[Calendar Date MDY].&[2050-12-31T00:00:00] ) )
		*	AddCalculatedMembers([Customer].[Customer Unique Identifier].[Customer Unique Identifier].MEMBERS)
		*	(  ([Process Date].[Calendar Date MDY].&[2010-06-06T00:00:00] 
				: [Process Date].[Calendar Date MDY].&[2010-06-13T00:00:00]  ) ),
			NOT ISEMPTY([Measures].[Sales Dollar Amount])
			),
		[Customer].[Customer Unique Identifier])
SET [Non Program A Customers] AS
( [All Client Customers] - [Program A Customers] )
MEMBER [Measures].[Program A Customer Count] AS
CASE WHEN DISTINCTCOUNT(([Program A Customers], [Process Date].[Calendar Date MDY].CURRENTMEMBER)) = 0
	 THEN NULL
	 ELSE DISTINCTCOUNT(([Program A Customers], [Process Date].[Calendar Date MDY].CURRENTMEMBER)) 
 END
MEMBER [Measures].[Non Program A Customer Count] AS
CASE WHEN DISTINCTCOUNT(([Non Program A Customers], [Process Date].[Calendar Date MDY].CURRENTMEMBER)) = 0
	 THEN NULL
	 ELSE DISTINCTCOUNT(([Non Program A Customers], [Process Date].[Calendar Date MDY].CURRENTMEMBER)) 
 END
MEMBER [Measures].[Program A All Client Sales]
AS
Sum(
	( EXISTS([Program].[All Client Sales], 
				([Program A Customers], [Process Date].[Calendar Date MDY].CURRENTMEMBER)
					))
	, [Measures].[Sales Dollar Amount]
	)
MEMBER [Measures].[Non Program A All Client Sales]
AS
Sum(
	( EXISTS([Program].[All Client Sales], 
				([Non Program A Customers], [Process Date].[Calendar Date MDY].CURRENTMEMBER)
					))
	, [Measures].[Sales Dollar Amount]
	)
SELECT  
		NON EMPTY
		{	[Measures].[Program A Customer Count] 
			,[Measures].[Program A All Client Sales]
			,[Measures].[Non Program A Customer Count]
			,[Measures].[Non Program A All Client Sales] 
			} 
			ON 0,
		Hierarchize(
			([Process Date].[Calendar Date MDY].&[2010-06-06T00:00:00] 
				: [Process Date].[Calendar Date MDY].&[2010-06-13T00:00:00])
						) ON 1
  FROM [Client Sales Transactions]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources