Deck 4: Business Intelligence Bifor Data Analysis

Question 1

A foreign key resides in the "one" side of a one-to-many relationship whereas a primary key resides in the "many" side of a one-to many relationship.

Answer 1

False

Question 2

The query language,Structured Query Language (SQL)enables queries to be carried out,but is only relevant for some relational data base management systems.

Answer 2

False

Question 3

One way to create a unique identifier for every element in a data table is to use an existing unique identifier,such as a social security number.

Answer 3

True

Question 4

Two things you can do in the Power Pivot window is add calculated columns and add measures using DAX formulas.

Question 5

A linking table is a table used to transform a many-to-many relationship between tables into two one-to-many relationships; usually composed mostly of foreign keys.

Question 6

The initial step of correcting problems in a data set before you do any serious analysis is called cleansing data.

Question 7

Microsoft Access is an example of a relational database management system.

Question 8

A foreign key is not a unique identifier.

Question 9

Primary and foreign keys relate information between tables making it possible to look up information about all orders made by a specific customer for a specific product.

Question 10

It is reasonable to assume that the data you obtain are clean.

Question 11

Excel Data Model is a recent Microsoft technology used for data visualization,all within Excel.

Question 12

Power Pivot is needed to create a Data Model.

Question 13

Tableau Public is a power Excel add-in that provides many data visualization options.

Question 14

The feature that sets Power Pivot apart from regular Excel pivot tables is the relatively new language,Structured Query Language (SQL)for performing calculations.

Question 15

Using Tableau Public you can combine charts into a dashboard or a story.

Question 16

Many software companies offer BI software.What does BI stand for?

A)Business intelligence
B)Building interest
C)Brilliant illustrations
D)Bivariate interpretations

Question 17

Microsoft refers to the role of add-ins such as Power Query and Power Pivot as self-service BI because then people who need to analyze data for their jobs do not need to rely on IT departments,but can perform the analysis themselves.

Question 18

Data visualization

A)is the study of making graphical displays more simplistic by removing labels and scales.
B)shows data graphically only using traditional graphs like histograms,scatterplots,and bar graphs.
C)shows data graphically in new and imaginative ways.
D)is the study of organizing data into linked tables using spreadsheets.

Question 19

A field in a database table that serves as a unique identifier is called an autonumber key.

Question 20

Business intelligence refers to the insights gained from data analysis.

Question 21

A Data Model is a _____ that is stored in Excel.

A)relational database
B)flat file
C)single-table database
D)power query

Question 22

You can try to import data from a Web page into Excel using the From Web button.Using Power Query,_____ Web queries will succeed.

A)no
B)some
C)all
D)half of all

Question 23

A popular software package for creating imaginative types of visualization techniques is

A)Power Display.
B)Dashboard Story.
C)Data Cleansing.
D)Tableau Public.

Question 24

Which of the following is not an important strength of Power Query relative to the previous Excel importing tools?

A)It lets you choose the type of graphical display that best suits the data.
B)You can import data from more sources than before.
C)Once you locate data of interest,you can use the new Query Editor to "shape" the data to be imported.
D)Your queries are stored in step-by-step fashion so that they can be modified and reused later.

Question 25

Which of the following is the feature that sets Power Pivot apart from regular Excel pivot tables?

A)Power Pivot Alchemy
B)Data Model language
C)DAX language
D)SQL

Question 26

It is best to store the data just once and the _____ is often the better choice.

A)worksheet
B)Data Model
C)single-table database
D)flat file

Question 27

In a many-to-many relationship the linking table will have at least how many foreign keys?

A)2
B)3
C)4
D)5

Question 28

The pivot table functionality that has been part of Excel for decades is great,but has its limits.With _____ and _____,these limits are expanded significantly.

A)Power Pivot,its DAX language
B)Data Models,SQL
C)data analysis,data visualization
D)power queries,summary measures

Question 29

A _____ is a new column in a table of a Data Model.

A)DAX
B)calculated column
C)measure
D)formula

Question 30

Which of the following is true of a relational database with several related tables?

A)A primary key value such as 16 in one table can be associated with several records in a related table,each with the foreign key value of 16.
B)A foreign key value such as 16 in one table can be associated with several records in a related table,each with the primary key value of 16.
C)If one table has a primary key value of 16,then another table must have multiple records with foreign key value 16.
D)If one table has a primary key value of 16,then only one other table can have records with foreign key value 16.

Question 31

In a database,the number that provides a unique identifier to a record within the table is called the _____ key.

A)foreign
B)primary
C)unique
D)personal

Question 32

A hospital has a database for which all patient visits are logged.For each visit,the hospital records information such as the patient's name,age,address,reason for the visit,the date,the doctor's diagnosis,etc.The relationship between the patient table and the visits table is called a _____ relationship.

A)many-to-one
B)one-to-one
C)many-to-many
D)one-to-many

Question 33

Suppose a Products table has a ProductID primary key,and a related Sales table has a ProductID foreign key.Which of the following would cause a concern?

A)The Sales table has several records with ProductID 79,and the Products table has exactly one record with ProductID 79.
B)The Products table has exactly one record with ProductID 43,and the Sales table has no records with ProductID 43.
C)The Sales table has several records with ProductID 61,and the Products table has no records with ProductID 61.
D)The Products table has exactly one record with ProductID 87,and the Sales table has only one record with ProductID 87.

Question 34

A query specifies

A)the graphical display of the data that has been imported.
B)the structure of the data you want to import.
C)how much data you want to import.
D)exactly what data you want to import.

Question 35

Most Data Models will include a separate Dates table with the purpose of

A)enhancing the readability of the Data Model.
B)streamlining the query process.
C)listing all possible dates in many possible ways.
D)storing the data in separate worksheets for easy access.

Question 36

The whole purpose of separating data into separate tables in a relational database is to

A)provide the data in a format that can be analyzed using Excel.
B)enable the data to be cleansed.
C)simplify the process of importing data.
D)avoid redundancy.

Question 37

Which is true of an autonumber key?

A)It can be used,with consecutive integer values,when no other primary key is available.
B)If a "natural" primary key,such as product code,is available,it is a better option than an autonumber primary key; it will lead to more accurate query results.
C)It is best used as a primary key in conjunction with another primary key,such as a Social Security number.
D)It has often been used in Access database tables,but it shouldn't be used in an Excel Data Model.

Question 38

The flexibility of the measures created by _____ is the real "power" in Power Pivot.

A)the row and column fields
B)the Data Model
C)DAX
D)the RELATED function

Question 39

When you run a query to import external data into Excel,there are several options as to where to store the data.Which of the following is not one of those options?

A)The data can be stored into an Excel table.
B)The data can be stored into a pivot table (or pivot chart)report.
C)The data can be stored as a Data Model.
D)The data can be stored as a Power Model.

Question 40

There are four basic steps for all data imports.Which of the following gives these steps in the correct order?

A)(1)select the type of data source; (2)choose the tables of interest in the Navigator window; (3)use the Query Editor to shape the query; and (4)use a Close & Load To option to import the data into Excel.
B)(1)choose the tables of interest in the Navigator window; (2)use the Query Editor to shape the query; (3)select the type of data source; and (4)use a Close & Load To option to import the data into Excel.
C)(1)choose the tables of interest in the Navigator window; (2)use a Close & Load To option to import the data into Excel; (3)select the type of data source; and (4)use the Query Editor to shape the query.
D)(1)select the type of data source; (2)use the Query Editor to shape the query; (3)choose the tables of interest in the Navigator window; and (4)use a Close & Load To option to import the data into Excel.

Question 41

Suppose you try to create a pivot table from multiple tables stored in a Data Model and,when you check a particular field to be placed in the pivot table,you get a warning about a missing relationship.Which of the following does this not imply?

A)You are trying to use fields from unrelatable tables,so no matter what you do,your pivot table results will be wrong.
B)You can open the Power Pivot window,create the missing relationships,and try building the pivot table again.
C)You can click the Auto-Detect button in the PivotTable Fields pane,and the chances are that Excel will correctly create the missing relationship.
D)You can click the CREATE button in the PivotTable Fields pane,which allows you to manually create the missing relationship.

Question 42

Which of the following is true of a measure in Power Pivot?

A)A measure can be used to fill a column in a table of a Data Model.
B)A measure is always a summarization by summing.
C)Measures are basically the same as calculated fields,which have been available for Excel pivot tables for many years,so they don't add much new functionality.
D)Measures created by DAX are always listed in the PivotTable Fields pane,so that they can be used just like any other fields in the Values area of a pivot table.

Question 43

Within the Power Pivot window,you can choose to hide individual columns or even entire tables.Why might you want to do this?

A)It prevents users from adding fields to pivot tables that would certainly lead to errors.
B)It creates less confusion for users,so that they won't be tempted to use fields in pivot tables that wouldn't lead to interesting results.
C)It ensures that fields unrelated to other fields won't be used in pivot tables.
D)Its only use is to hide primary and foreign key fields so that they won't be used in pivot tables.

Question 44

Which of the following is not a many-to-many relationship?

A)Between authors and single-authored books.
B)Between the products a company stocks and the orders from the company's customers.
C)Between college students and sections of college courses.
D)Between the products a company stocks and the vendors who supply the products to the company.

Question 45

When there is a many-to-many relationship,it is common to have a linking table.A good example is between musicians and record albums,where any musician can record multiple albums,and any record album can be created by multiple musicians.In this case,what would be true of the linking table?

A)It would be composed entirely of the primary keys MusicianID and AlbumID,with no other fields allowed.
B)It would be composed entirely of the foreign keys MusicianID and AlbumID,with no other fields allowed.
C)If the Musicians table has a primary MusicianID value of 21,the linking table would have at least one record with a foreign MusicianID value of 21.
D)It would contain the foreign keys MusicianID and AlbumID and possibly other fields.

Question 46

Which of the following languages are used to create queries?

A)SQL and DAX
B)SQL,M,and DAX
C)M and DAX
D)SQL and M

Question 47

Which of the following is true of Power Query?

A)It has a Navigator window where you can edit the data to be imported.
B)It allows you to import data from Microsoft Access,but only if you have first created a saved query in Access.
C)It has an Editor window where you can "shape" the data you want to import,but about the only thing this Editor window lets you do is delete unnecessary rows and/or columns.
D)In its final Import Data dialog box,it's not a good idea to select "Table" and check the "Add this data to the Data Model" because this would unnecessarily create two versions of the data and increase the size of the Excel file.

Question 48

Which of the following most accurately explains Excel's Data Model and Power Pivot (in Excel 2016 or later versions)?

A)Excel's Data Model and Power Pivot are synonyms for the same technology: for creating pivot tables based on large databases.
B)The Data Model and Power Pivot are both Excel add-ins.You must check each of them in the add-ins list before you can use them.
C)The Data Model is used to store and relate tables in Excel,and Power Pivot provides a user interface for using the Data Model to create pivot tables.
D)A Data Model requires VLOOKUP functions to relate table data,and then Power Pivot can be used to create pivot tables from the related data.

Question 49

Which of the following is not true of the Power Pivot user interface?

A)It allows you to create relationships between tables.
B)It allows you to edit the data in any of the tables,just as you would in an Excel worksheet.
C)It allows you to use DAX formulas to create calculated columns in a table.
D)It allows you to use DAX formulas to create measures that can be used in the Values area of a pivot table.

Question 50

Which is true of an Excel Data Model?

A)It must consist of at least two related tables.
B)The data do not need to be stored in one or more Excel worksheets.
C)The data must first be imported into Excel from an RDBMS,either Access or SQL Server.
D)The data will always appear in two places: in one or more Excel worksheets and in the Power Pivot window.

Question 51

Which of the following is true of a measure in Power Pivot technology?

A)A measure is the same thing as a calculated column.
B)A measure is a summarization of any single column in a Data Model table.
C)A measure is a summarization of data in one or more tables of a Data Model,created with a DAX formula,to be used in the Values area of a pivot table.
D)A measure is a formula for summarizing data,to be used in the Values area of a pivot table,and it can be based on the usual built-in Excel functions; no DAX functions are necessary.

Question 52

Which of the following is not true of a hierarchy in an Excel Data Model?

A)Only one hierarchy per table is allowed.For example,it wouldn't be possible to create the hierarchy Continent,Country,Region,City and the hierarchy Continent,Country,State/Province,City,Zip based on a Geography table.
B)A hierarchy must be created in the Power Pivot window.
C)A hierarchy is often aided by DAX's RELATED function,so that fields from related tables can be brought into a single table and used in building the hierarchy.
D)A hierarchy's main use is to allow users to drill down through the hierarchy in a pivot table.

Question 53

The DAX function RELATED is used for which of the following?

A)It is used to indicate that two tables in an Excel Data Model are related.
B)It is used to bring a field from a related table into the current table as a measure.
C)It is used to bring a field from a related table into the current table as a calculated column.
D)It is used to create a relationship between two tables.

Question 54

Which of the following is true?

A)You can create a pivot table from the data in an Excel Data Model,but you must first ensure that Power Pivot is loaded.
B)You can create a pivot table from the data in an Excel Data Model even if Power Pivot is not loaded.
C)If your data are stored in an Excel Data Model,not in one or more worksheets,you can view the data regardless of whether Power Pivot is loaded.
D)Assuming that you want to build a pivot table and Power Pivot has not been loaded,your data should be stored in an Excel Data Model and in one or more worksheets.

Question 55

Which of the following is true of the DAX language?

A)If Revenue and Cost are fields in the Sales table of a Data Model,the DAX expression Profit:=Sales[Revenue]-Sales[Cost] can be used to create a new calculated column called Profit.
B)If Revenue and Cost are fields in the Sales table of a Data Model,the DAX expression Profit:=Sales[Revenue]-Sales[Cost] can be used to create a new measure called Profit.
C)It is easy to learn and master the DAX language because its functions are practically the same as Excel's built-in functions.
D)A measure created by DAX does not need to be a summarization.

Question 56

Which of the following is not a primary advantage of Microsoft's self-service BI tools?

A)They allow ordinary Excel users to perform powerful data analysis without needing help from a corporate IT department.
B)The tools let you analyze the data in Excel.However,you must first create a relational database in a database package such as Access.
C)To analyze large relatable tables in Excel,the usual practice of employing multiple VLOOKUP functions is no longer required.
D)These tools allow you to analyze data sets in Excel that go well beyond Excel's approximately one million row limit.

Question 57

In terms of importing data from a website,which of the following is not true?

A)Excel has had a Web query tool since at least version 2010,but this option has been greatly improved in Power Query.
B)If you can see data of interest on a website,Power Query will always enable you to import the data into Excel.
C)Some websites have what look like tables of data,obvious candidates for importing into Excel.But even the new Web query tools in Power Query are not guaranteed to be able to import such data; it depends on how the website was created.
D)Once you find a URL with interesting data and you use Power Query to import the data,the steps to do so (navigate,edit,load)are basically the same as those for importing data from any other (non-Web)source.

Question 58

Which of the following is true of Tableau Public?

A)Tableau Public is totally separate from Excel,although Excel data can be imported into Tableau Public.
B)You can publish your own Tableau Public visualizations to the Web,but only if you're willing to pay for an account.
C)The data for a Tableau Public visualization must be based on a single source table of data.
D)Tableau Public can be used as a standalone software program,independently of Excel,or it can be used as an Excel add-in.

Question 59

Which of the following is true of Power Pivot?

A)If you don't have a Data Model in your Excel file,you won't see any data in the Power Pivot window.
B)The data you see in the Power Pivot window can be a mix of data in the Data Model and data not in the Data Model.
C)The tables you see in the Power Pivot window have necessarily been related through their primary and foreign keys; otherwise,the tables wouldn't appear in the Power Pivot window.
D)The only way to relate tables that have not yet been related is through the Power Pivot window; Excel has no buttons on it ribbons to create relationships.

Question 60

Which of the following is true of Power Query?

A)Power Query is just another name for the querying tools Excel has had for over two decades.
B)Power Query is an add-in,so it must first be checked in the Excel add-ins list.
C)Even with Power Query,a user must be good at the SQL querying language to import data from relational databases.
D)Power Query remembers the steps of your queries so that you can use or easily modify these queries for later uses.

Question 61

Tableau Public refers to worksheets (or simply sheets).Which of the following is true?

A)Each sheet contains data from a particular data table.
B)Each sheet contains a geographic map.
C)Each sheet contains a single chart.
D)Each sheet contains a single dashboard.

Question 62

Which of the following is not true about data manipulation within Tableau Public?

A)You can hide columns that you don't intend to use for any visualizations.
B)Each column is automatically assigned a data type,and you are not allowed to change these.
C)You can create a union of several tables such as Salary2017 and Salary2018 that have the same columns,essentially stacking the data from the tables on top of one another.
D)You can relate (that is,join)tables through primary and key fields; Tableau Public is usually smart enough to detect the relationships.