Some of the
data sources have option both to import data as well as use live connection.
But there is no easy method to change it once you already built your report. So
you should rather carefully decide which method you’re going to use upfront. To
make this decision right, it is good to know limitations of each method. Purpose
of this blog post is to cover these limitations.
First what
kind of license are you going to need? If you’ll use live connection, you’ll
need Pro license for all end users consuming report/dashboard. If you’ll import
data from on premises data source through gateway, you’ll need Pro License
again. If your data are accessible without gateway, but you want to refresh
them more often, then once a day, Pro License is required too. If you want to
stay with free Power BI license, you’ll need to use data Import method only and
avoid on premises data sources, refresh data maximally once a day.
Data import
Data import
can be used against any data source type. Current Power BI service limitation
published file size is 1 GB. Data are loaded to file using column store
compression. If you’ll read for report purposes just data required for usage in
visuals this is not limiting too much. Even with Power BI Pro license you are
limited to 8 refreshes per day. Times can be scheduled either at full hour or
half past full. Scheduled time is approximate. When will refresh actually
happen depends on service utilization. If you need to refresh data, more often
consider using live connection. When using import, data are stored in Power BI
file/service. Therefore, there is no need to setup permissions on data source
side (service account for load is enough) and you can share data publically or
with people outside organization. On the other hand, all data are stored on
Power BI. So if you have requirement for Row Level Security, you need to
implement it on Power BI side. If you need same logic applied for multiple
reports, you have to duplicate it in all the reports. Same applies to
calculations, if you need same measure in multiple reports, you need to
recreate it every time.
Live connection
There are
more limitations for live connection in place. It doesn’t work against all data
sources. Current list can be seen here https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/#live-connections-and-directquery-to-on-premises-data-sources
You are also
limited to just one data source/database you selected. You can’t combine data
from multiple data sources anymore. If you are connected to SQL Database, you
can still create logical relationships between objects from that database as
well as measures and calculated columns. When you are connected to SQL Server
Analysis Services, you are limited just to report layout and even can’t make
calculated columns or measures. When connected to SSAS, you could also reach
some licensing limitations. Power BI issues DAX queries against SSAS and this
isn’t supported in Standard edition of SQL Server before SQL Server 2016. When
using live connection, users have to have access to underlying data source. This
means you can’t share outside of your organization or publically. Other way
around, when you set up security once, all reports using live connection to the
same source have the same security model applied automatically. Similarly, you can
prepare your data model carefully with all measures and calculated columns on
data source side. All reports from the same data source can benefit of it.
Refresh frequency is unlimited. Power BI simply shows data as they are in
database. However, be careful using live connection against highly transactional
OLTP systems. If you suffer from performance problems now, analytical workloads
from Power BI won’t help it. Consider enabling features on data source side
like Operational Analytics in SQL Server 2016.
Summary
Both
methods have limitations, developer should be aware upfront. For import it is
mainly refresh frequency, data size and necessity to duplicate same logic
(calculations, security) in multiple reports. That could be helped by
templates, but if you need to change logic of calculation, you need to change
it in multiple reports again. For live connection, not all data sources are
supported. If you pick one, then you’re limited just to that one data source
for report. If logic is setup on source side, all reports can benefit. My
personal preference is use live connection against SSAS models, have the model
centralized and maintain logic in one place. For SQL Databases where I don’t
have model, I prefer import and use Power BI as a model and vizualization.
Žádné komentáře:
Okomentovat