PASS Summit 2013 Recap of Madness and Mayhem

Greetings! Another PASS Summit has come and gone. This was my third Summit and I have to say that I am truly hooked on this spectacular event. I had the privilege to be a bit more involved this year than in years past. I love when I can give some of my time and energy to help out an organization that has been hugely important for me in my career with SQL Server.

I spent much of Tuesday in meetings related to the PASS organization. There was the Regional Mentors meeting, the Chapter Leader meeting, and a chance to sit down with the Canadian Chapter Leaders. As a PASS Regional Mentor for Canada, that was great since I don’t often get the chance to see them in person.Summit 2013 Badge

Since it was already confirmed on Twitter, I would like to say that I will be working with SQL MVP and Winnipeg  SQL Server User Group chapter leader, Mike DeFehr (Blog|Twitter), on planning a SQL Saturday in his fine city. I am hoping I will get to go as well. I have never been involved in running a SQL Saturday and I thought helping Mike would be a great way to fulfill my role as a Regional Mentor and also give me the experience to better help others with their event planning in the future.

PASS invited me to be on the Blogger Core and have a seat at the Bloggers table for the keynotes this year. While it can be challenging to keep up with meaningful things to post while live blogging, I really enjoy it. I always get a lot out of it. You can read those posts at PASS Summit Keynote Live Blog – Day 1 and  PASS Summit Keynote Live Blog – Day 2 respectively.

When PASS invited me to serve on the Blogger Core, they also indicated I would have access to an Interview room. I had never interviewed anyone before, but it seemed like a great opportunity to capitalize on connections I had made at PASS BA Conference, Tech Ed North America, and past PASS events. I decided, with the fast pace of change occurring in Business Intelligence both at Microsoft and in the industry as a whole, that I should try to sit down with prominent Microsoft personnel to talk about their work and the future of Microsoft BI. Being a lowly blogger from Minneapolis, I didn’t expect to get very far, but I had to try. I was astonished at the access I was given. Listed in chronological order of when the interviews took place, I sat down with:

  • Matt Masson (Blog|Twitter), Senior Program Manager for SQL Server Integration Services
  • Kamal Hathi, Director of Program Management for Microsoft Business Intelligence
  • Kasper de Jonge (Blog|Twitter), Program Manager for SQL Server Analysis Services

I know! Right? For real.

Transcribing the audio from these interviews is taking a little time. Each interview will get its own blog post. I hope to have the first one released next week. I have to express my great appreciation to Matt, Kamal, and Kasper for giving of their valuable time to chat with me. I also need to say thanks to Microsoft’s Cindy Gross (Blog|Twitter) for helping with my connection to Kamal Hathi.

I also had the honor of wearing a SPEAKER ribbon on my Summit badge this year. It was my first time speaking at PASS Summit; a career milestone to be sure. I am happy to report my presentation, DANGER: The Art and Science of Presenting, went extremely well. And, despite presenting right after Dr. David DeWitt’s brilliant keynote and being on the other end of the Convention Center, there were very few empty chairs in my room. The feedback I have received so far is all very positive. Thanks to all who chose my session when there were so many great options to choose from.

While I ended up having to miss the public Q&A Session with the PASS Board of Directors, I do have some notes from the private Blogger Q&A with the executive committee. I will prep that for another post.

If you ever get the chance to go to PASS Summit, grab onto it with both hands. There’s nothing like connecting, sharing, and learning with a few thousand of your closest friends to get you jazzed about what you do.

GO

Upcoming Presentations and More

Wow. I have the tremendous joy to have 4 presentations coming up in the next few weeks. I love when that happens.

October 12 – SQL Saturday #238 Minnesota

I will be giving two sessions at the SQL Saturday here in sunny Minnesota.

1. MDX Trek: First Contact

2. DANGER: The Art and Science of Presenting

PASS SUMMIT!!!! For Real!

I get to give my first Summit presentation ever. I’m really excited for this.

3. DANGER: The Art and Science of Presenting

In addition to presenting, I will be on the Blogger Core, live blogging keynotes and sharing my observations all week long on Twitter. I also have the great privilege of getting interviewed on PASS TV! My interview will be from 4:30pm to about 4:45pm (Eastern) on Wednesday, October 16th. I am sure I will also be spending plenty of time in the PASS Community Zone. Please stop on by and say hi. Meeting new people is one of my favorite things to do at PASS events. I volunteered to host a Birds of a Feather Lunch table as well. That should be tons of fun. It’s going to be quite a week. 🙂

October 19 – SQL Saturday # 237 Charlotte BI Edition

I will be staying an extra day in Charlotte to present at the SLQ Saturday.

4. MDX Trek: First Contact

All in all, it will be quite a busy time.

Technical Article: Report Model (SMDL) Alternatives in SQL Server 2012

It is with tremendous joy that I announce the release of my very first Technical Article! Click the image below to get a copy of your very own.

image

When I first started in the SQL Server business with Digineer about 6.5 years ago, one of my earliest projects was implementing a SQL Server 2005 Report Model for a client. Lara Rubbelke (Blog|Twitter), who led Digineer’s Information Management (SQL Server) Team at the time, set me up with that project along with some top cover from the mighty Jason Strate (Blog|Twitter). With two great mentors like I was lucky enough to have, that project set the stage for my career in SQL Server. It was during that project that I came to firmly believe in empowering end users in a managed environment. In the next few years, I ended up working on several Report Models for clients and giving well attended Intro To Report Model presentations.

Upon discovering that SQL Server 2012 meant the beginning of the end for Report Models, I was concerned for the folks that had been investing in them since their release. I saw forum posts that expressed frustration at their demise since many organizations had come to rely on them. It was clear that there were some technologies available that were superior to Report Models that could make great replacements in different scenarios. I looked for a source that combined all that information into a single location to help ease the transition of Report Models to other alternatives in the Microsoft BI stack. Since I could not find one, I decided I should go ahead and make one. Behold!

I want to thank Lara and Jason for their mentorship at the beginning of my career and through today. I also want to thank my great Technical Reviewers for this article:

Jin Cho (LinkedIn) – Microsoft

Mike Donnelly (Blog|Twitter) – Digineer

Aaron Drinkwine – Digineer

Chris Fish (Twitter) – Digineer

I need to thank Digineer for their continued support and being an awesome place to call home.

SSRS Cascading Multi-Value Parameters

Not too long ago, there was a question posted to #ssrshelp on Twitter regarding having multi-value parameters in SSRS 2005 that ALSO cascade. While I don’t have access to a SQL 2005 instance anymore, I wanted to try it in 2012 since I had an idea how to accomplish it. My idea worked just fine in 2012 and uses functionality that, if I recall correctly, should be available in 2005 as well.

To start, I want to define these two types of parameters so we are all on the same page.

Multi-Value parameters allow the user to select one, some, or ALL values from a drop-down list. This is native SSRS functionality and it creates the Select ALL option for you. The result(s) of the user selection are placed into a comma separated list of the values chosen, which you can then pass to a dataset or use in some other way.

Cascading parameters allow the value(s) select by one parameter (the Parent, if you will) to control the available values presented in another parameter (the Child, if you will). An example would be a list of US States in one parameter and a list of Counties in the second. The Counties parameter would limit its available values to the Counties that are in the State(s) chosen in the State parameter. With Cascading parameters, the Child parameter is not activated until a valid choice has been made on the Parent parameter.

I pretty much always use stored procedures for my datasets with reporting. Now, natively, passing multi-value parameters to a stored procedure is not supported. I know of two ways around that, both of which I have used at clients.

The XML Method

I wrote about this method myself in this post.

The Table-Value Function Method

This one is demonstrated in a great post by David Leibowitz (Blog|Twitter) here.

I will be using the Table-Value Function method in this post.

We start by creating a quick database and a few objects. Feel free to use an existing database to house these objects instead.

USE master;
GO
 
CREATE DATABASE SSRSParmDemo
;
GO
 
USE SSRSParmDemo;
GO
 
CREATE TABLE dbo.Race
(
      RaceID int IDENTITY(1,1) NOT NULL
    , RaceName varchar(50) NOT NULL
    , CONSTRAINT PK_Race PRIMARY KEY CLUSTERED (RaceID)
)
;
GO
 
INSERT INTO dbo.Race VALUES
  ('Dwarves')
, ('Elves')
, ('Men')
;
GO
 
CREATE TABLE dbo.Person
(
      PersonID int IDENTITY(1,1) NOT NULL
    , RaceID int NOT NULL
    , PersonName varchar(100) NOT NULL
    , CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID)
    , CONSTRAINT FK_Person_Race FOREIGN KEY (RaceID) REFERENCES dbo.Race (RaceID)
);
GO
 
INSERT INTO dbo.Person VALUES
  (1, 'Balin')
, (1, 'Gloin')
, (1, 'Thorin')
, (2, 'Elrond')
, (2, 'Legolas')
, (2, 'Buddy?')
, (3, 'Aragorn')
, (3, 'Boromir')
, (3, 'Faramir')
;
GO
 
    
/**
-- http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql/
**/
 
CREATE FUNCTION dbo.fn_String_To_Table (
   @String VARCHAR(max), /* input string */
   @Delimeter char(1),   /* delimiter */
   @TrimSpace bit )      /* kill whitespace? */
RETURNS @Table TABLE ( [Val] VARCHAR(4000) )
AS
BEGIN
    DECLARE @Val    VARCHAR(4000)
    WHILE LEN(@String) > 0
    BEGIN
        SET @Val    = LEFT(@String,
             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String) - 1, -1),
             LEN(@String)))
        SET @String = SUBSTRING(@String,
             ISNULL(NULLIF(CHARINDEX(@Delimeter, @String), 0),
             LEN(@String)) + 1, LEN(@String))
  IF @TrimSpace = 1 Set @Val = LTRIM(RTRIM(@Val))
    INSERT INTO @Table ( [Val] )
        VALUES ( @Val )
    END
    RETURN
END;
GO
 
CREATE PROC dbo.getRace
 
AS
 
SELECT
      RaceID AS Value
    , RaceName AS Label
FROM dbo.Race
;
GO
 
CREATE PROC dbo.getPerson
 
    @RaceList varchar(max)
 
AS
 
SELECT
      p.PersonID AS Value
    , p.PersonName AS Label
FROM dbo.Person p
WHERE p.RaceID IN
    (
        SELECT Val
        FROM dbo.fn_String_To_Table (@RaceList,',',1)
    )
;
GO
 
CREATE PROC dbo.getPersonReport
 
      @PersonList varchar(max)
 
AS
 
SELECT
      p.PersonName + ' is of the Race of ' + r.RaceName AS PersonRace
FROM dbo.Person p
INNER JOIN dbo.Race r
ON p.RaceID = r.RaceID
WHERE p.PersonID IN
    (
        SELECT Val
        FROM dbo.fn_String_To_Table (@PersonList,',',1)
    )
;
GO

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

The purpose of each procedure is as follows:

getRace provides a list of the Races available in the dbo.Race table. This will be the parent parameter whose selected values will cascade as a filter for the Person parameter.

getPerson provides a list of the folks in the dbo.Person table based on the list of Races passed in to its @RaceList parameter. This will be the child parameter whose values will be pre-filtered by those selected in the Race parameter.

getPersonReport returns the folks from the Person table based on the list of PersonID values passed in via the @PersonList parameter. This will be the dataset for the report.

Let’s add the three datasets to the report, each using the Shared Data Source pointing to the database created above. You can see them in Figure 1.

Figure 1

image

Each dataset maps to the stored procedure of the same name.

The report for this example is very basic. It is simply a table (tablix) which shows the PersonRace column for each row returned in the getPersonReport dataset. The report layout is shown in Figure 2.

Figure 2

image

Once all three datasets are added, there will be two report parameters added for you, as shown in Figure 3.

Figure 3

image

We need to do a little work with each parameter.

Double-click the RaceList parameter, revealing the Report Parameter Properties dialog shown in Figure 4.

Figure 4

SNAGHTML23ede7

Ensure that the Allow Multiple Values option is checked, as shown by the red arrow in Figure 4. Then click the Available Values option shown in the oval in the upper left. This will bring up the Available Values dialog just like Figure 5.

Figure 5

SNAGHTML2e6863

Select the Get Value From a Query option and choose the getRace dataset. From there, just match up the Value and Label fields like in Figure 5. Then click OK.

Double-click the Person parameter to bring up its Properties, as in Figure 6.

Figure 6

SNAGHTML2bb1d1

Make sure the Allow Multiple Values option is checked. In my report, the Allow Blank Value for this parameter was checked automatically. I tried running the report both with it checked and with it unchecked and noted no difference in functionality. Click the Available Values option to bring up a dialog similar to Figure 7.

Figure 7

SNAGHTML2ddb03

Check the Get Value From a Query option and set the Dataset to getPerson. Set the Value and Label fields respectively as shown in Figure 7 and click OK.

Now let’s run the report, which should resemble Figure 8.

Figure 8

image

Notice the Race List parameter has checkboxes for each Race along with a (Select All) option, which SSRS built for us. Also notice that the Person List parameter is grayed out. Because it requires Races to be passed to it, SSRS knows that we cannot use until at least one Race has been selected. When we choose a Race, perhaps Dwarves, and click away from the Race List parameter, that value is passed to the getPerson stored procedure, producing the available values list for the Person List parameter, as shown in Figure 9.

Figure 9

image

Notice that we have a list of folks along with the (Select All) option that SSRS created for us. If you are familiar enough with the works of J.R.R. Tolkien, you will recognize that all of these folks are Dwarves. Either way, click (Select All), which will put a check in every checkbox available, and click View Report. Your results will resemble Figure 10.

Figure 10

image

That’s really all there is to it. Play around with different combinations (you may enjoy the Easter Egg among the Elves; What’s your favorite color?).

Since it has been a long time since I used SSRS 2005, I cannot fully promise this will work. But none of the functionality above is all that fancy, so it seems to me it could possibly work just fine. If you try it in 2005, please let me know how it goes.

A Milestone: Presenting at PASS Summit

I have been presenting for several years. I can’t properly explain how much I love it. Since before I even attended my first PASS Summit, it has been a career goal that I would one day present at the premier SQL Server focused training event on the planet. When I got the email a while back that my DANGER: The Art and Science of Presenting session had been chosen as an Alternate for the 2013 Summit, I was pretty happy. It was my best result yet.

It is with tremendous joy that I now report that I am no longer an Alternate. Due to a cancellation, PASS had an opening in the Professional Development track and asked me to fill it. Not only do I get to fulfill a career goal of presenting at the Summit, but I get to do so with an exciting new session. I honestly feel this session will not only help new presenters get started on the right foot, but will also help re-invigorate more experienced folk.

You can read the abstract for it here: DANGER: The Art and Science of Presenting

PASS_2013_Banner_300x250

SQL Saturday #239 East Iowa Recap

I just returned home from a fantastic SQL Saturday in Iowa City. Ed Leighton-Dick (Blog|Twitter) and his team did an excellent job. Many thanks to them and to all of the folks that worked so hard to make this event what it was. FamAtFieldOfDreams

I had the joy of presenting twice. In first time-slot of the day, I co-presented Hailing Frequencies: Analysis Services Terms and Concepts with my friend, Doug Lane (Blog|Twitter). In the final time-slot, I gave my MDX Trek: First Contact presentation. Both went well and I want to thank those who attended these sessions when there were other great topics to choose from. I LOVE presenting and the chance to do it twice at a single event was outstanding.

In addition to presenting, I went to great sessions as well. Ed Leighton-Dick gave an excellent introduction to Service Broker, a topic near and dear to him. It was very clear and concise and provided troubleshooting tips and lessons learned. I also went to a great session by Time Mitchell (Blog|Twitter) on SSIS Incremental Load Design Patterns. In addition to quality content, there was meaningful discussion. I went to Doug Lane’s session, From Minutes to Milliseconds: High-Performance SSRS Tuning. Doug gave great tips on memory/IO as well overall architecture choices. I learned valuable new things in every one of these sessions.

In addition to seeing #sqlfamily down in Iowa, I brought my wife and kids along this time. We stopped at the Field of Dreams baseball field in Dyersville, Iowa on the drive down from Minneapolis. That was really cool. My wife, a far bigger baseball fan than I am, absolutely loved it. Actually, she is watching that movie with my ten-year-old as I type this. 🙂 It was great to turn a SQL Saturday into a mini-vacation for my family as well. If you have a chance to do that some time, I highly recommend it.

SQL Saturday is such an important part of PASS and the overall #sqlfamily. It makes me so happy to see how SQL Saturday has grown so mightily over the years and spread across the world. That wouldn’t happen without people like Ed Leighton-Dick and his team who put in so much work to make these events happen. The people that give so much of themselves to put on these events deserve our Thanks and appreciation. 🙂

SQLFriends Lunch #6 – Minneapolis UPDATED

SQLFriends is a chance for members of #sqlfamily to gather together, have some lunch, and talk about various topics with a “host” that has been making a difference in the SQL Community. Past SQLFriends hosts include the following fine people:Photo

Brent Ozar (blog | Twitter)

Ted Krueger (blog | Twitter)

Jason Strate (blog | Twitter)

Jes Borland (blog | Twitter)

Dan English (blog | Twitter)

UPDATE: The June 28th lunch could not occur due to my contracting a pestilence I didn’t want to share.

On June 28th August 2nd, at Hell’s Kitchen in downtown Minneapolis, there will be another installment in the fantastic SQLFriends lunch series. And, the host this time around will be little old me. I am really honored and delighted to take part in this. Come have some excellent food, great conversation, and totally shameless puns with other members of our #sqlfamily. Bring your questions about Business Intelligence, Consulting, Blogging, Presenting, being a PASS Regional Mentor, and my experiences at PASS Summit, the PASS Business Analytics Conference,  SQL Saturday events and TechEd North America.

You can register for the event by clicking this delicious link: BACON

SSAS 2012 Tabular CONNECT Digest – VOTE PLEASE

A while back, I had the opportunity to implement an SSAS 2012 Tabular Model for a client. While I am really excited about the technology itself, I did find some challenges with the development environment in SQL Server Data Tools (SSDT), the SQL 2012 successor to Business Intelligence Development Studio. This post is a quick list of a few suggestions for Microsoft that have been posted to Connect.

Allow Marking a Table as Date Table in Diagram View

This one is not that huge a deal, to be honest. But, it did eat up some time. Like a stereotypical “guy,” I wanted to try to figure it out on my own before asking for directions… As it turns out, currently in SSDT, you must be in the Grid View of your Tabular model to mark a table as a Date table. It seems strange to me that you cannot perform this action from Diagram View. It seems like a silly limitation to me since it is not very intuitive.

If you think this would be worthwhile to fix, please vote up this Suggestion.

Allow Changing Many Column Names At Once

Tabular models are meant for business user consumption. Thus, friendly column names are important. With SSAS Multidimensional, you can make many changes to your project and afterward deploy them all at once. With SSAS Tabular, you are always working with a live Tabular model residing in your workspace database. As you make a change, the model in the workspace database is updated. While this makes it easy for you to play with your model via Analyze in Excel functionality, it means that tedious changes like changing column names to be more friendly can be a total pain in the office.

If you would like SSDT to allow for changing multiple columns names at once and then making the model update AFTER all of those changes instead of each one, please vote up this Suggestion. Note, there is a workaround on that item that was provided by Microsoft Program Manager Kasper De Jonge (b|t).

Campaign For PowerPivot/Tabular Textual Modeling Language

This one comes from Marco Russo (b|t). I am at Tech Ed North America in New Orleans this week. After the excellent DAX PreCon given by Marco and Alberto Ferrari (b|t), I was chatting with them a bit about my own experiences with Tabular. Marco asked what I thought about trying to get a human-usable textual scripting language for Tabular. I was totally on board with that. Marco release this blog post to start this ball rolling. Marco makes some excellent points in this post, which I will not reiterate here. Please read that post.

If you agree that a textual DDL style language for Tabular would be way helpful, or you just want to build up great karma by helping out Tabular developers, please vote up this Suggestion Marco created.

This suggestion by Marco, in my opinion, is way more important than my suggestions related to the GUI of SSDT. A scripting language would be AWESOME here.