Join tables for Tableau

Question

Join tables for Tableau

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I needed to create an extraction for a Tableau report, where my team developed the database and the web system, and another company developed the Tableau report.

This is the schema of the problem

To feed the tableau report I developed the solution below, but I do not know if this was the best solution

DECLARE @Pillars table (Id int , Name varchar(30), ExhibitionOrder tinyint)
DECLARE @Initiatives table (Id int identity(100,2) , PillarId int,  Name varchar(35), ExhibitionOrder tinyint) 
DECLARE @Goals table (Id int identity(10,30) , PillarId int, Descriptions varchar(60), ExhibitionOrder tinyint) 

INSERT @Pillars (Id, Name, ExhibitionOrder) values 
 (1, 'Communication',1)
,(2, 'Environmental Responsibility',2)


INSERT @Initiatives (PillarId, Name, ExhibitionOrder) Values
(1,'Social Network',2)
,(1,'Television',1)
,(1,'Brand Content',3)
,(2,'Financial education',1)
,(2,'Your garbage, Your responsibility',0)

INSERT @Goals (PillarId, Descriptions , ExhibitionOrder) Values
 (1, 'Improves the number of direct contact on call center in 10%',2)
,(1, 'Reduces paper media in 2%',1)
,(2, 'Reduces in 35% the recycle garbage in the office',1)
,(2, 'Reduces in 2% the carbon footprint on the office',1)
,(2, 'Reduced 2% Hydro bill',1)


;WITH 
lv0(n) AS (SELECT 0 FROM   (VALUES (0), (0))G(n)), --2 
lv1(n) AS (SELECT 0 FROM   lv0 a CROSS JOIN lv0 b), -- 4 
lv2(n) AS (SELECT 0 FROM   lv1 a CROSS JOIN lv1 b), -- 16 
lv3(n) AS (SELECT 0 FROM   lv2 a CROSS JOIN lv2 b), -- 256 
lv4(n) AS (SELECT 0 FROM   lv3 a CROSS JOIN lv3 b), -- 65,536 
--lv5(N) as (select 0 from lv4  a cross join lv4 b), -- 4,294,967,296 
tally(n) AS (SELECT Row_number() OVER( ORDER BY (SELECT NULL)) FROM   lv4), 
t1 AS (SELECT Row_number() OVER( ORDER BY id ) N ,* FROM   @Pillars), 
t2 AS (SELECT Row_number() OVER( ORDER BY id ) N ,* FROM   @Initiatives), 
t3 AS (SELECT Row_number() OVER( ORDER BY id ) N ,* FROM   @Goals) 
SELECT  A.Name Pilar 
       ,b.Name Initiative 
       ,c.Descriptions Goals
       ,a.id 
       ,b.PillarId 
       ,c.PillarId 
FROM   tally t 
       CROSS JOIN t1 a 
       LEFT JOIN t2 b 
              ON t.n = b.n 
                 AND a.id = b.PillarId 
       LEFT JOIN t3 c 
              ON t.n = C.n 
                 AND a.id = c.PillarId 
WHERE  a.id IS NOT NULL 
       AND ( b.id IS NOT NULL 
              OR c.id IS NOT NULL ) 
ORDER  BY  IsNUll(a.ExhibitionOrder,255)
          ,IsNUll(b.ExhibitionOrder,255) 
          ,IsNUll(c.ExhibitionOrder,255)

            SELECT 
                    A.Name Pilar 
                   ,b.Name Initiative 
                   ,c.Descriptions Goals
                   ,a.id 
                   ,b.PillarId 
                   ,c.PillarId                 
            FROM
                @Pillars A
            INNER JOIN @Initiatives B
                ON
                A.Id = B.PillarId
            INNER JOIN @Goals C
                ON  
                A.Id = B.PillarId
            ORDER  BY  IsNUll(a.ExhibitionOrder,255)
                      ,IsNUll(b.ExhibitionOrder,255) 
                      ,IsNUll(c.ExhibitionOrder,255)

Result

Pilar                          Initiative                          Goals                                                        id          PillarId    PillarId
------------------------------ ----------------------------------- ------------------------------------------------------------ ----------- ----------- -----------
Communication                  Television                          Reduces paper media in 2%                                    1           1           1
Communication                  Social Network                      Improves the number of direct contact on call center in 10%  1           1           1
Communication                  Brand Content                       NULL                                                         1           1           NULL
Environmental Responsibility   Your garbage, Your responsibility   Reduced 2% Hydro bill                                        2           2           2
Environmental Responsibility   Financial education                 Reduces in 2% the carbon footprint on the office             2           2           2
Environmental Responsibility   NULL                                Reduces in 35% the recycle garbage in the office             2           NULL        2

(6 row(s) affected)

sql-server tableau

asked by anonymous 30.09.2017 / 04:47

2 answers

1

For the 3 tables there is the PillarId link field. That is, a hierarchy in 3 levels. But the connection field between the 3 levels is the same. Then the result will be this same, many rows, because the result will be to return all the records of table 3 for each PillarId of table 2 for each ID of table 1. In this case, for id 1 of table 1 there are 3 records in the table 2 and 2 records in table 3, this will result in 2 records (table 3) for each record in table 2.

If this does not happen, there needs to be a other link field between Tables 2 and 3.

02.10.2017 / 15:57

Change server CRON function through PHP Run-time error in pthreads

score 0 · Accepted Answer

It seems to me that the query can be assembled by using FULL OUTER JOIN type join between the Initiatives and Goals tables, adding pillar sequencing to each of these two tables.

-- código #1 v4
with 
cte_I as (
SELECT *, 
       Seq= row_number() over (partition by PillarId 
                               order by coalesce(ExhibitionOrder, Id))
  from @Initiatives
),  
cte_G as (          
SELECT *, 
       Seq= row_number() over (partition by PillarId 
                               order by coalesce(ExhibitionOrder, Id))
  from @Goals
)
SELECT coalesce(I.PillarId, G.PillarId) as PillarId,
       P.Name as Pilar,
       I.Name as Iniciativa, 
       G.Descriptions as Objetivo
  from cte_I as I 
       full outer join cte_G as G on I.PillarId = G.PillarId
                                     and I.Seq = G.Seq
       inner join @Pillars as P on P.Id = coalesce(I.PillarId, G.PillarId)
  order by P.ExhibitionOrder, coalesce(I.Seq, 255), coalesce(G.Seq, 255);

Pillar sequencing is reliable in cases where the ExhibitionOrder column is either informed for all rows or for no rows on the same pillar. But if for a given PillarId value there is ExhibitionOrder information on only part of the rows, pillar sequencing may (or may not) generate unexpected order.