Joining on Month& Year in Date column

[Former Member]

I have 2  table with the below dates. SQL Server is the database. Table A 2016-03-31 2016-02-29 Table B 2016-03-1 2016-02-1 I have to join these 2 tables to get data for each month. However Inner join would fail and trying to extract month & year and joining in SQL, which is working fine. Table A.MONTH=Table B.MONTH AND Table A.YR=Table B.YR But how would i do this join in the universe level?

[former_member207052]

This is a design question rather than a technical question. Check with your DBA to decide on the best way.

My take would be to create a Date_Dim table.

why?

1. Right now you have two tables what will happen if you need to add a new one later?
2. Joining two fact tables could be tricky, especially when you are trying to link it with a data factor
3. When to plan to bring in other dimensions it will become even more complicated, you will struggle with traps and loops.
4. You will have a new requirement, say, to calculate sales only for Sundays, what will you do?

How?

I've uploaded the data in CSV format here for you to play around. (Dates are from 01-Sep-2015 to 30-March-2016)

After creating the time_dim table (or date dim) setup your universe (unx or unv - it doesn't matter).

Make sure that the outer join is always on the Time_Dim table.

[mhmohammed]

Hi Cristina,

How about creating Calculated Columns for Month and Year in those tables and then join on those newly calculated columns as Table A.Month = Table B.Month and Table A.Year = Table B.Year?

You can use Year() and Month() function to get the Month and Year number, its available by default in IDT for some databases, check out if it works for the type of database you are using.

This can help to you create Calculated Columns using Date value:

Information design tool 4.0: Create a calculated column using a date value

Let us know if this helps and/or you have any questions/issues.

Update: If you've used Universe Design Tool (UDT) to create the Universe, then you'll have to create a Derived table.

Thanks,
Mahboob Mohammed