Skip to Content

Universe design challenge - Conditional join??

Mar 24, 2017 at 01:57 PM


avatar image

Hi Experts, I have been puzzled by the design of the below requirements in Universe design

1. I have a fact table which has customer ID; a customer can be anonymous or explicit; anonymous customers share the same customer ID in customer master dimension;

2. Customers have unique "Customer type" and "Country" stored in other dimension tables which is joined with Customer Master Dimension; if report needs to be done on anonymous customers, currently they all return the same value as the customer ID is the same for those two fields;

3. New requirement is to allow Anonymous customers able to be reported on different values for "Type" and "Country", which will be picked at the time when order is generated in the source system. So the same customer ID will have different combinations of those two fields, dev team has agreed to add those two new fields in the fact table (Since Type and Country is now derived by Unique order for Anonymous), but only will store the data for anonymous customers, those two new fields will be blank for explicit customers and still be derived by CustomerID.

The challenge for me is: in the Universe, there needs to be two kind of joins to generate report on "Type" and "Country"

1. If Explicit customer, Fact (CustomerID)->Customer Master Dim-> TypeDim

2. If Anonymous customer, Fact (TypeID) -> TypeDim

I couldn't figure out how to include both in one reporting object for "CustomerType". Please share some thoughts and greatly appreciate it.

10 |10000 characters needed characters left characters exceeded

for explicit customer reporting objects needed from all three tables? or can pick objects from fact & Type Dim only?

* Please Login or Register to Answer, Follow or Comment.

2 Answers

Junjie Li Mar 27, 2017 at 01:35 PM

Amit - Explicit customers needs all three tables to be able to report on some other objects.

10 |10000 characters needed characters left characters exceeded
Mar 27, 2017 at 01:48 PM

If Explicit customers needs objects from all three table for reporting then you can enable the shortcut join between "Fact (TypeID) -> TypeDim" tables.

when ever for Anonymous customer user drag the objects from these two tables,report will follow the join path from these two tables only.

10 |10000 characters needed characters left characters exceeded