locked
Is this data less than ideal to view in powerpivot? RRS feed

  • Question

  • I have data in the format shown in the diagram below. It came from a rough and ready spreadsheet and i had to compile a quick and dirty report. Simple enough after importing into SQL Server. 

    But I also tried with powerpivot and had a lot of issues and am wondering if this data is not ideal for powerpivot.

    Issues:

    - when i dragged on Applicant Name from the Applicant Table & qualification Name from the qualifications table, each applicant had ALL the qualifications under them, not just their qualifications. I fixed this by creating a dummy measure on the qualification table, but this seems like a terrible workaround.

    -when i dragged on the Score_PartA, the sum was not correct. It summed up the value based on all the qualifications. e.g. if the applicant scored 5 on partA, and the applicant had 5 qualifications, the measure showed 25. i got around this by selecting the MAX, but again, this feels hacky.

    Id appreciate any feedback on how i could approach this better.

    Friday, August 28, 2015 6:37 PM

Answers

  • 1) The MDX (yes, all Excel pivots generate MDX - this is translated by the SSAS instance (yes, Power Pivot runs a private instance of SSAS to support the Tabular model you create in Power Pivot) into DAX to return the appropriate data) that Excel generates in a pivot table uses a cross-join for any fields dragged onto the rows/columns. This is an artifact of the aggregation (note pivot tables are strictly an aggregation tool, not a query tool; while it is possible (and common) to use a pivot table as a poor-man's query builder, the results are not always reasonable, as you've discovered, and this use case is therefore not recommended) tool you have used as a front end to your Tabular model, and not an artifact of the relational model in the Tabular engine. The MDX generated is wrapped by a function that removes rows where the measure evaluates to a null value, but this only kicks in when a measure is included, as you discovered.

    2) As Steelleg noted, relationships in a Tabular model are (currently) limited to uni-directional relationships. Context only propagates from the lookup, not from the looking-up table.
    Without further insight into your model (sample data, or even what your intention/desired outcome is), it is very difficult to provide assistance.

    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Friday, August 28, 2015 11:12 PM
    • Marked as answer by Charlie Liao Tuesday, September 8, 2015 2:01 AM
    Friday, August 28, 2015 8:12 PM

All replies

  • Hi,

    for second issue I think you have relationship between Applicants and QuestonareResults in wrong direction. In your PowerPivot model.

    for first issue I am not sure why is that but that is how it works. You have to have some measure.


    Friday, August 28, 2015 7:26 PM
  • 1) The MDX (yes, all Excel pivots generate MDX - this is translated by the SSAS instance (yes, Power Pivot runs a private instance of SSAS to support the Tabular model you create in Power Pivot) into DAX to return the appropriate data) that Excel generates in a pivot table uses a cross-join for any fields dragged onto the rows/columns. This is an artifact of the aggregation (note pivot tables are strictly an aggregation tool, not a query tool; while it is possible (and common) to use a pivot table as a poor-man's query builder, the results are not always reasonable, as you've discovered, and this use case is therefore not recommended) tool you have used as a front end to your Tabular model, and not an artifact of the relational model in the Tabular engine. The MDX generated is wrapped by a function that removes rows where the measure evaluates to a null value, but this only kicks in when a measure is included, as you discovered.

    2) As Steelleg noted, relationships in a Tabular model are (currently) limited to uni-directional relationships. Context only propagates from the lookup, not from the looking-up table.
    Without further insight into your model (sample data, or even what your intention/desired outcome is), it is very difficult to provide assistance.

    GNet Group BI Consultant

    • Proposed as answer by Michael Amadi Friday, August 28, 2015 11:12 PM
    • Marked as answer by Charlie Liao Tuesday, September 8, 2015 2:01 AM
    Friday, August 28, 2015 8:12 PM
  • Both answers have been very helpful, thank you.

    Relationship between Applicant and questionareResults is correct. In my data its actually 1 to 1, but in theory you could have many questionare results for each Applicant.

    Im trying to view the data in a very standard tabular way

    Applicant Name, Score_PartA, Score_PartB, QualificationName, QualificationYear

    The reason i tried a pivot table is due to the fact that there are multiple qualifications per applicant. I wanted to allow the user to see a table with just one row for each applicant and their scores, but be able to drill down to view the qualifications.

    Friday, August 28, 2015 8:21 PM
  • Hi,

    what I was trying to say is that you should double check your relationships in PowerPivot because in one-to-one scenario PowerPivot can create relationship with either direction and you dont have to notice it.

    Saturday, August 29, 2015 7:41 AM