cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JLurie248Vig
Frequent Visitor

How to build data model - fact tables with StartDate and EndDate - need to run report As of Date

I am trying to build a data model in SSAS Tabular where the central dimension table (Employee) is related to several fact tables which are historical audit tables with StartDate & EndDate.  Employee table is unique by EmployeeId.  All fact tables are related to Employee table in a many:1 relationship with PK/FK as EmployeeId.

 

JLurie248Vig_2-1608730445600.png

 

I need to be able to create reports both as a snapshot in time (as of specific date) so the final result would look like this (only one result would be returned, just showing two examples here).

 

JLurie248Vig_1-1608730068413.png

and also need to be able to return values between a given date range like this:

 

JLurie248Vig_3-1608731025292.png

This is a very simplified version of the full data model (there are over 100 tables total, about 30% of them have the start/end date logic and others are simple dimension tables.  

 

The only possible solution I've come across is creating calculated tables for each of the fact tables and expanding out the dates to include a record for every day within the given date range like THIS but given almost 20 years worth of data I'm worried this will make the model so large it'll be slow and difficult to work with (in Direct Query mode with encrypted connection).  

 

Any suggestions here on a simpler approach?  Thanks all and Happy Holidays!

 

 

 

 

3 REPLIES 3
TomMartens
Super User II
Super User II

Hey @JLurie248Vig ,

 

from reading your initial question my assumption is that you are facing the events-in-progress problem.

This post Events-In-Progress | Gerhard Brueckl on BI & Data (gbrueckl.at) is one of the most sophisticated post regarding this topic.

Nevertheless, it contains links to every essential post.

I recommend starting with the article by Jason Thomas (SQLJason).

 

If you need more help, consider to create a pbix that contains sample data, but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
amitchandak
Super User IV
Super User IV

@JLurie248Vig , refer to my blog on the same topic, if that can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://www.youtube.com/watch?v=e6Y-l_JtCq4&t=98s



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hi Amit,

Great post, but the problem I'm trying to figure out is much more complex.  I am not doing aggregations (count, sum, etc).  I am comfortable with that side of things.  What I need to do here is look up at any given point in time what the "current" value is for each table for any given employee so more of a "lookup" than a calculation.  

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors