CODEWITHSUNDEEP
data-warehousebusiness-intelligencedimensiondimensional-modeling
Justin Grant
Justin Grant

Reputation: 46763

Dimensional Modelling for boolean OR filtering

How does dimensional modeling address Boolean-OR filtering requirements?

For example, assume a dimension Customer with attributes HomeAddressId and BillingAddressId. Both fields point into an Address dimension. Some business users will only care about home address, others will only care about billing address, but some users want to filter results to, for example, "all customers with billing or home address in Texas".

Another example: a 'Contact' dimension may have attributes Email1 and Email2, but filtering will almost always be on both fields rather than one or the other.

Upvotes: 2

Views: 769

Answers (1)

hashbrown
hashbrown

Reputation: 3516

Alias-based Approach

In this approach, Address dimension table will be aliased as HomeAddress and BillAddress and the ID from both of these two tables will be linked to HomeAddressId and BillingAddressId columns of Customer table respectively. In this design pattern, when you ask a question like this,

all customers with billing or home address in Texas

It will translate to a query like this:

SELECT Customer.*
FROM Customer
LEFT OUTER JOIN Address HomeAddress ON Customer.HomeAddressId    = HomeAddress.Id
LEFT OUTER JOIN Address BillAddress ON Customer.BillingAddressId = BillAddress.Id
WHERE HomeAddress.Name = 'Texas' OR BillAddress.Name = 'Texas'

The Aliases will be defined ideally in the Reporting layer (e.g. Universe in SAP Business Objects) or query layer.

Bridge Table Approach

In this approach, you will create a new bridge table to store the relation between Customer and Address according to the types. This approach is not "dimensional" approach as such but can be used in dimensional modeling in the form of Fact-less Fact table.

CustomerID   |  Type  | AddressID
-----------------------------------
1            |  Home  | 10
2            |  Home  | 20
2            |  Bill  | 30
3            |  Home  | 10
3            |  Bill  | 40

The query in this approach will take the form,

SELECT DISTINCT Customer.*
FROM Customer, CustomerAddress, Address
WHERE Customer.ID  = CustomerAddress.CustomerID
AND   Address.ID   = CustomerAddress.AddressID
AND   Address.Name = 'Texas'

This approach is more applicable in cases where you need to analyze relation between customers belonging to same address.

Not sure if you were looking for this exactly, but hope the answer will be helpful for your visualization and design.

Upvotes: 3

Related Questions