Search code examples
ssasolapmdxcubecubes

Calculated Measure using dimension

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.

Solution

  • 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.