Power BI DA-100
What are the different data roles?
-Business analyst -Data analyst -Data engineer -Data scientist -Database administrator
What are the three ways to create a custom column?
-Create the column in the source query when you get the data, for instance, by adding the calculation to a view in a relational database. -Create the custom column in Power Query. -Create a calculated column by using DAX in Power BI.
How early do you want to remove unnecessary columns in the data shaping process?
A key step in the data shaping process is to remove unnecessary columns. It is much better to remove columns as early as possible. One way to remove columns would be to limit the column when you get data from data source. For instance, if you are extracting data from a relational database by using SQL, you would want to limit the column that you extract by using a column list in the SELECT statement.
What does a quick measure do?
A quick measure creates the calculation for you. They're easy and fast to create for simple and common calculations.
What should a simple table structure consist of?
A simple table structure will: -Be simple to navigate because of column and table properties that are specific and user-friendly. -Have merged or appended tables to simplify the tables within your data structure. -Have good-quality relationships between tables that make sense.
What is a view?
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 is a visualization?
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 should the visualizations achieve?
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 the danger of bi-directional cross-filtering?
A word of caution regarding bi-directional cross-filtering: You should not enable bi-directional cross-filtering relationships unless you fully understand the ramifications of doing so. Enabling it can lead to ambiguity, over-sampling, unexpected results, and potential performance degradation.
How do relationships impact filters?
Active relationships will propagate filters. It's possible to mark a relationship as inactive so filters don't propagate. Inactive relationships can exist when there are multiple relationship paths between tables. In which case, model calculations can use special functions to activate them.
How do you combine queries?
After you have finished reformatting, you can combine the queries. On the Home tab on the Power Query Editor ribbon, select the drop-down list for Append Queries. You can select Append Queries as New, which means that the output of appending will result in a new query or table, or you can select Append Queries, which will add the rows from an existing table into another.
What is true of WHERE clauses in SQL queries?
All queries should also have a WHERE clause. This clause will filter the rows to pick only filtered records that you want.
While query folding is one option to optimize performance when retrieving, importing, and preparing data, what is another option?
Another option is query diagnostics.
Why do you need to be careful about reordering steps in M?
Be careful about reordering these steps because it could ruin the statement dependencies.
What needs to take place before the data analyst can prepare their report?
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.
How do you examine the data structures?
Before you begin examining the data in Power Query Editor, you should first learn about the underlying data structures that data is organized in. You can view the current data model under the Model tab on Power BI Desktop.
Who can become a data engineer?
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.
How many rows does Power Query examine by default? How do you increase?
By default, Power Query examines the first 1000 rows of your data set. To change this, select the profiling status in the status bar and select Column profiling based on entire data set.
What is happening to you file when you use calculated columns?
Calculated columns are materialized in the .pbix Power BI file extension, meaning that each time you add a calculated column, you are increasing the size of the overall file. Having too many calculated columns will slow performance and will cause you to reach the maximum Power BI data size sooner.
What is Connect Live?
Connect live is a new option in Azure Analysis Services. Azure Analysis Services uses the tabular model and DAX to build calculations, similar to Power BI. These models are compatible with one another. Using the Connect live option helps you keep the data and DAX calculations in their original location, without having to import them all into Power BI.
What is DAX?
Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. It is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values.
How do data analyst interreact with the data engineer and database administrator?
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 data granularity?
Data granularity is an important topic for all data analysts, regardless of the Power BI tools that you are using. Defining the correct data granularity can have a big impact on the performance and usability of your Power BI reports and visuals.
What is data granularity?
Data granularity is the detail that is represented within your data, meaning that the more granularity your data has, the greater the level of detail within your data.
What can descriptions be applied to?
Descriptions can be applied to table, columns, hierarchies, or measures. In the Fields pane, description text is revealed in a tooltip when a report author hovers their cursor over the field.
What are descriptive analytics?
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 dimension tables?
Dimension tables contain the details about the data in fact tables: products, locations, employees, and order types. These tables are connected to the fact table through key columns. Dimension tables are used to filter and group the data in fact tables. The dimension tables, by contrast, contain unique values, for instance, one row for each product in the Products table and one row for each customer in the Customer table.
What happens in the background each time you create a step in Power Query?
Each time you shape data in Power Query, you create a step in the Power Query process. Those steps can be reordered, deleted, and modified where it makes sense. Each cleaning step that you made was likely created by using the graphical interface, but Power Query uses the M language behind the scenes.
What is true about the size of fact tables versus dimension tables?
Fact tables are usually much larger than dimension tables because numerous events occur in fact tables, such as individual sales. Dimension tables are typically smaller because you are limited to the number of items that you can filter and group on.
What is do fact tables contain? What do dimension tables contain?
Fact tables contain information about events such as sales orders, shipping dates, resellers, and suppliers. Dimension tables store details about business entities, such as products or time, and are connected back to fact tables through a relationship.
What are fact tables?
Fact tables contain observational or event data values: sales orders, product counts, prices, transactional dates and times, and quantities. Fact tables can contain several repeated values. For example, one product can appear multiple times in multiple rows, for different customers on different dates. These values can be aggregated to create visuals.
What is the filtering option is available for many-to-many relationships?
For many-to-many relationships, you can choose to filter in a single direction or in both directions by using bi-directional cross-filtering. The ambiguity that is associated with bi-directional cross-filtering is amplified in a many-to-many relationship because multiple paths will exist between different tables.
What is the filtering option is available for one-to-one relationships?
For one-to-one relationships, the only option that is available is bi-directional cross-filtering. Data can be filtered on either side of this relationship and result in one distinct, unambiguous value.
With respect to modeling, if your Power BI report is performing slowly what do you need to do?
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 is true about smaller data models?
Generally, a smaller data model is better because it will perform faster and will be simpler to use. However, defining what a smaller data model entails is equally as problematic because it's a heuristic and subjective concept.
What approach do you want to take with respect to the number of records you work with?
Generally, the fewer the records that you are working with, the faster your reports and visuals will function. This approach translates to a faster refresh rate for the entire dataset, which might mean that you can refresh more frequently.
What are the steps to merge tables?
Go to Home on the Power Query Editor ribbon and select the Merge Queries drop-down menu, where you can select Merge Queries as New. This selection will open a new window, where you can choose the tables that you want to merge from the drop-down list, and then select the column that is matching between the tables.
What do good SQL Server performance tuning techniques include?
Good SQL Server performance tuning techniques includes index creation, hardware upgrades, execution plan tuning, and data compression.
What are the basic building blocks in Power BI?
Here are the basic building blocks in Power BI: -Visualizations -Datasets -Reports -Dashboards -Tiles
What happens when Power BI uses a view?
If Power BI uses a view, when it retrieves data, it participates in query folding, a feature of Power Query.
How do you use the Pivot Column feature?
If the data that you are shaping is flat (in other words, it has lot of detail but is not organized or grouped in any way), the lack of structure can complicate your ability to identify patterns in the data. You can use the Pivot Column feature to convert your flat data into a table that contains an aggregate value for each unique value in a column. For example, you might want to use this feature to summarize data by using different math functions such as Count, Minimum, Maximum, Median, Average, or Sum.
What approach should you take if you need you need the rows, columns, and complexity?
If you need the rows, columns, and complexity, consider taking small chunks of data and then bringing them back together by using Power Query. For instance, you can combine half the columns in one query and the other half in a different query. Power Query can merge those two queries back together after you are finished.
What is the downside to limiting records?
If your users want to drill into every single transaction, summarizing the granularity will prevent them from doing that, which can have a negative impact on the user experience. It is important to negotiate the level of data granularity with report users so they understand the implications of these choices.
What is a Power BI report?
In Power BI, a report is a collection of visualizations that appear together on one or more pages.
What is a star schema?
In a star schema, each table within your dataset is defined as a dimension or a fact table.
What will incorrect data types result in?
Incorrect data types will prevent you from creating certain calculations, deriving hierarchies, or creating proper relationships with other tables
What is best practice with respect to where you write queries?
It is a best practice to avoid doing this directly in Power BI. Instead, consider writing a query like this in a view.
What does PATH() do?
It is a simple DAX function that returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current.
What do you need to consider when deciding on the correct storage mode?
It is important to select the correct storage mode for your data. Do you require that visuals interact quickly but don't mind possibly refreshing the data when the underlying data source changes? If so, select Import to import data into Power BI. If you prefer to see updates to data as soon as they happen at the cost of interactivity performance, then choose Direct Query for your data instead.
What are the properties of many-to-many (.) cardinality?
Many-to-many (.) cardinality: -Describes a relationship where many values are in common between two tables. -Does not require unique values in either table in a relationship. -Is not recommended; a lack of unique values introduces ambiguity and your users might not know which column of values is referring to what.
What is true about many-to-many relationships and/or bi-directional relationships?
Many-to-many relationships and/or bi-directional relationships are complicated. Unless you are certain what your data looks like when aggregated, these types of open-ended relationships with multiple filtering directions can introduce multiple paths through the data.
What are the properties of many-to-one (*:1) or one-to-many (1: *) cardinality?
Many-to-one (*:1) or one-to-many (1: *) cardinality: -Describes a relationship in which you have many instances of a value in one column that are related to only one unique corresponding instance in another column. -Describes the directionality between fact and dimension tables. -Is the most common type of directionality and is the Power BI default when you are automatically creating relationships.
What is Microsoft Power BI?
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.
Native queries are not possible for which transformations?
Native queries are not possible for the following transformations: -Adding an index column -Merging and appending columns of different tables with two different sources -Changing the data type of a column -Running complex DAX functions
What do you do when you encounter the "We couldn't find any data formatted as a table" error while importing data from Microsoft Excel.
Occasionally, you may encounter the "We couldn't find any data formatted as a table" error while importing data from Microsoft Excel. Fortunately, this error is self-explanatory. Power BI expects to find data formatted as a table from Excel. The error event tells you the resolution. Perform the following steps to resolve the issue: -Open your Excel workbook, and highlight the data that you want to import. -Press the Ctrl-T keyboard shortcut. The first row will likely be your column headers. -Verify that the column headers reflect how you want to name your columns. Then, try to import data from Excel again. This time, it should work.
Why would you want to null values?
Occasionally, you might find that your data sources contain null values. For example, a freight amount on a sales order might have a null value if it's synonymous with zero. If the value stays null, the averages will not calculate correctly. One solution would be to change the nulls to zero, which will produce the more accurate freight average. In this instance, using the same steps that you followed previously will help you replace the null values with zero.
How can you avoid using calculated column?
One way to avoid using a calculated column is to use one of the X functions, such as SUMX, COUNTX, MINX, and so on. They allow you to create measures that are aware of the data in individual rows and calculate totals based on the totals in the row.
What are the properties of one-to-one (1:1) cardinality?
One-to-one (1:1) cardinality: -Describes a relationship in which only one instance of a value is common between two tables. -Requires unique values in both tables. -Is not recommended because this relationship stores redundant information and suggests that the model is not designed correctly. It is better practice to combine the tables.
What are other ways to optimize query performance?
Other ways to optimize query performance in Power BI include: -Process as much data as possible in the original data source. Power Query and Power Query Editor allow you to process the data; however, the processing power that is required to complete this task might lower performance in other areas of your reports. Generally, a good practice is to process, as much as possible, in the native data source. -Use native SQL queries. When using DirectQuery for SQL databases, such as the case for our scenario, make sure that you are not pulling data from stored procedures or common table expressions (CTEs). -Separate date and time, if bound together. If any of your tables have columns that combine date and time, make sure that you separate them into distinct columns before importing them into Power BI. This approach will increase compression abilities.
How do you identify and fix problems in Power BI?
Power BI provides the Performance Analyzer tool to help fix problems and streamline the process.
How does Power BI determine data anomalies?
Power Query Editor determines data anomalies by using the Column Distribution feature.
What does it mean to set a data source to Organizational?
Privacy levels can be configured for data source to determine whether data can be shared between sources. Setting each data source as Organizational allows them to share data, if necessary.
What does it mean for a data source to be private?
Private data sources can never be shared with other data sources. It doesn't mean that Private data cannot be shared; it means that the Power Query engine cannot share data between the sources.
What is the purpose of data profiling?
Profiling data is about studying the nuances of the data: determining anomalies, examining and developing the underlying data structures, and querying data statistics such as row counts, value distributions, minimum and maximum values, averages, and so on.
What is the purpose of the query timeout.
Relational source systems often have many people who are concurrently using the same data in the same database. Some relational systems and their administrators seek to limit a user from monopolizing all hardware resources by setting a query timeout. These timeouts can be configured for any timespan, from as little as five seconds to as much as 30 minutes or more.
When retrieving data sources, how do you need to treat them differently?
Retrieving data from different data sources requires treating each data source differently. For instance, Microsoft Excel data should be pulled in from an Excel table. Relational databases often have query timeouts. You can connect to cubes with Connect live which allows you to see data changes in real-time.
What does PATHITEM() do?
Returns the item at the specified position from a string resulting from evaluation of a PATH function. Positions are counted from left to right.
What are role playing dimensions?
Role-playing dimensions have multiple valid relationships with fact tables, meaning that the same dimension can be used to filter multiple columns or tables of data. As a result, you can filter data differently depending on what information you need to retrieve.
How would you formulate an SQL query where ID, NAME and SALESAMOUNT are selected from the SALES table?
SELECT ID , NAME , SALESAMOUNT FROM SALES
What does Diagnose Step help you to see?
Selecting Diagnose Step shows you the length of time that it takes to run that step. This selection can tell you if a step takes longer to complete than others, which then serves as a starting point for further investigation.
What is true of DAX calculated columns with respect to compression?
The DAX calculated column does not compress as well as the other methods. The other column types do get compressed, which makes the .pbix file smaller and the performance usually faster.
How do you schedule datasets to refresh?
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.
Why is the ability to combine queries is powerful?
The ability to combine queries is powerful because it allows you to append or merge different tables or queries together.
What is the canvas?
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 happens when you try to use the custom column dialog via Power Query?
The custom column dialog uses the M language to create the new column.
What is true with respect to the timing of creating columns and mixing methods for calculation?
The earlier you can create a column, the better. It is not considered an optimum practice to use DAX for calculations if you can use a different mechanism.
What are the first steps when connecting your Power BI desktop tool to a SQL Server database?
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
What is the fixed decimal number data type and why is it important?
The fixed decimal number data type stores values with full precision, and so requires more storage space that decimal number. It's important to use the fixed decimal number type for financial values, or rates (like exchange rates).
What is flattening the hierarchy?
The process of viewing multiple child levels based on a top-level parent is known as flattening the hierarchy.
What is query folding and what does it help?
The query folding within Power Query Editor helps you increase the performance of your Power BI reports. Query folding is the process by which the transformations and edits that you make in Power Query Editor are simultaneously tracked as native queries, or simple Select SQL statements, while you are actively making transformations.
Why is data profiling important?
This concept is important because it allows you to shape and organize the data so that interacting with the data and identifying the distribution of the data is uncomplicated, therefore helping to make your task of working with the data on the front end to develop report elements near effortless.
What is the "Power BI Query Error: Timeout expired"?
This error indicates that you've pulled too much data according to your organization's policies. Administrators incorporate this policy to avoid slowing down a different application or suite of applications that might also be using that database.
What does this expression mean? if [TotalProductCost] = null then [OrderQuantity] * [StandardCost] else [TotalProductCost]
This expression tests if the TotalProductCost value is missing. If it is, produce a value by multiplying the OrderQuantity value by the StandardCost value; otherwise, it uses the existing TotalProductCost value.
What does query diagnostics allow for?
This feature allows you to determine what bottlenecks (if any) exist while loading and transforming your data, refreshing your data in Power Query, running SQL statements in Query Editor, and so on.
How do you access query diagnostics?
To access query diagnostics in Power Query Editor, go to Tools in the Home ribbon. When you are ready to begin transforming your data or making other edits in Power Query Editor, select Start Diagnostics on the Session Diagnostics tab. When you are finished, make sure that you select Stop Diagnostics.
How do you create a quick measure?
To create a quick measure in Power BI Desktop, right-click or select the ellipsis (...) button next to any item in the Fields pane and then select New quick measure from the menu that appears. The Quick measures screen will appear.
How do you start shaping data in Power Query Editor?
To start shaping your data, open Power Query Editor by selecting the Transform data option on the Home tab of Power BI Desktop.
What is the concept of directionality?
Unlike other database management systems, Power BI has the concept of directionality to a relationship. This directionality, or cardinality, plays an important role in filtering data between multiple tables.
When would you use unpivoting?
Unpivoting is a useful feature of Power BI. You can use this feature with data from any data source, but you would most often use it when importing data from Excel. The following example shows a sample Excel document with sales data.
What does unpivoting do with respect to creating DAX measures?
Unpivoting streamlines the process of creating DAX measures on the data later. By completing this process, you have now created a simpler way of slicing the data with the Year and Month columns.
What is true about the efficiency of using SQL to create a column?
Using SQL language is an efficient way of creating a column because it would make the data source do the calculations for you. In Power BI, the calculated column would appear like any other column.
What approach should you take if you get the "Could not find file" error?
Usually, this error is caused by the file moving locations or the permissions to the file changing. If the cause is the former, you need to find the file and change the source settings. -Open Power Query by selecting the Transform Data button in Power BI. -Highlight the query that is creating the error. -On the left, under Query Settings, select the gear icon next to Source.
What are ways that you can build a common date table?
Ways that you can build a common date table are: -Source data -DAX -Power Query
What do you need to do to correct column names that are not interpreted as headers?
When a table is created in Power BI Desktop, Power Query Editor assumes that all data belongs in table rows. However, a data source might have a first row that contains column names. To correct this inaccuracy, you need to promote the first table row into column headers.
What is important in terms of tables having unique columns?
When the distinct and unique counts are the same, it means the column contains unique values. When modeling, it's important that some tables contain unique columns. They will be used to create one-to-many relationships.
What is true about refreshing data when creating columns?
When you create a calculated column by using DAX, you do not need to refresh the dataset to see the new column. In the other methods, you would need a refresh to see changes. This process can be lengthy if you are working with a lot of data. However, this issue is irrelevant because, after columns have been created, they are rarely changed.
What does Power BI do to detect data type?
When you import a table from any data source, Power BI Desktop automatically starts scanning the first 1,000 rows (default setting) and tries to detect the type of data in the columns.
What is merging queries similar to with respect to SQL?
When you merge queries, you are combining the data from multiple tables into one based on a column that is common between the tables. This process is similar to the JOIN clause in SQL.
How does Power Query Editor track your work?
When you work in Power Query Editor, all steps that you take to shape your data are recorded. Then, each time the query connects to the data source, it automatically applies your steps, so your data is always shaped the way that you specified.
While importing data into Power BI, you may encounter errors resulting from factors such as?
While importing data into Power BI, you may encounter errors resulting from factors such as: -Power BI imports from numerous data sources. -Each data source might have dozens (and sometimes hundreds) of different error messages. -Other components can cause errors, such as hard drives, networks, software services, and operating systems. -Data can often not comply with any specific schema.
What does the single cross-filter direction accomplish?
With a single cross-filter direction: -Only one table in a relationship can be used to filter the data. For instance, Table 1 can be filtered by Table 2, but Table 2 cannot be filtered by Table 1. -For a one-to-many or many-to-one relationship, the cross-filter direction will be from the "one" side, meaning that the filtering will occur in the table that has unique values.
What does the cross-filter and bi-directional cross-filter accomplish?
With both cross-filter directions or bi-directional cross-filtering: -One table in a relationship can be used to filter the other. For instance, a dimension table can be filtered through the fact table, and the fact tables can be filtered through the dimension table. -You might have lower performance when using bi-directional cross-filtering with many-to-many relationships.
What is query folding?
With query folding, Power Query will optimize data retrieval according to how the data is being used later.
How do you write to a query formula?
Write to a query formula step by using the "in" statement. Generally, the last query step is used as the "in final data set" result.
What are the "join kinds" you can choose from when merging?
You can also choose how to join the two tables together, a process that is also similar to JOIN statements in SQL. These join options include: -Left Outer - Displays all rows from the first table and only the matching rows from the second. -Full Outer - Displays all rows from both tables. -Inner - Displays the matched rows between the two tables.
Under what circumstances can you combine tables into a single table?
You can combine tables into a single table in the following circumstances: -Too many tables exist, making it difficult to navigate an overly-complicated data model. -Several tables have a similar role. -A table has only a column or two that can fit into a different table. -You want to use several columns from different tables in a custom column
How do you promote headers?
You can promote headers in two ways: by selecting the Use First Row as Headers option on the Home tab or by selecting the drop-down button next to Column1 and then selecting Use First Row as Headers.
Where can you pull data from?
You can pull data from files, relational databases, cubes, cloud-based applications, websites, and more.
How do you resolve "What is the "Power BI Query Error: Timeout expired"?
You can resolve this error by pulling fewer columns or rows from a single table. While you are writing SQL statements, it might be a common practice to include groupings and aggregations. You can also join multiple tables in a single SQL statement. Additionally, you can perform complicated subqueries and nested queries in a single statement. These complexities add to the query processing requirements of the relational system and can greatly elongate the time of implementation.
How can you share Power BI dashboards?
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.
How can you use DAX?
You can use DAX to solve a number of calculations and data analysis problems, which can help you create new information from data that is already in your model.
How can you use Power Query to define a common date table?
You can use M-language, the development language that is used to build queries in Power Query, to define a common date table. Select Transform Data in Power BI Desktop, which will direct you to Power Query. In the blank space of the left Queries pane, right-click to open the following drop-down menu, where you will select New Query > Blank Query.
How do you see what apps you can connect?
You select the Get button in the Services box
What do the load and transform tools allow you to do?
-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.
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?
-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 is a NoSQL database?
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.
What rules apply to Power BI dashboards?
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).
What is the role of the data analyst?
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
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.
What is the role of the database administrator?
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.
What is a flat file?
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.
What is the guideline to know if you can use query folding?
A good guideline to remember is that if you can translate a transformation into a Select SQL statement, which includes operators and clauses such as GROUP BY, SORT BY, WHERE, UNION ALL, and JOIN, you can use query folding.
What do you need to after connecting your URL?
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.
How to you connect your URL?
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 are examples of descriptive analytics?
An example of descriptive analytics is generating reports to provide a view of an organization's sales and financial data.
What is the role of data connectors in Power BI?
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.
How can you use SQL query to import data?
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.
Who does the data engineer work with?
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.
How does the data analyst divide most of their time?
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 is important to keep in mind as you build reports?
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 does Azure Analysis Services allow you to do with respect to refresh schedules?
Azure Analysis Services can have a fast refresh schedule , which means that when data is refreshed in the service, Power BI reports will immediately be updated, without the need to initiate a Power BI refresh schedule. This process can improve the timeliness of the data in your report.
What is Azure Analysis Services?
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.
What are cognitive analytics?
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.
What is the role of the data engineer?
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.
What is data preparation?
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 is the role of the data scientist?
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 are diagnostic analytics?
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.
Why is using DirectQuery useful?
DirectQuery is useful because it ensures you are always viewing the most recent version of the data.
How getting data from Azure Analysis Services cubes similar to getting data from SQL Server?
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 do you do if the View Native Query option is not available?
If the View Native Query option is not available (not displayed in bold type), that query folding is not possible for this step, and you will have to work backward in the Applied Steps area until you reach the step in which View Native Query is available (displays in bold type). This process will reveal the native query that is used to transform the dataset.
What does Dual Mode import allow you to do?
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 a tile in Power BI?
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 are inferences structured for cognitive analytics?
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 must you do to import a JSON type record?
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.
What are notable differences between Azure Analysis Services cubes and SQL Server?
Notable differences between Azure Analysis Services cubes and SQL Server are: -Analysis Services cubes have calculations already in the cube, which will be discussed in more detail later. -If you don't need an entire table, you can query the data directly. Instead of using Transact-SQL (T-SQL) to query the data, like you would in SQL Server, you can use multi-dimensional expressions (MDX) or data analysis expressions (DAX).
What are the steps of diagnostic analytics?
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 does managing entail for the data analyst?
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.
What is an app?
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.
What are predictive analytics?
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 are prescriptive analytics?
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?
Prescriptive analytics techniques rely on machine learning strategies to find patterns in large datasets.
How does data management reduce duplication of efforts?
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.
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?
SELECT ID , NAME , SALESAMOUNT FROM SALES WHERE OrderDate >= '1/1/2020'
What does SQL stand for?
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.
How do you connect data in an application?
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 is connect live similar to a relational database?
Similar to a relational database, you can choose the tables that you want to use. If you want to directly query the Azure Analysis Services model, you can use DAX or MDX.
How do you import datasets when they are too large or when you have security requirements?
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.
What techniques does predictive analytics use?
Techniques include a variety of statistical and machine learning techniques such as neural networks, decision trees, and regression.
What does DirectQuery allow for with respect to caching data and meeting security requirements?
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.
What is import mode?
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.
How do you start a SQL query?
The SQL query starts with a "Select" statement, which allows you to choose the specific fields that you want to pull from your database.
What is the most important step of understanding and interpreting the information that is displayed on the report?
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 are the benefits to query folding?
The benefits to query folding include: -More efficiency in data refreshes and incremental refreshes. When you import data tables by using query folding, Power BI is better able to allocate resources and refresh the data faster because Power BI does not have to run through each transformation locally. -Automatic compatibility with DirectQuery and Dual storage modes. All DirectQuery and Dual storage mode data sources must have the back-end server processing abilities to create a direct connection, which means that query folding is an automatic capability that you can use. If all transformations can be reduced to a single Select statement, then query folding can occur.
What is the common flow of activity for working in Power BI?
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 is the goal of a visual?
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 does performance in Power Query depend on?
The performance in Power Query depends on the performance at the data source level.
What are the three different types of storage modes you can choose from?
The three different types of storage modes you can choose from: -Import -DirectQuery -Dual (Composite)
Why is using the wildcard discouraged?
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.
How does DirectQuery help with large datasets?
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 are the divisions of the core components of analytics?
To analyze data, core components of analytics are divided into the following categories: -Descriptive -Diagnostic -Predictive -Prescriptive -Cognitive
What are the basic building blocks of Power BI and their roles?
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 follows data preparation?
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.
When using an SQL query to import data, what approach should you take with respect to the wildcard character (*) in your query.
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.
What is a Power BI dashboard?
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 is the role of the business analyst?
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.
How do you connect data in Power BI?
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.
How do you update data in Power BI?
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.
What are additional features of using the SQL query?
You can also join different tables, run specific calculations, create logical statements, and filter data in your SQL query.
How can you use Power Query to load and transform data in Power BI?
You can use Power Query to load data into Power BI. Using Power Query Editor you can then make further transformations to your data, such as renaming or deleting columns, appending, parsing, filtering, or grouping your data.
How do you connect Power BI to a relational database?
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 your options in the Navigator window?
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 to data in Azure Analysis Services?
You use the Get data feature in Power BI Desktop. When you select Analysis Services, you are prompted for the server address and the database name with two options: Import and Connect live.
What is an acceptable alternative with respect to importing the data that you want (from Excel, SQL Server, and so on) into the Azure Analysis Services model and using a live connection. What are the advantages of this alternative?
You will likely import the data directly into Power BI. An acceptable alternative is to import all other data that you want (from Excel, SQL Server, and so on) into the Azure Analysis Services model and then use a live connection. Using this approach, the data modeling and DAX measures are all performed in one place, and it's a much simpler and easier way to maintain your solution.
How do you connect a NoSQL (Azure Cosmos DB) to Power BI?
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.
In what locations might your Excel files be located?
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.
