1
Q
Create Database
A
CREATE DATABASE bob;
(To Verify) SHOW DATABASES;
USE bob;
SHOW TABLES;
(None)
CREATE TABLE Checks Check # NUMERIC(6) NOT NULL, Payee VARCHAR(20) NOT NULL, Amount DECIMAL(6,2) NOT NULL, Remarks VARCHAR(20) NOT NULL;
INSERT INTO Checks VALUES
(‘I’, ‘MA’, ‘Bell’, ‘150’, ‘Have sons next time’);
etc…
2
Q
Arithmetic Operators
A
(*), (/), (%), (+), (-) Order of Operations is multiplication, division, modulo, addition, and subtraction.
3
Q
Place a Condition on a Query
A
WHERE clause = more selective queries
- in order to find a particular item or group of items you need 1 or more conditions
- contained in the WHERE clause
Ex: name = ‘Brown’ or number of hours > 100 or where
4
Q
Expression
A
- an expression returns a value
- anything that follows a clause is usually an expression
ex: SELECT Name, Address, Phone, FROM Addressbook;
or
where name = ‘Brown’
(Boolean)
5
Q
How would you select different tables in a database?
A
select * from deposits
Note: no semicolon
6
Q
Plus (+)
A
ex: add to prices
SELECT Item, Wholesale, (Wholesale + 0.15) FROM price;
7
Q
How would you change order of columns?
A
SELECT payee, remarks, amount, check #, FROM checks;
8
Q
Dostinct
A
SELECT DISTINCT amount FROM checks;
or
SELECT DISTINCT, amount, payee FROM checks;
9
Q
Primary Keys
A
- column that uniquely identifies row
- must contain unique values
CREATE TABLE suppliers
( supplier_id int not null,
supplier_name char(50) not null,
contact_name char(50),
constraint suppliers_pk primary key
(supplier_id)
);
10
Q
Basic Query Syntax
A
- commands are not case sensitive
- spacing does not matter
- semicolon tells program the query is complete
- SQL’s magic element is “keywords”, used in clauses, to create SQL statements (FROM, SELECT, ORDER BY)
11
Q
Select
A
- does not work alone
- almost always followed by from (SELECT names FROM people;)
12
Q
Select *
A
SELECT ALL
- returns info in it’s own order
13
Q
Putting all the data into one place makes it useful to …
A
- Query the data
- Update the data
- Insert new data
- Delete old data
14
Q
What is a database?
A
A container made up of columns and rows to help organize data in a constructive way
15
Q
SQL stands for …
A
Structured Query Language
16
Q
Declarative Language
A
– statements are declared and the system executes those statements
17
Q
What tells the program the query is complete?
A
Semicolon ;
18
Q
Does spacing matter?
A
No
19
Q
Are commands case sensitive?
A
No
20
Q
Is data case sensitive?
A
Yes, it may
21
Q
How do you change the order of a column?
A
SELECT payee, remarks, amount, check # FROM checks; (whatever order you would like)
22
Q
Query Expression
A
- returns a value
- anything folllowing a clause (ie SELECT or FROM) is usually an expression
ex: SELECT amount FROM checks;
23
Q
Conditions
A
- contained in the WHERE clause
- in order to find a particular item or group of items in your database you need 1(+) condition(s)
24
Q
Types of Databases
A
Relational, object oriented, and document based are a few of the most common,
25
Table
- has a name and a collection of columns
26
Column
- has a name and a restriction that restricts the size and category of data that can be stored in that column
27
Required Column
- each row contains data for at least all the required columns
28
Row
- a single record in a table, retrieved by asking questions of the data
ex: “Who are all my contacts with the last name Smith?”
29
Key
- keys belong to tables
- each table should/must have one column that can uniquely identify a row (Primary key)
- keys are what create relations between Rows in Tables (relational databases require keys)
30
Foreign Key
- links the Table’s key to a different Table’s Primary Key
| - also allows rows of each Table to be linked together
31
Primary Key
- column that uniquely identifies row
- if more than one table uses the same primary key, you can merge the two tables
- more than likely you will want to merge a subset of each table
32
Tableau Server does what in a nutshell?
Frames the analytical process.
33
Data goes in a single section split by roles: (Tableau)
Dimensions & measures
34
A well designed Tableau dashboard should always be considered an...
interactive information-driven application. (IIDA)
35
Two important principles of visualization are...
Manage intricacy.
| Look for relevancy.
36
What is a perfect database?
Non-existent
37
Systems Thinking
‘The bathtub model’
SOURCE -> (input control ‘spigot’)
-> STOCK ‘tub’ -> (output control ‘drain’)
-> DUMP
38
Acquire Data (Tableau)
Key to answering questions/monitoring dashboards.
39
Filter Data (Tableau)
Filter down to only data related to question.
40
Enhance (Tableau)
Add in metrics, calculations, cost per customer.
41
Tune (Tableau)
Use aggregates(?) etc to make dashboard faster.
42
Key Points of Tableau
```
Aquire Data
Filter Data
Enhance
Tune
Deliver Dashboard or Visualizations
```
43
Key Differences between Tableau Desktop and Tableau Server
Desktop is used for visualizations and workbook creation.
Server is used for distribution of these reports, visualizations, or dashboards. Users can edit or update in (Server/online) but cannot create new dashboards.
44
Define Parameters (Tableau)
Dynamic variables/values that replace constant values in data calculations and filters
45
Boolean Expression
A logical statement that returns a boolean value i.e. either true or false. Can compare data of any type as long as both parts of the expression are of the same basic data type.
46
Differentiate between Parameters and Filters (Tableau)
Parameters are not filters but variables. Interactive, allows users to insert their own values. Do not operate alone and require to be a part of something such as an equation, a reference line, a calculated field, or parameter controls. Can be used in calculations for further analysis.
Filters are static or interactive (non-interactive by nature). Static filters cannot be edited from the dashboard or Tableau Public. Becomes interactive with a Quick Filter which adds a filter list to the worksheet and dashboard. Filters cannot be used in calculations.
47
Name the File Extensions in Tableau
```
Tableau Workbook (.twb)
Tableau Packaged Workbook (.twbx)
Tableau Datasource (.tds)
Tableau Packaged Datasource (.tdsx)
Tableau Data extract (.tde)
Tableau Bookmark (.tdm)
Tableau Map Source (.tms)
Tableau Preferences (.tps)
```
48
Tableau Products:
Server, Desktop, Public, and Reader.
49
Tableau Desktop
Desktop environment to create and publish standard and packaged workbooks.
50
Tableau Server
On-premise or cloud hosted software to access the workbooks built.
51
Tableau Reader
Local access to an open Tableau packaged workbook.
52
Tableau Public
Workbooks available publicly online for users to download and access the included data.
53
Can Parameters Have a Dropdown List? (Tableau)
Yes, parameters do have independent dropdown lists. This enables users to view the data entries available in the parameter during its creation.
54
How can you display the top five and last five sales in the same view? (Tableau)
Create two sets, one for top 5 and another for bottom 5. Then JOIN these two sets which will display a unique set of 10 total rows.
55
What is a TDE file? (Tableau)
TDE is a Tableau Desktop file, it refers to the file that contains data extracted from external sources like Excel or CSV files.
56
Two aspects that make TDE’s ideal for supporting analytics are? (Tableau)
1. Columnar store.
2. Architecture awareness, TDE’s use all parts of computer memory (RAM to hard disk) and puts each part to work at what best fits its characteristics.
57
How to use group in calculated field? (Tableau)
By adding the same calculation to the ‘GROUP BY’ clause in the SQL query or by creating a Calculated Field in the Data Window to use when you group the fields.
—
58
Fact Table (Tableau)
Facts are the numeric metrics (measurable quantities of the data) which can be analyzed by the dimension table.
Facts are stored in the Fact Table which contains foreign keys that uniquely refer to associated dimension tables.
‘This table supports data storage at the atomic level.’ It allows more records to be inserted at one time. For example it can have a product key, customer key, promotion key, all referring to a specific event.
59
Dimension Table (Tableau)
Dimensions are the descriptive attribute values for multiple dimensions of each attribute, defines multiple characteristics.
A dimension table can consist of product name, product type, size, color, description, etc. Has a product key from the ‘fact table’ for reference.
60
Quick Filters (Tableau)
A way to filter each worksheet on a dashboard until each of them contains a dimension. Useful for worksheets using the same data source which sometimes results in slow results.
61
What is the biggest Tableau infra you’ve been a part of? (Interview)
Single node for non-profit but varied user base.
62
How do you handle security for Tableau server? (Interview)
‘Defense in depth’; user rotation, auto password expiration.
63
How do you deal with raw, unstructured data? (Interview)
During the work with the non-profit a lot of it was free-form so we had to standardize around keys for ‘types’ of comments and concerns.
64
Where do you see the analytics industry moving? (Interview)
Fast, clean, and orchestrated.
65
How do you know Mike? (Interview)
We worked for a while together at a non-profit.
66
Limitations of Parameters (Tableau)
Parameters facilitate only four ways to represent data on a dashboard (seven in Quick Filters).
Parameters do not allow multiple selections in a filter.
67
Aggregation of Data (Tableau)
Way to develop a scatter plot to compare and measure data values. Calculated form of a set of values that returns a single numeric value. A measure with values 1, 3, 5, 7 returns 1.
Default aggregations can be set for any measure (they can’t be user-defined). Default aggregations consist of sum, avg, median, count, etc.
68
Disaggregation of Data (Tableau)
Way to develop a scatter plot to compare and measure data values. Refers to viewing each data source row while analyzing the data both independently and dependently.
69
Data Blending (Tableau)
Combining of data from different sources and platforms.
ex: Blending data from an Excel file with that of an Oracle database to create a new dataset.
70
What is a Context Filter? (Tableau)
Makes the process of filtering smooth and straightforward. Establishes a filtering hierarchy where all other filters refer to the context filter for their following operations. The other filters now process data that has been passed through the context filter.
Creating one or more context filters improves performance as users don’t need to create extra filters on a large data source. This reduces the query execution time.
71
What are the limitations of context filters? (Tableau)
Tableau places filters in context. When a filter is set as context one, the software creates a temporary table for that particular context filter. Consists of all values that are not filtered by either context or custom SQL filter.
72
What are the differences between .twb and .twbx? (Tableau)
.twb is the most common extension used in Tableau and presents an XML format file. Comprises all the information present in each dashboard and worksheet, for example styles and formatting applied to a worksheet and dashboard. This workbook does not contain any data. The packaged workbook merges the information in a Tableau workbook with local data available.
.twbx serves as a zip file, which will include any custom images. Allows users to share their work with other Tableau Desktop users and let them open it in Tableau Reader.
73
XML
eXtensive Markup Language.
74
What are Extracts? (Tableau Server)
Data extracts are the first copies or subdivisions of the actual data from an original source(s).
Workbooks using data extracts instead of using live database connections are faster since the extracted data is imported in Tableau Engine. After extraction users can publish the workbook which also publishes extracts in Server. (Workbook and extracts won’t refresh unless users apply a scheduled refresh on the extract.)
75
What are Scheduled Refreshes? (Tableau Server)
Scheduling tasks set for data extract refresh.
When publishing a workbook with data extracts, extracts get refreshed automatically. Removes task of republishing the workbook every time the data involved is updated.
76
Components of a Dashboard (Tableau)
Horizontal, Vertical, Text, Image Extract, Web [URL ACTION]
77
Horizontal (Components of Dashboard(Tableau))
Horizontal layout containers allow the designer to group worksheets and dashboard components left to right across the page as well as edit the height of all elements at once.
78
Vertical (Components of Dashboard(Tableau))
Vertical containers allow the user to group worksheets and dashboard components top to bottom down the page as well as edit the width of all elements at once.
79
Image Extract (Components of Dashboard(Tableau))
A Tableau workbook is in XML format. In order to extract images Tableau applies codes to extract an image which can be stored in XML.
80
Web [URL ACTION] (Components of Dashboard(Tableau))
An URL action is a hyperlink that points to a web page, file, or other web-based resource outside of Tableau.
You can substitute field values of a selection into the URL as parameters.
81
How do you view underlying queries in Tableau?
1. Create a performance recording which records the performance information about the main events you interact with. Users can view the performance metrics in a workbook. Help > Settings and Performance > Start Recording
2. Review the Tableau Desktop logs. C:\Users\\My Documents\My Tableau Repository
^For live connection to data source, check log.txt and tabprotosrv.txt
^For an extract check tdeserver.txt
82
Page Shelf (Tableau)
The page shelf fragments the view into a series of pages with a different view on each page. User friendly and minimizes scrolling to view data, you can flip through the pages using specified controls and compare them.
83
How do You do Performance Testing in Tableau?
Performance testing is done by testing Tableau Server with an application such as TabJolt which is a “point and run” load generator created to perform QA.
*QA
84
Explain the Concept of Dual Axis. (Tableau)
Allows users to view two scales of two measures in the same graph. Let’s you compare multiple measures s at once, having two independent axis layered on top of one another.
85
How many tables can you join in Tableau?
32 tables, table size is also limited to 255 columns (fields).
86
How to remove ‘All’ options from a Tableau auto-filter?
The auto-filter provides a feature of removing ‘All’ options by simply clicking the down arrow in the auto-filter heading. After scroll down to ‘Customize’ in the dropdown and then uncheck ‘Show “All” Value’.
87
How do you add custom color to Tableau?
Restart Tableau Desktop after you save .tps, from the Measures pane drag the color you want to ‘Color’. From the color legend menu arrow select Edit Colors in the dialog box and select the palette drop-down list to customize.
88
What is Tableau?
Tableau is a business intelligence software that allows anyone to connect to respective data, and then visualize and create interactive, shareable dashboards.
89
What are the supported datatypes in Tableau?
Boolean, Date, Date & Time, Geographical Values, Text/String, Number (Decimal), Number (Whole)
90
What is the maximum no. of rows Tableau can utilize at one time?
What is the maximum no. of rows Tableau can utilize at one time?
SQL w/Tableau Server
flashcards
Decks in class (1)
# Cards
