Managing a Date-Time Dimension Table: Retain DirectQuery Functionality Issue Data Size Over Time

[TheGuyInGlasses]

Issue: Report SQL Server; data set stores Date and Time as a single column (field) *multiple fileds, act as timesstamps*

Workaround: Built a Date and Time Dimension table (best practice); using ETL process, split the source data fields into seperate Date and Time fields.

Downside: Lose Direct Query ability, as source data is being altered (ETL process). This results, in a data model that needs to use scheduled refresh, runs hourly. Lose ability to get "on demand" data updates from data source. Over time, the data source gets bigger and bigger as more records are added. More demand on resources and processing ETL each refresh cycle. 

Solutions:

1) Have source data (sql server tables) Date-Time fields separted (optimal solution); barrier, extensive custom view table builds by external party to make those changes to source data

2) Create a Date-Time Dimension Table, host/store on server or locally to data model; barrier, every day:hour:minute:second grows over time rapidly turning into a monster dimension table

Goal: save resources, improve efficiency, maintain direct query function

Question: "are there any other alternatives, workarounds and best practices that you have implementd with success?"

Apprecite any wisdom/insight...

TheGuyInGlasses

[huzaifaimtiazh4]

Hi, did you get any workaround the problem? Can I do it if the my data is from Dataverse via direct query and date dimension is on SQL server?