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
Post PatronHi 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.
- jirineoral
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
Anonymous Another thing to throw in to this discussion is the need for backup/recovery/change control for a corporate solution. If you have the option to build and use Tabular I would suggest you go that route for several reasons.
1) You BISM layer isn't stuck in Desktop files
2) You only need to maintain 1 model. (If you build int Desktop, you would need to maintain "x" number of copies depending on how many reports you created. For example, 5 PBIX files into your project you realize you need to add a column or calc, you have to update in 5 places rather than 1).
3) Code/model is secured and backed up on SSAS instance and Visual Studio
4) Check in / Check out policies with TFS
5) Processing doesn't seem to be a major concern for you, but having a dedicate SSAS instance to handle the load is nice.
6) Always up to date without having to manage schedules in Power BI
Live connection to Tabular is my preferred method for corporate solutions for all of the above reasons.
Anonymousjirineoral
Thanks for the advice. Another factor that can be thrown in the equation is that whether it is a formal governed reporting or an add-hoc type analysis. Seems for the formal reporting, preference is to have the SSAS layer. But if the business is after quick cheap ad hoc type analysis (which happen more and more like this in the BI space), import mode with PBI offers the best cost effective solution,
- jirineoral
Anonymous
Could be wrapped up this way. Just to add, for analytical request you can do it in Power Pivot and publish it to Power BI service as well
Jiri