Work flow for Newbie

[ERC2]

I am trying to learn powerpivot and wanted to know the best way to set up my data. I get an extract of student test data in a spreadsheet. It has the student name, id, grade, and the test scores for a given test. Each time they take another test, it adds a row of data. At the end of the year, most students have 4 or five rows of data. My goal is to link it to another table that has attendance data and another table with discipline data. Each table has a shared student ID. The problem is each table has multiple rows of data for each student. each day absent, each test administration, each infraction is a new row. I would like to have one row with each column representing the cumulative record. Any suggestions as to the most efficient way to do this?

I am brand new to powerpivot and struggling with Russo and Ferrari's book as my guide.
Thanks,

 

[Tianbas]

Sounds like your data are already in the way PowerPivot likes it -> many rows, less columns

What would really help in your model is a Student table that has one ID column per student. If you cant extract that directly take your above student test data, make a pivot out of it with student id and upload that as a separate table to your power pivot model. Now you can create relationships between your test data, the ID table and all your other tables using the student ID. If you create a (Power-) pivot table from that model use the student ID from the ID table in rows and you should be able to connect all the data from the different tables in one pivot

 

[ERC2]

Sounds like your data are already in the way PowerPivot likes it -> many rows, less columns

What would really help in your model is a Student table that has one ID column per student. If you cant extract that directly take your above student test data, make a pivot out of it with student id and upload that as a separate table to your power pivot model. Now you can create relationships between your test data, the ID table and all your other tables using the student ID. If you create a (Power-) pivot table from that model use the student ID from the ID table in rows and you should be able to connect all the data from the different tables in one pivot

Thanks I will get to it.

 

[scottsen]

Also, while Marco and Alberto's book is fantastic, it is also tough on the beginner. I would get Rob Collie's book (not Alchemy, the first one).