Customer Sales based on YTM dimension

Hello,

We have a cube that has customer sales data for last 5 years. Time Dimension displays the YTM hierarchy.

=> Selecting "month" is a parameter for the user on a Reporting Service report. Once he selects a month from YTM hierarchy - how to get list of "only those customers" whose sales have been continuously below say 80,000 dollars, beginning the month he selects as a "start month" until "next 6 months".

Multiple selection - not allowed. Only one month can be selected by user at any time.

Any help highly appreciated.

Thanks,

RajShri

Here's an Adventure Works example, which lists all customers with < $1000 in sales for each of 6 months, starting with the selected month (here, Jan. 2004). Note that this includes customers with no sales as well:

>>

With

Member [Measures].[Max6MonthSales] as

Max(LastPeriods(-6,

OpeningPeriod([Date].[Calendar].[Month])),

[Measures].[Internet Sales Amount])

Set [LowSalesCustomers] as

Filter([Customer].[Customer Geography].[Full Name].Members,

[Measures].[Max6MonthSales] < 1000)

select {[Measures].[Internet Sales Amount],

[Measures].[Max6MonthSales]} on 0,

[LowSalesCustomers] on 1

from [Adventure Works]

where [Date].[Calendar].[Month].&[2004]&[1]

>>

sql

Customer Count

Hello,

I have a Fact table containing sales information, and is linked to a customer dimension through a CustomerKey and to a product dimension. The Customer Dimension is a slowly changing dimension having CustomerKey as primary key and CustomerPIN as BusinessKey. A customer can have historical records in the Customer Dimension.

I want to count the number of clients per product. If I create a measure Customer Count as Distinct Count on the CustomerKey, my results are not quite correct because 2 different Customer Keys might belong to the same Customer since the latter is an SCD. So what I want is actually Distinct Count on CustomerPIN, but I am not able to create this measure correctly. It is being created as a measure group under Customer but is not linked to the sales.

I am not sure if I'm doing the right way or if I'm missing something.

Appreciate your help,

Grace

Hi Grace,

One possibility is to use a Named Query for the fact table, where the original fact table is joined to the Customer table, so as to add a Customer PIN field on which a distinct count measure can be created.

|||

Thanks Deepak,

I was able to create a Named Query having SalesKey as Logical Key with Person PIN according to the Person Key of each Sales transaction. And I added a relation between SalesKey from the New Named Query and the Fact table. Then, I was able to create a measure as Distinct Count on Person PIN and use it correctly.

However I was wondering if maybe since I can modify my underlying fact table to originally include this Customer PIN in addition to the Customer Key, and fill both from the SSIS, wouldn't that be faster and less complex? What do you suggest?

Thanks again,

Grace

|||

Grace,

My own inclination would be avoid loading redundant data, unless the cube processing performance with the Named Query is a problem. But I can't say that I've dealt frequently with this scenario, so maybe others on this Forum could comment as well?