locked
How does one relate tables with another in PowerPivot? RRS feed

  • Question

  • We are working with a relational database and have imported the files to PowerPivot.

    We are trying to build a highly advanced datamodel (motor) and is having troubles with the relations between the tables(entities?). We can connect through attributts like geography, year and mayby ID.

    Does pivot support N:M (Many-Many) relationships?

    Is it easier to use MySqL Connectors than Access/Pivot?
    http://dev.mysql.com/downloads/connector/

    Best regards

    Friday, November 28, 2014 10:11 AM

Answers

  • Hello,

    See
    TechNet Wiki: A Practical Example of How to Handle Simple Many to Many Relationships in Power Pivot/SSAS Tabular Models
    and Many-to-Many relationships in PowerPivot

    If you want to import data from MySQL to Power Pivot model, then you have to install a MySQL OleDB or ODBC data provider to Access the database directly.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, November 28, 2014 10:21 AM
  • Hi there Kong!

    PowerPivot is an extremely powerful object-relational database (ORDS) and should easily be able to handle the different issues you describe. It does, indeed, support N:M-relations, as long as the cardinality is compulsory (mandatory). I would further recommend you to normalize the different attributes to avoid determinants to depend on the primary key (PK) and to prevent redundancy between entity types. This normalization makes the relations run smoother when done in Powerpivot because it increase the number of entities. Even though the number of entities (and therefore also the total size) increases, the queries will run a lot faster due to the in-memory-function Powerpivot contains. This is simply because the queries run faster through tables than through columns within the same table. 
     
    When you feel you master the posibilities Powerpivot serves you, I would deeply recommend you to use MS Access to relate the different entities by defining the cardinality between them. This is by far the best option todays EDWs serves offers.

    Best regards, 
    Bill T. Smith     

    Friday, November 28, 2014 12:45 PM

All replies

  • Hello,

    See
    TechNet Wiki: A Practical Example of How to Handle Simple Many to Many Relationships in Power Pivot/SSAS Tabular Models
    and Many-to-Many relationships in PowerPivot

    If you want to import data from MySQL to Power Pivot model, then you have to install a MySQL OleDB or ODBC data provider to Access the database directly.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, November 28, 2014 10:21 AM
  • Hi there Kong!

    PowerPivot is an extremely powerful object-relational database (ORDS) and should easily be able to handle the different issues you describe. It does, indeed, support N:M-relations, as long as the cardinality is compulsory (mandatory). I would further recommend you to normalize the different attributes to avoid determinants to depend on the primary key (PK) and to prevent redundancy between entity types. This normalization makes the relations run smoother when done in Powerpivot because it increase the number of entities. Even though the number of entities (and therefore also the total size) increases, the queries will run a lot faster due to the in-memory-function Powerpivot contains. This is simply because the queries run faster through tables than through columns within the same table. 
     
    When you feel you master the posibilities Powerpivot serves you, I would deeply recommend you to use MS Access to relate the different entities by defining the cardinality between them. This is by far the best option todays EDWs serves offers.

    Best regards, 
    Bill T. Smith     

    Friday, November 28, 2014 12:45 PM