Knowledge Sharing

In datawarehousing field, it is more likely to load data from text files and it is deveopers head ache to deal with the problems of formatting issues.  In this post I would like to show one typical issue with text file which can be tackled very easily. I have a file with Sudent,Mark and address details which need to load into the table. I have created the flat file connection in SSIS and realised that it was not able to recognize the columns due to some extra junk lines in text file before starting of the actual required details and considered as a single column as shown in Figure 1. Figure 1 Find out the number of lines with junk values and skip those lines by providing the numerical skip values to the property Header rows to skip as shown in Figure 2. Also provide the tick mark for the property column names in the first data row which will consider the first line after the skipped lines as a column name. Figure 2 now go back to

MS-Excel is one of the wonderful tool available today for data analysis. It is having number of lovely features to do data analysis. Here I would like to show you one simple but very time saving and important bucket analysis. In data analysis field, it is very important to bucket the data and do the deep dive of each area seperately based on the bucket result. I have one file with Population data as shown in Figure 1 Figure 1 Now the requirement is to identify each 20 years bucket right from 0, how many people are belongs to each. We can easily analyse this by loading these table data into pivot and group accordingly which I have demonstrated below. Step 1 : Create pivot table Figure 2 Step 2 : pull the people field into aggregate window of pivot and age into row values as in figure 3 Figure 3 Step 3: right click on the row label (bucketing field) and select group and provide the details as in figure 4 

I have been thinking to explore SQL server 2012 since its lanuch date April 2nd 2012. I had opportunity to work using SQL server 2005, 2008 and 2008 R2 BI tools and now SQL server 2012 code name changed to DENALI. Microsft is boasting about Denali's capability in self service and corporate business intelligence and we can bet on this only after some years. One of the main change that I liked in SSIS is its look which is totally changed from its predecessor. Interface is improved to include details about the selected task and sample to learn about it, added one more window to work with package parameters, tool box is having two more windows to add favourites and common tasks and the user can choose which task can be added into these windows. Another useful but minor feature that developer would like is undo and redo feature which was not available in earlier versions. In this post I would like to explain about another wonderful feature which will definitely a great help to th