Forum Discussion
Advocate IPower BI Data Modeling Question
I have 2 questions on my data modeling:
For my Power BI report requirement I had to integrate 2 data sources of different grain
Student Data Source(176 columns) - Student identifier, term, program,major, major school, major department, student level, type ,demograhics - ethnicity,citizenship,country origin, sex - Min 1 row/per student to a max of 3 rows based on the major count and program count for each term
Course Data Source(315 columns) - Student identifier, term, course attributes - course number, course section, instructor, active course details,credit hours - Min of 4 row/student to 6 rows for each term
Additionaly, both the datasources have snapshots captured everyday. So for example for each snapshot, the student data source has 9000 records and course data source has 45000 records roughly. Both the tables has 3 years of data and roughly the data count is around 35 million.
Due to the nature of the grain if I combine these flattened tables, it becomes a cartesian join and the performance would be worse. So following are the steps taken:
1. Removed the unwanted columns from the silver layer and moved the refined tables to gold layer:
Student Data Source(81 columns)
Course Data Source (119 Columns)
2. Created bridge tables as below to filter both the data sources on a common slicer
Dim School - union of distinct schools from both the data source
Dim Department - Union of distinct department from both the data sources
Dim Student Level - Union of distinct Student Levels from both the data sources
Dim Term - union of distinct terms from both the data sources
I used the onelake mode semantic model instead of import or Direct query, due to the number of columns and the multiple snapshots.
Earlier, when I tried with import mode, I had delay issues in rendering the data to the report, whenever I made any changes to the datatype/renaming the values,
etc.
It took 45 minutes per change. So I opted for the Directlake mode, which helped in quick data rendering.
Question 1 - Is my approach correct?
Question 2 -
2.1 The dims I created are having blanks, but if I ignore the blanks, my totals match.
2.2 Although it is a union, I see some values missing.
Example: Student Data Source has an additional department Healthcare Management offering a Major, but that department is not in Course Data Source, as they don't offer any courses
when I list the department in a separate slicer, I see the Healthcare Management, but if I add them in a matrix adding school(from dim_school) and the department(from dim_department), and select the Student enrollment metric(built using student data source), healthcare management is missing. What could be the reason?
Thanks for reaching out. I changed my design and created separate views for all 3 metrics instead of using parameters. I was able to achieve the right numbers and did not miss any departments with this approach! I used bookmarks to handle the same effect
4 Replies
- krishnakanth240
Super User
Hi RajiKarthik
1:Yes, the approach is correct. Keeping the Student and Course facts and using shared dimensions instead joining two fact tables.
2.1:Blanks usually indicate null dimension keys in the source. You can check the relationship and key columns. If totals match, the blanks represent records without a dimension value.
2.2:Most likely relationship or filter-direction issue. A shared Dim Department can show Healthcare Management in the slicer but if the matrix combines School + Department and the student fact is not correctly filtered through those dimensions then the combination might disappear.
Please check these
Relationship between Dim Department - Student fact. Relationship cardinality and cross-filter direction. Whether Healthcare Management has a valid matching department key in the Student fact. Whether the matrix is using both dimensions from the same model and the DAX measures
RajiKarthikThank you for your response. I think I found the issue, but still not sure about the fix. I created a parameter that includes metrics from both data sources. If I use that parameter to change the metric, the Student -> Healthcare management disappears. Instead, I tried validating by just placing the count of students (same measure in parameter), and it works. I need to figure out why the parameter is behaving strangely!
v-abhinavmuCommunity Support
Hi RajiKarthik,
Thanks for reaching out to the Microsoft Fabric Community forum. and thanks to krishnakanth240 for sharing valuable insights.
That additional test is useful. Since the Student measure works correctly when added directly to the matrix, I would compare that result with the same measure when selected through the field parameter.
Microsoft documents that field parameters can dynamically switch measures or dimensions in a visual, and measure-based field parameters can be used in the Values field.
You could check the following:> Test each measure from the field parameter directly in the matrix and compare the results.
> Verify that the parameter uses explicit DAX measures, as implicit measures aren't supported for field parameters.
> Review the field parameter definition. Microsoft documents three columns for each field: name, field reference, and order.
> Compare the DAX of the affected measure with the direct measure, particularly where filter context or relationship propagation is involved. Microsoft notes that explicit measures can affect filter context and relationship propagation.
This comparison should help identify whether the difference is related to a particular measure or to how it is represented in the field parameter.
For more details, please refer to the below Official Microsoft Documentation:
Use Field Parameters in Power BI Reports - Power BI | Microsoft LearnUnderstand star schema and the importance for Power BI - Power BI | Microsoft Learn
I hope this helps. Please feel free to reach out if you have any further questions.
Thank you.
- RajiKarthik
Thanks for reaching out. I changed my design and created separate views for all 3 metrics instead of using parameters. I was able to achieve the right numbers and did not miss any departments with this approach! I used bookmarks to handle the same effect
