Skip to Content
Aug 16, 2017 at 07:36 PM

OUTER JOIN merge dimensions


I am attempting to build a Web Intelligence report based on GB & Smith's 360Eyes universe. Essentially, I want to tally Crystal Reports and Webi usage by date.

One of the challenges is that the vendor doesn't provide a well-constructed date dimension to make this easier. Rather, the 360Eye's universe exposes a single date/time dimension, named (poorly) Action Date.

To remedy this, I created a universe that creates a date/time hierarchy, based on our organization's DATE_DIMENSION table.

My goal is to create a CrossTab that aggregates usage by report, year, and week, displaying all the weeks (even if there are no report-usage events for that week).

As such, I'm hoping there is a way to "outer join" the DATE_DIMENSION dimensions with the 360Eye's Action Date dimension.

A sample of the DATE_DIMENSION's dimensions:

A sample of the 360Eye's dimensions:

Note: I created the Week of Action Date and Date of Action Time (dimensional) variables.

My attempt to simply merge the Action Date dimension with the Date dimension result in a "INNER JOIN" (effectively only showing the data in the 360Eyes universe).

Is it possible to do an "OUTER JOIN"? If so, how do I do this?