Drive widgets based on user chosen Timezone

[Jijish]

As the platform has users across the globe, the timezone chosen for reporting must get aligned with the logged-in user's locale. SiSense already displays the ElastiCube's last build time in the logged-in user's timezone. In similar lines, whenever a widgets reports intra-day transactions / volumes, an option to display the data in logged in user's timezone should be made available by default in the SiSense platform.

[HamzaJ]

Hey,

If you are looking for a solution right away, I saw the following post on the community page:

https://community.sisense.com/t5/knowledge/enabling-timezone-by-user/ta-p/9593

And I also found a plugin Timezone Adjustments (https://www.paldi.solutions/plugins/time-zone-adjustments)

Hamza

[Jijish]

Hi Hamzaj,

I had tried the date dimension + data security option suggested. However, that introduced undesired behaviors in my dashboard.  

1) The moment I configure a data security layer on a table, that column is added to all of the queries fired from widgets.

2) Owing to #1, undesired joins with tables are getting formed which ends up truncating the dataset fetched from the aggregated tables.

3) The solution proposed works only if I alienate the intra-day transaction table from all other tables in my elasticube which is impossible for me.

Explaining the behavior using an example:-

Column names of the Intra day table (storing last 36 hours data) :-

• DateTimeHours
• ProductID
• Count of Products sold

Column names of Aggregate table :-

• Date
• ProductID
• CountofProducts Sold

DateDimTable ( Data Security applied on Timezone)

• DateTimeHoursInIntraDay
• Timezone
• DateTimeHoursWithTimezoneOffset 

Behaviours introduced by my design  :-

• ProductId is common between Intra-day and Aggregate table. 
• There will be ProductIds in AggregateTable that are not in the Intra-day table as Intra-day stores a snapshot of hourly data.
• Relationship exists between DateTimeHours and DateTimeHoursInIntraDay 
• Relationship exists between ProductIDs in Intra-day and the aggregate table 

My observations / findings :-

• Once the data security is applied, data fetched from AggregateTable gets limited to those ProductIds that are present in the Intra-day table as Timezone data-security forces the SiSense platform to first refer to the DateDimTable --> then fetch matching DateTimeHours from Intraday table to arrive at the ProductIds . 

Constraints / open questions even with the proposed solution:-

• Cannot bring all of the transaction data into Elasticube owing to the size 
• Even if I use the DateDim+DataSecurity option, I have to figure out a solution for converting data in aggregate table to logged in user's timezone.

Do share your thoughts.

Regards

Jijish