Jump to content


Photo

Lesson 6-2 Creating a simple data model


  • Please log in to reply
11 replies to this topic

#1 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 30 April 2015 - 01:52 PM

Hi jonathan,

 

I tried following this lesson for my own project but I didn't get the desired result.

 

1. I successfully created the relationship between the 2 tables to define my data model. However, when I get into the insert pivot table, external data source, choose connection, then table, I saw 4 tables instead of 2. I already deleted those 2 tables and was surprised that the tables deleted were still there. Can you help me?

2. Also, I noticed that I revised the one of the tables, I deleted one column. But that deleted column is still apprearing in the olap table. Can you also help me on this?

 

I attached the said file for your reference.

 

Thank you so much in advance.

Mon

Attached Files



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 30 April 2015 - 08:37 PM

Hi monq,

 

The problem you're seeing is a bit of a quirk in Excel's data model features.  Even after you delete tables and relationships, traces of them can remain behind as 'data connections', which can lead to lots of problems further down the line, as the old connections will not work properly if you try to use them.

 

You can find these connections by clicking Data > Connections > Connections.  The simplest solution is often to remove all of the connections and start again, just to make sure that all of your connections are valid.

 

Hopefully Microsoft will fix this bug in a later version of Excel, but for the moment users should be very careful when creating data models.  If you don't get it right the first time and try to delete and recreate what went wrong, it's likely that problems will occur due to Excel holding onto pieces of the original model.

 

I hope this helps and you're able to solve the problem, but if not please feel free to reply and I will take another look at it.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 30 April 2015 - 09:20 PM

Thank you Jonathan for your quick reply.

 

I followed your instruction and it worked properly, the data connections were all gone.

 

However, when I started creating the new data model, the relationship won't work. It says there's a duplicate data in my primary key in the studentlist table. I double checked my studentlist table but can't seem to find any duplicates. Besides, it worked before, why can't it work now? 

 

Appreciate your inputs.

 

Thanks, again.



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 01 May 2015 - 11:10 AM

Hi monq,

 

I tried this on the copy that you attached and it seemed to work fine for me.  All I did was remove all of the data connections and then recreate the relationship.

 

My guess is that you may have tried to create the relationship with the Student table as the foreign key table instead of the primary key table.  It's important to remember that the foreign key must be specified first when creating a relationship and then the primary key, so your relationship should look like this:

 

keyexample.png

 

For the video lesson explaining how to create relationships, see: Expert Skills Lesson 6-2 Create a simple data model.

 

I have attached a copy of your workbook with the relationship defined correctly:

 

keyexample2.png

Attached File  PinkDailyTransactionRecordrev2.xlsx   276.64KB   245 downloads

 

I hope this is what you needed, but as usual please feel free to reply if I can offer any more assistance.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 05 May 2015 - 02:26 PM

Dear Jonathan,
 
Thank you so much. It's working right now.
 
I just have a follow-up question. I was trying to create the many to many relationship. I have just started to create the relationship between one table (parents) that has a primary key and the link table (parentsstudent). But the pivot won't show it right.  I can't find where the error is. I have attached the file for your reference.

 

 

monq

Attached Files



#6 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 05 May 2015 - 02:39 PM

Dear Jonathan,

 

I would like to join the student and parent tables. I made the link table parentsstudent and just starting to create the relationship between parentsstudent table and parent table when I noticed the pivot table not working properly.

 

Appreciate your help.

 

Thanks.



#7 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 05 May 2015 - 03:26 PM

Hi monq,

 

I can't see a problem with your relationship, but your pivot table isn't working because you haven't specified anything to be used as 'Values'.

 

In order to use relationships correctly, pivot tables need some kind of value to summarize. In this case, you can simply add the StudentID under Values and the relationship should be processed correctly. This adds an extra column that's not very useful, but you can simply hide this if you don't want to see it.

 

For more on creating pivot tables, see the video lessons in Expert Skills Session 5: Pivot Tables.

 

I have attached a copy of your workbook with the relationships completed and a pivot table that uses them added:

 

Attached File  TrialDatabasePink3.xlsx   319.98KB   201 downloads

TrialDatabasePink3.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#8 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 06 May 2015 - 05:38 AM

Dear Jonathan,

 

Thank you for help. I understand now. In fact, I already watched the whole session 5 about pivot tables. In deepens my understanding in pivot/

 

Anyway, I want to go back to my current project. I am creating the relationship between DailyTransaction table and StudentList table. I successfully did. However when I went to pivot table, it won't work. The relationship is there and I could see it is already existing in the connections, But I can't solve why it won't work in the pivot. The DailyTransaction table is still not connected with the 3 tables, StudentList, ParentsStudent and Parents. Can you help me again identify where the problem is?

 

rgds.



#9 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 06 May 2015 - 11:50 AM

Hi monq,

 

I suspect the problem is that you're not including the ParentsStudent table in your pivot table's data fields.  In order for a relationship to work in a pivot table, you must include at least one field from all of the tables along the relationship 'chain'.

 

In other words, Excel won't automatically figure out the relationship between StudentList and Parents just because a relationship is present. You must include ParentsStudent in the pivot table in order to make the link between StudentList and Parents.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#10 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 06 May 2015 - 10:31 PM

Dear Jonathan,

 

Isn't it that ParentsStudent Table is aleady connected by StudentID field? It is in fact one of the primary keys in that table.

 

The relationship between Parents table (with ParentsCode as primary key), ParentsStudent table (joining table with ParentsCode and StudentID as primary keys), SudentList table (with StudentID as primary key) is working properly. This is the question I asked before which you have given reply and I understood already.

 

This time, I created the relationship between StudentList table (with StudentID as primary key) and DailyTransaction (with StudentID as foreign key) but it doesn's link to the relationship above. My intention is to link it to the current relationship which from my understanding it will connect because DailyTransaction has StudentID as the foreign key. When I checked the amount paid in the Daily Transaction table, the pivot report doesn't connect. I hope you can identify where the problem is. 

 

Please see attached file for your reference. 

 

Thank you so much. I look forward to your response.

 

rgds. 

Attached Files



#11 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 07 May 2015 - 12:48 PM

Hi monq,

 

I've given it a look and I'm having the same problem as you - I can't get Excel to work with both the many-to-many relationship between StudentList and Parents and the one-to-many relationship between StudentList and DailyTransaction at the same time.

 

I suspect this is not something that can be achieved with Excel's current data model features, as they are still quite primitive compared to true database products.

 

An alternative solution would be to create two different pivot tables and link them together using a Slicer.

 

You can see a video showing how to create Slicers in: Expert Skills Lesson 5-8 Filter a pivot table visually using slicers.

You can see how to link a Slicer to multiple pivot tables in: Expert Skills Lesson 5-25 Use slicers to filter multiple pivot tables.

 

I have attached an example:

 

Attached File  TrialDatabasePink3rev.xlsx   460.93KB   138 downloads

TrialDatabasePink3rev.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#12 monq

monq

    Member

  • Members
  • PipPip
  • 11 posts

Posted 10 May 2015 - 10:21 AM

Hi Jonathan,

 

This is a great alternative solution.  Thank you so much for the help. This forum excites me a lot as I am gaining insights from experts like you and I'm learning a lot.

 

Little by little, I'm beginning to uncover the limitations of excel to certain desired results.

 

I hope you will not get tired of answering my questions. I am sure I will find more as I go through this excellent course. Again, thank you.

 

Best rgds.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users