none
bridge table with M:M

    Question

  • Hi,

    I have PO table with 6 attributes(po_line#,po_number,po_rcpt#,po_release#,po_buyer,po_code) with no key columns.I cannot have a composite key to serve as a key because there are duplicates.Another fact table called Sales with all other attributes besides the PO attributes as above.I am populating the PO and Sales from a single table.

    I want to connect the PO table with sales table.The distinct of po_Rcpt#+po_line# serve as a key without other columns.I would like to know how bridge table between PO and Sales could be used to achieve the above.

    I have loaded distinct of po_rcpt#+po_line# in bridge and have connected that key with PO table key(concat(po_rcpt,po_line#)).It works as 1:m now.Also,i did the same between fact Sales key concat(po_rcpt,po_line#) join with bridge table key.Everything seemed to look fine,but I get cross join kind of results when i use measure from Sales and query the columns from PO.I am sure missing something,but dont know what.Pls advise.

    Tuesday, May 15, 2018 7:03 PM

All replies

  • Hi,

    When you say both are populated from the same table, you can still concatenate the columns into one using a group by function for dimension and link the composite column to the fact table with the same concatenation.

    In this case you will have direct 1:M relation between dimension and fact.

    I don't see a need of a bridge table.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Tuesday, May 15, 2018 8:58 PM
  • I am not clear about the suggestion.Can you please elaborate with a query.Also,my PO table comes as a union of 2 different tables,hence the duplicates.
    Tuesday, May 15, 2018 9:14 PM
  • Hi,

    What i am saying is that the dimension table which you generate can contain unique values. Something like this

    select distinct col1, col2,col3,col4,col5,col6,concatenation(col1....col6) as key from table a

    this will form your dimension table.

    Now the fact will have the same concatenated column and you can link your fact and dimension using the concatenated column which will still act as 1:M relationship.

    HTH,

    Ram


    Please vote as helpful or mark as answer, if it helps

    Tuesday, May 15, 2018 9:25 PM
  • Cube is implemented in a way that we lookup attributes in dimension without having to store them as facts in fact table.There is no meaning to have all the attributes in fact and dimension just for the sake of a key.
    Tuesday, May 15, 2018 11:34 PM
  • Hi msdnpublic1234,

    Thanks for your question.

    Would you minding typing out 10 to 20 rows of sample data for PO table and sales table?

    One more thing to note, the design of your data model mainly depends on the requirement of your customers, it is much better if you can share the requirement for this data model.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 16, 2018 6:30 AM
    Moderator
  • Willson,

    Attached the 3 tables from model.Pls note that the Sales table has much more columns which I dont worry about.The measure i have is:

    TotalRejects:=CALCULATE(COUNTROWS(DISTINCT('Sales'[COLLECTION_ID])),FILTER('Incoming Inspection','Sales'[FinalDate]=1&&'Sales'[INSPECTION_RESULT]="Reject"))

    The bridge,sales and PO share the same composite key of po_rcpt#+po_line#.The relationship is 1:n and n:1 with bridge table included.The behaviour that is odd is when i slice by any column from PO table,i get Cartesian product despite having measure.The row has to return exactly one value for PO_rcpt or po_header_id per row for the sample attached as per the db,but it gets me cartesian values.I dont know what has to be changed to allow slicing by PO attributes.I tried lot of functions like all,allexcept,iscrossfiltered but no luck.

    Pls help me fix this as i have already spent enough hours on figuring out.I fear that my join between PO and Sales are not working somehow even with bridge table in place.

    Bridge:

    Sales:

    PO:

    Pivot with measure:


    Wednesday, May 16, 2018 4:41 PM
  • Actually,i figured out a problem.The cartesian product is happening because i need to enable bidirection filter between bridge,Sales and PO tables.It worked with bidirectional filter enabled in powerBI.In ssas tabular 2014,i dont see that crossfilter function.Pls suggest a workaround to achieve that functionality.
    Wednesday, May 16, 2018 6:31 PM
  • Hi msdnpublic1234,

    Thanks for your response.

    Many-to-may relationship is not supported in SQL Server 2012 and 2014. SQL Server 2016 will contain this feature, thus, you can try to upgrade to SQL server 2016. The simple work around to solve this issue is to place all related tables in filter. This shortcut worked in my environment. Example is given below for your reference:

    TotalRejects :=
    CALCULATE (
        COUNTROWS ( DISTINCT ( 'Sales'[COLLECTION_ID] ) ),
        FILTER (
            'Incoming Inspection',
            'Sales'[FinalDate] = 1
                && 'Sales'[INSPECTION_RESULT] = "Reject"
        ),
        'Bridge',
        'PO'
    )
    


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Sunday, May 20, 2018 11:27 AM
    Thursday, May 17, 2018 1:16 AM
    Moderator
  • Willson,

    I already tried that and it gave me error with columns having numbers but with po_buyer,i get the correct values.The error says analysis server is out of memory and very large OLAP query is still running.


    Thursday, May 17, 2018 2:20 AM
  • Hi msdnpublic1234,

    Thanks for your response.

    >>>I already tried that and it gave me error with columns having numbers but with po_buyer,i get the correct values.The error says analysis server is out of memory and very large OLAP query is still running.

    If you have big table for table Bridge and Po, you might encounter this out of memory issue. In this scenario, I would suggest to upgrade to SQL Server 2016. You can enable bi-direction filter in SSAS tabular 2016 with compatibility level  1200.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 17, 2018 6:51 AM
    Moderator