This year at the Techdays in Belgium, I presented a session on Office 365, and the do’s and don’t’s when evaluating Office 365, based on the most typical questions I get when talking to customers about Office 365. Here’s a short recap of this session…more to be added J

Do 1: Know What You Are Subscribing To

• Office 365 Service Descriptions: http://technet.microsoft.com/en-us/library/jj819284.aspx
• Office 365 Service Updates: http://community.office365.com/en-us/wikis/office_365_service_updates/974.aspx
• Office 365 Service Upgrade Center for Enterprises: http://community.office365.com/en-us/wikis/office_365_service_updates/office-365-service-upgrade-center-for-enterprise.aspx
• Sign-up for a trial: http://alturl.com/rt9j8
• The new Office 365 Deployment Center: http://www.deployoffice365.com/

The new Office 365 Deployment Center is the place to find the tools, guidance, and technical resources to help you pilot and deploy Office 365. With Microsoft’s recommended approach, you can set-up a 25-user pilot quickly and experience the full set of Office 365 service features including the new Office applications. Then, smoothly move your entire organization into production and add advanced features, when needed.

Do 2: Understand Identities

Webcast on “Understand Identities and Single Sign On” available here: http://community.office365.com/en-us/blogs/office_365_technical_blog/archive/2013/02/07/understand-identities-and-single-sign-on-with-our-upcomoing-ignite-webcast.aspx

Do 3: Realize ADFS is more than Federated Identities

• ADFS:
• Enables users to access both the on-premises and cloud-based organizations with a single user name and password
• Provides users with a familiar sign-on experience
• Allows administrators to easily control account policies for cloud-based organization mailboxes by using on-premises Active Directory management tools
• Enables SharePoint Hybrid Search
• AD to Windows Azure AD Quick Start Guide: http://www.itproguy.com/ad-to-windows-azure-ad-quickstart-guide-released/
• Access Control Policies possible with ADFS:

Do 4: Is your environment ready to hook up to Office 365?

• Check your AD with the Microsoft Office 365 Deployment Readiness Tool, that provides analysis of your on-premises environment in preparation for an Office 365 enterprise deployment: http://community.office365.com/en-us/forums/183/p/2285/8155.aspx
• Supported clients
• Exchange: http://technet.microsoft.com/en-us/library/aa996719(v=exchg.150).aspx
• SharePoint Supported Browsers: http://technet.microsoft.com/en-us/library/cc263526.aspx
• Lync:
• Supported Clients: http://technet.microsoft.com/en-us/lync/jj663067.aspx
• Client comparison tables: http://technet.microsoft.com/en-us/library/gg425836.aspx
• Office 365 Client side requirements: http://onlinehelp.microsoft.com/office365-enterprises/ff652534.aspx

Do 5: Check your Network

• Lync:
• Lync 2013 Network Bandwidth Requirements for Media Traffic: http://technet.microsoft.com/en-us/library/jj688118.aspx
• Lync 2010 Bandwidth Calculator: http://www.microsoft.com/en-us/download/details.aspx?id=19011
• Exchange:
• Exchange Client Network Bandwidth Calculator: http://gallery.technet.microsoft.com/office/Exchange-Client-Network-8af1bf00
• SharePoint:
• Plan for Bandwidth Requirements: http://technet.microsoft.com/en-us/library/cc262952(v=office.12).aspx
• Office 365 URLS and IP Address Ranges
• http://onlinehelp.microsoft.com/en-us/office365-enterprises/hh373144.aspx
• Exchange Online URLs and IP Address Ranges
• http://technet.microsoft.com/en-us/exchangelabshelp/gg263350
• RSS Updates for URL and IP Address Range Changes
• http://go.microsoft.com/fwlink/?linkid=236301
• Set up your network for Lync Online
• http://onlinehelp.microsoft.com/en-us/office365-enterprises/hh416761.aspx

Do 6: Check out Azure

• Current Guidance:

ADFS should only be deployed in Azure VM for High Availability.

We would also not recommend a customer deploy the underlying AD domain controller to Azure. There would be latency issues for NTML authentication of domain join machines.

• http://msdn.microsoft.com/en-us/library/windowsazure/jj156090.aspx

You can deploy corporate domain controllers alongside AD FS on Windows Azure virtual machines, which provides additional guarantees of service availability in the event of unforeseen failures such as natural disasters. This is especially true for online services such as Microsoft Office 365 that can authenticate users directly from their on-premises corporate Active Directory.

• http://weblogs.asp.net/scottgu/archive/2012/07/26/windows-azure-and-office-365.aspx
• Developing Windows Azure Web Sites Integrated with Office 365
• Developing Windows Azure Workflows Integrated with Office 365
• Windows Azure Ad RMS
• Integration with Exchange Online
• Company Confidential
• Company Confidential Read Only
• Do not forward (Works across tenants)
• Integration with SharePoint Online
• There is no support for SharePoint Online Wave 15 (v2013) integration with customer on-premise AD RMS infrastructure.
• Documents that have been protected with RMS can be uploaded to SharePoint Online only in standard document libraries.
• In Office 365 Wave 15 (v2013), SharePoint Online supports RMS integration with the Windows Azure RMS service

Do 7: UC & C: Decide what to keep On Premises and what to move to Online

Do 8: Ready to move Exchange, think about your options

·Exchange 2013 Deployment Assistant : http://technet.microsoft.com/en-US/exdeploy2013/Checklist?state=672-W-AAAAAAAAQAAA

Do 9: Check our Trust Center

http://trustoffice365.com

Additional info:

• Skydrive Pro
• SharePoint Team Blog: http://blogs.office.com/b/sharepoint/archive/2013/03/06/out-and-about-new-sharepoint-mobile-offerings.aspx

Small part of this:

The SkyDrive Pro app. As you may know, SkyDrive Pro is cloud storage that organizations provision for employees as part of their SharePoint 2013 on-premises and/or Office 365 SharePoint Online deployments. 

This personal file storage service is for business use, and users can store, access and synchronize their files using the SkyDrive Pro client software.  Users are not limited to synchronizing their personal docs--they can sync any document library to which they have permission (for document libraries where sync is turned on).

As we shared at SharePoint Conference, we're working on native SkyDrive Pro apps for Windows 8 and iOS, both will be coming in summer of 2013. There, too, is a Windows desktop client that ships with Office 2013--and soon this "folder sync" technology will ship as a free, standalone installable client. In addition, the capabilities already come natively in Windows Phone (more on that below in the Office Hub section of this post).

• Customize Click-to-Run, Office 2013 deployment: http://blogs.technet.com/b/office_resource_kit/archive/2013/01/22/office-2013-click-to-run-customization.aspx
• SharePoint 2013 hybrid resources: http://www.microsoft.com/en-us/download/details.aspx?id=35593
• One-way hybrid environment with SharePoint Server 2013 and Office 365
• Two-way hybrid Search environment with SharePoint Server 2013 and Office 365
• Business Connectivity Services Hybrid Overview
• Lync 2013 Hybrid possibilities: http://technet.microsoft.com/en-us/library/jj204805(v=ocs.15).aspx

Imagine you have been testing Office 365 for 30 days, using the Free Trial option available for both Small Businesses (Plan P1) and Midsize Business and Enterprise (Plan E3) (get your own trial today, by clicking here), and after doing so you want to buy for example the E1 subscription for your users, the question is: can you switch your existing “users” from an Office E3 subscription to E1, without them losing anything?

The answer is yes  

• Looking at http://onlinehelp.microsoft.com/en-us/office365-enterprises/gg570315.aspx

You can assign new licenses or replace existing licenses for more than one user at a time. On the Users page, select the check box next to the names of the users, click Edit, and then click Next twice until the Assign licenses page appears. Select Replace existing license assignments or Add to existing license assignments, and then select the check box for the licenses that you want to assign. For information about editing multiple users, see Create or edit users.

• And more importantly here on http://www.microsoft.com/en-us/office365/compare-plans.aspx#fbid=O3TwiI0AXgF

Question: If I trial Plan E3, can I switch to plans E1, E2, or E4?

Answer: Yes, you can switch between plans. Once you have signed up for a Plan E3 trial, you can make this change in the billing and subscription management of your Office 365 admin overview. Note: only Plan E3 is available for trial.

Now, be careful, take note of the differences between the different subscriptions, since moving between the different subscription plans, might come with losing and gaining features! E3 for examples has an unlimited Online Archive in it, whereas E1 has a limit of 25Gb for the sum of Primary Mailbox and Online Archive!

But You CanNOT migrate between a Plan P Account and a Plan E account

You cannot migrate between a Plan P account and a Plan E account. In order to move between the two plans you would need to cancel your account and then sign up for a different one.

Important Sources when looking/deciding between the different subscription plans in Office 365:

• Try Office 365 for free for 30 days: http://www.microsoft.com/en-us/office365/free-office365-trial.aspx?WT.z_O365_ca=Try_online-software _en-us
• Compare the different Office 365 Plans: http://www.microsoft.com/en-us/office365/compare-plans.aspx
• Office 365 for Enterprise Service Descriptions: http://www.microsoft.com/en-us/download/details.aspx?id=13602
• Office 365 for professionals and small businesses Service Description: http://www.microsoft.com/en-us/download/details.aspx?id=13779
• Microsoft Office 365 Service Descriptions and Service Level Agreements for Dedicated Subscription Plans: http://www.microsoft.com/en-us/download/details.aspx?id=18128

Can I move a user from Plan K1 to Plan E3?

In the following print screens you can see the switch from a Plan K1 license to a Plan E3 subscription in the same tenant for a user called Kiosk 1.

Start = Kiosk1 = Microsoft Office 365 Plan K1

OWA for Kiosk1 looks like:

Time to switch Kiosk1 from Plan K1 to Plan E3:

Be careful: you need to uncheck K1 and then select E3, followed by clicking Save.

When Kiosk1 signs in, the services for Plan E3 are initialized. When changing a user from E3 to E1 the setup of Lync and SharePoint is not needed anymore, since it has already been done

Once the setup is done, Kiosk1 can open his mailbox again:

And OWA gives the same messages:

Office 365 Rocks!

Ilse

Got a question today if it is possible to prevent a user from changing his or her own password when using Exchange Online, within Office 365.

The answer is Yes And how to do this? Find one way of doing this in this blog post, using (what did you expect?) the magic of PowerShell.

Setting the scene

I want to create a new user id, called Test, in my Office 365 environment, and have the following password characteristics:

- Password should never expire

- Password should not need to be changed at first logon of the user

- Password can not be changed by the user, using OWA.

Step 1. Create the user

Using the  Office 365 admin portal, I’m creating my new user:

Step 2. Changing Password Settings

I do not want my user to sign in with that temporary password, so by using the Microsoft Online Service Module for Windows PowerShell, I change the password to the one I want, and I set it to never expire, and I disable the fact that the user will need to change the password at the first logon.

To set the password to never expire, I’m using the cmdlet Set-MsOlUser and adding PasswordNeverExpires, and setting it to $True.

To set the password to a predefined value, I use the cmdlet Set-MsOlUserPassword, and add the new password using NewPassword (be aware, you need to identify the password in clear text here, no need to encrypt it first), and then by adding the ForceChangePassword and setting it to $False, the user won’t be prompted to change it after his first login!

Signing in, the user needs to enter his password I have given the user, and can sign in:

But the user is still able to go to OWA, select Options, and from there change his password.

When searching the web, it is possible in an Exchange On Premises environment to disable the Change your password functionality from within OWA/ECP:

• http://technet.microsoft.com/en-us/library/bb684904
• http://technet.microsoft.com/en-us/library/bb123962.aspx

But the cmdlet Set-OwaVirtualDirectory and its parameter ChangePasswordEnabled, is not available in Exchange Online!

How to do this in Exchange Online? Using RBAC!

As described in one of my previous blog posts (Exchange Online (Office365) and RBAC?), you can create and assign custom roles in Exchange Online. The permission to change your password, is included in the default role assigned to any mail-enabled user in Exchange Online. It is included in the role MyBaseOptions.

First I will create a new role AllButChangePassword, and make it a copy of the existing MyBaseOptions role.

Looking at the parameters that can be set using Set-Mailbox within the role AllButChange Password, it is visible that password is included:

Time to remove it:

And then to create a new RoleAssignmentPolicy, which will include the AllButChangePassword:

Then assign the new role to my test user:

And time to test!

Logging in to OWA as test user, going back to ECP, the ability to change the password is gone!

Exchange ROCKS!

Ilse

Yesterday I have had the pleasure of doing a TechNet LiveMeeting here @Microsoft Belgium, entitled “Using the Power of PowerShell to manage your Exchange Online and Exchange On Premises Environment”. The session was not a level 400 deep dive in PowerShell, but its aim was to give some tips on how one can use PowerShell to manage an Exchange Online tenant in Office365.

Tip 1. Set-ExecutionPolicy Unrestricted and -AllowRedirection

Connecting to Exchange Online is easy, and boils down to launching Windows PowerShell, creating a new persistent connection to the remote Exchange Client Access Server, and importing it, like can be seen in the picture below:

Two remarks here:

1. Before you are able to run the cmdlet Import-PSSession, you need to make sure you are allowed to run scripts... When you run Import-PSSession and you get the following error message:

Import-Module : There were errors in loading the format data file:
Microsoft.PowerShell, , C:\Users\Seppe\AppData\Local\Temp\tmp_0740bdd5-5276-437
7-a890-50bb10d3d32b_cuwqdhef.gqv\tmp_0740bdd5-5276-4377-a890-50bb10d3d32b_cuwqd
hef.gqv.format.ps1xml : File skipped because of the following validation except
ion: File C:\Users\Seppe\AppData\Local\Temp\tmp_0740bdd5-5276-4377-a890-50bb10d
3d32b_cuwqdhef.gqv\tmp_0740bdd5-5276-4377-a890-50bb10d3d32b_cuwqdhef.gqv.format
.ps1xml cannot be loaded because the execution of scripts is disabled on this s
ystem. Please see "get-help about_signing" for more details..
At line:3 char:30
+                 Import-Module <<<<  -Name $name -Alias * -Function * -Prefix
$prefix -DisableNameChecking:$disableNameChecking -PassThru -ArgumentList @($se
ssion)
    + CategoryInfo          : InvalidOperation: (:) [Import-Module], RuntimeEx
   ception
    + FullyQualifiedErrorId : FormatXmlUpateException,Microsoft.PowerShell.Com
   mands.ImportModuleCommand

To enable the execution of scripts you can run the cmdlet Set-ExecutionPolicy Unrestricted. This can be enforced btw by using Group Policies, you can download the ADM Group Policy Template for PowerShell here. For more information on Set-ExecutionPolicy, check this link here.

2. Do not forget the parameter AllowRedirection, which will enable redirection to the appropriate Exchange server using different URI.

Tip 2. Connecting to Exchange Online and Exchange On Premises at the same time, use –Prefix

When you launch the Exchange Management Shell, you are using Remote PowerShell to connect to a Client Access Server in your Exchange On Premises environment, as you can see by running Get-PSSession after launching Exchange Management Shell

If you would then create a new persistent connection to Exchange Online using the directions mentioned above, you would get the following warning when importing the PowerShell Session:

“WARNING: Proxy creation has been skipped for the following command: …., because it would shadow an existing local command. Use the AllowClobber parameter if you want to shadow existing local commands.”

If you would use the parameter AllowClobber, you would indeed shadow the existing commands, meaning, you would hide or replace the original commands: eg. running Get-Mailbox would retrieve the Exchange Online mailboxes, but you wouldn’t be able to retrieve the ones in your Exchange On Premises organization anymore in this EMS Session!

Solution: Use the Prefix parameter, which will add the given prefix to the nouns in the names of the imported commands.

Running the following Import-PSSession $Session –Prefix o365 will import all the commands, but will prefix all the nouns with o365, running Get-DistributionGroup will return a list of all Distribution Groups in my On Premises Exchange Organization, where-as running Get-o365DistributionGroup will return a list of Distribution Groups in my Exchange Online environment:

Tip 3. Remove-PSSession

Looking at the definition of Remove-PSSession on TechNet

The Remove-PSSession cmdlet closes Windows PowerShell sessions (PSSessions) in the current session. It stops any commands that are running in the PSSessions, ends the PSSession, and releases the resources that the PSSession was using. If the PSSession is connected to a remote computer, Remove-PSSession also closes the connection between the local and remote computers.

Why would you do this for your Exchange Online? Because if you do not close the Windows PowerShell window without disconnecting from the server-side session, your connection will remain open for 15 minutes. And you have a limit of three connections to the server-side session at one time per account.

Tip 4. Use Profiles

If you want to know more about Windows PowerShell Profiles, please head over here @MSDN, where you can dive into the wonders of profiles. The reason it might be useful to use profiles here is that you don’t need to type everything every single time you want to connect to your Exchange Online environment. By entering the New-PSSession and Import-PSSession lines in any of the 4 profiles, you can create functions that you can call upon when you want to connect to your Office365 Exchange Online tenant by simply entering Connect-ExchangeOnline.

In the TechNet LiveMeeting I created my Windows PowerShell Profile, the process on how to create this is clearly described here, and here’s what it looks like after creating it:

By using the so-called Windows PowerShell user profile, this will only work for the currently logged on user, and only for the Microsoft.PowerShell shell, if I launch ISE for example, you won’t have the functions Connect-ExchangeOnline and Disconnect-ExchangeOnline:

Question: is it possible to schedule a PowerShell script against Exchange Online?

Answer: Sure

In the below example I will schedule a task to when run will create a excel file with an overview of the mailbox sizes, the tricky part is the passing of your credentials. I have chosen to first create a passwordfile, which will contain the password of the user that will be used to connect in the script to the Exchange Online tenant:

Read-Host -AsSecureString | ConvertFrom-SecureString | Out-File C:\users\ilvancri\MyPassword.txt

Then I have created a script called “Mailboxsizes.ps1”, that will connect to Office365, create the csv file, and remove the PSSession in the end:

$password = type C:\users\ilvancri\MyPassword.txt | ConvertTo-SecureString
$userid = "ilse@microsoftbelux.onmicrosoft.com"
$cred = New-Object System.Management.Automation.PSCredential $userid,$password
$global:session365 = New-PSSession -configurationname Microsoft.Exchange -connectionuri https://ps.outlook.com/powershell/ -credential $cred -authentication Basic -AllowRedirection
Import-PSSession $global:session365

Get-Mailbox | Get-MailboxStatistics | Select Displayname,TotalItemSize, ItemCount | Export-csv .\mailboxsizes.csv
Get-PSSession | Remove-PSSession

Now it’s time to create a  batch file that when run, will launch and execute the script:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -command ". 'C:\users\ilvancri\mailboxsize.ps1'

And now you can schedule to run the BAT file when needed.

Ilse

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, as taken from TechNet

You can collocate each of the following databases on the same database server:

• Back-end database
• Monitoring database
• Archiving database

You can collocate any or any or all of these databases in a single SQL instance or use a separate SQL instances for each, with the following limitations:

Each SQL instance can contain only a single back-end database, single Monitoring database, and single Archiving database.

The database server cannot support more than one Front End pool, one Archiving Server, and one Monitoring Server, but it can support one of each, regardless of whether the databases use the same SQL instance or separate SQL instances.

• 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 rtccab1 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. 

UPDATE dd April23rd, 2012

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.

Ilse & Wesley