SQL Server 2012 Business Intelligence

SQL Server 2012 is out now and there are a lot of great new features in the world of Business Intelligence (BI) included in this new release and this article covers my top 5 new BI features in SQL Server 2012. From Reporting, Loading Data, and Analysis there are a lot of new features to choose from in 2012. It is hard to choose from all of the new features, so this list was based on how useful each feature is in terms of BI development and analyzing data.

#1 Power View

Power View is a great new ad-hoc reporting tool built for the end users. It requires SQL Server 2012 and SharePoint 2010 and has one of the coolest UI displays compared to the previous tools. Power View gives end users an intuitive ad-hoc reporting tool they can use to easily create and interact with data from PowerPivot workbooks or tabular models deployed to SQL Server 2012 Analysis Services. It runs in a browser and uses Silverlight from within SharePoint Server 2010. One of the coolest graphing tools in Power View is the time line. You can build a chart and place a time line along the bottom and click the play button and watch the data change over time. I for one can’t wait to build reports with this tool and see my users build them too.


#2 SSIS Configure and Execute with T-SQL

SSIS has improved significantly in SQL Server 2012. The greatest new feature is the ability to run and control your SSIS packages using T-SQL. With the addition of the new SSIS catalog and the project deployment, your packages are housed in a database now and there is an entire set of stored procedures and functions for SSIS administration. This opens up a Pandora’s Box of possibilities! Imagine writing complex stored procedures that call multiple SSIS packages, or using a cursor to execute a package for each row on a table. You can select data from a table and pass it to a package using parameters. This is much improved from the command line prompt method in the previous versions of SQL Server. It gives developers and DBAs the ability to incorporate SSIS more into their database and their development.

#3 SSIS Parameters and Environments

Speaking of Parameters, it happens to be number three on my top 5 BI list. Parameters and environments allow developers to pass variables into packages now without using configuration files or tables. That’s right, no longer do developers have to manage a group of files or tables separate from their packages. They can execute packages with T-SQL and pass in the parameter values using the T-SQL. They can also save multiple parameter values in the new environments. Environments can be thought of as a parameter bucket that holds a set of parameter values. Packages that need a different set of values can have all the parameters changed with just a simple change of selecting a different environment. A good example of this would be a package that needs one set of values during the week and a different set on the weekend or at month end. As you read in number two, you can execute the package with T-SQL, so you can place logic in your T-SQL, like a Case When statement, to select the proper environment. You can also use them for Development versus Production settings.


#4 SSAS Tabular Models

Developers now have the ability to create tabular models in SQL Server Data Tools (SSDT, Formerly BIDS). Instead of having to use Power Pivot in Excel to develop a tabular model, developers can now use a tool they are more familiar with, Visual Studio. This gives developers the ability to create and deploy tabular models using SSDT. These are available to the end user to connect with power pivot and start slicing a dicing data. The diagram view makes it easy to visualize the data and build hierarchies. Before SQL Server 2012, PowerPivot was the only way to create a tabular like model in SQL Server. This new model puts the developers in a familiar environment and allows them to create models for the users to easily consume.


#5 SSIS Undo/Redo

I know this seems like a small improvement, but for any SSIS developers out there, they know the undo/redo feature added to SSIS makes like so much easier. Imagine writing a word doc and not having undo. Oops, you accidently deleted an entire paragraph, now write it again! In SSIS, that was the norm, until SQL Server 2012. Now if you delete some task or make changes you need to reverse, CTRL + Z is here to save the day.


Top Ten Reasons to Buy VS Team Foundation Server.

Top Ten Reasons to Buy VS Team Foundation Server.

  • Streamline The Flow of Data Across Your Entire Team
    Project artifacts are stored in a central repository that facilitates in-context collaboration reducing waste in hand-over time between tasks and streamlines the development process allowing team members to focus on delivering value over transitioning information between roles.
  • Reduce Risk with Real-time Visibility
    Powerful reporting and dashboards provide historical trending and real-time visibility into overall project health. Real-time metrics give you early warnings of potential problems that enable you to be proactive and to make data-driven decisions and course corrections.
  • End-to-end Traceability
    Define, query and report on custom relationships between requirements, work items and test cases. Full traceability lets you track progress and quality back to business goals and customer requirements.
  • Lightweight Agile Planning Tools
    The new Excel Agile Planning Workbook makes it easy for teams to adopt Agile software development methodologies like SCRUM. Use it to create and manage user stories and product backlog, estimate the team’s velocity, and break the project down into iterations. The Iteration Backlog enables you to plan iterations and track progress.
  • Project and Portfolio Management
    Integration with Microsoft Project and Office Project Server enables business stake holders and project managers to gain insight into the health of inflight projects, understand how they support the business needs and help identify ways to improve existing processes.
  • Simplified Installation for Smaller Teams
    Smaller teams and individual developers can choose the new Basic Install option to leverage the power of Team Foundation Server 2010 without the footprint of the full installation.
  • Understand Parallel Development
    Reduce the complexity in branching and merging with powerful new visualization tools. Understand the scope, organization and maintenance of your source code and easily identify, track and manage changes across branches.
  • Prevent Broken Builds
    The new gated check-in feature helps teams working in the same branch to prevent costly and time consuming build breaks by testing code in isolation before it goes into the full repository.
  • Flexible Build Automation
    Windows Workflow-based builds with powerful features like build queuing and build agent pooling enable teams to easily customize, manage and scale out their build environments.
  • Enterprise Scalability
    Network Load Balancing, 64-bit server support and new project

What is Volume Activation

What is Volume Activation?
Volume Activation is a product activation technology that was first introduced with Windows Vista and Windows Server 2008. It is designed to allow Volume License customers to automate the activation process in a way that is transparent to end users.

Volume Activation applies only to systems that are covered under a Volume Licensing program and is used strictly as a tool for activation. It is not tied to license invoicing or billing.

Volume Activation provides two different models for completing volume activations. Either or both key types may be used by customers to activate systems in their organization:

  • Key Management Service (KMS)
    KMS allows organizations to activate systems within their own network.
  • Multiple Activation Key (MAK)
    MAK activates systems on a one-time basis, using Microsoft’s hosted activation services.

What is the Key Management Service (KMS)?
The Key Management Service (KMS) is an activation service that allows organizations to activate systems within their own network, eliminating the need for individual computers to connect to Microsoft for product activation. It does not require a dedicated system and can be easily co-hosted on a system that provides other services.

KMS requires a minimum number of either physical or virtual computers in a network environment. These minimums, called activation thresholds, are set so that they are easily met by Enterprise customers.

  • Activation Thresholds for Windows – Your organization must have at least five (5) computers to activate servers running Windows Server 2008 or Windows Server 2008 R2 and at least twenty-five (25) computers to activate client systems running Windows Vista or Windows 7.
  • Activation Thresholds for Office – Your organization must have at least five (5) computers running Office 2010, Project 2010, or Visio 2010 to activate installed Office products using KMS. For more information about Volume Activation of Office products (that is, Office 2010, Project 2010, and Visio 2010), see the Volume Activation Quick Start Guide for Office 2010, and Volume Activation Overview for Office 2010.

What is a KMS Host Key?
A KMS Host Key is used to activate the KMS host computer with a Microsoft activation server and can activate up to six (6) KMS hosts with 10 activations per host. Each KMS host can activate an unlimited number of computers.
Note: Contact the Microsoft Activation Center if you require additional KMS activations for activating more than six (6) KMS hosts.

What is a Multiple Activation Key (MAK)?
A Multiple Activation Key (MAK) activates systems on a one-time basis, using Microsoft’s hosted activation services (that is, it requires connection with a Microsoft activation server). Once computers are activated, no further communication with Microsoft is required.
Important: Each MAK has a predetermined number of allowed activations, based on your Volume Licensing agreement. Contact the Microsoft Activation Center to increase your MAK activation limit.
You can activate licenses in one of two ways using MAK:

  • MAK Independent Activation – Each computer individually connects to Microsoft via the web or telephone to complete activation.
  • MAK Proxy Activation – One centralized activation request is made on behalf of multiple computers with a single connection to Microsoft online or by telephone. This method uses the Volume Activation Management Tool (VAMT), which is a part of the Windows 7 Automated Installation Kit (WAIK). VAMT enables IT Professionals to automate and centrally manage the Volume Activation process using MAK, and includes a check on the number of activations on the MAK.

What’s new for IT professionals in Office 2013

Office 2013 provides new features and improvements that help IT administrators configure, validate, deploy, and protect their Office installations. This article describes some changes in these areas.
In this article:

  • Active Directory-Based activation
  • Apps for Office
  • Click-to-Run
  • Click-to-Run customization
  • Co-Authoring
  • Access 2013: new application model
  • New Group Policy and OCT settings
  • Office Telemetry Dashboard and Office Telemetry Log
  • Office Web Apps
  • Office Web Apps Server
  • Security changes
  • Sign-in IDs
  • Downloadable proofing tool packages

Active Directory-Based activation

Volume activation establishes a relationship between the Volume License (VL) product key and a particular installation of the VL software on a device. Microsoft policy requires you to activate VL editions of Office 2010 and Office 2013 that run on both physical computers and virtual machines.
When Office 2013 runs on Windows 8 or Windows Server 2012, a new volume activation method is available: Active Directory-based activation.
Active Directory-Based activation uses your existing Active Directory infrastructure to activate all Office 2013 VL clients through their connection to the domain. To set up Active Directory-Based activation for Office 2013, configure Active Directory Domain Services (AD DS) from either a Windows 8 VL edition computer or a Windows Server 2012 computer. The Office 2013 VL clients can automatically activate against the domain as long as they are running on a Windows 8 or Windows Server 2012 client computer.

The two volume activation methods that were introduced in Office 2010: Key Management Service (KMS) and Multiple Activation Key (MAK), are still valid for Office 2013.

Apps for Office

Developers use apps for Office to create engaging new consumer and enterprise experiences that run within supported Office 2013 applications by using the power of the web and standard web technologies such as HTML5, XML, CSS3, JavaScript, and REST APIs.
An app for Office is basically a webpage that is hosted inside an Office client application. You can use an app for Office to extend the functionality of a document, email message, or appointment. The apps in apps for Office can run in multiple environments and clients. These include rich Office desktop clients, Office Web Apps, mobile browsers, and also on-premises and in the cloud. Developers can publish apps for Office to the Office Store or to an onsite catalog, where they can be available to users from their Office 2013 applications. As the IT Administrator of your organization, you can control how the apps for Office become available to users.


Office 2013 Click-to-Run is a technology that reduces the time that is required to download and use Office 2013 client products. Click-to-Run is based on core virtualization and streaming Microsoft Application Virtualization (App-V) technologies. The streaming technology lets you use a Click-to-Run program before the complete program is fully downloaded and installed on your computer.
Click-to-Run virtualization provides the following advantages:

  • Isolation of resources in a virtual environment allows for coexistence with earlier versions of Office, and improves cleanup and restore processes.
  • Extension points to the computer are published so Office is more integrated. This allows for add-ins to be written.
  • Streaming helps improve performance. The Office package is divided and is uncompressed over time, which optimizes network bandwidth and CPU usage.

Click-to-Run customization

Administrators can customize Click-to-Run product installations. Click-to-Run for Office 365 provides a downloadable Office Deployment Tool for Click-to-Run that enables administrators to download Click-to-Run for Office 365 product and language sources to an on-premises location. This is useful in scenarios where administrators want to minimize the demand on the network or want to prevent users from installing from the Internet because of corporate security requirements.
You can download the Office Deployment Tool from the Microsoft Download Center site. The download includes a sample Configuration.xml configuration file. To customize an installation, administrators run the Office Deployment Tool and provide a customized Configuration.xml file. The Office Deployment Tool performs the tasks that are specified by using the optional properties in the Configuration.xml configuration file.


Co-authoring simplifies collaboration by enabling multiple users to work productively on the same document without intruding on one another’s work or locking out other users. This capability requires no additional server setup and is the default status for documents that are stored in SharePoint 2013 document libraries.
Here are some changes in co-authoring for Office 2013.

  • Co-authoring is now supported in Visio Professional 2013, Word Web App, and PowerPoint Web App.
  • Seeing other editors who join the document to co-author is faster in Word 2013, Excel 2013, PowerPoint 2013, and Visio Professional 2013.
  • Updates are faster when multiple users co-author in the same OneNote page.
    Users who co-author also benefit from these changes in Office Web Apps, Office 2013, and SharePoint 2013.
  • Users can view, add, and reply to comments in Word Web App and PowerPoint Web App.
  • Users can open Word files that contain revision marks in Word Web App.
  • Users can easily set document permissions and send sharing notifications by using the Share with People feature in Office 2013 and SharePoint 2013.

Access 2013: new application model

The on-premises version of Access 2013 provides a new application model that uses SharePoint 2013 for hosting and SQL Server for data storage.

Access as a SharePoint app

Access 2013 is now an app for SharePoint, which means that you create Access web apps that run in the browser, and can manage Access as you would any SharePoint app, controlling everything centrally by using SharePoint tools. No additional passwords or logins are required because security is controlled through the same SharePoint infrastructure. Users can discover and share Access apps through the public SharePoint App Store or a private App Catalogue. They can use your app as long as they have a web browser and an Internet connection. Access does not have to be installed on users’ devices.
In addition, Access 2013 can open databases created in previous Access versions.
For more information about the new Access 2013 application model, see “New application model” in What’s new for Access 2013 developers.

SQL Server back end

If you create an app on a SharePoint Server that your company hosts on premises, Access creates the database in the SQL Server 2012 installation that is selected by the SharePoint administrator. This database stores all the objects and data that your application requires, such as tables, queries, macros, and forms. Whenever users visit the app, enter data, or change the design, they will be interacting with this database behind the scenes. The database created is specific to your app and is not shared with other apps.
SQL Server storage allows for speed, reliability, and scalability of the database long-term. In addition, advanced users can connect directly to the SQL Server database for advanced reporting and analysis with familiar tools such as Excel, Power View, and Crystal Reports.

More new features

Although these new features are not necessarily targeted to IT Professionals, you should be aware of them.

Access in Office 365

Access 2013 is also available in Office 365. If your Office 365 plan includes SharePoint, Microsoft can host your Access 2013 databases in the cloud. When you create an Access app in Office 365, your data is stored in a SQL Azure database.

New user features

Access 2013 provides new features that will help users perform tasks faster, and with more efficiency and flexibility.

New Group Policy and OCT settings

The Office 2013 Preview Administrative Template files (ADMX/ADML) and Office Customization Tool download package contains all Group Policy Administrative Template (ADMX/ADML) and Office Customization Tool (OPAX/OPAL) files for installations of Office 2013 (Windows installer-based) and Office 365 ProPlus (Click-to-Run).

Office Telemetry Dashboard and Office Telemetry Log

Office 2013 introduces a new application and document compatibility tool, known as Telemetry Dashboard. It replaces the Office 2010 compatibility tools Office Migration Planning Manager (OMPM), Office Code Compatibility Inspector (OCCI), and Office Environment Assessment Tool (OEAT). Telemetry Dashboard helps speed up Office 2013 deployments by reducing the overall time that is needed for migration planning and compatibility assessment. IT Professionals can use Telemetry Dashboard to identify typically used Office documents and solutions in their organizations and to view application events and crash data for select Office 2013 applications.
A companion tool for Telemetry Dashboard, known as Telemetry Log, is designed for developers and expert users who want to view event data for documents and solutions as they load, run, or raise errors in Office 2013. Telemetry Log shows local event data, whereas Telemetry Dashboard combines this event data for multiple client computers.

Office Web Apps

When used with SharePoint 2013 on-premises, Office Web Apps provides updated versions of Word Web App, Excel Web App, PowerPoint Web App, and OneNote Web App. Users can view and, depending on the current license, edit Office documents by using a supported web browser on computers and on different mobile devices, such as Windows Phones, iPhones, and iPads.
In addition to new features in Office Web Apps, the architecture and deployment methods have also changed. Office Web Apps is no longer tightly integrated with SharePoint. Instead, it is installed separately as part of Office Web Apps Server, a stand-alone Office server product. You no longer have to optimize the SharePoint infrastructure to support Office Web Apps, and you can apply updates to the servers that run Office Web Apps Server separately and at a different frequency than you update SharePoint.

Office Web Apps Server

Office Web Apps Server is an Office server product that provides browser-based file viewing and editing functionality for Office files. Office Web Apps Server works together with products and services that support WOPI, the Web app Open Platform Interface protocol. These products, known as hosts, include SharePoint 2013, Lync Server 2013, and Exchange Server 2013. Here are some features that Office Web Apps Server enables for these products:

  • SharePoint 2013 Users can access Office files from SharePoint document libraries by using Word Web App, Excel Web App, PowerPoint Web App, and OneNote Web App. There are many new features and capabilities, such as support for co-authoring in Word Web App and PowerPoint Web App.
  • Lync Server 2013 Users can broadcast PowerPoint presentations by using Lync 2013 and Lync Web App. Broadcasting is improved to support higher-resolution displays and a wider range of mobile devices than in earlier versions. Users who have the appropriate privileges can scroll through a PowerPoint presentation independent of the presentation itself.
  • Exchange Server 2013 In Outlook Web App, all attachments in an email message are displayed in a filmstrip that includes a thumbnail of each attachment. Users can preview attachments online in full fidelity.

An Office Web Apps Server farm can provide Office services to multiple on-premises hosts. You can scale out the farm from one server to multiple servers as your organization’s needs grow. Although Office Web Apps Server requires dedicated servers that run no other server applications, you can install Office Web Apps Server on virtual machine instances.
Deploying and managing Office Web Apps Server across an organization is easier now that it is a stand-alone product. You can apply updates to the Office Web Apps Server farm separately and at a different frequency than you can when you update SharePoint Server, Exchange Server, or Lync Server. Having a stand-alone Office Web Apps Server farm also means that users can view or edit Office files that are stored outside SharePoint 2013, such as those in shared folders or in document management products that support WOPI.

Security changes

Several new security features make it easier for users and IT professionals to use and trust Office 2013 applications and documents. These include the following:

  • Authentication in Office The days are over for providing a password multiple times as users conduct normal business, opening multiple Office files from different locations. Now users create a profile, sign in one time, and can seamlessly work on and access local and cloud Office files and not be required to re-identify themselves. Users can connect multiple services such as an organization’s SkyDrive or a user’s personal SkyDrive account, to their Office profiles and have instant access to all files and their associated storage. Users authenticate one time for all Office applications, including SkyDrive. This is true regardless of the identity provider (Microsoft account and the user ID that you use to access Office 365) or the authentication protocol that is used by the application (for example, OAuth, forms based, claims based, or Windows Integrated Authentication). From a user perspective, it all just works. From the IT perspective, these connected services can easily be managed. For more information, see Overview of Office 365 ProPlus.
  • File password escrow key Office 2013 includes new functionality that lets admins unlock password-protected Office files when, for example, the file owner either forgets the password or leaves the organization. By using a new Escrow Key Admin Tool, IT personnel can easily assign a new password to the file, or no password, and can save the file to the same location or a new location. You can download the Escrow Key Admin Tool from the Connect site.
  • Digital signature Several digital signature improvements were made for documents that were created by using Office 2013 applications.Plus, Office 2013 will verify signatures on ODF files that were created by using
    • other applications.
    • XAdES digital signatures in documents that are created by using Office 2013 applications are now easier to create. XAdES signed Office 2013 documents allow signers to add their addresses, titles, and to describe the intent of their signatures. Office 2013 will also evaluate -XL signatures by using the certificates and any revocation data that is contained in the file
  • Information Rights Management Office 2013 incorporates new Windows 2012 Active Directory Rights Management Services (AD RMS) features. Support for multiple user IDs for access to Office 365 and single sign-on means that choosing an identity and protecting content is much easier for users. Behind the scenes, this new functionality and automatic service discovery eliminate the need for registry configuration.
  • Enhancements to Office 2013 documents opened in “protected view” This is a feature that was introduced in Office 2010 and helps reduce exploits by opening files in a restricted sandboxed application container “lowbox” so that they can be examined before editing. New Windows 8 capabilities mean stronger process isolation and an application container that is blocked from network access.


IDs

In Office 365, users can sign in by using either of two types of credentials: Personal (Microsoft account) or Organization (the Office 365 user ID that is assigned by the organization). The user supplies these credentials within the user interface (UI) or they can be picked up from the operating system in certain cases.
You can enable one of four sign-in states by using the Block sign-in to Office setting in the Registry. This setting controls whether users can provide credentials to Office 2013 by using either their Microsoft account or the Office 365 user ID assigned by the organization.

The Registry key for this setting is as follows:


If you enable this setting, you can specify a particular sign-in option by setting one of the following values:

  • 0 Both IDs allowed
  • 1 Live ID only
  • 2 Org ID only
  • 3 None allowed

The following table shows what users can do when you set a particular sign-in option:

options and their effect

If you select this… This is what a user can do…
Both IDs allowed Sign in and access Office content by using either ID.
Live ID only Sign in only by using a Microsoft account.
Org ID only Sign in only by using the Office 365 user ID that is assigned by your organization.
None allowed Can’t sign in by using either ID.
If you disable, or do not configure, the Block sign-in to Office setting, the default setting is Both IDs allowed. Users can sign in by using either ID.
Office 2013 automatically bootstraps itself by using any Active Directory Domain Services (AD DS) account through which the user signed in to the operating system. If that Active Directory account is federated with Office 2013, the user automatically receives all benefits of signing into Office 2013 without having to perform any additional steps.

Downloadable proofing tool packages

Beginning in Office 2013, free downloadable proofing tool packages in 52 languages are available to all customers who have purchased the on-premises (MSI-installed) version of Office 2013, whether or not they are volume license customers. You can download these proofing tool packages in 32-bit or 64-bit versions from Office Proofing Tools.

What’s New in Exchange 2013

What’s New in Exchange 2013

Microsoft Exchange Server 2013 brings a new rich set of technologies, features, and services to the Exchange Server product line. Its goal is to support people and organizations as their work habits evolve from a communication focus to a collaboration focus. At the same time, Exchange Server 2013 helps lower the total cost of ownership whether you deploy Exchange 2013 on-premises or provision your mailboxes in the cloud. New features and functionality in Exchange 2013 are designed to do the following:

  • Support a multigenerational workforce Social integration and making it easier to find people is important to users. Smart Search learns from users’ communication and collaboration behavior to enhance and prioritize search results in Exchange. Also, with Exchange 2013, users can merge contacts from multiple sources to provide a single view of a person, by linking contact information pulled from multiple locations.
  • Provide an engaging experience Microsoft Outlook 2013 and Microsoft Outlook Web App have a fresh new look. Outlook Web App emphasizes a streamlined user interface that also supports the use of touch, enhancing the mobile device experience with Exchange.
  • Integrate with SharePoint and Lync Exchange 2013 offers greater integration with Microsoft SharePoint 2013 and Microsoft Lync 2013 through site mailboxes and In-Place eDiscovery. Together, these products offer a suite of features that make scenarios such as enterprise eDiscovery and collaboration using site mailboxes possible.
  • Help meet evolving compliance needs Compliance and eDiscovery are challenging for many organizations. Exchange 2013 helps you to find and search data not only in Exchange, but across your organization. With improved search and indexing, you can search across Exchange 2013, Lync 2013, SharePoint 2013, and Windows file servers. In addition, data loss prevention (DLP) can help keep your organization safe from users mistakenly sending sensitive information to unauthorized people. DLP helps you identify, monitor, and protect sensitive data through deep content analysis.
  • Provide a resilient solution Exchange 2013 builds upon the Exchange Server 2010 architecture and has been redesigned for simplicity of scale, hardware utilization, and failure isolation.

Visual Studio Capabilities comparison

Visual Studio editions are tailored to specific team needs and sizes, as well as the roles of individual team members. Find the one that’s right for you.
Feature comparisons


1. Includes Tier Interaction Profiling
2. Read-Only Diagrams
3. Requires full Team Foundation Server and TFS CAL (purchased separately)
4. Microsoft SharePoint Server licensed separately
5. Microsoft Project and Project Server licensed separately
6. Microsoft System Center licensed separately
7. End users can use the free Feedback Client to submit feedback to TFS, and access MSDN software to review the app and provide feedback.
8. MSDN Subscription not required. Updates vary by edition.
9. Windows Azure benefits vary by offering
10. Paid MSDN subscriptions will have access at no additional charge when paid plans become available