PASS Summit 2014

Well, it is that time of year again. The Professional Associations for SQL Server (PASS) annual summit. This year we go back to Seattle where Microsoft mostly lives and hope we can get lots of their employees to visit and talk shop.

One of my main goals is to Network and get contacts from around the SQL Server world, but I always find 3 new things to learn at the Summit to bring back to my job. There, I can show the value of going to this event to my boss(es). This year the first goal is to find new information about maintaining a robust Data Warehouse since this is my main job at eQHealth. The second is to learn about PowerBI and what can be done in the Cloud (yes, you heard that right I said Cloud). Finally, I need to learn about Column Store Indexes and other improvements in performance of queries. There is always something to learn in this area and many sessions to be a part of.

I fly in on Monday night, so I will miss the annual Networking event put on by Steve Jones and Andy Warren. If you are there Monday night, it is worth the time. Networking is a big deal at these events, so get out and start talking.

Tuesday, I will start the day with a visit with other PASS volunteers, then attend one of the many great Pre-Conference All-Day sessions with Alberto Ferrari – From 0 to DAX. Tuesday evening is the Welcome Reception and a Volunteer Party.

 

 

Wednesday morning I will start the morning at the Community Zone from 7:30AM to 8:30AM. Then, listen to the Keynote. The 10:15AM session will be with Reza Rad on Data Mining. I meet Reza last year and it was a pleasure talking and learning from him. Smart guy. At noon, I will be in the Virtual Chapter lunch as I am a VC Mentor and need to meet and greet our leaders. At 1:30PM, Jen Stirrup is talking PowerBI & R. Peter Meyers (one of the best presenters I know) will be presenting on Master Data Services is at 2:45PM, which might be helpful at my job. At the end of the day it is a toss up between Marco Russo – DAX Patterns and Jon Welch – Continuous Delivery of DW & DMs. Wednesday night is the Exhibitor Reception.

Thursday morning has another Keynote on The Cloud, followed by a 10:15 session by Reza Rad (again) on Query M Functions. I will probably have to leave early to man the Community Zone from 11:30-12:30 during lunch. Hosting a Lightening Round session is next at 1:30 which I am really excited about. I will probably have to chill out after the Lightening Round and take a leisure break at the community zone and just visit with people or end the day at Alberto Ferrari’s Adv Model w/ SSAS Tabular. Thursday night is the Community Appreciation Part – EMP Museum which is always fun.

Friday morning I present at 10:15AM – Excel 2013 Tips and Tricks for SSAS Multidimensional. Unless I am totally comfortable with the presentation, I will probably take it easy getting up in the morning, rehearse the presentation at the hotel and then end up in the Speaker Ready room and try to sneak in the presentation room to test everything. After that all bets are off, except to fly home late on a red-eye to get back to Baton Rouge.

Can’t Create A Timeline in Excel on Multidimensional Cube

Excel 2013 - We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.

After getting excited about the Timeline object in Excel 2013, I tried to add this cool feature of Excel 2013 and got an error – “We can’t create a Timeline for this report because it doesn’t have a field formatted as Date.”

 

After some Bing searches :), I found the solution to be adding the ValueColumn property of the Date Key attribute to be a date value. In this example, my Date key attribute in the Date dimension is an integer like 20140101 not the actual date itself. The NameColumn property is a text description of the date.

 

After adding the actual date (FullDate), the first error went away.

 

The following error was new – We couldn’t create a Timeline because the date field contains values that are not supported as dates in Excel.

 

 

In the initial stages of loading data, we had source dates that were unknown and we replaced those dates with 01/01/1800 (12/31/2099 for End Dates) or the date would come in as 01/01/1000 from the source. These values do not work in the Timeline. Once I verified that the unknown dates were cleared up, I changed the Date source in the Data Source View of the cube from including these date values:

BEFORE

SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)
  OR (DateKey = - 1)
  OR (DateKey = - 2)

The OR in the WHERE includes a lower (1800-01-01) and upper (2099-12-31) in the Date dimension. These are no longer needed because the data in scrubbed first to exclude or correct the date problems.

AFTER

SELECT DateKey, FullDate, DateName, DayOfWeek, DayNameOfWeek, DayOfMonth, DayOfYear, WeekdayWeekend,
    WeekOfYear, MonthName, MonthOfYear, IsLastDayOfMonth, CalendarQuarter, CalendarYear,
    CalendarYearMonth, CalendarYearQtr, 'Q' + CONVERT(varchar(1), CalendarQuarter) AS CalendarQuarterName,
    DateRange, CONVERT(int, REPLACE(CalendarYearMonth, '-', '')) AS MonthKey,
    RTRIM(LTRIM(MonthName)) + ' ' + CONVERT(varchar(4), CalendarYear) AS MonthYear
FROM dbo.DimDate
WHERE (DateKey BETWEEN 20090101 AND 20141231)

Now, no more error:

 

Nice!!!