Skip navigation
2015
Tableau kumar

Data Blending

Posted by Tableau kumar Mar 27, 2015

Data Blending

 

 

 

We use the Data Blending process when data is located into multiple databases. It is Horizontal merging it means Data bases are having different columns apart from common column for define the relationship.

 

Example:

 

The following example illustrates, how to use the two data sources to build the Report.

 

 

 

 

 

 

 

 

 

 

 

 




1) Connect the Tableau with "ABC" database, Then connect to Database "XYZA" by clicking on "Connect to database"

http://1.bp.blogspot.com/-d9ejRrUtSHs/VNw64MjULmI/AAAAAAAAAJ0/7R35F6rMO0k/s1600/7.png



 



2) Once we connected to database 2, we have to define the relationship between 2 databases. Here Column "A" is common to define the Relationship. 3) Click on Data on menu bar, Click on "Edit Relationship", Then Select Primary & secondary databases then define the relationship between those 2 databases.

 

Now we can use 2 databases to build  the reports.Once we define the Relationship between 2 databases, Primary Database will be highlighted inBlue, Secondary in Orange color.

 

 

 

General Issues:

If relationship will be removed, then we get the nulls when use columns.

from 2 databases.

 

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

General Issues - Data Blending


Solutions - for General Issue

Relationship

 

 

Define proper relation between two databases.

 

1) If we don't define the relationship for blending purpose, it will show "*" symbols.

 

104.png

 

2) If we define the Relation, that "*" will be removed.

105.png

 

 

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

 

 

If any calculation using Both Data sources in the Calculation, we have to use that both columns in the view hence we can hide if don't want to show.

 

Database 1 Data:

94.png

 

Database 2 Data:

 

95.png

 

After connected to Databases, I renamed as Database 1, Database 2.

 

Then i have created a calculation using two data sources fields.

 

91.png

 

If we use only 1 Column in the view even 2 are used to create calculation, it wont show the Data correctly since we are calling only 1 column from 1 DB onto View.

92.png

 

If we use columns from both data sources, it won't pass error since we are calling two fields from two DB.

 

93.png

 

Now we can find difference easily

 

89.png

 

===== ======

Some Additional Information.

Taking “X” from 1 data source, “C” from another data source. & I defined the relation  (Blending) using common column “A”.96.png

 

If I drag "X", "C" then it will pass error. IF i use "X", "A", "C" then it won't pass error.


Here were getting null values for  "Calculated" field since we are not using  referenced column of two both data sources.

 

97.png

 

 

Now there are null values  since we are using referential column in the report, that is "A".

 

98.png

 

99.png

 

 

If we want to hide "A", then right click on "A" then click on "Show Header".100.png

 

 

101.png

 

 

 

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

Data blending works like Left Outer Join (All records from Primary data source, only matched records from secondary data source.)

 

Lets take an example.

 

  Data source 1 ( I am using 2 different XL  workbooks)

CountrySales
C1100
C2200
C3300
C4400
C5500
C6600
C7700
C8800

 

 

Data source 2

 

 

CountryProfit
C8800
C9900
C101000
C111100
C121200
C131300
C141400
C151500

 

Country is the Common column to define the join between 2 database.

(Country is the Common record in terms of Country column)

1) I connected to the DB1.

68.png

 

2) Connected to DB2.

 

69.png

 

3) Now I defined DB1 as Primary, DB2 as Secondary.

70.png

4) If we drag Country from DB1, Country from DB2, DB1 Country shows all records & Only matched records from secondary  data source71.png.

5) If we drag Country from DB1, Country from DB2, DB2 (Primary) Country shows all records & Only matched records from DB1 (Secondary)  data source

 

72.png

 

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

Here we can't show all records from DB1, DB2 (Means all 15 Country's data at a time).

Means Full outer Join is not possible by doing the Data Blending.

 

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

 

If we want to show all 15 countries data on the view, we go for custom SQL.

 

 

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

Some times data in 2 databases may not having the same names ( Like USA in 1 Database , United States of America in another Database) , we have to edit the names & define the Aliases then it will match.

 

Procedure:

 

1.open a fresh workbook.

2.drag country to row shelf to get a list of countries.

3.right click on any country name and edit alias (at the bottom). change it

 

to match the target country name.

 

 

 

Example:

 

The problem is the countries are in different notations: One is in short form, the other is in long form.

 

Say, one is like this:

 

Country_Short
US
UK
CN
FR

 

The other is like:

 

Country
United States of America
United Kingdom
France
People's Republic of China

 

How to blend them? Are they blendable at all? The answer is affirmative, not without a little twist though. I am listing two ways of doing it. Assume both data sources are connected.

 

1. Create a calculated field [Country] based on [Country_Short]:

Using this newly created dimension [Country], you can blend with the other data source.

 

2. Add an alias to each country in the [Country] list of the second source.

 

- open a fresh worksheet
- drag [Country] to the row shelf
- right click on each country and click the last item of the context menu which is Edit Alias
- edit the alias of 'United States of America' to be US and so on
- make sure all the aliases are the corresponding names in the other table.


Either of the above lays the ground for blending the two data sources. We are ready to blend.

 

To blend naturally, rename the two country dimensions to be exactly the same. Tableau will try to link up the two dimensions automatically through intelligent guess.

 

If you want to keep the dimension names different and still want to blend the two tables,
- open menu Data>Edit Relationships
- link up the two dimensions there, using custom method to join the two data sources.

 

Note that for some data sources such as published data extracts, you are not allowed to edit aliases. Quite a weird restriction. The workaround is to duplicate the dimension in question, and work on the duplicate.

 

You may also right-click any dimension to edit the aliases for the whole filed.
In Tableau Desktop 8: Right-click Dimension>Default Properties>Aliases...
In Tableau Desktop 9: Right-click Dimension>Aliases...


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

Join VS Blending

 

 

Joining is the Better Approach than Blending when two tables located in the same Database & Same Schema, Since

1) If we implement Data Blending, Can't use the "Show Relevant Value option (Cascading Quick Filter),  If we use Join then it easier.

2) If we implement Data Blending, We can't apply the Global Filter when different reports from Different Databases, If we use Join then it easier.

3) Blending woks like Left Outer Join. Joining can support all kind joins (Inner, Left, Right,----)


4) Measure Names, Measure Values does not appear in the Second Database, So Joining is better in the approach.

5) We can't apply the Action from 1 report to another in the Dashboard when different report are built using different Databases.

 

 

We can add our customized Images to report.
Procedure:
Create a new Folder in the Shapes Folder, then add your shapes.
82.png83.png
then Reload the Shapes in the view.
84.png
Then Select the New folder which was created in previous steps.
85.png
Then you can assign the Shapes accordingly.
86.png
88.png
Now we can show New Shapes on the Report.
90.png

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.
23.png  24.png 25.png
1) All 3 Sheets are located in single XL Workbook.
2) Connect XL workbook (Sheet 1) to Tableau.
3) Then Extract the Data.
26.png        27.png
4) Connect to the Same XL workbook Sheet2.
29.png  30.png
5) Define the Relationship Between DB1 & DB2. Then Right click first DB Extract, click on "Append data from data source". Then refresh Extract
31.png     32.png
33.png
34.png36.png
6) Now we are having data fro 3 Countries (Which are located in 3 XL sheets in 1 Workbook.)

37.png

How to get the Latest Transaction date for each customer?

How to get the Latest Transaction date for each customer?
Procedure:

Here I'm creating 2 reports , 1 is to show Normal Report, another 1 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.
========================================
Another Example

Procedure: To get the Maximum Date
1) Create a calculation like i commented on the first image.
6.png
2) Drag [Case Number], [Status] on to Row shelf.
3) Drag "MAx" field onto Filters shelf, select "True".
7.png
4) Right click on "MAx" on Flters shelf, click on "Add table calculation".
8.png
9.png
Select "True"0.png
Now we will get your required result.
11.png
5.png
Procedure: To get the Minimum Date

Follow the Same steps like above apart from Calculation.
Do the Calculation like below

3.png

4.png

Final Result:

5.png
2.png
1.png

Dynamically Selecting Measure & Dimension values in the View.

 

Parameter: Parameters are variable, which are used to pass the values dynamically to the view.

 

An example of using Parameter is "Dynamically Selecting Measure & Dimension values in the View.

 

 

1) Create a Parameter for Dimension Selection purpose "Dimension Parameter", By using this Parameter, we can select the Dimension Value (Field) dynamically.
9.png
2) Create a Parameter for Measure Selection purpose "Measure Parameter", By using this Parameter, we can select the Measure Value (Field) dynamically.
10.png
3) Create a Calculated Field for Dimension Selection  using the syntax in the image.
1`4.png
4) Create another calculated field for Measure Selection like below image.
15.png
3) Place "Measure Selection", "Dimension Selection"  calculated fields, Right click on both Parameters & Click on "Show Parameter Control". (Note: We can  select Max 1 Value at a time from any parameter, It means Parameter limitation is Select Max 1 Value at a time.)
11.png
12.png

 


13.png

 

==============================
The following example explains when measures located on two databases.

 

Create a Parameter5.png
By using Parameter, create 2 Calculation for QS1, QS26.png
7.png
Then Place two measures on the Report like below image.Then you can define different marks for both measures.
121.png
When we select "QS1" from parameter,
122.png
When select "QS2",
1123.png
When Select "Both",
121.png

We can get it either Top 95% of total, ot Bottom 95% of total by using Sort option.

 

Top Cities upto 95% of total Sales1.png

 

 

Bottom Cities upto 95% or Less2.png

 

 

I used t he Following calculations for this task,

 

Running Sum (Field)

Syntax: RUNNING_SUM(sum([Sales] ))

 

Window Sum (Field)

window_sum(sum([Sales] ))

 

 

Upto .95  (Field)

Syntax:

if (RUNNING_SUM(sum([Sales] ))/ window_sum(sum([Sales] )))<=.95 then (RUNNING_SUM(sum([Sales] ))/ window_sum(sum([Sales] ))) end

 

 

 

you want to show list of cities upto 95 % of total, & hide remaining things, then create a calculated field (Boolean) then drop it into Filters then select "Yes". like the below screen shot.

 

Name: Show Cities upto 95 % of  Running total

Syntax: (RUNNING_SUM(sum([Sales] ))/ window_sum(sum([Sales] )))<=.95

 

11.png

 

22.png

 

33.png

Tableau kumar

Sheet Selection

Posted by Tableau kumar Mar 18, 2015

We use the Parameter to select the Sheet or Sheets dynamically on the Dashboard.

 

1) Create 3 Reports Individually.

a) Chart b) Line c) Text

 

 

2) Create a Parameter, here Values are defined as per our requirement.

 

3) Create a Calculated Field, which used to define the values which we can select from Parameter.

     

4) Drag "Sheet Selection" calculated field & define the values, if we select any one of these values then we will get this sheet on the Dashboard.

Here, values are All, Chart, Chart & Line, Chart & Text  for "Chart" sheet

5) Similarly, we can define the values for sheets "Text", Line"

 



6) Now add all 3 reports to Dashboard.

7)

Now we can select the Sheets according to requirement.