Drive widgets based on user chosen Timezone

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