Data source order issue in Tableau

Recently while creating one of the Tableau Dashboards I met with an issue where the column values order in tableau was not similar to our data source order.

As soon as data was coming to tableau it was changing the sort order to ascending or descending. when I did some research about the issue in tableau forums, I came to know that you cannot just keep the same order from your data source.

Therefore I came up with the workaround which anyone can apply if they came across with similar issue.

Below I will first Re-Produce the issue using the below data source.

Student	Marks
RM	2
NB	4
PA	4
CM	4
CS	4
GI	4
PS	3
CA	3
RT	4
RA	4
HR	3
ERM	4
DG	3
GBA	3

If you will notice in the above table RM is the first Student so I was expecting the same order my Graph.Now I am using above table as my Tableau data source to create a bar chart report on this as below.

After plotting the Student and Marks in the plot I found that the data order has been changed from its original data source sort order, If you will see in the bar chart CA is coming first then CM then CS then DG whereas our actual sort order was RM, NB, PA and so on.

Based on the above it was understood that after connecting to Data source the we were loosing the actual sort order in Tableau Report

To fix this issue one way is to Right click on Columns where we have placed our student dimension click on Sort and manually sort the order as below. This will work perfectly fine without any issue.

But think of my scenario where i was having multiple sheets like this and was having multiple of column values in it. So, if we will go with above solution like going to each sheet and apply the manual sorting and again and you have around 100 plus values in your column then it will be a time consuming hectic task.

So below is the best way to retain the original Data source order in your tableau Report.

You can add a Rank or Row Number column in your data source as below

Student	Marks	Rank
RM	2	1
NB	4	2
PS	4	3
CM	4	4
CS	4	5
GI	4	6
PS	3	7
CA	3	8
RT	4	9
RA	4	10
HR	3	11
ERM	4	12
DG	3	13
GBA	3	14

Now refresh your data source and just click on sort and in sort by select Field and Select Rank from there click apply and you will notice that your bar graph will have similar order as of your data source.

Now you have any no. of sheets or any no. of values in your column just you have to add a new column with Rank and sort the data based on this Rank.

Converting Weekday dates to Weekend dates in SQL

I was recently asked by one of my FB Group user that “Is there any way to convert week day to that week end Saturday for example- 1 Jan 2016 is Friday so it should come as 2 Jan 2016 i.e. Saturday, other example like 1 Feb 2016 is Monday so it should come as 6 Feb 2016 i.e. Saturday” likewise his table was having multiple dates which has to be converted to that week Saturday.

Below is the test table

Create table Test(ID  int IDENTITY(1,1), StartDate Date)

Insert into Test VALUES ('1-1-2016'),('2-2-2016'),('10-10-2016'),('2-10-2016'),('1-13-2016')

SELECT *,DATENAME(WEEKDAY,STARTDATE) as [DayofWeek] FROM Test

If you will run the below query you will see the Result as below I have used datename function to extract the weekday from StartDate Column.

Now as per the user Requirement the Start Date Columns dates has to be converted to its respective weekend i.e. Saturday

Below is the code

Select *,Dateadd(day,(7- DATEPART(dw,startdate)),StartDate)as EndofWeek

from Test

--The above query has given us the date as required,

--In order to verify that all EndofWeek extracted should be saturday Just run the below code

Select ID,StartDate,DATENAME(WEEKDAY,STARTDATE) as Startdateday, Dateadd(day,(7- DATEPART(dw,startdate)),StartDate)as EndofWeek ,Datename(DW,Dateadd(day,(7- DATEPART(dw,startdate)),StartDate))as EndofWeekDay

from Test

Custom logging in SSIS

Integration Services includes logging features that write log entries when run-time events occur but in cases when you want to capture thing which you can’t do with SSIS Logging for eg ( how many records inserted, updated, and deleted; when it was done; what was the source; who ran the package; what machine did it run on, etc). In that case you can do your own custom logging

Below I will demonstrate with a simple example that how to enable custom logging in SSIS.

First Create Below tables in your database

--Source Table

CREATE TABLE [dbo].[Claim](

       [ClaimSID] [int] NOT NULL,

       [ClaimName] varchar(100)

       )

Insert into Claim values (1,'A'),(2,'B'),(3,'C'),(4,'D')

-- Create similar destination table with below code

select top 0 * into ##claimtest from Claim

--Log Table Code

create table ##Test( packagename varchar(100), startdate datetime, Errordescription varchar(8000),enddate datetime ,taskname varchar(100))

Now Open SSIS and create a basis package with one dataflow and one execute task as below Screenshot and name the package as Eventhandler.

Now in Data flow connect the above source table Claim and connect it to destination and map destination table (##Claimtest)

Next in sql Execute task add the below query that will generates an error, so that we can capture the same in our log table which is ##Test

update ##claimtest

set cliamsid=10

where claimsid=-1

IF you will notice that the actual column name is claimsid and in the update statement I am using cliamSID which is incorrect as there is a difference in spelling, so this will throw an error which we will capture in our custom log table(##Test) along with the package name and other fields.

To enable logging on error event go to event handler window and in event handler drop down select on error. This will captures error messages of the packages.

Drop an sql execute task on that place

And add the below code

insert into ##Test values(?,?,?,Null,?)

Click on Parameter mapping and do a mapping as in below screenshot.

Next again in event handler drop down  select on Postexecute. This will captures the post execute event like when package started and completed.

Drop an sql execute task on that place

insert into ##Test values(?,?,Null,getdate(),?)

Click on Parameter mapping and do a mapping as in below screenshot.

So the logic is if there is an error in that case end date will be blank and in case of no error the enddate will be there with no values in error columns.

Now Run the package and it will track all the details in log tables which is ##Test in our case.

Select * from ##Test

Interview Questions on SSIS

This post will be helpful to all the SSIS Developers who are looking for change or wanted to build their carrier in SQL Server Integration Services.

Below interview questions will help you out to crack interviews in SQL Server Integration Services irrespective of how much experience you have

1. What is Control flow and Data flow in SSIS?
2. SSIS Architecture
3. Current SSIS Project your working on, can you create a diagram showing control flow of your SSIS package?

4. Best ways to optimize your SSIS Package?
5. Difference between Merge and Union ALL transformation?
6. What are dimension and facts, How you are loading dimension and facts in your DW?
7. How you are performing Incremental load in your database?
8. What is SCD and it types, which SCD Type you are applying in your DW?
9. Can you call temp tables in your SSIS Package? If yes then how?
10. What's the most complex thing which you have faced in SSIS?
11. Different type of errors in SSIS, Error Handling in SSIS?
12. Break points in SSIS?
13. Check points in SSIS?
14. Event handler and its usage in SSIS?
15. How you are deploying your package in SSIS?
16. Project Deployment in SSIS? 
17. Blocking, Semi blocking and non-blocking transformations in SSIS?
18. SCD transformation brief explanation?
19. Term extraction transformation in SSIS?
20. Isolation properties. Default Isolation property in SSIS?
21. Derived column with an example?
22. Type of Enumerator in for each with an example?
23. Difference between For and for each in SSIS?
24. Character map transformation and its usage?
25. Transactions in SSIS?
26. How to schedule a SSIS package on daily basis?
27. Type of configuration and its usage in SSIS?
28. Environmental variable in SSIS?

29. How to load multiple sheets in a single destination and then moving it to Achieve folder?
30. Calling child package in Parent Package, how to use Parent package variable in child package?

31. Difference between lookup and merge join transformation?

32. When you have used script task in your SSIS Package?

There are lots of other questions also that an Interviewer can ask these are just the common question in most of the companies.Hope these questions will help you out.All the best. 

Note: For Interview Question on SQL Server and SSRS Read my Previous Posts:

SQL- http://www.sqlandssrssolutions.com/2016/06/interview-questions-on-sql-server.html

SSRS- http://www.sqlandssrssolutions.com/2016/07/interview-questions-on-ssrs.html

Interview Questions on SSRS

This post will be helpful to all the SSRS Developers who are looking for change or wanted to build their carrier in SQL Server Reporting Services.

Below interview questions will help you out to crack interviews in SQL Server Reporting Services irrespective of how much experience you have

1. What is the difference between SSRS 2005, SSRS 2008,2012, 2014 & SSRS 2016? 
2. What is Sub Report, How to Pass Main Report Parameter to sub Report? 
3. What is Dashboard, how to create Dashboard in SSRS ? 
4. Explain what is the difference between Tabular and Matrix report? 
5. What is the difference between Drill Down and Drill through Report? 
6. How many ways you can deploy a Report in Report Server? 
7. What is difference between Report Server in Native Mode vs Report Server in Share point mode? 
8. What is Report Server db and Report Server temp DB? 
9. What is the difference between IIF and Switch condition? 
10. How to apply Alternate Colour on Rows in SSRS? 
11. Can we write custom code in SSRS ? IF yes then why and where we can write? 
12. What is Linked Report and its usage? 
13. What is the difference between Report Server and Report Manager? 
14. Difference between Query Parameter and Report Parameter? 
15. What is the use of list in SSRS with an example ? 
16. What is caching and Snapshot in SSRS, what is the difference between them? 
17. What are subscriptions and Type of subscriptions? 
18. How to find longest running Reports ? 
19. How to debug Reports which are taking lots of time to Preview? 
20. What is cascading of Parameters in SSRS? 
21. Does changing the Parameter order will affect the Report ? 
22. Report builder and it’s usage ? 
23. Difference type of charts in SSRS, Can we plot secondary Y axis in the chart? 
24. What are the different types of data sources in SSRS? 
25. What is document map in SSRS?

There are lots of other questions also that an Interviewer can ask these are just the common question in most of the companies.Hope these questions will help you out.All the best. 

Note: For Interview Question on SQL Server Read my Previous Post- http://www.sqlandssrssolutions.com/2016/06/interview-questions-on-sql-server.html

Interview Questions on SQL Server

This post will be helpful to all the SQL Server Developers who are looking for change or wanted to build their carrier in SQL Server.

Below interview questions will help you out to crack interviews in SQL Server irrespective of how much experience you have

1. Difference between Clustered Index and Non Clustered Index?
2. What is Column Store Index?
3. Difference between Primary, foreign and unique key?
4. Difference between Function, SP and view?
5. What are Joins, Explain all type of joins with an example?
6. Difference between CTE, Temp Table and Table variable?
7. Difference between Delete, Drop and Truncate?
8. Best ways to optimise your Stored Procedure?
9. Why to use SP when you can directly write the SQL Query?
10. What is Trigger, where you have used Trigger?
11. What is Cursor, did you ever used Cursor, why to avoid cursor, Alternate of Cursor?
12. Best optimization techniques and Performance tuning in SQL?
13. What is covered Index and Filtered Index?
14. What is Normalisation, Type of Normalisation, Your Project DB is in which normalised form and why?
15. What is Data ware house?
16. What is Data Mart?
17. Difference between Database, Data ware house and Data Mart?
18. What are facts and dimension?
19.  Difference between SQL 2005, 2008 R2, 2012, 2014 and 2016?
20. Ranking Functions in SQL with difference between Row-number,Rank and Dense Rank ?
21. Pivoting and UnPivoting of Data in SQL?
22. Difference type of Isolation's level in SQL and their use?
23. What is deadlock,blocking and dirty Read ?
24. Different type of System database and their usage?
25. Different type of locks in SQL ?
26. What is materialised view with an example?
27. What is the difference between Union and Union All, which is good for optimization?
28. What are transactions, How you are doing transactions in your's sp ?
29. What are ACID Properties with an example of each property ?
30. Common SQL queries

• Delete Duplicates
• Max Salary Department Wise
• 2^nd Highest Salary
• Recursive CTE Code
• Update Gender Column Male to Female and Female to Male

There are lots of other questions also that an Interviewer can ask these are just the common question in most of the companies.Hope these questions will help you out.All the best.

Lead and Lag Function in SQL

I often heard in various forums about the use of lead and Lag Function, since most of the developers are still working on 2008 R2, so they are not aware of these two Lead and Lag functions.Below I will explain the Lead & Lag Function with a simple example

SQL Server 2012 introduces new analytical function LEAD() and LAG(). These functions accesses data from a subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join.

First I will explain what exactly Lead and Lag is with an example

Below is a list of 5 records from A to E

A, B, C, D and E

 As per Lead definition- “Lead Accesses data from a subsequent row in the same result set without the use of a self-join”

So in our example Since A is the first value so B is leading A Similarly C is leading B Next D is leading C and E is leading D and since E is the last Record and doesn’t have any subsequent Row so the Lead Value of E is NULL as shown in below table structure

Name   LeadValue

A          B

B          C

C          D

D          E

E          NULL

Now using the same example I will show the Lag values

As per LAG definition- “Accesses data from a previous row in the same result set without the use of a self-join”

So in our example Since A is the first value so A doesn’t have any lag value so it will have LAG Value as NULL. Now Since A is coming before B so B has a lag value as A Similarly C has lag values as B, next D has a Lag value which is C, and E has lag value which is D as shown in below table structure

Name   LagValue

A          NULL

B          A

C          B

D          C

E          D

Now, I will use the above example to use Lead and Lag Function in SQL Server

--Test table Script with above example

create table #t(id int identity(1,1), name varchar(100))

insert into #t values('A'),('B'),('C'),('D'),('E')

--Using Lead & Lag Function to Get the Leading & Lagging value

SELECT *,

LEAD(Name) OVER (ORDER BY ID) LeadValue,

LAG (Name) OVER (ORDER BY ID) LagValue

FROM #t

This is just a basic and very simple example of Lead and Lag function, in real time scenario’s there will be many situations where these functions are quite handy.

NOTE: Lead & Lag functions only works with SQL 2012 & above versions