Jessica M. Moss

Happy New Year Resolutions

noreply@blogger.com (Jessica M. Moss) — Thu, 03 Jan 2013 16:00:00 +0000

Happy 2013! 2012 was a great year, and I hope that 2013 will be even better. To assist, I decided to create resolutions this year. I don't usually create resolutions because I believe that they aren't maintainable. Too often we hear of the gym membership that expired after a month due to disuse, the 20 pounds to lose that became 2 pounds, the bad habit that we were going to break... tomorrow…

But what I do like about new year resolutions is that the new year is a fresh start, a way to wipe the slate clean and wish for a better and brighter future. To make sure my resolutions don't become those drift into the ether, I need specific tasks that will force me to accomplish this.

So with that optimistic outlook, my professional resolutions for 2013:

Blog twice a month. This will be my hardest resolution based on past history. ;)
Tasks:

Tell people I'm going to do this (DONE!)
Create an initial list of blog topics and dates - is there anything you want to read about?
Add task reminders to my RememberTheMilk list so that I don't forget

Learn more about PowerPivot, BISM, and DAX.
Tasks:

Read DAX Formulas for PowerPivot: The Excel Pro's Guide to Mastering DAX (already purchased)
Create a new prototype to practice these skills

Learn more about Windows Azure SQL Reporting. I haven't delved into the exciting world of Microsoft cloud options, and I want to be able to intelligently share the different options available to people.
Tasks:

Set up my own account to explore the options
Create a comparison chart of pros and cons for cloud versus traditional BI/reporting options

Explore EIM integration of DQS, MDS, SSIS. Matt Masson delivers a great presentation that shows how to use these three products together to create an EIM infrastructure. I believe the real power of these tools include using each product to create a full data ecosystem.
Tasks:

Find a realistic example and implement a solution

Employee Management Training. On the "soft skills" side, I need to learn more about management. I've recently agreed to start taking on minions--er, I mean managing employees. This is an entirely new skill for me. Since it directly affects the development and career of others, I really don't want to screw it up.
Tasks:

Harvard ManageMentor training. I am lucky to be part of a company that believes in training its employees by offering opportunities such as this. I plan on utilizing this service this year
Read The First-Time Manager

I think that's enough to start 2013 off right. What are your resolutions?
Jessica

PASS Summit 2012

noreply@blogger.com (Jessica M. Moss) — Mon, 05 Nov 2012 21:00:00 +0000

For the fifth year in a row, I am excited to present at the PASS Summit in Seattle, WA. The PASS Summit is the premier SQL Server conference in the United States for anyone interested in SQL Server, SQL Server Business Intelligence, and Microsoft data technologies. I always learn something new when I attend, and I enjoy catching up with old friends. If you’re a new friend that I haven’t met yet, please introduce yourself to me at one of the following locations:

11/5/2012 8:30am-4:30pm SSIS Design Patterns pre-conference session

11/5/2012 6:00pm-9:00pm Networking Dinner at Gordon Biersch

11/6/2012 6:30pm-8:00pm PASS Summit 2012 Welcome Reception

11/7/2012 11:30am-12:30pm Book Signing – PASS Bookstore

11/8/2012 1:00pm-2:00pm & 3:00pm-4:00pm Book Signing – Apress Booth

11/9/2012 9:45am-11:00am Jessica’s Session: “Getting Reports on Your Schedule”

11/9/2012 11:30am-12:30pm Birds of a Feather table: Data Warehousing

Hope to see you then!

SSIS 2012 Design Patterns 24HOP Wrap-up

noreply@blogger.com (Jessica M. Moss) — Mon, 22 Oct 2012 15:00:00 +0000

Thank you to everyone who attended Michelle Ufford's and my presentation on SSIS Design Patterns for the 24 Hours of PASS session. Michelle's demo materials can be found here: http://sqlfool.com/summit2012/. My demo materials for a 2012 template and some reporting can be found here: http://www.jessicammoss.com/Demo/24HOP_Demos.zip.

If you missed it, the session recording is available here: http://www.sqlpass.org/24hours/fall2012/.

And if you want to see more design patterns, come see us in Seattle for a full day training session: http://www.sqlpass.org/summit/2012/Sessions/SessionDetails.aspx?sid=3159.

Enjoy!

SQL Server 2012 Integration Services Design Patterns

noreply@blogger.com (Jessica M. Moss) — Thu, 20 Sep 2012 15:00:00 +0000

A book, a pre-conference training session, and a webinar - oh my!

The Book

Over the past two years, I have been lucky enough to work with some of the great SSIS-gurus, Andy Leonard, Matt Masson, Tim Mitchell, and Michelle Ufford, on a book project that has finally been realized. I am pleased to announce the publication of SQL Server 2012 Integration Services Design Patterns (amazon | apress). Thank you to everyone who helped with the writing, editing, and reviewing of the book.

The Pre-conference Training Session

The PASS Summit has invited the ENTIRE author team to give a pre-conference training session on Monday, November 5, 2012. This training session gives you the chance to ask anything you want from the author team and take home advanced knowledge that can immediately be implemented. SSIS Design Patterns description:

In this full-day session, the "SSIS Design Patterns" (Apress, 2012) author team – Matt Masson, Tim Mitchell, Jessica M. Moss, Michelle Ufford, and Andy Leonard – will describe and demonstrate patterns for package execution, package logging, loading flat file and XML sources, loading the cloud, dynamic package generation, SSIS Frameworks, data warehouse ETL, and data flow performance.

The Webinar

As a preview of our pre-conference session, Michelle and I will be discussing a few design patterns during the 24 Hours of PASS webinar schedule for September 20, 2012 at 10:00PM EST. I've had a blast working with Michelle, and I believe this session will give you something to use today, as well as provide an idea of what you can learn in a whole day session. Tune into SSIS Design Patterns for Fun and Profit:

As a preview of the PASS Summit 2012 pre-conference session "SSIS Design Patterns" by Andy Leonard, Matt Masson, Tim Mitchell, Michelle Ufford, and Jessica Moss, Michelle and Jessica will present design patterns to assist in the day-to-day day activities of SSIS 2012 development. Learn how best to set up a template package, to utilize the framework for your SSIS development, to review the metadata of executions, and more. This online session will provide you information you can use today as well as provide a preview of the types of things you can learn in this fall's pre-conference session.

I look forward to hearing your thoughts on our book, seeing you at the training session, or answering your questions on the webinar. Happy ETL-ing!

Upgrading and Overhauling SSIS Q&A

noreply@blogger.com (Jessica M. Moss) — Fri, 07 Sep 2012 15:00:00 +0000

Thank you to everyone who attended my School of Wrox presentation "Upgrading and Overhauling Your SSIS Packages for 2012" last month. There were many remaining questions at the end of the session that we weren't able to fit. The questions below are the unaddressed ones asked during the session that are specific to upgrading to 2012. Enjoy!

Will SSIS 2008 package run AS IS in 2012?

Yes, the SSIS 2012 service will temporarily convert the package in its 2005 or 2008 format to the 2012 format when the package runs. The package may not execute properly if it references providers or assemblies that are not available, so upgrading the package before deploying is highly recommended. For more information, see: http://msdn.microsoft.com/en-us/library/bb522577.aspx.

Can we integrate file based execution in catalog in DB and generate reports about the executions?

Storing packages for execution on the file system is only possible in the Package Deployment Model. To utilize automatic logging, you need to deploy your SSIS packages to the SSIS Catalog.

Is it mandatory to create a Project deployment even if there is only one package in the Project?

You can run one package using the Package Deployment Model, but you will not have access to the 2012 features, such as automatic logging and parameterization. To use the new 2012 features, you will need to use the Project Deployment Model, no matter how many packages the project includes.

If you need to change one package within the Project, do you redeploy the whole Project or you can select to redeploy the changed package only?

You will need to redeploy the whole project. For additional information on this, please see this blog post: http://www.mattmasson.com/index.php/2012/07/can-i-deploy-a-single-ssis-package-from-my-project-to-the-ssis-catalog/

Is there a manifest file created during the deployment (just like in 2005) which a DBA uses to deploy the packages?

Yes, in the Project Deployment Model, an .ispac file is created which can be used to deploy packages.

Can we have more info on the that stored proc that is used to call the SSIS jobs?

To execute an SSIS package, you can use the create_execution stored procedure. For more information, please see: http://msdn.microsoft.com/en-us/library/ff878034.aspx.

Is there a way to have a "global" set of parameters that would apply to all projects?

Environments are applied at a project level, but can be programmatically created. To mimic a global set of parameters, I would try a programmatic environment creation script that could be applied to each project, keeping in mind that it would be copied multiple times.

Can we still use dtexec to run the packages from the cmd prompt?

Yes, you can still use the dtexec executable. Note that behind the scenes, the executable is still calling the stored procedure referenced above. For more information, see: http://msdn.microsoft.com/en-us/library/hh231187.aspx#server

Will the upgrade tool have issues with a package that has third party data flow tasks?

To use custom components, you can update configuration files, as described here: http://blogs.msdn.com/b/mattm/archive/2011/08/09/making-your-existing-custom-ssis-extensions-and-applications-work-in-denali.aspx. In the long run, it is recommended to upgrade your components to 2012.

Learn Reporting Services in a Day!

noreply@blogger.com (Jessica M. Moss) — Tue, 28 Feb 2012 21:00:00 +0000

I am excited to be presenting a pre-conference session at the Atlanta SQLSaturday on April 13, 2012. The session is entitled Learn Reporting Services in a Day! and is a full day introduction to SSRS 2008 R2. It costs $99.00 through March 15, and $109.00 after that. There are only 16 seats left, so don’t hesitate to sign up here:

http://ssrsinaday.eventbrite.com/

In addition, there is a full slate of sessions on April 14, 2012, including my session: Report Parts: Increasing Productivity Since 2008R2.

Here is the information about the pre-conference session:

SQL Server MVP, Jessica M. Moss, presents an exciting, introductory, full day training session on SQL Server Reporting Services 2008 R2. In the three-part class, Jessica will teach you how to build reports from the ground up. In Part 1, learn the basics of report development, including picking a report development tool and creating your first report. Part 2 delves into visualizations, groupings, and drill-down functionality. Finally, Part 3 highlights core administration tasks in Reporting Services. In addition, Jessica will point out industry-wide best practices for report development and show numerous live demos using a variety of data sources.

9:00-12:00

101: The Basics

Introduction
Report Builder vs. BIDS
Data Sources & Datasets
Set Reports
Additional Toolbox Items
Interactive Exercise A

1:00-4:00

201: Intermediate

Visual Reports
Dashboard Reports
Grouping/Sorting
Parameters/Filters
Calculated Expressions
Drill-Down and Drill-Through
Interactive Exercise B

4:00-5:00

Administration

Configuration Modes
Report Manager
Deployment
Delivery
Security

I look forward to seeing you there!

Displaying Images from Analysis Services

noreply@blogger.com (Jessica M. Moss) — Wed, 22 Feb 2012 16:00:00 +0000

One of the things we love in business intelligence is pretty pictures. We want to be able to show graphs and KPIs that really highlight information and direct people to an accurate conclusion. We also want to be able to show images that help tell a story, such as a picture of a certain location or the logo for a store. Analysis Services provides the functionality to show images, but it’s a little buried. Let’s walk through the steps of making it easier for you to access! Our final product will look like this:

Setting up Analysis Services to show the image

Let’s work with the AdventureWorks 2008 R2 sample database and Analysis Services cube. In the product dimension, there is already an attribute in the database and Analysis Services cube called LargePhoto with a data type of varbinary(max). This contains an image that is associated with each product in the dimension. In fact, in the Analysis Services dimension, we can see that it has been set up so that the ValueColumn for that attribute uses that value, while the key (and by default, the name) column uses the surrogate key of the product dimension.

To make the logo as accessible as possible, we want to include the value as a measure. This will allow report developers to drag and drop the value directly onto their query pane in Reporting Services.

Let’s create a measure called [Product Photo] with the expression:

[Product].[Large Photo].MEMBER_VALUE

Using the image in Reporting Services

After building and deploying the new cube, we can set up our SSRS dataset.

We use the dataset in a table that contains Products in the first column’s detail row, and an image in the second column’s detail row. Set up the image properties as shown here:

This will use the photo associated with that product to display as an image for each row, and you get the final product as shown above!

Version used: SSAS 2008 R2, SSRS 2008 R2, Report Builder 3.0

SQL Server Reporting Services is Free!

noreply@blogger.com (Jessica M. Moss) — Sat, 28 Jan 2012 20:48:00 +0000

You may not be aware of this great side of SQL Server: free Reporting Services. When I say free, I don’t even mean “comes with the SQL Server that you already paid for, so is sort of free”, I mean “any random person off the street can use it for free, as in beer” I know, the first time I heard it, I was a little skeptical too. Fortunately, it is absolutely true!

SQL Server Express Edition with Advanced Services comes with the ability to create your own reports. There are some limitations of this version; for a full list see this site. A few of the larger limitations are listed here:

You can only display data from the SQL Server Express databases on the same server
The Reporting Services metadata will be stored in the SQL Server Express database on the same server
You can only run on-demand reports, which means no subscriptions

This version is a great way to try out the capabilities of Reporting Services and learn how to develop reports. And on top of that, everything you create can also be deployed to another edition of Reporting Services. You can download the Express version here: http://www.microsoft.com/download/en/details.aspx?id=25174.

If you’re interested in learning about general reporting or the capabilities of Reporting Services, I highly recommend you check it out!

My Thanks to the #SQLFamily

noreply@blogger.com (Jessica M. Moss) — Thu, 22 Dec 2011 04:02:00 +0000

I've decided to return from my blogging hiatus for a great cause: supporting the #SQLFamily. I saw a link on twitter explaining that the Microsoft SQL Server team will donate to the Pragmatic Works Foundation based on #SQLFamily stories. I think it is appropriate that my story surrounds Mr. Brian Knight himself. Hence, my story:

I was just starting to become engaged in the SQL community in a little town called Tampa, Florida. I had recently decided to start consulting independently and was eagerly gathering any tidbits of information I could find. I was speaking at the first SQLSaturday event in Orlando, where I was talking to some of the speakers. I mentioned to Brian in the morning, who I had only known a short while, about the new adventure I was undertaking. After lunch, he found me to give me a copy of Joe Webb's "The Rational Guide to IT Consulting" to help me in my career.

Only in the #SQLFamily would such a busy, expert, and community member provide help to an essentially unknown, new speaker with no consideration for himself.

Thank you to the #SQLFamily, Brian, and the many others who have helped me throughout my career, and Happy Holidays to all!

Training by Jessica

noreply@blogger.com (Jessica M. Moss) — Thu, 13 May 2010 15:53:00 +0000

I have a few events coming up that I wanted to let you knew about. If you attend, be sure to say hi!

Extract, Transform, and Load your Data Warehouse

The SQLPASS Business Intelligence Virtual Chapter always provides great information for both beginners and more advanced BI developers. I’ll be presenting at noon EST on 5/14/2010 on Extract, Transform, and Load your Data Warehouse. We’ll talk about why ETL is different/important for data warehousing and give examples of more advanced dimensional load scenarios you may face.

Adding SSRS Report Bells and Whistles

I’m traveling down to Raleigh, NC to speak for the Triangle SQL Server User Group on 5/18/2010 at 6:30pm EST. I’ve really enjoyed giving this presentation, where I speak about best design practices for SSRS 2008. I hope to get lots of great questions as well!

Easier than Ever Report Authoring in SSRS 2008 R2

The second annual 24 Hours of PASS will be held from on May 19th for the entire day. Yes, that’s right – 24 sessions, back-to-back, one right after another. Many of the sessions are on SQL Server 2008 R2, so if you have been considering upgrading or would like to learn about the new functionality, you should definitely check it out. My session, entitled Easier than Ever Report Authoring in SSRS 2008 R2, is on 5/19/2010 at 6:00PM EST.

IndyTechFest

This will be my first time attending the IndyTechFest conference in Indianapolis on 5/22/2010. I will be presenting a different variation of my Extract, Transform, and Load your Data Warehouse presentation as well as participating in the Women In Technology Luncheon panel. I’m looking forward to meeting lots of new people during my two sessions.

Prairie Developer Conference

Last, but certainly not least, is the Prairie Developer Conference in Regina, Canada. My good friend, D’Arcy Lussier, is putting this conference on for the first time to provide great training to middle-Canada. I will be speaking on two topics: Incorporating Reporting Services into SharePoint and Who Needs a Data Warehouse? You still have time to register before the conference on 6/2/2010-6/3/2010, so what are you waiting for?

SQLSaturday Richmond Wrap-up

noreply@blogger.com (Jessica M. Moss) — Tue, 27 Apr 2010 16:42:00 +0000

What an amazing event. Over the past several years, I have been an attendee, volunteer, and speaker for a number of SQLSaturdays and code camps. Not until recently have I understood the amount of work and effort that an event of this type entails.

SQLSaturday events are community-driven events that provide free training for SQL Server DBAs, developers, and BI professionals. Started in 2007 as the brain child of Andy Warren, Steve Jones, and Brian Knight, the brand has recently been acquired by PASS, the Professional Association for SQL Server.

On April 10, 2010, Richmond, VA put on a SQLSaturday at the local ECPI College of Technology. Thank you so much to the speakers, sponsors, and volunteers who made this event possible. A huge thanks goes to the leadership team of Andy Leonard, Kevin Israel, Ron Deskins, and Steve Fibich who put in many hours of work!

As most of these wrap-ups go, I feel there were some things that we could have done better, and I wanted to share my thoughts to hopefully help future event leaders!

Volunteers

Volunteers are a godsend. Do not underestimate the need for multiple volunteers throughout the entire day. At one point when I was manning the registration table, I needed to grab an attendee to sit at the table while I ran away to take care of a room situation. (Thanks, Jimmy!) Speaking of room situations...

Technical Equipment

Check your projects and/or any other technical equipment needed the day of the event. We had a projector that decided to cut off of a fifth of the screen! We were lucky to be able to move to another room and readjust accordingly. Having a back up room and/or back up projector is a must.

Evaluations

We elected not to have evaluations for the event based on time and budget constraints. In hindsight, I wish we had deemed this more important. I really only have my own perspective and a few feedback emails to judge how the event went. It would be nice to know if we missed anything that was important!

Raffle

After raffling off the sponsors' raffle prizes, we had a raffle for everyone to win some books donated by Microsoft. Trying to be proactive, we had put everyone's name in the box who had signed up to attend. Unfortunately, with a 30% drop off rate, that means we pulled quite a few names of people who were not there. This actually could tie in very well with Evaluations, where we use the forms that people fill out to pick the winners of the final raffle.

Materials

Tell your sponsors they must send their materials to be including in attendee bags/raffle signs/raffle prizes to you ONE WEEK BEFORE THE EVENT. The items must arrive ONE WEEK BEFORE THE EVENT. Send the sponsors multiple emails to make sure the materials are there ONE WEEK BEFORE THE EVENT. Did I say that enough times? ;) We did not clarify a time for materials to arrive, and due to some last minute postal crises, had some extra details to work out the night before the event. Better to be safe than sorry, so make sure you receive your materials ONE WEEK BEFORE THE EVENT. ;)

Beginning of the day

Start your morning setup at least one hour before you tell attendees registration will open. We had some attendees who were so ready to learn some SQL Server, they arrived 25 minutes early! We had to ask them to wait as we finished setting up the registration table, breakfast, and last minute room adjustments.

I also wanted to highlight a few things that I think went pretty well!

Schedule

We received a few compliments on our massive color coded schedule. Each room had its schedule on the door in the appropriate color, that matched the wall schedule, and matched the map that each attendee received. We were easily able to switch rooms or make adjustments to both the wall-schedule and the door-schedules. I think it worked out well for both the attendees and leadership team!

Lunch

I cannot stress how great boxed lunches are. We ordered lunches from Firehouse Subs, which each came in nice, neat, easy boxes. Attendees grabbed a box, grabbed a drink, and were on their way. This resulted in no line during lunch, and very little mess to clean up! (Yes, that is my flow diagram for lunch - there is no such thing as too many signs or too many instructions ;) )

Sponsors

We set up our sponsors in the same room as breakfast and lunch. This allowed attendees to grab some food and browse the sponsor's wares at the same time. Based on the amount of raffle tickets in the sponsors’ raffle boxes, I think we got almost everyone to visit the sponsors!

Volunteer Instructions

This could be me going overboard, but we created instructions for the folks at the registration table. This included FAQs that attendees may ask, things to mention to the attendees when they checked them in, and the different steps they needed to do at the table. This worked out well because we could hand the volunteer the instructions and let them have at it, while we focused on other items of interest!

Here are a few posts from SQLSaturday Richmond speakers as well:
http://jasonhall.blogs.sqlsentry.net/2010/04/sql-saturday-30-richmond-virginia.html
http://nullgarity.wordpress.com/2010/04/14/integration-services-in-the-real-world-slide-deck
http://robertlambert.net/2010/04/sql-saturday-30-richmond-virginia-april-10-2010

Do you have feedback? Thoughts? Want to run your own SQLSaturday? Leave a comment below!

Different Items per SSRS Column Group

noreply@blogger.com (Jessica M. Moss) — Mon, 05 Apr 2010 21:48:00 +0000

I had an interesting reporting scenario posed to me that I thought I would share! (The actual business information has been changed to protect the innocent.) In this scenario, we own multiple stores that sell a dozen or so products. We would like a report that shows the type of products that each store has sold on a particular day. Keep in mind that we could start stocking new products at any time, and we would still like our report to work.

Here is our existing data for the database:

3/4/2010	Sports-R-Us	Virginia	Softball Glove
12/15/2009	Sports-R-Us	Virginia	Tennis Racquet
12/15/2009	Sports-R-Us	Virginia	Dartboard
6/6/2009	Sports-R-Us	Virginia	Fishing Pole
6/6/2009	Games And More	Virginia	Canoe
2/21/2010	Games And More	Maryland	Pool Table
2/21/2010	Games And More	Maryland	Golf Bag
2/21/2010	Games And More	Maryland	Canoe
2/21/2010	Games And More	Maryland	Softball Glove
2/21/2010	Games And More	Maryland	Football

This scenario really comes into play when you have many different and unknown items in your column grouping, and you need to show them in a compact space.

We can use the following query to simulate this situation:

SELECT '03/04/2010' as TransactionDate, 'Sports-R-Us' as StoreName, 'Virginia' as StateProvince, 'Softball Glove' as Product
UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Tennis Racquet'
UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Dartboard'
UNION ALL SELECT '06/06/2009', 'Sports-R-Us', 'Virginia', 'Fishing Pole'
UNION ALL SELECT '06/06/2009', 'Games And More', 'Virginia', 'Canoe'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Pool Table'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Golf Bag'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Canoe'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Softball Glove'
UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Football'

At first glance, this seems like a simple matrix scenario. If we create a dataset using our query, we can use the fields in a matrix that contains a detail grouping for our rows on TransactionDate, StoreName, and StateProvince with Product as a dynamic column grouping at the end. Unfortunately, this will result in a lot of blank spaces for the Product columns, as the data per row will only display for the associated group. If you see a matrix that looks like this, you may have a similar situation!

To get the desired layout, we need to make three changes to our report. First of all, modify the query to give a ranking per every group, which means we get to use one of my favorite clauses: OVER. In our scenario, we will partition based on TransactionDate, StoreName, and StateProvince and order by Product. Here is our new query:

SELECT TransactionDate, StoreName, StateProvince, Product
      , ROW_NUMBER() OVER ( PARTITION BY TransactionDate, StoreName, StateProvince ORDER BY Product ) AS ColumnGroupNumber
FROM
(
      SELECT '03/04/2010' AS TransactionDate, 'Sports-R-Us' AS StoreName, 'Virginia' AS StateProvince, 'Softball Glove' AS Product
      UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Tennis Racquet'
      UNION ALL SELECT '12/15/2009', 'Sports-R-Us', 'Virginia', 'Dartboard'
      UNION ALL SELECT '06/06/2009', 'Sports-R-Us', 'Virginia', 'Fishing Pole'
      UNION ALL SELECT '06/06/2009', 'Games And More', 'Virginia', 'Canoe'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Pool Table'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Golf Bag'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Canoe'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Softball Glove'
      UNION ALL SELECT '02/21/2010', 'Games And More', 'Maryland', 'Football'
) AS OriginalQuery

The second change that we need to make is in the matrix itself. On the Product column grouping, we change the field so that it groups on our new "ColumnGroupNumber" field. Don't change the field in the detail row, as we would still like to show each item. Once we have made that change, every set of Products will start in the first column.

Our final change is to change the header expression so that it only shows the header on the first column and doesn't repeat fifty times. We do this by modifying the expression of the textbox to this:

=IIF(Fields!ColumnGroupNumber.Value = 1, "Product", "")

When we put all of the pieces together, we end up with a report that looks pretty darn good!

SQLSaturday Richmond 2010

noreply@blogger.com (Jessica M. Moss) — Thu, 03 Dec 2009 13:00:00 +0000

Do you have a neat T-SQL trick that you'd like to show off? Is your idea of a good time figuring out Analysis Services hierarchies? Have you solved world peace using SQL Server? If so, you should submit an abstract to speak at SQLSaturday Richmond!

We are currently accepting speaker submissions. You can submit an abstract by going to the website: http://www.sqlsaturday.com/30/callforspeakers.aspx! Please submit by 12/12/2009.

If you're not interested in wowing people with your extensive SQL ninja skills, we hope you will consider attending this amazing SQLSaturday event first time ever in Richmond, VA. Obligatory marketing blurb with further information below:

Join the Richmond developer community for a free, one-day training for SQL Server professionals, developers, DBAs, and even people who don't know how to spell SQL.
When: January 30, 2010
Where: ECPI College of Technology
4305 Cox Rd
Glen Allen, VA
Admittance to this event is free; all costs are covered by donations and sponsorships. Please Register soon as seating is limited, and let friends and colleagues know about the event. For more information, visit http://www.sqlsaturday.com/ and select “SQLSaturday #30 - Richmond 2010”

I won't be speaking this time around, but I will be there to help things run smoothly! I hope to see all of you there :)

Installing and Configuring SSRS in SharePoint Integrated Mode

noreply@blogger.com (Jessica M. Moss) — Wed, 25 Nov 2009 05:29:00 +0000

To put it bluntly, configuring SQL Server Reporting Services 2005 or 2008 to run in SharePoint integrated mode with Microsoft Office SharePoint Server 2007 is a pain in the behind. The setup of the numerous moving pieces seems to get confused between two sets of configurations, two sets of security, and two sets of databases. (I would get confused with all those pieces too! ;) ) Let's take a look at some highlights of the installation steps and a few warnings that may help you in your environment.

Components
It makes sense that you would need Reporting Services (2005 with SP2 or 2008) and SharePoint (WSS 3.0 or MOSS 2007) in some fashion on your servers. The secret ingredient to tie everything together is actually an extra add-in that must be installed on your SharePoint server. This is a free download that can be found here (2005) or here (2008).

Security
Once you have installed all components, you must complete a series of screens in the SharePoint Central Administration tool. The screens tell SharePoint where the report server resides and sets up the security so that the two pieces can talk to each other. You need to be sure that you have a user account that has access to both server/databases to facilitate this.

Warning, warning!
Here are a few road bumps that I have run into in the past:

When you configure your SSRS databases, ensure that you have selected the option to create the database in SharePoint Integrated mode. Each mode creates its own type of database and never the twain shall meet.
Once you get your environment set up, make sure Reporting Services reports are stored in the Default zone. Otherwise, the reports will not render.
Don't mix your SharePoint and Reporting Services (2005) applications in IIS. SharePoint should have sole ownership of its virtual, and SSRS's setup should not be disturbed.

Do as I say, not as I do
To install and configure your environment, I highly recommend that you follow the steps in the EXACT order as written in the Microsoft SQL Server Reporting Services (SSRS) Installation/Configuration Guide for SharePoint Integration Mode whitepaper. While this document is written for SSRS 2005, it very closely aligns with the 2008 method as well. Good luck with your setup!

PASS Community Summit 2009

noreply@blogger.com (Jessica M. Moss) — Sat, 31 Oct 2009 22:13:00 +0000

One of my favorite times of the year is the PASS Community Summit. It's five jam-packed days of SQL Server sessions by Microsoft employees, MVPs, and the people that you read about in technical books. It's pre-conference and post-conference workshops on all different levels of topics of SQL Server. It's a way to meet new people who love to do what you love to do and connect with old friends who you only see once a year. It's a way to get a job or sell a job. It's all of this and so much more that I can't even begin to describe.

I'm excited to attend for the third year starting tomorrow. If you are attending the Summit, I hope you'll come and introduce yourself. You can find me at one of these locations/times:

Monday, November 2, 2009
4:30pm-6:30pm
Networking Pre-Con - I'm really excited to learn about the best ways to meet people and meet other people who are interested in the same thing!

Tuesday, November 3, 2009
11:45am-12:45pm
Birds of a Feather Lunch (Expo Hall 4B) - I am hosting a table during lunch on “Is Business Intelligence an Oxymoron?”. I'm looking forward to hearing your thoughts on how we can incorporate the business into our data warehouse and delivery design.

Wednesday, November 4, 2009
11:30am-1:30pm
Women in Technology Luncheon and Panel Discussion (Room 6BC) - I am one of the panelists discussing “Energizing the Next Generation: Encouraging and Inspiring Young Women to Choose Tech Careers”.

3:00pm-4:15pm
“Adding SSRS Report Bells and Whistles” (Room 612 (165) ) – I am presenting on Reporting Services 2008 design best practices and guidance.

Thursday, November 5, 2009
12:00pm - 12:30pm
Book Signing (South Lobby, across from Summit Bookstore) – I’ll be hanging out by the bookstore as one of the authors of Microsoft SQL Server 2008 Integration Services Problem-Design-Solution. Definitely come by if you have any question about the book!

Of course, I will also be attending sessions and some evening events, so please come and talk to me there instead. I’m looking forward to attending the best Summit yet.

Fall 2009 Conference Odyssey

noreply@blogger.com (Jessica M. Moss) — Fri, 16 Oct 2009 05:10:00 +0000

As promised, I wanted to let you know about a few events where I'll be in the upcoming weeks. This Saturday (10/17), I'm making the trek up to East Iowa for their SQLSaturday event. They pulled in quite a few great speakers, and they've also invited me to speak on my favorite BI products: SSRS, SSIS, and SSAS! It looks like registration is still open, so if you're in the area, you should definitely sign up, come by, and say hi.

The following week, I am pleased to be speaking at the Charlotte SQL Server User Group on Extract, Transform, and Load your Data Warehouse. I'll be making the drive down (audio book suggestions, anyone?) on Thursday, October 22 to speak that evening. I'm looking forward to meeting new people and seeing some old friends in the area. I'll also be staying over for a soon-to-be-announced event on Friday.

The first week of November, I'll be at the PASS Community Summit. These is my third year attending the conference, second year speaking, and first time as a panelist on the WIT luncheon. This conference is so big and has so much going on that I will give it its own post closer to that time.

The following week is the SharePoint Connections conference. My friend and colleague, Kevin Israel, invited me to deliver a joint preconference workshop entitled SharePoint BI - Building Dazzling Dashboards and Sizzling Scorecards in SharePoint. Can you tell which section will be my part? *coughBIcough* ;) The all-day workshop is on November 9, so I'll be taking the rest of the week to attend some sessions and talk shop with attendees. Please do look me up and introduce yourself!

Rounding out the list is the SharePoint Saturday event in Richmond on November 21. I'm not sure if I'm volunteering or speaking yet, but either way, I will be there! The SharePoint Saturday events have taken off like a rocket, and I'm excited to participate.

That wraps up the current list. I hope to see you at one of the events.

Update: Fixed SQLSaturday date

I'm an Author and Other Sort-Of-But-Not-Completely-Related News

noreply@blogger.com (Jessica M. Moss) — Thu, 08 Oct 2009 17:54:00 +0000

Over the years, I've read several posts similar to this one, and now it's my turn! At the beginning of this year, I was asked to participate in writing a new SSIS book with co-authors: Erik Veerman, Brian Knight, and Jay Hackney. After many late nights and just a few cans of Mountain Dew, I'm proud to say that the book has been completed and will be published at the beginning of November. Please check out: Microsoft SQL Server 2008 Integration Services Problem-Design-Solution.

Along with that exciting news comes an apology for not blogging as much as I would have liked. Here's a quick recap from the past several months:

David Giard invited me to do a short video on Integrations Services on his Technology and friends series.
I had a great conversation with Richard Campbell and Greg Hughes on Reporting Services on RunAs Radio.
The PASS BI Virtual Chapter had me speak a few times. I definitely recommend you check out the line up of great speakers/presentations that they have coming up as well!
I flew to Nashville to participate in the devLink conference with two presentations on SSRS and SSIS.
I am honored a second time to be re-awarded a Microsoft MVP Award on October 1. Thank you all for allowing me to come speak at events and share the exciting things I've learned with you.

Also, if you follow me on Twitter, you probably already know a lot of this. And if you don't follow me on Twitter, why not?

More posts to follow on future events and, of course, some technical content as well!

Sitting on the beach in Pensacola

noreply@blogger.com (Jessica M. Moss) — Thu, 09 Jul 2009 03:12:00 +0000

The SQLSaturday phenomenon is taking over the nation! I was honored to be selected to give a presentation at the Pensacola event on June 6, 2009 on PerformancePoint Server 2007 M&A (I'm still waiting to hear more information about next version incorporated with SharePoint :)) I had a great time seeing the SQLSaturday crew, old friends, and meeting some new friends!

While I was down in beautiful Pensacola, I was also thrilled to participate in the third episode of the It's All About The Tools TV Show! I gave a demonstration on the SSIS 2008 data profiling tools - you can check it out here: http://channel9.msdn.com/shows/toolshed/Show-Episode-3-Its-All-About-The-Tools-TV-Show/. Russ Fustino and Stan Schultes put on a great show that I'm sure you're going to enjoy!

I really enjoy speaking at events and sharing information on data warehousing and the Microsoft business intelligence suite. I’ll warn you in advance that I prioritize engagements based on the shortest distance to water, sunshine, and community -- and not necessarily in that order :)

SQLTeach Vancouver 2009

noreply@blogger.com (Jessica M. Moss) — Tue, 19 May 2009 10:00:00 +0000

On June 8-12, I'll be attending and speaking for the DevTeach/SQLTeach Vancouver conference. I've written about SQLTeach before, and I have to say that it's one of my favorite conferences. It's a small group of attendees with speakers who are some of the biggest names in the field. You really get a great opportunity to learn from and hobnob with speakers such as Peter DeBetta, Bill Graziano, Kevin Kline, Joe Webb, and more! I know I'm looking forward to picking their brains on a few things.

A few highlights of the conference:

Monday night: Party with Vancouver IT community DevTeach Kickoff party
Weekend after: Alt.NET event - DevTeach attendees receive a special registration code
Preconferences: 3 preconferences on Silverlight, F#, and Agile Development
Keynote: Tim Huckaby will discuss "Your Development Happy Place"

Dimension ETL from One Source Table

noreply@blogger.com (Jessica M. Moss) — Thu, 14 May 2009 05:24:00 +0000

In an ideal world, the source system of your data warehouse has the exact information that you need to populate all of the fields in your dimensions. In a less than ideal world (also known as the real world), we need to cobble pieces of data together. You may come across one scenario where all of your dimension and fact information is in one large table. How do we handle this ETL in SQL Server Integration Services?

It is possible to load new records into your dimensions while loading your fact. One way would be to use a Lookup transformation to check for existence, and if the business key doesn't yet exist, insert that value, return the surrogate key, and go along your merry way. On the other hand, if you need to use those dimensions for other fact tables, you may decide to load only your dimensions first.

To load all dimensions from one table, we can utilize the Aggregate transformation in SSIS, which provides the capability to perform aggregations on columns in your data flow. The aggregations that are available to you include: Count, Count distinct, Sum, Average, Minimum, and Maximum. You also include a column to group the data. For the output that you're setting up, you can add as many columns to aggregate or group by that you need. For those of you familiar with T-SQL, this concept should be very similar to the GROUP BY clause.

When we're working with aggregations in SSIS, there are a few other things we should mention. The aggregate transformation will not pass through all input columns, only the columns that you specify in your settings. You can set comparison flags on your grouping columns on how to group the data together. I also use the "Ignore case" option to help get a list of case insensitive distinct values from the column.

Initially opening up the Aggregate transformation allows you to set up the columns you want aggregated and what you want to group by. In this scenario, we will use the Group By function for each column to get a distinct list of values; however, if we put all of them on this screen, we will still end up with duplicate values. Instead, we want to create multiple outputs that each contain a single Group By on the appropriate column.

There's a sneaky little button at the top of the designer window that toggles between Advanced and Basic modes. It defaults to Basic, which is why you only see one list right now. Push that button to toggle to Advanced and create a different output for each dimension you need to populate. Once you have a different output, you can perform a lookup against that dimension and only insert records that do not exist. We end up with a package that looks similar to this to load all of our dimensions:

The aggregate transformation has a little bit of a bad reputation, as well it should. It is an asynchronous component, so it creates a new buffer set when it runs, uses a huge amount of memory, and slows down your package execution time. See Kirk's great article about performance tuning SSIS (the aggregate transformation notes are applicable to both 2005 and 2008) for more information: http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ssis-tuning-the-dataflow-task/.

This method may not be the best for your situation. It will load the source data twice and doesn’t take into account any slowly changing attributes. This should only be used if the situation calls for it. Hopefully, this will help you if you do fall into that situation!

Version used: SSIS 2008 SP1

SSIS Insert Statement Using an OLE DB Destination

noreply@blogger.com (Jessica M. Moss) — Thu, 26 Feb 2009 21:51:00 +0000

When building a SQL Server Integration Services package, many business scenarios call for inserting a record into a table if it doesn't already exist. The most commonly used database destination, the OLE DB destination, looks as though it can handle this through the SQL command Data access mode. Unfortunately, SSIS appearances can be deceiving...

In the OLE DB Destination, setting the Data access mode to SQL command causes a SQL command text window to appear. You can perform typical SSIS SQL statement actions, such as building the query through the Graphical Query Designer, importing the SQL from an external file, or parsing the query. Note that there is no option to specify query parameters on this display. You will not need to set query parameters because the OLE DB Destination uses this SQL statement to find the metadata of the desired insertion table. Even adding a WHERE clause to filter the data will not change the outcome of the result. All rows passed through the Data Flow pipeline are inserted into the destination table.

The SSIS OLE DB Destination uses the OLE DB Provider specified in the connection manager associated with that destination. The resulting SQL statements from the OLE DB Provider set to use the SQL command resemble the SQL statements from the Table or view Data access mode. I created an SSIS packages that inserts data into the AdventureWorksDW DimProductCategory table using both destination data access modes. The insert portions (taken from SQL Profiler) both match this code:

exec sp_cursor 180150003,4,0,N'[DimProductCategory]',@ProductCategoryAlternateKey=9,@EnglishProductCategoryName=N'Bikes',@SpanishProductCategoryName=N'Bicicleta',@FrenchProductCategoryName=N'Vélo'
go
exec sp_cursor 180150003,4,0,N'[DimProductCategory]',@ProductCategoryAlternateKey=10,@EnglishProductCategoryName=N'Components',@SpanishProductCategoryName=N'Componente',@FrenchProductCategoryName=N'Composant'
go

Note that sp_cursor, an internal API server cursor call that the OLE DB Provider uses, performs the insert.

The difference in these two methods is in the set up of the initial cursor. The SQL command method uses two statements:

declare @p1 int
set @p1=1073741825
declare @p5 int
set @p5=229378
declare @p6 int
set @p6=294916
exec sp_cursorprepare @p1 output,NULL,N'SELECT  ProductCategoryKey
    ,ProductCategoryAlternateKey
    ,EnglishProductCategoryName
    ,SpanishProductCategoryName
    ,FrenchProductCategoryName
FROM    DimProductCategory
WHERE   ProductCategoryKey <> 1',1,@p5 output,@p6 output
select @p1, @p5, @p6
go
declare @p2 int
set @p2=180150003
declare @p3 int
set @p3=2
declare @p4 int
set @p4=4
declare @p5 int
set @p5=-1
exec sp_cursorexecute 1073741825,@p2 output,@p3 output,@p4 output,@p5 output
select @p2, @p3, @p4, @p5
go

While the Table or view method uses just one:

declare @p1 int
set @p1=180150003
declare @p3 int
set @p3=2
declare @p4 int
set @p4=4
declare @p5 int
set @p5=-1
exec sp_cursoropen @p1 output,N'select * from [dbo].[DimProductCategory]',@p3 output,@p4 output,@p5 output
select @p1, @p3, @p4, @p5

You can solve the original business problem of only inserting records if it doesn't already exist by performing a lookup against your destination table, redirecting the rows that do not match, and inserting those rows using the OLE DB Destination. Depending on your particular scenario, other methods also exist that do not use a lookup. Whichever method you use, know that it will not entail a hand-written insert statement from an OLE DB Destination.

Version used: SQL Server 2005

SQL Down Under

noreply@blogger.com (Jessica M. Moss) — Fri, 13 Feb 2009 05:14:00 +0000

I had the honor of being invited to join Greg Low, fellow Solid Quality mentor, on an episode of SQL Down Under. On last week’s episode, we discussed the new features of SQL Server Reporting Services 2008 and best practices for all versions of Reporting Services. You can check out the episode here.

During the episode, Greg and I talked about reports that can be used to monitor Reporting Services. You can find these reports on CodePlex. Also, the design book that I mentioned is called Information Dashboard Design. I hope you enjoy the episode and these resources!

Analysis Services MDX Templates Exploration

noreply@blogger.com (Jessica M. Moss) — Tue, 27 Jan 2009 05:02:00 +0000

While looking in SQL Server Management Studio for something else entirely, I stumbled upon the Template Explorer. This window provides Analysis Services templates for querying data mining structures (DMX), querying cubes (MDX), and performing DDL (XMLA). I took a deeper look into the “MDX Queries” templates.

To view the Template Explorer, select the View menu > Template Explorer option. To see the Analysis Services template, select the Analysis Services cube option at the top of the window. Double-clicking any of the templates listed will then open a new query window containing the selected query. For example, the Basic Query will show the following:

Select <row_axis, mdx_set,> on Columns,
<column_axis, mdx_set,> on Rows

From <from_clause, mdx_name,>
Where <where_clause, mdx_set,>

You have two choices at this point in time: selecting the Query menu > Specify Values for Template Parameters option or replacing the phrases enclosed by angle brackets manually. The former choice opens a dialog box with all parameters listed to allow you to fill in the correct value; however, you will need to type in the full hierarchical structure by hand. If you’re anything like me, this is bound to cause a typo and a few frustrating minutes of letter-by-letter comparison. I prefer to modify the query directly by dragging the measure or dimensional attribute/hierarchy to my query window. Then I don’t need to worry about mistyping anything.

While these templates will not teach you how to write MDX, they are an easy way to save yourself some typing or looking up a particular keyword that you have forgotten. Looking over the XMLA queries, they appear to be more useful, as I am forever looking up the exact syntax for a particular XMLA query.

Versions: SQL Server 2005/2008

SSIS Designer Tip

noreply@blogger.com (Jessica M. Moss) — Fri, 09 Jan 2009 00:35:00 +0000

When designing a SQL Server Integration Services package, it can seem tedious to drag over each and every task and component from the toolbox to your Control Flow and Data Flow and connect all of the precedence constraints and pipelines. You can alleviate some of this by modifying the default Business Intelligence options within Business Intelligence Development Studio (BIDS).

Under the default settings, you can double-click any toolbox item and it will show up in your package designer with no connectors or specific place. To improve this, open up the Tools > Options... menu in BIDS. Then expand Business Intelligence Designers and Integration Services Designers. You will see Control Flow Auto Connect and Data Flow Auto Connect. If you check the option to "Connect a new shape to the selected shape by default", the drop down lists for specifying connector type and location are enabled in each menu. I prefer to use a Success constraint and add the new shape to the right of the selected shape, but you have a few options based on your design predilection.

Once these options are checked, double-clicking a toolbox item will add that item to the designer, using the options specified in the drop downs. You can of course change the type of constraint or move the item once it has been generated for you. Hopefully, this will save you a little bit of time when designing!

[Hat tip] I read about this option from Donald Farmer's great SSIS Scripting book.

Versions: Visual Studio 2005, Visual Studio 2008

Presentation References

noreply@blogger.com (Jessica M. Moss) — Mon, 05 Jan 2009 23:02:00 +0000

Over the past two months, I had the honor of presenting at many user groups and conferences. I wanted to put together some information for those who attended (and for those who were unable to attend!).

If you saw either "Building Reports in SQL Server Reporting Services 2008" or "New Features of SQL Server Integration Services 2008" and are excited to get your hands on a SQL Server 2008 instance, but your company won't upgrade... you can download a free trial/evaluation version from Microsoft. Maybe you can even show your boss some of the things you learned from my presentation!

@AndyLeonard: “You got your SSIS in my Twitter!”
@JessicaMMoss: “You got your Twitter in my SSIS!”
[Previous dialogue shamelessly stolen from Brent Ozar's blog because it's a perfect lead-in to...]

For those of you who are not familiar with Twitter, it's a micro-blogging tool / social network platform that has completely taken off over the past year. Andy Leonard invited me to join in his "once-a-decade" great idea, using SSIS to write to Twitter! You can download the current version of the SSISTwitterSuite from CodePlex.

PASS puts on the largest SQL Server and Business Intelligence conference. If you attended, you can download the slides for both "SSIS Scripting" and "Building an SSIS Management Framework" (which I co-presented with Rushabh Mehta) by logging into the summit site. As an attendee, you can also purchase a DVD of all of the sessions. If you are interested in the SSIS Framework discussed during the presentation, contact Solid Quality Mentors.

This was my second time speaking at the DevTeach/SQLTeach conference in Canada, and I was thrilled to speak on SSIS, SSRS, and Data Mining in Office 2007! If you attended the conference, you can sign in and download the slides and custom component code. If you have an inkling to work with the data mining tools, check out http://www.sqlserverdatamining.com/.

Finally, the SQLPASS BI SIG Data Mining webcast was recorded. Once it is available, you should be able to listen to it at:
http://www.sqlpass.org/Community/SIGs/BusinessIntelligenceSIG/tabid/82/Default.aspx.

I look forward to speaking at more events in the future!