Tableau with Sql Server Example 1

1. Suppose there are 10 types of defects allocated to different products and the database is built like this example below. Product 12244 has defect type 5,6,8,9 and 10 etc..

2. If we plot this data directly in tableau, generating dashboards will be a bit difficult. In order to visualize the data in tableau easily, we need to arrange this data through a custom query.
3. Write the query in SSMS like below:
SELECT 
ProductID, 
Product_Name,
'Defect_Type1' as Type,
Defect_Type1 as Defect_Desc
from dbo.Defects

4. This will pull all the records of the first defect "Defect_Type1". Similarly, copy this code above and paste after this code with Union All and change the 2nd defect to "Defect_Type2", see below:

SELECT 
ProductID, 
Product_Name,
'Defect_Type1' as Type,
Defect_Type1 as Defect_Desc
from dbo.Defects
UNION ALL
SELECT 
ProductID, 
Product_Name,
'Defect_Type2' as Type,
Defect_Type2 as Defect_Desc
from dbo.Defects

5. Continue the same with other defects:

SELECT ProductID, Product_Name,'Defect_Type1' as Type, Defect_Type1 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type2 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type3 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type4 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type5 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type6 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type7 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type8 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type9 as Defect_Desc from dbo.Defects
UNION ALL
SELECT ProductID, Product_Name,'Defect_Type2' as Type, Defect_Type10 as Defect_Desc from dbo.Defects

6. This above code need to be used in Tableau
7. Open Tableau and connect the SQL Server database instance
8. Click on "New Custom SQL" under Data Source.

9. A dialog box will appear. Paste the SQL query, as below and click Ok:

10. It will look like this

11. Now prepare your dashboard as per your requirement.