The past few months I have been working with SSAS Tabular Model introduced in SQL Server 2012. Generally, I automate my Tabular Model Processing using XMLA via SQL Server job. However, I have recently come across SQL Server Management Studio (SSMS) bug whereby performing “Process Full” on the Tabular Model database would incorrectly show “Process Recalc” instead . If it did, the data in tabular model would not be as expected. The underlying behaviour however seems to be correct.
Steps To Reproduce The Issue
I run Process Full on Tabular Model database, by Right clicking on the tabular model database in SSMS, and choose “Process Database” as shown below.
This brings up the “Process Database” dialog box, which I then choose “Process Full” on the Mode drop down list as illustrated below.
Choose Process Full mode
The “Data Processing” dialog box will appear showing the progress. Once it’s finished you can click on the “Details” link which will show “Processing Details” with an unexpected “Type” in the Process XMLA command.
Data Processing Dialog Box
Processing Details showing ProcessRecalc instead of ProcessFull
The Underlying Behaviour
Process Full and Process Recalc on Tabular Model database are different. As mentioned by Cathy Dumas in “Processing tabular models 101” on her MSDN blog:
| Process Full | Loads data into all selected partitions or tables. Any affected calculated columns, relationships, user hierarchies, or internal engine structures (except table dictionaries) are recalculated. |
| Process Recalc | For all tables in the database, recalculates calculated columns, rebuilds relationships. rebuilds user hierarchies, and rebuilds other internal engine structures. Table dictionaries are not affected. |
This means that if the data is modified in the source database(s), Process Recalc will not be getting this modified data. Process Full, on the other hand, will.
Not convinced that invoking Process Full on the Tabular Model database via SSMS would indeed invoke Process Recalc as shown on the dialog box, I decided to run SQL Profiler.
According to SQL Profiler – on Line 61 or so on my trace – I noticed that the Command executed looks as follows.
The XMLA command looks like below (copied and pasted to Notepad):
After running a couple of tests, it seems that the Process Full on Database via SSMS in SQL Server 2012 RTM version, does actually perform Process Full despite reporting it as Process Recalc. My tabular model database is refreshed with the new data.
Related Posts
Looking at Microsoft Connect after I encountered this issue, there was a fairly old post that was not directly reporting this issue. However, in the response, Cathy Dumas mentioned that she found the above bug in a SQL Server 2012 version pre RTM. Check her comment in this link below.
Wrap Up
SQL Server Management Studio User Interface invokes Process Full properly on the Tabular Model database in SQL Server 2012 RTM version despite the misleading “Process Recalc” info on the Details. This is confirmed by using SQL Profiler and checking the outcome of the Process Database execution. When in doubt, always inspect the underlying behaviour.
No responses yet