I've been at SQLRally Nordic this November, and I've had a fantastic experience. I hope that they will organise this event again! Here are some highlights:


High quality sessions from a variety of speakers on various subjects, including a presence from the SQLCat team such as Alexei Khalyako, Mark Souza (who is also a PASS Director), Thomas Kejser and Tobias Thernström. 


Business Intelligence experts such as Peter Myers, Chris Webb, Peter Myers, Marco Russo and Alberto Ferrari all gave sessions.  We also had a precon from Itzik Ben-Gan and Tim Peterson. Johan Ahlen - chief organiser - also gave a session in Business Intelligence.


It was also great to see long-serving MVPs support the event, so many thanks to Aaron Bertrand and the Quest team, supported by Kevin Kline. I'd also like to thank Johan Ahlen for his hard work in organising the event. I am sure that there were plenty of others who helped, whom I didn't come across - but their hard work was visible because SQLRally Nordic went flawlessly. Well done everyone!


The event wasn't all 'work and no play'! There was a party in the evening, and if you're interested in seeing what the Swedish IT Computing press made of the event, please do visit their site here.


We also had a WIT lunch, and it was great to have this facility where people could network and contribute. We talked on a number of subjects, and our main focus was on how we could individually mentor and support people as they progress through their IT and professional careers. It wasn't a 'pity party' attitude at all! The main focus was to allow people to connect and network if they wished. I'd personally like to thank Simran Jindal and Johan Ahlen for facilitating the 'Women in IT' lunch.


I enjoyed giving my session in data visualisation. There was a very strong presence in business intelligence delegates, and I love meeting other Business Intelligence fanatics in order to share experiences! The Swedish delegates were very friendly, and they made the event very special with their welcome.


As a final note, the event was duly supported by a number of sponsors, and if you'd like to see the list, then you can find the sponsor details here. 

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. 

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:
sinc
=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]  ) - 13)  &&  ( 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. 

The issue is that, although the formula is technically correct, the initial visual feedback to the user shows a red ball. Generally, in the West, red denotes a 'warning' colour, to show a problem; so we have red traffic lights, for example.  A more appropriate notification to the user would be a green icon, which would indicate 'success' or 'go'.  In Denali PowerPivot, this is exactly the improvement that we see, and this can be seen from the same screen in the Denali version. Here is an example below:



Although this seems like a little thing, it is extremely effective. If you've ever held your breath to see if your PowerPivot formula has worked, and felt the sense of relief of realising that the red ball means 'Success' in 2008 R2, then you'll know what I mean! I am hoping that the tick will cater for colour-blind people as feedback if the green colour is an issue, and I look forward to your comments on this issue.

Little things make me happy!

Note that the 'Absolute Value' is set to 1, not 100; and the percentages are specified in the decimals rather than as percentage values. Hopefully users won't get confused, since if the percentages are specified rather than the decimal values, then they might wonder why their KPI value is not working.

If we choose the red-yellow-green 'traffic symbols' then our report appears as follows. If it is hard to read, please do click on the image to go to my flickr blog.




Creating KPIs in PowerPivot is extremely easy to do, and I achieved some impactful results in just a few steps. It didn't require any typing so if you are most comfortable with directly interacting  with the interface to produce the KPI, then this is the tool for you.


X F J S U O
X F J S U O





In this example, I have tried to go with the 'longer length equals higher value' approach, and not used any colour to distinguish the KPI statuses. Ideally, I would like to make these icons go 'left to right' in order to facilitate comparison between the Years or Row Labels. I would also be able to choose red-blue colours to distinguish between statuses properly. Let's see what happens!

In my next post, I will be covering more new PowerPivot features in Denali. In the meantime, I look forward to your comments.

In the Scots language, a 'blether' is someone who likes the sound of their own voice. I will be speaking at four events in July, so I'm sure I qualify as a blether! In case anyone's interested, here are the details of the events:

I will be speaking at the Tableau UK User Group on 5th July. Stephen Few is presenting, and he's the best presenter I have ever seen (and I've attended lots of presentations). I've volunteered to go straight after Stephen which is brave since he'll have set the standard for presentations extremely high, and I won't compare! To Register, visit our Tableau User Group site.

I am speaking at a SQLPass Performance Webinar on the visualisation of the data contained in the Microsoft Management Data Warehouse using SQL Server Reporting Services 2008 R2. This takes place on July 5th, after the Tableau event, and the registration details can be found here. This event sponsored by Confio

I will be speaking on Data Visualisation and SQL Server Reporting Services at the South Wales SQL Server UG July 14th Cardiff. To get more details, and to register, please visit the SQL Server User Group site.

On 21st July, I am also speaking at the Manchester SQL Server User Group but the details aren't up on the site yet. The topic will be the data visualisation of the data in the Management Data Warehouse again, and the link to the site is here

Wish me luck!
Jen x