Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TheGuyInGlasses
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*

 

 

Date-Time.JPG

 

 

 

 

 

 

 

 

 

 

 

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

1 REPLY 1
huzaifaimtiazh4
New Member

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?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.