TDE fies - Extracts

Extracts are saved subsets of a data source that you can use to improve performance, upgrade your data to allow for more advanced capabilities, and analyze offline. You can create an extract by defining filters and limits that include the data you want in the extract. After you create an extract you can refresh it with data from the original data source. You can either fully refresh the data, replacing all of the extract contents; or you can increment the extract; which only adds rows that are new since the last refresh.

Extracts can:

• Improve performance. For file based data sources such as Excel or Access, a full extract takes advantage of the Tableau data engine. For large data sources, a filtered extract can limit the load on the server when you only need a subset of data.

• Add functionality to file based data sources, such as the ability to compute Count Distinct.

• It provides work offline facility to the developers.
• Extracts are saved with .tde extension.

Limitations:

Defining Relationships among the Tables

Defining relationships among the tables and applying filter.


a. Open the Tableau Software by double click on Tableau Software Icon.
b. Connect to the Data Base,
c. Drag required tables in to right panel like the below figure. In this Example Orders, People, Return tables are selected for reporting.
d. Tableau Engine defines the Relationships automatically. Although we can also define our own relationships.(IT IS EQUALENT TO V-LOOKUP INEXCEL)

e. And we can apply the filter before we connect to the work area.
Click on Filters which is located on top right side.
 f. Click on add, in the following example We are applying filter on field “ Market” & select “Central” & “West”.
Central” & “West” markets only available to the report since we applied Data source filters.
g. After apply the filter, click on go to work sheet.
h. Drag Market, Product fields onto row shelf, sales onto column shelf.


Modifying Data source filters:
1) Right click on Data source.

2) Now we can modify filter here as we want. In this example we are adding one more filter on “Product Line” column.

How to get the Latest Transaction date for each customer?

Here I'm creating 2 reports , one report is to show Normal Report, another one is to show Calculated Report. Hence adding those 2 reports to Dashboard, which will show Latest Order customer wise.

1st Report (Calculated Report)
1) Create a Calculation 
Name: Max Date
Syntax: window_max(Max([Order_Date]))

2) Add fields [Customer_Name], [Order_ID]  to Row Shelf, drag Calculated field into Text Label

2nd Report (Normal Report)

1) Create a Report using  [Customer_Name], [Order_ID], [Order_Date].

Create a dashboard by dragging the above reports onto Dashboard.

Difference between Parameter & Quickfilter

Definitions:

Quick Filters:
We use the Quick Filters to include or exclude the members from the report.

Parameters:
Parameters are dynamic variables that can be used as placeholders in formulas.
And we can replace constant value by dynamic value

Differences:

1) We can implement Quick Filters on "Rows" only, it means implement on "Records".
where
Parameters can be used on Rows or Columns.

2) We can select More than 1 value at a time
where
Parameter allow us to pass max 1 Value at a time.

3) Parameter values not get updates automatically
where
Quick filter will get update as database updates.

4) We can call Parameters into Calculations, where we can't call Quick Filters into calculations.

 

Quick Filters:

Quick Filters:

-> We use the Quick Filters to include or exclude the members from the report.
-> It offers more interaction with report, It means End user can select required data instantly.
If user don't want to see the data on the report, then they can deselect that specific member from Quick Filter.

EX:
1) Drag [Region], [Order Date], [Customer Name] fields into Rows shelf.
2) Drag the one of Measure fields from Measures window into Column Shelf.
3) Right click on  [Region] in the work area then click on Show "Quick Filter".



Changing the UI:

1) Single Value (List):  This option offers select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
2) Single Value Drop Down List:
 This option also offers select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
3) Single Value Slide Bar:
This option also offers select only 1 member at a time or select all members by selecting "All" radio button.
It does not allow selecting multiple members from the selection criteria.
4) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" check box.
5) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" check box.
Here List of the values shown in the drop down list.

6) Drop Down Multiple Values:
This option also offers select either at 1 or multiple members a time or select all members by selecting "All" check box.
Here List of the values shown in the drop down list.
7) Custom Value List:
This option also offers select either at 1 or multiple members a time or select all members by typing the characters.
It will search the members which are related to your typed word.
If type ST, it will show the data EAST, WEST then select which ever members want to show on the report.
It is quite similar to Contains filter.

What is show Relevant values in Tableau? (CASCADING FILTERS)

We use the show Relevant values to view associated values in quick filter, we can say it is CASCADING FILTER.

It means, State Quick Filter is showing Values as per values selected in "Region" quick filter.

How to build Dashboards in Tableau

Dashboard: General Definition:

A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored.

In Tableau, We use Tableau to place multiple Reports at single place as well as to the define relationship from report to others.

Creating Simple Dashboard:

1) Create a Report with Region, Sales fields and Name it as "Region Sales"

2) Create another Report with State, Sales Fields and Name it as "State Sales"

3) Create a Dashboard by click on Dashboard menu, then click on "Create New Dashboard"

4) Then Drag the "Region Sales", "State Sales" report into Dashboard.

5) Now Dashboard have 2 Reports.

Objects in Dashboard:

1)  Horizontal: it is used to place the organize the reports horizontally.

2) Vertical: it is used to place the organize the reports Vertically.

3) Image: We can add Image to the Dashboard.

EX: Drag the Image object to the work Area, then provide the path of the Image.

4) Webpage: It is used to add the Webpage to Dashboard.

EX: Drag the Webpage object to the work Area, then provide the URL.

5) Blank: It is used to  keep space between two Reports on the Dashboard.

6) Text: It is used to add the textual data to the Dashboard.

Action :

Actions,  which are used to define the interaction between reports.
Actions are used to define the Drill down in Dashboards.

Action Types:

 

 

1) Filter: Action Filters are used to define the Filter from 1 report to another, it means which ever we use in one report that related data will only show in other report.

EX: 

1) Create a Report with Region, Sales fields and Name it as "Region Sales"

2) Create another Report with State, Sales Fields and Name it as "State Sales"

3) Create a Dashboard by click on Dashboard menu, then click on "Create New Dashboard"

4) Then Drag the "Region Sales", "State Sales" report into Dashboard.

5) Now Dashboard have 2 Reports.

6) Click on "Dashboard" file menu then click on "Action" , then on "Filter"

7) Select "Region Sales" as Source Sheet, "State Sales" as Target Sheet.

8) Select Run on as "Select"

9) Click on "OK".

10) Click on any of the Region in   "Region Sales"  report, then we can see selected Region related Data in the 

"State Sales".

3) Highlight:

Highlight option is used to highlight the selected data related on other report.

EX: 1) Create a Report with Region, Sales fields and Name it as "Region Sales"

2) Create another Report with State, Sales Fields and Name it as "State Sales"

3) Create a Dashboard by click on Dashboard menu, then click on "Create New Dashboard"

4) Then Drag the "Region Sales", "State Sales" report into Dashboard.

5) Now Dashboard have 2 Reports.

6) Click on "Dashboard" file menu then click on "Action"  then click on "Highlight"

7) Select "Region Sales" as Source Sheet, "State Sales" as Target Sheet.

8) Select Run on as "Select".

9) Click on "OK".

10) Click on any of the Region in   "Region Sales"  report, then we can see selected Region related Data in the 

"State Sales" will be highlighted.

3) URL:

We use this kind of Actions to navigate from Report to any specific webpage.

1) Create a Report with Region, Sales fields and Name it as "Region Sales".

2) Click on "Dashboard" file menu then click on "Action"  then click on "URL".

3) Then Define "https://www.google.co.in/" in the URL, click on "OK".

4) click on any of the region in the report, it will take to the google page.

========================

Run Action On: 

We use "Run Action On" to define the User interaction with report. After define the Action type, we define Run Action On.

Run Action Types: 

1) Hover: We use this type to define the interaction from 1 report to another, in this approach action will be applied by keeping mouse hover on the Source Report.

2) Select: We use this type to define the interaction from 1 report to another, in this approach action will be applied when select (Click) any data in the Source Report.

3) Menu: We use this type to define the interaction from 1 report to another, in this approach action will be applied when click on data then click on menu bar in the Source Report. 

Creating Dashboards:

Navigating with in the Same Dashboard

1) Create a Report with Region, Sales fields and Name it as "Region Sales"

2) Create another Report with State, Sales Fields and Name it as "State Sales"

3) Create a Dashboard by click on Dashboard on menu bar, then click on "Create New Dashboard", Right click on Dashboard at footer level then Rename as " Region, State Sales Dashboard".

4) Then Drag the "Region Sales", "State Sales" report into Dashboard.

5) Now Dashboard have 2 Reports.

6) Click on "Dashboard" menu bar  then click on "Action" , then on "Filter"

7) Select "Region Sales" as Source Sheet, "State Sales" as Target Sheet.

8) Select Run on as "Select"

9) Click on "OK".

10) Click on any of the Region in   "Region Sales"  report, then we can see selected Region related Data in the 

"State Sales".

Navigating from one Dashboard to another.

1) Create a Report with Region, Sales fields and Name it as "Region Sales"

2) Create another Report with State, Sales Fields and Name it as "State Sales"

3) Create a Dashboard by click on Dashboard on menu bar, then click on "Create New Dashboard", Right click on Dashboard at footer level then Rename as " Region Sales Dashboard". Then Drag "Region Sales" report into work area.

4) Create a Dashboard by click on Dashboard on menu bar, then click on "Create New Dashboard", Right click on Dashboard at footer level then Rename as " State Sales Dashboard". Then Drag "State Sales" report into work area.

5) Now there are 2 Dashboards .

6) Go to  "Region Sales Dashboard" menu bar  then click on "Action" , then on "Filter"

7) Select "Region Sales" as Source Sheet from Region Sales Dashboard, "State Sales" as Target Sheet from .State Sales Dashboard.

8) Select Run on as "Select"

9) Click on "OK".

10) Click on any of the Region in   "Region Sales"  report, then we can see selected Region related Data in the 

"State Sales".

Note:

1) We can define the Size of the view in the Dashboard by selecting the Sizes which are located in Left bottom side.

2) When we drag the Reports into Dashboard, it occupies total space of the Dashboard, if you want to define the size then select "Float" option then we can define the Size of the Report in the Dashboard.

Float: it is used move report from 1 place to another place within the Dashboard and can define the custom size.

Tiled: It is fixed size, occupies maximum size in the Dashboard, If we place 2 reports in Dashboard, that 2 reports occupies total space of the Dashboard.

3) Text Object: We use if only for add the comments or to show any textual data along with Report in the Dashboard.

How to place more than 6 Columns in a view.

Generally Tableau allow us to place up to 6 Columns, if we want to place more than 6 columns in view,
Analysis(Menu Bar) -> Table Layout -> Advanced

 

 

Note: Tableau allow us to place max 16 columns in a view.

 

File Extension Types in TABLEAU

Workbooks (.twb) – Tableau workbook files have the .twb file extension and are marked with the workbook icon. Workbooks hold one or more worksheets and dashboards.


• Bookmarks(.tbm) – Tableau bookmark files have the .tbm file extension and are marked with the bookmark icon. Bookmarks contain a single worksheet and are an easy way to quickly share your work.


• Packaged Workbooks (.twbx) – Tableau packaged workbooks have the .twbx file extension and are marked with the packaged workbook icon. Packaged workbooks contain a workbook along with any supporting local file data sources and background images. This format is the best way to package your work for sharing with others who don’t have access to the data.


• Data Extract Files (.tde) – Tableau data extract files have the .tde file extension and are marked with the extract icon. Extract files are a local copy of a subset or entire data source that you can use to share data, work offline, and improve database performance.


• Data Connection Files(.tds) – Tableau data connection files have the .tds file extension and are marked with the data connection icon. Data connection files are shortcuts for quickly connecting to data sources that you use often.

Measure Types - Continuous vs. Discrete

In addition to dimensions and measures, each field is categorized as either discrete or continuous. Below are example graphs illustrating the difference between these two data roles. Both examples show the Sum of Margin as a function of Inventory level. It is the same information presented in two different ways.

Discrete Each inventory value is drawn as a header. The Inventory field is colored blue on the Column shelf.

Continuous Each inventory value is drawn along a continuous axis. The Inventory field is colored green on the Column shelf.

Whether a field is continuous or discrete is reflected in the color of the field’s data type icon. In the Data window, blue icons indicate discrete and green icons indicate continuous fields.

Discrete fields always result in headers being drawn whenever they are placed on the row or columns shelves. Continuous fields always result in axes when you add them to the view. These roles are important because you may want to display your data continuously or discretely depending on what you are trying see and the data itself. You can switch between continuous and discrete data roles.

What is the Difference between Groups & Sets?

Groups are used to create higher level category by using the lower level category members.

EX:
Customer wants to see "Territory", which is combination of "Region" members.

Sets:
Sets- are custom fields that define a subset of data based on some conditions. Computed sets update as data is being changed.

-> Alternatively, a set can be based on specific data point in your view.
Sets are sub set of data, which are derived from either only Dimension or combination
of Dimension & Measures.

Difference B/W Groups & Sets:

1) Groups are created Manually, we cant use them in  any Calculated fields.
Sets are created either using manual or calculated fields and can use in calculated Fields.

2) We can't use GROUPS in Calculated fields, but can use SETS.

3) We use sets for Analysis
EX:  Set 1  Union  Set2

Sets

Sets - are custom fields that define a subset of data based on some
conditions. Computed sets update as data is being changed.
-> Alternatively, a set can be based on specific data point in your view.
Sets are sub set of data, which are derived from either only Dimension or combination
of Dimension & Measures.
EX:
1) Right click on Region, cick on "set".
2) Click on condition  "By Field", Select the measure, define condition.(Sales>=10000)
3) Click on "OK".
4) Set has been created, it shows the list of customers whose sales are more than 10K.
EX: Combine sets
1) We can use the sets for set analysis.
Set 1 = Top 3 Customers
Set 2 = Bottom 3 Customers
2) Combination of Top 3, Bottom 3
Set 1 Union Set 2
3) Right click on Set 1 then click on "Combine Sets"
then select option "Union"
Additional Points:
1) We can use Parameters instead of Top 3 as Top N.
And We can use Parameters instead of 10k.
2) We use sets for set Analysis like Union, Intersect, Minus.

Dual Axis:

Dual Axis:
Showing 1 Measure values on top on another Measure Values, it means override the Measure Values.
Main Usage is comparing two KPI's for any Dimension.

EX: BUZ User Wants to see Sales, Profits of Regions in the Single Report.

1) Drag the Regions on to Rows shelf, Sales & Profit fields onto Columns Shelf.
2) To differentiate measures, apply different colors for both Measures.
3) Right click on Second measure in the work Area, then click on "Dual Axis".
4) Now Report will show two measure values in single pane.
Have to Keep 1st Max Value KPI then Less Value KPI.

Synchronize Axis:
To follow the Same Scale for both Measures.

How can show the Data when data is located 3 different sheets in the single XL Workbook - Data Appending

Data Appending:

 

How can show the Data when data is located 3 different sheets in the single XL Workbook

Procedure:

Below are the XL sheets, where having data for 3 Countries in 3 Sheets.

   

1) All 3 Sheets are located in single XL Workbook.

2) Connect XL workbook (Sheet 1) to Tableau.

3) Then Extract the Data.

        

4) Connect to the Same XL workbook Sheet2.

  

5) Define the Relationship Between DB1 & DB2. Then Right click first DB Extract, click on "Append data from data source". Then refresh Extract

     

6) Now we are having data fro 3 Countries (Which are located in 3 XL sheets in 1 Workbook.)

 

=========================

 

Data Appending: Record Level Merging

(Data Appending's Principle: Metadata should be same in all Databases & Sequence of Fields)

 

DB1

CNT State Sales

A A1 100

A A2 200

A A3 300

DB2

CNT State Sales

B B1 100

B B2 200

B B3 300

After Implement the Data Appending:

CNT State Sales

A A1 100

A A2 200

A A3 300

B B1 100

B B2 200

B B3 300

 

==============================

Data Appending is not Possible since Sequence of the Columns are not same in 2 Databases.

DB2

CNT State Sales

B B1 100

B B2 200

B B3 300

DB1

State Sales CNT

A1 100 A

A2 200 A

A3 300 A

==============================

Data Appending is not Possible since  Column names are not same in 2 Databases.

DB2

CNT State Sales

B B1 100

B B2 200

B B3 300

DB1
CNT             States         Sales

A A1 1000

A A2 2000

A A3 3000

 

==============================

Data Appending is not Possible since  Metadata is not same in 2 Databases.

DB1

CNT(Char 20) State(Char 20) Sales(Integer 12)

B B1 100

B B2 200

B B3 300

DB2

CNT(VarChar 20) States(Char 20) Sales(Integer 12)

A A1 1000

A A2 2000

A A3 3000

==============================

Data Appending is POSSIBLE since  Metadata, Sequence of the Fields are same in 2 Databases.

CNT(VarChar 20) State(Char 20) Sales(Integer 12)

B B1 100

B B2 200

B B3 300

CNT(VarChar 20) States(Char 20) Sales(Integer 12)

A A1 1000

A A2 2000

A A3 3000

After Implement the Data Appending:

CNT State Sales

A                  A1                100

A                  A2                 200

A A3 300

B B1 100

B B2 200

B B3 300

Data Appending Principle: Metadata, Sequence of the Columns should be same.

Meta Data: Column Name, Data Type, Size)