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.

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.

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}
}

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.

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:

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.

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.