My blog has moved to WordPress.com

In case you didn’t know, I have moved my blog this past week – So long Live Spaces, Hello WordPress!.  I am now up and running on WordPress.com and just getting used to the setup.  I noticed one thing today and that was some of my blog posts were a tad wide with images like one of my popular ones (I can now track the Top Posts and monitor them more closely – excellent, this will help direct me as to what content interests people) Using Excel Services Reports with PerformancePoint Server (PPS).  I noticed that when you viewed this post it was cutting off the third column in the images.  Ouch and sorry about that.  On Live Spaces if you clicked on the blog title it would basically remove the column on the right and make the posting wider.  Not so in WordPress.com, for some reason the header limits the width of the blog site and then the left and right columns reduce the blog width as well.  Need to watch this a little more closely.

Anyway, I swapped to a new theme tonight.  I don’t really like the color scheme, but the layout presents a good size for the blog postings which is most important and I can surround them on both sides with additional items like Top Posts, Visitor Tools, Tag Cloud, Categories, and a Search feature.

So if you had a references to my Live Spaces blog or RSS feed you will need to update them appropriately or you will not be receiving any of my new content.  If you were using my feedburner RSS feed you will be fine.  So please take some time now and double check your setup and references – https://denglishbi.wordpress.com

So long Live Spaces, Hello WordPress!

On Monday when I went to access my blog that I have had since December 2007 I received a message stating that Live Spaces had reached an agreement with WordPress.com and that I had an option to move our postings over.  I was a bit surprised, but actually quite relieved at the same time.  I knew it was time for a change for a long time now and this was my opportunity.  It did state that I could still continue making posts through the end of the year, but after that it was going to be locked down and then as of March of next year I would not have an opportunity to do the conversion anymore.  Kind of like last year when some of the services I was using decided to pack up and shutdown – Goodbye GeoCities, Popfly, and now Soapbox.

Some of the things I have been wanting for my existing site like better stats (actually at this point any stats since Live Spaces dumped them all together a few months back without any warning), improved comment control, tagging, top and recent post displaying, more layout display control, calendar and number of posting display, twitter display, removal of cryptic URL references, and more were all of a sudden available to me within just a couple of minutes.

First I downloaded my 266 existing blog posts (nice touch to provide this option during this transition) and this included the comments and images as well.  Then I did the conversion process and this also redirects Live Space references to WordPress.com and after just a couple of minutes I was up and running.  I was free!  Sweet!

Now I have so much and it is just so easy to setup and use, just have to make a decision on how I want it to look now.  Here are a few things I have already done (and I am sure this will change a few more times over the next couple of weeks) and have access to now:

1. Top and Recent Post display – now with the improved stats and widget capability I can quickly include this on my site for visitors to reference

 

2. Blog Stats – what I have been wanting and more.  Referrers, Top Posts & Pages, Search Engine Terms, Clicks, Summary

3. Tagging – now I just need to add them to posts and then I can include the nice little Tag Cloud in addition to categorizing posts (and I can have more than one tag or category per posting now!)

4. Post Counts for Archives and Categories – nice little touch and available with a simply checkbox option in the widget and just like magic after the item you will see in parenthesis the post count

5. Twitter display – ability to show some of my recent tweets

6. Comment control – spam filtering and moderating capabilities (I actually received an email this morning alerting me of a comment and giving me an option to approve it. Plus it catches spam comments (finally, thank you) and I can edit comments (nice)

7. Post Calendar – highlights the days that I blogged on (just a nice little touch and good visualization for me to keep me motivated)

8.  Goodbye cryptic Live Spaces URLs – now people will have an idea of what I am directing them to when I send them a URL

so instead of this: http://denglishbi.spaces.live.com/blog/cns!CD3E77E793DF6178!3457.entry

they will now see this: https://denglishbi.wordpress.com/2010/06/23/sorting-powerpivot-labels-manually/

9. Nice little SnapShot like site display for viewers (it probably is SnapShot, maybe WordPress bought them, not sure) – this gets displayed when you hover over a URL in a posting

10. Built in blog searching capability – had to setup a gadget on Live Spaces and typically did not produce any results recently which was a major disappointment, could have been a code change

11. Themes, Pages, Navigation, and more – these are additional items that I will be playing around with over the next couple of weeks, so don’t be surprised if the view changes.  There probably is a way now to include the Microsoft MVP logo now…  Lots of options and I have only been using the new set of tools for just a couple of days and I am probably just scraping the surface.

If only I could have done this switch over sooner.  It is such a relief to switch over and just after a couple of days I am extremely pleased of the move.  Thanks Live Spaces and three cheers to WordPress!

Calculating Previous Year Marketing Calendar Values with DAX

UPDATE (9/27/2010): watch out for the mystery 53 week that occurs every 5 years or so.  formula does not account for this at all and will fail eventually.

UPDATE (10/9/2010): I have an updated post that has a resolution for handling the 53 week Marketing (Retail) Calendar setup – Round 2: Calculating Previous Year Marketing Calendar Values with DAX.

When it comes to reporting typically we will use the common calendar year that starts on January 1 and goes through December 31 with the standard months.  We might even be faced with a fiscal calendar that starts on July 1 and ends June 30 the following year.  In both of these cases we are sticking with the set days in the months and are not doing anything special.  But what happens when we start to use a custom calendar for marketing or retail purposes like a 445, 454, 544, or possibly 13 periods of 4 weeks each?  Will this have any impact on how we need to create our metrics to report on our data?  The answer – Yes.  Because in this case we are not simply evaluating September 1 of this year to September 1 of last year.  Nope.  We are actually evaluating it to September 2 of last year which is the same day of the week.

So now the challenge is how can we do this and how can we do it with PowerPivot using DAX?

Disclaimer: I cannot guarantee that this calculation and example will work the same as it did for me, so please always verify the results and add any additional checks that may be needed, this is strictly setup as a demonstration.

For this demonstration I am going to use a marketing calendar using a 454 week setup that starts with the first week in March.  To create this date dimension table I leveraged the functionality in BIDS to create a time table in my data source to generate this table for me – Creating a Time Dimension by Generating a Time Table.

Once this table was created for me in my SQL Server database I went into that table and created a new column in the table that I called PY_Date.  This column is going to contain the prior year equivalent date for the dates that are stored in this table in the PK_Date field that was generated by the wizard.  I then went ahead and populated this column with the prior year equivalent dates so we now have data that looks like this in the table (I am just displaying the two date columns here):

 

This new column is what I call the ‘silver bullet’ that will be required to reference in our DAX formula.  I am kind of blowing it by introducing this first, but I will continue on and show a couple of DAX formulas for a typical calendar and then how it will be adjusted to work with the marketing calendar.

Once we have this table setup and have some facts that we want to relate to this new time (date) dimension we go ahead and load this into PowerPivot and setup our relationship between the two tables.

Now we are ready to setup a PowerPivot PivotTable to analyze our data and create additional measures.  In this first example I place the Year Name and Month on the Rows and Sales Amount in the Values.  After this I then create a new measure to calculate the Previous Year Sales Amount leveraging the CALCULATE, DATESBETWEEN, FIRSTDATE, DATEADD, and LASTDATE functions:

PY SalesAmount =CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],FIRSTDATE(DATEADD(DimRetailDate[PK_Date],-12,MONTH)),LASTDATE(DATEADD(DimRetailDate[PK_Date],-12,MONTH))))

Here are the results we end up with:

Now this works create when you have standard reporting that uses the typical set month periods.  What does it look like when we swap out the members on the Rows with the Reporting Year Name and Reporting Month values that are referencing our marketing 454 week calendar setup:

As you can see this same formula will not work.  So now we take a look at using that additional column that we setup earlier for the equivalent prior year value for our dates and we modify the original calculation slightly:

PY Sales Amount =CALCULATE([Sum of SalesAmount], DATESBETWEEN(DimRetailDate[PK_Date],FIRSTDATE(DimRetailDate[PY_Date]),LASTDATE(DimRetailDate[PY_Date])))

Now let’s take a look at how this looks with the data:

There we go.  That is what we want.  Now we are looking at equivalent dates.  So we need one measure to use with standard calendar reporting periods and a different one if we are reporting against equivalent time periods like using a 454, 445, 544, or some other type like 13 equal 4 week periods.  The secret ‘silver bullet’ is the additional previous year equivalent date column in the time (date) dimension table that is utilized to make the results we need.

Warning: As stated above I cannot guarantee that this is going to work the same in your environment and you might need to include some additional IF logic to do some additional checks to return BLANK().

Enjoy!

PASSMN September 2010 Meeting Follow-up

This past Tuesday night we had another great monthly meeting here in Minnesota.  We had the pleasure of having Kalen Delaney present to us and she delivered another great session to our community on Isolation vs Concurrency: What Are the Choices?  This was the same presentation she did for the 24 Hours of PASS last week, but I was not fortunate enough to attend so this was perfect.

If you did not get to attend this meeting or see the 24 Hours of PASS session you will be able to check it out starting next week.  All of the sessions for the 24 Hours of PASS were recorded and will be made available:

All sessions have been recorded and will be available for viewing by September 27.

Don’t forget that next month we will not be having our regular monthly meeting, but don’t worry there is an option for you and guess what….it is an all day free event at the casino!  SQL Saturday #58 will be on Friday, October 29.  We are looking to have at least 4 tracks of most likely 6 sessions (presentations) each, maybe more, with tons of great content and all related to SQL Server of course.  Hope to see you there!

If you want to check out the announcements from this month’s meeting I have uploaded them to my SkyDrive.  Thanks to Jason Strate for putting these together.  We also place this in our Resources->Documents section on our PASSMN site, but currently we are working out some SSO site issues with a recent switch we did.

Last call for SQL Saturday #58 Speakers

We are on the final week for the call for speakers for the SQL Saturday (on a Friday) event in Minnesota at Mystic Lake Casino on Friday, October 29.  This is an all day free event brought to you by our wonderful and generous sponsors and the community.  We already have a great line up of sessions submitted by local and national speakers like Kevin Kline, William Pearson, Brian Knight, Jason Strate, Brian Larson, Lara Rubbelke, and more!  I will also be jumping in the ring to present a session.

So if you have a topic that is of real particular interest to you and you want to share the content with your fellow community then here is your chance.  This is a great opportunity to talk about something that you find very interesting and share it with people that want to hear about it.  We are anticipating close to 250 people in attendance.  Right now we have just over 160 people that have registered and the list is growing.

What are you waiting for?  The session submission ends this Friday, September 24.  Go ahead and throw your hat in the ring.  If you don’t feel like presenting you can submit a suggestion for someone to talk about.  You can at least go out and register for the event and come join in the excitement.

Next week we are hoping to have a finalized schedule in place, so stay tuned.

Sorting PowerPivot Labels Continued

I was reading the new PowerPivot book, Practical PowerPivot & DAX Formulas for Excel 2010, I purchased a few weeks back and I came across some new information (always a plus).  The information I discovered ties back into my posting I did a few months back in regards to sorting labels manually – Sorting PowerPivot Labels Manually.  The posting talked about sorting month labels so that they were not in alphabetical order, but in the standard Jan, Feb, Mar, etc. order.  Now what I come to find out is that instead of doing that manual work you can simply use the ‘Sort A to Z’ option instead.  Excel is smart enough to know that these are months and should be sorted in a particular order.  Let’s take a look at this:

Before (notice that the months are sorted alphabetically):

Select the ‘Sort A to Z’ option for the month label name:

After:

Now everything is in the proper order.  Not exactly what you would have expected, but this is what we want to see.  Nice little hidden feature.  So what else can this work for…hmmm…what else is standard?  How about days of the week?  Let’s take a look at that:

Before (notice that the days of the week are sorted alphabetically):

After the ‘Sort A to Z’:

Shazaam!  Sweet.  Now if only you could do this in the Slicers that would be golden, but no such luck.  Plus this is only for the particular PivotTable that you performed this on, so you would need to continue to remember to sort the label names in other PivotTables.

Hope you like that little trick, I know I do.

PASSMN September 21, 2010 Monthly Meeting

The next Minnesota SQL Server User Group meeting is on Tuesday, Sept 21.  This month we be meeting in a different location across the skyway over in the 8000 building on the 2nd floor above the Caribou Coffee shop.  We are pleased to announce that Kalen Delaney will be presenting this month – www.SQLServerInternals.com.

In order to RSVP for the event you will need to login to the national PASS site and click on the RSVP link.  The sponsor for this month’s meeting is Microsoft User Group Support Services.

PASSMN September 2010 Meeting – Isolation vs Concurrency: What Are the Choices?

• Meeting sponsor: Microsoft UGSS
• Location: 8000 Norman Center Drive, 2nd Floor, Bloomington, MN  55437
• Please RSVP here for the event to assist in making sure there is enough snacks and beverages ordered.
• Live Meeting URL: no live meeting due to location change

Agenda:5:00 –5:15 : Announcements
5:25 –6:45 : Isolation vs Concurrency: What are the Choices?
6:45 –7:00 : Survey Collection and GiveawaysPresentation:Isolation vs Concurrency: What are the Choices? (presenter Kalen Delaney) – What does it mean to have data isolation? In this presentation, we’ll look at the four different ANSI isolation levels and the new one, Snapshot Isolation, added in SQL Server 2005 to see how much isolation each provides and what behaviors—such as Dirty Reads, Nonrepeatable reads, and Phantoms—are possible in each level. We’ll discuss what consistency might mean to your applications and what the trade-offs are as you choose a higher isolation level to enable greater isolation and consistency. We’ll also discuss the difference between the optimistic and pessimistic concurrency models and look at the relationship between isolation level and concurrency models to see how the various isolation levels work in either an optimistic or a pessimistic manner.

One other thing to mention is that next month we will not be having our regular monthly meeting.  Instead we will be having an all day free event out at Mystic Lake Casino in Prior Lake on Friday, October 29 – SQLSaturday #58 – Minnesota 2010, Oct 29.  Registration for this event will be required so that we can properly order the food and organize the space required for the event.  This will be a similar event like last year where we had different tracks (DBA, BI, SQL Dev) and multiple sessions (presentations), just like the launch event earlier this year as well. 

Please spread the word in regards to both of these events!

What happened to my PowerPivot Excel Data? 1904? What the?

A few months back I had a rather unique Excel discovery.  We were loading Excel data files into PowerPivot using the import Excel file option instead of the copy/paste and linked table method (for obvious reasons) and all of a sudden we noticed some issues with dates.  The source data looked like the following:

But when we loaded this into PowerPivot it looked like this:

I was like…what the?  Now I know I can’t stand using Excel as a source file for loading data from, but this was something I had never experienced before.  Why were all of the dates on the rows imported into PowerPivot off by 4 years and 1 day?  We did go ahead and try the copy/paste option and here are the results we saw:

Well now, that is what we want.  But we don’t want to use the copy/paste option because we want to streamline the load process and eventually schedule data refreshes in SharePoint.  So what is causing this to happen?  Well it turns out that different versions of spreadsheet software have different start dates and actually Excel provides a way for users to set the starting year date to not reference 1900 (which it thinks is a leap year, but it actually isn’t) and to use 1904 instead.

This was news to me.  I tracked down a Microsoft knowledge base article in regards to this issue as well XL: The 1900 Date System vs. the 1904 Date System.  So since the Excel file I was loading the data into did not have the 1904 setting in place, but the source file did then this caused the dates when loaded using the import option to be offset by 4 years and 1 day.  What we ended up doing was changing the source file setting to not reference the 1904 starting year and then adjusted the dates accordingly.  Once we did that we were back in business.  We did contact the business users and verify that this was okay and made sure they were aware of the change we were making.  It turned out they didn’t even know about the setting either.  Weird.  Assuming someone that originally created the file must have done it for some reason.