SSIS Junkie : SSRS

SSIS Reporting Pack – a performance tip

jamiet — Mon, 05 Nov 2012 22:36:41 GMT

SSIS Reporting Pack is a suite of open source SQL Server Reporting Services (SSRS) reports that provide additional insight into the SQL Server Integration Services (SSIS) 2012 Catalog. You can read more about SSIS Reporting Pack here on my blog or had over to the home page for the project at http://ssisreportingpack.codeplex.com/.

After having used SSRS Reporting Pack on a real project for a few months now I have come to realise that if you have any sizeable data volumes in [SSISDB] then the reports in SSIS Reporting Pack will suffer from chronic performance problems – I have seen the “execution” report take upwards of 30minutes to return data. To combat this I highly recommend that you create an index on the [SSISDB].[internal].[event_messages].[operation_id] & [SSISDB].[internal].[operation_messages].[operation_id] fields. Phil Brammer has experienced similar problems himself and has since made it easy for the rest of us by preparing some scripts to create the indexes that he recommends and he has shared those scripts via his blog at http://www.ssistalk.com/SSIS_2012_Missing_Indexes.zip. If you are using SSIS Reporting Pack, or even if you are simply querying [SSISDB], I highly recommend that you download Phil’s scripts and test them out on your own SSIS Catalog(s).

Those indexes will not solve all problems but they will make some of your reports run quicker. I am working on some further enhancements that should further improve the performance of the reports. Watch this space.

@Jamiet

Build-time dependency resolving coming to Entity Framework. Now, how about those BI tools too?

jamiet — Tue, 07 Dec 2010 10:04:00 GMT

Three months ago I wrote a blog post entitled Some thoughts on Visual Studio database references and how they should be used for SQL Server BI where I shared some thoughts on a feature available to database developers in Visual Studio 2010 that I would love to see added to SQL Server Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS). In there I said:

Over the past few weeks I have been making heavy use of the Database tools in Visual Studio 2010 and one of the features that has most impressed me has been database references.

Database references allow you to have stored procedures in your database project that refer to objects (tables, views, stored procedures etc…) that exist in other database projects and hence when you build your database project it is able to resolve those references.

It occurred to me that similar functionality would be incredibly useful for SQL Server Integration Services(SSIS), Analysis Services (SSAS) & Reporting Services (SSRS) projects. After all reports, packages and data source views are rife with references to database objects – why shouldn’t we be able to have design-time dependency checking in our BI projects the same way that database and .Net developers do?

In that blog post I shared links to three Connect submissions where I requested this feature be added to SSIS, SSAS & SSRS. In addition I also submitted a request that the feature be extended to .Net projects so that any reference to a database object in a .Net assembly can be resolved at build time. That Connect submission is at [Entity FX] Use database references to constrain the EDM and overnight it received this comment from Microsoft:

We have been working on this feature for a while and and will be available soon

This is really good news - it improves the Microsoft developer ecosystem by ensuring invalid references to database references get caught at build time (ideally as part of a Continuous integration build) rather than run time. [Hopefully it might nip this code-first nonsense in the bud too (Ooo...way to incite flame comments :) ) ]. If you want to see this feature in action then check out a video from Teched Europe last month entitled SQL Server Developer Tools Code-named "Juneau" where it is demo'd by Lance Delano and Tim Laverty.

The point of this blog post though is not just to draw attention to this forthcoming feature for .Net developers, it is to ask you to petition Microsoft to get this feature added to SSIS/SSAS/SSRS too. After all, we already know (from the video above) that the feature is coming to this new code-name Juneau development environment plus we also know that Juneau will be the development environment for SSIS/SSAS/SSRS as well - is it really much of a stretch to expect the BI tools to have access to this great feature too? I don't think so and if you agree with me then I urge you to vote and add a comment to the Connection submissions that are requesting this feature. They are at:

[SSAS] Declare Object Dependancies
[SSRS] Declare Object Dependancies
[SSIS] Declare Object Dependancies (Update, Apparently someone at Microsoft has deemed it necassary to set this to private and I am not able to change it back even though I submitted it. You can still vote on the other two though.)

Let's close that SQL Developer Gap!

@Jamiet

Convert an integer seconds value into words in VBScript : Reporting Services

jamiet — Sun, 14 Nov 2010 22:36:00 GMT

Occasionally I stick random bits of code up on this blog because (a) I know I’ll need it again someday and (b) maybe it’ll be useful for someone else. This particular VBScript function called TimeInWords can be used in SQL Server Reporting Services to turn a numerical value representing number of seconds into a meaningful string. For example:

TimeInWords(53) returns “53s”
TimeInWords(1632) returns “27m 12s”
TimeInWords(7923) returns “2h 12m 3s”
TimeInWords(894823) returns “10d 8h 34m 43s”

Pretty useful for reporting on durations. I haven’t done endless testing on it but I’m pretty sure it works. Here’s the code:

Function TimeInWords (RemainderTimeInSeconds as Integer) AS String
    Dim ReturnValue As String
    IF (RemainderTimeInSeconds > 86400)
        ReturnValue = TimeInDays(RemainderTimeInSeconds )
    ELSE IF (RemainderTimeInSeconds > 3600)
        ReturnValue = TimeInHours(RemainderTimeInSeconds )
    ELSE IF (RemainderTimeInSeconds > 60)
        ReturnValue = TimeInMinutes(RemainderTimeInSeconds )
    ELSE
        ReturnValue = TimeInSeconds(RemainderTimeInSeconds )
    End If
    Return ReturnValue
End Function

Function TimeInDays (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 86400)) + "d " + TimeInHours(RemainderTimeInSeconds Mod 86400)
End Function
Function TimeInHours (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 3600)) + "h " + TimeInMinutes(RemainderTimeInSeconds Mod 3600)
End Function
Function TimeInMinutes (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(Floor(RemainderTimeInSeconds / 60)) + "m " + TimeInSeconds(RemainderTimeInSeconds Mod 60)
End Function
Function TimeInSeconds (RemainderTimeInSeconds AS Integer) AS String
    Return CStr(RemainderTimeInSeconds) + "s"
End Function

Nothing particularly special but if it saves someone else from writing it well…why not!

Hope that is useful to someone.

@Jamiet

Some thoughts on Visual Studio database references and how they should be used for SQL Server BI

jamiet — Tue, 14 Sep 2010 12:53:00 GMT

Over the past few weeks I have been making heavy use of the Database tools in Visual Studio 2010 (formerly known as datadude, DBPro, VSTS for Database Professionals or one of a plethora of other names that it has gone by over the past few years) and one of the features that has most impressed me has been database references.

Database references allow you to have stored procedures in your database project that refer to objects (tables, views, stored procedures etc…) that exist in other database projects and hence when you build your database project it is able to resolve those references. Gert Drapers has a useful introduction to them at his blog post Database References; in his words database references allow you to:

represent and resolve 3 and/or 4-part name usage inside a database project. Database references are conceptually the same as assembly references inside a C# or VB.NET projects; they allow you to reference objects from your database project that live inside another namespace (database).

It occurred to me that similar functionality would be incredibly useful for SQL Server Integration Services(SSIS), Analysis Services (SSAS) & Reporting Services (SSRS) projects. After all reports, packages and data source views are rife with references to database objects – why shouldn’t we be able to have design-time dependency checking in our BI projects the same way that database and .Net developers do?

Here are some examples of ways in which this would be useful:

If a SSRS report pulls data from a view, I firstly want to know (at design-time) that that view exists. Secondly, if the view definition changes I want to know that my report will still work.
Similarly for a SSAS cube, if a view referenced in the DSV changes or disappears I want my SSAS project build to fail or succeed accordingly
Similarly for a SSIS Execute SQL Task, if it executes a stored procedure I want to know that the named parameters that I am using actually exist in the stored procedure.

To this end I have submitted three identical suggestions to Connect, one each for SSIS, SSAS & SSRS:

[SSAS] Declare database object dependencies

[SSRS] Declare database object dependencies

[SSIS] Declare database object dependencies

I have said before in my blog post The SQL developer gap that we database and BI developers deserve as much love from Microsoft as our .Net counterparts do and I’m of the opinion that providing design-time dependency checking across the full gamut of BI projects would be a huge step in the right direction. The holy grail is an object dependency graph from database tables all the way up through database views, database functions, stored procedures, DAL, ORM layer & business logic layer all the way through to presentation – I don’t know how long it will take but we will get there one day I am sure, hopefully sooner rather than later!

@JamieT

UPDATE: This feature (i.e. the ability to define dependencies on database objects at build time) is coming to the Entity Framework as confirmed in this Connect submission: http://bit.ly/igmEiS. Let's hope the same comes to SSIS/SSAS/SSRS soon.

The SQL developer gap (Warning: rant coming up)

jamiet — Tue, 12 Jan 2010 23:43:21 GMT

I recently submitted a bug to Microsoft Connect concerning the behaviour of the expression language in SSRS, if you’re interested you can read about it here: DateAdd() doesn't throw an error when it has invalid parameters.

I got a swift and detailed reply (for which I am grateful) from a fellow on the SSRS team however the content of it wasn’t exactly music to my ears:

While I do agree it would be desirable to have an error here, the SSRS engine makes use of the VB code engine for functions such as this. So, the call to DateAdd is just a passthrough as far as SSRS is concerned. The majority of the functions available in SSRS are actually just VB ones.

In other words the inequities of SSRS’s expression language are down to the frailties of a 14 year old scripting language that should have been thrown out long ago on the coattails of Crystal Reports. Am I the only SQL Server BI developer that feels a little … oh I don’t know … unloved sometimes by the various BI teams in SQL Server land. Let’s have a look at some of the things that we have to put up with:

two different expression languages (one for SSIS, one for SSRS), neither of which support comments as far as I am aware, neither of which have any debugging features and neither of which are extensible
an expression language in SSRS that is based on a dead scripting language and rarely produces meaningful errors
an expression editor in SSIS for which there isn’t even context-sensitive help let alone intellisense
an MDX query editor in SSMS that doesn’t have intellisense
a script task editor in SSIS that shows read-only system variables in the ReadWriteVariables dropdown
I could go on…

None of these issues are particularly prohibitive on their own but when you add them all up (along with many others) I’ve no doubt that we could all be a heck of a lot more productive were they all solved.

I look around at our cousins in .Net land and I see their fancy WPF code editors, extensible languages, Javascript Intellisense, code navigation shortcuts, mockable code, code style checkers, MEF-compliant IDEs, type-inferencing, 3rd-party code editors, keyboard shortcuts, modern type-safe compilers, code generation templates, meaningful error messages, proper debuggers, runtime watches, collapsable code regions, IDEs with built-in refactoring support, built-in code formatting, code documentation tools, whitespace highlighting, code performance analysis tools, LINQ, fluent this-that-and-the-other etc… and I can’t help but feeling a little hard done by. (Anyone that has used Resharper for .Net will know exactly what I mean.) Am I the only SQL guy that feels like a second class citizen in the Microsoft developer ecosystem?

I must be fair and admit that we have it a lot better than other database platform developers (if you have ever used any of the plethora of Oracle developer tools out there you will know what I mean) but nonetheless it would be nice to see a little bit of love go the way of us lowly SQL bods don’t you think?

OK, rant over. My apologies if you came here hoping for some words of wisdom but I’ve been wanting to get this off my chest for ages; normal service will be resumed forthwith!

@Jamiet

P.S. A valid response to this from the SQL Server team may be “submit your ideas to Connect”. I’d like it to be known that the majority of things I have listed above have already been submitted to Connect in some way shape or form and I would be happy to supply a list if one were required!

I should also apologise to the SSRS team for making an example of them – the submission highlighted above is hardly the most annoying of problems listed here but it just happened to be the one that pushed me over the tipping point to writing this!