Data source

In Power BI, the data you explore comes from a dataset. But in order to have a dataset, you first need to get some data.

A dataset is like the engine in a car. The dataset provides the data, metrics, and information that’s displayed in your Power BI dashboard. Of course your engine, or dataset, needs fuel, and in Power BI, that fuel is data

A car has a fuel tank that provides gas to the engine. Much the same in Power BI, you need a fuel tank that has data you can feed to your dataset. In our case, that fuel tank is a Power BI Desktop file, an Excel workbook file, or a .CSV file.

Once we have a file with some data, we can get it into Power BI as a dataset

Power BI supports many different data sources

• Databases
• Files
• Excel workbook
• OneDrive
• on-premises SQL Server Analysis Services (SSAS) tabular dataset
• From Azure
• App (get data from a service through app)
• Salesforce dataset
• and so on

Import vs Get Data

List of data sources

• https://docs.microsoft.com/en-us/power-bi/power-bi-data-sources
• Power BI Desktop: https://docs.microsoft.com/en-us/power-bi/desktop-data-sources

Databases

Import

• You can always import data from supported databases, that means Power BI copies your data into the Power BI service in the cloud
• You’ll have to take additional steps to update the data later if it changes
• With some databases import is the only option you have, such as DB2 and MySQL, among others
• With Analysis Services databases you can import data from either multidimensional models or tabular models.

DirectQuery

• limited to a subset of databases
• With Direct Query the data does not move out of the source database, instead Power BI queries the database when you view the report, so the data is always current
• need to have Enterprise Gateway installed by your IT department to work with these sources in Direct Query mode
• Your model can only use data from the same database. That means you won’t be able to mix and match data sources
• Can not add calculated columns or tables to your model
• Can not change data types for columns
• A great option when your data set is large or when you need real time data

Connect Live

• Unique to Analysis Services
• Cannot combine tabular data with other sources
• Can set up a live connection for multidimensional models (Power BI Desktop). You won’t be able to publish reports that you create to the Power BI service when using multidimensional data
• Need to have an Enterprise Gateway installed if you plan to publish reports to the Power BI service for your tabular model source

See: https://docs.microsoft.com/en-us/power-bi/desktop-data-sources#database-data-sources

Files

In Power BI (Power BI service), you can connect to or import data and reports from three types of files:

• Microsoft Excel (.xlsx or .xlsm)
• Power BI Desktop (.pbix)
• Comma Separated Value (.csv)

In Power BI Desktop (Home > Get data > More…)