Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for October 2010

Counting New and Returning Customers in MDX

with 100 comments

Finding the number of distinct customers you had in any given time period, and then finding how many of those have bought from us before and how many are new customers, is a pretty common requirement. It’s a problem I blogged about a few months ago and showed how to solve in it DAX; I was thinking about it again recently and have just updated that post with a more elegant solution. However I thought it would also be useful to show how to do these calculations in SSAS and MDX.

Here’s a query on the Adventure Works cube that shows how to calculate these values:

WITH
MEMBER MEASURES.[Returning Customers] AS
COUNT(
NONEMPTY(
NONEMPTY(
[Customer].[Customer].[Customer].MEMBERS
, [Measures].[Internet Sales Amount])
, {[Measures].[Internet Sales Amount]}
* {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
)
)
MEMBER MEASURES.[New Customers] AS
[Measures].[Customer Count] – MEASURES.[Returning Customers]
SELECT
{[Measures].[Customer Count]
, MEASURES.[Returning Customers]
, MEASURES.[New Customers]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1
FROM [Adventure Works]

There’s already a measure in the cube, [Measures].[Customer Count], that gives the distinct count of customers, so that bit’s easy. Finding the number of returning customers, ie customers who’ve bought something in this time period and who have also bought something from us before, is the tricky bit. It’s a four stage process:

  1. First, get the set of all customers:
    [Customer].[Customer].[Customer].MEMBERS
  2. Then, filter that set to get the set of all customers who bought something in the current time period. Using the NonEmpty function (as opposed to the Filter function) is the most efficient way of doing this – customers who bought something in the current time period are those who have a value for the measure Internet Sales Amount:        NONEMPTY(
    [Customer].[Customer].[Customer].MEMBERS
    , [Measures].[Internet Sales Amount])
  3. Then, take this set and find the customers in that set that bought something in all time periods previous to the current time period. Again, we can use the NonEmpty function to do this, but this time in the second parameter we want to find the customers who have a value for Internet Sales Amount for the set of time periods {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}; for more information on how NULL and the colon operator is used here, see this post. This gives us the set expression:    NONEMPTY(
    NONEMPTY(
    [Customer].[Customer].[Customer].MEMBERS
    , [Measures].[Internet Sales Amount])
    , {[Measures].[Internet Sales Amount]}
    * {NULL : [Date].[Calendar].CURRENTMEMBER.PREVMEMBER}
    )
  4. Finally, we need to count the number of items in this set using the Count function.

Of course, with the count of distinct customers and the count of returning customers, we can subtract the latter from the former and then get the number of new customers, ie those who’ve never bought anything from us before.

Here are the results from the Adventure Works cube:

image

 

UPDATE: if you’re hitting performance problems with this type of calculation, you might also want to read the following post http://cwebbbi.wordpress.com/2013/06/28/optimising-returning-customers-calculations-in-mdx/

Written by Chris Webb

October 8, 2010 at 1:02 pm

Posted in MDX

Tagged with ,

DrillDownX MDX Functions and Calculated Members in R2

leave a comment »

Earlier this year I blogged about one of the few MDX-related changes in SSAS 2008 R2, to do with the Subqueries=2 connection string property. In that post I mentioned that the DrillDownX family of MDX functions has also changed in R2 but didn’t go into details – so here, at last, are the details (not that they are very interesting)…

Basically, functions like DrillDownMember have got a new parameter that allows them to include any calculated members that are children of the member you’re drilling down on. Consider the following query on the Adventure Works cube:

WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 1
SELECT {} ON 0,
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,1)
ON 1
FROM [Adventure Works]

The DrillDownMemberTop function returns the top n members underneath the members in a given set, to allow you to control the number of members you see when you drill down. In the query above I’ve got a calculated member underneath the year 2003 and I’m drilling down to show only the top 1 children of 2003, which returns only H1 CY 2003:

image

Before R2 if I wanted calculated members to appear on an axis in a query like this I’d have to use AddCalculatedMembers, like so:

WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 2
SELECT {[Measures].[Internet Sales Amount]} ON 0,
ADDCALCULATEDMEMBERS(
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,1)
)
ON 1
FROM [Adventure Works]

And in this case, even though I’m only asking for the top 1 children I’m getting two children displayed: H1 CY 2003 and DEMOCALC.

image

Not good. Which is why this new parameter is needed. By using the INCLUDE_CALC_MEMBERS parameter instead of the AddCalculatedMembers() functions, like so:

WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 2
SELECT {[Measures].[Internet Sales Amount]} ON 0,
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,1
,,,INCLUDE_CALC_MEMBERS)
ON 1
FROM [Adventure Works]

image

WITH
MEMBER [Date].[Calendar].[Calendar Year].&[2003].DEMOCALC
AS 2
SELECT {[Measures].[Internet Sales Amount]} ON 0,
DRILLDOWNMEMBERTOP(
{[Date].[Calendar].[Calendar Year].&[2003]}
,{[Date].[Calendar].[Calendar Year].&[2003]}
,3
,,,INCLUDE_CALC_MEMBERS)
ON 1
FROM [Adventure Works]

image

I can make sure calculated members are treated like any other child when drilling down.

Thanks to Akshai once again for giving me the details of this change.

Written by Chris Webb

October 6, 2010 at 11:45 am

Posted in MDX

Goodbye, Windows Live Spaces

with 4 comments

In December 2004 I made the decision to start a blog: it was Christmas, I didn’t have much to do at work, blogging was the hot new thing and since Mosha had just started one I thought I might as well too. I looked around for somewhere to host my blog and being a good Microsoft person I thought their new free blogging platform would be ideal; I started posting and quickly got hooked. 676 posts and six and a half years later here I am.

As you may have noticed I migrated my blog to WordPress today in response to last week’s news that Windows Live Spaces is being discontinued. I’m not sad about this and indeed had wanted to move my blog for several years, but up until this announcement there was no easy way to migrate away from the Live Spaces platform (MS have now provided a very smooth migration path); I could have started from scratch somewhere else but I didn’t want to lose my old posts and comments and break all the links to my blog that were already out there. Also, I’ll be honest and say that I was lazy and so long as my blog did what I wanted it to do – namely give me somewhere to post and hopefully drive some business for my consulting – then I was ok. I can already see I’m going to be much happier on WordPress though.

Now also seems a good point to sound off about the rubbish treatment I received over the years at the hands of Windows Live Spaces. It seems amazing but in the six years I used it it never got any better, and indeed got noticeably worse with each revision it received: functionality disappeared (including the ability to see any kind of statistics earlier this year) and UI changes made it much harder to find pages in the admin area. It’s services like this that have given MS the bad reputation it’s got today, and it pains me to say that as an MVP whose entire livelihood is built on MS being successful as a company. Why bother creating services and products if they’re just going to be a source of frustration for everyone that uses them, if you’re not going to commit the money and talent to developing them into serious competitors to whatever else is out there? MS should be concentrating on doing the things it does well (like SQL Server) rather than messing around in the world of social media; hopefully the death of Windows Live Spaces means that MS is going to start doing this in the future…

Written by Chris Webb

October 5, 2010 at 10:16 pm

Posted in Off-topic

SQLBits 7 Summary

with 2 comments

So another SQLBits is over… One of my jobs this time was to write the emails that got sent out every week and in them I used plenty of marketing phrases like “this will be the biggest and best SQLBits ever”. But it really was the biggest and best SQLBits ever this time, honest! Not just in terms of attendance – we had 528 people attend over the three days – and in terms of the amazing speaker talent we attracted from outside the UK (Buck Woody, Brent Ozar, Rob Farley, Joe Chang, Jonathan Kehayias, the SQLCat guys, Brad McGehee, André Kamman, Maciej Pilecki, Klaus Aschenbrenner, Sacha Lorenz, Raoul Illyés, Ramesh Meyyappan – hopefully I’ve not forgotten anyone, and thanks for coming over for us), but in terms of the sheer number of people who came up to me and told me they were having a great time. I certainly had loads of fun.

If you weren’t there and you’re wondering what you missed, Jamie Thomson did a sterling job with his video camera on Friday capturing the spirit of the event. You can see all his work here:
http://www.youtube.com/view_play_list?p=01617594AB55E450

However I thought I’d link direct to one I appear in:

If you’re wondering about the headgear, it’s because the event took place in York which has a lot of Viking history. And yes, we’ve already been informed several times that Vikings didn’t really have horns on their helmets.

All that remains for me to do is to say thank you to all of my friends on the SQLBits committee: Simon Sabin, Martin Bell, James Rowland-Jones, Allan Mitchell, Darren Green, Chris Testa-O’Neill, Tim Kent and Christian Bolton. We’ll have to do it again sometime… but not too soon!

Written by Chris Webb

October 3, 2010 at 9:10 pm

Posted in Events

Follow

Get every new post delivered to your Inbox.

Join 3,298 other followers