Forum Discussion
Direct connect vs import, SQL server vs SSAS source
Hi Anonymous
there are multiple factors to consider.
Frequency: with import you are limited to 8 times per day and my personal experience is, that you can't depend on times exactly. It runs in approximate time frame as close to scheduled time depending on how utilized the service is. If you need to refresh more often. You'll need to go for live connection.
Data size: 1 GB file limit for import, and even if not, how often would you like to bring data in.
Live connection limitations: If you would go for live connection against source system. It could have negative impact on source system as well as performance on aggregated data could be better if preaggregated.
Live connection against SSAS model works quite good and that's why we use it at work. However you need enterprise edition of SQL Server in background. Although SSAS Multidimensional is supported in Standard SQL, Power BI issues DAX queries against it and this is not supported in Standard (untill SQL Server 2016).
Import from SSAS would work a little strange against SSAS, because you'll need to recreate potentially existing logic in your SSAS model. For example if you insert percentage measure. This is computed on detailed level when using import and if you want correct percentage, you'll need to recalculate it in your model.
Security is another concern. If you would import data to multiple files from same datasource, you'll need to recreate logic for roles every single time. In comparisson when using live connection you set it up once on data source side
My personal preference is
Use import against SQL Server for precalculating data for better query performance
Live against SSAS not to duplicate logic on report level, that already exists in your data model on premises.
Jiri
Makes sense to me that import from SQL, direct connect for SSAS.
refresh frequency is not an issue. Most DW systems do once a day anyway.
By the way, we use 2014 Enterprise SQL server so SSAS can be an option.
So would you prefer import from SQL source or build a SSAS cube then do direct connect for the cube? Unless size is an issue, I do not see a case why we need a build SSAS in between Power BI and SQL server.
Anonymous
for instance we have requirement for 15 minutes delay, and this can't be achieved by import. If it is not your case, good for you :) and you can use import as well.
I personally prefer intermediate SSAS layer, where you can do all the calculations, logic once and reuse it across multiple reports using live connection. If you did it using import and then the calculation would change, you would need to replace all the files. This won't happen using live connection.
But both approaches have advantages and limitations. So pick the one which suits you the most for your current case.
Jiri