Jessica M. Moss

SQL Server 2016 versus 2014 Business Intelligence Features

noreply@blogger.com (Jessica M. Moss) — Tue, 03 May 2016 14:00:00 +0000

Hello, SQL Server 2016
Yesterday, Microsoft announced the release of SQL Server 2016 on June 1st of this year: https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/. Along with performance benchmarks and a description of the new functionality, came the announcement of editions and features for the next release.

Good-bye, Business Intelligence Edition
The biggest surprise to me was the removal of the Business Intelligence edition that was initially introduced in SQL Server 2012. Truthfully, it never seemed to fit in the environments where I worked, so I guess it makes sense. Hopefully, fewer licensing options will make it easier for people to understand their licensing and pick the edition that works best for them.

Feature Comparison
Overall, the business intelligence services features included with each edition for SQL Server 2016 are fairly similar to SQL Server 2014. Nothing has been "downgraded" from 2014, in that nothing previously included in Standard edition is now only in Enterprise edition. A few features previously only in Enterprise edition are now included in Standard edition though! And we gained a lot of new features across all the editions! Here are the highlights as currently shared:

2014 Enterprise features now in 2016 Standard:
- Multidimensional DAX queries
- BI Semantic Model for Tabular (except perspectives and DirectQuery storage modes) is now available in Standard edition
New features included in 2016 Enterprise:
- Report Services mobile reports and KPIs
- SQL Server Mobile Report Publisher (.rsmobile)
- Power BI apps for mobile devices (iOS, Windows 10, Android) (.rsmobile)
New features included in 2016 Enterprise, Standard, and Express variations:
- Integration Services Azure data source connectors and tasks and Hadoop / HDFS connectors and tasks
- Reporting Services Pin report items to Power BI dashboards

Talk to the Licensing Folks
For all licensing question, contact your licensing specialist - they have the best information! For detailed information to talk to them about, see:

Features Supported by the Editions of SQL Server 2014: https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx
Features Supported by the Editions of SQL Server 2016: https://msdn.microsoft.com/en-us/library/cc645993.aspx

Is Data Science a Buzzword? aka: My first Coursera Course

noreply@blogger.com (Jessica M. Moss) — Thu, 17 Dec 2015 21:02:00 +0000

Data science and data scientists are all the rage right now in the information technology space. Every company wants one; every job candidate touts they are one. But what actually does that mean to companies and potential employees? I decided to take a course on data science to see if I could find out!

My co-worker, Gabriella Melki, recommended the Coursera Data Science specialization by John Hopkins Bloomberg School of Public Health. The entire specialization contains a set of 9 courses, but you can take each one individually. I started with the first course, called "The Data Scientist's Toolbox". Over the four week timeframe, I was able to view lectures and perform the assignments at my own pace. I've listed below my thoughts on the course and what I learned about data science.

Week 1: Introduction to Data Science
Data science is about data, specifically about answering questions, and science, following a method to discover an answer. A data scientist is the person that uses data to answer questions. The reason data scientists are such a commodity is because people either can't find the data they need or there is so much data, it is difficult to wade through it all. There are a variety of tools available to help with this task. A common tool is the R language, which is what the rest of the course used. The course teaches the students to accomplish tasks on their own, such as suggesting good places to research R functions (help.search, anyone?) and recommending the use of online forums like StackOverflow to get help on confusing errors. The first week wrapped up with an overview of all the courses in the specialization and a few questions to make sure you were paying attention.

Week 2: Learning the Tools
I used to program about 10 years ago, but have focused more on SQL in recent years. So I had a bit of a learning curve when it came to the tools needed to work with R programs. Week 2 of the course walked the students through installing and the basics on each of these tools. Specifically, I learned about Git Bash, Git, GitHub, R packages, and RStudio. Although it seemed a little overwhelming at first, the course went step-by-step through each installation and explained each of the commands needed to use each of the tools. At the end of Week 2, we ran some of the commands to show our mastery of the commands taught.

Week 3: Understanding Questions, Data, and Approach
As we learned in Week 1, data science is all about trying to answer questions with data. Based on the data you have and the answer you need, you may ask different questions and use different approaches. For example, you may just want to describe the data, to make a prediction about the information, or more. Data comes in all shapes and sizes: qualitative versus quantitative, large versus small, confounding versus predicting. The most important thing is to ask the right question first, design a logical experiment, and then investigate the data to find the answer. Be careful not to force your results through experious correlations or data dredging! You don't want just any answer - you want the most accurate one. At the end of the third week, we completed a project to show our full understanding of the process and tools that we had learned over the past three weeks.

Week 4: Completing the Project
Week 4, the last week of the course, we checked everyone else's work. This grading exercise was also interesting to me. They asked every student to check over the work for at least three other students. Then they assigned a grade based on those results. I'm guessing they have some way to verify the information - maybe kick out the highest/lowest values and average the rest? Or kick out the students' results with the most variety in their scores? Sounds like a good data science problem. I'm not sure of the answer, but I completed the course with a 100%!

If you're interested in taking the data science course described above, visit here: https://www.coursera.org/course/datascitoolbox

Using Power BI Custom Visualizations

noreply@blogger.com (Jessica M. Moss) — Tue, 10 Nov 2015 15:02:00 +0000

Power BI (https://powerbi.microsoft.com) is Microsoft’s tool that provides fast analysis and reporting to developers and business users. Microsoft releases features on a monthly basis to this tool, so this post may be out of date before it’s even published! One of the more recent releases includes the ability to create and publish custom visualizations for use by others.

Power BI Visuals Gallery
The Power BI Visuals Gallery is where you can publish, search, and download custom visuals for use in Power BI Desktop and the Power BI website. People in the community and Microsoft have published visualizations that enhance the dashboard experience and still interact with the other visualizations as though they came from out-of-the-box! (On a side note, do we need to stop saying out-of-the-box now that everything is cloud-first…) The types of visuals run the gamit from charts, graphs, animations, and slicers.

Searching for a Custom Visual
To start, go to the Power BI Visuals Gallery: https://app.powerbi.com/visuals.

For example, Microsoft released a custom visual that handles advanced slicing, known as the Chiclet Slicer. Once you find that visual (or another that you like), click on it, and click the “Download Visual” button.

It warns you that this isn’t supported by Microsoft, but if you like living on the edge, click “I agree”. (I chuckled a little that the visualization published by Microsoft is not supported by Microsoft, but I’m not complaining - I’m just happy to have this cool tool!)

The visualization will download as a *.pbiviz (Power BI Visualization) file to your desktop.

Installing a Custom Visual
Next, you need to install the custom visualization. This shows the install process for Power BI Desktop, but the process is similar to the Power BI Website. Kick off the install through the File > Import menu or by clicking the ellipses (…) in the Visualizations pane.

Select your downloaded .pbiviz custom visualization, and you will be warned yet again to make sure you really, really, really want to import the custom visualization. If so, click the “Import” button.

And hurray, you have installed the visual!

Verifying the Custom Visual
You can check to see that the visual is installed by looking at the visualization pane within Power BI Desktop. You will see the new visualization in the Visualization pane, available for your use.

When you next open the report, you will get a message warning you about custom visuals again. Just select “Enable custom visuals”, and you are all set. Happy dashboarding!

Versions: Power BI Visuals Gallery on 11/2/2015, Power BI Desktop v2.28.4190.122

My Week at the PASS Summit 2015

noreply@blogger.com (Jessica M. Moss) — Tue, 03 Nov 2015 23:11:00 +0000

Last week the SQL PASS organization held the annual PASS Summit in Seattle, Washington. The PASS Summit is a week-long conference that brings thousands of SQL Server, Business Intelligence, and Business Analyst professionals together to learn all about best practices in use today and about new features coming in the next version. I was able to attend by volunteering to help with PASS and by the good graces of my company and had an amazing week! Many thanks go to all of the organizers, speakers, volunteers, and sponsors who put on another great event.

The conference has two full-day preconference sessions on Monday and Tuesday which can be purchased in addition to the conference. These trainings are amazing and definitely worth your while if you want more training. I ended up flying out on Tuesday to start with the main conference on Wednesday. On Tuesday, I explored Seattle a bit, and even visited the space needle (my first time in all my years visiting Seattle!). I checked into the conference, visited with some friends at the Denny Cherry Associates and SIOS #sqlkaraoke event, and went to bed early to prepare for the next day.

Wednesday

Wednesday, the first day of the conference, started with some great sessions. James Phillips and team presented the Foundation Session on Microsoft Business Intelligence, which talked about the new business intelligence vision (consistency and modernization across all of the reporting tools). Here are a few of my favorite features:

SSRS reports pinning to Power BI
Auto-insights coming in Power BI
DAX will have intellisense, comments, formatting, and new functions
SSAS tabular will optimize DirectQuery querying
Power BI will provide a new visualization every week
Power BI can use data from on premise multidimensional SSAS databases

I attended a few more sessions and closed out the day by attending an executive meet and greet sponsored by Microsoft and the PragmaticWorks #sqlkaraoke event.

Thursday

Thursday was another great session day. I attended a Microsoft SSIS Focus session on the new features coming in SSIS 2016. Jimmy Wong presented many of the new features and took feedback from the group on their thoughts. Some of the cool new things that will be coming include:

Incremental package deployment (no more "all-or-nothing")
Ability to turn on an "optimize buffer size" option in packages
Execution of R in SQL, which can be called through and SSIS package
Ability to adjust how the ForEach Loop loops through files

There were a few other sessions, and then I closed out the evening at the Community Appreciation Party at the EMP museum (an amazing museum if you haven’t been there).

Friday

On Friday, I hosted a Birds of a Feather table, where people working on similar topics (in my case, it was business intelligence architecture and design) sit together during lunch to discuss the topic. I had a great table with a split between more advanced professionals and newbies to BI and architecture. We covered topics from choosing products to modeling to ETL performance tuning and more. Thanks everyone who attended!

Friday was the last day, so I chatted with some friends, explored Seattle more, and relaxed a bit before week 2 at the MVP Summit. I can't post anything about it, but be sure I'm sharing your feedback with Microsoft as much as I can :)

More information

For more information on the PASS, the Summit, and announcements, visit the following links:

Information about the Summit: http://www.sqlpass.org/summit/2015/About.aspx
Watch some session on PASSTV: http://www.sqlpass.org/summit/2015/Live.aspx
Reporting Roadmap released during the Summit: http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/29/microsoft-business-intelligence-our-reporting-roadmap.aspx
Latest version of SQL Server 2016 CTP 3: http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Accidental SharePoint Designer 101

noreply@blogger.com (Jessica M. Moss) — Wed, 03 Sep 2014 14:02:00 +0000

I fully profess to know little to nothing about SharePoint, but I occasionally get pulled into setting up little sites or adding web parts for some of my reporting and business intelligence work. Each time, I have to relearn the start-up steps to create what is needed! So I decided to record a few of my go-to places so I can remember next time. I used SharePoint 2010 to document the below steps, but the directions may be applicable to other versions. Also, these steps assume you have full control of your site.

Getting Started

The first step is start editing the page rather than looking at it like an end user. Do this by:

Select the Page tab at the top of the screen
Click the Edit Page button/drop down list
Select the Edit Page option

PS. When you're done, do these same steps, except select the "Stop Editing" button.

Content Creation

You may need to create a document library, a list, or another type of container. I like to create my content first because it makes the design portion later. To do this:

On the Site Actions menu in the top left corner, select the appropriate content creation option:
- New Document Library
- New Site
- More Options
Specify the important info, and be sure you're happy with the view of everything

Web Part Addition

As long as you're in editing mode, this is straightforward: Just select the Add a Web Part link where you want your web part.

PS. this is where creating your content comes in handy because you can select your content without having to pick the web part type.

Web Part Editing

To make any changes to the web part, such as style, name, or option, you can do that in the page itself. To do this:

In editing mode, hover your mouse over the web part you want to modify
On the right side of the toolbar (at the top of the web part, next to the title), click the down arrow
Click the Edit Web Part menu option
On the right side of the screen, make any setting changes you would like, and be sure to select the OK button at the bottom.

Color and Style

Next, we want to make the color and style to match either the rest of the system or to match our own colors! Change this by:

On the Site Actions menu in the top left corner, select the Site Settings option
Under the Look and Feel section, click the Site theme link
Either inherit your parent's theme, specify your own theme, or customize your color options

Left Navigation Menu

My usual goal is to make the page look less "SharePoint-y", which includes removing the items from the left navigation menu and making links to other sites or pages within the site. You can change the menu by:

On the Site Actions menu in the top left corner, select the Site Settings option
Under the Look and Feel section, click the Navigation link
Scroll down to Navigation Editing and Sorting and have fun playing!

Security

Hopefully, someone is going to use the page that you just put so much time and energy into! So we need to give those people access. Do this by:

On the Site Actions menu in the top left corner, select the Site Permissions option
Give access to either Windows or SharePoint groups and decide what permission they get

PS. It is important have a good security plan in place, and hopefully you can work with your SharePoint administrator on this.

Good luck if you end up an "accidental SharePoint designer" like me!

Upgrading your SSIS Management Framework: Part 3

noreply@blogger.com (Jessica M. Moss) — Tue, 29 Jul 2014 14:02:00 +0000

At this point, you understand the options for moving an SSIS framework to the latest version of SSIS, and you've upgraded the logging portion of the framework using a hybrid approach. The final step in the framework upgrade is handling your configurations. Let's walk through an existing configuration implementation and how you can upgrade it by combining your existing implementation with the standard SSIS framework.

Overview

A typical "old-school" configuration scheme is described in the SSIS PDS book or in this blog post here: http://jessicammoss.blogspot.com/2008/05/ssis-configuration-to-configuration-to.html. Starting in SSIS 2012, the configuration scheme uses environments and parameters when using the Project Deployment Model, as discussed here: http://msdn.microsoft.com/en-us/library/hh213290(v=sql.110).aspx.

In both scenarios, the core ideas in a configuration scheme are:

Provide the ability to move packages through environments without having to touch the packages
Provide one location where connection strings / variables are stored, so in case a value changes, you don't have to change the value in multiple places

Assumptions

To enable our hybrid approach, we will utilize the SSIS 2012+ catalog as our "master" version of the configuration values and modify the previous framework to use its data. This example counts on the following assumptions. If your system is different, you may need to make some modifications to the implementation described here.

Assumption for the old framework: All configuration entries modify Variables, rather than Connections or other object types.
Assumption for the new framework: You have a CommonConfigurations environment on each server that holds your values for your development, test, and production servers, as well as an environment for each package that would have its own package level values.

Configurations

To tie the two systems together, we will use the environments stored in the 2012+ catalog to pass to the earlier framework. The earlier framework retrieves all of its information from a table called dbo.SSIS Configurations, so we can just replace that table with a view that points to the new catalog!

Start by renaming your old table to [SSIS Configurations Old]:

EXEC sp_rename 'dbo.SSIS Configurations', 'SSIS Configurations Old';
GO

Next, create a view named [SSIS Configurations] that reads from the SSIS catalog:

CREATE VIEW dbo.[SSIS Configurations]
AS
    SELECT CAST(e.name AS NVARCHAR(255)) as ConfigurationFilter
        , CAST(ev.value AS NVARCHAR(255)) AS ConfiguredValue
        , CAST('\Package.Variables[User::' + ev.name + '].Properties[Value]'
                AS NVARCHAR(255)) AS PackagePath
        , CAST(ev.type AS NVARCHAR(20)) AS ConfiguredValueType
    FROM [SSISDB].[catalog].[environment_variables] ev
    LEFT JOIN [SSISDB].[catalog].[environments] e
        ON ev.environment_id=e.environment_id
GO

Compare the output from SSIS Configurations and SSIS Configurations Old, and add any additional variables needed to the environment in the SSIS catalog. From now on, you will add new configurations only to the new catalog. You have one place to keep the "master" values and you only change them in one place!

Good luck!

Keep in mind that based on your framework implementation, not all of this may be applicable. However, I hope that it gives you something to think about as you are evaluating your framework upgrade options!

Upgrading your SSIS Management Framework: Part 2

noreply@blogger.com (Jessica M. Moss) — Fri, 27 Jun 2014 14:04:00 +0000

Based on Part 1 of Upgrading your SSIS Management Framework, you’ve decided to go with a hybrid approach for your framework. The hybrid approach which will use some components of the custom framework (this post will use the framework provided in SSIS PDS, but the concepts are applicable to any custom framework) and also utilize the standard SSIS framework. This allows you to tie your existing package ecosystem with the latest and greatest built-in framework. Let’s talk through an overview of what we’re going to do and then explain each of the steps needed to implement it.

Overview

When it comes down to it, we need to accomplish two main things for this hybrid approach: tie our logging tables together and tie our configuration tables together. When it comes to logging, each system has its own important identifier (ID) that can get you to anything else in the system. The important ID in the custom SSIS framework is the PackageLogID, and the important ID in the standard SSIS framework is the ServerExecutionID. The work you need to do is to map these two executions together. When it comes to configurations, you want to be able to have one place to modify your connection strings and common variables that will modify all of your packages.

Logging

Let’s start with logging. As previously mentioned, our goal here is to the two logging systems together. Since we don’t want to modify the standard SSIS framework (that would defeat the purpose of moving to that framework!), we’ll do our modifications in the existing framework. However, we want to be sure not to change anything in the packages themselves because that would cause a lot of rework. Fortunately, we can do this in the table and stored procedure that the framework utilizes.

Begin by adding a new column to your main table that contains package executions, such as:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE [name] = N'ServerExecutionID' AND [object_id] = OBJECT_ID(N'PackageLog'))
BEGIN
    ALTER TABLE [dbo].[PackageLog]
    ADD [ServerExecutionID] bigint NULL
END

Next, you will modify the stored procedure to populate the field you just added:

UPDATE dbo.PackageLog
SET ServerExecutionId = (
    SELECT MAX(execution_id) AS execution_id
    FROM SSISDB.catalog.executions
    WHERE status = 2 AND end_time IS NULL
        and package_name = @PackageName )
WHERE @PackageLogID = @PackageLogID

This will insert the ServerExecutionID from the standard framework into the custom framework. Next, you can modify your reporting queries to utilize the new column. For example, I modified the existing standard framework query for the “All Executions” report to include information from the old framework in the following query:

SELECT TOP(10)
    a.[execution_id],
    CAST(a.[start_time] AS smalldatetime) AS shortStartTime,
    CONVERT(FLOAT, DATEDIFF(millisecond, a.[start_time], ISNULL(a.[end_time], SYSDATETIMEOFFSET())))/1000 AS duration
FROM [catalog].[executions] a
INNER JOIN [catalog].[executions] b ON
    a.[package_name] = b.[package_name] AND
    a.[project_name] = b.[project_name] AND
    a.[folder_name] = b.[folder_name]
WHERE b.[execution_id] = ? AND
    a.[status] = 7
UNION ALL
SELECT a.PackageLogID,
    CAST(a.[StartDateTime] AS smalldatetime) AS shortStartTime,
    CONVERT(FLOAT, DATEDIFF(millisecond, a.[StartDateTime], ISNULL(a.EndDateTime, SYSDATETIMEOFFSET())))/1000 AS duration
FROM SSIS_PDS.dbo.PackageLog a
INNER JOIN SSIS_PDS.dbo.PackageVersion b ON a.PackageVersionID=b.PackageVersionID
INNER JOIN SSIS_PDS.dbo.PackageVersion c ON b.PackageID=c.PackageID
INNER JOIN SSIS_PDS.dbo.PackageLog d ON c.PackageVersionID=d.PackageVersionID
WHERE d.ServerExecutionID = ?
     AND a.ServerExecutionID IS NULL
ORDER BY [start_time] DESC

In this way, you can see all executions together, and you can use similar queries to tie any executions from the old framework to the new one!

Configurations

Next week, we’ll look at configurations and how to manage those in both frameworks.

Upgrading your SSIS Management Framework: Part 1

noreply@blogger.com (Jessica M. Moss) — Tue, 17 Jun 2014 14:02:00 +0000

Background

Before SQL Server 2012, SQL Server Integration Services (SSIS) had no built-in logging, auditing, and configuration framework. All of the pieces were available to build your own, but everyone ended up doing that just a little bit differently. Most of us consultants came up with our own variation to implement at client sites and ensure that all of those functions at that one client were the same. I'm especially proud of the framework that Rushabh Mehta and I developed that is published in Microsoft SQL Server Integration Services: Problem, Design, Solution (SSIS PDS) and implemented by many others as well.

Along came SSIS 2012, when Microsoft realized this "multiple-different frameworks" spread was happening, and thought "how cool would it be if we could standardize the framework so ALL our clients have the same one". This would not only reduce initial development time of the framework, but also ensure that upgrades and future maintenance would go smoothly. Coinciding with (or perhaps due to) this decision, Microsoft moved the execution of SSIS packages to run inside of SQL Server. Having a consistent framework is wonderful, and I'm a big fan of using the consistent built-in framework.

Upgrade Options

However, what do you do if you've already implemented a custom framework, similar to one in SSIS PDS? There are a couple of options:

Move lock, stock, and barrel to the new framework. To do this, you would have to upgrade all existing packages, remove the components that logged to the framework, and change your configurations schema to use parameters. This means that you would never have to worry about upgrading your framework again because Microsoft will take care of it. On the other hand, you would lose ties to your existing log records. If you have a small number of packages or you have only used a custom framework for a short amount of time, I would recommend this option.
Stick with what you've got. This option is the most easy to implement, but provides the least amount of value. The new SSIS framework contains much more logging than most of the custom frameworks, and when a package fails and you don't know why, mo' logging = mo' better. I do not recommend this option.
Use a hybrid approach, which the potential to phase out the custom framework in the future. This option includes a little bit of up-front work, but will be maintainable (and hopefully enhanced!) in future SSIS versions.

Okay, let’s do it!

Next week, we'll look at how to implement the hybrid approach based on the PDS framework.

Where is my BI Development Tool?

noreply@blogger.com (Jessica M. Moss) — Tue, 10 Jun 2014 14:02:00 +0000

"The development tool for SQL Server business intelligence packages is missing!" says almost every developer who installs SQL Server 2014. It's okay, don't panic. Microsoft decided to separate the installer for the server and the installer for the development environment. Unfortunately, the next statement by almost every developer who installs SQL Server 2014 is "There are MULTIPLE SQL Server Data Tools?!?". Also not a problem, let's talk about the difference options and how to get the correct software on your machine.

Option 1 is SQL Server Data Tools (the original), which is a template for Visual Studio that allows you to create and store information on databases and database objects. This tool is part of Visual Studio. For more information, see: http://msdn.microsoft.com/en-us/data/hh297027.

Option 2/3 are SQL Server Data Tools - Business Intelligence, which contain the templates for SSIS, SSAS, and SSRS (the standard BI tools we are accustomed to). This tool is part of SQL Server. You have two places to download this (depending on if you're developing for SSIS 2012 or SSIS 2014. Here are those download links:

Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2012 (and SSIS 2012): http://www.microsoft.com/en-us/download/details.aspx?id=36843
Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013 (and SSIS 2014): http://www.microsoft.com/en-us/download/details.aspx?id=42313

I do wonder why they are named the same. Is it possible that Microsoft might merge these two products in the future? This could be a great move for full data warehouse projects - designing your data layer and then populating it with ETL go hand-in-hand! Pretty please?

Eek! Starting as a Regular Full-Time Employee

noreply@blogger.com (Jessica M. Moss) — Tue, 03 Jun 2014 14:02:00 +0000

Well, I jumped into the career deep end last week. I started my first-ever job as a regular full-time employee. This week contained a mixture of emotions for me: scared, nervous, but overall - EXCITEMENT! It's been a long road, but I'm very happy with where I've come and where I'm going.

I started with a consulting company right out of college. Back then, graduating with a computer science degree pretty much meant you knew a little bit about a lot, but had no deep knowledge in any area. So working on a little bit of everything to gain some knowledge in a consulting company seemed like a perfect fit! I still remember during my interview, the interviewee said "You'll be assigned a project over the weekend, buy a book on the technology, and by the time you arrive on Monday, you need to be an expert". While we all know that won't exactly work, I was able to learn SO much by hopping around and trying different technologies, industries, and systems.

While trying out different technologies, I fell in love with data warehousing. To that end, I then tried my hand at my own business, still consulting, but with more training and mentoring. I absolutely loved this kind of work, but wasn't a huge fan of the full-time travel or all of the paperwork ;) I then went back to consulting for a couple of local companies, where I was able to work with many organizations with data, without the travel or paperwork.

So... after ten years of consulting, why did I give it up? Truthfully, I'm ready for a change. While it's fun to bounce from organization to organization, there's always that dichotomy between "the client" and "the consultant". And I don't blame either side for thinking that way. Consultants provide a great service for organizations that don't have the resources (time, people, money, etc.) to do it themselves. At the end of the day, a consultant's number one duty is to their company, whether that is looking for additional sales or is eventually leaving the project to move onto the next.

All that being said, I'm off to start my new adventure! I'm a data architect for a firm based in Richmond that handles chemicals and manufacturing. I get to help them consolidate their data in a data warehouse and hopefully learn a bunch in the process... I also need to learn how to be a regular full-time employee. Do you have any advice for me?

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.