MDX calc member: sales to new customers vs existing

Question

MDX calc member: sales to new customers vs existing

ruzanna8888

SSC Veteran

Points: 215
More actions
September 13, 2009 at 7:20 pm

#135636

I have
1) Customers dim with effective date (date when customer purchased product for the first time)
2) Product dim with list of serial numbers (SN) purchased and FirstDate of purchase
3) Date dim that in the fact table corresponds to date of each serial number/ product purchase
Question: how to create a calculated member that will mark SN(products) purchased by new customers vs existing.
In SQL it would be:
select f.productcount, -- sales
(case when d.Date = c.EffDate then 'sale to new cust'
else 'sale to existing cust' -- d.Date > c.EffDate
end) as TypeOfSale
from FactSales f
inner join DimProduct p
on f.DimProductKey = p.DimProductKey
inner join DimCustomer c
on f.DimCustomerKey = c.DimCustomerKey
inner join DimDate d
on f.DimDateKey = d.DimDateKey
Of course, I could create another column in DimProduct and populate it using simple logic of dates comparison, but wanted to start utilizing MDX for this type of requests.
Help anyone? 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RonKyle SSC-Dedicated Points: 31558 More actions · Answer 1

I recommend you create the new column that you would prefer not to create and have it populated as part of the download process. While such columns are normally not done in an OLTP database, you want the retrieval process in an OLAP database to be as quick as possible.

ruzanna8888 SSC Veteran Points: 215 More actions · Answer 2

Yes, but the number of similar different requests I get is growing (dims are getting longer) and since the use of this specific one won't be too high I'd really prefer to explore the way to do the same in MDX...

Veteran with SQL, but new to MDX, I just can't figure out how to compare fields from two dimensions using MDX, in this case, dates from DimDate.date and DimCustomer.EffDate.

mvee Hall of Fame Points: 3634 More actions · Answer 3

I'm relatively new at MDX too, and I've never tried this specifically, but I think you can create a member using the with statement and use the IIF function to do the comparison and create your 'sales to old of new customer' text.

something like:

with

member [customer].[NewOrOld] as iif(Date = EffDate,'new','existing')

select

product count on 0,

[customer].[NewOrOld] on 1

from mycube

?

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

@mvee - you're trying to make it an attribute of customer, which it isn't really as the 'new-ness' is driven by (and on) the product.

@ronkyle - i agree, to implement this easily, you'll want an attribute against the product.

@ruzanna - I am sure that you can determine the answer as part of a query (just like you did with TSQL) but to add this as a calculated attribute of the product could be problematic. What value do you show when the product dimension is not filtered to a single Product|Serial# combination? ie, if you have Serials on the rows, then an attribute calculated as prod.date == cust.instantiate_date would work, But if there were Product Groups on rows, what would you show? If there were 2 products in filters and some completely different attributes/dimensions on rows/columns, what would you show?

Again, in a query, for sure, you could attempt to return the result as per TSQL. IMHO, in a calc member to be attached to the product dim, you're better suited (in this case) to add a new attribute to the source.

Steve.

ruzanna8888 SSC Veteran Points: 215 More actions · Answer 5

First, big thanks to everyone who is trying to understand this and replied with answers.

Steve, I saw your MDX posts before and was waiting for your reply hoping to see some MDX to this solution. You're correct the new value is driven by Product(SN).

To answer your question: DimProduct that has SN is mapped to Product Category/Subcategory. Cat->Subcategory->DimProduct (SN level), so yes there are groups on top of SNs.

from SQL you can see that in Fact table ProductCount is mapped per SN which is rolled up under subcategory/category and has a value of 1, so "Product count" is defined as count in AS showing how many products per Customer were purchased.

ProductSubCategory Product ProductCount

SubCategory1 SN1 1

SubCategory1 SN2 1

SubCategory1 SN3 1

So I was hoping to get a new calc member on top of existing measure "ProductCount"... somehow. Tried IIF, but don't know the right way to plug this all in.

Can you show some MDX to start with? The one I tried below doesn't work, shows 1 all the time. Can I even use IIF that way?

WITH MEMBER [Measures].[New Customer Product Count] AS

IIF([Date].[Date]=[Customer].[Start Date],1,0)

SELECT

{ [Measures].[New Customer Product Count] } ON COLUMNS,

{ [Product].[Product Category Name].Members} ON ROWS

FROM Sales

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Part of the issue with trying to calculate this on the fly versus writing it in as an attribute of the product (which is what it sounds like it is to me) is the concept in a cube of the current location. In SQL you don't have that so much but in the cube, there is the idea that you are in a given cube space at a given point in time. So, where in your MDX you have written [Date].[Date]=[Customer].[Start Date] this doesn't actually make sense, you're not specifying 'which' Date member and which customers start date. You'll often see ..CURRENTMEMBER referenced in MDX as this indicates to the engine what member (in a given set of members) is being evaluated.

I'm not sure how you could achieve what you're looking to achieve in a calc member but having it as an attribute would be an easy implementation. If you wanted to pursue the MDX approach further, I would recommend trying to get an answer from Chris Webb or Mosha (Posumansky). Both have their own sites/blogs but I think both also answer questions on the msft newsgroups/help.

Steve.