We have Dimension Table and Fact Table in SSAS Cube. The DimCustomer table is Slowly changing Dimension, it tells when Business Key eg: 2, Joe Smith, changes his address, phone number. Hence, a Customer may have many dimension rows, everytime his attributes changed.

We also have Fact table which shows customer transactions.

In SSAS, How do I tell Number of Distinct Customers who had purchase transactions in fact table. We cannot do distinct on DimCustomerIds, because Joe may have changed his address in the slowly changing dimension table, but is the same person. So how would I do a distinct Business Key Customer Count on Customer BK on the fact table?

create table dbo.DimCustomer
     DimCustomerIdentityId identity(1,1) primary key,
     Customer_BK int,   -- customer business key, Joe has BK: 2
     CustomerName varchar(50),
     CustomerAddress varchar(50),
     CustomerPhone varchar(25)
     BeginDate date,
     EndDate date

create table dbo.FactCustomerTransaction
     FactCustomerTransactionId primary key identity(1,1)
     DimCustomerId int, 
     PurchaseAmount decimal(10,2)
     PurchaseDate date
  • SSAS Multidimensional or Tabular? – GregGalloway Mar 8 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy