As I am trying to optimize my SSAS model, I've been wondering if there is a difference between using a Table and a View in SSAS. As I'm not an expert, I don't know if SSAS stores data in its own database or if it queries it from SQL Database.
Thanks in advance
I'm thinking of switching my complex views into tables that are populated with stored procedures to reduce querying views every time.
I'm not sure about tabular models, but for multidimensional models you can connect to either tables or views. The table or view that you reference in your data source view is still stored in the underlying database, but SSAS will also store the aggregated data in an SSAS database, which you can then view and write MDX queries against from SSMS. I personally prefer to connect to views, because then when a user requests new business logic I can update the logic in the view, and not have to mess with changing my SSIS packages that update my underlying tables. You can also maintain most of your business intelligence logic within SSAS, but I feel it is easier to implement on the database, which requires less MDX knowledge. In summary, I connect my SSAS data source views to SQL server views quite frequently, and many of my views are quite complex. I've never run any sophisticated testing, but when I changed my architecture from using tables to using views, I didn't notice any significant decrease in performance when it came to processing the cubes.