Skip to Content

Universe design challenge - Conditional join??

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Mar 27, 2017 at 01:35 PM

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

    Add comment
    10|10000 characters needed 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.

    Add comment
    10|10000 characters needed characters exceeded