views:

114

answers:

2

I am trying to build a calculated measure in SSAS that incorporates a dimension parameter. I have two facts: Members & Orders and one Dimension: Date. Members represents all the unique members on my site. Orders are related to members by a fact key representing a unique user. Orders also contains a key representing the vendor for an order. Orders contains a key to the date dimension.

FactMember - MemberFactKey - MemberId FactOrder - FactOrderKey - OrderId - FactMemberKey - DimVendorKey - DimDateKey DimDate - DimDateKey - FYYear

The calculated measure I am trying to build is the number of unique vendors a member has ordered from. The value of the calculation must of course change based on the date dimension.

A: 

I am confused, it would make more sense to make Members and Orders both separate dimensions and then reference them from a FACT table, say Fact.Sales. This would eliminate the need to even build a calculated member if you keyed your Members dimension on some sort of member_key.

ajdams
Kevin D. White
Well this calculation seems like something that would be quite common which is why I concluded maybe the structure of the cube itself needed to be re-evaluated. I wasn't aware that it wasn't in the realm of possibility in your situation.
ajdams
A: 

Wouldn't the DISTINCTCOUNT function be the one to use here? Creating a distinct count of Vendors could then be used in this query and elsewhere.

WITH MEMBER [Test]
AS
DISTINCTCOUNT([Vendor].[Vendor].[Vendor])

I will say in advance that this may well be slow (Depending on data volume/distribution), so if this query will be a popular/big part of the design it may be worth considering a restructure.

Meff