SQL Saturday 58 – Minnesota: Follow up & Lessons Learned

This past Friday was the big SQL Server event of the year in Prior Lake, MN with the first ever SQL Saturday on a Friday (for the R2 launch earlier this year we were around 240 people in attendance).  Last year we simply put together our own little all day free event called PASSMN SQL Summit and this year we decided to leverage the SQL Saturday branding.  Based on last year’s attendance of 100 people and the R2 launch this year we knew that we needed a large area to hold the event.  This year we headed back to one of my previous employers and went to Mystic Lake Casino Hotel.  We had over 280 people register for the event, around 20 decided to cancel, and then 50 that were still on the wait list (around 330 people signed up!).  We ended up with approximately 250 show up for the event (around 40 people were wait list or just showed up – a 23.6% drop off rate, but the wait list or unregistered count made up for that and so did the sponsors) and then add another 30 or so headcount with the sponsors (probably more because they were taking shifts), so in all close to 280 or so in attendance.  I was completely in awe by the turnout and I think we did surprisingly well getting everyone through the registration process in time for the opening sessions.

Each of our rooms held up to 66 people.  Next year we will definitely need more seating because the rooms did fill up.  We did have the rooms split into 4 rows of classroom style and then a couple of rows of theater style seating.  The class room style setup was nice for the people taking notes or using laptops.  We did have free WIFI service, but I was offline for the entire event.  Here are a few things that I will need to remember for next time (some of these I did think of, but just didn’t get the tasks completed):

• speaker room – on the way to the event I thought about this, but when I arrived at the location and started setting everything up it slipped my mind.  There actually was one other room in our area we could have leveraged, so next time I will make sure we get something setup where the speakers have an area designated for them to get prepared, relax, and place their items.
• speaker survey baskets – I  did have four spare baskets from the raffle baskets and I simply didn’t get around to labeling them and placing them at the back of the rooms by the water stations.  I thought about this after I placed the raffle baskets on the sponsor tables, but then I headed up to the registration area and forgot about it, plus I needed one more and I could have used a box.
• prize table signage – we had a prize table setup loaded with books, training video, and software (along with a couple of sponsor raffle baskets), but I didn’t have a ‘Prize Table’ sign.
• prize ticket distribution – should have provided the tickets ahead of time to the speakers or simply left some at the podium in each room for the speakers to give away during their sessions.  We were providing each speaker with two tickets to give out so that an attendee could go and claim a prize at the table that was not labeled (I think people caught on though after a bit – tons of great books thanks to our sponsors and we got some of them autographed).
• speaker survey collection – because of the lack of baskets setup for these we didn’t communicate what we were doing with these.  Basically we just wanted the speakers to collect them and keep them for their own personal feedback.  I didn’t get any results, but I did ask people that attended.  They said I did a good job and learned something new, so I am happy.  I am sure I was a bit out of it, so hopefully I did a halfway decent job (don’t know what compels me to present and help organize the event).
• networking bingo card basket – once again we didn’t have any formal process for the collection of these either.  A basket was finally setup on the prize table for people to place them, but we only had about 30 of them turned in by the end of the day (not a high rate for the work put into them, but great concept).
• bring camera – yeah I did have my blackjack phone, but an actual camera would have been a better idea and by the end of the day I didn’t take a single picture.  Jason Strate did bring his, so I am anticipating some to be posted on his follow up blog posting sometime soon.
• double check event guide prior to print – wasn’t a huge deal, but there were a couple minor glitches.  The schedule on the web site had Brian Knight’s sessions back to back and in the guide we had Brian Larson set to present in between his sessions.  We simply made this designation on the poster by the room and made an announcement to the people in the room and standing in the hallway.  Wasn’t a huge deal, but none the less a glitch.  In the guide it also had two different times for the drawings, not a big deal.  The other thing that I missed was stating that Microsoft User Group Support Services (MSFT UGSS) was also a sponsor and provided funding for the event.
• more communication – I should have provided more information to sponsors about a few things like how the raffle process was going to work at the end and came up with a table designation for them that placed them in the area according to their service.  Some of the sponsors did swap tables, so that was solved for the most part (basically between the DBA and BI vendors).  Should have provided more information in the event guide about prizes, breakfast, lunch, surveys, and PASSMN 2011 election.  People asked for the most part and I explained it, but it would have helped to include a few more details in the guide and in email reminders prior to the event.  I did do one blog posting about the event prior to go-live, so hopefully that provided a little more insight – Inside Scoop on SQL Saturday 58 – Minnesota.  Over communicating is never a bad thing, especially for such a large event.  Live and learn I guess.  This was a big task to pull off based on the size of the event.
• more planning time – so we started setting this up on August 9 with getting the SQL Saturday site setup and then on August 11 I started talks with Mystic Lake Casino.  81 days (2 months, 2 weeks, and 6 days) later the event happened.  Pretty amazing that we were able to get 24 sponsors and everything put together in that time period, but all-in-all I think it was extremely successful and we can only improve upon this for next year.  The PASSMN Executive Board did a great job in helping out and my wife also came through for me big time.  I have a tough time asking for help and I am working on that and next year we will definitely need a few more people to help out during the day at registration and at the prize table (need to create a schedule and sign-up sheet).
• bring chap stick – sounds rather odd, but for some reason my lips got very dry and they still are today.

It would have been nice to sit in on a couple of sessions, but I did not.  I spent my time making sure everything was running smoothly, helped with registration, worked the prize table, talked with the sponsors, talked with speakers, and of course talked with attendees.  I wish we would have been able to come up with some way to record them, maybe with more time we could have tried to get Live Meeting setup and have everyone record their sessions (if they were okay with that of course).  Hopefully we will be able to get everyone to upload their content and have it available on the schedule page (click on the session and the download link will be available if they have done this).  I uploaded my content today – Leverage the MS BI Toolset to Monitor Performance.

I did get a lot of compliments on the event, the location, food, setup, speaker lineup, and prizes.  That was good to hear.  I hope everyone enjoyed it (how could you not?).  I know I did and it made me feel really good to see everything come together and to have people actually show up, volunteer, sponsor the event, and for people to come and speak.  We only had 16 speakers/volunteers show up for the dinner the night before the big event and 18 people show up for the after party (was hoping for more, so not sure what happened there – we hit the happy hour though).  I would like to see some better numbers for these two items, so next year we will try and improve upon that.

Now next year we will have to see if we have this on a Friday or Saturday.  We would anticipate that we would need a larger lunch area if we provide food for the event.  Mystic Lake could still work, but based on a convention style setup with the costs we would have to evaluate this (going to add a Platinum level for sponsorship).  I will not be on the PASSMN Executive Board next year.  After two years of being the Chair/President I have decided to open the spot up for someone new.  I have definitely enjoyed being a part of the board and am very pleased with how things have gone the last couple of years.

I will actually be shifting my focus over to the Microsoft BI User Group here in Minnesota next year.  I will be working with GNet Group and Microsoft, so this will now include Superior Consulting Services.  Next year I believe we are going to try to pool the two user groups together and combine forces to have another event like this, but try and make it even more special (is that possible? can it be done? yes, we can surely try or best).  We might have to explore the possibility of going down to the U of M campus and doing it on a Saturday, but we will see because I am still not a fan of the Saturday events.  We will publish a survey to get feedback about this year’s event along with suggestions/ideas for next year soon (please take a few minutes and provide some meaningful feedback with constructive comments).

I did a little PowerPivot analysis with the attendee registration last week (did you think I wouldn’t…) just to see how we were distributed and what companies were attending.  I know for a fact that Mystic Lake had at least 10 people in attendance, so they were our number one company for attendance if you factor out the blanks.  A little data cleansing would need to be implemented here to get the company names standardized because you will see quite a few different variations of the company names (data quality problems? nah).  Here is what we had (this is who registered, not actually attended):

And just remember, this was a FREE event for all of the attendees.  Simply amazing with a big kudos to all of the organizers, volunteers, Mystic Lake staff, and our sponsors!

I did upload my presentation material and this is available on the schedule page once you click on the session – Leverage the MS BI Toolset to Monitor Performance.  A reminder will be getting sent out to make sure that the other speakers upload their content as well, so hopefully over the next week or two all of them will be available.

Well, that is it for this one.  We should be ready to announce the new PASSMN 2011 Board shortly (doesn’t look like a vote will be needed – 2 years in a row).  We will have three new members on the board next year and I will still be around and volunteer when needed.  I am a fan of the SQL community, so I will always be around to help out.  We still have at least one more meeting this year and possibly two if we have a December meeting and it seems like we typically squeeze one in.

Inside Scoop on SQL Saturday 58 – Minnesota

I wanted to give everyone a good heads up on what is going on with the SQL Saturday that is scheduled for this Friday, October 29 at Mystic Lake Casino in Prior Lake, MN.  The registration for this event has been closed for a while now and we are fully booked for this event.  We also have a fantastic line up with 5 tracks (2 DBA, 2 BI, 1 SQL DEV) and 6 sessions (presentations) per track – Schedule – so it is understandable why we are sold out for this event.  Well ‘sold out’ is not exactly true, because this is a FREE event that is being made possible because of all of the Sponsors that have stepped forward to make a contribution and have shown their support and commitment to our community.  There has been a lot of work put in to get this event put together and I need to thank all of those involved.  The current Executive Board (and my wife) has done a fantastic job getting sponsors, getting the location planned, and making all of the additional preparations (printing, stuffing bags, ordering items, cutting items, stapling items, responding to numerous emails, etc.).  This is by no means a simple task to put together and organize and compared to last years event the preparation has almost tripled in size.

So on to some of the details now.  The agenda for the day is as follows:

Time	Event
7:30	Registration (by Dakota Ballroom)
8:00	Pre-show (in each Track room)
8:15	Session 1
9:30	Session 2
10:45	Session 3
11:45	Lunch (in Dakota Ballroom)
12:00	Keynote/Sideshow/Announcements
1:00	Session 4
2:15	Session 5
3:30	Session 6
4:30	Raffle (in Dakota Ballroom)
5:00	After Party Event (Mc Hugh’s Grille & Bar – Savage, MN)

 

Each session will be 1 hour in duration, so the talks will be probably around 50 minutes in duration so that we have 10 minutes or so available for Questions/Answers.  There is seating in each track for  66 people (3 rows of classroom seating and 3 rows of theater style seating).  Attendees can attend any track and session that interests them.  Each attendee that is registered for the event and on the attendee list will receive an attendee bag that is filled with information from our sponsors, event guide, network bingo card, and speaker evaluation forms.  During registration attendees will also get a name badge and raffle tickets that they can place in the raffle baskets located on each of the sponsor tables.

The network bingo cards are going to be used to award 2 individuals with an MSDN Ultimate Subscription that are being contributed by myself and Jason Strate (thanks to the MVP program).  These will be raffled off at the end of the event.  The sponsors will raffle their items off at the end of the event as well.  Unless noted otherwise, attendees can only win one raffle prize and you must be present to win.

We will have continental breakfast located in the session/sponsor area

and for lunch a buffet style setup will be in the Dakota Ballroom will include a deli sandwich setup with pastas and some dessert.

The food order has been set and there is a limited number which is based on our funding.  Hopefully we will be okay, but I apologize if for some reason we run out or do not have enough seats.  This has been a very difficult task to coordinate and I have done my best to try and accommodate as many people that I believe we can.  Definitely provide me any feedback and please be kind, because don’t forget this is a FREE event and a lot of work has gone into make this happen.  Not only are we organizing and working during this event, but some of us are also going to present (and Jason is doing two presentations – true SQL warrior).

We will have a prize table setup and each speaker will be provided a couple of prize tickets that they will be able to give out.  The attendees that receive these tickets will then be able to go to the prize table and select an item of their choosing.  For the most part the prize will be a book and we have received books from Wrox, O’Reilly (and ebook offers), Apress, Pearson’s Education, and McGraw-Hill Professional.  It is truly amazing all of the support we have gotten from all of the Sponsors – thank you so much.

One last item before I sign off and get ready for work today, I just wanted to mention that there will be free WIFI in the conference area.  I am planning on testing that out Thursday night when I am out there for the speaker/volunteer dinner.  I am looking forward to meeting everyone and I apologize if I am a little out of it on Friday and have to cut our chats short because I will be running around making sure everything is going as smooth as possible.  It will be interesting heading back to Mystic Lake Casino Hotel since that is where I used to work for over 10 years.  Lots of memories.

If you are on the waiting list I apologize.  We have been having cancellations which will enable the waiting list attendees a spot.  I cannot guarantee a spot (primarily when it comes down to the food).  If you show up on the day of the event and we do not reach our max capacity once the first session starts we should be able to get you included in the event.  We just request that you be patient as we get all of the registered attendees signed up and once it begins we will assess where we are at and do our best to get you included.  We definitely want to allow everyone to attend, but we are limited on the food based on funds and we want to make sure that those that did register get a spot to sit in their sessions that they want to attend.

Hope to see you there – SQL Saturday #58 Minnesota.  Don’t forget you can tweet and follow information about the event using the hash tag #sqlsat58. Later!

Microsoft Business Intelligence Portal Page References

[tweetmeme source=”denglishbi” only_single=”false”]

UPDATE (6/2/2011): Updated the Microsoft BI link to reflect updated BI site and resources (2nd link) and added the SharePoint 2010 Resource Centers link.

Looking for good information in regards to the Microsoft Business Intelligence components?  Not sure where to start?  Well look no further, here is a list I compiled that provides the current link to where the portal pages are for each BI related component:

Microsoft Business Intelligence Scenarios	http://technet.microsoft.com/en-us/bi/default.aspx
Microsoft Business Intelligence	http://www.microsoft.com/BI/en-us/pages/home.aspx
SQL Server Best Practices (MSDN & TechNet – slight differences and not sure why there are two of them)	http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx
SQL Server Reporting Services (SSRS)	http://msdn.microsoft.com/en-us/sqlserver/cc510304.aspx
Report Builder	http://technet.microsoft.com/en-us/ff657833.aspx
SQL Server Integration Services (SSIS)	http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx
SQL Server Analysis Services Multidimensional Data (SSAS)	http://technet.microsoft.com/en-us/sqlserver/cc510300.aspx
SQL Server Analysis Services (SSAS) Data Mining	http://technet.microsoft.com/en-us/sqlserver/cc510301.aspx
PowerPivot for Excel (Business Intelligence)	http://technet.microsoft.com/en-us/bi/ff604673.aspx
SQL Server Master Data Services (SSMDS)	http://msdn.microsoft.com/en-us/sqlserver/ff943581.aspx
Microsoft SharePoint 2010 Products	http://technet.microsoft.com/en-us/sharepoint/default.aspx
Microsoft SharePoint 2010 Resource Centers	http://technet.microsoft.com/en-us/sharepoint/ff465365
Microsoft Office PerformancePoint Server 2007	http://technet.microsoft.com/en-us/office/performancepoint/default.aspx#tab=1

 

These are all great reference points to help you get started in the technology, highlight particular areas, and also introduce you to the members of the community.  The first part is to find them and that has been taken care of (wish it was a bit easier myself…).  Some items listed are on TechNet and some on MSDN.  Not sure what the differences are between the two, TechNet and MSDN (IT Pros vs. Developers I guess), and that has confused me why these are split.  Now to go explore and check them out, that part is up to you.

If I have missed one please let me know and I will be sure to add it to the list.

Thanks and enjoy!

SQL Saturday 58 – Minnesota Update

We are less than two weeks away from the big event.  SQL Saturday 58 – Minnesota is just around the corner and is on Friday, October 29 at Mystic Lake Casino Hotel in Prior Lake, MN.

We are plugging away at getting final arrangements made for the event and I am sure I will have a few sleepless nights between now.  We have a great lineup of presentations and speakers:

Start Time	BI-1	BI-2	DBA-1	DBA-2	Development
8:15 AM	Brian Knight Introduction to PowerPivot	Steve Hughes Report Lockdown: Implementing SSRS Security	Kevin Kline SQL Server Internals and Architecture	Eric Strom Tuning SQL Code using Logical Equivalencies	Mike Benkovich Taking your Data to SQL Azure
9:30 AM	Brian Knight Performance Tuning SQL Server Integration Services	Mark Vaillancourt Intro To Reporting Services(2008 R2) Report Models	Bill Preachuk Maintenance Plans – Lessons from the Real World	Jason Strate Using XML to Query Execution Plans	Ross McNeely Dynamic Text File Creation Via Single SSIS Package
10:45 AM	Brian Larson Using Data Mining to Improve SSIS Data Loads	Gilbert Evans Self-Service Reporting/Analytics for SQL Server	Kevin Kline Top 10 Admin Mistakes on SQL Server	Dean Richards Query Tuning – Get it Right the First Time	Eddie Wuerch Drive SQL Server Performance: I/O For Developers
12:45 PM	Dan English Leverage the MS BI Toolset to Monitor Performance	William Pearson Getting Started with MDX	Tim Plas Managing & Administering SQL Azure	Zach Mattson Powershell Eye for the Monitoring Guy.	Russel Loski Stored procedure tracing using the xml datatype
02:00 PM	David Baldauff A Single Sign-On BI Story: SAML Claims in SharePoi	Mustafa Hussain Answers at the speed of thought with SSAS Cubes	Eddie Wuerch Find Performance Problems by Reading the Waits	Jason Strate Extended Events, Work Smarter Not Harder	Mike Benkovich Programming with OData & the Microsoft Data Stack
3:15 PM	Russel Loski SSIS and SSRS Better Together	William Pearson Documenting Your Integrated Microsoft BI Solution	Tim Plas High-Availability & D/R Options for SQL Server	Ben Thul Introduction To Transactional Replication	Eric Zierdt Table Partitions for Archiving

I will be doing a presentation on using the Microsoft BI Toolset to build out dashboards.  Trying to demystify the different tools that are available and show how they can all be leveraged to build out a unified dashboard.

We are currently at our max capacity for the event which was 250 and we should be able to open this up later this week to allow everyone on the waitlist to be included.  If you are registered and are unable to attend please take the time to use the cancel option on the site – http://sqlsaturday.com/58/changestatusrequest.aspx.

Thanks again to all of our sponsors who are signed up for making this event possible – http://sqlsaturday.com/58/sponsors.aspx.

Another item to watch out for at the event will be the return of the Networking Bingo card that we had last year at the PASSMN SQL Summit event.  This card will be used to giveaway two MSDN Ultimate Subscriptions!  Very cool prize!

Hope to see you there for this spooktacular event!

SSRS R2 Map – What Happened to Michigan?

I am not a geography or map expert and that is most likely why I did not pickup on this when I did my previous postings on the new Map report item in Reporting Services 2008 R2, but yesterday I noticed that Lake Superior was not being represented on the map

Does something look a bit odd?  The rest of the states look pretty good.  So why does this not look like what I am used to seeing?

So I thought this was a bug with the map of the entire USA.  I go down to the individual states and this is the shape for Michigan

Hmmm… What is that?  I thought Michigan looked like the following

If you look at Bing and Goolge maps you will see the outline polygon shapes and it does align with what is setup with the map in SSRS

Bing

Google

And you will notice that the shapes of Minnesota and Wisconsin are a tad bit different as well and this is also represented in the SSRS map.

I go ahead and pull down the spatial data for the states (for the steps on how to do this check out Sean Boon’s (Blog | Twitter) response in the forums here – How do I do this? R2 Map Feature) and create a map using the spatial data and here is what we get

Seems really odd to me.  It does not appear to be a bug because it is used this way.  Really confusing to me though and not sure what the reasoning is for this representation.  There is a connect bug for this, but as I stated it seems like it is correct – SSRS 2008 R2 USA By State – Michigan has incorrect shape.  So what do you think?  Anyone know why this is?

I did find this little blurb here and maybe this is the answer, not sure:

In addition to a common database structure, the conterminous U.S. state databases (the contiguous 48 states) have been fitted to a set of standard state boundaries so that, when states are merged, they match without slivers or overlap. No attempt has been made to reconcile differences in mapping across state boundaries.

Just kind of odd to me, but I guess this is what we have to work with.  The shapefiles for  Alaska and Hawaii are accurate – would be odd if they were not and just got merged like the rest of the 48.

UPDATE (10/17/2010): I went ahead and loaded the spatial data for Michigan for the counties and zip 5 data and I ended up with these results.  One actually provides the results we would expect to see

County representation:

Zip 5 representation:

Interesting.  State and county polygon data do not provide the layout we would expect, but referencing the zip codes does provide a familiar view of Michigan.

PerformancePoint Unexpected Error Occurred 11861

From time-to-time when I am building out a new dashboard with PerformancePoint Services 2010 I will all of a sudden get an unexpected error when I try to create an Analytical Report.  When I create the report and add a measure to the report I simply get the error message “An unexpected error occurred. Error 11861.”

An unexpected error occurred. Error 11861. Additional details have been logged for your administrator.
Contact the administrator for more details.

When I try to add items to the report I keep getting a message stating “This data source cannot be used because PerformancePoint Services is not configured correctly. Additional details have been logged for your administrator.”

That is a funny message.  Kind of scary as well.  Is my PerformancePoint broken?  Oh no!  Help!  What did someone do to the service?  Wait a second, I just got done setting up KPIs with no problems.  What is going on?

Well the issue is that you have created a data connection that is currently not published (saved) to the server.  KPIs don’t have any issues with this, but the Analytical Reports do.  You will notice other odd things during your PerformancePoint development like this where these two items behave a bit differently.

So if we take a look in the Workspace Browser we will see the pencil on the data source icon in the data connections.  This means that it has been modified and is basically in edit mode.  This could also be the case if it was already saved (published) to the server, but in this case I have not performed an initial save.

So if we select the data connection and click the Save icon in the Quick Access Toolbar or do a right-click and select Save then the data connection will end up looking like this:

Now if we go back to our Analytical Report we were creating we will see the following message:

Drag measures, dimensions, or named sets to Rows and Columns to lay out the report.

Now the designer is happy and we can build out the report and add items to the rows, columns, background, etc.

So if you get this message don’t be alarmed and go running off to your SharePoint Farm Administrator, simply take a look at your Workspace Browser and see if you have a connection that needs to be published.

Round 2: Calculating Previous Year Marketing Calendar Values with DAX

So a couple of weeks back I did the posting Calculating Previous Year Marketing Calendar Values with DAX where I talked about a solution for getting at previous (last) year values in DAX with Marketing (Retail) Calendars.  For the most part the solution worked, but as I looked at the results a bit closer I noticed that it eventually broke down and failed.  Why is this?

Well the answer should have been obvious and I have ran into this before, but eventually these 445, 454, 544, or 13 period calendars run into a small issue that they need to deal with.  The issue is that these calendars only deal with 364 days (13 periods x 4 weeks x 7 days = 364 days).  So there is an extra day that we eventually need to deal with and then we run into the leap year and all of this eventually creates the mysterious week 53!

This situation is documented here on the National Retail Federation web site “4-5-4 Calendar”:

An Explanation of the 53-Week Year

Dividing the retail calendar into 52 weeks of seven days each, or 364 days, leaves an extra day each year to be accounted for. As a result every five to six years a week is added to the fiscal calendar.

If I go back to my original solution and scroll a little further down in my PivotTable I would have seen this since in Feb 2008 we run into a leap year.  This eventually causes the solution to bomb out when we get into the 2009 and 2010 comparisons and so on.

So, now what do we do?  Time to regroup and look for a solution to work around this.  After some brainstorming I realized that we just needed to expand on the original solution.  This means that we need to tell each time frame that we are dealing with how it relates to the previous period.  So not only at the day level like I originally setup, but also for the week, month (period), quarter, and year.  So we go back into our table and setup some additional columns:   

And then populate these dates with values:

Make a few tweaks to the calculation to evaluate the different time frames and reference the date ranges that we just defined (I don’t show it, but we also updated the table in the PowerPivot Window to include these new columns into our model):

PY Sales Amount =

IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Year])) = 1,
CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1,
FIRSTDATE(DimRetailDate[PY_Date]),
FIRSTDATE(DimRetailDate[PY_Reporting_Week_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Month_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_Start_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Year_Start_Date])),
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Quarter_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Month_Of_Year])) = 1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Week_Of_Year]))=1,
IF(COUNTROWS(VALUES(DimRetailDate[Reporting_Day_Of_Year]))=1,
FIRSTDATE(DimRetailDate[PY_Date]),
FIRSTDATE(DimRetailDate[PY_Reporting_Week_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Month_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Quarter_End_Date])),
FIRSTDATE(DimRetailDate[PY_Reporting_Year_End_Date]))))
,BLANK())

And now we go back and take a look at our results:

Tada! We are back in business. And this will work at the other levels as well (here we use the Reporting Year and Quarter):

So now we are back in business. Just needed to expand out the original solution into each of the different time frames that we wanted to analyze and make sure that we have the previous year mappings setup according to our business rules in the table we are referencing.

Where did my Excel PowerPivot tab go? Follow-up

Back in June I did a post called Where did my PowerPivot Excel 2010 tab go? and I talked about being impatient waiting for Excel to startup.  The reason that it takes longer now is that I have to wait for the add-ins to load and specifically the PowerPivot one now that I am using that with Excel 2010.

I had a dream that Excel would load fast.  I even did a tweet about this back in May:

denglishbi any options to speed up #Excel 2010 loading. now that #PowerPivot is installed it takes too long to load add-in & extras. please speed up.

I then did the post that I referenced above talking about how I was able to disable the PowerPivot add-in by clicking on the cancel option on the startup splash screen when Excel loads up.  Little did I know there was someone observing my comments and they decided to take some time and actually build out a solution!  And his name is Hrvoje Piasevoli (Blog | Twitter).  He came up with a solution creating some PowerShell scripts – Shortcuts to start Excel with or without PowerPivot add-in.  Today I download the solution and ran the setup process provided in his instructions and it works like a charm!  Big kudos to Hrvoje!

One thing to watch out though is that if you don’t use the shortcuts to access Excel the next time that it will load up the last option that you ran.  This means that if you do the ‘Excel Only’ shortcut, close Excel, and then click on the Excel shortcut in the start menu that Excel will load normally (without PowerPivot add-in).  To enable the PowerPivot add-in you can follow my steps in the posting above or simply close Excel and then double-click the ‘Excel with PowerPivot’ shortcut on your desktop.

Many thanks go out to Hrvoje for creating this setup.  Maybe it should be moved out to CodePlex?  Not sure what the process is for that, but definitely a sweet little setup and even includes an uninstall process.  Once again, thanks Hrvoje!  You rock!

October 2010 Free DW & BI Training

Looking for some FREE data warehousing and business intelligence training?  Well you are in luck, this month is loaded with lots of great content both online and in-person where you can learn about Microsoft SQL Server offerings.

Online you can check out the following:

PASS DW/BI Virtual Chapter

• Fri, Oct 8th 12 p.m. EST – Reporting from Analysis Services Cubes Using Excel 2010 and Sharepoint 2010 Excel Services (by Peter Myers)
• Tue, Oct 12th 12 p.m. EST – Maps Integration into Reporting Services (by Matt Smith)
• Wed, Oct 20th 12 p.m. EST – Getting started with SteamInsight and Complex Event Processing Applications (by Chris Coneybeer)
• Mon, Oct 25th 12 p.m. EST – Applied MDX (by Kevin Geoff)

SQL Server Community Training – Pragmatic Works

• Tue, Oct 19th 2 p.m. EST – SQL Statement Tuning with Indexing Strategies (by Kevin Kline)
• Thu, Oct 21st 11 a.m. EST – Out with the old in with the new, Migrating DTS to SSIS (by Brian Knight)

In-person you can check out (if you are in MN):

SQL Saturday #58 – Minnesota (registration is limited, so sign up now)

• Date: Friday, October 29, 2010 Time: 8 a.m. to 5 p.m. (registration starting at 7:30)
• Location: Mystic Lake Casino Hotel, Prior Lake, MN
• Cost: FREE!
• Schedule: http://sqlsaturday.com/58/schedule.aspx
• Registration: http://sqlsaturday.com/58/register.aspx
• Event site: http://sqlsaturday.com/58/eventhome.aspx

At SQL Saturday (on a Friday) there will be training on PowerPivot, SSRS, SSAS, Data Mining, SSIS, PerformancePoint, SharePoint, PowerShell, and more!

PASSMN 2011 Executive Board Nominations Now Open!

This month we are now accepting nominations for next year’s Minnesota SQL Server User Group (PASSMN) Executive Board.  You can fill out the nomination form here – http://www.surveymonkey.com/s/FY9RTJS.

This is your chance have a voice and to take your involvement with the user group and our local SQL Server community to the next level.  Our user group meets typically once a month on the 3rd Tuesday of each month.  There are five executive board roles that are available once you are voted into the board.  These are:

• Chair
• Director of Program Development
• Director of Membership/Treasurer
• Director of Corporate Development
• Director of Technology

For more information about these positions and for a list of the current board members check out the PASSMN site here – http://minnesota.sqlpass.org/About/BoardMembers.aspx.

The nomination process will go through the end of the month and the voting process will begin in November with the new 2011 board announcement in December.  So don’t procrastinate, put your name in now!