Sql-server – SSAS 2016 tabular : Discrepancy on countrows between measures visual studio vs Excel

sql-server-2016ssastabular-model

Is anyone aware of a SSAS (tabular) 2016 in which the number of records in a table are not fully shown in Excel?

Say that when working in Visual Studio, a table has a rowcount of 740,000 records.
We process and deploy that table, but in Excel it shows only 12,000 records.
also funny enough, when we deploy the model in VS it says that it only processed 12,000 records (yet the rowcount in the model is clearly 740,000 records).

(we ensure that Excel is connected to the deployed Tabular Model and not the user's workspace copy in Visual Studio)

We then change the table properties in VS to apply a filter, then we remove the filter, to see if this somehow "resets" this strange behaviour. It seems to do it.
However the problem seems to arise again during the next data load on the underlying database.

This only seems to happen with ONE table (all other tables are fine). So we tried recreating the table, but the same problem persists. Any idea of what might be causing this issue?

Best Answer

The solution was to change the processing in Visual Studio (VS) from Default to Full.

The issue is described in detail here: https://connect.microsoft.com/SQLServer/feedback/details/685325/processing-ssas-in-tabular-mode-not-refreshing-data (see Cathy Dumas's reply under "Posted by Microsoft on 8/25/2011 at 2:02 PM")

Related Solutions

Sql-server – SSAS tabular browse Excel relationships not working

I think i've found the problem. SSAS doesn't seem to relate data between tables if there are no measures defined. I added a simple measure like grade count and voila i have my data relationship.

How to calculate/store Top 10 in a tabular model

I had a similar scenario and used the following DAX query...

First, to make it simple, I defined a measure to use inside the DAX so I don't have to repeat the formula. Then I used the generate to iterate on the TOPN formula:

define measure TableInTabular[NameOfTheMeasure] = COUNTAX(FILTER('Stats', ISBLANK([DeactDate]) = TRUE), 1)
evaluate
 (
  addcolumns
   (  
    filter
     (  
      generate
        (  
         VALUES(DatesTableName[Month]),  
         TOPN (10, VALUES(TableInTabular[ParentID]),TableInTabular[NameOfTheMeasure],0)
        ),
        TableInTabular[NameOfTheMeasure]>0
      ),
      "ActiveCount (or how you want to call this Column)",
      TableInTabular[NameOfTheMeasure]  
    )  
 )  
order by DatesTableName[Month] asc, 
TableInTabular[NameOfTheMeasure] desc

With the above you should have a top 10 ParentID and the Measure by each month. just replace the "TableInTabular" with your tabular table name where you have the data and the "DatesTableName" with the name of the dates table.

Please let me know if I misunderstood your question and hope it helps...

Best Answer

Related Solutions

Sql-server – SSAS tabular browse Excel relationships not working

How to calculate/store Top 10 in a tabular model

Related Question