1 Reply Latest reply on Aug 24, 2017 11:52 AM by Amber Loranger

Dashboard data connection taking more time to edit which is using stored procedure

ravindra babu Aug 17, 2017 3:27 PM

Hi Everyone,

I have a issue raised by a user. user wants to edit the dashboard data connection which is using a stored procedure in My SQL server. it took more than 1 hr to load the data.

Note: we just created a dashboard. not published in server.

i had gone through below blogs

Re: stored procedure takes 11 minutes runs in a minute using raw sql

Using stored procedures and temp tables

SET NOCOUNT (Transact-SQL) | Microsoft Docs

https://www.experts-exchange.com/questions/28045667/Using-Tableau-With-SQL-Server-Stored-Procedures.html

Stored Procedures

i am attaching the create Query for your reference. please help out of this.

tableau version :10.2

USE [DW_PA]

/****** Object: StoredProcedure [dbo].[TableauHighwayMaster] Script Date: 8/17/2017 3:37:55 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- ================================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[TableauHighwayMaster]

-- Add the parameters for the stored procedure here

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

Truncate TABLE dbo.HighwayOverview

INSERT INTO dbo.HighwayOverview

Select distinct

b.Region

,c.HubName

,c.HubID

,CASE WHEN k.IncludeInCarrierDBFlag is null then 'N' else k.IncludeInCarrierDBFlag end as IncludeInCarrierDBFlag

, CASE WHEN a.HighwayServiceType in('LBMN','LDBS','LDBT') THEN 'Y' ELSE 'N' END as 'LoadBoardFlag'

,a.OrderNumber

,a.LoadNumber

,a.OrderStatus

,e.*

,j.ReportingCustomerNumber

,j.ReportingCustomerName

,j.CustomerNumber

,j.CustomerName

,a.LogisticsFlag

,a.OriginDispatchHub

,q.HubName as OriginDispatchHubName

,a.HighwayServiceType

,a.TransportationMode

,a.TransportationSubMode

,f.JobReferenceID

,a.CarrierSource

,a.Mileage

,d.PickupFromAppointmentDate

,d.PickupToAppointmentDate

,d.PickupFromRequestDate

,d.PickupToRequestDate

,d.ArrivePickupLocationDate

, Case WHEN a.OriginState IN ('PA','NJ','MD','CT','ME','MA','DE','NY','NH','RI','WV','VT') THEN 'NORTHEAST'

WHEN a.OriginState = 'OH' and a.OriginZoneNumberHighway <> 436 then 'NORTHEAST'

WHEN a.OriginState IN ('VA','NC','SC','GA','FL','AL','MS') THEN 'SOUTHEAST'

WHEN a.OriginState = 'TN' and a.OriginZoneNumberHighway <> 381 then 'SOUTHEAST'

WHEN j.OriginCountryCode = 'CAN' and a.OriginState <> 'ON' THEN 'SOUTHEAST'

WHEN a.OriginState = 'KY' and a.OriginZoneNumberHighway = 421 then 'SOUTHEAST'

WHEN a.OriginState IN ('CA','OR','WA','UT','AZ','ID','CO','NM','NV','WY','MT') THEN 'WEST'

WHEN a.OriginState IN ('IN','IL','MI','WI','MN','MO','IA','SD','ND') THEN 'CENTRAL NORTH'

WHEN a.OriginState = 'OH' and a.OriginZoneNumberHighway = 436 then 'CENTRAL NORTH'

WHEN a.OriginState = 'KY' and a.OriginZoneNumberHighway <> 421 then 'CENTRAL NORTH'

WHEN j.OriginCountryCode = 'CAN' and a.OriginState = 'ON' THEN 'CENTRAL NORTH'

WHEN a.OriginState IN ('NE','KS','OK','AR','LA','TX') THEN 'CENTRAL SOUTH'

WHEN a.OriginState = 'TN' and a.OriginZoneNumberHighway = 381 then 'CENTRAL SOUTH'

WHEN j.OriginCountryCode = 'MEX' and a.OriginState IN('BC','BS','BJ','SO') THEN 'WEST'

WHEN j.OriginCountryCode = 'MEX' and a.OriginState NOT IN('BC','BS','BJ','SO') THEN 'MEXICO'

ELSE 'ZNot Classified' end as 'OriginZone'

, Case WHEN a.DestinationState IN ('PA','NJ','MD','CT','ME','MA','DE','NY','NH','RI','WV','VT') THEN 'NORTHEAST'

WHEN a.DestinationState = 'OH' and a.DestinationZoneNumberHighway <> 436 then 'NORTHEAST'

WHEN a.DestinationState IN ('VA','NC','SC','GA','FL','AL','MS') THEN 'SOUTHEAST'

WHEN a.DestinationState = 'TN' and a.DestinationZoneNumberHighway <> 381 then 'SOUTHEAST'

WHEN j.DestinationCountryCode = 'CAN' and a.DestinationState <> 'ON' THEN 'SOUTHEAST'

WHEN a.DestinationState = 'KY' and a.DestinationZoneNumberHighway = 421 then 'SOUTHEAST'

WHEN a.DestinationState IN ('CA','OR','WA','UT','AZ','ID','CO','NM','NV','WY','MT') THEN 'WEST'

WHEN a.DestinationState IN ('IN','IL','MI','WI','MN','MO','IA','SD','ND') THEN 'CENTRAL NORTH'

WHEN a.DestinationState = 'OH' and a.DestinationZoneNumberHighway = 436 then 'CENTRAL NORTH'

WHEN a.DestinationState = 'KY' and a.DestinationZoneNumberHighway <> 421 then 'CENTRAL NORTH'

WHEN j.DestinationCountryCode = 'CAN' and a.DestinationState = 'ON' THEN 'CENTRAL NORTH'

WHEN a.DestinationState IN ('NE','KS','OK','AR','LA','TX') THEN 'CENTRAL SOUTH'

WHEN a.DestinationState = 'TN' and a.DestinationZoneNumberHighway = 381 then 'CENTRAL SOUTH'

WHEN j.DestinationCountryCode = 'MEX' and a.DestinationState IN('BC','BS','BJ','SO') THEN 'WEST'

WHEN j.DestinationCountryCode = 'MEX' and a.DestinationState NOT IN('BC','BS','BJ','SO') THEN 'MEXICO'

ELSE 'ZNot Classified' end as 'DestinationZone'

,CASE WHEN a.TransportationMode = 'IML' then 'INTERMODAL'

WHEN a.HighwayServiceType in('SPCS','SPCT','EMDR') AND a.LogisticsFlag =0 AND c.HubID not in(10,24) and j.ReportingCustomerNumber not in(15155,21113,26307,18490,3410) then 'PROJECT'

WHEN a.CustomerNumber = 1501 and a.OriginCity in('LANARK','MORTON') then 'PROJECT'

WHEN a.LogisticsFlag = 1 and a.OriginDispatchHub in(01,03,04,05,06,07,10,11,12,13,14,15,16,17,20,24,26,29,30,33,34,48) then 'UNYSON'

WHEN c.HubID =10 and a.CarrierSource <> 'DRAY' THEN 'WHITESTOWN'

WHEN (a.CarrierSource = 'DRAY' or a.TransportationSubMode in('HSF')) and a.LogisticsFlag = 0 then 'CORE'

WHEN c.HubID =10 and a.CarrierSource = 'DRAY' THEN 'CORE'

WHEN a.OriginDispatchHub =24 and j.ReportingCustomerNumber in(206,239,360,1088,1450,46159,359,374,35392) and a.TransportationMode = 'OTR' and e.YearNumber >= DatePart(Year,DateAdd(Year,-1,Current_TimeStamp)) THEN 'CAPACITY SOLUTIONS'

WHEN c.HubID =24 and e.YearNumber in(2015,2016,2017) THEN 'CAPACITY SOLUTIONS'

WHEN a.HighwayServiceType = 'HCSL' and a.OriginDispatchHub =24 and a.LogisticsFlag = 0 and e.StandardDate >= '09/01/2015' then 'CAPACITY SOLUTIONS'

ELSE 'CORE' end as 'BusinessCategory'

,CASE WHEN a.OriginZoneNumberHighway in(294,292,296,282,276,284) then 'CAROLINAS'

WHEN a.OriginZoneNumberHighway in(303) then 'ATLANTA'

WHEN a.OriginZoneNumberHighway in(61,21,11,32) then 'NEW ENGLAND'

WHEN a.OriginZoneNumberHighway in(197,212,89,181,171,191,152,200,122,112,142,148,165,146,132) then 'MID-ATLANTIC'

WHEN a.OriginZoneNumberHighway in(606,601,617,604,611,612,625) then 'CHICAGO'

WHEN a.OriginZoneNumberHighway in(384) then 'MEMPHIS'

WHEN a.OriginZoneNumberHighway in(752,780,770,761) then 'TEXAS'

WHEN a.OriginZoneNumberHighway in(900,917) then 'LA/ONT'

WHEN a.OriginZoneNumberHighway in(641,631) then 'STL/KC'

WHEN (a.OriginZoneNumberHighway in(952,941) OR j.OriginZipCode3 in ('955','960')) then 'NORTHERN CAL'

WHEN a.OriginZoneNumberHighway in(352,356,366,361) then 'ALABAMA'

WHEN a.OriginZoneNumberHighway in(477,462) then 'INDY'

WHEN a.OriginZoneNumberHighway in(235,232,240,226) then 'VIRGINIA'

WHEN a.OriginZoneNumberHighway in(452,441,432,436,482) then 'OHIO'

ELSE 'zOTHER' end as Market

,a.OrderUserID

,a.OrderSource

,h.ZoneName

,a.OriginCity

,a.OriginState

,j.OriginCountryCode

,j.OriginZipCode3

,a.OriginZoneNumberHighway

,m.ZoneNumber as 'OriginDATZone'

,m.ZoneDescription as 'OriginDATZoneDesc'

,a.DestinationCity

,a.DestinationState

,j.DestinationCountryCode

,j.DestinationZipCode3

,a.DestinationZoneNumberHighway

,n.ZoneNumber as 'DestinationDATZone'

,n.ZoneDescription as 'DestinationDATZoneDesc'

,f.DualModeLaneID

,j.OrderDate

,a.HighwayVendorNumber

,a.CommodityCode

,k.VendorName

,a.HighwayEquipmentCode

,i.HighwayEquipmentCodeDescription

,l.OriginDispatchDate

,a.PriorWeekYearNumber

,a.CurrentWeekYearNumber

,a.PriorWeekNumber

,o.VendorName as OriginDraymanVendorName

,p.VendorName as DestinationDraymanVendorName

,Cast(Null as Money) as TotalRevenue

,Cast(Null as Money) as LineHaulRevenue

,Cast(Null as Money) as FuelRevenue

,Cast(Null as Money) as EquipmentRevenue

,Cast(Null as Money) as NonEquipmentRevenue

,Cast(Null as Money) as OtherRevenue

,Cast(Null as Money) as TotalCost

,Cast(Null as Money) as LineHaulCost

,Cast(Null as Money) as FuelCost

,Cast(Null as Money) as EquipmentCost

,Cast(Null as Money) as NonEquipmentCost

,Cast(Null as Money) as OtherCost

,Cast(Null as Money) as TotalMargin

,Cast(Null as Money) as LineHaulMargin

,Cast(Null as Money) as FuelMargin

,Cast(Null as Money) as EquipmentMargin

,Cast(Null as Money) as NonEquipmentMargin

,Cast(Null as Money) as OtherMargin

,Cast(Null as nvarchar(1)) as PriorDayFlag

,Cast(Null as nvarchar(1)) as WTD

,Cast(Null as nvarchar(1)) as MTD

,Cast(Null as nvarchar(1)) as QTD

,Cast(Null as nvarchar(1)) as YTD

,Cast(Null as nvarchar(255)) as OTRIMLOffice

,Cast(Null as nvarchar(255)) as OTRIMLRegion

,Cast(Null as nvarchar(255)) as RCSub

,Cast(Null as nvarchar(255)) as HSFFlag

,Cast(Null as nvarchar(255)) as PricingRegion

,Case when df.ReasonCode in ('BD','BE','CR','UA') then 1 else otp.DeliveryOnTimeCount end as DeliveryOnTimeCount

From dw2.dbo.vwFactHeaderMasterV2Table a with (nolock)

left join dw2.dbo.vwDimRegion b with (nolock)

on a.DimRegionFunctionKey = b.DWKey

left join dw2.dbo.vwDimHub c with (nolock)

on a.SellingHub = c.HubID

left join dw2.dbo.OperationalSummary d with (nolock)

on a.OrderNumber = d.OrderNumber

left join dw2.dbo.dimDate e with (nolock)

on a.DSRDateNull = e.StandardDate

left join dw2.dbo.vwDimDocuments f with (nolock)

on a.OrderNumber = f.OrderNumber

left join dw2.dbo.DimRamp g with (nolock)

on a.DimRampRailOriginKey = g.DWKey

left join dw2.dbo.vwDimZonesHighway h with (nolock)

on a.OriginZoneNumberHighway = h.ZoneNumber

left join dw2.dbo.vwDimHighwayEquipmentCodes i with (nolock)

on a.HighwayEquipmentCode = i.HighwayEquipmentCode

left join dw2.dbo.OperationalSummary j with (nolock)

on a.OrderNumber = j.OrderNumber

left join dw2.dbo.vwDimVendor k with (nolock)

on a.HighwayVendorNumber = k.VendorNumber

left join dw2.dbo.vwDimOrderEventDates l with (nolock)

on a.OrderNumber = l.OrderNumber

left join (Select Distinct a.ZoneNumber, a.ZoneDescription, b.Zip3

from ODS_TPS_IU.dbo.ZNEZNEMP_BN a with (nolock)

left join ODS_TPS_IU.dbo.ZNEZIPTP_BN b with (nolock)

on a.ZoneNumber = b.ZoneNumber) m

on j.OriginZipCode3 = m.Zip3

left join (Select Distinct a.ZoneNumber, a.ZoneDescription, b.Zip3

from ODS_TPS_IU.dbo.ZNEZNEMP_BN a with (nolock)

left join ODS_TPS_IU.dbo.ZNEZIPTP_BN b with (nolock)

on a.ZoneNumber = b.ZoneNumber) n

on j.DestinationZipCode3 = n.Zip3

left join dw2.dbo.dimVendor o with (nolock)

on a.OriginDraymanVendorNumber = o.VendorNumber

left join dw2.dbo.dimVendor p with (nolock)

on a.DestinationDraymanVendorNumber = p.VendorNumber

left join dw2.dbo.dimHub q with (nolock)

on a.OriginDispatchHub = q.HubID

left join dw2.dbo.OTPMaster otp with (nolock)

on a.OrderNumber = otp.OrderNumber

left join dw2.dbo.vwdeliveryfailure df with (nolock)

on a.OrderNumber = df.OrderNumber

Where

a.TransportationMode = 'OTR'

and a.TransportationSubMode not in('PHM','IML','HDT')

and a.OrderStatus not in ( 'CNL')

and c.HubID not in ( '35','02','38','08','54','55','18' )

and a.OriginDispatchHub not in( '18')

and a.CustomerNumber NOT IN ( 94, 33976, 33977, 48839 )

and a.OrderNumber NOT IN ( 109081134, 109081154, 109081187, 113516389, 111627289, 111104365, 110943603, 111056691, 111047990, 111189538, 111174811, 111345038,

111335582, 111479086, 111365499, 111476008, 111631635, 111626090, 111785046, 111770524, 111791886, 111915326, 111908320, 112068606,

112060525, 112227441, 112208144, 112235404, 112400540, 112391788, 112537744, 112551154, 112716150, 112682011, 112682041, 112811238,

112811173, 114303404, 114696522, 116681251, 115377104, 115446054, 115446068, 116236557, 116663613, 116663613, 116663613, 115260820,

114661094, 115371648, 115595367, 115645078, 115645080, 115645090, 115646256, 115646261, 115646262, 115647299, 115725234, 116199046,

116293000, 116525134, 116822768, 116322583, 116490867, 115295297, 116261898, 116261903, 114931664, 115082318, 116143771, 116306202,

116452718, 115234599, 115234599, 122046809, 122046878, 122046921, 122046997, 122047041, 122047079, 122047153, 122047196, 122047464,

122047488, 122047569, 122047598, 122047713, 122048981, 122047102, 122047449 )

and

(a.LogisticsFlag = '0' or (a.LogisticsFlag = '1' and a.OriginDispatchHub IN ( '01','03','10','13','15','29','30','04','07','17','20','24','26','05','06','11','12','14','16','33','34','48','18' )))

and e.YearNumber >= DatePart(Year,DateAdd(Year,-1,Current_TimeStamp))

Update dbo.HighwayOverview

Set OTRIMLOffice = CASE WHEN ReportingCustomerNumber = 320 then '29'