I'm currently building an education data warehouse.

I have three factless fact tables for distinct set of grades due to the fact I have no measures (I'm not aggregating anything), the grades are completely unrelated to each other hence why they're in different tables. I'm only slicing student dimension by grade fact tables.

In Excel, when I drag a student onto a row and then grades into columns, I would expect to see grades for the student. However, I am just seeing all students/all grades. The relationships do not seem to be working even though I can clearly see the relationships in the tabular diagram view.

Any ideas as to why this is?

As you can see below, SSAS has imported the relationships; however when I browse in Excel there does not seem to be any relationship between the data. If I filter by one row for student and select grades, I just get all grades not just the grades relevant for that student.

enter image description here

enter image description here enter image description here

If I drop student keys in the row section and grades in the column section, it will just display all/all. If I change the student key filter to display a few records nothing seems to change, so SSAS is fairly useless to me at the moment.

enter image description here


2 Answers 2


I think i've found the problem. SSAS doesn't seem to relate data between tables if there are no measures defined. I added a simple measure like grade count and voila i have my data relationship.


From the Looks of the image I asked you to post, you have a many to many relationship between students and grades

many students can have many grades

I can not tell from either Image, if each entity Contains a Grade. Consider breaking each relationship into a one to many.

one student to many grades

In order to accomplish this, each entity that can contain a single grade should:

exam paper needs grade [or score]
all the ones you've blotted out need grade [or score]

Then each graded entity, should have a 1 to 1 relationship with student. If a student has multiple exam papers, then each exam paper should be linked to student, but not to each other. Also, the Grade Columns cannot be named Grade Type in One table and Score in Another. They must be named Grade in every table, or score in every table.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.