SQL Relay – Exciting new UK event! A relay rather than a rally

In the UK, an exciting and innovative event will be held in October, called SQL Relay! The SQL Server User Groups in the UK will be holding 16 User Group events in the space of four days, commencing on Monday 3rd October. 
The buzz of activity culminates on Thursday 6th October, where Itzik Ben-Gan will be presenting at the Relay Finish Line at Microsoft’s office in Cardinal Place in London. If you don’t know who Itzik Ben-Gan is, then you don’t know your SQL heritage and it’s even more reason to go!
We will be presenting at three separate events. Allan Mitchell MVP is your host and presenter for the User Group in Manchester on 3rd October. Allan will be discussing Data Quality and Master Data Management for the evening.
In addition to technical people, I’d strongly recommend Allan’s discussion for anyone within an organisation who has dirty data, and is planning to do something pro-actively about it. When facing a data quality project like this, it is useful to gain advice from experts like Allan so that you can skip some of the learning curve and put a robust plan in place. Allan’s happy to take questions as well so I’d take advantage of the opportunity to ask someone who can help you. So, if you are considering taking a friend to a SQL Server UG, this is one to consider.
I am presenting on 4th October in Leeds, on the topic of Mobile Business Intelligence. I chose to present in Leeds because I wanted to meet some of the active SQL Server Twitterati who are based there, and it was a good opportunity to get ‘face to face’ with some of the people that I’ve been tweeting for some time. 
Allan and I are both presenting at the Cambridge User Group on 5th October. This will be the first meeting of the Cambridge User Group, which has been organised by one of my best friends and DBA superhero Mark Broadbent. Mark’s a Community Contributor Award holder, in addition to keeping me right with his DBA expertise. So we were pleased to be in attendance at this event.
I keep hearing this event being called SQL Rally, which is a completely different – and still wonderful – event. I just thought I’d mention that the two events are different, so that there is no mixup! Both events are amazing in their own ways and if you like one, you may well like the other. It’s worth keeping an eye on both!

SQLPass 24hop Review: Slicers in Reporting Services

Twice a year, SQLPass put on a stellar free ’24 hour hop’ which is aimed at engaging with SQL Server fans all over the world. Basically, SQL experts from all over the world give an hour of their time in order to present you, the SQL Server viewing public, with some SQL goodness, advice and tips and tricks to make your lives easier. Ever since I have got involved with the SQL Server community in the UK and the US, I have learned so much from my peers. I can say that SQLPass and SQLBits have both enriched my life so much in terms of my skills, and also introducing me to some wonderful friends that I’ve made my home in the community. For that, I am grateful. The 24 hop is one of the ways in which we can learn more about SQL!

The SQLPass 24 hop sessions, given in Fall 2011, have been designed to give you a flavour of the precons that will be provisioned as a part of SQLPass Summit.  The 24hop sessions are given by world-class SQL experts. This fall, I listened to a total of 5 different sessions and learned a lot! 
I’d like to thank the following people for giving up their time to educate the wider SQL Community (and me!): Denny Cherry, Stacia Misner, Paul Turley, Rob Farley, Simon Sabin and Peter Myers for their excellent sessions and for giving up their time to help people in the community by provisioning free training. If you are interested, I’d recommend that you take the time to look at the SQLPass preconference training for each of these sessions since you’d be trained by the ‘best of the best’. 

In this blog, I wanted to call out Simon’s Reporting Services session since I thought that his session was particularly outstanding. Simon’s webinar focused on provisioning slicers for Reporting Services. Yes, you read that right! Slicers are available in Excel and in Project Crescent. Every time I show slicers to an Excel user for the first time, the customer is usually impressed by their simplicity and ease of configuration. It’s also possible to ‘theme’ slicers so that they match the rest of the dashboard elements. They also increase usability because they are consistent with Schneiderman’s Visual Information Seeking Mantra: ‘overview, filter and zoom’ methodology with respect to data navigators ‘surfing’ their way through the data.

Basically, Simon used VB and SSRS to produce the slicers in the report. For SSRS people who’d like to know more about what .Net can offer them, this is an excellent route towards learning some .net whilst enhancing report usability in line with the best data visualisation thinking as advised by gurus like Schneiderman. What I especially liked is that Simon paid attention to the .Net requirement from the SSRS writers’ perspective, and was careful to call out any potential pitfalls or mistakes that the SSRS report writer might make. 

The end result was great to see, and Simon produced the report, which you can see on his blog, on less than an hour. It seems to me that his SSRS precon would be especially interesting since you’d obtain lots of useful practical advice, packed into a one-day event, that would really make a difference to writing SSRS reports.

As a business intelligence specialist, I believe firmly that accurate and useful reporting can drive a business from data towards business intelligence and customer intelligence; listening to the stories that the data is telling you. If you can’t ‘hear’ the data because the reports are poor or don’t have the user in mind, then you are still not using the data properly – even if you have lots of it. Quantity of data is not quality of data – it has to be clean and well-presented before it can be used to support the enterprise.

It seems to me that an investment in report writing is fundamentally an investment in business. SSRS precon and training like Simon’s session offers a real ‘value-add’ to the business long term, by supporting the provisioning of reports by report writers to the decision-makers who need the reports to drive the business forward. SSRS can be left behind a bit, in the fanfare over Excel Services, and of course, Project Crescent. Despite the new technologies and new ‘self-service’ business intelligence outlook, there is always a role for straight reporting in running a business knowledgeably and accurately, based on the data. 

If you’re interested in the SQLPass pre-cons, then please do head over to the site and take a look! If you decide to go, please do let me know by tweeting me and hopefully I’ll see you at SQLPass Summit!

DAX PowerPivot Arbitrary Time Intelligence with Fact tables containing Future Data

One thing I love about PowerPivot is the Time Intelligence feature. Aspects of Time Intelligence appear non-intuitive at first, but with practice, it becomes clear. This is all very well if you have nice, clean Time Intelligence requirements, such as Year to Date, Month to Date, and so on. However, what happens if you have arbitrary or variable date handling requirements? What happens if these arbitrary requirements are complicated by the additional factor where your fact table contains future facts in addition to previous facts?

This blog focuses on handling arbitrary time periods where it’s not possible to use more standard Time Intelligence functionality in DAX. It also addresses the issue whereby the ‘rolling’ period needs to exclude future months in the calculation, since you need to know the rolling period from now, not from some future date. This activity has already been covered briefly on PowerPivotPro’s site.  so I would like to call out this post as a useful first step which helped me to get onto the road of arranging my own arbitrary time dimension in my environment, which I subsequently developed and enhanced for the rest of Step One. This helpful advice helped to set us on the road for implementing Time Intelligence in arbitrary environments.

However, this blog takes this idea and makes it into something different since the topic is covered specifically in terms of fact tables that contain future events.

The sequence of activities is as follows:

1. Create a month sequence number that starts at zero, being the earliest month, and increments until the latest month is reached.
2. Create calculated columns that help you to handle the arbitrary time intelligence periods by marking rows appropriately. For example, if the user wants to see the last 13 rolling months, then these rows can be identified and the calculated column can contain a relevant value e.g. ‘Last 13 Months’.
3. Create a calculated measure in order to retrieve the correct value in the calculated columns and display it in the Excel spreadsheet.

Here are the steps to set up the arbitrary time period. We use the AdventureWorks2008R2 database, which is available from CodePlex. The first step requires a bit of discussion since there are different ways of implementing this feature, and we settle on the best one.
We then move onto the second and third steps.

Step 1: Calculated Column to hold the month sequence number

The first step is to create a calculated column which contains a month sequence number that starts at zero, and increments with each month. This appears as follows:

Month Seq No =(FactInternetSales[OrderYear] – MINX(FactInternetSales,FactInternetSales[OrderYear])) * 12   + RELATED(DimDate[MonthNumberOfYear] )

However, what happens if your Fact table contains future data?  The issue here is that your MAX date could be in a future time, so the ’13 month’ period would start from the future date, not the current date.
In order to ensure that we capture the current date rather than future dates, we need to amend the ‘IF’ statement so that it looks at the existing month. For clarity, we can put this into another calculated column so you can see the difference.

This formula will take care of records where the date is in the next year or later:

=IF(  Year(TODAY()) < FactInternetSales[OrderYear], 0,   (FactInternetSales[OrderYear] – MINX(FactInternetSales,FactInternetSales[OrderYear])) * 12   + RELATED(DimDate[MonthNumberOfYear] )  )

The above fragment might help in the case where you wish to exclude records that occur in later years
What about the case where the records might occur later in the current year?
This condition could be added to the formula in the calculated column as follows:
=IF( MONTH(TODAY()) > RELATED(DimDate[MonthNumberOfYear])  && YEAR(TODAY() ) = FactInternetSales[OrderYear]   ||  Year(TODAY()) < FactInternetSales[OrderYear], 0,   (FactInternetSales[OrderYear] – MINX(FactInternetSales,FactInternetSales[OrderYear])) * 12   + RELATED(DimDate[MonthNumberOfYear] )  )

When we use the revised formula above, this means that the month is only assigned a sequence number if the year and month is less than, or equal to, the current month. This means that future records in the fact table are not included in the ‘Rolling 13 month’ since the sequence number is set to zero for these records. This ensures that the maximum value is the current month, which allows us to navigate more clearly.

Step 2: Calculated column to identify the arbitrary Time Intelligence

We can now set up a calculated column that provides arbitrary Time Intelligence information. The following formula takes the maximum Month Sequence Number, and marks the most recent 13 months as “Last 13 Months”. The 13 months previous to this set is marked as “Last 13-26 Months”. Other records are marked as “Not Relevant”. This is calculated in the following formula:

Month_13MonthPeriods =IF(  FactInternetSales[Month Seq No] >=  MAX(FactInternetSales[Month Seq No] ) – 13,  “Last 13 Months” , IF(  ( ( FactInternetSales[Month Seq No]   = MAX( FactInternetSales[Month Seq No]  ) – 26 )   ),  “Last 13-26 Months”, “Not Relevant”  )   )

Step 3: Calculated measure to pick out the arbitrary Time Intelligence

It is possible to set up a calculated measure that will filter out the rows for the last 13 months, and display only that data. Here is an example, that will display the amount if the data is within the time period of Last 24-48 months, otherwise it will return zero value. An example formula could be found here:

=CALCULATE(sum(FactInternetSales[SalesAmount]), filter(FactInternetSales, FactInternetSales[Month_24MonthPeriods] = “Last 24-48 Months” )  )   

I hope that this has been useful for someone! I look forward to your comments. 

Sorting Months from PowerPivot models in Excel Filters and Slicers

Date Sorting by month requires additional steps for Excel Filters and Slicers, and these will be discussed here. The excellent book entitled ‘Practical PowerPivot and DAX Formulas for Excel 2010’ by Tennick suggests one method of fixing the issues around the Excel slicer sorting, which involves an IF statements on an existing Month Number column to obtain the Month number, and sort by that. This blog offers an alternative way of dealing with the sorting issues involved with Excel Slicers. Here is a different approach, which is designed to promote re-use by generating a separate Month Number column from the Date 

Date Sorting by Month for Excel Filters

When Excel retrieves the month from a PowerPivot model, the months are sorted in alphabetical rather than chronological order. 
This can be seen from the following image:

1. Initial Incorrect Date Sorting in Filters

Figure 1 Date Sorting by Month in Alphabetical Order

In order to sort by chronological order, additional steps are required:

  • The user needs to click on the arrow next to Row labels, and select the option ‘Sort A to Z’

This can be seen from the following diagram:

2. Sorting dates in Filters

Figure 2 Sort menu in Excel

The months are now sorted in chronological order. This can be seen from the image below:

3. Correctly sorted dates in Excel Filter

Figure 3 Chronological sequence of months in resulting Pivot 

Date Sorting by Month for Excel Slicers

The ‘Month Number’ attribute may not be able to be used because there is no leading ‘zero’ in order to assist in the sorting process. This may result in strange sorting, if the Month Number is interpreted as a string e.g. 1, 10, 11, 12, 2 and so on.
In order to obtain ‘Month Number’, the following calculated field called ‘Month Number Extract’ was created on the Date Dimension:
=RIGHT(LEFT(‘Date Dimension Query'[Date As Integer], 6), 2)
Therefore, if the date key is in the format YYYYMMDD, then the above calculation takes out the ‘MM’ piece. For example, if the date is 11th February, 2011, then the key looks like: 20110211 and the calculation would take out ‘02’.
Since the calculation already has the leading zero prefixed, there’s no need to add it in again. Also, by putting this field into a calculated column, it is potentially reusable by other formulae if required.
Once the Month Number has been obtained, it is then possible to prefix the Month Name so that it is sorted correctly.  In other words, instead of getting the alphabetical ordering of months, e.g. April, August, December, and so on, the user is presented with a nice chronological ordering of months e.g. January, February, March and so on.
In order to arrange the prefixed month, the calculated column will appear as follows:
=’Date Dimension Query'[Month Number Extract] & “-” & ‘Date Dimension Query'[Policy Inception DateYQMDMonth]
This results in a calculated column which returns month names with prefixed month number orderings as follows:
.. and so on.
When an Excel Pivot table has a Slicer added to it, then the new Slicer appears as follows:

4. Final Slicers

The months are nicely sequenced, but it is unfortunate that the month ordering element is still visible. According to the book entitled ‘Practical PowerPivot and DAX Formulas for Excel 2010’ by Tennick, the Excel slicer sorting issue will be fixed in the next release of PowerPivot.
In the meantime, this workaround will help to ensure that the months are sorted properly in a way that is intuitive to the users.