4 deleted 1 character in body
source | link

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 manymay 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
)

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 many 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
)

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
)
3 It looks like the question is about (a) data manipulation, not about (b) database design, so I removed the tag that does not seem to apply.
| link
2 edited tags
| link
1
source | link