CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012ssas
Bill Blankenship
Bill Blankenship

Reputation: 3356

SSAS -> Cube Browsing/Querying -> Dimension with three relationships to another dimension, trouble retrieving data

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.

Upvotes: 0

Views: 715

Answers (1)

Bill Anton
Bill Anton

Reputation: 2970

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)

Upvotes: 3

Related Questions