Tuesday, July 31, 2012

SSIS Parallel Processing

In SSIS one of the key metric that is observed is how well the SSIS packages are performing. The ETL operations become critical to the the availability of data marts/data warehouse which are used for different types of reporting by Business. One of the package level property that is available in SSIS is MaxConcurrentExecutables, please see image below:


Sam Vanga , a MS BI Expert has explained this property in further detail along with some illustrations. The value of -1 is the default setting, it should not be altered without fully understanding the impact of the value.
The link to the artile is here:
http://www.sqlservercentral.com/blogs/samvangassql/2012/07/20/parallel-execution-in-ssis-with-maxconcurrentexecutables/
Hope this article is useful for developers working with SSIS packages.

Tuesday, July 24, 2012

SQL Server BI...SSAS

BI in MS SQL Server is growing at a very rapid pace, at the same time there is a lot of expertise that can be shared among professionals. SQL Server Analysis Services is still the core product for doing the traditional BI and there are customers who use SSAS is the traditional way compared to the upcoming self service/Ad hoc BI solutions. One of the questions which was recently asked in a Q&A Setting was that How would one deploy a sustainable/reliable cube, meaning a SSAS cube with minimal downtime. Here the emphasis is on what kind of server architecture would be used to achieve the above scenario. In order to answer/handle this scenario, one should have really hands on experience deploying SSAS cubes in highly visible/Sensitive environment. One of the solution suggested was that 1) One needs to take advantage of the core processors on the server 2) Since SSAS cubes are predominantly read only, one could Load Balance the servers on the which the cubes are deployed on so that there is minimal downtime. One can also refer to the following link for tuning the SSAS 2008 cubes.

http://www.microsoft.com/en-us/download/details.aspx?id=17303

Tuesday, July 17, 2012

SQL Server-Results in Grid

In SQL Server Management studios while running queries, the results can displayed in text format or for better formatting the results can be displayed in Grid format. When the results are displayed in grid format the column headers with the column values are displayed. The results can be saved to .csv file/excel, let us say in case the columns need to moved in order of appearance, say the the 10th column in the result set need to be next to the first column in the result set, this can be in done in the Results in Grid Format. In order to swap the columns,
1. Click on the column header(say 10th column in the result set) that need to be moved, keep the left mouse button pressed, this would be change the background of the column header to be suppressed.


Please refer to the notes in the image where in I have used an actual example.

2. With the left mouse button pressed drag the column header and start moving it to the column header where it needs to be placed.
3. When the column header which is being moved is on top of the column header(the current 2nd column header) where it needs to be placed, release the left mouse button.
4. This would complete the movement of the column header, now the 10th column would be in the second place, the current second column would be moved by one to the third place.

Thursday, July 12, 2012

SQL Server - SSDT(Data Tools)

One of the tools I recently came across was SSDT for SQL Server. SSDT stands for SQL Server Data Tools. This tool once downloaded and installed integrates with the VS 2010 Professional environment. It is powerful tool which provides lot of database related functionality and extends the current database features available within VS. In one sense it is like providing SSMS type functionality from VS. That being said the question might arise what would be reason to have two feature similar tools SSMS and SSDT. There are some features which stand out in SSDT. One of the feature available in SSDT is Compare Schemas, this would allow one to compare database schemas, this would be very handy when one has Dev,QA,UAT and Production environments. This would provide the database admin/developer an ability to maintain consistency of schemas across all environments. The other feature which i liked was the ability to script out data from a table. This is very useful when there are certain tables that need to populated with base data in a new installation or a testing environment. The interface of SSDT is very similar to the SSMS interface the difference being the options which are available. One of the Pre-requisites when installing on VS 2010 is that SP1 of VS 2010 should be installed. There is a very good website on msdn which has all the information needed:
Microsoft SQL Server Data Tools ...
http://msdn.microsoft.com/en-us/data/tools.aspx

In the above site there is also an article related to Getting Started with LocalDB Debugging using SSDT

where in the concept of LocalDB is explained.
In Summary SSDT is a good tool for Database/BI folks who are hands-on while working with VS2010, in the future I will explore the options on this tool and writeup some blog posts.




Monday, July 9, 2012

SQL Server-Career Perspective...

Technology today is evolving at a very fast rate wherein it takes a good bit of work to put arms around it. Growth and advances in technology is mutlifold spreading across different aspects. SQL Server is no exception from what was a pure DB product to today with multitude of offerings where in expertise in a specific area of sql server demands a lot of time and effort. With this in the background how does one shape up a career, more so for experienced folks how do they relevant and when is the right time to give up your job and move on. I came across this article by Brent Ozar who is a SQL Server expert and writes amazing articles. The article is titled "Why I quit my jobs"
http://ozar.me/2012/07/why-i-quit-my-jobs/#comment-480
This would be a article to gain a perspective while I try to research my next blog write up.

Monday, July 2, 2012

SQL Server-BI Questions...

Recently I happened to attend a interview for a BI Position which was a based on a Consulting type of role. The questions spanned from SSIS,SQL,SSRS and SSAS. Of course if there was a an expsoure to the newer features such as PowerPivot,Powerview and use of sharepoint integrated mode that would have opened up an other set of questions. Here is a list of questions which were asked:
1. The first questions was around slowly changing dimensions, what are the the different types of SCD's. How is each type of SCD implemented from a schema standpoint. In SCD type 2 scenario how would one locate the most recent record. This was a pretty easy question to answer, of course more real project implementations help significantly.
2. What kind of methodology was used whil designing/building data marts/Data Warehouses (Inmon or Kimball), depending on the answer the next set of questions were asked.
3. What is a snowflake schema, how is it designed in SSAS, give an example, aslo differentiate between a snowflake schema and a parent child dimension. Here a understanding of snowflake schema and practical experience of implementing such dimensions in SSAS would help. Also implementing a parent child relation ship in SSAS would help as well, here with respect to parent child (it is about performing a self join on the table).
4. What is the purpose of Lookup task in SSIS, give a scenario describing where a Lookup task would work well, also provide a contrasting scenario where Lookup task would not work well.
5. While peforming fact table load thorugh a ETL process how would one perform Dimension lookups for the existing of valid keys, also in case of late arriving dimension data, how would the related fact data be handled.(Here one of the options is how to use UNKNOWN attribute which could be part of a dimensions).
6. How are Aggregations designed in SSAS, what is the default behavior, if one were to selectively build aggregations how would one do it. (here an understanding of SSAS cube architecture, how aggregations are performed is very important).
7. Compare and Contrast MOLAP vs ROLAP, what are your personal obervations regarding performance such storage in SSAS, here building cubes in these two storage modes, understanding how load,processing and retrieval of data from such cubes is important. This focuses on how cube is built kind of behind the scenes in SSAS.
8. How is security set up for a cude, in case security needs to setup for a dimension how is it performed, what is importance of having a role.
9. What different kinds of SSRS implementations are available. What different kind of reporting work were performed by SSRS (relational/SSAS cube reporting). In case of SSAS cube reporting how was the MDX created, here understanding of MDX very important.
10. Was TFS used in yourprojects, for what purpose was TFS used and was it used for SSIS,SSRS and SSAS projects.
I have provided  as much info about the questions that was possible. I feel all the questions focus on understanding of how things work, why such approaches are being adopted and what are the best practices. In order to tackle these a good hand on implementations and challenges faced would really enhance the responses and provide details as needed.