none
Assistance required with a multiple vendor/single customer data model schema

    Question

  • Hi,

    I am trying to set-up a data model and currently have the below schema.

    What I am trying to represent in schema is a relationship as follows:

    K1Orders represents 'Sales', these sales are fulfilled by vendors, and the vendors have profile information (Name, Address, Type etc). 'Writer_Work_Rec' hold records of all our vendors, vendors are individuals and almost all orders have multiple vendors. This table links to 'Writer_User_Rec' that is simply a table containing the full name of the vendor (and later additional profile information). 

    I would like to produce a table that represents the vendors and all of the sales they have fulfilled. 

    The table would have rows of vendors displayed using 'Writer_FullName' value. These rows should  then have sub-rows (probably not correct terminology!) of sales ('Order_id' from the K1Orders table). The values then would be whatever I like, in this case I need 'Word-Count' which is a number column. 

    A crude representation would be:

    Joe Bloggs [Vendor]

           1234 [Sale]

           1235 [Sale]

    Tom Smith

           1236

           1237

    Adam Cook

         1238

         1239

    The current relationship does not work.  The Writer_User_Rec forms have a relationship with Writer_Work_Rec and I can produce the table if I use values from this table. However I cannot use any values from the K1Orders table.

    How can I get this to work? I'm more interested in improving my understanding of the relationships and the schema in general since there are multiple other aspects not yet added to the schema that will face the same problem such as each Sale have multiple payments and multiple payment methods etc.

    Please ask if nothing is clear and I will provide additional information.

     

    Friday, November 22, 2013 7:28 PM

Answers

  • Hi Maracles,

    This looks like a many-to-many (M2M) relationship. Power Pivot doesn't support this natively yet; it only supports one to many. It also does not carry across filters contexts going from a 'many' side to the 'one' side, only the 'one' side to the 'many' side. In your scenario, the filter context will carry across (i.e. propagate) from Writers_User_Rec to Writer_Work_Rec, but will then be lost when going from Writer_Work_Rec to K1Orders. This is why you get the the right result when you produce a pivot table using the values from the Writer_Work_Rec table but not from the K1Orders table.

    We can achieve the behaviour that you want by using the CALCULATE and SUMMARIZE DAX functions together. I have mocked-up a subset of your data model to illustrate:

    K1Orders:

    order_id word_count
    1111 2500
    2222 1500
    3333 5000

    Writer_Work_Rec:

    id writer order_id
    1 1 1111
    2 2 1111
    3 2 2222
    4 3 3333

    Writers_User_Rec:

    id fname sname Writer_FullName
    1 Joe Bloggs Joe   Bloggs
    2 John Smith John Smith
    3 Joan Mystery Joan   Mystery

    You will notice that based on the data in these tables, Joe Bloggs and John Smith have co-authored a work with of 2,500 words. John Smith has also authored a work by himself of 1,500 words. Joan Mystery has authored a work herself of 5,000 words. I am assuming that you have defined your word count measure similar to the following:

    WordCount:=CALCULATE(SUM(K1Orders[word_count]))

    Based on the test data, this would return a result of 9,000 for each of the writers as shown below

    As mentioned earlier, the filter context is being lost when going from one side of the M2M relationship to the other. If we slighy alter the [WordCount] measure by introducing the SUMMARIZE function, we can force the filter context to be kept when going from Writers_User_Rec to Writer_Work_Rec to K1Orders. This will result in the word count being evaluated correctly for each writer. Here is the formula for a measure that makes the word count measure to be 'M2M friendly':

    WordCountM2M:=

    CALCULATE( [WordCount], SUMMARIZE( Writer_Work_Rec, K1Orders[order_id] ) )

    We already know that the Writers_User_Rec will filter Writer_Work_Rec table. What the SUMMARIZE does here is to generate a list of all the K1Orders[order_id] values that are related to the Writer_Work_Rec table (taking into account any filters that have already been placed on it). It will then use this list to filter the K1Orders table before evaluating the [WordCount] measure in this context. The result can be seen below.

    Hope this helps,

    Michael

    Tuesday, December 03, 2013 2:54 PM
    Moderator

All replies

  • Maracles,

    Did you make any progress with this?

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, November 29, 2013 11:31 PM
    Owner
  • None at all I'm afraid. I'm not sure if my question was too vague or broad but I'm still struggling with the same issue. 

    Any help would be appreciated.

    Thanks.

    Monday, December 02, 2013 3:45 PM
  • Hi Maracles,

    This looks like a many-to-many (M2M) relationship. Power Pivot doesn't support this natively yet; it only supports one to many. It also does not carry across filters contexts going from a 'many' side to the 'one' side, only the 'one' side to the 'many' side. In your scenario, the filter context will carry across (i.e. propagate) from Writers_User_Rec to Writer_Work_Rec, but will then be lost when going from Writer_Work_Rec to K1Orders. This is why you get the the right result when you produce a pivot table using the values from the Writer_Work_Rec table but not from the K1Orders table.

    We can achieve the behaviour that you want by using the CALCULATE and SUMMARIZE DAX functions together. I have mocked-up a subset of your data model to illustrate:

    K1Orders:

    order_id word_count
    1111 2500
    2222 1500
    3333 5000

    Writer_Work_Rec:

    id writer order_id
    1 1 1111
    2 2 1111
    3 2 2222
    4 3 3333

    Writers_User_Rec:

    id fname sname Writer_FullName
    1 Joe Bloggs Joe   Bloggs
    2 John Smith John Smith
    3 Joan Mystery Joan   Mystery

    You will notice that based on the data in these tables, Joe Bloggs and John Smith have co-authored a work with of 2,500 words. John Smith has also authored a work by himself of 1,500 words. Joan Mystery has authored a work herself of 5,000 words. I am assuming that you have defined your word count measure similar to the following:

    WordCount:=CALCULATE(SUM(K1Orders[word_count]))

    Based on the test data, this would return a result of 9,000 for each of the writers as shown below

    As mentioned earlier, the filter context is being lost when going from one side of the M2M relationship to the other. If we slighy alter the [WordCount] measure by introducing the SUMMARIZE function, we can force the filter context to be kept when going from Writers_User_Rec to Writer_Work_Rec to K1Orders. This will result in the word count being evaluated correctly for each writer. Here is the formula for a measure that makes the word count measure to be 'M2M friendly':

    WordCountM2M:=

    CALCULATE( [WordCount], SUMMARIZE( Writer_Work_Rec, K1Orders[order_id] ) )

    We already know that the Writers_User_Rec will filter Writer_Work_Rec table. What the SUMMARIZE does here is to generate a list of all the K1Orders[order_id] values that are related to the Writer_Work_Rec table (taking into account any filters that have already been placed on it). It will then use this list to filter the K1Orders table before evaluating the [WordCount] measure in this context. The result can be seen below.

    Hope this helps,

    Michael

    Tuesday, December 03, 2013 2:54 PM
    Moderator
  • Hi Michael,

    Thanks for taking the time to write out that clear answer, much appreciated. I am just going through this now but your answer seems to make a lot of sense. I will report back with any further obstacles I come across.

    Is there a way I could set my data schema up to make this easier? 

    Thanks!

    Wednesday, December 04, 2013 5:07 PM
  • Hi Maracles,

    Based on the description of the relationship that you provided in your initial post, this is a genuine many to many scenario and the model that you came up with is a good fit :-)

    Michael

    Wednesday, December 04, 2013 5:15 PM
    Moderator
  • Michael,

    Your answer would make a good article for the TechNet Guru contest: 

    http://social.technet.microsoft.com/wiki/contents/articles/21343.technet-guru-contributions-for-december.aspx#SQL_BI_amp_Power_BI

    Thanks!


    Ed Price, Power BI & SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, December 16, 2013 6:59 PM
    Owner
  • Ed,

    Thanks for the suggestion. I'll try and get this into a wiki article soon. There are other works out there such as 'The Many-to-Many Revolution 2.0' that cover this approach in greater detail, but I suppose the real-world scenario presented in this thread will serve as a good example of its application.

    Michael




    Thursday, December 19, 2013 3:38 PM
    Moderator