Learning Data Analytics Part 2: Extending and Applying Core Knowledge

You want a list of all products your company sold last year to see which products did not sell. You change Unique Values in the Access Property Sheet from No to Yes before running the query. Why?

It will show each product only once rather than by how many times it was sold.

Why are business rules important?

- They help define the data - They impact the timing of data - They drive the necessary calculations

You want to see what percentage a value is of the total values. Which option will you choose?

% of Grand Total—changing the value to show a grand total of the percentage will show you each value at each cross-section's percentage

Adding a filter directly to a visual is an example of _____.

A hard coded filter on that visual

What is an ad hoc report?

A one-time request on data—an ad hoc report is a one-time request. It sometimes becomes a standard or organizational report

You want a visual of products on backorder from vendors, products received damaged, and products returned to vendors. There are 40 products to visualize. What is a good chart type to use?

A stacked bar chart—the bars will show you what percentage of total orders each variable accounts for.

You have multiple datasets to export and need them to be combined into a single file. What command should you use?

Append using folders—append using folders will read every file that you drop into the folder and automatically combine into a single data set

In data analytics, noise is _____.

Data that is not meaningful for reporting—systems generate a lot of data that is meaningful to the system but not always meaningful to your reporting. Noise is anything that isn't valuable to the report or visualization.

What is the key difference between embed and link?

Embed makes a copy of the data and link establishes a link to the data—embed will make a copy of the data and link will establish a link to the data. If your data changes in the source file, only the link will update the data automatically

You have a mock-up of a dashboard with some noninteractive components, and you want a colleague to provide feedback. What is the easiest way to receive the feedback?

Export the mock-up as a PDF, and then send the PDF by email—because the components are not interactive, emailing a PDF is the easiest and quickest way to receive feedback

You want to add education level to your Income Survey data set to show changes in income as more education is attained. In what order should you sort the data?

From lowest to highest education attainment level—this shows changes in income from high school education to college and so on, which is what you want

What is the first step you should take before adding artifacts to a dashboard?

Gather and clean the data—the only way to get started with a dashboard is to get your data gathered and cleaned.

You create an Excel chart for 2021 year-end sales and want to include it in a PowerPoint presentation. How should you do this?

Insert a picture—because your chart shows activity that already occurred and will not change, a picture is the best choice and the easiest

What is one of the final steps before publishing your dashboard?

Intricate sizing and alignment of the objects/artifacts on the dashboard—sizing and alignment makes the most sense when you have everything already laid out and ready to publish.

You use a distinct query when you _____.

Need a unique list of information—A distinct query will allow you to add fields, and then only display the unique records in your data set based on those fields.

How can you prevent people from consuming your new organizational visualization?

Permissions & Licenses—permissions are the technical barrier; if you don't have a license to view the information, you will be denied access

You would like to determine which year in the last five years a particular product had its highest percentage of sales. How can you do this with a pivot?

Right-click on the first value in the table, and then choose Show Values As > % of Row Total—this shows you the percentage of sales in each of the last five years of the product's total sales

You use Select queries to build data sets that _____.

Select fields of data—When selecting data from a table in a data set, you only need a select query.

When working with charts, you can save time by _____.

Setting the default chart and building your own custom templates—using the default chart and building your templates will help you save time on reformatting the charts.

Building basic visuals will allow you to _____.

Spot issues in your data early—first working with lots of data, a basic visual at the beginning will allow you to spot issues early on and take care of them

Your data set shows survey results of your website visitors. What information of interest can you quickly view by using column profile?

The value distribution of visitors by age—you can view distributions for other data of interest as well

Your customer table has a column titled Customer Type. How does this most likely relate to your company's business rules?

There is a business rule that defines what a Customer Type is—The rule would define, for example, Lead as someone who should be contacted and Current as someone who has ordered.

Why would you use custom sorting?

To create a sort that is predefined—the custom sort by a table allows you to completely define how your data is sorted by any value. It removes the limits of sorting by highest to lowest, or alphabetical sorting.

What query is used to update data fields?

Update queries—will update values in a field or in a table.

If you want to visualize data line by line in a pivot table, you can _____.

Use Sparklines—sparklines let you apply win/loss, columns and other features of your data line by line in a pivot table (and in any table)

Your company, GreenPath, wants to use its medium green brand color on all visuals. How can you do this easily and quickly?

Use a theme—this enables you to quickly apply the company's brand colors to your visuals, including font colors and effects

When would you use Merge queries?

When you are addressing joins with multiple tables—Power Query and other software use Merge queries for this purpose