Data Access & Manipulation with Tableau

From the desk of our Tableau expert: part 2

Chuck Hooper, one of our in-house Tableau gurus, answers the most commonly asked questions we get about Tableau. Chuck started Tableau’s Professional Services consulting division and was one of the few individuals Tableau has designated as a Tableau Zen Master. If you have questions, you’ll get the best answers here! Read his first post in which he answers questions about getting started.

Q: Can Tableau do text reports and are there any limitations?
A: Tableau can easily do text reports. It can effortlessly add totals and subtotals by rows/columns. The only limitation is that you may find it difficult to create reports of a very specific “non row / column” format. An example would be trying to put out a report to print as a bank check. That is not what Tableau does. Tableau is designed to analyze data.

Senturus Upcoming Events

Q: I’m a novice user – what can I do to keep from messing up my source data?
A: Initially, you’ll mess up and get fired. Just kidding! Tableau is safe, it does not write back into your data. You can do no harm to your source data.

Q: What data sources does Tableau connect to?
A: Tableau Professional connects to many data sources including: flat files, Excel, Access, statistical files (SAS, SPSS, R), SQL Server, MySQL, Oracle, Amazon Redshift, Amazon EMR, Amazon Aurora, Actian (Matrix & Vector), Aster, Cloudera Hadoop, DataStax, EXASolution, Google (Cloud SQL, BigQuery, Analytics), Hortonworks Hadoop Hive, Vertica, IBM BigInsights, DB2, Neteeza, MapR Hadoop Hive, MarkLogic, MSAS, PowerPivot, MonetDB, OData, Essbase, Greenplum, Postgress Salesforce, SAP HANA, Netweaver BW, Sybase ASE, Sybase IQ, Snowflake, Spark, plunk, Teradata, and ODBC. There is also a Web Data Connector. In addition, third-party connectors allow Tableau to connect to other data sources. For example, the Senturus Analytics Connector for Tableau from Senturus gives Tableau direct access to data in existing BI systems, such as IBM Cognos, thereby eliminating the multitude of redundant reports needed to feed Tableau.

Q: Does data being accessed by Tableau need to be in any special form or structure (Star Schema, third normal form)?
A: No. Tableau accesses data in its native format. That said, many times it is advisable to pull disparate data sources into a single data mart / data warehouse for performance and ease of use.

Q:  Can I sort my data and if so, are there any limitations?
A: There are many ways to sort your output views. You can set a default for how you want to sort, you can manually “drag & drop” to rearrange it, you have a number of single click options to sort, you can also create a calculated field and sort on it. Effectively, you can sort the data anyway you want to.

Q: How does Tableau allow me to limit the data being processed?
Ah, this is a big topic since Tableau provides many ways to limit what is pulled in and what is shown from the data that has been pulled in. A primary way is with Filters, which allow you to limit the data that is being pulled from the database. Filters can also be used to limit the data that gets included in any view.

Q: Can I join data from two different tables in one database and from different tables in different databases?
A: Yes. In the latest version, you can join multiple tables from the same database. Also, you can blend data from different databases. Blending is a way of matching or joining data from two very different data sources. For example, you can obtain history data in Oracle and next year’s target in Excel. The blending process is more limited than a SQL join in that it is always a left join. Tableau is working on the ability to join data across disparate databases. As of March 2016, it is believed that this capability will be in a Version 10.x release. To learn more about data blending, you can watch our on-demand webinar: Faster Self-Service Analytics.

Q: What is a Tableau extract? What is the benefit of using it and when should I use it?
A: When you connect to your database to start a process in Tableau Desktop, you might want to continue working without maintaining a connection to the database. Tableau gives you the ability to pull data out of (or extract) your connected-to database and store it locally in a proprietary Tableau format. The pulling process creates a Tableau extract file (.TDE extension) that can then be used for offline analytics. Once you create your views, it is a simple process to reconnect to the live database to make the data current. The actual extract dialog allows you to filter to a subset of data based on values in the database (i.e. only pull the last 24 months out of a five-year data set) or to pull a random subset of data (i.e. give me 5% of my billion row data set so I can build offline without pulling the entire data set to my local system). Also, extracts respond fast – that proprietary storage format is a column store database designed to get data out quickly. In addition, when data is extracted, the .TDE file can be included in a saved packaged workbook. If you have the free Tableau Reader, you can interact with the views supplied by the Tableau Desktop developer.

Q: Is there a metadata layer required before I can start analyzing data?
A: No, Tableau does not require a metadata layer. You can connect to any data source and start analyzing immediately. You do have the ability to create some basic metadata for each data source, if needed.

Q: Does Tableau support any statistical processes or functions?
A: Yes, Tableau has multiple ways of implementing statistical functions – which is too extensive of a topic for this blog post though. Many of the more than 140 calculation functions support statistical needs. Also, Tableau has a direct interface to the open source R statistical package. If you don’t have access to an R server, install R server free and follow the simple install process as described in this document I created.

Q: Does Tableau interface with any statistical packages (SPSS, SAS, R)?
A: Yes, Tableau can read data from any of those sources. In addition, Tableau gives you a direct interface into an R server. See the above question.

Q: Besides SUM, what levels of aggregation does Tableau support?
A: In addition to SUM(), Tableau supports the following levels of aggregation: MIN(), MAX(), AVG(), Count, Count distinct, and more. Tableau has a large list of supported functions that work against numbers, date, datetime, and character fields.

Q: I use a lot of dates in my analysis, how hard is it to set up date level hierarchies?
A: You don’t have to set any date hierarchies. Tableau already understands date hierarchy (Year->Quarter->Month->Day->Hour->Minute->Second). Tableau understands weeks as well. Also, if you have a fiscal calendar that starts in a month other than January, you can, with a couple mouse clicks, tell Tableau what your first month is. If you have a fiscal calendar with a non month-to-month structure (e.g. your physical year starts the second Tuesday in October) you should consider joining your date fields to a calendar table that has your structure defined.

Q: Does Tableau allow me to create calculated values?  If so, how complex can they be?
A: Yes, Tableau supports more than 140 calculation functions against numbers, dates, and text data. Calculations can be as complex as needed. Tableau supports calculations sent to the database and calculations that are done on the returned / displayed data. I recommend you choose the calculation in which the task is most efficiently done.

Q: I do a lot of zip code analysis, does Tableau facilitate maps at that level?
A: Yes, Tableau Desktop includes geographic mapping capability at the country, state / province, county, city, ZIP / postal code, Congressional district, CBSA / MSA markets, and area code levels. Most U.S. Census demographics are available.

Q: What is a Tableau parameter and how should I use it?
A: A parameter is a mechanism that allows you to input values (numbers, text, dates, times) to be used by the developer. Here are two simple examples:

  • Use a parameter to input a projected percent sales change for next year. You can show last year’s actual sales versus next year’s projected sales on the same chart. Multiple parameters can be used in creating the projected Sales$ using the Tableau supplied SuperStore sample data. For example, you might have three parameters allowing input for projected sales change by product category: technology, furniture, and office supplies.
  • Tableau filters default to connecting via an “AND.” If you want to connect two filtering options with an “OR,” you need to use a parameter. For example, you want to show data for all the states in the east region, plus California. If you filter on State and Region and select the East Region, there is no way to get California in the results. But, if you use a State parameter and a Region parameter, you can select California from the State parameter and east from the Region parameter. Then you filter the data on a calculated field: IF INCOMING STATE = PARAMETER STATE OR INCOMING REGION = PARAMETER REGION THEN “Show” ELSE “Don’t show” END….

Stay tuned for the next installment of Q&As when Chuck will address reports, dashboards and sharing data with Tableau. Read the first Tableau FAQ post with Chuck.

You may also be interested in our on-demand webinar, Tips for Tableau Beginners, to learn fundamental techniques and best practices.

Learn Tableau. Expert instructors. Advanced topics.

Connect with Senturus

Sign up to be notified about our upcoming events

Back to top