Monday, April 23, 2012

SQL Server 2012-BI Edition

I finally got to download the BI Edition of SQL Server 2012, this edition is new in SQL Server. The edition is focused on tapping the new features that have been added to the BI landscape. There were certain new features which i noticed in SQL Server 2012 BI edition. I choose to install the SQL Server DB engine,SSAS,SSRS and Integration Services. SSRS install has gone through a revision, there are new features added to SSRS sharepoint mode which is to facilitate the install and execution of Powerviewer. In SSAS the server can be configured in two modes 1. The regular multi dimensional and data mining mode. 2. The tabular mode, in this mode SSAS server would handle self service BI capabilities. Based on these two options, in case a business needs to handle both then i guess there would be two instances of SSAS one handling the regular MOLAP capability and the other Server would handle the self service piece. It is pretty evident that both the modes cannot coexsist on the same SSAS server. Integration Services and BIDS have gone thorugh a change in this edition. Int the programs under SQL Server 2012, one gets to see a new program called SQL Server Data Tools, this is the new name for BIDS. On launcching the SQL Server Data tools it opens a VS 2010 Shell and it can be customized for Business Intelligence Development. Once the VS 2010 shell has been customized and if you choose the option New Project and the project type is Integration Services Project, there are some elements which appear in the design page of SSIS package. In SSIS 2012 one of the main new features is the addition of Parameters which is available at the Project level and the Package Level. In the Solution Explorer one gets to see the Project Parameters like in the image show below. The project parameters is named Project.params.

In the main design page of the SSIS package one gets to the addition of an extra tab called Parameters.
The third tab (the one after Data flow tab is the Parameter tab) is the screen to add parameters.

Based on the initial looking around in SSIS 2012 parameters has a big role to play. As i continue to explore I will blog about the usefulness of Parameters in SSIS 2012.

Friday, April 20, 2012

PowerView Video-SQL Server 2012

SQL Server 2012 is released, the database market is flooded with alternatives, Bigdata concepts and tools are on the rise, the number of reporting tools are also on the rise. PowerView of Microsoft's big components in Data Visualisation is ready, how does it stack up, what works well in PowerView, what are areas of improvement, the following link is a video which gives a very good perspective of PowerView.
The video is interesting and important because PowerView has been evaluated on the basis of current cognitive research around Data Visualisation principles. There is a lot to cover in SQL Server 2012 a much bigger and better offering from Microsoft.

Monday, April 16, 2012

NoSQL Databases...

As the Database world evolves one of the rapidly developing space in Databases is NoSQL databases. There are a variety of implementations available. I had blogged about NoSQL Databases in an earlier blog post. Here is one I would like to share with you all, it is a database called MongoDB. There is a very detailed and excellent article post on MongoDB architecture by Ricky Ho. It is very well written and the differences between Relational and NoSQL have been highlighted.

Thursday, April 12, 2012

SSIS Connections...

One of the key aspects of working with SSIS is using Connections which are key to extracting data from data sources and populating data into destination databases. As the ETL project gets bigger, managing connections becomes a very important aspect. It can become very tedious if one is opening and closing packages as the SSIS packages are moved from one environment to another and at the same time changing connection strings. There are different configuration methods to alleviate some of the redundant work.
Andy Leonard SSIS expert recently gave a presentation on Managing SSIS connections in SSIS 2008.
He provides insight into connection managers and how to store connection strings and at the same time be compliant with the audits. Here is the link:
A very good video to get insight into connections in SSIS.

Monday, April 9, 2012

Custom SSIS Logging...

In one of my blogposts I had blogged about using the Logging Options Provided by SSIS. The options available in SSIS are good. Depending on one's needs one could develop Custom SSIS logging solution. One of the options I have utilised is to use a table in a SQL Server database, create the table with meaningful columns which capture information that would help in troubleshooting and auditing. The columns which i would like to include are source_rows, Inserted_rows,error_rows. These would enable a developer to keep track of how data is flowing through a SSIS package. The audit table for example could be named as SSIS_Package_Audit with columns such as ( UniqueId(Identity),PkgName, StartDate, EndDate, SourceRowCnt, InsertRowsCnt, ErrorRowCnt,ElapsedTime_m, LastError,ServerName,DatabaseName). When a Package starts as one of the first steps in the SSIS package, a new row is inserted into the table to indicate that the package has started. As the package progresses through different points, the audit table can be updated. In case the package encounters an error, an update can be made to the LastError Column in the table with an appropriate error text. This could tell us what error was encountered when the package was run. Inserts and Updates to the audit table can be done through a Execute SQL Task or a Script Task.
I have included a image for reference, The first script task inserts a row, the last script task actually updates the row which was inserted with values for the rowcounts and enddate, Last error column would be set to NULL. This would indicate a good run of the SSIS package. The solution being presented here is a starting point, this can be greatly enhanced to suit the business needs and dependencies of packages could be included in the table.

Thursday, April 5, 2012

SSRS 2012

With SQL Server 2012 being released there is lot of new features all across the board which are being studied and used. The mechanisms to pull data and use data in different ways is ever exploding. SQL Server Reporting Services 2008 and 2008 R2 were good releases where in SSRS started to being looked at as a reporting tool with good features and capability. SSRS 2012 has an increased set of features, Microsoft has listed the main highlights of the new product. Sharepoint is also getting lot of traction in lots of companies these days. In order to look at what is new in SSRS 2012, here is a link:
I am planning to start working with the evaluation version of SQL Server 2012 and keep abreast of what is being offered.
As a side note: I am including a nice article on how to land a job as a developer: This appeared in SQL Server Central as well.

Wednesday, April 4, 2012

Data Visualization-Part 2

As Newer software and newer versions of operating systems are getting rolled out so is the field of data visualization which is rapidly expanding. In my earlier posts I had blogged about data visualization and Powerviewer. In this blog post I would like to share new tools which have come out and which address different platforms. Computerworld magazine has come up with a article which looks into 8 Data visualization apps and summarizes the findings.
The article nicely summarizes the different operating systems on which these apps run.