I have a fact [Orders], and a customer dimension [Customers]. There are three relationships between these two, as the fact Orders can have three types of customers. : shipper, consignee, billto. So, in turn when I try to browse the data via the customer id and name from the customer dimension and try to pull in fact order attributes the cube is unable to determine which of the three attributes it should splice data by.
I am essentially just attempting to figure out the best way to handle the situation. Obviously, I could normalize the table and create three assignment tables specific to the types that I specify above which would alleviate the issue. I am just wondering if there is a way to do this in the cube without changing the structure of the tables in the sql database.
There are a few ways to handle the relationship between DimCustomer and FactOrders so it depends on how you have the data modeled...
If FactOrders has 3 fields that each link back to DimCustomer like below...
FactOrder
ShipperKey (FK)
ConsigneeKey (FK)
BillToKey (FK)
DimCustomer
CustomerKey (PK)
...then you will end up with 3 dimensions for Customers in your SSAS database. You can implement these dimensions via Role-Playing (1 dimension added 3 times to your cube) or as 3 separate dimensions. The latter allows for more user-friendly naming conventions.
On the other hand, if your FactOrders table only has 1 field that links to DimCustomer like below...
FactOrder
CustomerKey (FK)
DimCustomer
CustomerKey (PK)
...then you will need to differentiate "Customer Types" (Shipper, Consignee, BillTo) using 1 of two methods...
Method 1: Create a CustomerType field in your DimCustomer table and make it an attribute in the customer dimension in your SSAS project...
FactOrder
CustomerKey (FK)
DimCustomer
CustomerKey (PK)
CustomerType
Method 2: Create a new dimension table (DimCustomerType) and add it to your cube...
FactOrder
CustomerKey (FK)
CustomerTypeKey (FK)
DimCustomer
CustomerKey (PK)
DimCustomerType
CustomerTypeKey (PK)