The course is part of this learning path
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
Database and file resources
Connecting to a Dataverse data source is essentially the same as connecting to a regular data source, but with a few quirks. From a product point of view, Dataverse is within the Power Platform ecosystem and is a data repository that can bring together information from a large variety of sources. Like Power BI, data can be imported into your Dataverse or linked to the original data source. Due to Dataverse also being part of the Power Platform family, there are several ways you can access the connector.
There is a button on the home toolbar, a menu item within Get data, and you can find it within the power platform category of the get data dialogue. When you click the connect button, you will be prompted to authenticate with the Power platform if you haven't already. I'm going to import a table called SalesTargets, and this is where we find the first quirk of a custom Dataverse table. Within the Import Wizard, I scroll down to where you'd expect to find SalesTargets, but it's not there. I can search for it and find it that way.
Custom tables are prefixed to avoid naming collisions. In this case, the prefix has been auto-generated, which is why Salestargets didn't appear within the S range. I'll select the table and click transform data. In this case, I'll import the sales targets into a Power BI model. Once open in Power Query editor, we can see that Dataverse has added 40 columns to the four-column table I imported into it. I imagine in most cases, you won't need all these audit columns in your reporting data model, so I'm going to remove all the extra columns except one.
I'll do that by selecting the columns I want and then removing all the ones I don't. So that's control plus click to get the columns I want, including SalesTargetId that was auto-generated when I imported the targets into Dataverse. Once I have selected the columns I want, I can go up to remove columns and click remove other columns. Next, I'll rename the table and the columns removing the auto-generated prefix. Having given the table and columns sensible names, I can now hit close and apply to save the sales targets to my Power BI model and bring up the model view.
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.