Power BI -Create the table with Date Dimension

Reported Date =
ADDCOLUMNS (
  CALENDAR (MIN(Tickets[Reported Date]),MAX(Tickets[Reported Date])),
"Reported Date DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Reported Date Year", YEAR ( [Date] ),
"Reported Date Monthnumber", FORMAT ( [Date], "MM" ),
"Reported Date Year-Month", FORMAT ( [Date], "YYYY-MM" ),
"Reported Date YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"Reported Date MonthNameShort", FORMAT ( [Date], "mmm" ),
"Reported Date MonthNameLong", FORMAT ( [Date], "mmmm" ),
"Reported Date DayOfWeekNumber", WEEKDAY ( [Date] ),
"Reported Date DayOfWeek", FORMAT ( [Date], "dddd" ),
"Reported Date DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Reported Date Quarter", "Q" & FORMAT ( [Date], "Q" ),
"Reported Date Year-Quarter", FORMAT ( [Date], "YYYY" ) & "-Q" & FORMAT ( [Date], "Q" )
)

Power BI – Live connection vs. Import, comparison and limitations

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-directq...

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.