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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Super User

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



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
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.