Getting Data into Power BI from Different Sources looks at connecting to data sources on-premise, in the Azure cloud, and Power platform data sources. The course is focused on how various data sources can impact your data model's performance, rather than looking at every single source that Power BI accommodates, as there are literally dozens and dozens of them. Apart from typical database and file data sources we look at Dataverse, a power platform repository and data flow an online ETL service.
Learning Objectives
- How to connect to a database and file data source on-premises and in the Azure cloud
- How to change the location of a data source without reimporting the data
- Look at the pros and cons of each Power BI data storage mode
- User Power Platform's Dataverse product as a data source
- How to and why you would use a Data Flow as a data source
- Setting up and connecting to an XMLA endpoint
Intended Audience
- This course is intended for anyone who wants to get data into Power BI
Prerequisites
- An understanding of Power BI desktop and power BI online
- A Power BI premium account for XMLA endpoint, one of the more specialized data connection topics
Power Bi, whether online or in desktop mode, allows you to pull data from a multitude of sources in terms of both format and location. The default mode is to import data into a Power Bi data model where data is copied from the source into Power Bi. Import mode offers the best performance due to the highly optimized Vertipaq database engine and access to all DAX functionality. The primary limitation with import mode is for very large datasets. Non-premium Power Bi subscriptions are limited to 1 GB datasets, but you can get around this using direct query where your reports pull data from the original source. Direct query means you don't have to worry about refreshing your data model, but performance tends not to be quite as good. You can combine direct query and import into a composite model where you can set tables up in dual access mode so a table's data can be used in joins at the source when needed. Dataverse brings together multiple data sources into a Power platform repository for easy access by other power platform products like Power Bi.
Data flow is analogous to an online extract transform and load ETL service that allows data modelers to provide a common source of truth dataset for report authors. Like standard Power BI datasets, you can set up a refresh schedule and, as with shared datasets, view a flows lineage.
An XMLA endpoint is a URL pointing to a Power BI workspace, available to Premium subscribers. The endpoint is analogous to a web API URL or database connection string which can be used to connect non-Power BI clients like SQL Server Management Studio to Power BI datasets.
My name is Hallam Webber, and I've enjoyed presenting this Getting Data into Power BI from Different Sources course. I hope you've found the course informative and interesting and are now ready to hook up your data sources.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.