1

I am hoping for some assistance with data modelling. I have created a sample database to show my problem.

https://1drv.ms/u/s!Ap6q8W-mvm27hKRL1J85BopnnrfYRQ?e=tgASW4

Essentially I have a client and date calendar as dimension tables. The client has 'episodes' each with a start and end date. They can have multiple episodes. I have assumed this is the FACT table as I will mainly be measuring events at the end date of an episode.

I have several further dimension tables, all with rows with starts and end dates. Again, a client can have multiple rows in each table.

My measures will select the relevant values from these tables where the record is 'open' on the end date of the episode. I will then produce calculations on these.

The attached is just a sample, in reality I have many more of these tables and a lot of data.

I would really appreciate advice on how best to model the data.

My thoughts are:

  1. Create a new FACT table for each of the episode to dimension tables. I assume each would need a bridge table
  2. Build one big fact table by merging each in turn (My application is quite slow so I worry this would take a long time to run)
  3. Create virtual tables combining the data I need in Dax

Is there another option that I haven't considered? I'm trying to build the most efficient and least data hungry model

Thank you

EDIT: I have created a FACT table for Episode/ Accommodation using Table.SelectRows with conditions in power query to try to reduce and simplify the merge process. On my live data it is taking much longer to load. I may continue with this approach but would appreciate if anyone has advice on a better method. New files below:

https://1drv.ms/u/s!Ap6q8W-mvm27hKRL1J85BopnnrfYRQ?e=U5psza

https://1drv.ms/x/s!Ap6q8W-mvm27hKRabDz0TXyZA8qxMA?e=wBzXzI

Improve this question
1
  • You might get a better response to this if you can include the ERD's as images in your post (I'm assuming that is what the files you linked are). I'd try to help but I am on Linux and not really familiar with the Microsoft BI tool suite. The question is clearly stated though and someone with the right knowledge should be able to give an answer. Welcome to StackOverflow! Commented Jan 25, 2024 at 4:43
Related questions
1
Dax vs M (power query) tables the best practice for combining large tables
0
How to create DAX query with two date dimension table
0
Power BI Tables and DAX - Import and Write DAX or do everything in the Source
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.