Search results matching tags 'sql server' and '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

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.

T-SQL Tuesday #005: Creating SSMS Custom Reports

Mike C — Tue, 13 Apr 2010 00:14:00 GMT

This is my contribution to the T-SQL Tuesday blog party, started by Adam Machanic and hosted this month by Aaron Nelson. Aaron announced this month's topic is "reporting" so I figured I'd throw a blog up on a reporting topic I've been interested in for a while -- namely creating custom reports in SSMS.

Creating SSMS custom reports isn't difficult, but like most technical work it's very detailed with a lot of little steps involved. So this post is a little longer than usual and includes a lot of screenshots. There's also a downloadable ZIP file with the projects from this article included.

SSMS Custom Reports

SQL Server 2008 and 2005 both offer custom report functionality in Management Studio. With a little bit of work you can create custom SSMS reports just like the standard reports that Microsoft ships with Management Studio. The Disk Usage Report shown below is just one of the many standard reports that comes with SSMS.

Creating a Report Project

SSMS uses the SQL Server Reporting Services (SSRS) 2005 client/viewer control to render both standard and custom reports. This is true for both SSMS 2005 and SSMS 2008. So the first step to building a custom SSMS report is to fire up Visual Studio 2005 and create a new Report Server Project as shown below. For this example we'll create a custom report that lists missing indexes, so give the project the name Missing Index.

Once you create the Report Server Project right-click on Reports in the Solution Explorer and choose Add > New Item...

When the Add New Item box appears, choose the Report template and give the report a name. For this example I named the report Missing Index.rdl - the .rdl extension stands for "Report Definition Language", and is the standard extension for SSRS report definition files.

Defining the Report Dataset

Once the report is added to your project you have to add a new dataset to the report. The dataset defines the structure and content of the source data that will populate your report. Choose <New Dataset...> from the Dataset: dropdown.

Visual Studio will respond with a Data Source box. Just make sure the Type: dropdown is set to the default Microsoft SQL Server and put Data Source=. in the Connection string: box. This is all that's required since the SSMS custom report you're creating will use connections created and managed by SSMS's Object Explorer.

After you define the data source, you can define the SQL query that will populate your report. Just put the query in the dataset window as shown below.

I borrowed (and slightly modified) the following query from Brent Ozar. He originally published it at SQLServerPedia. This particular query uses SQL Server's missing index Dynamic Management Views (DMVs) to identify missing indexes.

-- Begin missing index query

WITH cte
AS
(
    SELECT mid.object_id AS object_id,
        QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id)) AS table_schema,
        QUOTENAME(OBJECT_NAME(mid.object_id)) AS table_name,
        QUOTENAME('IX_' + OBJECT_SCHEMA_NAME(mid.object_id) + '_' +
            LEFT(CAST(NEWID() AS CHAR(36)), 8)) AS index_name,
        mid.index_handle
    FROM sys.dm_db_missing_index_details mid
)
SELECT
    DENSE_RANK() OVER
    (
        ORDER BY cte.table_schema, cte.table_name
    ) AS table_color,
    ROW_NUMBER() OVER
    (
        PARTITION BY cte.table_schema, cte.table_name
        ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC
    ) AS index_color,
    cte.table_schema,
    cte.table_name,
    cte.index_name AS index_name,
    (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS calc_impact,
    'CREATE NONCLUSTERED INDEX ' + cte.index_name + ' ON ' +
    cte.table_schema + '.' + cte.table_name +
    ' (' + COALESCE(mid.equality_columns, '') +
        CASE WHEN mid.inequality_columns IS NULL
            THEN ''
            ELSE CASE WHEN mid.equality_columns IS NULL
                THEN ''
                ELSE ','
                END + mid.inequality_columns
            END +
    ') ' + CASE WHEN mid.included_columns IS NULL
            THEN ''
            ELSE 'INCLUDE (' + mid.included_columns + ')'
            END +
    ';' AS create_stmt,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
        AND mid.database_id = DB_ID()
INNER JOIN cte
    ON cte.index_handle = mid.index_handle
WHERE migs.group_handle IN
(
    SELECT group_handle
    FROM sys.dm_db_missing_index_group_stats WITH (NOLOCK)
)
ORDER BY calc_impact DESC;

-- End missing index query

There are a couple of caveats to keep in mind about the missing index DMVs that this query relies on:

They're not designed to be exhaustive. If you want to get a serious analysis of useful indexes in your databases, you definitely want to use a tool that will perform a more extensive analysis.
The DMVs only persist their information since the last time the SQL Server service was restarted. If you've recently restarted the service you'll get very little information back. These DMVs are best to use when your server has been running under normal load for a while.
The suggestions returned by the DMVs aren't always the best way to go. These DMVs often recommend suggested indexes with lots of overlap (lots of columns in common, often in similar order).

To put it another way, don't take the raw information returned by these DMVs at face value. The information they return is definitely useful for determining where indexes might provide benefit if your system has been running under normal load for a while, and the redundant index suggestions tend to indicate which indexes might be most useful. But these DMVs just amount to a starting point for analyzing your indexing needs. I'd recommend against creating dozens of redundant indexes based on the raw output of these DMVs.

Visual Studio will normally populate the dataset fields. If for some reason it doesn't, you can manually edit the fields by hitting the Edit Selected Dataset button to pull up the Dataset window. You can then enter the field names on the Fields tab if they aren't already populated.

Building the Report

So far most of what we've done is just setup. With this done, it's time to design and build the actual report. For this, click on the Visual Studio Layout tab to get to the report designer surface. You can drag and drop text boxes, charts, tables, images and any other controls from the Toolbox onto the designer surface. To keep it simple we'll just drag a text box and a table onto the designer.

Put the title of the report in the text box (in this case "Missing Index Report") and format it to your liking. The table we dragged onto the designer surface has three columns by default. For this example we want six columns total. To add more columns right-click on the top border of the table and choose Insert Column to the Left to add a new column. Repeat two more times.

In the Header row of the table we'll type in the headers for each column like the following:

In the Detail row we'll put in the formulas to populate the database fields like this:

SSRS formulas begin with the equal sign (=). Fields from the dataset are referenced directly using the format Fields!field_name.Value.

Deploying and Running the Report

At this point use Visual Studio to build the project. Once it builds without error, navigate to the project directory in Windows Explorer and copy the Missing Index.rdl file to the SSMS Custom Reports directory (on my computer this directory is located at C:\Users\Michael\Documents\SQL Server Management Studio\Custom Reports, it'll be different on yours).

You can now bring up this report in SSMS by right-clicking on a database in the Object Explorer and selecting Reports > Custom Reports... to select the custom report.

Choose your custom report from the file selection box and click Open.

When you run a custom report in SSMS you'll get a warning like the following:

Just choose Run. You might also want to check the box that says "Please don't show this warning again" to keep the box from popping up every time you run a custom report. The simple custom report you've created looks like the one shown below.

You can use images, color and other formatting techniques to make the report easier to read and use, and more flexible for your users. Consider the image below, which is a screenshot of a reformatted version of the Missing Index report. This one includes more information, color and collapsible sections. Both reports are included in the attached ZIP file under the Missing Index and Missing Index Color directories.

You can play around with the source files included in the attached ZIP file.

It's Official - SQLSaturday is Coming to NYC!

Mike C — Sat, 06 Feb 2010 19:59:00 GMT

New Jersey SQL Server User Group (NJSQL) is bringing SQLSaturday #39 to NYC on April 24, 2010! The free all-day training event will be hosted by Microsoft at their Midtown Manhattan offices. The speaker line-up is growing fast—if you'd like to present, visit the event's open call for speakers. This is a free full-day training event, but registration is required to attend. Seating is limited.

Registration, speaker, and sponsorship details are posted at http://www.sqlsaturday.com/39/eventhome.aspx.

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!

5 things I wish every SSRS developer would do

James Luetkehoelter — Tue, 26 Feb 2008 10:34:00 GMT

I had a great discussion while teaching a class on Reporting Services today, discussing the basics of report design (yes, I make them consider basic principles of report design before I start talking about the technical details - I'm a stickler that way). That made me consider some of the most common design issues that I see with SSRS and the principles I'd like to see everyone stick to:

1) Design for your audience - If you develop a report that is more than one page and give it to a CEO or upper management, it will probably be ignored. If you give a one page report to a "bean-counter", they'll throw it away (and yell at you to boot). One of the hardest aspects of report design is determining the level of detail for any given audience.

2) Design for design - This is an odd phrase, but the whole concept of getting all of your report requirements up front, spending a fair amount of time developing the report, only to have the end user say "that isn't what I wanted". Often you can't get sufficient requirements from your customers without presenting them with a sample report and asking them if it accurate (data) and what they wanted (layout). That first pass through in report design should be simply to find out what the customer really wants. How often do customers ask for what they really want?

3) Design for reuse - No report should be a complete standalone. It may begin that way, but there should always be the opportunity to either expand on the original design or leverage it using different parameters. Rather than making a single report for a single department, investigate creating a general report that any department could use, then make it specific for that single department by means of a hidden parameter. If you combine that type of general report with the functionality of linked reports, you can have distinct, visual and securable reports all driven from the same design. Think about reusability from the very beginning - you'll be surprised how often it pays off.

4) Design with pagination in mind - In particular with parameterized reports, you need to be aware of how SSRS will handle pagination. Since we can set pagination rules with each individual data region, those including multiple regions (read: a list within a list within a list ad infinitum), pagination can quickly get out of control. Side by side data regions are even worse. What you thought would be a single page report can often grow into a multi-page report, page-breaking in the most awkward of places. Be sure to control your pagination (hint: ever explore the Rectangle quasi-data region?).

5) Design as if you aren't writing a report - SSRS gives you the ability to display marginally related data (from a database standpoint) together on the same report by means of separate data regions. Other reporting tools traditionally use a banded design: a detail row, grouping rows, report headers and footers, etc. SSRS presents us with a canvas to paint a variety of report display items from separate data sources (without subreports - no one should ever have to use a supreport again). Take advantage of that ability and fundamentally rethink what the best way to visualize data.

If you disagree, tell me why. If you want me to blather on more about a specific item, just let me know (those that know me well know that once you get me started, I'm off to the races - viva la propeller-heads!).