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
An XMLA endpoint is a URL that enables you to access Power BI datasets within your online workspaces. This is useful for accessing this type of data from outside the Power BI ecosystem. XMLA endpoints will only work for Power BI or cube formatted datasets. Let's go through the process of connecting to an online dataset using SQL Server management studio. I have a dataset called PowerSales within my GettingData workspace.
PowerSales is refreshed from an Azure SQL database. To use the XMLA connector, you must be subscribed to Premium Power BI or Premium per User. The little diamond or gem icon denotes the premium status of a Power BI subscription. Before connecting SSMS, let's check a few settings within the admin portal. Under Premium per User, I'll make sure my XMLA endpoint is set to read-write so that I can issue commands from my client application.
Under my workspace settings is the URL connection string, which you can think of and use as a server connection string. Depending on the size of your dataset, you can set the default storage format to large dataset. I'll copy the workspace connection string and fire up SQL Server management studio. When you connect to the XMLA endpoint, you're connecting to Analysis Services, not a database server. The server name is the XMLA URL, and I'm connecting with Azure Active Directory with MFA. Once connected, the eligible datasets within your workspace appear under databases. We can see all the tables, including system tables, that you would see if using some third-party tool like Dax studio.
From within SSMS, you can process a table or tables to refresh the data. In this case, I'll process the data for the Region table. Before I do that, I'll add a record to the table in the Azure SQL database. I'll connect to the source database and insert a record for another region. Back in Power BI.com, we can see the region table as a report. In SSMS, I'll right-click on Region, select process table, and change the mode to process data. You can select multiple tables no matter from which table you open the process table dialogue.
Once that has finished processing, I can go back to Power BI.com, refresh the report, and see the newly added data. Alternately, instead of running the command, you can script it. Instead of clicking OK, I'll copy the script to the clipboard and insert another record into the Region table. Next, I'll paste the JSON script into a new MDX query window and run it. After the run has completed, we can see another region record has been added to the report in Power BI.com.
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.