none
Tabular mode ssas fact table join with one dimension table but certain data belong to the dimension are not listed in Excel pivot table

    Question

  • I have one fact table contains a site Key.

    the key value are: 

    2000

    2001

    2004

    3000

    4000

    5000

    The fact table contains all of these site keys.

    The relationship is many to one from fact table to dimension table connect using the site key.

    but when I deploy the project and use Excel to pull the data into a pivot table.

    when I set row as the site key.

    Only 2000 , 2001, 2004 are showing.

    3000, 4000, 5000 are not showing up.

    But in the fact table I do have the data with site key 3000, 4000, 5000

    I compare the table column collate it is the same.

    Not sure what could cause this.

    I am using SQL 2017 standard version with tabular mode for ssas

    Deploy tool: visual studio 2017 community version: 15.7.3

    Thank you very much for the help.

    Chad Chen




    • Edited by Chad_Chen Monday, June 11, 2018 9:00 PM
    Monday, June 11, 2018 8:56 PM

All replies

  • Hi Chad,

    Thanks for your question.

    >>>when I set row as the site key.Only 2000 , 2001, 2004 are showing.3000, 4000, 5000 are not showing up.But in the fact table I do have the data with site key 3000, 4000, 5000.Not sure what could cause this.
    This might happen when site key 3000, 4000, 5000 does not exist in your dimension table which contain site key, site key 3000, 4000, 5000 in your fact table will binding to unknown member in your dimension table. That's why you will only see site key 2000 , 2001, 2004 in your Power Pivot report. In this scenario, you might need to add site key 3000, 4000, 5000 to you dimension table.


    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 Tuesday, June 12, 2018 8:17 PM
    Tuesday, June 12, 2018 4:48 AM
    Moderator
  • Hi,

    To verify what Willson suggested you could write a SQL query which joins between fact table and dimension table on the key column and verify the same.

    Cube does an inner join when it process the data.

    HTH,

    Ram


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

    Tuesday, June 12, 2018 1:10 PM