Two columns of a relational table can have the same values

You cannot create two columns with exactly the same name. Because if that was possible, how would SQL know in what column you want to insert values (keep in mind that you can insert values by the order you determine, for example both would work:

). But you can name them slightly different, like col1 and col_1, like in your example. Then everything will work fine.

You can learn about both table relationships and time intelligence using free data on the Microsoft Azure Marketplace. Some of these datasets are very large, requiring a fast internet connection to complete the data download in a reasonable period of time.

1. Start Power Pivot in Microsoft Excel add-in and open the Power Pivot window.

2. Click Get External Data > From Data Service > From Microsoft Azure Marketplace. The Microsoft Azure Marketplace home page opens in the Table Import Wizard.

3. Under Price, click Free.

4. Under Category, click Science & Statistics.

5. Find DateStream and click Subscribe.

6. Enter your Microsoft account and click Sign in. A preview of the data should appear in the window.

7. Scroll to the bottom and click Select Query.

8. Click Next.

9. Choose BasicCalendarUS and then click Finish to import the data. Over a fast internet connection, import should take about a minute. When finished, you should see a status report of 73,414 rows transferred. Click Close.

10. Click Get External Data > From Data Service > From Microsoft Azure Marketplace to import a second dataset.

11. Under Type, click Data.

12. Under Price, click Free.

13. Find US Air Carrier Flight Delays and click Select.

14. Scroll to the bottom and click Select Query.

15. Click Next.

16. Click Finish to import the data. Over a fast internet connection, this can take 15 minutes to import. When finished, you should see a status report of 2,427,284 rows transferred. Click Close. You should now have two tables in the data model. To relate them, we’ll need compatible columns in each table.

17. Notice that the DateKey in BasicCalendarUS is in the format 1/1/2012 12:00:00 AM. The On_Time_Performance table also has a datetime column, FlightDate, whose values are specified in the same format: 1/1/2012 12:00:00 AM. The two columns contain matching data, of the same data type, and at least one of the columns (DateKey) contains only unique values. In the next several steps, you’ll use these columns to relate the tables.

18. In the Power Pivot window, click PivotTable to create a PivotTable in a new or existing worksheet.

19. In the Field List, expand On_Time_Performance and click ArrDelayMinutes to add it to the Values area. In the PivotTable, you should see the total amount of time flights were delayed, as measured in minutes.

20. Expand BasicCalendarUS and click MonthInCalendar to add it to the Rows area.

21. Notice that the PivotTable now lists months, but the sum total of minutes is the same for every month. Repeating, identical values indicate a relationship is needed.

22. In the Field List, in “Relationships between tables may be needed”, click Create.

23. In Related Table, select On_Time_Performance and in Related Column (Primary) choose FlightDate.

24. In Table, select BasicCalendarUS and in Column (Foreign) choose DateKey. Click OK to create the relationship.

25. Notice that the sum of minutes delayed now varies for each month.

26. In BasicCalendarUS and drag YearKey to the Rows area, above MonthInCalendar.

You can now slice arrival delays by year and month, or other values in the calendar.

Tips:  By default, months are listed in alphabetical order. Using the Power Pivot add-in, you can change the sort so that months appear in chronological order.

1. Make sure that the BasicCalendarUS table is open in the Power Pivot window.

2. On the Home table, click Sort by Column.

3. In Sort, choose MonthInCalendar

4. In By, choose MonthOfYear.

The PivotTable now sorts each month-year combination (October 2011, November 2011) by the month number within a year (10, 11). Changing the sort order is easy because the DateStream feed provides all of the necessary columns to make this scenario work. If you’re using a different time intelligence table, your step will be different.