data

You are currently browsing articles tagged data.

Recursively Delete SSIS Folder

February 28, 2010 in Integration Services, T-SQL | No comments

A while ago I posted a query to create a list of all the Integration Services packages deployed to the MSDB.  I am now using that query to take it a step further.

If you’ve been using SSIS for a while you’ve probably noticed that the Management Studio doesn’t like to delete Integration Services folders that are not empty.  In fact, it will politely ask you if you’re sure that you want to delete the folder on which you’ve just selected the “Delete” option through the right-click menu.

Right-click pop-up menu on SSIS folder

I am sure I want to delete this non-empty SSIS folder

So you click the Yes button.  But then it shows you the following message:

SSIS folder ‘FolderWithSubfolders’ contains packages and/or other folders. You must drop these first. (Microsoft SQL Server Native Client 10.0)

Graphically it looks like this:

Object Explorer pop-up: you can't delete SSIS folders that contain packages or other folders

And this message can be really annoying if you’ve got a main folder with, let’s say, five subfolders, and each subfolder contains about 20-30 packages.  If you want to delete this folder you first need to delete each package separately and then delete the five subfolders, and then you can finally delete the main folder.  And all that through the right-click pop-up menu because you can’t just select the object in the Object Explorer and hit the Delete button on the keyboard – it doesn’t have an action on SSIS objects…

So, I wasn’t planning on doing such a job manually and came up with the following stored procedure.

It’s probably a bit long but don’t run away just yet, I will explain what’s going on down below the code, and there are some comments in the code as well.

/*
DESCRIPTION: Deletes all folders and packages under, and including, specified folder.
WRITTEN BY:  Valentino Vranken
CREATED:     2010-02-28
VERSION:     1.0
USAGE:
  -- mind the forward slash
  EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'
  -- to delete a subfolder
  EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders/ASubfolderWithPackages'

COPIED FROM: http://blog.hoegaerden.be

Note 1: folder names are not case-sensitive
Note 2: uses system tables and (undocumented) stored procedures located in MSDB.
Note 3: this code was written for SQL Server 2008. For 2005:
  o sysssispackagefolders -> sysdtspackagefolders90
  o sysssispackages -> sysdtspackages90
  o sp_ssis_deletefolder -> sp_dts_deletefolder
  o sp_ssis_deletepackage -> sp_dts_deletepackage
*/
CREATE PROCEDURE dbo.SSIS_RecursiveDeleteFolder
    @Folder varchar(2000)
AS
BEGIN
    set nocount on;

    declare @foldersToDelete table
    (
        folderid uniqueidentifier,
        Lvl int
    );

    declare @packagesToDelete table
    (
        PackageName sysname,
        folderid uniqueidentifier,
        Lvl int
    );

    --retrieve list of folders to be deleted
    with ChildFolders
    as
    (
        select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
            cast('' as sysname) as RootFolder,
            cast(PARENT.foldername as varchar(max)) as FullPath,
            0 as Lvl
        from msdb.dbo.sysssispackagefolders PARENT
        where PARENT.parentfolderid is null
        UNION ALL
        select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
            case ChildFolders.Lvl
                when 0 then CHILD.foldername
                else ChildFolders.RootFolder
            end as RootFolder,
            cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
                as FullPath,
            ChildFolders.Lvl + 1 as Lvl
        from msdb.dbo.sysssispackagefolders CHILD
            inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
    )
    insert into @foldersToDelete
    select F.folderid, F.Lvl
    from ChildFolders F
    where F.FullPath like @Folder + '%';

    --retrieve list of packages to be deleted
    with ChildFolders
    as
    (
        select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
            cast('' as sysname) as RootFolder,
            cast(PARENT.foldername as varchar(max)) as FullPath,
            0 as Lvl
        from msdb.dbo.sysssispackagefolders PARENT
        where PARENT.parentfolderid is null
        UNION ALL
        select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
            case ChildFolders.Lvl
                when 0 then CHILD.foldername
                else ChildFolders.RootFolder
            end as RootFolder,
            cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
                as FullPath,
            ChildFolders.Lvl + 1 as Lvl
        from msdb.dbo.sysssispackagefolders CHILD
            inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
    )
    insert into @packagesToDelete
    select P.name, F.folderid, F.Lvl
    from ChildFolders F
        inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
    where F.FullPath like @Folder + '%';

    --use cursor to loop over objects to be deleted
    declare objectsToDelete_cursor cursor
    for
        select P.folderid, P.Lvl, P.PackageName, 'P' as ObjectType
        from @packagesToDelete P
        UNION ALL
        select F.folderid, F.Lvl, null, 'F'
        from @foldersToDelete F
        order by Lvl desc, ObjectType desc;

    open objectsToDelete_cursor;

    declare @folderid uniqueidentifier;
    declare @lvl int;
    declare @packageName sysname;
    declare @objectType char;

    fetch next from objectsToDelete_cursor
    into @folderid, @lvl, @packageName, @objectType;

    while @@FETCH_STATUS = 0
    begin
        if @objectType = 'F'
        begin
            print 'exec msdb.dbo.sp_ssis_deletefolder '
                + cast(@folderid as varchar(max));
            exec msdb.dbo.sp_ssis_deletefolder @folderid;
        end
        else
        begin
            print 'exec msdb.dbo.sp_ssis_deletepackage '
                + @packageName + ', ' + cast(@folderid as varchar(max));
            exec msdb.dbo.sp_ssis_deletepackage @packageName, @folderid;
        end

        fetch next from objectsToDelete_cursor
        into @folderid, @lvl, @packageName, @objectType;
    end;

    close objectsToDelete_cursor;
    deallocate objectsToDelete_cursor;
END

Before trying to dismantle this stored procedure, I recommend you to read my previous article on retrieving the list of packages.  That already explains half of the code, if not 75%.

Our mission is to find a way to recursively delete packages and folders contained in a specified folder.  To be able to loop over those objects in the correct order (from the deepest level up until the level of the folder specified), the SP creates two table variables: one to hold all folders under the specified folder (@foldersToDelete) and one to hold the packages under the specified folder, including all subfolders (@packagesToDelete).

Based on those two lists I create a cursor that joins these two together, taking their level and object type into consideration.  That’s important because we first need to delete the packages in the lowest level folder, followed by their containing folder, then move one level up and do the same.

We then use the cursor to loop over the packages and folders and use two undocumented system stored procedures – one for each object type- to delete the package or folder.  These system SPs are located in the MSDB.  Here’s how they are defined:

ALTER PROCEDURE [dbo].[sp_ssis_deletefolder]
  @folderid uniqueidentifier
AS

ALTER PROCEDURE [dbo].[sp_ssis_deletepackage]
  @name sysname,
  @folderid uniqueidentifier
AS

As you can see, the parameters for these procedures are not that complicated.  Both of them expect a uniqueidentifier as identification for the folder.  That’s okay, these IDs are stored in the msdb.dbo.sysssispackagefolders table and retrieved by our queries to create the list of to-be-deleted objects.

Furthermore, the sp_ssis_deletepackage SP expects the name of the package to be deleted.  Not a problem either, those names are obtained from the msdb.dbo.sysssispackages table.

Note for SQL Server 2005 users: this code was written for SQL Server 2008.  The system stored procedures and system tables exist in 2005 as well, but they have different names.  See the comment header of my SP for more details.

So, let’s give it a little test.  Following screenshot shows the setup.  What I will do is use the stored procedure to delete the FolderWithSubfolders folder.  If you’ve been paying close attention, that is the same folder which I tried to delete manually through the Management Studio’s right-click menu (see first screenshot above).

Overview of my deployed folders and packages

After creating the SP, I ran following command:

EXEC dbo.SSIS_RecursiveDeleteFolder '/FolderWithSubfolders'

And that gave me the following output in the Messages pane:

exec msdb.dbo.sp_ssis_deletepackage AnotherPackage, 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletepackage Package, 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletefolder 4102ED59-ED75-4D93-BBAE-0A162447BF02

exec msdb.dbo.sp_ssis_deletefolder 7F38288D-4370-40A8-80E3-E92283033E4C

exec msdb.dbo.sp_ssis_deletefolder C156B436-8C78-4BF9-99F9-5ABFAB10C405

I have deliberately put a couple of print commands in the stored procedure to dump the commands that are actually being executed.  This gives us a good idea of what’s going on.

That’s it for now folks.  Thank you for reading this, and if you found it useful or you’ve got some questions about it: post a comment!

Have fun!

Valentino.

  • Share/Bookmark

Tags: , , , ,

SSIS: Adding Files To The Miscellaneous Folder

February 11, 2010 in Integration Services | 2 comments

Sometimes my posts are over 20 pages long when pasted into a Word document.  That’s when I call them article, or tutorial.  Other times I post real quickies about little things that have annoyed me in the past, because I had to spend too much time looking for a solution to a certain issue, or just because they are not very obvious and I can image fellow developers doing a search on the internet on that specific subject.

This post is one of the latter.

In a SQL Server Integration Services project, have you ever wondered how on earth you can get files into that Miscellaneous folder?  When you right-click on the folder in the Solution Explorer, nothing happens, no pop-up menu.

Well, the answer is simple, once you know it.

All you need to do is go one level up in the tree and right-click the Project node in the Solution Explorer.  In the menu that appears, select Add > Existing Item….

How to add a file to the SSIS Miscellaneous folder

In the pop-up window, navigate to any file that you’d like to add to the project.  Files of a different type than the usual SSIS files such as .dtsx and .ds are automatically added under the Miscellaneous folder.  When adding files, they will be automatically copied to the SSIS project folder, no matter where they were stored originally.  See, not that complicated… once you know it!

In the following screenshot I’ve added three different file types to the folder, just to prove that it’s working.

Miscellaneous folder with some files added to it

I like using this folder to store files that belong with that particular project.  Examples of those are configuration files, XSLT files for complex XML conversions, and also the SQL scripts that create my databases.  Each time when I make schema changes, I update the scripts.  And as I’m using TFS integration, I can rest assured that I always have a backup of my files.  (At least, assuming the TFS team is doing their job – this is usually beyond my responsibility :-) )

Speaking about TFS, watch out if you use Business Intelligence Development Studio 2008 to connect to Team Foundation Server 2005!  There’s an interesting setting in the Options screen (through the Tools menu), located in the Source Control > Visual Studio Team Foundation Server page.  This setting is called Get latest version of file on check out.

Get latest version of item on check out

However, there’s one caveat!  On TFS2005 it doesn’t do anything!  If you’re not aware of that, you may get an annoying surprise when you’re trying to check in your changes because you may have been working on an outdated version of your package!  And as you probably already know: merging two versions of an SSIS package is, well, what shall I call it, a challenge?

Another setting that helps you to avoid the issue described above is located under Source Control > Environment and is called Get everything when a solution or project is opened.  Activate this setting and each time when you open your project, you’ll get a popup window which allows you to Get the latest version of the files.

Get evrything when a solution or project is opened

That leaves one more possible conflict situation.  If someone changes a package on the same day as you, the second person will need to explicitly do a Get Latest Version or he/she will be working on an outdated version.  So, communicate with your team mates so that you know if someone has gotten an assignment that collides with yours.  Of course, this last problem is just a theoretical possibility.  In teams, work is usually divided so that developers do not need to work with more than one person on the same piece of code.  The same logic applies to SSIS packages.

And remember, have fun!

Valentino.

  • Share/Bookmark

Tags: , , ,

Memory Dumps And Crazy Code Samples

February 6, 2010 in Reporting Services, T-SQL | No comments

I usually don’t write posts just to mention a link to another site.  Except when I’ve come across articles which are so good that I want everyone to know about them.  Here are a couple of articles in that particular category.

What Does Microsoft Do With Those Memory Dumps?

The first one is written by Adam W. Saxton, a member of the Microsoft Customer Service and Support (CSS) SQL Server Escalation Services team.

The subject of the post is an issue that developers of Reporting Services reports may come across: “InvalidReportParameterException with Data Driven Subscription”.  The report complains about an invalid parameter while the parameters seem to be okay, visually.  Then the author goes on to describe the actual issue: trailing spaces!  If you’ve been doing BI for a while, this is a quite common issue.  Adam also shows the difference between the LEN() and the DATALENGTH() functions.

But that’s not the reason that I’m mentioning that article here.  The best part starts following the Summary chapter, and is entitled Techie Details.  In that extra chapter he shows how the CSS team uses those crash memory dumps which I’m sure you’ve all seen now and then. 

I won’t give you any details about that, just have a look at the article.  If you’re a developer, there’s some very valuable information there!

I am definitely looking forward to seeing his pre-conference session at the SQL PASS European Conference in Germany this year: Tackling Top Reporting Services Issues!

Some Crazy Code Samples

The second article that I’d like to mention here is written by Phil Factor, a regular at the Simple-Talk site.

This article is called Laying out SQL Code and mentions some database naming conventions and T-SQL coding layout, as the title already implies.  Even if you’re not interested in that (although as a serious developer you should be!!), the article is worth the effort of reading just for its code samples.

Here’s my favorite one:

CREATE TABLE "╚╦╩╗" ( "└┬┴┐" nvarchar(10))
DECLARE @ nvarchar(10)  set @='═'
INSERT  INTO "╚╦╩╗"
        ( "└┬┴┐" )
        SELECT  replicate(@,5)

SELECT  *
FROM    "╚╦╩╗"

And just to prove to you that this really is valid code, here’s the result:

Result of crazy query

So, how about that database for that plumbing company? :-)

BTW: when running that code without paying attention to the details (such as what DB your SSMS is connected to), you may end up with something like this:

Silly table stored in master DB - that's not a best practice! 

So you may want to clean up after running the query using this statement:

drop table "╚╦╩╗"

Have fun!

  • Share/Bookmark

Tags: , , , ,

Your First OLAP Report

January 24, 2010 in Analysis Services, Reporting Services | No comments

Introduction

This article is aimed at report developers who are used to develop reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes.

It demonstrates how to build a report using SQL Server Reporting Services 2008 with data coming from an OLAP cube running on SQL Server Analysis Services 2008.

The OLAP database used in the article is called “Adventure Works DW 2008”, available for download at CodePlex.

If you’re fairly new to Reporting Services (aka SSRS) and you find that this article is going a bit too fast, I’d like to point you to my other article which explains how to build a report that’s retrieving data using regular stored procedures.

OLAP <> OLTP

When people are talking about databases, what they are usually referring to are “regular” relational OLTP databases.  OLTP stands for Online Transaction Processing.  As the name implies, these types of databases are built to handle many simultaneous transactions (consisting of actions such as inserts, updates, deletes) in real-time.  I’m sure you’re familiar with these types of database so I won’t go further into them.

OLAP (Online Analytical Processing) on the other hand is a totally different story.  OLAP cubes are built to answer multi-dimensional analytical queries as fast as possible.  For that purpose, what you can find in such a database are measures (these are the numbers) stored in cubes, and dimensions which allow filtering the measures.  This filtering is often referred to as slicing and dicing.  Furthermore, OLAP cubes contain pre-aggregated data, again to be able to answer queries as fast as possible.

Let’s make this clear with an example.  Imagine the following request:

“Give me the sum of all sales of product X for period Y in country Z.”

Three dimensions can be recognized in that request: “product X” is found in the Product dimension, “period Y” in the Date dimension and “country Z” in the Geography dimension.  (I’ve used the actual dimension names as they are called in the Adventure Works OLAP database.)

Each dimension consists of attributes and attribute hierarchies and it’s those attributes that you’re actually referring to when building an MDX query.  MDX stands for Multidimensional Expressions and that is the language used to query an OLAP database, just like you use SQL to query a relational database.

Looking at our example, what we need is for the Product attribute in the Product dimension to be equal to X.  An attribute in a dimension can also be written as [Dimension].[Attribute], thus we also want [Date].[Date] to be equal to Y and [Geography].[Country] equal to Z.

As for the measure part, that’s what “the sum of all sales” is referring to.  When looking at the measures available in the Adventure Works cube, one of the measures that would fulfill the request is the Reseller Sales Amount in the Reseller Sales measure group.  The Analysis Services engine searches the cube and retrieves the aggregated number for [Measures].[Reseller Sales Amount] available at the intersection of [Product].[Product] X, [Date].[Date] Y and [Geography].[Country] Z.

OLAP cubes are usually, although not necessarily, build on top of a data warehouse.  In SQL Server, a data warehouse is still a relational database, unlike an OLAP cube, but the table structure is different from an OLTP database.  A data warehouse contains tables that represent dimensions and other tables that contain the facts.  The facts are the numbers, so the measures that were mentioned earlier.  This is called a dimensional model.  Dimensional modeling was invented by Ralph Kimball, one of the pioneers in data warehousing.  For completeness I’d like to mention that another data warehousing approach was described by Bill Inmon.  I’ll leave it up to you to do some research on both approaches and decide for yourself which one you prefer, possibly even a mix of both.

As far as the “Adventure Works DW 2008” OLAP database is concerned, it’s built on top of the AdventureWorksDW2008 dimensional database.

Okay, I believe this theoretical explanation was sufficient for now, let’s start with the report!

Your First Report

Business Requirements

You’ve gotten the assignment to create a report that shows the reseller sales numbers by region.  The highest level to be shown is Country, with drilldown through State/Province to City.

Creating The Shared Data Source

Just like when building reports on OLTP databases, we’re not going anywhere without a Data Source.  I’m going to create a Shared Data Source called OLAP_AdventureWorks.rds:

Shared Data Source connecting to Adventure Works OLAP Database

The Type that we need is Microsoft SQL Server Analysis Services, which is the SQL Server service that’s running the OLAP databases.  Furthermore I’ve selected the “Adventure Works DW 2008” database.

Connection Properties specifying the Adventure Works DW 2008 OLAP database

There’s no need to type the database name yourself.  After you’ve provided sufficient credentials in the Credentials page, you can just select it from the dropdown in the Connection Properties screen.  This screen is opened by clicking that Edit button on the Shared Data Source Properties window.

Your First OLAP Dataset

I’ve created a new report called FirstOLAPReport.rdl.  In that report I’ve specified that I’ll be using the Shared Data Source created earlier.  This source is known as srcAdventureWorksOLAP in my report.

Next step is to create the dataset.  I’m calling it dsResellerSalesByRegion.  As this is our first OLAP report, we’re not going to write the MDX ourselves but we will use the Query Designer which is opened by clicking the button that has the words Query Designer printed on them, how difficult can that be?!

How to open the MDX Query Designer

The BIDS knows that it should open the MDX Query Designer because our data source is connecting to an Analysis Services server.  All we need to do now is to drag the measures and dimension attributes that we require into the area marked with “Drag levels or measures here to add to the query.”.

Let’s start by dragging our measures into that area.  We need two measures, both located in the Reseller Sales measure group.  They are called Reseller Order Quantity and Reseller Sales Amount.  Following screenshot shows the situation after the first measure has been added.  The second measure was being dragged into it as well.  When dragging items into the area, a vertical blue line appears to indicate where the item can be added.

MDX Query Designer: dragging a measure into the query

Next I’m going to drag the Geography hierarchy, located in the Geography dimension, into the design area.

MDX Query Designer: dragging a hierarchy into the query

Now we’ve got all the data we need for our report.

As you have noticed, the Query Designer automatically executes the query each time it gets modified when you’re dragging an item into the design area.  If you don’t want this behaviour, it can be switched off by clicking the Auto Execute button in the toolbar (indicated by a red 1 in the screenshot below).

Query Designer toolbar

Another interesting button is the Design Mode button (indicated by a green 2).  This one allows you to toggle between the graphical designer and the text editor.  By clicking it you can see the actual MDX query that the designer has prepared for you.

As you can see, the query is nicely formatted using capitals for the keywords and so on.  Well, no, actually it’s the worst editor around!  No syntax coloring, no multi-line formatting, nothing.  So if you are going to take a close look at the query, I recommend you to use the Management Studio.  Connect to your Analysis Services server, locate your database and right-click it in the Object Explorer.  Then choose New Query > MDX and paste the query into that new window.  You’ll still need to manually break it down into different lines but at least you get syntax coloring.  Furthermore, if you’re going to make manual modifications to it, you’ve got some command completion and error indicators as well.

Please take into account that once you’ve made manual changes to your query, you cannot switch back to the graphical designer.  Well, you can, but you will lose all manual modifications.  Don’t worry about doing it accidentally though, a nice pop-up will warn you:

Warning message when switching back to design mode.

Something else that you’ll also notice is that the results displayed in the Query Designer and those displayed in the Management Studio are not exactly the same.  That’s because both environments interpret the results differently.  Remember, you’re not retrieving two-dimensional row/column data like with a SQL query.  You’re retrieving multi-dimensional data!

If you take a closer look at the query that we’ve produced above, it’s similar to this:

SELECT something ON COLUMNS,
    something_else ON ROWS
FROM [Adventure Works]
That query is selecting data on two axes: COLUMNS and ROWS.  But in fact, MDX supports up to 128 axes.  However, the client tools that we are using here are not able to visualize that kind of cellset (as the result set of an MDX query is also called).
 
Okay, enough about our dataset.  We’ve got the data, let’s put it on the report!
 

Displaying The Result Set

As a reference, these are the fields available in our dataset:

Fields available in OLAP dataset

Without going into too much detail – there’s no difference compared to reporting off a relational database – I’ve set up a table with three grouping levels on the rows.  I’ve also added some makeup like background colors and font modifications.

As shown in following screenshot, the highest-level group is Country, followed by State_Province and City to conclude, just as specified in the requirements mentioned at the start of this chapter.

Table with three groupings defined

Rendering the report in preview gives us something like this:

Report without any numeric formatting applied

What is still missing at this point is decent formatting for those numbers!  And here’s where we can take advantage of the fact that we’re retrieving data from an OLAP cube.  A cube developer has the possibility to define the format for the measures in the cube itself.  Doing that ensures that the same formatting is applied no matter what OLAP client tool is used.  Any client that supports this way of formatting will show the numbers using the same format.

As you’ve seen in that last screenshot, there’s no formatting applied at all.  Does this mean that there was no format defined in the cube?  Let’s find out!

A Little Walk Into The Analysis Project

We are going to open up the Analysis Services project that contains the cube definition.  If you don’t have any experience with SSAS, don’t worry!  We will just have a look at a couple of properties and that’s it, plus I’ll explain each step as needed.  In case you’ve forgotten where the sources are located, this is the default location: C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks Analysis Services Project\.  I’m opening the project located under the \enterprise subfolder by double-clicking the Adventure Works.sln file.

Once the project is loaded into the BIDS, locate and open the Adventure Works.cube in the Solution Explorer.  You can find it in the Cubes folder of the Adventure Works DW project.

By default it will open the cube Design showing the first page called Cube Structure.  At the top-left, we’ve got the Measures pane.  The measures are shown in measure groups.  Open the group called Reseller Sales.  Now locate the measure called Reseller Sales Amount and select it.

Cube in Design with Reseller Sales Amount selected

Now that we’ve selected one of the measures that we are retrieving in our report, have a look at the Properties window.  In case it’s not open yet you can right-click the measure and select Properties.  The property that we’re interested in is called FormatString.

Properties of the Reseller Sales Amount measure showing Currency as format string

The cube developer has specified that this measure should be shown as being a Currency.

Now that you’re in the cube, have a look at the properties for our other measure, the Reseller Order Quantity.  This one is being formatted as #,#.

The FormattedValue Field Property

So why are we not seeing those formats in our report?  Because by default they are not applied in an SSRS report!  When dragging fields from the Report Data window onto the design area, what the BIDS is retrieving is the Value property of the field.  However, there’s also a property called FormattedValue.

(You may want to make a copy of your report before applying the following changes.)

Now, change the six table cells that are showing the numbers (so including the ones showing the totals) to retrieve the FormattedValue property instead of the Value property.  The expression for the totals of the Reseller Sales Amount looks like this:

=Sum(Fields!Reseller_Sales_Amount.FormattedValue)

Once you’ve done that, have a look at the Preview:

Report Preview showing no numbers after retrieving the FormattedValue property

That doesn’t look right, does it?  We’ve lost our numbers!

Now hit the Refresh button: Refresh button in Report Preview

This time we’ve got some numbers:

Report Preview showing formatted numbers, and errors!

But we’ve also got some errors for free!  Looking at the Output window we get some extra details on the reason for the error.  Here’s one of them:

[rsAggregateOfNonNumericData] The Value expression for the textrun ‘Reseller_Order_Quantity1.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric.  Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

In short, what it says is that our data is not numeric.  And this poses an issue when it tries to apply the SUM() aggregate function.  Right, as our data now contains formatting, it became a string instead of a number, and strings can’t be added together using SUM().

So that’s not a good way to apply the formatting, not in this case anyway.  Luckily there’s another method to do that.

But first, undo those last changes and replace the FormattedValue with the Value property.

(Or switch back to the original report if you took a copy earlier.)

The Cell Properties

What exactly is our MDX query doing?  I’m taking a closer look at it by taking it from the Dataset Properties window and pasting it into a MDX query window in the Management Studio:

SELECT
NON EMPTY { [Measures].[Reseller Sales Amount], [Measures].[Reseller Order Quantity] }
ON COLUMNS,
NON EMPTY { ([Geography].[Geography].[Postal Code].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Besides retrieving the requested measures and dimension attributes, it’s retrieving several Cell Properties, including FORMATTED_VALUE and FORMAT_STRING.  I believe that the first one rings a bell by now.  What we’re going to do is to retrieve the second one and apply it as Format property for our numeric table cells.

In the report’s Design, select one of the table cells containing a number.  In the Properties window, one of the properties is called Format.  Click to select it, then in the dropdown choose Expression….  For each of the six numeric cells, create an expression similar to the following:

=Fields!Reseller_Order_Quantity("FORMAT_STRING")

The example above tells the BIDS to retrieve the FORMAT_STRING cell property from the Reseller_Order_Quantity field.

Tip: you don’t need to open up the Expression builder for each of the six cells.  You can just copy/paste the string from the Format field.  Just ensure that you’re retrieving the format from the same field as the one that the cell is displaying.

Now let’s have a look at the Preview again:

Format is working for the quantity amounts but not for Currency!

Hmm,  the quantities are fine now, but the currencies are not!  So, let’s try out yet another method for those cells.

For the three cells containing a currency measure, remove the Format property – it’s not working anyway!

Next, change the expression that’s retrieving the Value property to something similar as this one:

=Format(Sum(Fields!Reseller_Sales_Amount.Value),
    Fields!Reseller_Sales_Amount("FORMAT_STRING"))

This expression applies the value of the FORMAT_STRING property using the Format() function.  In this particular case it’s the expression used to produce the Reseller Sales Amount total.

Having modified all three currency cells, here’s another Preview look:

Both Currency and regular numeric cells are showing formatted values!

That certainly looks better doesn’t it?!

Okay, to conclude, let’s activate drilldown by setting the subgroup levels to a collapsed state by default.

I will not go into full detail on this.  To start, make sure that the cells that are going to contain the +/- toggle have gotten a decent name, such as txtCountry for the cell that shows the Country name.  Then edit the properties of the subgroups by setting Visibility to Hide.  Also, activate the Display can be toggled by this report item checkbox and select the textbox showing the label one level higher.  Shown below is how to configure the group on State_Province.

Group Properties showing how to activate drilldown

 

Let’s have another look at the report Preview:

Fully working drilldown report

By default all nodes were collapsed.  I’ve expanded a couple of them just to show that it’s all working.

The InitialToggleState Property

Okay, I will not let you go just yet.  To really conclude I’ll let you in on a little feature related to the drilldown.  Open up the group properties for the State_Province group and set the initial visibility to Show (leave the “Display can be toggled by this report item” checked!).  Then checkout Preview:

Visibility toggle is broken!

Wow, that’s weird, the country level is expanded and yet there’s a plus icon in front of the country’s name.  Clicking it will collapse the states and change the icon to minus.  If that isn’t mixed up then I don’t know what is!

Well, the solution to this problem is simple.  Select the textbox showing the country name and locate the InitialToggleState property.  By default this is set to False, which means collapsed or in other words, False shows the plus icon.  Change it to True and now your initial state icon will be a minus!

Conclusion

With this article I believe to have shown you how to get started with reporting off an OLAP cube while throwing in a couple of tips in the process.

Have a look at another article that I wrote earlier, it explains an issue which you may run into when taking OLAP reporting a step further: SSRS and MDX: Detecting Missing Fields

Happy Reporting!

Valentino.

References

BOL 2008: The Basic MDX Query

BOL 2008: Using Cell Properties (MDX)

MDX: Retrieving Cell Properties by Greg Galloway

  • Share/Bookmark

Tags: , , , , , ,

Free SQLUG Evening: Service Broker

January 20, 2010 in SQL Server | No comments

What?

image

Service Broker provides queuing and reliable messaging for SQL Server. Service Broker is used both for applications that use a single SQL Server instance and applications that distribute work across multiple instances.

Within a single SQL Server instance, Service Broker provides a robust asynchronous programming model. Database applications typically use asynchronous programming to shorten interactive response time and increase overall application throughput.

Service Broker also provides reliable messaging between SQL Server instances. Service Broker helps developers compose applications from independent, self-contained components called services. Applications that require the functionality exposed in these services use messages to interact with the services. Service Broker uses TCP/IP to exchange messages between instances. Service Broker includes features to help prevent unauthorized access from the network and to encrypt messages sent over the network.

(The above was quoted from the BOL.)

Who?

Dr. Nico Jacobs, PhD in Data Mining, trainer/consultant at U2U focusing on SQL Server/BI.

When?

February 11, 2010.

Where?

Avenade, Vilvoorde.

Register here: http://sqlug.be/nextevent/

More info at the SQLUG site.

See you there!

  • Share/Bookmark

Tags: , ,

List All SSIS Packages Deployed On Your Integration Server

January 10, 2010 in Integration Services, T-SQL | 1 comment

When deploying packages to SQL Server Integration Services, it’s advisable to set up a folder structure so that you can easily distinguish packages belonging to different projects.  Furthermore it may be interesting to create subfolders under the main project folder to separate packages according to the different phases in your ETL (Extract, Transform, Load) process.  When loading a data warehouse, interesting folder names are Dimensions for your dimension ETLs and Facts for the packages that load the fact tables.

After a while you end up with lots of packages spread over lots of folders.  To get a good view of what is deployed on your server, it may be interesting to find a way to list all the packages.  And that’s exactly the reason why I’m writing this article.

The query further down generates a list of all packages deployed in the MSDB database on your SQL Server.  What you get is the name of the packages, their location and version-related information.  I’ve also created a  RootFolder column so that it’s easy to filter on project.  (See now why it’s interesting to create separate folders per project?)

It’s important to note that packages deployed to the File System will not be shown in the list.  After all, they are not stored in the MSDB database but in a folder somewhere on the server’s hard drive, more precisely in a subfolder of where you’ve installed your SQL Server.  In case you’ve forgotten where that was, here’s a small tip.  On your server, open up the list of Windows Services (Start > Run > type "services.msc" > enter) and locate the service called SQL Server Integration Services 10.0.  Open the properties of that service and have a look at the Path to executable value in the General tab.  Take the path, drop the \Binn part and add \Packages instead.  That is where, by default, the packages are deployed.  (If you’re running SQL Server 2005, apply the same procedure but look for a service called SQL Server Integration Services.)

On my system, this is where the packages are located: D:\Program Files\Microsoft SQL Server\100\DTS\Packages.  I will also prove it with following screenshot:

Packages deployed to the file system on SSIS 2008

Okay, time for the real stuff, the query:

/*
    DESCRIPTION: Lists all SSIS packages deployed to the MSDB database.
    WRITTEN BY: Valentino Vranken
    VERSION: 1.1
    COPIED FROM: http://blog.hoegaerden.be

    Note: this query was written for SQL Server 2008. For SQL2005:
        o sysssispackagefolders => sysdtspackagefolders90
        o sysssispackages => sysdtspackages90
*/
with ChildFolders
as
(
    select PARENT.parentfolderid, PARENT.folderid, PARENT.foldername,
        cast('' as sysname) as RootFolder,
        cast(PARENT.foldername as varchar(max)) as FullPath,
        0 as Lvl
    from msdb.dbo.sysssispackagefolders PARENT
    where PARENT.parentfolderid is null
    UNION ALL
    select CHILD.parentfolderid, CHILD.folderid, CHILD.foldername,
        case ChildFolders.Lvl
            when 0 then CHILD.foldername
            else ChildFolders.RootFolder
        end as RootFolder,
        cast(ChildFolders.FullPath + '/' + CHILD.foldername as varchar(max))
            as FullPath,
        ChildFolders.Lvl + 1 as Lvl
    from msdb.dbo.sysssispackagefolders CHILD
        inner join ChildFolders on ChildFolders.folderid = CHILD.parentfolderid
)
select F.RootFolder, F.FullPath, P.name as PackageName,
    P.description as PackageDescription, P.packageformat, P.packagetype,
    P.vermajor, P.verminor, P.verbuild, P.vercomments,
    cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData
from ChildFolders F
    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
order by F.FullPath asc, P.name asc;

The query uses a recursive CTE (Common Table Expression) to get data out of a system table called sysssispackagefolders, located in the MSDB system database.  The CTE gives us a list of all folders stored in the database and at the same time uses the hierarchical structure of the table to build the FullPath and the Lvl columns.

Note: the CAST() calls are needed because the data type of the foldername column is sysname.  And sysname does not implicitly convert to varchar, which is needed for the concatenation building the FullPath column.

The CTE is joined with another system table called sysssispackages, also located in MSDB.  Not all columns are being retrieved from that table but I believe I’ve selected the most important ones.  Have a look in the Books Online for more info on the columns available.

There’s one column however on which I’d like to add some additional info myself.  That column is called packagedata and it contains the actual SSIS package.  The data type of this column is image, not sure why because after all, an SSIS package (or .dtsx file for that matter) is pure XML.  So why isn’t it stored as XML?  If anyone knows the reason: post a comment!

Anyway, as you can see in the query, to get it converted from image to XML you need to go through varbinary.  The image datatype cannot convert directly to XML.  See the Books Online here on what casts are allowed: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Note for SQL Server 2005 users: as mentioned in the query’s comments, these tables don’t exist in SQL Server 2005.  Well, actually they do, they just have different names.  See the comment in the code for their equivalent.

To finish off I’ll show you what the results look like when executing the query on my test system.  But first, following screenshot shows all deployed packages as reported by the Management Studio.  As you can see, two packages are deployed to the File System.  These two packages were shown earlier in the first screenshot.  Some other packages have been deployed to the MSDB database.

Object Explorer showing all deployed SSIS packages

And here are the results of the query:

A list of all SSIS packages deployed to my SQL Server 2008 MSDB database

To be honest, I added a little filter to keep the results clean.  The Data Collector, a new feature of SQL Server 2008, also uses some packages so I’ve filtered those out by adding a WHERE clause to the SELECT statement at the bottom of the full query:

select F.RootFolder, F.FullPath, P.name as PackageName,
    P.description as PackageDescription, P.packageformat, P.packagetype,
    P.vermajor, P.verminor, P.verbuild, P.vercomments,
    cast(cast(P.packagedata as varbinary(max)) as xml) as PackageData
from ChildFolders F
    inner join msdb.dbo.sysssispackages P on P.folderid = F.folderid
where F.RootFolder <> 'Data Collector'
order by F.FullPath asc, P.name asc;

If you’ve been paying attention, you’ve noticed that the two packages deployed to the File System are not mentioned in the output of the query, as expected.

That’s all for now folks, have fun!

References

BOL 2008: Tutorial: Creating a Simple ETL Package

BOL 2008: sysssispackagefolders (Transact-SQL)

BOL 2008: sysssispackages (Transact-SQL)

BOL 2008: Recursive Queries Using Common Table Expressions

  • Share/Bookmark

Tags: , , , , ,

SSMS Showing Incorrect Version Numbers In Object Explorer

January 10, 2010 in Management Studio | No comments

Ever since I upgraded to SQL Server 2008 Service Pack 1 I noticed that the Management Studio was reporting incorrect version numbers when connected to Integration or Reporting Services.  This incorrect version number is located to the right of the server instance in the Object Explorer.

As usual, a picture says so much more than … :

Object Explorer showing wrong version numbers

As I have posted earlier, 10.0.2531 is the version number for SP1, while 10.0.1600 is the original RTM version number.

I never really spent time looking for an answer to this.  It was obviously a bug but I could live with it and someone else would probably already have filed it as being a bug.  So recently I came across a post by Phil Brammer that mentioned this issue.  This post got a comment from Matt Masson, a developer on the SSIS team.  Have a look at the comment but in short: the version numbers that are being shown in the Object Explorer are actually the version numbers of the service’s .exe file!  And SSMS is now showing the wrong number because these files didn’t get an update in SP1.

After a little search I found the bug report on Microsoft Connect, reported on March 11, 2009, by Dan English.  Its status is Fixed but it seems that it isn’t.  At least, looking at the comments, CU5 (Cumulative Update) for SQL Server 2008 SP1 is still showing the problem.  So I guess you could go over to the Connect page and click on that Yes button if you’re interested in seeing this fixed.  After all, it could be quite misleading to novice DB guys and gals…

On this same subject, there’s another interesting post by Adam W. Saxton, a member of the Microsoft SQL Server Escalation Services Team.  In this post he takes a closer look at the SQL Server 2008 Reporting Services version number after having installed CU2.

Conclusion: if you need to find out what version your server is running, do not rely on the version numbers that you see in the Object Explorer.  As Adam explained, one way is to look at the version numbers of the files that were included in the upgrade.  But that may a bit of an overkill.  My favorite way, assuming that all components of the SQL Server installation have been upgraded to the same version, is to use the following query:

SELECT @@VERSION;

 

On my machine that comes back with the following result:

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

And remember, have fun!

  • Share/Bookmark

Tags: , , , , , , , ,

Adding Leading Zeroes To A Number

January 6, 2010 in T-SQL | No comments

After having successfully survived the festivities, here I am again, ready for another year of blogging, article writing, forum answering, conference attending, …

Happy New Year to anyone reading this!!

In this year’s first post I’d like to share my preferred way of adding leading zeroes to a number, useful for any situation where you need to convert a number to a string using SQL.  In fact, this method can be used whenever a certain string needs to get increased to a specific length using a particular character, not just numbers.

And here’s the code sample:

declare @number int = 42;
declare @character char(1) = '0';
declare @expectedLength int = 8;

select REPLICATE(@character, @expectedLength - LEN(@number)) + CAST(@number as varchar(8)) as Result;

 

I’m using the REPLICATE() function to create a string of our leading character, in this case a zero.  The length of this string of zeroes is the expected length minus the length of the actual number.  The LEN() function is used to get the length of our number – note that LEN() expects a string as parameter so an implicit cast from int to varchar is being performed here.

Then I use the + operator to concatenate the string of zeroes with our actual number.  This number gets converted to varchar using the CAST() function.  The length of the varchar should be equal to the maximum length that our output string can be.  This way we ensure that all acceptable values are being converted to string.  In the case that our number is longer than the expected output string, the result will be NULL.

And here’s the result:

Result of sample query to add leading zeroes to a number

Have fun in 2010!

  • Share/Bookmark

Tags: ,

Publishing Articles On Experts Exchange

December 13, 2009 in General | 3 comments

Since a while now, about two months and a bit to be exact, I’ve been publishing some of my articles on Experts Exchange.  It all started when their Content Coordinator found my blog and convinced me to start doing that, thanks for that Jenn (aka jennhp)!

In total I have now six articles published and four of them are EE Approved.  What does that mean?

EE-Approved is a designation awarded by the Page Editors to show that your Article is of superior quality: either it discusses something new, or provides a unique and original solution to a particularly interesting problem. If your article is selected as EE-Approved, you will receive 1,500 points.

When I published my first article back in September, getting an article EE Approved earned me 1,500 additional points on top of the 500 for getting it published.  This was recently changed to 4,000.  Which I really appreciate and I think was needed to make this a success.  When comparing the effort that goes into writing a good article with the effort to decently answer a 500 points question I think it is still not comparable.  A question can earn me 2,000 points (when the answer gets A-graded, most of my answers do) in just a couple of minutes – which doesn’t mean that all questions are solved this easily,  believe me.  But an article always takes several hours to write and publish.  Just to give you an idea: for my last article it took me one hour just to get it converted from my version in Live Writer to the tagged format expected by the “Write a new article” feature.  But then again, the minimum length requirements for an article is 300 words while my last article counted close to 2,500 words.

What should I conclude from the above paragraph?  That I should learn to make my articles shorter?  Or that Article Points and Expert Points received by answering questions are not comparable?  Well, probably both.  What I should learn is to split articles up when possible, but I will still keep mentioning the necessary details which in my opinion increases the quality indicator :-)

Anyway, it seems that I’ve been doing a good job because this is what the situation looked like on November 23, 2009.  Following screenshots were taken while filtering on Microsoft articles.

Five articles in Microsoft top 10

Not only did my articles control the Top 3 of most popular Microsoft articles,  they were also at position nine and ten, so in total I had five of my articles in the Top 10.

(The filter on Microsoft articles is highlighted in green btw.)

Highlighted on the left you can see one of my articles being featured.  This is a result of it being EE Approved.

As the next two screenshots show, it wasn’t the only one.

One of my EE Approved articles featured at Experts Exchange

And here’s number three:

My "Pie Chart Techniques" article feature at Experts Exchange

On that same page we can also see the Top 25 of Microsoft Contributors.  I was at position 7 with almost 15,000 points.  (Today I am at position 6, with about 20,000 points.)

Me at position 7 in the Top 25 of Microsoft article writers

So, why am I doing this?  Let’s see, I have several reasons:

  • I hope people will find them useful, which means I have been able to teach them something
  • It helps to improve my writing skills
  • It encourages me to explore details about certain topics that I wouldn’t need to explore otherwise
  • It brings me into contact with interesting people
  • I found out that writing can actually be a fun thing to do!
  • Points == T-shirts :-)

Right, so I started this post by thanking someone, let’s finish it the way it started.

I’d like to thank mark_wills for his enthusiasm and support.  He’s the Page Editor that has reviewed my articles and selected several for the EE Approved status (in cooperation with mwvisa1 I believe).  Just to make sure I haven’t missed anyone, I’d like to say thanks to all Page Editors that have approved my articles!

If you’d like to have a look at the articles, they are mentioned in my profile at Experts Exchange.  While you’re at it, if you like them I wouldn’t mind if you clicked that little YES button :-)

Now if you’ll excuse me, I’ve got some articles to write.  (In an attempt to control the Top 5 – erm, yeah right – just kidding of course.)

Happy reading!

Valentino.

  • Share/Bookmark

Tags: , ,

Generating A List Of Dates Out Of Numbers

December 2, 2009 in T-SQL | 2 comments

This post is meant partly as a “reminder to self” but also to show that, if you need to generate a list of dates, all you need are numbers.  Oh well, if that doesn’t make sense: read on!

One of the undocumented tables of SQL Server is called spt_values and it is located in the master database.  This table serves as a lookup table for SQL Server itself, more precisely it’s used by several system stored procedures.

Being a lookup table, it doesn’t consist of many columns.  One of the most important columns though is called type.  This one defines the meaning of the record.  An interesting value for this column is “LNG”.  When filtering on “LNG” it returns the following:

List of languages with their LCID

I’m quite sure that SharePoint developers recognize these values stored in the number column.  That’s the locale identifier of the language mentioned in the name column.  Could be interesting in case you need a list of languages to be shown somewhere, in a report parameter for instance.

But anyway, these records are not the point of this blog post so let’s move on.  The title of this post says we’re going to generate some dates out of numbers.  So to get started, what we need are those numbers.  Guess what the spt_values table contains?  Indeed, when filtering the name column on “P”, what we get are numbers starting at zero and ending at 2047:

List of numbers retrieved from master..spt_values

And how can we turn these into dates?  Using some creativity it’s not that complicated.  Let’s say we want to generate a list of dates starting at the first of January of the current year up until today, could be interesting for YTD calculations.  Following query does just that:

select CAST(DATEADD(dd, -number, GETDATE()) as date) dt
from master..spt_values
where type = 'P' and number < DATEPART(dy, GETDATE())

As you can see, several datetime-related functions are being used here.  The easiest one is GETDATE() which returns the current system timestamp of the database server.

Then we’ve got DATEPART() in combination with the “dy” parameter.  This returns the numeric value for the day of the year that the date parameter represents.  The following query returns 336 when ran today (December 2, 2009):

select DATEPART(dy, GETDATE())

(For readability purposes I will not repeat “when ran today” each time, the remainder of the post assumes everything is being executed on the aforementioned date.)

These two functions are being used in the WHERE clause.   So what the WHERE clause says is that we want all numbers smaller than 336.

Okay, so let’s move on to the SELECT.  It uses the DATEADD() function to subtract the retrieved number values from the current system date.  How does that work?  In combination with the “dd” parameter it tells the function what we’re working on are the days, and the minus sign in front of the number field ensures that days are being subtracted instead of added.

And to finish off, the value returned by DATEADD() is being converted to the date type using the CAST() function.  This removes the time value of the datetime type which is good because all we needed are dates.

This is what the query returns:

YTD date values

As you can see, we get 336 records which matches exactly with the values returned by DATEPART(dy, GETDATE()).  (BTW: the number field starts at zero which is why we get 336 instead of 335 records here)

For more advanced usage of a list of numbers I’d like to point you the following article by colleague Experts Exchange Expert Mark Wills: Fun with MS SQL spt_values for delimited strings and virtual calendars

Have fun!

Valentino.

  • Share/Bookmark

Tags: ,

« Older entries

© 2008-2010 A Developer's Blog All Rights Reserved