Count Loyalty

Hi

I would like to count loyalty customer for a brand

E.g.

Category Brand Customer

1 2 A

1 3 A

1 2 A

We need to find out whether customer A is buy Brand 2 all the time.

In above example, Under Cat. 1, this is not a loyal customer.

Count Category =3

Count Brand 2 =2

Count Brand 3 = 1

So how should i compare the result,

The Count Category = Count Brand 2--> Loyal customer,

Thanks for anyone to give some hint for me to start....

Doing a count of the nonempty brands should be a good start.

Count(NonEmpty([Brand].[Brand].[Brand].Members))

This would give you a count of the number of brands for what ever other dimension members (customers, time, etc) are currently selected. A count of 1 would indicate a loyal customer over the selected time period, 0 would indicate no purchases and 2+ would indicate "disloyalty".

|||

Hi Darren,

Thanks for your little guide.

now i manage to come out some result which i think is useful. but now how should i count the "L" from the result

WITH MEMBER [Measures].Loyalty AS

CASE

WHEN [Measures].[Int Category Id Count] = 0 THEN NULL

WHEN [Measures].[Int Brand Id Count] <> [Measures].[Int Category Id Count] THEN "S"

WHEN [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] THEN "L"

ELSE "C"

END

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].Loyalty

This will generate a list of

All
All L
100002 L
100003 L
100004 L
100007 L
100008 L

How should I count this result?

Thanks again,

|||

Does this mean you want to get customer counts for each of these categories? You would probably need to break this into separate measures. For the "L" group it would look something like the following

WITH MEMBER [Measures].[Loyalty Count] AS

Count( FILTER(EXISTING [Customer].[Customer].[Customer].Members, [Measures].[Int Brand Id Count] = [Measures].[Int Category Id Count] ))

SELECT [Dim Customer].[Dim Customer] on 0,

NON EMPTY([Dim Brand].[Dim Brand].Members) on 1

FROM [CS DW Cube]

WHERE [Measures].[Loyalty Count]

|||

Hi Darren,

I'm really appreciate your help, now i'm sure i very close to the answer.

There are one more question regarding the [Measures].[Int Brand Id Count]

Currently this measures, is only count on one field named (intBrandId), would that possible to count on combination key, reason is because the (intBrandId) is not unique.

Is this Possible to count Like this --> Count ( intBrandId, intCategoryId)

Thanks again

|||

If you want to count the unique combination of these two fields, you would crossjoin the sets of attribute members that map to them.

eg

Count( crossjoin( intBrandid, intCategoryId) )

or you can use the * operator which is just shorthand for crossjoining

eg

Count( intBrandid * intCategoryId )

|||

Hi Darren,

Cross join, it seem like count number of intBrandID in brand Table multiply with number of intCategoryId in Category table.

But i want to calculate total for this composite key (intBrandid and intCateogry)

Thanks

|||

Sorry, if these are independant dimensions your only choice is to count the non empty crossjoined members.

count( nonempty( crossjoin(...) ) )

but it almost sounds like these two attributes should be modelled in the same dimension.

|||

Hi Darren,

Yes your are right, is from 2 dimension.

The reason i asked (count on Composite key), is because i had a fact table with (intBrandId and intCategoyId), so my idea was to find out number of loyalty customer by compare the count of (intBrandId and intCategoryId) with the count of (intCategoryId) in the fact table. Is that possible because i'm not able to find a way to count (2 fields) within the fact table, or is this the right way to find the answer?

How is it actually able to find the answer by cross join 2 dimesion tables (Brand and Category) to find answer for number of loyalty customer?

Please explain for me, because i'm still weak in concept of SSAS.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

Thansk for you explaination so far.|||

As I have said before, if a brand belongs to a specific category, then these attribute should be modelled in the one dimension, which means that if this is the case the category key should not be in the fact table.

When you have these two attributes linked separately to the fact table you are essentially saying that it is logically valid for every brand to exist in every category. The crossjoin creates this theoretical subcube which we then need to filter down with the nonempty function to find only those combinations of attributes that have values.

The reason is i think the fact table contain the information to find out the loyalty customer, so should it find answer from fact rather than cross join this 2 tables?

If you are using MOLAP structures the tables to not come into play at all, they are read at processing time, but from then on SSAS creates it's own storage structures and returns the data from there. SSAS make use of bitmap indexes and crossjoins are common and are not usually as expensive as they would be in SQL and you are not actually "joining" tables.

An alternative approach might be to create a distinct count measure over your intBrandId, then you could just return this value for any given category. This might be an easier way to get the figures you want without crossjoins and without changing your data model too much.