ITGSS Certified Advanced Professional: Data Analyst

What shows by default on the tooltip for any visual you create?

The fields used for the visual -- By default, when you add fields to a visual, they will display in the default tooltip.

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.

There are many reasons to change case of any data. Using Proper() changes case to proper in Excel. What is the equivalent command in Power Query?

Transform > Capitalize each word -- Transform > Capitalize each word is the same outcome as using the Proper() function in Excel.

What query is used to update data fields?

Update queries -- Update queries will update values in a field or in a 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.

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.

Reviewing table design is important for a data analyst. What is something you will discover in the design of tables?

data types

What is a form of data cleaning and transformation?

deleting columns or adding calculations to an Excel spreadsheet

A file that is not connected to a live data set, and that you receive through either email or an export, is an example of a(n) _____.

flat file -- Flat files are data sets that are not directly connected to the data source. They are typically are exports to .csv or Excel files that you run yourself or have been emailed to you.

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.

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.

In Power Query, merge queries allow us to _____.

join data together -- Merge queries are the database equivalent to writing a select query.

Changes we make to our data in Power Query are recorded in _____.

Applied Steps -- When making changes to the data in Power Query, it automatically tracks each step in the Applied steps.

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.

What is an example of a business rule for data?

A bundle sale is defined as a minimum of 3 products sold at the same time. -- Business rules can be as simple as a definition. In this case, a transaction with the same date/time with a sale of three products is classified as a business rule.

Maintaining your original data set provides you with which option(s)? A. the benefit of not starting entirely back over after a mistake B. an audit trail C. a better understanding of your data D. none of these answers

A, B -- Keeping a copy of the original data provides you the ability to just quickly start back over after a mistake. It will also give you the ability to return to the original data for auditing the work.

Which items are examples of data cleaning? A. removing unnecessary columns B. changing the case of data (upper, lower, etc.) C. connecting to the data in a database D. removing unnecessary spaces from data

A, B, D -- Removing columns, spaces, and changing case of data are all examples of data cleaning.

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.

What is an example of a visual element that should be included where appropriate in a dashboard?

All of the answers: tooltip, data label, and visual title -- Be sure to title, label, and add tooltips appropriately. People should be able to read a title for context, understand the labels, and hover to get additional insight needed to support the visual—not just see the same thing that visual already shows.

What is an example of Master Data Management rules?

All of these answers: - All two letter abbreviations for State/Region/Province names must be in all caps. - Each department name will include the department number. - Each contact name shall include first name, last name, and middle initial. -- Master Data Management aims to keep a clean, complete, and accurate list of the master data for the organization. This allows everyone in an organization who works with the data to have the same consistent list of information.

What feature in Power BI automatically attempts to detect relationships?

AutoDetect -- Power BI tries to join data automatically through AutoDetect. You should always confirm that the relationships it establishes are correct.

What is not an example of a logical function?

Average -- Average is not considered a logical function, but an aggregate function.

Which items are best practices for being effective in a meeting? A. Dive right into the data and information when it's your turn. B. Ask to be on the agenda and state how much time you need. C. Provide a list of key definitions and consider a slide deck.

B, C -- As a data analyst, it is important that you have time to cover the information you need answers for your questions. Providing a key list of definitions up front is important to reduce time spent answering questions that you can answer with a simple document. Including a slide deck will help you stay focused and on point.

Basic SQL query

Basic queries allow you to select data from the database. SELECT -- fields from the table FROM -- table name WHERE -- filter data ORDER BY -- sort data where & order are not required statements. When used together you need to filter the data before you sort it.

Merge Columns replaces what function in Excel?

Concat() or Concatenate() -- Concat() and Concatenate() both combine values in Excel, and the outcome of Merge Columns is the same as both of these commands in Excel.

You need to create a status field in Power Query for any order that has 30 days between the start of the order and the ship date. It will provide a status value of "Over 30 Days." Which built-in function can you use?

Conditional Column -- This logical function will allow us to split the data into categories, and in this case, create a Status field with values of "Over 30 Days" for the rows that match.

It's not always obvious when looking at large data sets that data is duplicated. Which command in Excel lets you easily highlight duplicate values?

Conditional Formatting -- Excel Conditional Formatting has a highlight option that will immediately apply formatting that shows duplicated data.

You should _____ the headers on a new sheet tab to provide valuable information with your data.

Copy and transpose -- Transpose allows you to flip data from horizontal to vertical, which can be helpful when documenting information about fields and while providing data sets to others.

Master Data

Customer and address information project names product names

What is a critical component of every data role?

Documentation -- Documentation is a key component of any data project.

When data analysts consider all the data from any object, this is an example of them turning on their _____.

Data lens -- The data lens is something that a data analyst will improve over time. The more exposure to data you have, the more likely you are to think of data points when they are not exactly in front of you on a table or spreadsheet.

Determining the count of values and basic transformation requirements in a data set is known as _____.

Data profiling -- Data profiling is a high-level profile of the characteristics of the working data. Profiling helps determine what steps you should work through on the transformation of your data.

Structured Data

Data that fits neatly into tables or spreadsheets

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.

When working with data in Excel, what is your first step?

Ensure that the entire data set is recognized by Excel.

When starting a new data project, what is one of the best sources to help you begin?

Existing reports -- Learning about what is currently being reported will help you understand the organization. Using existing reports, you can often reverse engineer the data to begin your own projects.

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.

A data worker will likely have more access to data and a stronger understanding of relational databases than a data analyst, data engineer, or data scientist. TRUE/FALSE

FALSE -- Data workers will have some access to data, but not nearly the same access as a data analyst, data engineer, or data scientist.

Mockups help communicate the navigation of information. TRUE/FALSE

FALSE -- Mockups demonstrate overall appearance, but a wire frame allows you to show the navigation between visuals.

RDBMS stands for relational database machine systems. TRUE/FALSE

FALSE -- RDBMS stands for relational database management system.

To be a data analyst, you must be able to manually calculate every single math calculation you need. TRUE/FALSE

False

What type of command do slicers perform in a Power BI Dashboard?

Filter -- Slicers are another way of filtering. They narrow the portion of the dataset that is shown in the other report visualizations. Slicers are a useful way to filter information and focus on a specific portion of the dataset.

Hard filter (dashboard)

Filtering at the data level. i.e. a company only includes 10 years of data instead of 100

What command do you use to create aggregated data sets in Power Query?

Group By -- Group By will allow you to group data and add aggregate functions like SUM, COUNT and AVERAGE.

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.

What does a cross join or no join accomplish?

It associates every record to every record in both tables. -- When multiple tables are brought into a query, they must have a common field that links them together through a join. When this join line is missing, it will tie every record from one table to every record in another table.

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. -- You can see what products sold at any time last year, and then from that information you can determine what products did not sell.

Semi-structured Data

Mix of structured data and unstructured data. Example: a picture of a cat on a beach

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.

When working with data that requires transformations, which option provides you with the most robust options in Excel?

Power Query -- Leveraging Power Query allows you to transform your data into structures that are powerful and usable for data analysis.

What should you do with every new data set before you begin to analyze the data?

Profile the data -- Use data profiling to learn about data at a high level. Profiling helps determine what steps you should work through on the transformation of your data.

Unstructured Data

Requires our brain to review and provide context

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.

Which statements are required in a basic SQL query?

SELECT and FROM -- SELECT lists all fields from the table, and FROM lists the table name.

Spaces can be used as delimiters. TRUE/FALSE

TRUE -- Spaces are invisible to the eye, but they are actual characters that can be used as delimiters.

_____ code is written by recording macros in Microsoft Office.

VBA (Visual Basic for Applications) -- VBA is the code language that runs Microsoft Office products. When you create a macro, it is coding in the VBA.

You notice that the numbers for your data project do not seem to match what the team expects. What is the first thing on your task list?

Verify your data set to ensure you have all necessary data and that you have not introduced an error. -- It is a best practice to first review your work to ensure that you did not introduce an coding error.

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.

Microsoft SQL Server

a relational database management system with the primary function of storing and receiving data

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.

Why are business rules important?

a. all of these answers b. they help define the data c. they impact the timing of the data d. they drive the necessary calculations a -- These are a few reasons why business rules are important.

A(n) _____ translates large amounts of data sets in many tables, and maybe across multiple systems, into data sets that others use for their reporting.

a. data engineer b. data worker c. administrative data professional d. business analyst a -- A data engineer is someone who fully understands how to look at data sets and refine them into smaller, more sensible sets for people to use. They may also receive data from someone who is engineering that set from queries and provide it to others.

Which role requires more advanced math, statistics, and coding skills?

a. data engineer b. data worker c. data analyst d. data scientist d -- Data scientists leverage all other roles. They will have the skills of the worker, analyst, and engineer.

Which role needs the most technical and working experience?

a. data scientist b. data engineer c. data analyst Data scientists typically have lots of real-world working experience. They are fluent in coding, math, and statistics, as well as all other required data roles, like data engineering, data cleaning, and reporting outcomes.

What is not an example of data soft skills?

a. data storytelling b. active listening c. cleaning a data set in Power BI d. programmatic thinking c -- Using a tool like Power Query is an example of a technical hard skill used by data professionals.

What type of information is best displayed in a paginated report?

a. stacked bar chart b. dashboard c. list of invoices for the year d. scatter plot c -- Paginated reports can provide valuable reporting when your data expands over many pages and can easily be published or printed.

When you are learning a new database, when should you look at a sample of the data?

after you look at the table names

You want to join records in tables that match with fields in other tables. To do this, which join will you use?

an inner join -- This type of join returns those records which have matching values in both tables.

Which option describes parsing text?

breaking parts of text out of a field to create fields -- You can do this with any type of delimiter and every program handles this a little differently, but the outcome is the same.

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.

Which option is not a reusable data set?

order transactions -- Order transactions will change and be an overall part of the data we report and visualize. However, it is not what we consider reusable, like a date table or postal code data.

You need a report for every day that occurred in a period of time. To determine if you had transactions on those days, which join would you use between your transactions table and date table?

outer join -- In order to pull every date from a range of dates you will tie your transactions table to a date table through an outer join.

Data governance involves _____.

people, technology, and processes -- The best data governance will include not only people, but technology and processes to ensure that data is the highest quality, secured and meeting requirements.

One of the key ways to validate your queries is by knowing how many _____ you have in each data set.

records -- It is beyond important to know how many records are in the tables you are working with. This is one of the simplest ways to validate your joins.

In data, numbers are _____ aligned by default.

right -- Numbers are typically best read when they are right aligned by their last value. In data, it's also an indicator that the program sees values as numbers because it will default to right aligned.

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.

Data Architect

senior visionaries who translate business requirements and define data standards and principles

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 -- When 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.

Which choice is not a valid data type?

start date -- Start date is a field name and is likely a date and time data type.

Knowing the statistical significance of any data point or set is an example of the _____ truth.

stats

Which truth is a measure of an organization's production?

the business truth

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.

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.

Power BI 2 main functions

trandorm data & present data

What are the two core functions that Power BI offers to data analysts?

transformation of data and presentation of data -- Power BI offers two core functions for data analysts: transforming and presenting data.

A PDF of an organization's annual report would be considered _____.

unstructured data -- Unstructured data does not fit neatly into tables/spreadsheets. Examples of unstructured data are videos, pictures, and PDFs.

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).

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.