Power EXAM BI DA-100: Analyzing Data with Microsoft Power BI(latest version)

What are the divisions of the core components of analytics? correct answer- To analyze data, core components of analytics are divided into the following categories: -Descriptive -Diagnostic -Predictive -Prescriptive -Cognitive What are descriptive analytics? correct answer- Descriptive analytics help answer questions about what has happened based on historical data. Descriptive analytics techniques summarize large datasets to describe outcomes to stakeholders. What are examples of descriptive analytics? correct answer- An example of descriptive analytics is generating reports to provide a view of an organization's sales and financial data. What are diagnostic analytics? correct answer- Diagnostic analytics help answer questions about why events happened. Diagnostic analytics techniques supplement basic descriptive analytics, and they use the findings from descriptive analytics to discover the cause of these events. What are the steps of diagnostic analytics? correct answer- Performance indicators are further investigated to discover why these events improved or became worse. Generally, this process occurs in three steps: 1. Identify anomalies in the data. These anomalies might be unexpected changes in a metric or a particular market. 2. Collect data that's related to these anomalies. 3. Use statistical techniques to discover relationships and trends that explain these anomalies. What are predictive analytics? correct answer- Predictive analytics help answer questions about what will happen in the future. Predictive analytics techniques use historical data to identify trends and determine if they're likely to recur. What techniques does predictive analytics use? correct answer- Techniques include a variety of statistical and machine learning techniques such as neural networks, decision trees, and regression. What are prescriptive analytics? correct answer- Prescriptive analytics help answer questions about which actions should be taken to achieve a goal or target. By using insights from predictive analytics, organizations can make data-driven decisions. This technique allows businesses to make informed decisions in the face of uncertainty. What tools does prescriptive analytics rely on? correct answer- Prescriptive analytics techniques rely on machine learning strategies to find patterns in large datasets. What are cognitive analytics? correct answer- Cognitive analytics attempt to draw inferences from existing data and patterns, derive conclusions based on existing knowledge bases, and then add these findings back into the knowledge base for future inferences, a self-learning feedback loop. Cognitive analytics help you learn what might happen if circumstances change and determine how you might handle these situations. How are inferences structured for cognitive analytics? correct answer- Inferences aren't structured queries based on a rules database; rather, they're unstructured hypotheses that are gathered from several sources and expressed with varying degrees of confidence. Effective cognitive analytics depend on machine learning algorithms, and will use several natural language processing concepts to make sense of previously untapped data sources, such as call center conversation logs and product reviews. What are the different data roles? correct answer- -Business analyst -Data analyst -Data engineer -Data scientist -Database administrator What is the role of the business analyst? correct answer- While some similarities exist between a data analyst and business analyst, the key differentiator between the two roles is what they do with data. A business analyst is closer to the business and is a specialist in interpreting the data that comes from the visualization. What is the role of the data analyst? correct answer- A data analyst enables businesses to maximize the value of their data assets through visualization and reporting tools such as Microsoft Power BI. Data analysts are responsible for profiling, cleaning, and transforming data. Their responsibilities also include designing and building scalable and effective data models, and enabling and implementing the advanced analytics capabilities into reports for analysis. What are the responsibilities of the data analyst with respect to Power BI correct answer- A data analyst is also responsible for the management of Power BI assets, including reports, dashboards, workspaces, and the underlying datasets that are used in the reports. They are tasked with implementing and configuring proper security procedures, in conjunction with stakeholder requirements, to ensure the safekeeping of all Power BI assets and their data. How do data analyst interreact with the data engineer and database administrator? correct answer- Data analysts work with data engineers to determine and locate appropriate data sources that meet stakeholder requirements. Additionally, data analysts work with the data engineer and database administrator to ensure that the analyst has proper access to the needed data sources. The data analyst also works with the data engineer to identify new processes or improve existing processes for collecting data for analysis. What is the role of the data engineer? correct answer- Data engineers provision and set up data platform technologies that are on-premises and in the cloud. They manage and secure the flow of structured and unstructured data from multiple sources. The data platforms that they use can include relational databases, nonrelational databases, data streams, and file stores. Data engineers also ensure that data services securely and seamlessly integrate across data services. Who does the data engineer work with? correct answer- As a data analyst, you would work closely with a data engineer in making sure that you can access the variety of structured and unstructured data sources because they will support you in optimizing data models, which are typically served from a modern data warehouse or data lake. Who can become a data engineer? correct answer- Both database administrators and business intelligence professionals can transition to a data engineer role; they need to learn the tools and technology that are used to process large amounts of data. What is the role of the data scientist? correct answer- Data scientists perform advanced analytics to extract value from data. Their work can vary from descriptive analytics to predictive analytics. Descriptive analytics evaluate data through a process known as exploratory data analysis (EDA). Predictive analytics are used in machine learning to apply modeling techniques that can detect anomalies or patterns. These analytics are important parts of forecast models. What is the role of the database administrator? correct answer- A database administrator implements and manages the operational aspects of cloud-native and hybrid data platform solutions that are built on Microsoft Azure data services and Microsoft SQL Server. A data administrator is responsible for the overall availability and consistent performance and optimizations of the database solutions. They work with stakeholders to identify and implement the policies, tools, and processes for data backup and recovery plans. How does the data analyst divide most of their time? correct answer- As a data analyst, you'll likely divide most of your time between the prepare and model tasks. Deficient or incorrect data can have a major impact that results in invalid reports, a loss of trust, and a negative effect on business decisions, which can lead to loss in revenue, a negative business impact, and more. What needs to take place before the data analyst can prepare their report? correct answer- Before a report can be created, data must be prepared. Data preparation is the process of profiling, cleaning, and transforming your data to get it ready to model and visualize. What is data preparation? correct answer- Data preparation is the process of taking raw data and turning it into information that is trusted and understandable. It involves, among other things, ensuring the integrity of the data, correcting wrong or inaccurate data, identifying missing data, converting data from one structure to another or from one type to another, or even a task as simple as making data more readable. What follows data preparation? correct answer- When the data is in a proper state, it's ready to be modeled. Data modeling is the process of determining how your tables are related to each other. This process is done by defining and creating relationships between the tables. From that point, you can enhance the model by defining metrics and adding custom calculations to enrich your data. With respect to modeling, if your Power BI report is performing slowly what do you need to do? correct answer- From a Power BI perspective, if your report is performing slowly, or your refreshes are taking a long time, you will likely need to revisit the data preparation and modeling tasks to optimize your report. What should the visualizations achieve? correct answer- A well-designed report should tell a compelling story about that data, which will enable business decision makers to quickly gain needed insights. By using appropriate visualizations and interactions, you can provide an effective report that guides the reader through the content quickly and efficiently, therefore allowing the reader to follow a narrative into the data. What is important to keep in mind as you build reports? correct answer- As you build reports, it is important to think about people who will be accessing and reading the reports. Reports should be designed with accessibility in mind from the outset so that no special modifications are needed in the future. What is the most important step of understanding and interpreting the information that is displayed on the report? correct answer- The analyze task is the important step of understanding and interpreting the information that is displayed on the report. In your role as a data analyst, you should understand the analytical capabilities of Power BI and use those capabilities to find insights, identify patterns and trends, predict outcomes, and then communicate those insights in a way that everyone can understand. What does managing entail for the data analyst? correct answer- Power BI consists of many components, including reports, dashboards, workspaces, datasets, and more. As a data analyst, you are responsible for the management of these Power BI assets, overseeing the sharing and distribution of items, such as reports and dashboards, and ensuring the security of Power BI assets. How does data management reduce duplication of efforts? correct answer- Proper management can also help reduce data silos within your organization. Data duplication can make managing and introducing data latency difficult when resources are overused. Power BI helps reduce data silos with the use of shared datasets, and it allows you to reuse data that you have prepared and modeled. For key business data, endorsing a dataset as certified can help to ensure trust in that data. The management of Power BI assets helps reduce the duplication of efforts and helps ensure security of the data. What is Microsoft Power BI? correct answer- Microsoft Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. What is the common flow of activity for working in Power BI? correct answer- The common flow of activity looks like this: 1. Bring data into Power BI Desktop, and create a report. 2. Publish to the Power BI service, where you can create new visualizations or build dashboards. 3. Share dashboards with others, especially people who are on the go. 4. View and interact with shared dashboards and reports in Power BI Mobile apps. What are the basic building blocks in Power BI? correct answer- Here are the basic building blocks in Power BI: -Visualizations -Datasets -Reports -Dashboards -Tiles What is a visualization? correct answer- A visualization (sometimes also referred to as a visual) is a visual representation of data, like a chart, a color-coded map, or other interesting things you can create to represent your data visually. What is the goal of a visual? correct answer- The goal of a visual is to present data in a way that provides context and insights, both of which would probably be difficult to discern from a raw table of numbers or text. What is the role of data connectors in Power BI? correct answer- An important and enabling part of Power BI is the multitude of data connectors that are included. Whether the data you want is in Excel or a Microsoft SQL Server database, in Azure or Oracle, or in a service like Facebook, Salesforce, or MailChimp, Power BI has built-in data connectors that let you easily connect to that data, filter it if necessary, and bring it into your dataset. What is a Power BI report? correct answer- In Power BI, a report is a collection of visualizations that appear together on one or more pages. What is a Power BI dashboard? correct answer- When you're ready to share a single page from a report, or a collection of visualizations, you create a dashboard. Much like the dashboard in a car, a Power BI dashboard is a collection of visuals from a single page that you can share with others. Often, it's a selected group of visuals that provide quick insight into the data or story you're trying to present. What rules apply to Power BI dashboards? correct answer- A dashboard must fit on a single page, often called a canvas (the canvas is the blank backdrop in Power BI Desktop or the service, where you put visualizations). How can you share Power BI dashboards? correct answer- You can share dashboards with other users or groups, who can then interact with your dashboards when they're in the Power BI service or on their mobile device. What is a tile in Power BI? correct answer- In Power BI, a tile is a single visualization on a report or a dashboard. It's the rectangular box that holds an individual visual. In the following image, you see one tile, which is also surrounded by other tiles. How do you connect data in Power BI? correct answer- With Power BI, connecting to data is easy. From the Power BI service, you can just select the Get Data button in the lower-left corner of the home page. What is the canvas? correct answer- The canvas (the area in the center of the Power BI service) shows you the available sources of data in the Power BI service. In addition to common data sources like Microsoft Excel files, databases, or Microsoft Azure data, Power BI can just as easily connect to a whole assortment of software services (also called SaaS providers or cloud services): Salesforce, Facebook, Google Analytics, and more. What is an app? correct answer- Power BI's collection of visuals is called an app. Apps get you up and running quickly, with data and dashboards that your organization has created for you. For example, when you use the GitHub app, Power BI connects to your GitHub account (after you provide your credentials) and then populates a predefined collection of visuals and dashboards in Power BI. How do you see what apps you can connect? correct answer- You select the Get button in the Services box How do you update data in Power BI? correct answer- You can also choose to update the dataset for an app, or other data that you use in Power BI. To set update settings, select the schedule update icon for the dataset to update, and then use the menu that appears. You can also select the update icon (the circle with an arrow) next to the schedule update icon to update the dataset immediately. How do you schedule datasets to refresh? correct answer- The Datasets tab is selected on the Settings page that appears. In the right pane, select the arrow next to Scheduled refresh to expand that section. The Settings dialog box appears on the canvas, letting you set the update settings that meet your needs. What are the basic building blocks of Power BI and their roles? correct answer- We also discussed the basic building blocks in Power BI: -Visualizations: A visual representation of data, sometimes just called visuals -Datasets: A collection of data that Power BI uses to create visualizations -Reports: A collection of visuals from a dataset, spanning one or more pages -Dashboards: A single-page collection of visuals built from a report -Tiles: A single visualization on a report or dashboard What is a flat file? correct answer- A flat file is a type of file that has only one data table and every row of data is in the same structure. The file does not contain hierarchies. Likely, you're familiar with the most common types of flat files, which are comma- separated values (.csv) files, delimited text (.txt) files, and fixed width files. In what locations might your Excel files be located? correct answer- Your Excel files might exist in one of the following locations: Local: You can import data from a local file into Power BI. OneDrive for Business: You can pull data from OneDrive for Business into Power BI. OneDrive Personal: You can use data from files on a personal OneDrive account, and get many of the same benefits that you would with OneDrive for Business. SharePoint Team Sites: Saving your Power BI Desktop files to SharePoint Team Sites is similar to saving to OneDrive for Business. What are your options in the Navigator window? correct answer- You now have the option to select the Load button to automatically load your data into the Power BI model or select the Transform Data button to launch the Power Query Editor, where you can review and clean your data before loading it into the Power BI model. How do you connect Power BI to a relational database? correct answer- You can use the Get data feature in Power BI Desktop and select the applicable option for your relational database. You select the SQL Server option. What are the first steps when connecting your Power BI desktop tool to a SQL Server database? correct answer- The first step is to enter your database server name and a database name in the SQL Server database window. The two options in data connectivity mode are: Import (selected by default, recommended) and DirectQuery. Mostly, you select Import. Other advanced options are also available in the SQL Server database window After you have added your server and database names, you will be prompted to sign in with a username and password. You will have three sign-in options. What are they? correct answer- -Windows: Use your Windows account (Azure Active Directory credentials). -Database: Use your database credentials. For instance, SQL Server has its own sign- in and authentication system that is sometimes used. If the database administrator gave you a unique sign-in to the database, you might need to enter those credentials on the Database tab. -Microsoft account: Use your Microsoft account credentials. This option is often used for Azure services. What do the load and transform tools allow you to do? correct answer- -Load: Automatically load your data into a Power BI model in its current state. -Transform Data: Open your data in Microsoft Power Query, where you can perform actions such as deleting unnecessary rows or columns, grouping your data, removing errors, and many other data quality tasks. How can you use SQL query to import data? correct answer- Another way you can import data is to write an SQL query to specify only the tables and columns that you need. To write your SQL query, on the SQL Server database window, enter your server and database names, and then select the arrow next to Advanced options to expand this section and view your options. In the SQL statement box, write your query statement, and then select OK. What does SQL stand for? correct answer- SQL stands for Structured Query Language and is a standardized programming language that is used to manage relational databases and perform various data management operations. What are additional features of using the SQL query? correct answer- You can also join different tables, run specific calculations, create logical statements, and filter data in your SQL query. How do you start a SQL query? correct answer- The SQL query starts with a "Select" statement, which allows you to choose the specific fields that you want to pull from your database. How would you formulate an SQL query where ID, NAME and SALESAMOUNT are selected from the SALES table? correct answer- SELECT ID , NAME , SALESAMOUNT FROM SALES When using an SQL query to import data, what approach should you take with respect to the wildcard character (*) in your query. correct answer- When using an SQL query to import data, try to avoid using the wildcard character (*) in your query. If you use the wildcard character (*) in your SELECT statement, you import all columns that you don't need from the specified table. Why is using the wildcard discouraged? correct answer- The wildcard is not recommended because it will lead to redundant data in your data model, which will cause performance issues and require additional steps to normalize your data for reporting. What is true of WHERE clauses in SQL queries? correct answer- All queries should also have a WHERE clause. This clause will filter the rows to pick only filtered records that you want. How would you formulate an SQL query where ID, NAME and SALESAMOUNT are selected from the SALES table with recent sales data after Jan 1, 2020? correct answer- SELECT ID , NAME , SALESAMOUNT FROM SALES WHERE OrderDate = '1/1/2020' What is best practice with respect to where you write queries? correct answer- It is a best practice to avoid doing this directly in Power BI. Instead, consider writing a query like this in a view. What is a view? correct answer- A view is an object in a relational database, similar to a table. Views have rows and columns, and can contain almost every operator in the SQL language. What happens when Power BI uses a view? correct answer- If Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Power Query. What is query folding? correct answer- With query folding, Power Query will optimize data retrieval according to how the data is being used later. What is a NoSQL database? correct answer- A NoSQL database (also referred to as non-SQL, not only SQL or non-relational) is a flexible type of database that does not use tables to store data. How do you connect a NoSQL (Azure Cosmos DB) to Power BI? correct answer- You will use the Get data feature in Power BI Desktop. However, this time you will select the More... option to locate and connect to the type of database that you use. In this example, you will select the Azure category, select Azure Cosmos DB, and then select Connect. What must you do to import a JSON type record? correct answer- JSON type records must be extracted and normalized before you can report on them, so you need to transform the data before loading it into Power BI Desktop. How do you connect data in an application? correct answer- Select the "Get data" feature in Power BI Desktop. Then, select the option that you need from the Online Services category. In this example, you select SharePoint Online List. How to you connect your URL? correct answer- After you have selected Connect, you'll be asked for your SharePoint URL. This URL is the one that you use to sign into your SharePoint site through a web browser. You do not need to enter your full URL file path; you only need to load your site URL because, when you are connected, you can select the specific list that you want to load. What do you need to after connecting your URL? correct answer- After you have entered your URL, select OK. Power BI needs to authorize the connection to SharePoint, so sign in with your Microsoft account and then select Connect. correct answer- After Power BI has made the connection with SharePoint, the Navigator window appears, as it does when you connect to other data sources. The window displays the tables and entities within your SharePoint site. Select the list that you want to load into Power BI Desktop. Similar to when you import from other data sources, you have the option to automatically load your data into Power BI model or launch the Power Query Editor to transform your data before loading it. For this example, you select the Load option. How do you import datasets when they are too large or when you have security requirements? correct answer- Sometimes there may be security requirements around your data that make it impossible to directly import a copy. Or your datasets may simply be too large and would take too long to load into Power BI, and you want to avoid creating a performance bottleneck. Power BI solves these problems by using the DirectQuery storage mode, which allows you to query the data in the data source directly and not import a copy into Power BI. Why is using DirectQuery useful? correct answer- DirectQuery is useful because it ensures you are always viewing the most recent version of the data. What are the three different types of storage modes you can choose from? correct answer- The three different types of storage modes you can choose from: -Import -DirectQuery -Dual (Composite) What is import mode? correct answer- The Import mode allows you to create a local Power BI copy of your datasets from your data source. You can use all Power BI service features with this storage mode, including Q&A and Quick Insights. However, data refreshes must be done manually. Import mode is the default for creating new Power BI reports. What does DirectQuery allow for with respect to caching data and meeting security requirements? correct answer- The DirectQuery option is useful when you do not want to save local copies of your data because your data will not be cached. Instead, you can query the specific tables that you will need by using native Power BI queries, and the required data will be retrieved from the underlying data source. Using this model ensures that you are always viewing the most up-to-date data, and that all security requirements are satisfied. How does DirectQuery help with large datasets? correct answer- This mode is suited for when you have large datasets to pull data from. Instead of slowing down performance by having to load large amounts of data into Power BI, you can use DirectQuery to create a connection to the source, solving data latency issues as well. What does Dual Mode import allow you to do? correct answer- In Dual mode, you can identify some data to be directly imported and other data that must be queried. Any table that is brought in to your report is a product of both Import and DirectQuery modes. Using the Dual mode allows Power BI to choose the most efficient form of data retrieval. What is Azure Analysis Services? correct answer- Azure Analysis Services is an Azure product that allows you to ingest data from multiple data sources, build relationships between the data, and creates calculations on the data. The calculations are built using data analysis expressions (DAX). Azure Analysis Services is similar to the data modeling and storage technology in Power BI. How getting data from Azure Analysis Services cubes similar to getting data from SQL Server? correct answer- Getting data from Azure Analysis Services cubes is similar to getting data from SQL Server, in that you can: -Authenticate to the server. -Pick the cube you want to use. -Select which tables you need. What are notable differences between Azure Analysis Services cubes and SQL Server? correct answer- Notable differences