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

[Anonymous]

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.

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).

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

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!

[TomMartens]

Hey @Anonymous ,

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

[amitchandak]

@Anonymous , 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-trend/ba-p/882970

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

[Anonymous]

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.