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.
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?"