Forum Discussion
Update active relationship globally
Anonymous
With your sample data, I was able to create what you asked with the approach I mentioned.
For the 1st screenshot..
3. Shows the input data in each table. Time_Period_flat(refer last screenshot) and Timezone are the new tables you have to build. Ignore the Today and Yesterday flag from Date dimension.
1. Shows the data for Local Time.Today and Yesterday flag comes from Time_Period_flat table and the slicer are from TimeZone table(or you could avoid Timezone table and add slicers directly from Time_period_flat).
2. Shows the data for EST.Today and Yesterday flag comes from Time_Period_flat table and the slicer are from TimeZone table(or you could avoid Timezone table and add slicers directly from Time_period_flat).
Here is the model.
Relationship
Once you build the table Time_Period_flat, you could utilize the approach without any changes to measure.
Edit:
Ignore the Time_Period_flat table data from 1st screenshot..
Refer below for complete data.
If it helps, mark it as a solution
Kudos are nice too.
Hi VasTg,
Thanks for the response. The idea of using a bridge table is intriguing, but I'm not sure if it meets my requirement of handling all time zone reporting with a simple switch between Local and HQ.
Here is some better context regarding my data and situation.
We have stores across the US. Stores look at their sales according to their timezone and HQ looks at everything in Eastern. There is no need to look at everything in a different timezone than local or EST (all in Central or whatever).
We also have a date and time dimension with many attributes. In particular, our date dimension has around 100 attributes (fiscal period, retail periods, special events, etc.).
Sample data:
Sales
| Store | Sales $ | Local Date | EST Date | Local Time | EST Time |
| Boston | 100 | 2/7/2020 | 2/7/2020 | 1:30 AM | 1:30 AM |
| Chicago | 100 | 2/7/2020 | 2/7/2020 | 12:30 AM | 1:30 AM |
| San Francisco | 100 | 2/6/2020 | 2/7/2020 | 10:30 PM | 1:30 AM |
Date
| The Date | Fiscal Today Flag | Fiscal Yesterday Flag |
| 2/5/2020 | ||
| 2/6/2020 | Y | |
| 2/7/2020 | Y |
A simplistic example obviously, but if users are looking at the local time and want to see total sales, it would be:
Fiscal Today: 200 (Boston and Chicago)
Fiscal Yesterday: 100 (San Francisco)
If they are looking at EST, it would be:
Fiscal Today: 300
Fiscal Yesterday: 0
In my desired solution, the user would be able to use a single Fiscal Today attribute from one dimension, then a Time Zone attribute from somewhere. The Time Zone could be a slicer allowing Fiscal Today to switch between Local and EST.
Right now our users need to remove Fiscal Today and add another one from a different dimension. I believe they would need to do the same with the bridge table solution. If all the date attributes were in the bridge table, they would still need to change attributes rather than just change the time zone slicer (Fiscal Today could be one value if local and another when eastern). If the bridge table is narrow and all the attributes are in the two date dimensions, there is still the need to switch between the date dimensions.
Thanks,
Scott
Memorable MemberAnonymous
Scott,
Did you look at the edit in my previous post?
We can't actually model what you are asking when you have different timezones in fact. It should be in a seperate table with two rows instead of two columns.
For you to filter based on timezone, you should connect the sample table I have on top of date dimension. You should add your fiscal flag as an additional column in my table. The timezone column should be your slicer so that they can filter either PST or EST. Once they choose EST, only the rows for EST will be filtered in the below table and that filters your data dimension and that filters the sales fact.
You have to build the table as below ans use Timezone and fiscal flag columns as slicer.. This is just an example for the approach.
| Date | Timezone date | Timezone | Fiscal flag |
| 2/7/2020 15:00 | 2/7/2020 10:00 | EST | Y |
| 2/7/2020 16:00 | 2/7/2020 11:00 | EST | Y |
| 2/6/2020 15:00 | 2/6/2020 10:00 | EST | |
| 2/7/2020 15:00 | 2/7/2020 7:00 | PST | Y |
| 2/7/2020 16:00 | 2/7/2020 8:00 | PST | Y |
| 2/6/2020 15:00 | 2/6/2020 7:00 | PST |
If you have any questions let us know.
- VasTg
Anonymous
With your sample data, I was able to create what you asked with the approach I mentioned.
For the 1st screenshot..
3. Shows the input data in each table. Time_Period_flat(refer last screenshot) and Timezone are the new tables you have to build. Ignore the Today and Yesterday flag from Date dimension.
1. Shows the data for Local Time.Today and Yesterday flag comes from Time_Period_flat table and the slicer are from TimeZone table(or you could avoid Timezone table and add slicers directly from Time_period_flat).
2. Shows the data for EST.Today and Yesterday flag comes from Time_Period_flat table and the slicer are from TimeZone table(or you could avoid Timezone table and add slicers directly from Time_period_flat).
Here is the model.
Relationship
Once you build the table Time_Period_flat, you could utilize the approach without any changes to measure.
Edit:
Ignore the Time_Period_flat table data from 1st screenshot..
Refer below for complete data.
If it helps, mark it as a solution
Kudos are nice too.
Hi VasTg,
OK, I think I understand what you are doing better now.
I was thinking you would end up with multiple time or date dimensions, but that's not the case. You don't have anything like bi-directional ambiguity either I don't think, which is good.
I will need to adapt some before working into my model as I have separate date and time dimensions. But I'll mark you as the solution now as it seems like the right way to go.
Thanks much!
Scott