Dear Forum Members!
I hope you are well. I am currently trying to build a basic data model to then use in PowerPivot and PowerBI to create the reports I need. Until I have the right model in place, I cant proceed since I have tried other ways of organizing my data, and at some point the PowerPivot loses its functionality.
Somethings to note: Most of the fields in the datamodel are String. Therefore, I need to use DAX to be able to work with them in pivottables
The problem I am encountering is this:
When setting up my data model as given below, I have created the Dimensions tables as shown.
Each questions response is recorded as either a 0 (Wrong), or 1 (Correct). The sum of the values for each student from any particular test then gives the total score on the test.
I need to now create a Fact table that should be able to record the answers to each question by every student that takes a particular test. The powerpivot should then work in order to show me the breakdown of the results recorded as a 0 or 1 for each question that the student has attempted. I can use slicers to slice my resulting data by a particular test (TestID), test type (TestLevel, TestNumber, TestSkill), or even a particular student. I initially had all the data in a flat file and used slicers in the way I have described and have shown that below the data model diagram. I now want a multitable model to be able to import the data, store it, and use it more efficiently.
I am not sure how to set up a Fact table to record the results and then summarize them in the manner I have described. Any help would be appreciated.
Hi and welcome to our forum.
I think you should approach this from a different angle. If you already had a flat table, this is essentially your fact table. From that table you should identify what attributes you can take out of the fact table and put into dim tables.
e.g. You only need the test ID in the fact table. The TestName, TestLevel, TestNumber and TestSkill can all be removed and placed in the TestMaster table along with the unique list of Test IDs, as you've already identified. Likewise for the StudentID, which will remain in the fact table and the StudentName and StudentEmail will reside solely in the dim table along with the unique list of StudentIDs for the purpose of creating a relationship between the two tables.
Removing these dim columns will leave you with the data for the fact table.
I hope that points you in the right direction.
Mynda
Hello Mynda and thank you for your response!
So if I understand correctly, what you've recommended is actually how I have structured my current data model as given in the diagram. The Fact table only has TestID and StudentID at the moment.
However, when a student takes the test, the results file for that text is exported as a separate excel file.
So I have to then export data from each results file into a central results table which should include:
TESTID (as you recommended)
STUDENTID (as you recommended)
TIME OF TEST
DATE OF TEST
DURATION OF TEST
QU-01
QU-02
...
QU-NN (Where QU-## are columns)
This way I can get one row that corresponds to a students results for a particular test
The problem arises when I try to relate the QU-## to the QU-## in the Dim_QuestionMasterTable. I thought that giving each question a unique QUESTIONID (made by combining the TESTID and QU-##) would solve the problem but I cant get it to work. In addition, since the values for the QU-## field are 1 or 0 the values in the Pivot Table essentially result in sums or other arithmetic results due to which I have to use DAX. I've really been trying so hard to get a good data model working to help my students with remote learning but it seems way too hard.
So I changed the Data Model and this is the ER Diagram I came up with. I had a couple of questions:
Is it alright to create a Dim table that has a relationship with other Dim's but the others don't have any relationships between themselves? Kind of like a Dim hub?
Since each student has his/her own response to a particular question, I added StudentResponse as a column in the Fact_Results Table.
Would this model work? There is also one more issue: Where can I record the duration of time a student took to complete a test? Since the Facts_Results Table only records student responses to individual questions, I cannot put it in this table. Should I create another Table for this? Which brings me to another question, should I create Date and/or Time tables as I've seen with other data models being developed to record data for date and time?
Your diagram doesn't indicate the direction of the relationships, but as long as the outer dim tables always filter the inner dim tables and not vice versa, then you can have a snowflake structure as you have in the image above.
That said, I would also have the Test ID field in the fact table and have a direct relationship between the Dim_TestMaster table and the fact table, as this will also facilitate a relationship between the new fact table you'll need for the test start and finish times, which needs the following fields:
- Date
- Test ID
- Test Start Time
- Test End Time
You only need a date table if you have dates in your model, which you don't at the moment, but I'd have thought it makes sense to add a date field to the fact tables.
Mynda
