How do you create a relationship with date table in SQL Server Data Tools

Posted on 2014-08-18
Medium Priority
Last Modified: 2014-08-24
I am using SSDT 2012 to create a tabular model. I have several tables (Order, Order Details, Delivery Details, Item Details etc.) All of these are working fine. I have created relationships between these tables and I can sort and sum my data. My issue however is creating and assigning the date table. I created a data table with many date columns (Date, Month Name, Year, FY etc.) but I cannot get this table to work with all the others. I assigned it as the date table and marked the date column. Then when I do a report and select FY 2015, if gives me every order from the order table. If I create a relationship between the Date field and the Order date field, then it works. However, I cannot have another active relationship with the delivery date and Date field. So in short, how to I make the date table work with all the other table's date field?
Question by:jsgrosskopf
LVL 49

Expert Comment

ID: 40269400
Are you after how to do this in SSDT, or are you after how do yo do this in SQL?

Please provide the table and field names.

{+ edit} btw: I don't use SSDT so all I can offer s the SQL
sample data and expected result would greatly assist too.
LVL 101

Expert Comment

ID: 40269429
I don't use that tool either but the normal way to do that is to add a copy of the date table for each date you need to use it with.

LVL 37

Expert Comment

ID: 40269751
Haven't tested this with SSDT 2012, but I do it as advised by Kimball: Each date column points to a semantically different dimension. Cause they have the same values, create one complete base date dimenension table. Create views of it to get the semantics. Build the relationships to the according views.

Accepted Solution

jsgrosskopf earned 0 total points
ID: 40269974
I finally found this out, there is a calculation you can do in the date table with the "userelationship" variable and all my relationships work. Thanks for all the responses.

Author Closing Comment

ID: 40281352
Solution was found. Thanks for all the comments helping me

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question