Hi All,
I have requirement where i have 8 data sets with each one of them having different date granularity and i have a filter for Fiscal Year and Month, now what the users want is, if they select a fiscal year as 2016/2017 then all the months which falls under that year will be shown and likewise if a month is also selected with a fiscal year than the data set which have granularity on month and date will display the data of the selected month year while the one which have granularity on fiscal year will display complete data of that year.
To achieve this i have created a link table by creating a master calendar(Date,Fiscal Year, Month Year) and then cross joined it with all the distinct Countries values and then associating it with all the different data sets with the help of different keys. I then associated the date field in this table with the master calendar date field and then used the fields from the calendar directly in list box as filters.
Suppose, I have three data sets.
Data set 1:
| Country | Duration | Metric1 |
| A | 2016/2017 | 1 |
| B | 2016/2017 | 2 |
| C | 2016/2017 | 3 |
| D | 2016/2017 | 4 |
Data set 2:
| Country | Duration | Metric2 |
| A | Apr-17 | 5 |
| B | May-17 | 6 |
| C | Jun-17 | 7 |
| D | Jul-17 | 8 |
Data set 3:
| Country | Duration | Metric3 |
| A | 4/1/2017 | 9 |
| B | 4/2/2017 | 10 |
| C | 4/3/2017 | 11 |
| D | 4/4/2017 | 12 |
The resultant link table will look something like below
| Country | _LinkFYKey | _LinkDateKey | _LinkMonthYearKey | Date |
| A | A|2016/2017 | A|4/1/2017 | A|Apr-2017 | 4/1/2017 |
| B | B|2016/2017 | B|4/2/2017 | A|Apr-2017 | 4/2/2017 |
| C | C|2016/2017 | C|4/3/2017 | A|Apr-2017 | 4/3/2017 |
| D | D|2016/2017 | D|4/4/2017 | A|Apr-2017 | 4/4/2017 |
| A | A|2016/2017 | A|5/1/2017 | A|May-2017 | 5/1/2017 |
| B | B|2016/2017 | B|6/1/2017 | B|Jun-2017 | 6/1/2017 |
| C | C|2016/2017 | C|7/1/2017 | C|Jul-2017 | 7/1/2017 |
I wanted to know if this is the most appropriate approach or can it be done in a more efficient way?
