Monday, January 25, 2010

SSIS 2008

I was working on a Script Task in SSIS 2008, the purpose of the task was to traverse a folder and list the files in the folder. The language used for the script was Visual C#2008, in the script window I finished the script and saved it. Once i was back in the control task there was red cross sign in the script task, the error message said binary code not found. I have come across this error in sql server 2005, there were fixes issued for this by Microsoft, also there was a property for the script task in 2005 which was called precompilebinaryintocode (something like that) which had to be set for the error to go away. In 2008 that property is not present, I was puzzled initially as to how to fix the issue. I opened the script task, clicked on the edit script button to get into the script editior. In the menu for the script editior there is a Build menu option, when i clicked on it, I choose the script which had to be built.


The script name will be something like st_cxxxxxxxxxx, once i built the script, there were compilation errors. I fixed the errors in the script and saved it, when i got back to the script task in the main control task window, the binary code error went away. Based on this, it would be a good idea to build script, fix any errors and save the script, this could be one of the ways to prevent the Binary code not found error.

Tuesday, January 19, 2010

Wide Table...

I had a SSRS Report in SQL Server 2008 which using a table which a lot of columns. The columns in the table kept getting added as requirements kept changing/modified. I hit a point where the report performance slowed and decided to investigate the root cause. Ideally this situation should not have arised if the design of the table was done properly. The table which SSRS report was using had more than 300 columns, the reason this happened table was designed as a flat table. I decided to break this table out into a category master table and a category metrics table. The master table was a like a dimension table which had all the category id's and the corresponding category names. The metric(fact table) had the id's and the metrics for each id. In order to populate these table i wrote a stored procedure and this stored procedure also had to feed the data to my report. Given that my inital flat table with the lot of columns was populated, the performance of the report using the  dimension fact table setup was very much faster. The time that the report was taking earlier before the design was close to a minute, after the table split the run time was around 15 seconds. Based on this exercise what i learnt was that it is not necessary a wide flat table with huge number of columns will give better performance.

Tuesday, January 12, 2010

Parameters in Report Builder...

Report Builder 2.0: When developing reports, there are situations where on needs to create parameters. The purpose of parameters is allow the user to generate reports for a specific set of values. One of my colleague had to build out a report using report builder 2.0  and wanted to pass an ID as a parameter. In this situation the SQL was going to be executed as a query. At first i tried to create a parameter in report builder and defined all the necessary attributes for the parameter. The stumbling block was how to pass the parameter to the SQL Statement in the dataset being created, again we were not using a stored procedure. In the sql query as part of the where clause, i changed the clause to something like Where CustomerID = @CustomerID. When i used the placeholder @CustomerID in the where clause, this caused report builder to automatically create a parameter called CustomerID. Once this was complete at run time of the report there was a prompt requesting the user to input the CustomerID. So the query in the dataset looked like
SELECT CustomerName,CustomerCity,CustomerAddress
FROM Customer
WHERE CustomerID = @CustomerID

Saturday, January 9, 2010

BI Conference...

Hi folks,
There is going to be a Microsoft Business Intelligence Conference this year, the details are provided in the link below:
http://blogs.msdn.com/usisvde/archive/2009/12/28/mark-your-calendars-for-microsoft-bi-conference.aspx.
Conferences like the one mentioned are a good place to keep in touch with current and future developments in BI, also provide a good opportunity to network with other BI professionals. One of the issues which I try to focus on is how the featires/developmets mentioned in the conference can be used in the Company/Organization I work for.

Thursday, January 7, 2010

Replication...

I use replication in SQL Server 2008 extensively, the snapshot and transaction replication methods are used the most. The situation where it is used is that i  replicate production database tables to other (destination) database on a different server, this (destination) database is used as the source to perform all the data transformations. In this way the OLTP production (source) database is not overloaded, while replication was running recently there were some errors. These errors caused replication to be backed up meaning there were rows which were not being pushed down to the subscribers. In the replication monitor, Under the All subscriptions tab right click on the status column corresponding to the subscriber where the problem is occuring. In the window that opens up, click on the distirbutor to subscriber history.  Click on the error line, the error details appear in the bottom section of the window. The error message displayed here is not really helpful, all it does is it shows the Command attempted, along with the Transaction sequence number, note the transaction number which can be seen in the window below:




There is a stored procedure that be used to which we need to pass the transaction sequence number. The result of the stored procedure will display the actual sql command that was being attempted on the subscriber database.
Here is the stored procedure: (This has to be run on server which serves as the distributor, the distribution database):
The SQL below expects a start and end sequence number. Grab the value for [xact_seqno] and plop it into the parameters for the stored proc below. The start and end sequence number needs to be the same.

EXEC Distribution..sp_browsereplcmds @xact_seqno_start = '0x0018B73A00003BCF000800000000', @xact_seqno_end = '0x0018B73A00003BCF000800000000'
The result set contains a column called command which would have the actual Sql command.
I found the above command to be very useful for debugging errors that happen during replication.

Wednesday, January 6, 2010

Typography...

I was getting ready to blog about sql server related topic, incidentally i got diverted into a totally different topic while going through MS BI group on Linked In. It had to do with the aspect of presentation design and how content need to laid out for reports and presentations. The link which i am providing below provides very useful ideas and tips in the area of typography. I found the topic very interesting and how one can miss out an important aspect, presentation. This site discusses about typography and also give suggestions for books on the same subject:
http://www.thedesigncubicle.com/2008/12/10-common-typography-mistakes/
As working professionals care should be taken while presenting, the topics discussed in the above site also include Logo Design.