INTRODUCTION – Data Sources in Power BI
You will try to learn on how to set up data sources and their various capabilities in Power BI. This complete guide will help you set up connections to different types of data sources, making you understand how Power BI interacts with and utilizes those sources in feeding and administering all data.
In short, you will have the Power BI tools to find out the features and functionalities through which the tool manages to process data from various sources, transforming that knowledge into effective use to structure and analyze data. The module gives hands-on experience so that learners can gain access to all Power BI capabilities involving data-source integration to meet their analytical objectives.
Learning Objectives:
- Identify types of data sources and different types of storage in Power BI.
- Understanding how to configure data source in Power BI.
- Learn about the different modes of storing in Power BI.
- Configuring a storage mode within Power BI with the associated dataset.
- Understand different characteristics visible between structured and unstructured data.
- Understand connectors, triggers and actions of Power BI.
SELF-REVIEW: SETTING UP AN EXCEL DATA SOURCE
1. Which window should you use to select and load tables from an Excel workbook in Power BI Desktop?
- The Navigator window
- The Fields pane (CORRECT)
- The Overview tab
That’s correct! Within Power BI Desktop, the Navigator window allows users to select and load tables from an Excel workbook.
2. Which data sources can Power BI connect to?
- Local, cloud, and SaaS sources (CORRECT)
- Only local sources
- Only SaaS sources
That’s correct! Power BI can establish connection for more than a hundred different kinds of sources like actual on-premise and SaaS cloud sources.
3. How should you optimize data ranges in an Excel workbook for better visualization in Power BI?
- Leave them as simple worksheets.
- Format them as tables. (CORRECT)
- Convert them into charts.
That’s correct! Dum paithaassya utpe bude guiyanna, simple worksheets han hold data ranges format into tables. Create reports; this will much make it easier to visualize your data with the named tables and columns in the Fields pane.
4. You are setting up Power BI connectors for Adventure Works. What kind of services can the company use PowerBI to connect to?
- Power BI connects to many external apps and cloud services. (CORRECT)
- Power BI connectors are limited to local computers or personal accounts.
- Power BI connectors only link to Microsoft services
That’s correct! Power BI provides multiple built-in connectors, which help the user easily connect to various external apps and cloud services using Microsoft GitHub, Azure Blob Storage, and Dynamics 365, as well as many other solutions. Connectors help in importing data from other data sources easily; the already stored data can then be integrated with the data so that it can be presented more competitively from an enterprise-wide standpoint across numerous platforms.
5. What should you do if you need to change the location of your source file in Power BI?
- Select the correct option.
- Update the connection string. (CORRECT)
- Create a new connection string and leave the old one unchanged.
- Clear permissions.
Correct! It becomes necessary to update the connection string for keeping the reports live and free from errors like “File Not Found” or “Data Source Not Found.”
KNOWLEDGE CHECK: BASIC DATA SOURCES
1. What is the difference between a dataset and a data source in Power BI?
- A dataset is a container that holds some of the data from a data source, whereas a data source is where the data actually comes from. (CORRECT)
- A dataset is the same as a data source, but with fewer features.
- A dataset and data source are the same thing.
That’s correct! Power BI includes datasets that essentially serve as a storage for pieces or subsets of data drawn from a data source, and a data source is the origin from which this data is actually obtained.
2. True or False: You can create data sets that are reusable across multiple reports using basic data sources, which saves you the time and effort of having to import the same data repeatedly for each report.
- True (CORRECT)
- False
Correct! Once you’ve uploaded the dataset to the Power BI service, authorized users will have the access rights for report creation or any other usage of that dataset.
3. What are the types of workbooks that Power BI supports? Select all that apply:
- Workbooks with ranges or tables of data. (CORRECT)
- Workbooks with connections to external data sources. (CORRECT)
- Workbooks with shapes and images
- Workbooks with data models. (CORRECT)
Indeed! It’s actually better to format basic worksheets with data ranges as tables, since these improved named tables and columns in the Fields pane help in simplifying the visualization of data.
Yep! Your Excel workbook can have an external data source and can generate reports and dashboards in Power BI from that external data source. You could also set up scheduled refreshes to periodically connect and retrieve updates from the external data source.
Yes! That is, any workbook may create a data model containing one or more tables that have been loaded using linked tables, Power Query, Get & Transform in Excel, or Power Pivot. Power BI includes several data model features, including relationships, measures, hierarchies, and key performance indicators (KPIs).
4. What is the maximum size of an Excel workbook that can be uploaded to Power BI?
- 100 GB
- 10 GB
- 1 GB (CORRECT)
That’s correct! Excel workbooks greater than 1 GB cannot be uploaded into Power BI.
5. True or False: When using DirectQuery in Power BI, there is a limit on the number of rows that can be returned to your underlying data source.
- True (CORRECT)
- False
That’s correct! If a query returns more than a million rows while using DirectQuery, an error will be generated, and the query will not execute. Thus, it is always advisable to keep such limitations in mind while working with major data.
6. Adventure Works asks you if it can change its Power BI model from Import Mode to Direct Query mode once the initial storage mode has been created. How should you respond?
Select the correct option.
- A model cannot change from Import to Direct Query mode. (CORRECT)
- A model can easily be switched from Import to Direct Query mode.
- A model can only be changed within the latest version of Power BI.
That’s correct! This feature allows direct reporting from databases; however, in situations wherein all data is brought into the model, the Power BI model can switch from DirectQuery to Import mode.
7. Why would you, as a data analyst, configure Adventure Works’ storage modes in Power BI?
Select the correct option.
- To determine how visuals access back-end data sources. (CORRECT)
- To establish many-to-many relationships between tables.
- To import data to Power BI.
That’s correct! Power BI – This is how visuals retrieve back-end data sources according to selected storage mode. DirectQuery sends real-time queries to return the newest information and can therefore be slow. On the contrary, Import mode uses stored information for faster performance but may show outdated information from the last import since the data source was altered.
SELF-REVIEW: IMPLEMENTING TRIGGERS
1. How should your data look before loading it into Power BI?
- Each column contains different data types.
- The table should display columns and rows that show totals.
- Each column contains the same data type and the table should have a header row. (CORRECT)
That is correct! Power BI accepts datasets that contain same types of values within each column and has a header row.
2. How can you validate that notifications of failure refreshes will be sent to the dataset owner?
- Validating that the check box Send refresh failure notification is marked. (CORRECT)
- Setting the correct time zone.
- Specifying the email address of the dataset owner.
That’s correct! When the box is checked, Microsoft Power BI is informed to send an alert whenever a refresh operation fails.
3. To ensure the scheduled refresh will start at 10 PM every night, you should check the __________________ window.
- Get Data window
- Time zone window
- Scheduled refresh window (CORRECT)
That is correct! This schedule refresh window shows the frequency and time of the upcoming refresh.
4. What is the benefit of understanding the difference between structured and unstructured data?
- Understanding the difference can define the correct storage solution and the best kind of analysis needed. (CORRECT)
- Understanding the difference can enhancing data quality and reduce data processing time.
A comprehension of the difference could result in enhanced quality of data and speedier processing time, while also determining the best option for storage and directing towards the definite analysis approach required.
5. What two main factors do you need to consider when determining the appropriate data connector for Power BI?
Select the correct option.
- The type of data source and the data’s business requirements. (CORRECT)
- The size of the data source and the file format.
- The level of security and the financial cost of the connector.
That’s correct! It’s really important to have an idea about the data source with its specific characteristics like storage location, rate of update, and file format. It is important to understand business requirements: who uses this data and for what purpose?
6. What is the first step to set up an online refresh schedule in Power BI services?
- Select the Schedule Refresh button under the Refresh menu.
- Navigate to the Datasets + Dataflows tab in your workspace. (CORRECT)
- Modify the schedule in the Schedule Refresh pane.
That’s correct! Now go to the Datasets + Dataflows tab in your workspace to set up an online refresh schedule in Power BI services.
KNOWLEDGE CHECK: ADVANCED DATA SOURCES
1. What makes Azure Blob Storage an ideal option for storing unstructured data, such as photos and videos, for Adventure Works’ online retail website?
- Azure Blob Storage is a scalable and cost-effective Cloud Storage Service, suitable for storing large amounts of unstructured data. (CORRECT)
- Azure Blob Storage automatically converts unstructured data into structured data for easier analysis.
- Azure Blob Storage offers structured query language (SQL) support for complex queries.
Correct! Azure Blob Storage is suitable for the management and storing of all types of unstructured data in large volumes, thus making it a good candidate for storage of such media files as photographs and videos.
2. What is the purpose of the Data Source Settings option in Power BI?
- To select the data tables to import into Power BI.
- To change the connection details and credentials for the data source. (CORRECT)
- To specify the location of the data source file.
That’s correct! Data Source Settings in Power BI allows you to change the connection details for a data source. This can include details such as server address and database name for a SQL Server database. It is also possible to redefine connection credentials to the data source. This is important in ensuring security within the data source, such that credentials are saved separately on every machine and, thus, need to be configured separately within the service.
3. Considering the data classification at Adventure Works for their online retail website, which type of data is most appropriately stored and queried using a SQL database?
- Financial business data that is structured for year-to-year comparison and is mainly read-only. (CORRECT)
- Customer feedback in the form of audio files.
- Product images and video files used on the website.
That’s correct! This is possible with such type of financial business data ideally suited to a SQL database due to its structured format for read-only analysis like that of year-on-year comparisons since SQL is so well designed for dealing with such data.
4. True or False: In Power BI, triggers and actions are used primarily for visualizing data, enabling data analysts to create more complex and interactive reports.
- True
- False (CORRECT)
Triggers and actions in Power BI are essentially for workflow automation. For instance, scheduling the data refresh or sending a report at a defined time.
5. What is the primary purpose of setting up a scheduled data refresh in Power BI for a data analyst team?
- To automatically email the latest sales report to the management team at specified intervals.
- To ensure that sales report datasets are regularly updated without manual intervention, improving data accuracy and workflow efficiency. (CORRECT)
- To convert sales data from unstructured to structured formats for more straightforward analysis.
That’s correct! The primary reason a data analyst team needs to set up a scheduled data refresh in Power BI is to automate updating datasets instead of wasting time manually updating them and ensuring the data used in reports and dashboards is current and accurate at all times.
MODULE QUIZ: DATA SOURCES IN POWER BI
1. Complete the following sentence: Structured data is arranged in ___________ and ___________, and can be easily analyzed.
- columns, rows (CORRECT)
- cells, tables
- workbooks, tables
That’s correct! It makes it easy to compare, filter, sort, and calculate structured data organized into rows and columns. This allows data to be analyzed efficiently and decisions made based on findings.
2. Which of the following options in Power BI provides access to Power BI connectors?
- Get Data (CORRECT)
- Enter Data
- Publish
That is correct! With the Get Data option in Power BI, a menu opens displaying all the connectors available in the application. This enables you to connect to any data sources around the world.
3. True or False: By using conditional formatting in Power BI visuals, you can utilize colors, icons, or data bars to emphasize or highlight data in either text or numeric fields.
- True (CORRECT)
- False
That’s correct! Conditional highlighting is known for maintaining the particular significance of data using colors or icons according to a condition or value.
4. Which file types do Microsoft Excel workbooks in Power BI support?
- .pdf and .jpg
- .doc and .docx
- .xlsx and .xlsm (CORRECT)
That’s correct! Power BI is able to directly import or connect with workbooks from Excel, starting with version 2007 onward particular to .xlsx and .xlsm file types.
5. When you are tasked with selecting and loading tables from the Sales Data Excel workbook in Adventure Works, which window should you use in Power BI Desktop?
- The Fields pane
- The Navigator window (CORRECT)
- The Overview tab
That’s correct! Through the Navigator window in Power BI Desktop, you can select and import tables from an Excel workbook, determining for yourself which data would be imported.
6. True or False: You can set up a data source in Power BI using an Excel file with a pivot table.
- True
- False (CORRECT)
7. What are the steps involved in configuring a trigger in Power BI to schedule a refresh of a dataset at a specific time? Select all that apply.
- Configure the parameters for the refresh
- Configure the filters for the dataset
- Choose a function to schedule a refresh (CORRECT)
- Select the dataset to configure (CORRECT)
Very true! You need to select the dataset first before you can schedule the refresh for that dataset in the trigger configuration in Power BI.
That’s it! The next step in configuring a trigger in Power BI is to define the action to schedule the refresh of the dataset.
8. What type of dataset requires a source data refresh in Power BI?
- Import mode datasets (CORRECT)
- LiveConnect mode datasets
- DirectQuery datasets
That’s correct! Given that an imported mode dataset usually imports data externally, it requires refreshing the source data whenever the imported data needs to be updated from time to time or as and when necessary.
9. Which of the following is a limitation of data sources used in Power BI service and Power BI Desktop?
- The total number of columns that can be used in all the tables within a dataset is restricted to 10,000 columns.
- The maximum number of data sources allowed per user is 500.
- Several data connectors used in Power BI Desktop need Internet Explorer 10 or a newer version for authentication. (CORRECT)
Feedback: Indeed! A number of data connectors in Power BI Desktop need Internet Explorer 10 or a newer version for authentication because that becomes diehard for those users who would want to access any data from these sources.
10. What do Power BI data sources refer to?
- The charts and dashboards used to visualize data in Power BI.
- The different types of data that can be connected with Power BI. (CORRECT)
- The business insights provided by Power BI reports and Q&A.
That’s correct! Power BI offers the option of integrating various formats of data which are made available by data sources and doing lots of analysis and visualization.
11. Which of the following data source connectors can be found in Power BI? Select all that apply.
- Excel Workbook (CORRECT)
- Microsoft Word
- SharePoint Folder (CORRECT)
- SQL Server (CORRECT)
Indeed, an Excel Workbook is a proper source of data for Power BI such that you can import your data directly from Excel into your reports.
Yes, a SharePoint Folder can also be a valid data source in Power BI. Using the SharePoint Folder connector, you are able to import data from a specified folder by pasting the URL for the SharePoint site and selecting the folder.
Absolutely! This is also a valid data source in Power BI, since you can connect a Power BI instance or a Power BI Desktop directly to a SQL Server database to import data for further analysis and visualization.
12. True or False: When you create reports in Power BI, the named tables and columns in the Fields pane make it much easier to visualize your data.
- True (CORRECT)
- False
13. As a data analyst, what should you do if your Adventure Works Sales Analysis Excel workbook contains multiple sheets, with each sheet representing sales data for a specific region or time period, and has a range of data that includes columns such as date, product, quantity sold, and revenue generated?
- Leave them as simple worksheets
- Format them as tables (CORRECT)
- Convert them into charts
That’s correct! If your workbook is packed with simple worksheets that contain data ranges, it is wiser to convert those ranges into tables for maximum effectiveness in Power BI. You will find that when you go to make reports, the named tables and columns will assist you in easily visualizing your data in the Fields pane.
14. How can you ensure that your data is in a flat format with no total rows or columns when setting up an Excel data source in Power BI?
- Convert data to pivot table format
- Rename the Excel file
- Format the data as a table (CORRECT)
That’s correct! When using Excel workbooks as a data source in Power BI, the data must be flat, without total rows or total columns. Flatten the data, then format it as a table, and it’ll be easier to read and set up the data for efficient import into Power BI for analysis.
15. What is the definition of a trigger in Power BI?
- A trigger is a function used to schedule a refresh of a dataset in Power BI. (CORRECT)
- A trigger is a connector that links various data sources in Power BI.
- A trigger is a Power BI report that provides insights into data trends.
That’s correct! A trigger in Power BI is used to schedule an event, for example refreshing a dataset at a predetermined time or event.
16. Which of the following statements is true about Incremental Refresh in Power BI?
- You can manually update the data in the dataset on a rolling basis.
- You can refresh only the most recent data that has changed, making the refresh process faster and more efficient. (CORRECT)
- You can refresh all the data in the dataset every time you need to update your reports.
That’s correct! Incremental Refresh feature in Power BI An update in this feature that only updates the modified data and not the whole data make the updates work very fast. Organization of the data in small segments and updating data only for the latest increase the refresh speed.
17. True or False: The Home tab in Power BI contains quick access data source options, such as Excel, located next to the Get data button.
- True (CORRECT)
- False
That is correct! The options for quick access data sources are positioned under the Home tab in Power BI, from where one can easily connect and import data from various sources.
18. What is the benefit of arranging structured data in columns and rows?
- It’s easier to analyze it (CORRECT)
- It’s easier to delete it
- It’s easier to publish it
That is correct! The options for quick access data sources are positioned under the Home tab in Power BI, from where one can easily connect and import data from various sources.
19. True or False: Power BI can connect to custom codes such as R script, Python script or providers like ODBC or OLE DB inside the Other section in the Get Data window.
- True (CORRECT)
- False
That’s correct! In fact, R script, Python script, ODBC, and OLE DB are connectors to be found in the Power BI “Other” section, enabling the user to incorporate many outside data sources and advanced analytics into reports.
20. What is the benefit of formatting ranges of data as tables in Power BI?
- It improves the performance of the workbook.
- It makes the workbook easier to share with others.
- It makes it easier to visualize the data in Power BI reports. (CORRECT)
That’s correct! Using tables in Power BI for ranges of data enhances reporting for the purpose of visualizing. When creating reports, the named tables and columns in Fields pane make possible the simple organizing of the data for effective display in reports.
21. True or False: Configuring incremental refresh requires defining an incremental refresh policy only.
- True
- False (CORRECT)
That’s correct! The configuration for incremental refresh in Power BI requires the setting up of the parameters RangeStart and RangeEnd, applying proper filtering to slice the data as well as defining the policy of incremental refresh that will define how the data refreshes according to the set time intervals.
22. Structured Data can be easily analyzed and stored in __________ databases.
- NoSQL databases
- Azure Data Lake
- SQL (CORRECT)
That’s correct! Structured data is formatted and organized in a specific manner so that it could be easily analyzed and stored in SQL databases. Clear and consistent standards for structuring data enable easy categorization, indexing and querying, thereby streamlining data retrieval and analysis. By structured data, companies can utilize robust SQL database capability for better extraction, insights, and decision-making through data.
23. True or False: Unlike Excel workbooks stored on OneDrive or SharePoint team sites, you can’t edit local Excel files within Power BI.
- False
- True (CORRECT)
24. What is the purpose of the Scheduled Refresh feature in Power BI?
- To connect directly to data sources and query for updated data.
- To manually update the data in the dataset.
- To automate the process of refreshing the data on a regular basis. (CORRECT)
That’s correct! Scheduled Refresh automates the updating of data at timely intervals so that users are always up to date with the data, without having to trigger refreshing themselves.
25. Complete the following sentence: Some data sources marked as _____ or _____ have limited functionality and are not suitable for live environments.
- Live, Functional
- Legacy, Obsolete
- Beta, Preview (CORRECT)
That’s correct. Limited functionality is offered by beta or preview-data sources, rendering them unsuitable for live environments in which the stability and completeness of function are required.
26. What is the benefit of setting up an Excel data source for Power BI?
- To create a backup copy of the data
- To work more efficiently with data, collaborate more effectively with others, and gain better insights into your organization’s operations. (CORRECT)
- To export the data to another format
That’s correct! Undoubtedly, Excel is a good tool to use, but Power BI offers even more in terms of advanced data analysis or visualization. It possesses faster processing speeds and more customizable, professional-looking dashboards, with very advanced comparison features and a more intuitive interface that makes Power BI a more powerful solution when working with data. Basically, it improves collaboration, makes room for deeper insights, and ultimately supports making better-informed decisions. Thus, it could be termed an upgrade against Excel in analyzing and understanding the workings of your organization.
27. What are the two parameters you need to set up for incremental refresh? Select the two correct options.
- You need to set up a parameter for the size of the dataset.
- You need to set up a parameter for the start time of the refresh window. (CORRECT)
- You need to set up a parameter for the end time of the refresh window. (CORRECT)
- You need to set up a parameter for the type of data source.
It’s absolutely correct! Among the two metrics considered in incremental refresh, the periodicity is initiated with the start time of the fresh period.
That’s right! The other crucial parameter for incremental refresh is the end time of the refresh window.
28. Which one of the following actions cannot be undertaken once you set the storage mode of a table to Import?
- It cannot be modified to Upload mode.
- It cannot be changed to Publish mode.
- It cannot be switched to DirectQuery mode. (CORRECT)
That’s correct! A dataset that has its storage mode set to Import in power BI cannot directly change the storage mode, say to DirectQuery or Dual storage mode; it can be done only by recreating the dataset or modifying the data model that will switch storage modes.
29. What types of Microsoft Excel workbooks does Power BI support?
- Workbooks created in Excel 2007 and later with a file type of .xlsx or .xlsm. (CORRECT)
- Workbooks created in Excel 2003 and later with a file type of .xls or .xlsm.
- Workbooks created in Excel 2010 and later with a file type of .xls or .xlsx.
That’s correct! Documents created in newer versions of Excel, that is, Excel 2007 or later, can be imported or connected using Power BI since those documents are saved as .xlsx or .xlsm and have a limit of size not greater than 1GB to achieve an optimum functioning capability be performed.
CONCLUSION – Data Sources in Power BI
In conclusion, this module equips you with the knowledge and skills to set up and explore various data sources within Power BI. By understanding the diverse capabilities Power BI offers, you will be able to efficiently connect, manage, and analyze data from multiple origins. This hands-on experience ensures you are well-prepared to leverage Power BI’s functionalities to support your analytical needs and objectives, ultimately enhancing your ability to make data-driven decisions and achieve successful outcomes in your projects.