Add Data to SSAS Tabular Hourly
In order to add some data into SSAS Tabular model, you can basically follow two options to achieve this:
- Use Partitions
- Use Process ADD
Considering SSAS Tabular, using partitions is sometimes a little bit controversial. Therefore, you should be aware of the possible impact and you should be also prepared for proper testing of such an approach. On the other hand, ProcessAdd is much convenient option which you might prefer.
Smart ProcessAdd
There are many ways to implement ProcessAdd, as described by Marco or Cathy Dumas here and here. Your preference mainly depends on your knowledge and particular needs. I decided to keep SSAS layer and XMLA processing command simple. Therefore, I put “intelligent” part into SQL layer and SSIS Package.
Solution Components:
- SSAS tabular table with one partition, which is loading data through stored procedure (not SQL Select – nor View- but SQL Stored Procedure) – let’s call it usp GetSSASData
-
SQL layer
- Information on data rows when it was loaded into DW.
-
Help table with information what was loaded to SSAS already
- Stored Procedures operating help table. (setting meta information)
- Stored Procedure getting only new data into SSAS (usp GetSSASData)
- SSIS Package orchestrating incremental (as well as full) processing
Solution might be clear while checking the following figure (basic steps shown below) for ProcessAdd workflow in SSIS package. (As mentioned in my last blog, it is necessary to check whether there is something to be loaded.)
Figure 1 – SSIS Package Workflow
Main advantages:
- Simple XMLA Command (You can call simply ProcessAdd against particular partition with no worries about underlying SQL command structure)
- No scripting or implementing with code acting with SSAS object model.
- No change of SSAS structure.
The biggest down side of the solution:
- The solution is based on metadata – therefore wrong data would be loaded into SSAS as you do processing manually aside of prepared process.
Conclusion
As for the conclusion, implementing an incremental load of the data into SSAS Tabular model via “smart” SQL Stored procedure simplifies ProcessAdd command maintenance and implementation. Moreover, it’s not necessary to implement additional logic in different tool such as PowerShell.
4 Comments. Leave new
Hi Roman,
I’m trying to setup a similar approach as described in your article, namely having a helper table which will tell SSAS what to process. For some reason, I can’t see “Figure 1 – SSIS Package Workflow” on this page – is it missing or something with my browser perhaps?
Cheers,
Jean
Hi Jean,
Picture fixed. Thanks!
Roman
Hi,
I’ve used an option ProcessAdd for auto incremental load, but every time i process the partition the data gets doubled/duplicated.
Can you please help me with reason and solution.
Before processing the partition, I’ve selected ProcessAdd option on partition and configure the query as:
Select * from TableName.
Thanks
Hello,
You have two options for process add.
1 – Specify logic of increment in query. As proposed in this post. (in that case you cannot use SELECT * FROM table but something which incorporates increment logic (SP or User Function)
2 – Make command process add with SQL query which retrieves increment. Such as in example in this post: https://www.sqlbi.com/articles/using-process-add-in-tabular-models/
BR
Roman