SSAS - How do I count occurrences in different dimensions?

Question

question

Thomas 2 asked Feb 22, '10

SSAS - How do I count occurrences in different dimensions?

Hi.

I'm new to this forum, and have limited experience with MDX.

I have a cube with customer accounts over time (monthly statements). The Monthly Statement dimension has attributes indicating the status of an account (new, still active, closed). The fact table is linked to the monthly statement dimension, as well as a "Date Loaded" dimension, and a "Statement Date" dimension, and a "Customer" dimension.

I need to produce a matrix with the statement date months on the x-axis, date loaded months on the y-matrix, and showing the number of customers who had at least one new account on "date loaded", and have at least one open account at a "statement date".

The answer basically stipulates which customers I took on (new) and compared to that number, which customers I still have with my product.

I have tried intersect, filter, and exists, but I can't get it to work - I keep getting "different dimensionality" errors. I'm not sure if this is a cube design problem or mdx problem; the granularity of the cube is account, but I have also tried a second fact table "customer status" (rolling up accounts to customer level) but can't get it to work.

Thank you for any help.

ssas mdx

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-02-22T02:26:03Z

Rob Farley answered Feb 22, '10

Presumably you mean "number of customers who had a statement issued during the statement date month"

You should have a DistinctCount measure on the CustomerID column in your fact table. Then it should be as easy as:

SELECT [Measures].[Number of Customers] ON 0,
       [Date Loaded].[Calendar].[Month].Members *
       [Statement Date].[Calendar].[Month].Members 
            ON 1
FROM [Your Cube]

SSRS doesn't let you put anything apart from Measuers on Columns, so to make a matrix in SSRS, you need to do it this way. If you're just writing it in Management Studio, you could get away with:

SELECT [Date Loaded].[Calendar].[Month].Members ON 0,
       [Statement Date].[Calendar].[Month].Members ON 1
FROM [Your Cube]
WHERE ([Measures].[Number of Customers])

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thomas 2 · Feb 22, 2010 at 03:16 AM

Hi Rob. Thanks for your quick response. I can get the number of customers per date loaded and statement date, however, I need to know the number of customers who had a new account on "2001-01-01" and still have an open account on "2007-01-01". I can determine the new accounts, and I can determine the open accounts, but I need to intersect(?) the new customers from "2001" with the open customers in "2007" and count that. The matrix would ultimately show that of all new customers in 2001, I still have 60% active in 2007. I hope this makes more sense? Thomas.

0 ·

Rob Farley · Feb 22, 2010 at 04:15 AM

Ok. So you want to have Number of Customers per customer (ie, 1) per Loaded date, and the Number of Customers per customer (potentially 0) per Statement date. If you add them together, customers who are still by Statement date have a total of 2. So then you can filter your Customers to that list, and count them. I'm not sure if I have time to throw an example together for you, but potentially I might...

0 ·

Thomas 2 · Feb 22, 2010 at 05:54 AM

I definitely need an example then, because my MDX skills are not great. Is there a solution using intersect? I.e. take the set of new customers at "Date Loaded" and intersect with the set of open customers at "Statement Date"?

0 ·

question