• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Model To Use With PowerPivot and Query With DAX|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Model To Use With PowerPivot and Query With DAX|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotModel To Use With PowerPivot and Qu…
sp_PrintTopic sp_TopicIcon
Model To Use With PowerPivot and Query With DAX
Avatar
B Zafar

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
August 31, 2020
sp_UserOfflineSmall Offline
1
September 1, 2020 - 12:13 am
sp_Permalink sp_Print

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.

Image Enlarger

Image Enlarger

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
September 1, 2020 - 9:18 am
sp_Permalink sp_Print

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

Avatar
B Zafar

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
August 31, 2020
sp_UserOfflineSmall Offline
3
September 4, 2020 - 8:50 pm
sp_Permalink sp_Print

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.

Avatar
B Zafar

Active Member
Members
Level 0
Forum Posts: 3
Member Since:
August 31, 2020
sp_UserOfflineSmall Offline
4
September 13, 2020 - 9:28 pm
sp_Permalink sp_Print sp_EditHistory

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?

Image Enlarger

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4446
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
5
September 14, 2020 - 10:24 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Richard West, Shanna Henseler, Lawrence Smith, Nada Perovic
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.