Datasets and their types in Power BI
For creating dashboards and reports, the data plays a vital role. There are lots of data sources present in Power BI, which we can connect to create visualizations and data analysis. So, in this blog, we will discuss the types and modes of datasets used to connect with the data in Power BI.
Datasets in Power BI
Data is at the heart of Power BI, and datasets are used to store data. We can explore data by creating charts and dashboards or by asking questions with Q&A. The visualizations and answers get their underlying data from a dataset, which comes from a data source.
Power BI creates a dataset automatically when we use “Get data” to connect to and import data from a template app, file, or live data source. A dataset contains information about the data source and data source credentials. Also, the dataset often includes a subset of data copied from the data source. When we create visualizations in reports and dashboards, we often look at data from the dataset.
The data in a dataset comes from a data source.
For example, a database or file on a local computer or a server in your organization.
Types of datasets
Datasets in Power BI represent a source of data that is ready for reporting and visualization.
By considering the following parameters, we can create the datasets in Power BI:
-
- Connect to an existing data model that is not hosted in Power BI
- Upload a Power BI desktop file that contains a model
- Upload an Excel workbook that contains one or more Excel tables and/or a workbook data model or upload a comma-separated values (CSV) file.
- Use the Power BI service to create a push dataset.
- Use the Power BI service to create a streaming or hybrid streaming dataset.
There are five different dataset types which are as follows:
-
- External-hosted models
- Power BI Desktop-developed models
- Excel workbook models
- Push Dataset
- Hybrid Streaming dataset
1. External-hosted models
- There are two types of externally hosted models: Azure Analysis Services and SQL Server Analysis Services.
- Installing the on-premises data gateway, whether on-premises or VM-hosted Infrastructure-as-a-Service (IaaS), is required to connect to a SQL Server Analysis Services model.
- Here gateway is not required for Azure Analysis Services.
- It often makes sense to connect to Analysis Services when existing model investments are part of an enterprise data warehouse (EDW).
- Power BI can make a live connection to Analysis Services and enforce data permissions by using the identity of the Power BI report user.
- SQL Server Analysis Services supports multidimensional, cubes, and tabular models.
- As the following example, the image shows a live connection dataset that passes queries to external-hosted models.
2. Power BI Desktop-developed models
- Power BI Desktop is a client application for Power BI development, and one can create a model using this Power BI Desktop.
- The model is a tabular analysis services model.
- Models can be created by importing data from dataflows and blending it with data from external sources.
- We can develop three diverse types, or modes, of models by using Power BI Desktop: Import, DirectQuery, and Composite. The mode depends on whether data is imported into the model or remains in the data source.
- Row-level security can be used in Power BI Desktop Models to restrict the amount of data obtained for a certain user.
- Example: Users in the Salespeople security group, for instance, can only see report data for the sales region(s) to which they have been assigned.
3. Excel workbook models
- The creation of a model is automatic when datasets are created from Excel workbooks or CSV files.
- To construct model tables, Excel tables and CSV data are imported, and an Excel workbook data model is translated to produce a Power BI model.
- In every scenario, data from a file is imported into a model.
- A Data Model allows us to integrate data from multiple tables, effectively building a relational data source inside an Excel workbook. Data Models are used transparently in Excel, providing tabular data in PivotTables and Pivot Charts.
- A Data Model is visualized as a collection of tables in a Field List; most of the time, we will never even know it is there.
- Example: Following image example shows that we are using an excel workbook with fictional student details on classes and grades.
4. Push dataset
- A power BI dataset that can only be created and populated using Power BI API is known as the Push dataset.
- When the dataset is created, the Power BI service automatically creates a new database in the service to store the data.
- The tables are imported into the dataset, and new rows are incrementally added to the tables. This solution has no impact on the data source database performance because no additional SQL queries are sent to the data source.
- Push datasets require specific APIs to create the model and update the tables.
- You can push data into a dataset by using the following methods:
-
- The Power BI REST APIs
- The Power BI streaming dataset UI
- Azure Stream Analytics
-
- The idea is to extend a standard “import” dataset using a push dataset that inherits the existing structures and measures. However, the push dataset is populated and updated only for the relevant parts of the reports that require real-time.
- The goal is to minimize the development cost to obtain a solution where the reports are always up-to-date, and a dashboard can have frequent unattended updates like in the following example.
5. Hybrid streaming dataset
- We can define a streaming dataset as it is continuous and real-time data which transport to Power BI.
- Power BI stores this data in a temporary cache for a little time. The streaming dataset can visualize data in dashboard tiles (A tile is a snapshot of your data pinned to the dashboard)
- Social media sources, Factory Sensors, service usage metrics, and various other time-sensitive data collectors can all be used to collect and transmit streaming data.
- Any Power BI visual or dashboard can display and update real-time data and visuals.
- Factory sensors, social media sources, service usage metrics, and various other time-sensitive data collectors or transmitters can all be used to collect and transmit streaming data.
Summary
In this blog, we discussed how data plays a vital role in creating reports and dashboards. We have also seen what a dataset is, and its types based on different conditions with relevant examples.
We have helped many clients import data into Power BI by advising them on the prerequisites they should meet to generate reports, dashboards, and dataflows, along with suggesting datasets that would work best as per their needs. Feel free to contact us if you are facing any challenges integrating data into Power BI.
