locked
Powerpivot relationships RRS feed

  • Question

  • Hi,

    I have three tables which look like the following:

     

    Table 1 - customers

    account, name, credit status

    123,ABC Ltd, Good

    333,NYC Co, Good

    Table 2 - invoices

    account, InvoiceNo, InvoiceDate, InvoiceValue

    123,AAAA1,05/01/2012,£500

    123,AAAA3,20/01/2012,£450

    123,AAAA4,25/01/2012,£600

    333,AAAA7,15/01/2012,£300

    333,AAAA8,19/01/2012,£250

    Table 3 - calls

    account, CallStart, CallReason

    123, 10/01/2012, ProductA Support

    123, 29/01/2012, ProductF Support

    333, 30/01/2012, ProductC Support

    333, 03/01/2012, ProductE Support

    333, 28/01/2012, ProductA Support

     

    Table 1 (customers) ( links 1:many) with Table 2 (invoices) and Table 3 (calls).  I have linked these in Powerpivot and I can see in the 2 seperate pivot tables, invoice details per customer and call details per customer.   However, for true self service, I need this to be in 1 pivot table veiw.   Fro example,

    I want my users to be-able to drill into a customer to see each invoice and then pull in the call details for that customer whilst still being able to see the invoice details, in the same view, like the below:

    level 1 - 123

    Level 2 - 123,AAAA1,05/01/2012,£500

                  123,AAAA3,20/01/2012,£450

                  123,AAAA4,25/01/2012,£600 

    Level 3 - 123, 10/01/2012, ProductA Support

                  123, 29/01/2012, ProductF Support

    Please could you let me know if this can be done and what the best practice is?

    Thanks

    Maverick

     

     

     

     

     

     

     

    Monday, January 30, 2012 9:59 AM

Answers

  • Hi Maverick

    I looked at your sample data but can't visualize a way to tie all three tables.   Yes, there is a number of ways in which you can shape the data you load into PowerPivot (the data model) but in this case I can't seem to understand how would you want the output to look like.

    The invoice dates and call dates do not match so if we place them under the customer in a pivot table,  the Invoice Value and Call Reasons do not have any date relationship to each other and as a consequence would just repeat all values, which is obviously incorrect.

    Having a pivot table drill down in one path using one table and then in another path using another table is not something that seems possible or even intuitive to the user.  It appears to me you should consolidate your Calls and Invoices tables into one, in which case you *can* create a consolidated view of the data in a pivot table. Clearly, to get this done, you must find the correct level of granularity across both tables, and retrieve data at that level.   For example, you could have a table define like

    Call ID,   Invoice Date,  Call Date, Call Reason, Invoice Amount

     

    For a bit more explanation of how relationships work, check out this blog entry:   http://javierguillen.wordpress.com/2012/01/27/working-with-relationships-in-powerpivot-ssas-bism-tabular/

     

     




    Javier Guillen
    http://javierguillen.wordpress.com/


    Wednesday, February 1, 2012 8:30 PM
    Answerer

All replies

  • Hi,

    I am having problems trying the relate data tables to provide what I would see as true self service.  I need my users to be-able to see important data about our customers in the same pivot view but Powerpivot does not easily support linking to data tables via a bridging table.   Please could someone help me with this problem, I have provided more information below which should help understand my problem.

     

    I have 3 tables I need to link together. 

     

    Table 1 - customers

    account, name, credit status

    123,ABC Ltd, Good

    333,NYC Co, Good

    Table 2 - invoices

    account, InvoiceNo, InvoiceDate, InvoiceValue

    123,AAAA1,05/01/2012,£500

    123,AAAA3,20/01/2012,£450

    123,AAAA4,25/01/2012,£600

    333,AAAA7,15/01/2012,£300

    333,AAAA8,19/01/2012,£250

    Table 3 - calls

    account, CallStart, CallReason

    123, 10/01/2012, ProductA Support

    123, 29/01/2012, ProductF Support

    333, 30/01/2012, ProductC Support

    333, 03/01/2012, ProductE Support

    333, 28/01/2012, ProductA Support

     

    Table 1 (customers) ( links 1:many) with Table 2 (invoices) and Table 3 (calls). I have linked these in Powerpivot and I can see in the 2 seperate pivot tables, invoice details per customer and call details per customer. However, for true self service, I need this to be in 1 pivot table veiw. Fro example,

    I want my users to be-able to drill into a customer to see each invoice and then pull in the call details for that customer whilst still being able to see the invoice details, in the same view, like the below:

    level 1 - 123

    Level 2 - 123,AAAA1,05/01/2012,£500

    123,AAAA3,20/01/2012,£450

    123,AAAA4,25/01/2012,£600

    Level 3 - 123, 10/01/2012, ProductA Support

    123, 29/01/2012, ProductF Support

     

    Thanks

    Maverick

    • Merged by Challen Fu Wednesday, February 8, 2012 8:07 AM duplicated thread
    Tuesday, January 31, 2012 5:24 PM
  • Hi Maverick,

    Drill-down exist in Hierarchy relationship, in your scenario, Invoice and Call has no relationship, it is not Hierarchy structure, so it appears to me that we cannot achieve this. If Invoice and Call has relationship, you can join them together. Then display all the information on the one pivottable, but you still cannnot have this kind of drill-down just display them on the one PivotTable. Thanks for your understanding.

    Regards,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 1, 2012 8:52 AM
  • Thanks Challen,

    Just one point, the invoice data has an account field which relates to calls also (by account).   I would have thought Powepivot would be flexible enough to select invoice number, then value, then customer, then from customer we should be-able to see all calls from that customer.   We need this because my users need to be-able to see when the customer has contacted us, for each outstanding invoice.  If this is not possible then the user would have to remember the invoice and customer detailsa, then start the pivot again.  

    I had been told that there are ways to make Powerpivot more flexible to enable easy self service for users.  

    Thanks

    Maverick

    Wednesday, February 1, 2012 9:09 AM
  • Hi Maverick

    I looked at your sample data but can't visualize a way to tie all three tables.   Yes, there is a number of ways in which you can shape the data you load into PowerPivot (the data model) but in this case I can't seem to understand how would you want the output to look like.

    The invoice dates and call dates do not match so if we place them under the customer in a pivot table,  the Invoice Value and Call Reasons do not have any date relationship to each other and as a consequence would just repeat all values, which is obviously incorrect.

    Having a pivot table drill down in one path using one table and then in another path using another table is not something that seems possible or even intuitive to the user.  It appears to me you should consolidate your Calls and Invoices tables into one, in which case you *can* create a consolidated view of the data in a pivot table. Clearly, to get this done, you must find the correct level of granularity across both tables, and retrieve data at that level.   For example, you could have a table define like

    Call ID,   Invoice Date,  Call Date, Call Reason, Invoice Amount

     

    For a bit more explanation of how relationships work, check out this blog entry:   http://javierguillen.wordpress.com/2012/01/27/working-with-relationships-in-powerpivot-ssas-bism-tabular/

     

     




    Javier Guillen
    http://javierguillen.wordpress.com/


    Wednesday, February 1, 2012 8:30 PM
    Answerer
  • Wednesday, February 1, 2012 8:41 PM
    Answerer