MS TechEd NA 2012 - Keynote day 1

Yesterday the Microsoft TechEd North America started with the day 1 Keynote, that is,  if you skip the Pre-Con's that were already given on sunday.

I've followed the live stream of the Keynote and have put some points together, you can watch the whole video here: Keynote day 1. The video starts automatically at 1:29:40 with Satya Nadella.

For myself Amir Netz was amazing with his PowerView demo!

@1:29:40

Satya Nadella - President Server and Tools

• Virtualization

• Workload > sharing of resources

• Powershell > automate anything
• 2400 cmdlets

 @1:46:50

Jeff Woolsey - Principal Program Manager

Windows Server Virtualization

• Server 2012 / HyperV, some numbers:
• 320 logical processors per server
• 4 TB per server
• 64 virtual processors per vm
• 64 TB per virtual disk
• 64 nodes, 4k VM's
• IOMeter: 985.000 I/O per sec from 1 virtual machine

• Network Virtualization 

@1:59:50

Satya Nadella

• Video of introduction of  Windows Server 2012

@2:03:20

Mark Russinovich - Windows Azure

• Infrastructure as a service
• Easy creation of new VM's

@2:16:10

Satya Nadella

• Modern datacenter
• Server 2012 / Azure

• Modern apps (lifecycle)
• Personal
• Social (sharing)
• Intelligent
• Tools (VS2012) 

@2:22:00

Scott Guthrie - Corp. VP  Azure App Platform

• VS 2012: ASP.Net MVC 4 project
• Mobile plug-in for testing
• Very funny part @2:31:00
• Integration with TFS to Azure website
• Automative build and run unit-test, when positive deploy to Azure

@2:32:35

Jason Zander - Corp. VP Visual Studio

• Metro style apps
• VS LigthSwitch
• Odata

• Additional support
• jQuery
• iPad emulator

• Agile development
• System Center closely integrated with VS to debug and adjust problems and switch from developer to IT-pro.
• @2:47:45 demo video of ING Direct Australia

@2:49:15

Satya Nadella

• Big Data

@2:53:20

Amir Netz - SQL Server (Power View demo)

Technical Fellow

• Analyse (sentiment of) tweets to predict movie revenues and needed allocation of theaters
• 12.4 mln tweets of movies
• 1st week sales
• 2nd week sales

• Manage brands (awareness of actors)
• Sentiment of brand
• Effect / impact of events on brands (like oscar nominations)
• Small brands have major effect
• Established brands (Brad Pitt) have small effect

@3:04:00

Satya Nadella

• Summarisation & Conclusion

Power Pivot Security Architecture Diagram

This morning (thanks to my colleague Bas) I came across a nice diagram of an end-to-end view of all the related components of PowerPivot (for Excel and for SharePoint) for SQL Server 2008 R2.

Included in this diagram are:

• Service Accounts
• SharePoint Databases
• Security Hardening
• Automatic Data Refresh
• User Identity Flow
• PowerPivot Permissions Levels

The original document (in Microsoft Visio, PDF, or XPS format) can be downloaded from the MS Technet site (link).

Microsoft TechEd Europe 2012

I'm proud to announce I am one of the already 2.5k+ attendees at the TechEd Europe in Amsterdam next month!

I allready put up my agenda at europe.msteched.com, to attend at least some of the sessions of the following speakers:

- Kasper de Jonge

- Marco Russo

- Alberto Ferrari

- Jen Stirrup

See you all at the TechEd!

Performance difference between DISTINCT and GROUP BY

I was wondering if the following two queries differ in performance when used in SQL Server.

select DISTINCT persNrDV, volgnrDienstverband from medewerker

select persNrDV, volgnrDienstverband from medewerker 
GROUP BY persNrDV, volgnrDienstverband

The goal of both of the above queries is to produce a list of distinct employee numbers and employment numbers from the employee table. The first query uses SELECT DISTINCT to accomplish this task, and the second query uses GROUP BY.

I ran these queries with the Execution Plan option turned on, I found that not only the results are identical, but the Execution Plans are also identical. The SQL Server Query Optimizer has the ability to decipher each query, determining what the ultimate results are, and to produce the most efficient Execution Plan possible.

So my conclusion is that there is no performance advantage using one form of the query over the other.

Children and the alphabet

I thought this would be a nice start of the monday morning...

CALCULATE - The Queen of all DAX functions: From Row context to Filter context

This post is an update on AVERAGEX with SUM vs. SUMX with CALCULATE(AVERAGE). With special thanks to Marco and Alberto explaining the topic of Evaluation Contexts so well! :)

A little while ago I wrote about the different outcome of 2 measures, when trying to find a solution for a problem. Back then I didn't know why they had a different outcome. Since I have been attending the SSAS BISM Tabular workshop by Marco and Alberto this week, I couldn't resist to look at the problem again.

Let's start with the 2 measures:

(1)
AVERAGEX(
        FILTER(
              Organisatie,
              Organisatie[Type] = "Woonlocatie"
        ),
        SUM(Productie_OrganisatieGroei[AantalClienten])
)

(2) 

SUMX(

    Productie_OrganisatieGroei,

    CALCULATE(

             AVERAGE( Productie_OrganisatieGroei[AantalClienten]),               

             Organisatie[Type] = "Woonlocatie"

    )

)

To understand the relationships between the tables, there is a 1-to-Many relation between Organisatie and Productie_OrganisatieGroei:

Datamodel

Starting with measure 1, AVERAGEX is an iterator, so it creates a Row context on Productie_OrganisatieGroei. The FILTER on Organisatie however, because it is in a Row context, is not propagated to the many-side. So calculating the SUM of [AantalClienten] doesn't take the filter into account and takes the sum of the whole Organisatie table.

Measure 2 also starts with an iterator (SUMX) which creates a Row context, but then uses CALCULATE inside, which creates a Filter context on that one row. Remember that Filter context does propagate over relationships. Now that we have created a Filter context by using CALCULATE, the filter on Organisatie[Type] does propagate through the relationship and filters the Productie_OrganisatieGroei table. That is why this formula does produce a correct answer on the problem I had.

Once more the pivot table which has measure 1 and 2 in it:

Note: although the project was done with PowerPivot v1, I just imported the xlsx-file in SQL Server Data Tools (Visual Studio / former BIDS) to extract the datamodel for my workbook:

SSAS BISM Tabular Workshop

On april 16 and 17 I attended the BISM Tabular workshop from Marco Russo and Alberto Ferrari (from SQLBI.com with my colleague Bas Kroes. We were guests at Microsoft BV @ Schiphol, the facilities were excellent, although the temperature in the room was a bit too high these days.

BISM Tabular Workshop

It were 2 long days, also because on monday evening we attended the PASS Community event, co-hosted by ADA ICT, where Marco and Alberto talked about: Tabular or Multidimensional?

PASS Community event

I've been working with PowerPivot for Excel for almost a year now, but haven't done very much on SSAS, and nothing with Tabular (in regards to customers) yet, so I learned a lot in the workshop.

Specially the filter and row context in DAX was very good explained by them, it's still a hard topic, especially with 1-to-many and many-to-many relationships.
I can recommend the workshop to everyone who wants to know more on Tabular and SSAS 2012!