Recording Available for Reporting on a Cube with SSRS 2008 Webinar

Thanks to all of the attendees that showed up for my webinar yesterday. I had a great time speaking and I hope everyone enjoyed the webinar and maybe even learned something.

If you missed the webinar, Reporting on a Cube with SSRS 2008, have no fear! There is a recording available for you to watch. Just head over to the Pragmatic Works CMS site and create a free account. Not only will you be able to watch my webinar from yesterday, you’ll also be able to check out tons of other great webinars from some really sharp speakers. So head over to the Pragmatic Works CMS site, create a free account, and watch my webinar here.

Free Webinar Tomorrow (7/21) @ 11 am EST: Reporting on a Cube with SSRS 2008

Tomorrow (7/21) I’ll be speaking on reporting on an SSAS cube with SSRS 2008 at 11 a.m. EST. It’s a completely free event hosted by Pragmatic Works.

In this session I’ll give you an introduction to reporting on a cube with SQL Server Reporting Services (SSRS). You’ll learn about some of the advantages of using your cube as a source for your SSRS reports, how to build reports based on your cube with minimal knowledge of MDX, as well as a few tricks you can use to increase the usability of your reports.

It’s completely free to attend and you can do it during your lunch break. I’ll also be taking questions so don’t forget to get registered!

Encrypting Sensitive Information in SSIS Package Configurations using BI xPress

One of the downfalls to SSIS package configurations is that it does not natively allow you to encrypt your sensitive information, such as a connection string or a variable, that you may be configuring with an outside package configuration. There are some work-arounds that you can impliment that will allow you to automatically encrypt and decrypt stored passwords, but its not pretty. The good news is that now, with BI xPress, SSIS developers can easily and automatically encrypt any configured property of an SSIS package. So lets walk through using the BI xPress Package Configuration Organizer and look at a couple of the different options we have when encrypting an SSIS package configurations.

There’s a couple ways you can access the BI xPress Package Configuration Organizer. Just right-click on some empty space in your Control Flow design area and select Package Configurations (BI xPress).


You can also click the BI xPress menu at the top of BIDS and then select Package Configurations (BI xPress).


This should open the BI xPress SSIS Configuration Wizard. This is the wizard that will allow you to select which package properties you would like to configure and encrypt. Make sure you check the box to Enable package configurations.


On the next screen of the wizard, you’ll need to set a few options. The first is to decide what type of Configuration you want to use. You can choose from several options seen below, but I’ll be using an XML configuration file.


After you specify the Configuration Name, click Next.

On the next screen specify which property you want to configure/encrypt. At the top of the window, I can select an encryption mode, I can choose to encrypt only the sensitive data or the full value of the property. In this example, I’m encrypting the connection string for the connection manager OLEDB_SSISOPS_1, so I’ve checked the check box next to the ConnectionString property under OLEDB_SSISOPS_1. We can also select an algorithm type. I’m using the Advanced algorithm to encrypt the connection string. Using these settings, the entire connection string for this connection will be stored in the configuration file in an encrypted format.



Click Next and on the next screen click Start to generate the configuration file. Once the wizard is finished a summary screen will appear. Click Finish to close the wizard.

And you’re done! Here is a screen shot of the configuration file. You can clearly see that connection string is encrypted and unreadable.


Finally, it’s a piece of cake to encrypt sensitive information stored in package configurations!

The Best SQL Documentation Tool on the Market

If you read my blog you know from time to time I will blog about an exceptional third party SQL tool. Previously I’ve blogged about Task Factory and BI xPress, two amazing SSIS development tools. Well this week I figured I would talk about a killer SQL documentation tool called BI Documenter.


BI Documenter allows you to quickly and easily document your entire BI environment. So unlike the other popular tools out there, BI Documenter will document SQL Server databases (2000, 2005, 2008 & R2), SSIS packages, SSAS cubes, and SSRS reports. Because all of these objects can be documented in a single snap shot, you are then able to easily perform impact analysis across all objects, which has a pretty slick UI and is very intuitive. You can also do snapshot comparison across your whole stack, so you can see how any object in a snapshot differs from a previous snapshot.


One other thing to mention about documenting SSIS packages is that BI Documenter is the only documentation tool that not only documents the SSIS packages textually, but also visually!


This means that after you document you SSIS packages, you can view the SSIS packages (Including the Data Flow Tasks!) just like you would in BIDS. Very cool!

BI Documenter is a pretty cool documentation tool that is very well rounded and since it’s the only tool on the market that can document the entire BI stack all together, its basically you’re only choice if you want to do impact analysis across the stack. BI Documenter also has a lot of awesome features coming up in the next release such as documenting server level objects, like SQL Agent Jobs, Linked Servers, Backup Locations, and others.

If you’re in the market for a rock solid documentation tool with the kind of support you can count on, I’d highly recommend that you check out BI Documenter and download the free trial. It’s at the very least worth a look.


MDX Aggregate Functions Can Not Be Used on Calculated Members in the Measures Dimension

If you’ve ever tried to use the Aggregate function on a calculated measure, you’ve seen the following error:

Aggregate functions error

This is a problem if you’re trying to calculate something like Year To Date for a calculated measure. Take the following MDX query (which I understand doesn’t make sense and isn’t something you’d do in a real world situation, but just go with it for the sake of the example), which can be run against the Adventure Works cube, for example:

WITH MEMBER [Measures].[YTD Average Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Average Unit Price] ) Select [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]


If you execute this query, it will complete successfully with #Error as the results. You have a couple different options. First, you could replace the Aggregate function with the Sum function. If you are unable to use the Sum function, then you’ll have to  adjust the way you calculate Year to Date Average Price. We’ll have to break this up into a couple different calculations.

WITH MEMBER [Measures].[YTD Unit Price] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Unit Price] ) MEMBER [Measures].[YTD Transaction Count] AS Aggregate ( PeriodsToDate( [Date].[Calendar].[Calendar Year], [Date].[Calendar].CurrentMember) , [Measures].[Internet Transaction Count] ) MEMBER [Measures].[YTD Average Price] AS [Measures].[YTD Unit Price]/[Measures].[YTD Transaction Count], format=CurrencySelect [Measures].[YTD Average Price] on0, [Date].[Calendar].[Date] on1 From [Adventure Works] Where [Date].[Calendar Year].&[2003]


Doing our calculation this way, we are able to work around our inability to use the Aggregate function on a calculated member. I hope this gives someone an idea of how they can work around this annoying issue.

SSAS Workshop in Phoenix, AZ 6/21 – 6/22


Coming up next week starting June 21 through June 22, I’ll have the really cool opportunity to teach the SSAS Workshop with Brian Knight of Pragmatic Works during the Expedition Denali Workshops tour in Phoenix, Arizona. This will be my first time teaching at the Expedition Denali tour and I’m so excited. It’s going to be awesome! We’re going to be covering everything from dimensional modeling to building a cube to writing MDX and even Data Mining. It’s two full days jam packed with great information that’ll get your BI team ready and able to start developing an AS solution in your environment.

If you’re looking for some top quality training from industry experts, I’d highly suggest you consider registering for this great training opportunity. We’ve still got seats available for the workshop in Phoenix, so just go here to get registered. If Phoenix is just too darn far away for you, we’ve got a bunch other workshops lined up at other locations such as Toronto, Chicago, Dallas, Charlotte, and more. We’ve got seat available but make sure you get registered soon because these awesome workshops fill up fast!

Add 26 High Powered Tasks to your SSIS Toolbox

As a Business Intelligence Consultant with Pragmatic Works, I have the pleasure of being able to take for a spin any of the sports cars in the garage of Pragmatic Works software. And if you’ve frequented my blog before, you’ve heard about how much I love using BI xPress to speed up my SSIS package development. One of the coolest pieces of software out of Pragmatic Works is Task Factory.

Task Factory is a collection of high performance SSIS tasks that allows you to add and extend layers of functionality to your SSIS packages. One of the most powerful transforms that is included with Task Factory is the Dimension Merge SCD, formerly known as the Kimball SCD transform.

Dim Merge SCD

The Dimension Merge SCD transform is 100 X faster at handling Type 1 and Type 2 slowly changing dimensions than the native Microsoft SCD component which uses the slow-as-mess OLE DB Command transform. Being able to handle very large dimensions up to several million rows makes the Dimension Merge SCD included with Task Factory one of the most powerful transforms included with Task Factory. The Dimension Merge SCD offers the power and flexibility of the Kimball SCD with the support of the expert staff at Pragmatic Works.

One of my personal favorite new transformations just recently added to Task Factory is the Regex Replace transform.

Regex Replace

Previous to the development of the Regex Replace transform, I would have to use a Script Component and write some code to use regular expressions. But with the Regex Replace transform, I can easily and quickly use reg ex to either replace reg ex matched data or extract the reg ex matched data to a new column or the existing column.

The newest version of Task Factory includes 26 SSIS tasks and transforms that definitely are worth taking a look at. And if you’re even a little bit familiar with Pragmatic Works, you know of our dedication to the SQL community. With that said, Pragmatic Works offers a completely free Community Edition version of Task Factory, which includes five tasks and transforms such as the File Properties Task and the Data Validation Transform. Go here to download the latest version of Task Factory.

Dustin Ryan is a Data Platform Solutions Architect with Microsoft interested in Azure, SQL Server, and Power BI.

%d bloggers like this: