Joining date and time field in Tableau

0
votes

I'm working on project that has data warehouse implemented. The fact table has datetime stored in the format: 

2015-01-31 23:10:49.4020000

Date Dimension has the dates included in the following format:

2015-01-01 00:00:00.000

Time Dimension is storing the time in the following format:

23:10:49 AM

Now, I have to make analysis using Tableau software, for which I need to join the two dimensions with the Fact table. I'm trying to create custom sql for creating a join, unfortunately I'm not able to do so.

Can anyone share how should I join the fact table with both the dimensions?

1
mysqlsqljoindata-warehousetableau-api
You shouldn't need to use Tableau's custom SQL feature just to join a fact and dimension table. Try using the standard features on the data connection pane before resorting to custom SQL. What database are you using? Is it relational or a cube? – Alex Blakemore
Thanks Alex, I'm using SQL at backend. The reason that I cannot use standard features is because I have a mismatch in date and time format. The fact table has both "Date and Time Coloum", whereas in Date Dimension, I have only dates. when I try joining using standard features, tableau provides me an error. So, I'm wondering if I have to change my Data warehouse design – user4943236

1 Answers

1
votes

You are essentially looking for combining multiple tables using either Join Or Data Blending.

In tableau, you can combine data that exists across multiple tables or files by creating joins. Using joins to combine tables allows you to analyze data that have a relationship with each other.

Complete articles depicting all these steps can be found here

  • Connect to data and create your data source according to the examples in Connecting to Data Sources.

  • After you select the file, database, or schema, double-click or drag a table to the join area of the data source page.

enter image description here

  • Double-click or drag another table to the join area. The join dialog box opens.

enter image description here

  • Add one or more join conditions by selecting a field from one of the available tables used in the data source, a join operator, and a field from the added table. Inspect the join condition to make sure it reflects how you want to connect the tables.

enter image description here

  • For example, in a data source that has a table of order information and another for users information, you could join the two tables based on the Region field that exists in both tables. Select the type of join. When finished, click the "x" icon to close the Join dialog box.

enter image description here

In your case, You should go for these joins instead of Data blending. This is because, Data Blending does not create row level joins and is not a way to add new dimensions or rows to your data.

Data blending should be used when you have related data in multiple data sources that you want to analyze together in a single view. For example, you may have Sales data collected in an Oracle database and Sales Goal data in an Excel spreadsheet. To compare actual sales to target sales, you can blend the data based on common dimensions to get access to the Sales Goal measure.

You can refer this article for more details