Wednesday, March 28, 2012

SQL Server Certification-2012

With the SQL Server 2012 being released and making its way into the enterprise. The certifications program for SQL Server 2012 are being readied for launch. How much certifications help out has/is will be a subject of debate. Keeping debates aside, Microsoft has provided information on how to go about it. Here is the link to:
http://www.microsoft.com/learning/en/us/certification/cert-sql-server.aspx#tab2
It is being recommed that folks complete certifications for SQL Server 2008 before getting into SQL Server 2012 one of the reasons being that the path will be shorter. I guess more will be know about these as the program rolls out in  June 2012 (as mentioned in site).
In case any one has completed the SQL Server 2008 BI certification, could you please share the resources in the Comments section of the blog.
Thank you

Friday, March 16, 2012

SSRS-InitialToggleState

I was working on Developing a SSRS report which displays data about SSIS packages and package dependencies. To get a better perspective the report displays data about packages and its dependencies meaning which need to be completed before running a particular package. I created a stored procedure to provide data in a Master/Detail format and worked on presenting it in a SSRS report. In the report I had the detail row which was the dependencies, the main column on which the report was grouped on was the Parent/Master package column. The idea was to provide a collapsible master/detail report such that when expanding the Parent package one can see the dependent packages in the detail on the same report.
The SSRS report has a list of Package Names as a parameter with Multi Select enabled. By default the report runs for all the values and displays the parent Package and it s dependencies. The issue was that the
'+' sign was appearing before the Parent Package Value and also the details were shown. Ideally we should have the '-' sign appearing before the Parent Package name since all the details are show when the report is run initially. In order to handle this scenario I used a Property which is available for a column in the report, it is called the InitialToggleState Property. By default this property is to false, this was making the '+' sign appear before the column data in the report. I set it to True and ran the report, this displayed the data with the '-' sign in front of the Parent Package and the details were displayed in the same report.


Below is an image with the Report result:

Thursday, March 8, 2012

SSIS Documentation...

One of the important tasks when there are ETL SSIS packages being developed for a Data Mart/Datawarehouse project is ability to document the SSIS packages. It is sometimes very time consuming to open package by package and then go through all the tasks to understand what is being done in the package. The other issues here is that there is a dependency on Visual studio being present on the laptop/PC. One of the products available to perform this type of task is called SSISDocumentor, this product supports both SSIS 2005 and SSIS 2008. The product can be downloaded from the website:
http://www.ssisdocumentor.com/. Once the product is downloaded, the installation is very straightforward. When the installation is complete in case BIDS/Visual studio is already open, save the work and exit out of VS/BIDS. Reopen Visual Studio/BIDS, open up the SSIS project/solution or the package. In the Solution explorer, right click on the SSIS package that requires documentation and once can see the Generate Documentation Option. Please see image below:

Click on the option, once the document is finished, a folder called Documentation is created and in the folder one can see the package documentation with a .html extension. The neat thing about the .html option is that the documentation can be viewed in a browser. Right click on the .html like shown in the image below and choose view in browser, the documentation will open up in the browser.


There are 2 types of documentation available Brief/Verbose. Each of the task in the SSIS package is depicted in a diagram and one can click on any task, then it takes you detail of the task. The script/sql statements are depicted in a neat way like an editor and once can copy the script/sql statements to the clipboard or any other tool to examine the statements. The documentation tool is very helpful for developers,architects and support folks who need to analyse the package and understand the flow of data from source to destination. This type of SSIS documentation can greatly enhance the stability of an ETL group and in cases where folks leave, it would be very helpful for developers who are coming on board to the team. Snapshot of the documentation below:

Tuesday, March 6, 2012

Data Visualization...

The area of Data visualization has heated up with so many devices to choose from to display the data. With the rise of in memory BI offerings the level of data visualization has increased considerably. There are some niche players and some big vendors. The big companies are surely keeping an eye on the niche vendors which are doing well. The following blog entry talks about the Gartner report and takes a different view on the findings in the report. The blog also gives out lots of detail on different tools in the data visualization field.
http://apandre.wordpress.com/2012/02/12/data-visualization-platforms/

Friday, March 2, 2012

Monitoring SSIS packages...

When working on developing a Datawarehouse, there are going to be ETL operations which is going to involve lot of SSIS packages and related objects. One of the key things to keep in mind while building SSIS solutions to make sure there is sufficient auditing of the packages, which would also enable smooth troubleshooting of issues with SSIS packages. In my earlier blog posts I have talked about Logging in SSIS, let us say for example the scale of ETL has increased meaning it spans different servers. Brian Knight founder of Pragmatic Works has come up with Best Practices around monitoring SSIS packages.
Here are the Basic Principles.

Rule #1: Defragment Your Auditing
The better alternative is to create a centralized database to receive all the events from all of your SSIS servers. Then, create a series of reports on top of that centralized database.

Rule #2: Use Event Handlers and Row Counts for Auditing
You will also want to trap Warnings, PostExecute and PreExecute events in the event handler tab. Doing so will allow you to see when a step starts and stops.


Rule #3: Success does not mean Success
Folks who have been developing SSIS solutions can talk to Rule#3. There are situations where data rows would be transferred from the source but rows did not go through to the destination. A rowcount check needs to be performed on the source and destination. In case the row counts do not match send a email/raise a alert, this could avoid costly manual troubleshooting time.