WesleyB's Blog

PowerPivot on Lync – small update

2012-04-23T19:28:12Z

Just a small heads up that I changed the Lync PowerPivot file to make it scale better. In order to do so I have removed several unique columns from the import and moved some calculations to the query side instead of the client side.

I have placed the new file here and the original file is still available here.

If you are interested in having this as a Tabular Model (for SQL Server Analysis Services) please let me know. You can easily do this yourself if you like of course by importing the model in the SQL Server Data Tools.

Extending your Lync monitoring data using PowerPivot and Power View

2012-03-09T13:26:58Z

Cross posting from Ilse’s Blog.

During the last Techdays here in Belgium, I have had the privilege of being able to present a session on Lync. I had chosen as a topic for that session “Deploying Lync: Notes from the Field, and more”, where I talked about the top ten questions I run into when talking to customers about deploying Lync in their environment. I tried to answer the following questions:

Lync Online – Lync On Premises: where do we go?
Lync is a Puzzle, really?
Lync and NAT: Yes or No?
Whats’s up with the Picture in Lync?
Are there any tools available to help me design hardware and bandwidth requirements for Lync?

And then the final topic I touched upon was monitoring, and that’s when I was happy to introduce one of my Technology Advisor colleagues, Wesley Backelant, who knows almost everything about PowerPivot and Power View and SQL and BI to the IT Pro Lync audience And we promised to blog about it, so here it is, the details on the web

Monitoring Server in Lync, why would you use it?

In short, the Monitoring Server role in Lync is optional, and can be added after finishing the deployment of all the other server roles within Lync. The Monitoring Server enables you to capture both call detail records (CDR) for Enterprise Voice and Audio/Video conferences and data collections about your Audio/Video Quality of Experience, also incorporating data on file transfers, application sharing, and remote assistance. It does NOT archive the conversations itself (if you want to keep the IM conversations in your organization, you should consider deploying the Archiving server role).

Some typical questions on the Monitoring Server role in Lync:

Can the Monitoring server role be combined with any other Lync Server role?
Yes, as taken from TechNet:

“A Monitoring Server can be collocated with an Archiving Server, with a SQL Server store of an Enterprise Edition Front End pool, or with a file store of a Front End pool. The Monitoring Server requires a database, but the database can be collocated on the Monitoring Server, with the database server for the Archiving Server, or on the Back End Server of an Enterprise Edition Front End pool. A Monitoring Server cannot be collocated with a Standard Edition server in a production environment.”

Can you collocate the monitoring database with a Lync back-end database?
The answer is yes, but the back-end database needs to reside in a separate SQL instance.
Do you require SQL Reporting Server?
No, but if you do you will be able to take advantage of the built-in Monitor Server Reports, which you can customize as wanted.
What’s the advantage of using the Monitoring Server role with Microsoft System Center Operations Manager?
By installing the Microsoft System Center Operations Manager, which uses the Monitoring CDR and QoE data, you can enable the generation of near real-time alerts showing the health of call reliability and media quality, and define actions upon these.
Do I need a monitoring server per Lync Pool, and even per Site?
No, as taken from the same Technet Article linked to before:
“Multiple central sites can also share any of the following that you deploy in one central site:
- Archiving Server
- Monitoring Server
- Stand-alone Mediation Server or pool
- Edge Server or Edge pool”
Any documentation available on how to deploy the Monitoring Server?
Yes: Microsoft Lync Server 2010 Monitoring Deployment Guide, available for download here: http://www.microsoft.com/download/en/details.aspx?id=8207, which described the different steps envolved:
- Installing Message Queuing for Monitoring
- Installing SQL Server (with Reporting Services)
- Adding a Monitoring Server to the Topology using Topology Builder
- Installing the Monitoring Server
- Deploying Monitoring Server Reports (if wanted)
- Configuring Monitoring Server Settings

Is there a document that described how to use the Monitoring Server Reports in a Lync Server 2010 deployment?
Sure there is, there is Microsoft Lync Server 2010: Using Monitoring Server Reports whitepaper available for download here: http://www.microsoft.com/download/en/details.aspx?id=890

Built-In Reports examples:

What does the Dashboard give me?

And yes you can export the reports, and you can customize them…but if you know have the feeling as in “this is not exactly what I want to show my manager”, “I would like to have some more fancy reports” “I’m looking for an easier way to use the gathered data”…then please read along, because now it’s time to dive into PowerPivot and Power View!

Let me start by saying that the solution provided here is obviously for demo purposes. It is not a perfect solution and we are not responsible for the production use of this file.

Technologies Used

First a couple of words on the technology used in this solution.

PowerPivot is a powerful data mashup and data exploration tool based on xVelocity in-memory technologies providing unmatched analytical performance to process billions of rows at the speed of thought. It comes with a client part, a free add-in for Excel 2010, which allows you to import and combine data in an easy way. We used Excel in this particular case to get information from the Lync monitoring databases and create some interesting calculations. In order to get your hands on PowerPivot please visit the official website.

The second part of the solution uses SharePoint and Power View to share and visualize the information in a very interactive way. Sharing PowerPivot files in a scalable and controlled way can be achieved by using the Excel Services functionality of SharePoint. With Excel Services you have the ability to share Excel files to a broad audience without the need to install Excel 2010 (and in this case PowerPivot) on every machine. If you need more information about Excel Services please read this. When integrating SQL Server Reporting Services 2012 in SharePoint 2010, you will get a new powerful data visualization tool called Power View. Power View is really all about the interactive and stunning experience. Understanding Power View is actually the easiest by just watching this demo.

Now that you know the technology part let’s dive into some of the details on how we created this. Of course we started by interviewing customers to understand what they would like to get out of the information available. Some of the topics that came up were duration distribution, average response time, number of sessions by type, uptake of Lync and a lot more interesting stuff!

Most of the information you need is available in the LcsCDR which is fully documented here. The leading table is SessionDetails so you better figure out how this table is structured. One additional set of data you may need is a date table, I have included a hidden sheet with dates but you could also use the DateStream feed from our Windows Azure Marketplace. A couple of other tables we used are SIP, Users, ClientVersions and UriTypes

The other database we used was rtcab1 to get display names. This is a bit less straightforward to retrieve but here is how you can extract it:

SELECT avname.Value as 'DisplayName', avuri.Value as 'UserUri'
FROM AbUserEntry u
INNER JOIN AbAttributeValue avname ON avname.UserId = u.UserId AND avname.AttrId = 3
INNER JOIN AbAttributeValue avuri ON avuri.UserId = u.UserId AND avuri.AttrId = 8

The entire model looks like this

Now that we have all the information in place it is time to visualize some of it. A couple examples of the things we built (remember it was built on a test database too so don’t freak out by the unanswered call rate ).

Taking this PowerPivot file to the server to share it with a broad audience is a matter of saving it on an Excel Services enabled SharePoint Server. This would give you the following view

But we promised a more compelling and interactive way of visualizing this information and that is exactly what you will do with Power View. Power View can be used on PowerPivot files published on SharePoint or on solutions deployed to SQL Server Analysis Services. As a little side note, you can import the PowerPivot model or build this model from scratch in SQL Server Data Tools to make it a real server-side solution running on SQL Server Analysis Services.

The most popular view however is the adoption rate of Lync in the organization, this can be achieved by using the Scatter Chart functionality in Power View.

Please find the PowerPivot file here. In order to get data from your enviroment go to the PowerPivot window, select “Design” and “Existing Connection”. Edit both connections to point to your SQL Server and when done use “Refresh All”. In the Excel Windows go to Data" and select “Refresh All” and you are done. If you want to use Power View just upload the file to a SharePoint where this feature is enabled.

There is a LOT more you can get out of the Lync databases, so we hope this sparks a whole wave of creativity! Ilse’s favorite for example is the QoEMetric database to see the call quality for different users and devices. If I can find some time I’ll see if we can publish this PowerPivot file too.

Ilse & Wesley

Marketer of the Year

2011-10-18T14:46:08Z

I am doing a type of post I usually don’t really do. My blog is devoted to technical information but I really wanted to make an exception this time, because I think it is worth it.

David Merzel is one of my colleagues, he is the country manager of Microsoft BeLux responsible for Xbox and hardware products. David has done quite some extraordinary work around Xbox and Kinect (you all know Kinect so that proves it ). The gaming market is a very challenging one with some very strong and established players, so it is definitely not an easy job to become one of the most relevant players in that market. Because of this for the first time in the history of “Marketer of the Year”, there is a nomination for someone working for an IT company!

If you believe me please vote for him at http://trends.knack.be/economie/game-1194827758183.htm, it only takes a minute of your time. In case you don’t believe me feel free to review the information about David on the same website and then vote for him

Hoping for your support!

Restore improvements in SQL Server Denali CTP3 Management Studio

2011-07-18T13:27:16Z

SQL Server Denali CTP3 comes with a lot of improvements in all areas but some areas are more visible than others. Some of the improvements in SQL Server Management Studio in the restore area are quite useful however. Lets look at some of these improvements.

An interesting addition is the Timeline button

Clicking the Timeline button will open the following window:

This will let you configure a point-in-time restore in a visual way which will greatly simplify this task.
You can find more information about the Timeline at http://msdn.microsoft.com/en-us/library/ff963619(v=SQL.110).aspx.

A small but yet useful change is the ability to easily relocate database files. This was possible in previous version but the the interface is a lot more intuitive now.

Another way of limiting the time to do restores in case of corruption are Page Restores. Imagine you run into a situation where a certain physical error occurs corrupting just a page in the database. Since one of the most important aspects of a database is that you can guarantee consistency, having a corrupt page is a pretty significant issue to say the least. A new option in the interface will also help you determine which pages have been corrupted in your database. Detecting this can take some time if you have a large database of course (as mentioned below it will launch DBCC CHECKDB PHYSICAL_ONLY).

Time to corrupt a database and see if the interface reacts as expected. There is a nice tutorial on how you can corrupt your database at http://sqlserverpedia.com/blog/sql-server-bloggers/corrupting-databases-for-dummies-hex-editor-edition/. Obviously intentionally corrupting your database is not for production purposes.

This will add the following to the T-SQL script:

RESTORE DATABASE [AdventureWorksDWDenali] PAGE='1:1688' FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorksDWDenali.bak' WITH FILE = 2, NORECOVERY, NOUNLOAD, STATS = 5

These are just a couple of small changes in the UI that can make a world of difference when it comes to recovering your database.

SQL Server Denali CTP3 – Lots of interesting resources

2011-07-13T20:48:37Z

Yesterday we have announced the availability of SQL Server Code-named Denali. Obviously exciting times for everyone in the SQL Server business. As a result there is so much information being published on blogs and other locations that it might be hard to follow. So here I will try to recap some of the most interesting links with regards to SQL Server Denali CTP3.

First of all there is the ‘official’ announcement of course at http://bit.ly/rizjXQ.

If you are looking for the download location please refer to http://bit.ly/oj7hZW. The download for the SQL Server Denali CTP3 Feature Pack is available at http://bit.ly/nX0HxB. The SQL Server Denali CTP3 Resource Center is located at http://bit.ly/nRWECd.

Now of course you are all interested in the ‘What’s New’ at http://bit.ly/oT8lcF.

With SQL Server Denali CTP3 there are also a couple of other interesting products such as PowerPivot v2 CTP3 and Project Juneau CTP3 (development tools for SQL Server). You can find the PowerPivot download at http://bit.ly/oObujA and Project Juneau at http://bit.ly/n2qmwC.

The new SQL Server Denali samples can be downloaded from http://bit.ly/pCtcAK and http://bit.ly/qDclqf.

A complete Product Guide with tons of resources is available at http://bit.ly/nq41Rq.

Some interesting articles can be found at:

Recovery Advisor: http://bit.ly/qhtfWI
LocalDB: http://bit.ly/qhcGDb
Windows 2008 R2 Server Core: http://bit.ly/pnSNN6
Tabular Projects: http://bit.ly/qM5T3a
Project Crescent Overview: http://bit.ly/quL0yI
Project Crescent Demo: http://bit.ly/oR0wr1
Getting up and running with BI: http://bit.ly/qPCaHt
Getting Started With Project Crescent: http://bit.ly/pQRlh9
What’s new in SSIS: http://bit.ly/nyzjgG
Deploying to the SSIS Catalog: http://bit.ly/oT53al
Configure SSIS Project with Parameters: http://bit.ly/qfMn2V
DQS Cleansing Transform: http://bit.ly/rhRPEZ
Reference Data Services in DQS: http://bit.ly/opuPfI

There are tons of other interesting links out there and I am sure we will have many many more in the coming days and weeks, so keep your eyes open!
I'll do my best to add links when appropriate. Make sure you follow me on Twitter (@WesleyBackelant) to get the latest information.

Have fun and please submit any feedback on http://connect.microsoft.com!

TechDays 2011: Early Bird Pricing Extended Until 28 February 2011

2011-01-26T15:53:51Z

Register now with extended Early Bird pricing
Discover new technologies and increase your knowledge about current technologies on April 26, 27 and 28 at Metropolis, Antwerp. Do you want to attend at a discounted price? We have good news for you: the Early Bird pricing period has been extended until February 28, 2011. Register now and save up to €125!

Why you should not miss this year's TechDays
TechDays 2011 delivers more than 80 technical sessions presented by top national and international speakers. The 3-day conference covers overview to very technical sessions on .NET language, framework and tools evolutions including C# and Visual Basic futures, WCF, parallel programming, development practices, OData, VSTS, ...; new web and client development techniques and technologies like ASP.NET MVC 3, HTML5, MVVM, Silverlight 5, Windows Phone 7, XNA, etc; and of course the newest stuff covering the Cloud with the Windows Azure Platform.

Further you can expect how to learn to write PowerShell scripts, how to lockdown your pc infrastructure with Applocker and Group Policies, how to build high available virtualization infrastructure, what VDI is and what the impact will be on your infrastructure. Finally you will also discover the vnext versions of some of the System Center product and the future of SQL Server.

Deep dives
On April 28th we are organizing different deep dives. Deep dives are full day post-conference workshops delivered by industry experts.
This year's Deep Dives are focused on Application Lifecycle Management, the Cloud (Windows Azure Platform), Direct Access and Virtual Desktop Infrastructure.
For the ALM Deep Dive we can also confirm that some of the speakers are Visual Studio ALM Rangers (check out this site if you don't know what the Rangers are).

Top speakers
Even more top speakers have been confirmed, out of which we can give you a few names including Vittorio Bertocci, Glenn Block, Brian Keller, Rob Miles, Ingo Rammer, Clemens Vasters, Don Jones, Dan Holme, Jeremy Moskowitz, Dandy Weyn, John Craddock and many more…
Keep an eye on the TechDays website as we will continuously announcing confirmed speakers and sessions.

Using SharePoint Content Types as Report Templates

2010-12-17T13:37:00Z

Last week a customer asked us if he could help users to start a PowerPivot file from an existing template. For this specific environment some analysis was to be done on content stored in SharePoint Lists. Using SharePoint 2010 and PowerPivot enables you to subscribe to these lists as Data Feeds, which is a nice way of getting this data automatically out of SharePoint. Several lists had to be combined and obviously they didn’t want users to manually subscribe to these lists each time a new analysis had to be done.

To give the users a starting point where all necessary subscriptions were already defined, we used typical SharePoint functionality to attach “content types” to a document library. Content types can be based on existing documents which makes it a nice way to define templates.

Content types are described in the following way in the SharePoint documentation:
“A content type is a reusable collection of metadata (columns), workflow, behavior, and other settings for a category of items or documents in a Microsoft SharePoint 2010 list or document library. Content types enable you to manage the settings for a category of information in a centralized, reusable way.

For example, imagine a business situation in which you have three different types of documents: expense reports, purchase orders, and invoices. All three types of documents have some characteristics in common; for one thing, they are all financial documents and contain data with values in currency. Yet each type of document has its own data requirements, its own document template, and its own workflow. One solution to this business problem is to create four content types. The first content type, Financial Document, could encapsulate data requirements that are common to all financial documents in the organization. The remaining three, Expense Report, Purchase Order, and Invoice, could inherit common elements from Financial Document. In addition, they could define characteristics that are unique to each type, such as a particular set of metadata, a document template to be used in creating a new item, and a specific workflow for processing an item.”

So how exactly do you allow users to create a PowerPivot file with preconfigured content from SharePoint?
As a side note, this can be practically done for any file type and is not tied to the requirement to subscribe to SharePoint Lists.

There are a couple of easy steps to follow in order to get these content types linked to your document library (eg. PowerPivot Gallery). The first step is obviously to create your required template. If you want to learn more about subscribing to SharePoint lists please refer to this post by PowerPivotGeek. I assume you know how Excel / PowerPivot works so I’ll jump to the SharePoint part immediately.

The first step in SharePoint is to define this template as a new content type (Site Settings – Site Content Types – Create)

After you created the content type (by selecting OK) you will be redirected to the settings of the newly created type. To upload an existing Excel file go to Advanced Settings and browse for the file in the “Upload a new document template” option.

Now it is time to prepare the document library for additional content types. First you will have to allow management of content types by going to the settings of the document library on which you want to use this content type (Library Tab – Library Settings – Advanced Settings):

A new section is now added to the settings screen with content type options:

Select “Add from existing site content types” to attach your new content type to the document list. Also notice that you can configure this new content type to be the default and the original can be removed if necessary. When you have completed this step the content type list should look like this:

By following these easy steps users now have the ability to create a new PowerPivot file based on an existing template. All they have to do is select Documents – New Document and the templates are available from there.

More information on Content Types can be found at http://msdn.microsoft.com/en-us/library/ms472236.aspx.

A special thanks to Jan Tielens (blog | twitter) for the review.

New Windows Azure Platform Features Available Today

2010-12-15T06:59:51Z

Building out an infrastructure that supports your web service or application can be expensive, complicated and time consuming. Whether you need to forecast the highest possible demand, build out the network to support your peak times, getting the right servers in place at the right time or managing and maintaining the systems, these actions require time and money to do.

The Windows Azure platform is a flexible cloud computing platform that lets you focus on solving business problems and addressing customer needs instead of building that infrastructure to have your business running on. Furthermore with the platform, there is no need to invest upfront on expensive infrastructure all together. Pay only for what you use, scale up when you need capacity and pull it back when you don’t, all this power is provided by the Windows Azure Platform at your fingertips.

During PDC 2010 we announced much new functionality to become available at the end of this calendar year. Some of these new functionalities are available as of today:

Full Administrative Access
Full IIS Access
Remote Desktop
Windows Azure Connect
VM Role

Reading about cloud computing is one thing, experimenting and trying it out is a completely different thing. As such Microsoft provides you different ways allowing you exploring these new functionalities while making cloud computing and Windows Azure in particular more accessible to you and your business. All this and much more can be done in three easy steps.

Setup a Free Account

You will need an account and subscription to access the Windows Azure Portal allowing you to deploy your applications. Microsoft offers two choices for having a free subscription:

Windows Azure Introductory Special: This is a new offer specially made for you. Limited of one per customer and includes a base amount of the Windows Azure platform services with no monthly commitment and free of charge.

Navigate to the Microsoft Online Services Customer Portal.
Select the country you live in and press continue.
Right click on the sign in link to sign in the portal.
Click on the View Service Details link under the Windows Azure Platform section.
Locate the Windows Azure Platform Introductory Special offer and click on buy.
Provide a name for the subscription.
Check the Rate Plan check box below and click next
Enter the Billing information and click next
Check the Agreement box and click purchase.

Windows Azure for MSDN Subscribers: This is a special offer only available to MSDN subscribers providing a total of 16-month free access to the Windows Azure Platform:

Sign in to the Microsoft Online Services Customer Portal.
Click on the Subscriptions tab and find the subscription called “Windows Azure Platform MSDN Premium”.
Under the Actions section, make sure one of the options is “Opt out of auto renew”. This ensures your benefits will extend automatically. If you see “Opt in to auto renew” instead, select it and click Go to ensure your benefits continue for another 8 months.
After your first 8 months of benefits have elapsed (you can check your start date by hovering over the “More…” link under “Windows Azure Platform MSDN Premium” on this same page), you will need to come back to this page and choose “Opt out of auto renew” so that your account will close at the end of the 16-month introductory benefit period. If you keep this account active after 16 months, all usage will be charged at the normal “consumption” rates.

Note: You can have both offers active at the same time providing even more free access to the Windows Azure Platform and related new functionalities.

Download the Required Tools

Following tools are required to access the news features on the Windows Azure Platform:

Microsoft Visual Studio 2010 or the free Express Edition
Microsoft SQL Server 2008 R2 or a the free Express Edition
Microsoft .NET Framework 4.0
Windows Azure Platform SDK
Windows Azure Tools for Visual Studio
Windows Azure Platform Training Kit

Use and Experience the New Features

As part of the release of the new features, new detailed walkthroughs are being made available in learning how to use these new features:

Introduction to Windows Azure: In this walkthrough, you explore the basic elements of a Windows Azure service by creating a simple application that demonstrates many features of the Windows Azure platform, including web and worker roles, blob storage, table storage, and queues.
Deploying Applications in Windows Azure: In this walkthrough, you learn how to deploy your first application in Windows Azure by showing the steps required for provisioning the required components in the Windows Azure Developer Portal, uploading the service package, and configuring the service.
Virtual Machine Role: Windows Azure Virtual Machine Roles allow you to run a customized instance of Windows Server 2008 R2 in Windows Azure, making it easier to move applications to the cloud. In this walkthrough, you explore Virtual Machine roles and you learn how to create custom OS images that you deploy to Windows Azure.

What’s New in SharePoint 2010 for BI?

2010-10-01T08:06:45Z

I did a post on the new features in Excel / Excel Services 2010 from a BI point-of-view but notice that people also want to know this for SharePoint 2010. So apart from the Excel Services improvements which are mentioned here let me try to give you an overview of the other additions. to SharePoint 2010

If you feel like decorating your wall with something nice you can find a poster with the BI services in SharePoint 2010 at http://blogs.msdn.com/b/performancepoint/archive/2009/10/30/new-business-intelligence-services-in-sharepoint-server-2010.aspx.

As you know Visio has been an interesting tool to visualize many things and even with connectivity to backend information. Just like Excel with Excel Services we have extended Visio to come with a Visio Services capability. More information Visio Services can be found at http://blogs.msdn.com/b/visio/archive/2009/10/30/introducing-visio-services.aspx

Of course there are some important changes to PerformancePoint Server too, it is now known as PerformancePoint Services and really is part of SharePoint. This means no more separate installation required, it’s just there like Excel Services and all other SharePoint Services. So what exactly has changed in PerformancePoint Services? My friends from the PerformancePoint team have made an excellent blog post on the changes at http://blogs.msdn.com/b/performancepoint/archive/2009/11/05/new-features-in-performancepoint-services-2010.aspx. I know it is mentioned in the previous post but you should definitely look at the new “Decomposition Tree” at http://blogs.msdn.com/b/performancepoint/archive/2009/11/19/decomposition-tree-contribution-analysis-presentation.aspx.

We have built-in support for upgrading PerformancePoint Server 2007 to PerformancePoint Services 2010. An explanation of the options can be found at http://blogs.msdn.com/b/performancepoint/archive/2010/02/25/upgrading-performancepoint-server-2007-to-pps-2010.aspx.

Additionally we also improved the install experience of Reporting Services in SharePoint integrated mode and it is the reporting platform for Access Services. Please check http://blogs.msdn.com/b/robertbruckner/archive/2010/04/25/reporting-services-in-sharepoint-2010-access-services.aspx for more information on this.

First Microsoft Demo Day (June 2nd 2010) will keep its promise of offering a thrilling experience

2010-05-18T11:54:00Z

Microsoft Belgium and Luxembourg proudly announces its first Microsoft Demo Day. Please join us on Wednesday, 2^nd of June, 2010 at Microsoft Belux for a day packed with exclusive demo sessions on a wealth of Microsoft offerings.

Forget about lengthy PowerPoint presentations – this is a No-Slides-Allowed Event. Instead, you will experience a series of interactive live demonstrations and no-nonsense hands-on sessions that will leave you longing for more stunning discoveries.

Our Sales & Marketing Group and Consumer & Online Business Unit joined efforts to broaden your view on Microsoft’s latest offerings. This is what you can expect at the Microsoft Demo Day, among many other things:

During the opening keynote, Ludo Wijckmans, Microsoft’s Director Enterprise and Partner Group, will reveal one of his favorite Microsoft-internal solutions. Not to be missed under any circumstances!
Microsoft Advertising solutions built on the MSN and Windows Live platform.
A live replay of the world-record ETL (extract-transform-load of data), powered by SQL Server.
The most shiny SharePoint solutions to improve your organizational effectiveness.
Exciting new Microsoft hardware innovations.
How to test software like a professional using Visual Studio 2010.
How to authenticate to Windows 7 using your Belgian eID card.
What are search-driven websites powered by FAST technology.
The only exception to the no-slides-allowed rule - a snappy demo of the cool PowerPoint 2010 features.
The wonderful world of Exchange Management Shell
Close look at BPOS-S (Exchange Online, SharePoint Online)
RBAC, say what?
…

More details available soon.

Throughout the Microsoft Demo Day, a team of experienced subject matter experts will be at your service, dealing with all your questions, remarks and suggestions.

Make sure to register here. Don’t delay, since the number of guests at the Microsoft Demo Day is limited.

Hope to see you there!
A thank you to Ilse for letting me shamelessly copy her post

Can I upgrade my SQL Server 2008 R2 Trial to a full version?

2010-05-03T16:21:04Z

Now that the SQL Server 2008 R2 trial is available for download at http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx, many people want to know whether the trial version can be upgraded to a full version once you have a key. The answer is: yes! It is actually very easy and can be done in two ways, through the GUI or through command prompt.

If you are a GUI person just launch the SQL Server setup and go to the “Maintenance Tab”, you will see the “Edition Upgrade” option there. Follow a couple of steps in the wizard, provide the key and you are all set.

If you are more a command line type of person you can launch setup.exe with the required parameters.
Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=<MSSQLSERVER or instancename> /PID=<PID key for new edition>" /IACCEPTSQLSERVERLICENSETERMS

What’s new in Excel 2010 for BI?

2010-04-28T10:10:32Z

A very common question I get is “What is actually new in Excel 2010 for BI?”. As you all know Excel is a wonderful BI client and many reports eventually end up in Excel. As a result we obviously invest a lot in the BI capabilities of Excel and Excel 2010 is not an exception.

The first improvement and by far the most popular one is probably PowerPivot for Excel. PowerPivot is our new offering in the Self-Service BI space that allows you to import and combine data from different data sources, create your own calculations on them and eventually share them through PowerPivot for SharePoint.

Excel 2010 also introduces the concept of “slicers”, slicers offer you a visual way of filtering data in Excel and the best part is that they also filter out other slicers if the combination is not possible. A common misconception is that they only work on PowerPivot data but actually they work on all PivotTables including those against Analysis Services cubes.

There are many other cool improvements in Excel 2010 for BI but instead of going through them again I guess it is better to hear it from the people that developed Excel. So here is a list of posts the Excel team did on the Excel 2010 improvements. Should I have missed any please feel free to let me know.

Slicers: http://blogs.msdn.com/excel/archive/2009/09/23/easy-and-even-fun-data-exploration-introducing-excel-2010-slicers.aspx

What If: http://blogs.msdn.com/excel/archive/2009/10/12/excel-2010-pivottable-what-if-analysis.aspx

PivotTable: http://blogs.msdn.com/excel/archive/2009/10/15/a-few-more-pivottable-improvements-in-excel-2010.aspx

PivotChart: http://blogs.msdn.com/excel/archive/2009/08/25/more-charting-enhancements-in-excel-2010.aspx

Search Filter: http://blogs.msdn.com/excel/archive/2009/10/01/excel-2010-new-search-filter.aspx

Data Bars: http://blogs.msdn.com/excel/archive/2009/08/07/data-bar-improvements-in-excel-2010.aspx

Icon Sets: http://blogs.msdn.com/excel/archive/2009/08/05/icon-set-improvements-in-excel-2010.aspx

Sparklines: http://blogs.msdn.com/excel/archive/2009/07/17/sparklines-in-excel.aspx

Named Sets: http://blogs.msdn.com/excel/archive/2009/10/05/pivottable-named-sets-in-excel-2010.aspx

In order to complete the story a view on the new capabilities of Excel Services 2010 – which is a key component in the complete BI story – is also interesting. I do suggest you look at the REST option and JSOM in Excel Services as they are pretty exciting improvements.

Overview: http://blogs.msdn.com/excel/archive/2009/11/03/excel-services-2010-overview.aspx

Feature support: http://blogs.msdn.com/excel/archive/2009/11/19/excel-services-in-sharepoint-2010-feature-support.aspx

REST: http://blogs.msdn.com/excel/archive/2009/11/04/simple-access-to-spreadsheet-data-using-the-excel-services-2010-rest-api.aspx

JSOM: http://blogs.msdn.com/excel/archive/2009/11/30/introducing-the-javascript-object-model-for-excel-services-in-sharepoint-2010.aspx

ps. This is my first post using Windows Live Writer which is really a great tool for blogging. A thank you to Ilse Van Criekinge for showing me the power of Live Writer.

Twitter for the SQL Server professional

2010-02-11T11:16:00Z

Twitter as it is described on their website: "Twitter is a real-time information network powered by people all around the world that lets you share and discover what’s happening now." If you want to know more about Twitter check out http://twitter.com/about.

Twitter like any social network used for a lot of things, many of which are not really useful for everyone. You would be amazed however how many cool conversations on SQL Server take place on Twitter. Not only is it a super-fast way to get help (use the #sqlhelp tag) but it is a great source of information on anything that is SQL Server related (if you follow the correct people of course). Several people from Microsoft such as myself but also really meaningful people like @powerpivotpro, @dennylee, @buckwoody, @bobwardms and many more are active on Twitter ;-) Since MVPs are real community builders you can imagine that a lot of them are also present on Twitter like @PaulRandal, @KimberlyLTripp, @mrdenny, @frederikvdp, ...

If you do decide to start using Twitter you can find me at http://www.twitter.com/WesleyBackelant. Seesmic has some interesting clients at http://seesmic.com/ like Seesmic for Windows & Seesmic Look.

A quick start to find people that are interested in SQL Server can be found on WeFollow at http://wefollow.com/twitter/sqlserver.

See you soon!

PowerPivotFAQ

2010-01-11T13:46:00Z

One of my colleagues, Rob Collie, has created a new FAQ for PowerPivot @ http://powerpivotfaq.com/.
We will try to answer as much of the questions on PowerPivot as possible. This can only be achieved because of the great volunteers who moderate this list of course.

More information can be found @ http://powerpivotpro.com/2010/01/01/the-great-powerpivot-faq/.

This also means that if you have a question on PowerPivot you are free to let us know. You can comment on the original post or send me a message through the contact form on this blog.

Happy PowerPivoting ;-)

Project Gemini aka PowerPivot for Excel

2009-10-20T07:23:00Z

Yesterday the official name for Project Gemini was announced during the keynote of the SharePoint Conference, as of now Project Gemini will be known as PowerPivot (for Excel).
Personally I feel this name is very well chosen since it really expresses what Gemini is about, in the end it is a PivotTable (or Chart) on steroids. Of course there is a lot more happening behind the scenes but that is exactly what you should not be worrying about, the 'tricks' behind the scenes are not what Gemini is about, it is about giving flexibility to the end user to get to information and improve his insight. But before all the end users in the world start cheering and partying, remember that IT still has control over the sources and even more insight in what you are doing than before ;-)

And of course, with a new name comes a new website: http://www.powerpivot.com.
I hope you are all looking forward to the next public CTP of SQL Server 2008 R2 including the new PowerPivot add-in.

The hardest part for me is always to stop using the name Gemini, we have been talking about Gemini for over a year internally which makes it kind of a habit :-)

SQL Server Day 2009

2009-10-10T08:27:00Z

The Belgian SQL Server User Group is organizing the second SQL Server Day on the 3rd of December. Last year about 200 people gathered for this event and of course we hope to have even more this year!
Please visit http://www.sqlserverday.be for more information and registration (coming soon).

There will be in-depth sessions on SQL Server 2008 as well as a glimpse of the future with SQL Server 2008 R2, Project Gemini and more! Currently there are 2 tracks, one for the DBA and one for the Business Intelligence people. Obviously there will be some great speakers like Dirk Gubbels, Henk van der Valk, Chris Webb, ...

Hope to see you there!

SQL Server 2008 R2 CTP2 Available for download

2009-08-10T18:05:00Z

We are very pleased to announce the first public CTP (Community Technology Preview) of Microsoft SQL Server 2008 R2 for MSDN and TechNet subscribers. Availability for non-subscribers is planned for August 12th.

This is the first public CTP so it is not feature complete but it sure has enough exciting content for you to explore.
Some of the features that are in this build are:
- Application & Multi-Server Management
- Report Builder 3.0
- Scale Up with support for up to 256 logical processors

The Data Platform Insiders blog has a detailed post with all information regarding this CTP.

Please feel free to download and start using the CTP and help us deliver yet another great product by providing your much appreciated feedback.

Cumulative update package 3 for SQL Server 2008 SP1

2009-07-22T09:48:00Z

The third cumulative update package for SQL Server 2008 SP1 is now available @ http://support.microsoft.com/kb/971491.
This will take you to build 10.00.2723.

People using the auditing function will probably be interested in the following fix http://support.microsoft.com/kb/967552.

Data Compression and Heaps

2009-06-17T11:14:00Z

I think we can all agree that Data Compression is a great new functionality in SQL Server 2008. I know many of you have been experimenting with it but there was one little catch I wanted you to be aware of.

When you apply page compression to a table the pages get compressed when they are full but there is an exception as you would have expected. When you have a heap the newly allocated pages are NOT compressed until you rebuild the table (or remove and reapply compression or add and remove a clustered index) unless you are using BULK INSERT.

I have added a test script so you can see it with your own eyes.

First the clustered table:

SET NOCOUNT ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myClusteredTable]') AND type in (N'U'))
DROP TABLE [dbo].[myClusteredTable]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[myClusteredTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TheDate] [date] NOT NULL,
[TheTime] [time](7) NOT NULL,
[SomeFiller] [varchar](256) NOT NULL,
CONSTRAINT [PK_myClusteredTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_PADDING OFF
GO

--Add some records to the table with varying length
INSERT INTO dbo.myClusteredTable
VALUES (SYSDATETIME(), SYSDATETIME(), REPLICATE('W', CEILING(RAND() * 100) + 1))

GO 15000

--Check the space used
sp_spaceused myClusteredTable

--Now apply compression
ALTER TABLE [dbo].[myClusteredTable] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)
GO

--Check the space used
sp_spaceused myClusteredTable
GO

--Add some more records to the table
INSERT INTO dbo.myClusteredTable (TheDate, TheTime, SomeFiller)
SELECT TheDate, TheTime, SomeFiller FROM myClusteredTable
GO

--Check the space used again
sp_spaceused myClusteredTable
GO

DROP TABLE myClusteredTable

As you can see the compression keeps going even when you insert new rows.
But what about the heap?

SET NOCOUNT ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myHeap]') AND type in (N'U'))
DROP TABLE [dbo].[myHeap]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[myHeap](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TheDate] [date] NOT NULL,
[TheTime] [time](7) NOT NULL,
[SomeFiller] [varchar](256) NOT NULL,
CONSTRAINT [PK_myHeap] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SET ANSI_PADDING OFF
GO

--Add some records to the table with varying length
INSERT INTO dbo.myHeap
VALUES (SYSDATETIME(), SYSDATETIME(), REPLICATE('W', CEILING(RAND() * 100) + 1))

GO 15000

--Check the space used
sp_spaceused myHeap

--Now apply compression
ALTER TABLE [dbo].[myHeap] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)
GO

--Check the space used
sp_spaceused myHeap
GO

--Add some more records to the table
INSERT INTO dbo.myHeap (TheDate, TheTime, SomeFiller)
SELECT TheDate, TheTime, SomeFiller FROM myHeap
GO

--Check the space used again
sp_spaceused myHeap
GO

--Rebuild the heap?
ALTER TABLE myHeap REBUILD
GO

--Check the space used again
sp_spaceused myHeap
GO

--What about a bulk insert?
BULK INSERT TestDB.dbo.MyHeap
   FROM 'c:\temp\test.txt'
   WITH
      (
   FORMATFILE='c:\temp\myHeap.xml',
   KEEPIDENTITY,
   TABLOCK
      )
GO

--Check the space used again
sp_spaceused myHeap
GO

--Rebuild the heap?
ALTER TABLE myHeap REBUILD
GO

--Check the space used again
sp_spaceused myHeap
GO

--Seems compression was indeed applied with BULK INSERT, go ahead and drop the test table
DROP TABLE myHeap

This was tested on SQL Server 2008 SP1 + CU2 (build 10.0.2714).

Cumulative update package 2 for SQL Server 2008 SP1

2009-05-20T15:24:00Z

The second cumulative update package for SQL Server 2008 SP1 is now available @ http://support.microsoft.com/kb/970315.
This will take you to build 10.00.2714.

And do not forget the "Lock Pages In Memory" change (here)!

SQL Server Kilimanjaro becomes SQL Server 2008 R2

2009-05-11T19:51:00Z

Today at Tech Ed North America 2009 we have officially announced the new name for SQL Server code-named Kilimanjaro.
The official name will be: SQL Server 2008 R2!

More information can be found @ http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx and the "official" announcement can be found @ http://blogs.technet.com/dataplatforminsider/archive/2009/05/11/teched-2009-new-sql-server-innovations.aspx.

Also read the official press announcement @ http://www.microsoft.com/presspass/press/2009/May09/05-11TechEd09PR.mspx.

"Finally, Microsoft announced that a Community Technology Preview of Microsoft SQL Server 2008 R2 (formerly SQL Server code-named “Kilimanjaro”) will be available in the second half of 2009. SQL Server 2008 R2 will empower end users to make better decisions through self-service business intelligence, and help IT drive greater efficiency and reduce costs through new capabilities such as multi-server management and Master Data Services. In 2010, Microsoft will also introduce complex event processing for real-time insight into streaming information. This builds on the outstanding value of SQL Server 2008, which already provides some customers with as much as a 160 percent return on their investment.*"

Next UG event: Visual Studio Team System 2008 Database Edition - The Azure Services Platform and SQL Services - Jun 4th, 2009

2009-05-08T11:34:00Z

The Belgian SQL Server User Group is organizing another great technical evening.
I am looking forward meeting you there.

From their website:

This evening will be split into 2 sessions as follows:

Session 1: Overview of Visual StudioTeam System 2008 Database Edition
Brought by Gill Cleeren , Microsoft Regional Director and MVP ASP.NET
Microsoft's Visual Studio for database professionals was originally designed to fill a gap in database development by providing features for managing lifecycles as well as tools for collaboration and testing.
In this session, Gill will show you the most important features of the product, including data generation, testing and its integration in Team Foundation Server.

Session 2: The Azure Services Platform and SQL Services
Brought by Kurt Clayes, Solution Architect and Competence Leader ‘CloudServices’ at ORDINA.
A new initiative for a development platform is coming from Microsoft. The Azure Services Platform. This platform enables to build applications for the cloud and offers a scalable hosted infrastructure for deploying and managing these applications and their data stores. Microsoft SQL Data Services (SDS) is a cloud-based relational database platform built on SQL Server technologies. With SDS, you can easily provision and deploy relational database solutions to the cloud, and take advantage of a globally distributed data center that provides enterprise-class availability, scalability, and security with the benefits of built-in data protection, self-healing and disaster recovery. In this session we will have a brief overview of the Azure Service Platform and see the new concepts behind Microsoft’s SQL Services vision. We will discuss the infrastructure architecture to enable scalability and the global reach of data sources.
Kurt CLAEYS is MVP Connected System Developer, MCT, MCSD, MCDBA. Solution Architect and Competence Leader ‘CloudServices’ at ORDINA.

Click here to register.

Lock Pages In Memory in SQL Server Standard Edition

2009-04-27T12:30:00Z

Until now "Lock Pages In Memory" was an option only available to SQL Server Enterprise Edition customers. Very simply put it prevents SQL Server from paging out the memory it has allocated (a deep explanation can be found in the references below). We got a lot of feedback from customers who wanted to have support for this in SQL Server Standard Edition too. So we are very pleased to announce that it will be available in SQL Server 2005 and SQL Server 2008 Standard Edition too (as of SQL Server 2005 SP3 CU4 and SQL Server 2008 SP1 CU2). As with everything do not just enable this option by default but carefully plan and test.

More information on this can be found on http://blogs.msdn.com/psssql/archive/2009/04/24/sql-server-locked-pages-and-standard-sku.aspx

Slava Oks did a great job in explaining the Lock Pages In Memory (and AWE) mechanism on http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx and http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx.

Cumulative update package 1 for SQL Server 2008 SP1

2009-04-16T11:08:00Z

Wow, the guys in the SQL Server team are working really hard.
The first CU for SQL Server 2008 SP1 has already been released and will take you to build 10.00.2710.

More information here.

SQL Server 2008 Service Pack 1

2009-04-08T09:10:00Z

We are very proud to announce the release of the first service pack for SQL Server 2008.

With the release of SQL Server 2008 we have also made great investments in the installer. I am sure you have all noticed that the setup experience is completely different from the previous version of SQL Server. By doing this we have made way for a long requested feature for SQL Server, slipstreaming of service packs. Our good friend Peter Saddow has listed the steps to follow to create your own slipstreamed install package here.

Other nice improvements are the ability to uninstall the service pack and the support for click-once deployment of Report Builder 2.0. Also notice that SQL Server 2008 SP1 has 80% fewer fixes than SQL Server 2005 SP1!

So big thumbs up for the SQL Server team!

The official announcement can be found on the Data Platform Insider blog.
You can download SQL Server 2008 SP1 here.

*EDIT*
This post is an absolute must-read regarding SQL Server 2008 SP1 (thanks Bob!)

WesleyB's Blog

PowerPivot on Lync – small update

Extending your Lync monitoring data using PowerPivot and Power View

Monitoring Server in Lync, why would you use it?

Some typical questions on the Monitoring Server role in Lync:

Can the Monitoring server role be combined with any other Lync Server role?

Can you collocate the monitoring database with a Lync back-end database?

Do you require SQL Reporting Server?

What’s the advantage of using the Monitoring Server role with Microsoft System Center Operations Manager?

Do I need a monitoring server per Lync Pool, and even per Site?

Any documentation available on how to deploy the Monitoring Server?

Is there a document that described how to use the Monitoring Server Reports in a Lync Server 2010 deployment?

Built-In Reports examples:

What does the Dashboard give me?

Let me start by saying that the solution provided here is obviously for demo purposes. It is not a perfect solution and we are not responsible for the production use of this file.

Technologies Used

Marketer of the Year

Restore improvements in SQL Server Denali CTP3 Management Studio

SQL Server Denali CTP3 – Lots of interesting resources

TechDays 2011: Early Bird Pricing Extended Until 28 February 2011

Using SharePoint Content Types as Report Templates

New Windows Azure Platform Features Available Today

Setup a Free Account

Download the Required Tools

Use and Experience the New Features

What’s New in SharePoint 2010 for BI?

First Microsoft Demo Day (June 2nd 2010) will keep its promise of offering a thrilling experience

Can I upgrade my SQL Server 2008 R2 Trial to a full version?

What’s new in Excel 2010 for BI?

Twitter for the SQL Server professional

PowerPivotFAQ

Project Gemini aka PowerPivot for Excel

SQL Server Day 2009

SQL Server 2008 R2 CTP2 Available for download

Cumulative update package 3 for SQL Server 2008 SP1

Data Compression and Heaps

Cumulative update package 2 for SQL Server 2008 SP1

SQL Server Kilimanjaro becomes SQL Server 2008 R2

Next UG event: Visual Studio Team System 2008 Database Edition - The Azure Services Platform and SQL Services - Jun 4th, 2009

Lock Pages In Memory in SQL Server Standard Edition

Cumulative update package 1 for SQL Server 2008 SP1

SQL Server 2008 Service Pack 1