Post reply

Pivot shows single record field value that doesn't agree to tabualr data model value (source data) - misaligned?

  • July 4, 2017 at 4:02 pm

    #327036

    Hi All

    I have found something I'm a little concerned about.  Was trying to get a measure working and had it as a calc column previously so was comparing the 2 different outputs and checking for line by line differences.  I picked a good one and investigated.  calc column value was zero, measure value was £42.  The calc column is correct.  So i drilled into my measure to see what i could find.  Alarmingly, I found that for a certain field called DocumentStatus the pivot showed it as "LIVE".  But if I go to the table in visual studio and find that order the status is "COMPLETED".  I have checked and rechecked.  There is only 1 order on this table with the right DocumentNo.  The pivot seems to think this order is LIVE but the source data definitely shows it as completed.  What???  How can this happen?

    So strictly speaking the measure is actually calculating correctly because it is seeing the order as live therefore picking the £42 value is correct for that formula.  The calc column is correct because it is seeing the order as completed so picking zero as the final value is correct. 

    It is the fact that the record is being seen as both live and completed that is throwing me.  I'm concerned to say the least.  This feels like a bug.  I have checked and have no other filters in play.   I have checked other ways too - like filtering on all orders with a £42 value in a particular field - none of the have a LIVE status.  Its almost like that field is misaligned in the background.

    Has anyone ever seen this?

  • July 4, 2017 at 4:06 pm

    #1949375

    sorry, failed to mention this is a SSAS Tabular question and I'm on SQL server 2016; Visual Studio 2017

  • July 5, 2017 at 2:07 am

    #1949407

    Unfortunately I cannot provide sample data due to strict confidentiality. I have provided 2 screen shots below, both showing the same record - (1) is the view from the excel pivot table that is connected to the ssas tabular data model (2) is the view of the table in Visual Studio (note how the value of the Accrued Income measure in this view is not the same as the AccruedIncome total in the excel pivot table)

    I am wondering if this is to do with the way that I have deployed recent edits to the data model. Every time I make a change I am running the deploy & build commands so that I can refresh the excel reports to see if they are working as intended. What I don't know is when I do this am I deploying the metadata only or the metadata and the actual data (several hundred thousand rows on a dozen or so tables)? Is the issue here that the pivot is looking at an older set of data than the dataset visual studio is looking at? When I deploy & build do I then need to process the SSAS tabular object to update the data?

    Also note how the DocumentStatus is different in the 2 views.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply