Showing results for 
Search instead for 
Did you mean: 
TheGuyInGlasses Frequent Visitor
Frequent Visitor

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

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


Apprecite any wisdom/insight...



Helpful resources

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,563)