Monthly Archives: October 2016

Pushing till we get there

I am a very difficult boss to work with.

Before I carry on I want to explain that I don’t derive any pleasure from it; it’s just something that’s worked for me. A few days back I was watching a documentary on the training of Special Forces in the Indian Army and the end of the documentary was about have the paratroopers need to complete a 100 km run with 37 kg of supplies on their back. Initially they made it past the 10 km mark before the stress and fatigue started becoming obvious. By 60 kilometers through team work they managed to keep morale up and kept pushing each other to cover the distance. BY 80 km the fatigue was getting beyond what most men could tolerate and they were talking about giving up but being pushed by their leaders to complete the task. After 100 Kms the men said if they can cover this they can cover almost any distance. And therein lies my problem.

One important aspect of the above exercise was that the leaders always kept an eye on the troopers and there was always medical facilities available. So not matter how hard they were pushed they always had a safety net (obviously this wasn’t made aware to them).

I have always believed 10 guys who are pushed to their limits and come out the other end are worth 50 who never know what their true potential is. So I push my guys and I push hard. The more I believe someone has got the skills, the harder I push. I have been accused of not caring enough but often it is because I do care that I am so hard on them. I apply the same tactic to myself. People who meet me think I am special in some way and that is why I have achieved what I have. But that is far from the truth, I am ordinary but I remind myself to push forward every day.

I know this approach is flawed and it might cause more harm than good but if it’s good for them in the long run and helps them deal with the demands of the job/life in my company or any other for that matter then it was worth it. I have learnt more from my worst bosses than from my best. From my worst bosses I learnt that there is more to me and that I am capable of much more. From my best bosses I learnt that encouragement and silent support also has a large part to play in a team’s success. While I haven’t mastered the second part I am hoping someday I will get there too.

The fact is most of the guys I meet are content doing the same old thing every day , hardly any change in routine and no incentive to push themselves to new limits. You might ask what difference it makes as long as the job gets done. The difference is confidence, knowing that you have achieved something most other balked at gives you a new sense of respect for yourself and your abilities. Knowing that you have tackled far bigger challenges than the one you’re facing right now helps you deal with all the smaller problems you face every day. I try to be the safety net for my employees but I also try to push them to the point where they no longer need me either. I tell my guys I don’t want them working in my company after 5 years. I tell them we are a small company and I can’t offer them the kind of opportunities they might get elsewhere so learn what you can here and then be worth 10 other guys applying for the same position in the next company you interview for.

What I am trying to say is we are more than what we believe we can do and sometime we may not see it.

I learned this fact not during my employment but much earlier. I was part of the most successful volleyball team from my school and college days. Each one of us were awesome in our respective roles and could win the game single handedly on a good day but we were also an awesome team (almost to the point where we could read each other’s minds in the middle of a game). During one of our most critical games we were down the first two sets in a game of 5 and were stumped about how to come back; at this point a teammate from the basketball team shouted out ” Come on guys you are better than this, you have never lost a game yet!!!”

He was right. We just needed a reminder and then we went on to win the game.

Word Cloud on Second Presidential Debate :- SSIS and PowerBI

With the recent debates on the US presidential election we felt it presented a good opportunity to do some text analytics on the second presidential debates. We used the links in the reference section below to do most of the analysis and the results are presented as is. The intention of the post is to show how it is possible to do interesting analytics in the MSBI Stack and nothing else.

First we used loaded the transcript into MS SQL Server and then used Term extraction to extract words using the default settings of the transformation.

Inside the DFT

Once the terms were extracted the rest of the analytics was performed on PowerBI portal after downloading the Word Cloud App, link in the reference section.

TRUMPS WORD CLOUD FOR THE SECOND DEBATE

At first glance its obvious “Donald Trump used a lot of words, ”

 

CLINTONS WORD CLOUD FOR THE SECOND DEBATE

 

Most frequently used words for each candidate by Score

PersonCurrent

term

Score

PersonCurrent2

term3

Score4

Clinton

America

12

Trump

country

31

Clinton

child

14

Trump

disaster

11

Clinton

country

24

Trump

Hillary

14

Clinton

Donald

23

Trump

Hillary Clinton

12

Clinton

lot

29

Trump

ISIS

15

Clinton

people

42

Trump

lot

12

Clinton

president

18

Trump

money

14

Clinton

way

12

Trump

people

46

Clinton

woman

14

Trump

problem

11

Clinton

year

13

Trump

Russia

17

  

 

  

Trump

tax

17

  

 

  

Trump

thing

16

  

 

  

Trump

way

11

  

 

  

Trump

word

11

  

  

  

Trump

year

17

 

We used a list of positive and negative sentiment words from twitter to analyze which candidate used what kind of words and trump is the clear winner for negative words 14/3 while both are evenly matched for positive words at 5/3 for Clinton.

There are some interesting other points to note like Donald Trump uses a lot of words that revolve around Terror and ISIS while Hillary Clinton used a lot of words relating to Healthcare and the Supreme Court.

Hope you found this Post interesting and full disclosure we haven’t actually heard the debate since that wasn’t the point of the exercise it was just trying to use a machine to summarize a debate and provide unbiased text analytics.

References

http://www.politico.com/story/2016/10/2016-presidential-debate-transcript-229519

https://github.com/jeffreybreen/twitter-sentiment-analysis-tutorial-201107/blob/master/data/opinion-lexicon-English/positive-words.txt

https://github.com/jeffreybreen/twitter-sentiment-analysis-tutorial-201107/blob/master/data/opinion-lexicon-English/negative-words.txt

https://app.powerbi.com/visuals/

 

 

 

Plan reuse for conditional statements in MS SQL Server

I was asked this question the other day on linkedin and figured it might be good to write a post on it considering the confusion around the topic. The question was

I always read that in case of multiple execution paths, execution plan is saved for the first ever executed path. So if my SP has following logic if @x=1 select * from x else select * from y so execution plan will be saved either for table X or table Y. I found many links supporting this,http://dba.stackexchange.com/questions/9835/using-if-in-t-sql-weakens-or-breaks-execution-plan-caching as per this we should have multiple SPs for each execution path.

The basic outline for the question is if there is a procedure that executes different code blocks based on conditional IF parameter being passed in the input. Does SQL create a new plan for each condition or try to reuse the first plan? In order to understand this we need to look at the plan cache which is where SQL stores the execution plans. The below script which was provided by the OP helps identify what’s actually happening.

IF exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='x')
 
DROP TABLE x;
 
go
 
IF
exists(SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='y')
 
DROP TABLE y;
 
go
 
CREATE TABLE x(id INT PRIMARY KEY IDENTITY(1,1)
, val NVARCHAR(20))
 
INSERT INTO x(val)
VALUES('a'),('b'),('c'),('d'),('e'),('f')
 
CREATE TABLE y(id INT PRIMARY KEY IDENTITY(1,1),
 val1 NVARCHAR(20), val2 NVARCHAR(20))
 
INSERT INTO y(val1,val2)
VALUES('aa','aa'),('bb','bb')
 
go
 
IF OBJECT_ID('test') IS not null DROP PROCEDURE test;
 
go
 
CREATE PROCEDURE dbo.test
 
@id INT
 
AS
 
BEGIN
 
SET NOCOUNT ON;
 
SELECT @id % 2;
 
IF (@id % 2)=0
 
SELECT * FROM dbo.x WHERE ID=@Id;
 
ELSE
 
SELECT * FROM dbo.y WHERE ID=@Id;
 
END
 
go
 
DBCC freeproccache
 
go
 
EXEC test
1;
 
--Notice PK_Y
 
EXEC test
2;
 
--Notice PK_X
 
go

 

If you run the above script with Show Actual execution plan you will see the plan as below when executing the procedures individually.

For input value 1

For input value 2

As can be seen from the above, SQL is querying from different tables based on the input parameters. So does this means a new plan was created? It obviously doesn’t mean that the plan was reused, Right? Wrong!!!

When looking at the Actual execution plan we are seeing what the optimizer went ahead and executed, this doesn’t mean it’s the plan that was compiled. It’s only showing us the part within the original plan that was being used. To see what the original plan was run the statement below

select
*
from
sys.dm_exec_cached_plans c

outer
apply
sys.dm_exec_query_plan(c.plan_handle)d

where objtype =‘Proc’ AND dbid = DB_ID()

Notice the use count is 7 indicating the same plan is being used regardless of the input parameter.

So the question becomes what does the original plan look like?

Based on the input parameter SQL is simply executing different branches of the plan and that is what we see when we are running the Show Actual Execution plan. If you really want to see the behavior try passing NULL as the input and you will see the combined execution plan when viewed under Show Estimated Plan.

Creating subfolders within SSAS

Recently there was a requirement for organizing columns from certain dimensions into individual entities within the presentation layer of powerview and powerbi. The approach that was taken was to create additional dimension tables that reference only those subset of columns. Similar to the diagram below.

While the above design will do the job, we see that the relationship is 1:1 and therefore in the interest of designing a star schema we need to denormalize the data so that it look like the table Customer_source in the data source view of the cube while still providing a split into column subsets as shown on the right hand side of the above diagram.

In this case we import the table customer_Source as is and then use BIDShelper to give logical folder paths to the columns as needed. You can download BIDS helper from here. Make sure to check the version as per your installation of SQL Server. Once installed you can right click the model (in this case I am using Tabular model) and navigate to the folder Tabular Display Folder as shown below.

In the below screenshot you can see how certain columns from within the fact table have been bucketed under a folder called time and then a folder as per the nature of the column.

The same kind of hierarchy can also be implemented on dimension tables as well as shown below:-

The end result will look as shown in the below screen, this way we do not need to compromise on the Cube design to accommodate presentation layer requirements.

SSMS Browser

Excel

 

 

 

Tracing Analysis Services – the trace that won’t stop

I am currently facing an issue where profiler is running a trace on SSAS and the trace can’t be stopped from within SSMS profiler since it keeps throwing the below message.

Here is a very good link to identify and stop traces that are currently running on SSAS using XMLA commands. Unfortunately this hasn’t worked for me since I am still getting the above pop up after all traces including flight recorder have been stopped.

https://blogs.msdn.microsoft.com/pfebi/2013/02/12/managing-server-side-analysis-services-profiler-trace/

I am still trying to figure out what the root cause is and will update this post as soon as I find out a solution.

Update :- After restarting Analysis Services flightrecorder came back but the profiler window still didn’t close. The options under the file menu are disabled so can’t stop the trace using the UI either. Manually closing the Profiler trace window within task manager seems to have closed the window and on checking there doesn’t seem to be any other traces than flight recorder running at the moment. I am guessing at this point it’s a bug within SSMS profiler task which doesn’t shutdown the trace when it’s running on a tabular model that was unloaded from memory.