Skip to Content
Sep 28, 2020 at 11:25 AM

What is the most efficient way to join two tables of different grain?

149 Views Last edit Sep 28, 2020 at 11:29 AM 2 rev

I have a BI report that uses data from two tables of different grain, one is daily the other monthly. At the universe level the daily table is set up with a date filter, whenever the table is used in a BI report there is a prompt to pick a specific date.

Then whatever date is picked for the daily table is being used to filter the monthly table ([month end table date] < [daily table date]), this works fine and gives the right records. The issue is when i'm trying to use both dates in a variable, i.e. [date from month end table] < [date from daily table] im getting a #incompatiable issue.

At first, both tables were set up in different queries and then merged on a common dimension, this did not work. Next. i tried to bring both tables in the same query and then began getting the #incompatible error. My next step was trying to create something at the universe level, but that meant creating a list of alias table to be used which would not be the best way.

My question is what is the best way to join these tables and where should this be done? database, universe or report level?