Wednesday, June 30, 2010

Attribute RelationShips...SSAS 2008

One of the features that has been expanded upon in SSAS 2008 when compared to SSAS 2005 is the concept of attribute relationships. In SSAS 2008 there ia Attribute realtionships editior which is GUI based. It allows the developer to explicity to define the realtionships between the diferent attributes in a dimension. This concept is very important to be understood while building a dimension as this has an effect on the performance of the cube. As per MSDN: attribute relationships offer the following performance advantages:

An attribute relationship provides the following advantages:

• Reduces the amount of memory needed for dimension processing. This speeds up dimension, partition, and query processing.
• Increases query performance because storage access is faster and execution plans are better optimized.
• Results in the selection of more effective aggregates by the aggregation design algorithms, provided that user-defined hierarchies have been defined along the relationship paths.

Attribute relationships are sometime inherent and sometimes it is user defined, this would happen while defining ragged hierarchies.

Monday, June 28, 2010

Data Export/Import...

Usually I use SSIS to perform Data Import/Export type of activities. Recently I ran into a situation where I had to use the bcp utility to perform the export/import data activity task. The schema's of the the databases involved were identical, the databases were on different physical servers. Initially i used the bcp out command to export the data to text files (with .txt extension), once this was complete I set up the bcp in for the import of data. To my surprise i found that there were errors while importing the data. I expected all of the data to go in since the table definitions were the same, I tried using different flags on the bcp still i did not get it to work, the flag -n was set on the export and the import. At this point i tried a different command to perform the import, I used the BULK INSERT command. The Data Import worked fine, the counts of the source and the destination matched perfectly, the content was intact as well. The import option which i had to use was the Datafiletype = native.

BULK INSERT Testdb.dbo.Test_Table FROM "c:\TestImport\Test\test_table.txt" WITH (DATAFILETYPE ='native')

GO

Ram's Blog: SSRS-Backgorund Colors-Group Variable...

Ram's Blog: SSRS-Backgorund Colors-Group Variable...

Thursday, June 24, 2010

SSRS-Backgorund Colors-Group Variable...

I was recently working on creating a SSRS report in SQL Server 2008 which required alternating background colors. Initially it seemed a easy requirement but as the report got built out it had a row group and a column group which made the getting background colors tricky. Initially I went with the RowNumber and Running value functions, it did not get the required effect. In an earlier blog post i had mentioned the use of DENSE_RANK() function in a stored procedure to get the background color effect. I recently came across another approach which is using Group variables(I would like to acknowledge the MSDN forums and blogger's who have mentioned this approach), which works very well. First you would need a custom code function which would go in the Report Properties code section:

Public EvenRow as Boolean
Public Function AltRow() as Boolean
EvenRow = Not EvenRow
Return EvenRow
End Function

Once this is done, In the group properties (this would be the data point/column in the dataset for which we need the alternate row color), under variables create a variable called AltRowColor, in the value section call the function =Code.AltRow()

Select the whole row for which you need the alternate row color, in the background color property enter the following expression: =iif(Variables!AltRowColor.Value,"Gainsboro","White"). Once this is completed run the report you should see the alternate row colors. This was done in SSRS 2008 with the tablix control which has Row and Column Groups.

Reports Drill Down

When developing reports, I have  usually would have to develop summary and detail reports. On the summary report I would have to have hyperlinks for certain data points to drill down in the detail of the data. For the drill down of reports I tend to use the Javascript:windows.open function. I usually will have a function created which would build build the detail report path along with the parameters that is required for the detailed report. I ran into a situation where when i clicked on a data point to open the detailed report, i kept getting an error with the link which i was trying to build to open the detailed report. I investigated the issue, the function, the windows.open syntax everything looked fine. I did not get any errors while trying to deploy the report, it was taking a long time to fix the issue. Then i started to look at the parameter values that were being passed, then my colleague who was working with me on the report stumbled into the issue. The problem was that there was data for one of the parameters which had a + sign embedded in it. This caused HTML to think of it as a concatenation character, which was throwing off the report path. The source of data for this parameter was fixed, the + was removed, the drill down started to work fine. While sourcing the data it is important to profile the data and scrub it, remove any unwanted special characters in the data and keep the data clean. In a nutshell Data Validation and integrity is very important otherwise one would never know where problems might arise.

Friday, June 18, 2010

Report Templates

One of the most important aspect of report development in a Business/Corporate setting is delivering reports with consistent look and feel. In order to solve this problem, one can create standard report templates which have a standard company logo, Page header and page footers. In these templates one can also include any customcode that is standard across all reports. There can also be managed code/dll references which has standard function calls that can be used during report development. These templates are usually stored in the following path (For visual Studio 2008): C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject. The use of templates really in standardized report development, bringing a consistent look and feel for reports. In SQL Server 2008 R2 there are features like report gallery where one can store standardized images/logos and report parts.

Friday, June 11, 2010

SSRS-Backgorund Colors...

I was recently working on creating a SSRS report in SQL Server 2008 which required alternating background colors. Initially it seemed a easy requirement but as the report got built out it had a row group and a column group which made the getting background colors tricky. Initially I went with the RowNumber and Running value functions, it did not get the required effect. I was researching this and found a a discussion on MSDN where in one of the moderators had suggested the USE of DENS_RANK() function in the sql being used to create the DataSet. In my dataset there are multiple records appearing for a single state spanning different months. The Use of the DENSE_RANK() fuction allowed me to persist the Rank across multiple occurences of months for a singlke state. The Row_num column was created as part of the result set and sent it back to the DataSet in SSRS. For example: SELECT a,b,c,DENSE_RANK() OVER (ORDER BY state) AS Row_Num.
In the Report on the row of the tablix control, in the background property I used the expression =iif(Fields!Row_Num.Value Mod 2, "Gainsboro", "White"). This gave me the required alternate color effect on a tablix report with a Row and Column group.