1 of 19

Power BI for large databases

Hector Villafuerte, Business Intelligence Architect

Microsoft Certified Technology Specialist, SQL, Dynamics CRM - MCTS
Works with SQL Server, Power BI, SSIS, SSAS, SSRS, SharePoint, Dynamics CRM and Azure PAAS.
Microsoft Certified Professional Developer – MCPD
Full-stack .NET Developer and Web Applications Architect.

Reach me at:

https://www.linkedin.com/in/hector-v/
http://www.hectorv.com
hectorvmail@gmail.com

2 of 19

Survey

Among these tables which is the biggest dataset ?

One table with 5,000 records
One table with 500,000 record
One table with 5 Million records
One table with 5 Billion records

3 of 19

Power BI for large databases

Agenda

PowerBI - Imported mode for large databases
Power BI - Live Connection for large databases
Power BI - Direct Query with large database
Power BI - Big Data Sets

4 of 19

VertiPaq In-memory Technology (xVelocity)

VertiPaq engine: the in-memory columnar database that stores and hosts your model. Available in Power Pivot, Power BI, Analysis Services Tabular, SQL Server ColumnStore Indexes.

VertiPaq Analyzer reports the memory consumption of the data model. http://www.sqlbi.com/tools/vertipaq-analyzer/

5 of 19

Power BI Service

Security

6 of 19

7 of 19

8 of 19

9 of 19

10 of 19

11 of 19

12 of 19

13 of 19

14 of 19

15 of 19

16 of 19

As of June 2018:

Power BI Premium - Incremental refresh in preview.
Azure Analysis Services scale up to 400GB.

	DirectQuery Mode	Imported Mode	Live Connection
Data Size after compression (database, table, cardinality)	Very large dataset	1 GB <= PBI Free 10 GB <= PBI Pro 10GB > PBI Premium	Up to 400GB SSAS
Data Source Types	Limited	Many	Limited
Number of Data Source	1 (only)	Many	1 (only)
Power Query Transformations	Limited (to simple)	Not Limited (can do complex)	No Power Query
DAX (Data Analysis Expressions)	Limited	Not Limited (can do complex)	Not Limited (can do complex)

PowerBI Modes Comparison

17 of 19

Big Data Sets with Power BI in HDInsight

Imported Mode using Microsoft Hive ODBC Driver - data refreshes with this method can often be slow as a Hive job will be executed on your cluster before transferring the data

Imported Mode - HDInsight into Power BI is by connecting to flat files in either Blob or the Data Lake Store

Direct Query - Power BI can then use Spark SQL to interactively query the tables in Power BI's DirectQuery mode.

Direct Query - process your data in your cluster, but write the resulting curated and/or aggregated data to tables in Azure SQL DB (or Azure SQL Data Warehouse).

18 of 19

DEMO

PowerBI - Imported mode for large datasets
Power BI - Live Connection for large datasets
Power BI - Direct Query with large dataset

19 of 19

Resources:

Power BI Performance Best Practices:

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

Incremental refresh in Power BI Premium:

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

Using DirectQuery in Power BI

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

Hector Villafuerte

Linkedin: https://www.linkedin.com/in/hector-v/

Blog: www.hectorv.com

E-mail: hectorvmail@gmail.com

Questions?