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
yvtoropov
Regular Visitor

Modelling and querying multiple related fact tables

Good day!

 

Apologies if this is the wrong place to ask this sort of question.

 

I have a relational data source with 3 fact tables which are related to each other and model

patient doctor visits (EncounterEventFact table), assignment of a diagnosis to patient (DiagnosisEventFact table) and collecting lab results for the patient (LabComponentResultFact table) in the picture below. They all share EncounterKey a key, specifying a unique visit to a doctor. All EncounterKeys are in EncounterEventFact table and only once. I am using SSAS tabular in memory to model the data.

Each fact table has a few million rows (2-4 mil). DiagnosisDim has few tens of thousands of rows. PatientDim has few millions rows (<10 mil). LabDim has few hundred records. This is a simplified model with just 3 main dimensions.

My measures are a distinct count of EncounterKeys on DiagnosisEventFact and distinct count of EncounterKeys on LabComponentResultFact.

The sample report which is being developed is given a selection on diagnosis dim side, show counts of unique visits for this diagnosis and counts of lab collection visits for this diagnosis. 

diagnosis           count_visits_diagnosis                count_visits_labs

-----------------------------------------------------------------------------

ABC                         5                                          0

DEF                         10                                        5

 

etc

 

The problem comes when I select ALL diagnoses in the report. Counts for diagnoses measure loads instantly, while counts of labs measure takes forever. When I select a few diagnoses report works fast.

How to improve my SSAS tabular model or computation to handle this sort of reporting efficiently? 

Unfortunately I can not re-model data on the data source side.

 

Model.JPG

 

 

2 REPLIES 2
v-yuezhe-msft
Employee
Employee

@yvtoropov,

What is the DAX formula do you use to create the measure? What do you mean that "select ALL diagnoses in the report"? Do you use filters or directly select rows in the visual?

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

I am using =DISTINCTCOUNT(LabComponentResultFact[EncounterKey]) and =DISTINCTCOUNT(DiagnosisEventFact[EncounterKey]) for measures.

By 'selecting all diagnoses' I mean when creating a pivot table report I put both measures on columns and all values in diagnosis dimension on rows.

Regards,
Yuriy

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.

Top Solution Authors
Top Kudoed Authors