Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
13 | |
2 | |
2 | |
1 | |
1 |
User | Count |
---|---|
20 | |
3 | |
2 | |
2 | |
2 |