Power BI Direct Query
Power BI can be used in one of two modes to access data. The import option loads data from the source data to the Power BI model. Queries are then processed directly against the data in the model. Using the DirectQuery option, on the other hand, leaves at the source and Power BI queries the data as needed. For smaller tables using DirectQuery can be a good option to leverage when you do not want to build a data refresh process into Power BI. Source systems can potentially be queried on demand and provide users with the most up to date data as possible. DirectQuery does have limitations that you need to be aware such as connecting to a single database only, time intelligence is not available, and there is a million row limit.
For complete benefits and limitations of Power BI see – https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/
Power BI Tabular Models
The use of SQL Server Analysis Services in Tabular mode as a data store for Power BI enables fast querying in DirectQuery mode. Power BI visualizations that leverage tabular models overcome the limitations of DirectQuery. Data stored in a tabular model is optimized in memory to allow fast query operations. Tabular models allow for the full implementation of DAX calculations, time intelligence and can go over the million row limit of a data file or table.
To learn more about how to build Tabular Models and Power BI see my book available on Amazon – Tabular Modeling Cookbook
Tabular Modeling Cookbook Highlights
- Learn all about Tabular services mode and how it speeds up development
- Build solutions using sample datasets
- Explore built-in actions and transitions in SSAS 2016
- Implement row-column, and role-based security in a Tabular Data model
- Realize the benefits of in-memory and DirectQuery deployment modes
- Get up to date with the new features added to SQL Server 2016 Analysis Services
- Optimize Data Models and Relationships Usage
- Connect your data to Power BI to build visualizations and explore your data.