General

Run SQL scripts from a folder using sqlcmd

This is a very short post about a very useful tip. I came across here on   stackoverflow and it certainly deserves a post. Many thanks to the answerer and I think this is his blog. I already follow it and would highly encourage you if you are interested in SQL Server.

Very often I need to execute all the SQL scripts from a folder. There are tools out there to do this but I wanted something very simple, like a batch command. So let’s say you want to execute all the SQL scripts from folder ‘D:\Scripts’  on server ‘localhost’ and on database ‘playbox’. Here are the steps

1. Open command prompt.

2. Navigate to the scripts folder.

3. Type the following command.

for %f in (*.sql) do sqlcmd /S localhost /d playbox /E /i "%f"

What this will do is iterate through all the .sql files in the folder and execute each one using sqlcmd. /S, /d, /E  are sqlcmd parameters. The beauty of this solution is you don’t have to install anything. Wrap a few of these statements in a batch file and you can execute statements from different folders in one go. Neat!!!

SQL Server Query Optimizer and a trivial observation

This is a very short post on a trivial observation about SQL Server query optimizer. Look at the following query on AdventureWorksDW2012 database.

SELECT FinanceKey
,Amount
,DATE
FROM [dbo].[FactFinance] FF
INNER JOIN dbo.DimDate DD ON FF.DateKey = DD.DateKey
INNER JOIN dbo.DimOrganization DO ON FF.OrganizationKey = Do.OrganizationKey

And now look at the execution plan
Query Execution Plan

The joins to the dimension tables are missing. Now look at the query again. The optimizer was intelligent to recognize that the join would not have any effect on the final result set. We are not selecting anything from the dimension table neither there is a filtering condition. Hence the optimizer removed it from the plan altogether to make it more efficient.
Now lets add WHERE clause
SELECT FinanceKey
,Amount
,DATE
FROM [dbo].[FactFinance] FF
INNER JOIN dbo.DimDate DD ON FF.DateKey = DD.DateKey
INNER JOIN dbo.DimOrganization DO ON FF.OrganizationKey = Do.OrganizationKey
where DD.CalendarYear = 2005

and look at the execution plan. The WHERE clause adds a clustered index scan on DimDate but DimOrganization is still missing since nothing is selected or filtered on the table. Clever!!

Export file names from a folder

Lets say you want to export names of all the files and folders within a folder. Doing it one at a time will be pretty time consuming. This little trick will come handy in such situations. Open up cmd prompt and navigate to the folder from which you want to export file names.

Let me introduce you to “>>” operator. What this will guy will do is transfer the output of the DOS command you are executing to the file specified. This is very handy if you are executing SSIS packages through a batch command. Put “>>” at the end of every command, specify the file name and all the output messages will be transferrred to the file. In the following cases, the file is called ‘files.txt’ and will be created where ever your command promt location is. You can always specify a different location.

1. To get just the names of files and folders 

 dir /B  >> files.txt

2. To get the full path of files and folders 

 dir /B /S >>files.txt

3.To get only specific files for e.g. csv

dir /B /S *.csv >>files.txt

dir /B /S *.csv  >>files.txt

dir /B /S *.txt >>files.txt

dir /B /S *.xls >>files.txt

Why business intelligence?

I watched a very interesting talk on TED yesterday by Pankaj Ghemawat called “Actually the world isn’t flat”. I would highly recommend it especially if you are working in the Business Intelligence field. 

The argument in the talk is simple. We percieve our world as hyperconnected and globalized. We use devices ‘Designed in California Assembled in China’. We wear cloths made in India or Bangladesh. Our fruits and vegetables come from Africa. We bank with a Hong Kong based bank and we work in a country we were not born in. It does appear that everything is connected and that there is mass exodus from not-so-good places to better of places AKA immigration. However the actual numbers tell a different story. To quote an example from the talk, majority of french people thought the around 24% of the population are immigrants while the actual number is 8%. The external forces makes us exagarate the situation. Mr Ghemawat has collected a vast data sets which contradicts many of the popular belifs against what actual data suggests. 

This can be correlated in busineses as well. Managers tend to make intutional decisions based on the experiance, however, the stats paint a different picture. The aim of business intelligence to fill this void. So that managers can make informed decisions based on facts rather than the gut feeling. Data always speaks the truth. I would also recommend book by Davenport called ‘Competing on Analytics’. The argument is same. Take a decision based on data, not on intuition. And this where business intelligence will come handy. 

Microsoft Surface 199$ gamble

So if the news on the web is true then the new tablet kid on the block AKA Microsoft Surface is going cheap. Engadget and CNet are reporting that it will be around $199. Surprised! I know I am. When it was launched my gut estimate was around $499 for lowest Windows RT model going up $1500 for professional level high end model. $199 look really puny and I look like a fool. So what might the reason behind the magical $199 mark (magical because Nexus,Kindle & Nook are at same price range). Most will agree that Microsoft will not make much money at $199 price tag. Google hardly makes anything from 7″ Nexus 7 and Surface is going to have 10.1″ LCD. So profits on hardware seem to be out of question. Which means there must be a long term motive and here’s what I think it is. 

Microsoft is new in the already crowded market. That includes both hardware and software. So to take a slice out of it, the viable option for them would be to sell their wares at cheap. The target would be the large not-so-niche market. This would increase the user base. If the user base increase, other manufacturers can follow the suite and launch their own versions of Windows tablets. That means more options for end users and more Windows tablets in the market. Microsoft can licence the Windows copy on each of them and earn revenue. Just like what it does currently. IMHO, that’s the ultimate goal. Besides you always have the app store revenues from selling apps, music, movies, games etc. If down the line Surface starts making profits, that’s an added bonus. Launching Surface at cheap is just to kick-off things.

Let’s see how this thing shapes up. 😀

Apple,Google or Microsoft. Pick up your partner.

Reading about all the smartphone news these days, I cannot help to come to conclusion that ultimately it’s coming down to three major players Apple, Microsoft and Google. I mean it’s not that hard to see, is it? And I am not talking about the hardware. No sir. I am talking about the more lucarative software side of things : the OS, third party applications, media, cloud etc. Why Microsoft? You might be thinking. Read along to find out why I think so.

Let’s begin with the big daddy of all, the Apple. It is indeed in a very safe position right now. What with 68% tablet market share ,cool profit margins on its hardwarehealthy iPhone sale and awesome app system. It’s going to be here for long time.Period. Get over with it. But Apple’s ecosystem is tightly integrated with its hardware. So unless Apple allows third party access to its ecosystem,there will always be room for other players. I mean not everybody is going to buy Apple products suddenly. Also, Apple products are expensive which gives chance to other players to get in the not-so-niche markets.  RIM is somewhat in the same situation. Software tightly coupled with hardware. Except that people are not buying into Blackberry promise.

I guess Google understood that long before I did (I suppose that’s why they are Google). Giving the hardware manufacturers a free OS that can compete with Apple’s iOS, painting Google over it and generating revenue from it not only by way of Ads but also from Apps & Media, is ingenious. That seemed to have paid off well. Andriod is doing well. It is second only to iOS  Infact it is doing better than iOS. Couple that with low cost and variety of Andriod phones and tablets and I think Google should be pretty happy with itself. I am no expert but I think Nokia missed a big opertunity with Symbian there. Can you imagine if Symbian OS would have been modified quickly and efficiently enough for touch based devices and rolled out to handset manufactureres. Most of them already used Symbian the switch could have been smoother I suppose. Nokia could have been where Google is today with Android. Anyway.

Coming to the point of why Microsoft then? First and foremost I personally admire what they have done with the Metro UI changes. Second, all the services that an mobile ecosystem needs are already there from Microsoft. They just need to take these and integrate it into a platform in a seamless way. Take email for example. We always had hotmail and now the new Outook.com. There is skydriveXbox Live for media & games and office online for documents. Third, number of PC user’s are much more than of any other users. Yes, I know, the recent Mac sales are much more than PC’s but average Joe still has a PC and is conversant with using it. So if all his devices start talking to each other over the air, seamlessly and he doesn’t have to pay a fortune for it, wouldn’t that be cool?Fourth, Microsoft has a large developer community. If the user base increases, the number of apps will surely increase. Fifth, enterprises all over the world use Windows and Microsoft solutions. It’s inevitable that they will start offering these on mobile platforms one way or another.

As you would have guessed I am really hopefull that Microsoft platform picks up. Surface looked awesome and turned out what I predicted it to be. Apple started it all, still leads it but comes at a premium price. Google took the idea and opened it to the mass market. Now I think its Microsoft’s turn to make use of it’s strong user base and create a very unique offering. I, for one, am with them.