Monday, December 21, 2009

Working With NULLS

In SQL Server one of the interesting to deal with is NULLS while programming in T-SQL and also design of tables. Very recently i ran into an issue with NULLS, the scenario was in the context of T-SQL. The business rules were such that I had to compare values, so the SQL was constructed. Once the stored procedure was complete, it was time to unit test the stored procedure. On executing the stored procedure I was not getting the desired results, the expected the result was there were rows that should have got updated in the table. On debugging the sql, the problem area was comparing the two numeric values, the condition was like CurrentAmount <> InputAmount. Here there were 6 rows in the table which had NULLs in the currentamount and the other 10 rows which had values, when comparing NULL to a value the result is an Undetermined state this caused no rows to be updated. The expectation was 16 rows would be updated, in fact after a little bit of examining, the ISNULL function was used on the arguments on both sides of the condition. The check was modified to ISNULL(CurrentAmount,0) <> ISNULL(InputAmount), once this was done, I got the desired results. While working with NULLs it would be good to keep in mind the undetermined state while comparision.
The above situation arises when:
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:
When ANSI_NULLS IS OFF, the above behavior mentioned is not applicable.

Thursday, December 17, 2009

SSRS:Master-Detail Reports...



In SSRS 2008 there are different ways to create Master detail reports. One of the strategies adopted to Master Detail was to use the Javascript Windows.Open function. For this function the URL string has to be built, in case the detail report requires parameters, the values for the parameters need to be passed and built as well. For example if there is a Column called SalesPerson, the users wants to click on the sales person's name and view the details of his sales transactions. In SSRS , in the tablix control right click on the cell containing the Salesman name. Choose the Option TextBox Expression.





This opens up a window, on the left there is a option Action, click on this. You will be presented with a list of options, choose Go to URL, below that there will be a textbox with a button on to the right, click on it.





This will open the expression window where you will type:
="javascript:void(window.open(" & Code.GoToDetail("XYZ","Manager",Fields!SalesmanId.Value,Code.ShowParameterValues(Parameters!as_accttype),Code.ShowParameterValues(Parameters!as_city)) & ",'_Blank','Toolbar=no resizable=yes'))"

In the above example the windows.open calls a function called GoToDetail which is defined in the Code Section of Report Properties. In the GotoDetail function, one would build the URL String to access the detail report. The URL construct would like:


URL = "'" & ReportPath & ReportName & "&rs:Command=Render&rs:Format=" & RenderFormat & "&rc:Parameters=false" & as_Type & as_Section & as_accttype & as_city & "'"

The URL String returned from the function. When the user runs report, under the SalesmanID column the icon would change to pointing finger. When the user clicks on the SalesmanID value it would open the Detail Report. In case one has dual monitors the master and the detail report can be compared side by side.

Thursday, December 10, 2009

PowerShell

One of the new features to surface in SQL Server is PowerShell. It is a scripting lanaguage which is very powerful altrnative for VB script/SQL-DMO. PowerShell has lot of commands and functions which are very useful for database connectivity,querying and file movement and copying of files. The Powershell is also incorporated into SQL Server Jobs as a type which means that Powershell scripts can be executed with a sql server job. Once logged into SSMS, one can start the PowerShell by right clicking on the databases, this will take to the PowerShell Prompt, which looks likes a DOS-Command prompt window. In this window, Powershell commands can be executed, this can be used as a good testing ground for building PowerShell scripts. Here is a link to an excellent link on PowerShell tutorial:
http://www.powershellpro.com/, the lessons are very well laid out with lots of examples:

Here is a sample of how a Powershell script will look like: For example, if the need is to delete files which are more than 3 days old:


$Now = Get-Date
$Days = "3"
$TargetFolder = "Your Path Where the Files are Located"
set-location $TargetFolder
$ModifiedDate = $Now.AddDays(-$days)
$Files = get-childitem $TargetFolderWhere{$_.LastWriteTime -le "$ModifiedDate"}
If($Files -eq $Null)
{exit}
Else
{
ForEach($File in $Files)
{Remove-Item $File}
}

Tuesday, December 8, 2009

PowerPivot

While i was reading Pinal Dave's summary on PowerPivot in his blog(blog.sqlauthority.com), It made me curious and wanted to check out what the product is and what would be the features. Microsoft has put a out a nice website with the download option along with videos and try out Powerpivot hands on in the virtual lab. Here is the web site:
http://www.powerpivot.com/
I decided to check out the virtual lab of powerpivot. It was a nice tutorial lab experience to begin with. The lab takes you through how to launch the powerpivot window and connect to a sql server database. Then there is a series of steps in the lab manual which appears on the side. This instructs the user as to how to go about creating a Pivot table report along with a chart. Once i went through these steps it became evident that it is going to give the Power Users/Analysts of an organisation a self service BI component. The users can use Powerpivot to build out very useful Pivot tables/charts, it also gives the ability the user to refresh the reports/charts real time. The one thing i noticed as i was going through Powerpivot was that the interface could take some time to get used to along with plethora of other options. I guess main deal with the user interface is the office 2010 layout along with the placement of options. The lab also takes the user through on how to create a powerpivot report using data from the web. I guess with this way of extracting data from the web, it leads the way to accessing unstructured data present on the web. In the final analysis based on the initial looks Powerpivot is a powerful tool for doing BI for power users. It will be interesting to see how Powerpivot coexists with sql server analysis services in terms of how the lines will be drawn between the two tools in terms of usage.

Monday, December 7, 2009

SSRS 2008 - Tablix Control

One of the neat features available in SSRS 2008 is the tablix control. This control combines the table control and matrix control which were in the earlier versions of SSRS. These controls (Table and Matrix are still present in the current version). Matrix controls in SSRS is bit tricky to work with, tablix control on the other hand helps us generate complex reports in a efficient manner. One of the neat features available in the tablix control is presence of row groups and column groups. The row groups work like the report groups we have for the rows of data. For example in a Data set let us say we have SalesPerson,Quarters and Salesamount has the fields, once could have a row grouping on sales person on the tablix control. This could generate a report with sales amounts for different sales persons. Let us say in the same report the user wants the amounts to be broken out by quarters, one has to create a column grouping based on quarters. This way the user can display sales person and quarters on the same report with salesamount broken out by different quarters. This kind of groupin features also allows the use of total columns where in the sales amount can be summed across different column groups. This kind of combination grouping of row and column groups can be used to develop very complex reports. Below are snapshots of the tablix control:





Wednesday, November 18, 2009

SSIS - Sequence Container

SSIS is very powerful tool when it comes to ETL operations, there is always so much to learn and experiment with SSIS. One of the tasks which i had to do for a project was to create a series of tasks and organize them visually. The container gives the ability to group tasks which need to precede other tasks. For example, If you have to truncate a set of tables, delete data from some tables, once this is done, one needs to move on to Data Flow Tasks. Intially i started creating different control tasks in a sequence and connecting them through workflow. This was when i came across the control flow item called Sequence container. The neat thing about this control flow item is the ability to create a series of tasks within the container. There is a property in the sequence container which allows the developer to set the isolation level, in the SSIS package i created i set the property value for the isolationlevel to Serializable. On the whole this container allows the developer to organize a group of tasks.


Wednesday, November 4, 2009

SSAS 2008 - Member Properties

In Microsoft SQL Server 2000 Analysis Services there used to be a feature called Member properties. In SSAS 2005 and SSAS 2008 it can be achieved through a different strategy. I was learning the important aspects of user defined hierarchies and attribute relationships in SSAS 2005 and SSAS 2008 through the videos available at LearnmicrosftBI. While working with attirbute relationships, there were two properties of attributes which were discussed. In BIDS 2005/2008 for each attribute in a dimension there are properties called AttributeHierachyEnabled and AttributeHierarchyVisible. In order to achieve the member properties type effect, the AttributeHierarchyEnabled should be to False for a particular attribute. Once this is done, the dimensions and cube need to processed. In the Browser tool available in BIDS, drag over the measures and then drag over the lowest member of the dimension as values to be filtered by.
When you right click on the value there is a list of options which appear, in that choose: Show properties in Screen Tips, in that option choose Show all properties in the Report. When the user hovers the mouse over the values, the properties will be displayed as Tool Tips. This is really a neat feature which can be used to build/search on member properties.

Thursday, October 22, 2009

Source Control...TFS

One of the situations that arise while working in development teams is to keep track of code changes. Coding goes through lot if iterations and modifications, in order to handle such situations using version control becomes important. It gives the ability to store all the code related to a project in one place and provides check in/check out facility so that 2 developers do not step on to each other while modifying the same piece of code. Microsoft has a offering, one of the components that is available in the Team foundation Server and Team Explorer. The TFS server is the place we create all the projects and hierarchies as how the code is going to stored. Visual Studio 2008 can hook into the TFS using the Team Explorer. Once the TFS is setup, the developer can use the Team explorer Snap in to the get latest version of the code. The team explorer allows the developer to keep in sync with the changes in the TFS Server (version control system). I have found the TFS and Team Explorer very powerful when it comes to team code development.

Monday, October 12, 2009

ReportViewer

I spend a lot of time developing SSRS reports, recently came across a situation where i had to develop a c# application, this application had to provide links to the SSRS reports. The user base was pretty small and hence the decision to develop a application and perform a clickonce deployment solution. The report viewer control offered in the .NET framework is powerful and yet relatively simple to use. The reportviewer is part of the Microsoft.Reporting.Winforms.Reportviewer namespace. In the windows form drag the reportviewer control and drop it on the form. The control is present in the reporting category in the toolbox. Some of the properties that are useful in setting up SSRS reports to appear in the control are: these are in the Misc category.
They are LocalReport,ProcessingMode,ServerReport,(DisplayName,HistoryId,ReportPath,ReportServerUrl,TimeOut)
For the reports which are deployed on a reportserver, set the processingmode to Remote, set the reportpath to the path on the server, set the reportserverurl to the webserver having the reports. Once this is set, run the application, the report which is deployed on the server will appear on the application. The user will now be able to access the SSRS reports in the application.

Tuesday, September 29, 2009

SSRS-Keep Together

I was working on a building a SSRS report for the end users, got all the data ready and the queries worked out. Got to design the report by using the tablix control and attached the dataset to the control, the groups were created. I started to preview the report, the data was groupd by ID at the parent level and then there were the details. In the report the detail data for a one ID was spawning over several pages. This was getting difficult to understand the data on the report. Basically I wanted to keep the detailed data for a particular ID all together on one page. In the properties of tablix control, in the general section, under Page Break Options. there is an option called Keep Together On One Page If Possible. I checked this option and re-ran the report in the preview mode, this time the results were more in line with the users expectations. The detailed data for a particular ID was fiited on one page, then there was a page break to display the data for the next ID. I find this option very useful especially in complex reports where there are 2 tablix controls with the same dataset using the same grouping but displaying different parts of the detailed data.

Tuesday, September 15, 2009

SSRS 2008 - Export Report

One of the widely used features in Reporting Services is the use of Export functionality. The formats I have seen folks in the business I work for is Excel followed By .pdf files. Excel exports are very popular with analysts for obivious reasons like working with numbers and trying come up with trends. The .pdf format gets used a lot in canned reports like situations. One of the export formats which has been becoming popular with the users here is the MHTML format. This format is available in the drop down list for the export options. One of the plus points using this format it is very easy on printing and displays report without any added controls or formats, it gives the user more real estate to view the report(s).

Monday, August 31, 2009

Report Builder 3.0

In the latest release of SQL Server 2008 R2, Microsoft has released an updated version of Report Builder called Report builder 3.0. One of the interesting aspects of this tool is the ability to create map based reports. These reports are built based on spatial data available through SQL Server Database or through the ESRI Shape file. The ESRI shape file can be found on Census sites or through the Dundas Maps site.






Developing MAP based reports and dashboards have been bought to the developers tool set, this should give them the ability to provide more visual reports to the business. This also greatly enhances the value and presentation of the reports. Here is a link to report builder 3.0 tutorial.
http://msdn.microsoft.com/en-us/library/ee240845(SQL.105).aspx

Friday, August 21, 2009

SQL Server Profiler-SSAS

Usually the sql profiler tool is used for analyzing queries written against sql server databases, typically to find performance bottlenecks. This tool is used to trap sql sent by SSRS/Applications communicating with a sql server database. One of things I wanted to try after having heard about it was to use the SQL Server profiler for Analysis Services cubes (SSAS). I connected to the analysis server and created a new profile, choose the standard option. Then I checked show all events, in the events selection tab there is a category called query processing which probably houses all the important events that happen when a cube is processed. There are events which show the MDX script being executed, Getting data from Aggregation,Cache , whether a subcube is being queried, these based on the results i saw are probably important events which can be analysed.






When looking at the results generated from these events, it gives a better idea of what happens beneath the cube in terms of which dimensions are being queried. Based on the findings this aspect of sql profiler adds an another useful aspect to the already existing important features.

Tuesday, August 18, 2009

SSIS-Package Property

I had a situation where in data had to be transformed on several tables (more than 15 tables). For the data transformation based on the business rules stored procedures were used, then i ran into the next challenge. I did not want to run the 15 stored procedures in a sequential manner within SSIS package. At this point these processes were going to run in parallel in the SSIS package. One of the issues which i was going to run into was there could be more tables added to the transformation and I would need more stored procedures to be run in parallel. Here in I stepped into the property set at the package level called MaxConCurrentExecutables. This is present at the package level as mentioned before can be see here in the image below. The property is highlighted in agreen color text.


The number was changed to 20 on the property and the package execution time was better than prior to changing the value for this property. There is a definition of the MaxConcurrentExecutables at MSDN.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.maxconcurrentexecutables.aspx

Monday, August 10, 2009

Generating Reports Through WebService...

Usually once the reports are developed, they are deployed to the report server and the end users can view the report from the reporting portal. I ran into a situation where i had to perform data driven subscription kind of reports. Here the requirement at a set time of the day, the reports had to be generated in a .pdf format and stored in a network folder. In order to accomplish this, the reportexecution2005.asmx web service was used. The reference material at the MSDn site helped me guide to accomplish this effort. The .asmx file mentioned above is present in the report server. I added this file as a web reference to my project, once this was done there are methods which need to be used to generate the report. The methods are LoadReport,Render,GetExecutionInfo. The render method performs the actual rendering of the report. The output is then put out to the pdf file at the required location using the FileStream object. This whole thing was done in C# console application.

Sample Code:

ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
rs.Url = "http://myserver/reportserver/reportexecution2005.asmx";

The link to the msdn article:
http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsexecutionservice2005.reportexecutionservice.render.aspx

Tuesday, July 28, 2009

SSRS 2008...

I have been working with SSRS 2008 for a while now and have come across a couple of things. The first one seems like a bug in SSRS 2008, it has to do with the ReportItems collection. When you have columns placed on the report, the actual values are displayed in textboxes in the report. These textboxes are part of the ReportItems Collection, when one is in the ExpressionBox and you type ReportItems!, you can see a list of textbox item ID's. In the situation i ran into I typed in ReportItems!textbox40 in the expression, at preview time of the report i kept getting an error saying item not found or cannot be part part of the expression. After a few tries ,it kept getting frustrating and it was not listed in the collection, finally I put the exact name like ReportItems!Textbox40 and it worked. The issue was the case sensitivity of the name, also noticed in the ReportItems collection there are some with lowercase names and other with the first Character capitalised. I hope in the future releases these are made consistent.
One of the neat features available in SSRS 2008 is the adjacent Group feature in the tablix control.


This provides a great of flexibility in grouping, it allows grouping of the data in 2 different ways in the same control. One of the things I had to do was represent 2 parent child relationships in the same report, for this the adjacent group feature was very handy.

Friday, July 17, 2009

Data Mining...

Yesterday i had been to a Data Mining Conference in Cary,NC. The topic of presentation was about the usefulness of Data Mining and utilising the JMP Software for Data Mining. The JMP software came out of SAS, this is more aimed for the Desktop users who want to perform Data Mining tasks, there are 2 versions available 32-bit and 64-bit. One of the Key factors mentioned about Data Mining was that around 60-90% of the time is spent in the Preparation of data. The key aspect in Data mining projects is to define the Business goal/problem statement clearly. The lecture was delived by Dick De Veaux, is a Professor in the Department of Mathematics and Statistics at Williams College in Williamstown, MA. Once can google on his name to find out about his talks on Data Mining. There were Data Mining Methods Discussed which are part of the SQL Server 2008 Analysis Services Suite. The talk also bought about the differences between methods used for Descriptive and Predictive Data. For any one intesrested in the Jmp software, here is the web site: www.jmp.com.

Friday, July 10, 2009

Data Compression-SQL Server 2008

We have a sizeable data mart with around 50 tables. Initially they did not have any compression methods applied on them. In SQL Server 2008 there are two kinds of compression which are exposed at the table level: They are Page and Row compression. These techniques enable great space savings and also better query performances. One of things which i noticed was it takes lesser time to load the pages of the table into the memory when it is compressed, this has give me better query performances. In SQL Server 2008 there is a Data Compression Wizard available through SSMS. This feature allows the developers to test out the compression space savings. The Data compression option is set at the table level, this can be done at the CREATE TABLE or ALTER TABLE statements. The wizard is launched by right clicking on a table.





When you choose the Manage Compression Option, the Data Compression wizard comes up.



In this under the Compression Type dropdown there are the Page and Row Compression. When any one of these is chosen one can choose the calculate option and figure out the space savings as listed under the Current Space and Requested Compressed Space. In my Scenario i achieved more savings using Page Compression.

Tuesday, July 7, 2009

Enterprise Database Management Report...

The report which evaluates the different RDBMS vendors is available. In the report all the vendors providing relational database management systems have been evaluated, it is exhauastive in terms of the various factors which went to examining the different RDBMS offerings. In particular emphasis related to Microsoft SQL Server, here is a Quote from the report:

Microsoft: The most aggressive DBMS vendor with a strong road map. Microsoft has done reasonably well in the database business but over the past three years has shown increasing focus and commitment to going after the enterprise market. SQL Server 2005 and, more recently, SQL Server 2008 have enabled Microsoft to take market share in moderately sized to large enterprises, delivering good performance, scalability, security, and availability functionality. Five years ago, hardly any enterprises ran multiterabyte databases with SQL Server to support critical applications. Today, hundreds of enterprises are running 10-terabyte and larger transactional SQL Server databases.

For a complete read on the report: Use the link mentioned below

http://www.microsoft.com/presspass/itanalyst/docs/06-30-09EnterpriseDatabaseManagementSystems.PDF

Tuesday, June 30, 2009

SSMS-Table Changes

The other day I happened to add a column to a table using SSMS, normally i use the alter table script to do such a task. Once i added the column and tried to save the schema changes in SSMS i got an error saying Cannot save changes that require table re-creation. I digged around and found that there is an Option in SSMS to avoid such errors and enable table changes. In SSMS Under Tools -> Click On Options, a popup window appears like the one shown below. In Designers option, there is a choice for Table and Database Designers:



Under Table Options, Uncheck the box which says "Prevent Saving Changes that require table re-creation" and click the OK button.
Once this is done, this should allow table changes to be performed from SSMS, this is a new feature in SQL Server 2008.

Thursday, June 25, 2009

SQL Server 2008-Execution Plan

When writing stored procedures or writing complex queries it is pretty much a standard thing to do is the use of execution plans. The execution plans point to how the query is being executed, what kind of indexes are being used etc. In SQL Server 2008, there is an option called Include Execution plan which can be set prior to the execution of a query or a stored procedure. In SSMS this option can be found under the Query Menu option, the Keyboard shortcut is Ctr+M.

Once the query is executed, the execution plan can be see in the results window, one of the neat features in 2008 is that it points out where indexes are missing and gives recommendation on the kind of indexes that need to be created on a table in order to improve query performance.







In the execution plan, one has the option to save the plan as a .sqlplan file, also the query where the missing indexes are recommended can be edited in a new query window.

Wednesday, June 17, 2009

SQL Server 2008-Change Data Capture

One of the neat features available in SQL Server 2008 is Change Data Capture (CDC), there is also an other feature called Change Data Tracking. The Change data capture feature provides historical information for a user table by capturing data changes that were made. This feature allows the developer to indicate which columns in a particular table needs to be captured. This feature allows to build in audit capabilites which could be used for compliance purposes. In order to use CDC, one has to enable the database to perform Change Data Capture: This can be done by:
Use DatabaseName
GO
EXECUTE sys.sp_cdc_enable_db;
GO
Once this is done, One can selectively enable Change Data Capture on tables that need to be audited, this can be done by:


EXEC sys.sp_cdc_enable_table
@source_schema = N'Test'
,@source_name = N'Test_Table'
,@role_name = N'dbo'
,@capture_instance = N'tbl_audit_Test_Table'
,@captured_column_list = N'Col1Primarykey,Col2,Col3,Col4
,@filegroup_name = N'PRIMARY';
GO

One of the requirement here is that there needs to be a Primary key defined on the Source Table. Here the Test_table has Col1 has the Primary key.
This would enable CDC on the table called Test_table, the audit data would be captured in the database object called tbl_audit_test_table_CT(_CT is added to the name given in the capture instance by the system) , in order to query the data in this, the Select statement would be

SELECT Col1,Col2,Col3,Col4 FROM cdc.tbl_test_table_CT.

There are supporting functions which can be used to access data from the captured instance. MSDN and Books line have lot of information regarding these.

In order to Disable CDC on a table run this command:

EXECUTE sys.sp_cdc_disable_table
@source_schema =N'Test',
@source_name = N'Test_Table',
@capture_instance = N'tbl_audit_Test_Table';

To disable CDC on the Database, run the following command:
USE DatabaseName
GO
EXECUTE sys.sp_cdc_disable_db;
GO

Friday, May 29, 2009

SQL Server 2008-32 Bit Runtime...

We have a decent size of SSIS packages in Sql Server 2008 where in i import data from Excel Data sources. These SSIS packages run on a SQL Server 2008 box which has a 64 Bit installation of SQL Server. Since Excel is a 32 bit application, in order for the SSIS packages to work I had to use the command line option to run the SSIS packages where in i can invoke the 32 bit SSIS runtime so that it would work with the Excel Data sources. In Sql server 2008 one can use the SQL Server Integration Services option and set the 32 bit runtime check box. This option is available in the Execution Options tab. This way one can move away from the command line option. Here is a snapshot of setting:


Wednesday, May 20, 2009

New Features in SQL Server 2008...

There are Several New features in SQL Server 2008. In one of the instance I had upgraded 2005 SQL Database to SQL 2008. Then later i found out that i had to recreate the database in 2005 again. At this point i did not have SQL 2005 database backup, I was stuck for a while. Then on exploring the various tasks i found the following task which helped do my job. In the SSMS 2008, right click on the database, then click on Tasks, then click on Generate Scripts.




This will launch the Script Wizard, Choose Next, In the next screen choose the database, then choose the Choose Script Options. In this there is a option for Script for SQL Server Version, in this choose SQL Server 2005. This will generate the scripts compatible with SQL Server 2005. This would enable to create all the objects required for a SQL Server 2005 database.


There is another neat feature in this screen which the Script Data option, in case the sql server 2005 database needs to populated with Data, this can be set to true. This would enable to load a sql server 2005 database. These features came in handy when i had to go back to a SQL Server 2005 database.


Wednesday, May 13, 2009

Upgrade to SQL Server 2008...

Currently I am involved in the upgrade of Sql Server 2005 databases to Sql Server 2008. I did an inplace upgrade on the SQL Server 2005 Database to 2008, the whole process was seamless. Prior to upgrading i stopped replication and dropped all publications and subscriptions. During this upgrade process i had to upgrade SSIS packages in 2005 to 2008. In SSIS 2008 there is a upgrade wizard which helps one to migrate 2005 packages to 2008.
When I opened up BIDS(Visual Stusio 2008) and selected my SSIS 2005 Solution, the upgrade wizard popped up.


When I clicked the Next Button, it came up with the list of packages which needed to migrated. The window had three columns Existing Package names, upgrade package name and passowrd. In case the upgrade package name needs to be changed it can be changed here.


In the next screen is where there is a couple of important options. One of the things that has changed between ssis 2005 ans ssis 2008 is the connection strings to use new provider names. There is a checkbox which provides the option to do the conversion by the wizard itself. Having this box checked is highly recommended, this would make the upgrade process a lot more easier. There is a option to validate pacakges after upgrade and one for backing up original packages.

In the next screen one gets a summary of what needs to be done, once you hit finish the upgrade process starts and provided warning messages/errors once it is complete.

One thing i noticed after the upgrade was if i were using configuration files from SSIS 2005 in SSIS 2008, in some cases i got a XML badly formed error when i executed the package in SSIS 2008. In such cases i recreated the XML config file in SSIS 2008 environment.

Monday, May 4, 2009

SSIS 2008...

One of the challenges i was facing while developing SSIS packages was how to move around the packages through the different environments. There is a enable configuration option in the SSIS designer /BIDS. There are different kinds of configurations avaialble. They are:

XML file Configuration
Parent Package Variable
Environment Variable
Registry Entry
SQL Server





The options I choose were XML configuration and the SQL Server method. In the XML configuration option, In the Configuration wizard, Once has to choose the location for the XML File, Once that is done in the next screen, the properties for the different connection objects can be saved. This would typically contain the connection strings for different databases. In the final screen one can see all the different properties that are going to save in the XML file. Once this is complete the XML file will contain the important properties needed for the package. When the package is executed it uses the XML file to set the different connections. While moving across environments one has to create am XML file for QA and Production with the connection strings modified accordingly.

There are various other blogs across the web which describe the SQL Server method of configuring, In this case the configurations are stored in a table within a sql server database.

Thursday, April 30, 2009

SSRS 2008/Visual Studio 2008...

I recently installed Visual Studio 2008 with SP1. The service Pack1 for Visual Studio 2008 took around 45 Min's. Once I finished that I installed SQL Server 2008 with BIDS, this requires Visual Studio 2008 SP1 to be present. I had a bunch of 2005 Sql server 2005 Reporting services reports which I wanted to migrate to SSRS 2008. When I started the process i ran into errors, it was a weird error. In SSRS 2008 it looks like one cannot define data sources with spaces in it. For example I had a data source Called Dev B, SSRS 2008 had problem with it. To fix this issue i renamed the Data source As DevB, this fixed the issues. Once the SSRS BIDS studio is attached to Source control, there was a new feature which I noticed, it was called get latest version on Check out. I will blog more about this and other features once i explore them.

Wednesday, April 29, 2009

Hoshin

Hoshin is used as a management tool in many big corporations around the world. So what does Hoshin actually stand for? As per wikipedia it is defined as a method devised to capture and cement strategic goals as well as flashes of insight about the future and develop the means to bring these into reality. This method was initially developed by Dr. Yoji Akao that uses a Plan-Do-Check-Act to create goals. The purpose of hoshin is to help an organization which is listed below:
Focus on a shared goal
Communicate that goal to all leaders
Involve all leaders in planning to achieve the goal
Hold participants accountable for achieving their part of the plan
In Japanese, hoshin means shining metal, compass, or pointing the direction, this technique has/is being used by companies like Toyota,Hewlett Packard and Bank of America to name a few.

Business Intelligence...

Business Intelligence is an area of specialisation which has seen growth in the past several years and continues to grow. So what is Business Intelligence, Simply put it basically is aimed at providing information for what a Business whats to know and when the business wants to know it. Business Intelligence also encompasses the field of Data Mining which is aimed at unearthing hidden patterns in the data which has gathered by a particular entity. This provides the business with the capability of predicitng certain trends in the particular business domain. For example Data Mining techniques are/can be used for Direct Marketing Campaigns,Fraud Detection Techniques to name a few. There are lot of tools and techniques which facilitate Business Intelligence and Data Mining, for examples Vendors like Microsoft,Oracle,IBM provide Business Intelligence Tools. There are techniques like Neural Networks,Decision Trees,Machine Learning which are part of the Data Mining Domain.
There is an other important aspect of Business Intelligence which are Reports,DashBoards,Scorecards. These provide a Business a visual outlook of Key Performance Indicators,Trends and Projections which in turn a gives a good birds eye view of overall Busniess Performance. To summarise, Business Intelligence when utilised in a proper manner will provide very good benefits and can be a Value Add for a Business/Organisation.

iPhone Vs gPhone






In the smartphone market space the latest entrant has been Google's g-Phone. This phone has been launched with the aim of providing an alternative to the very popular Apple's iphone. iPhone has had a headstart here since it was lauched well before the G-phone came to the market. iphone had the platform of the successful ipod from apple, this made the lauch of iphone one of the most anticipated. Given that we have these options now how do these two phone match up with each other. There are bound to be lot of opnions regarding these two phones raging from usability experience to technical performance and reliability. In order to make things easire i am enclosing a link which gives overall comparison between the two phones.
http://apple20.blogs.fortune.cnn.com/2008/09/24/g1-vs-iphone-the-tale-of-the-tape/
Hopefully the above link will give a headstart for folks wanting to buy one these 2 devices/phones.

Blackberry...


These days smartphones have invaded the marketplace in large numbers. The customer is presented with varied styles of smart phones from different vendors. One of the Vendor which has an impressive array of smartphones is RIMM based in Toronto,Canada. The Blackberry was one of the first smartphones to bring in the combined use of e-mail and cell phone, it became instantly popular in the corporate world as it enabled business travellers to keep in touch with their respective business. Initially blackberry smartphones used keyboard form of inputs. It takes a while for new user to get used adjusted to typing in using the blackberry. As the years went by which increased competition from apple , Microsoft based Windows Mobile phones and presently with google's g-phone, RIMM has been rolling out newer models of blackberry into the market. The latest models to infiltrate the market place have been Blackberry Storm,Blackberry Bold and Blackberry Curve. Each of these phone have bought different feature sets to the marketplace. With Blackberry bold it was the wide display which meant more reading space,

The Storm model was the first blackberry model to bring in touch screen to the Blackberry world keeping in touch with competition from the iphone.
For more detailed reviews on different Blackberry models, please refer to this link.
http://www.mobiletechreview.com/BlackBerry-Reviews.htm

Smokey Mountains...

For my son's spring break we had made a trip to the smoky mountains along with our friends and stayed in a log cabin. The log cabin was situated near Pigeon Forge, TN. It was located in the mid mountain area, with lot of greenery around with spectacular views of the mountain. The ride to the cabin everyday after our daily routine was interesting.
Staying in log cabins is a great way to save money on accomodation for large groups of people. For example if 2 or more families are planning a trip, log cabins can be a great way to save. When I travelled we had a total of 8 people (2 families) stay for 4 nights. The total expense was 260 dollars which worked out to around 65 dollars a night. These cabins have fully furnished kitchen which means money can be saved on food expenses. During vacations food expenses tend to be very high, when planned in advance, one would take certain food items which could be cooked. The smokey mountain, pigeon forge area has a large amount of log cabins, the place where we stayed was at:
http://www.blackbearlodging.com/ (BlackBear Lodging,Pigeon Forge,TN)

Report Builder 2.0

Report Builder is an adhoc reporting tool which is available of the Sql Server Reporting Services. This tool would enable an user/analyst to build reports on adhoc basis. There has been a significant improvement with the Report Builder 2.0 which is available as part of SQL Server 2008. In this version the builder is more robust, almost mimics the actual Report designer environment in visual studio. There is the new tablix control along with lots of charts and gauge controls. The gauge and chart controls have been incorporated from Dundas software. The graphics on these controls is far superior to the native charts which were available in sql server 2005. With the new gauge controls one can build professional looking score cards and dashboards. The tablix control is a combination of the regular table and the matrix control, this allows user to create row and column groupings, this allows user to produce complex reports on a adhoc basis very quickly. With this version the user can export the report to Microsoft word format. The tool can be downloaded from the following Microsoft website.

http://www.microsoft.com/downloads/details.aspx?familyid=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en