Brought to you by our compatibility guru Curtis Sawin.
The primary use for OMPM is to provide details about document conversion issues, and it helps answer the question, “What’s my risk of converting my binary Office file(s) to the Open XML file format?” However, we find that many people use OMPM to help answer the question “What’s my risk of opening my binary Office file(s) in an Office 2010 application?” The result is that we see some people use OMPM for the wrong question, spending significant time, effort, and money using a good tool to provide the wrong information.
For any compatibility project, regardless of platform, you should separate your compatibility tasks into pre-deployment and post-deployment tasks. Meaning, prior to migrating to your new platform (such as Office 2010, Windows 7, or Internet Explorer 9), you should focus your efforts only on tasks that enable you to deploy the new platform. Such tasks should have a clear and direct impact on the ability to deploy your platform. This is why pre-deployment tasks are considered deployment enabling tasks.
Post-deployment tasks are ones that allow you to realize the benefits of your new platform, which could include increased productivity (did I mention Paste Preview?), and lowered costs. Further, post-deployment tasks can position you for future platform migrations. Such tasks are considered environment optimization tasks.
For example, updating deprecated macro code is a post-deployment task, because object model items that have been identified as deprecated from previous versions of Office will still compile, but might not be available in future versions of Office. In other words, the deprecated macro code doesn’t actually block Office deployments. So after you’ve deployed Office 2010, updating your deprecated code will position you to migrate to future version of Office.
Converting documents is also a post-deployment task, because doing so allows you reduce your network storage needs and helps optimize your environment.
As we mentioned earlier, OMPM identifies document conversion issues, not document issues. Meaning, for a specific document, OMPM can help tell you “will it convert” to the latest file format, but it does not tell you “will it work” in Office 2010.
Using OMPM in a pre-deployment manner to help figure out “will my documents work when I open them in Office 2010?” is a common misuse of OMPM. There are a couple reasons for this:
The last bullet is the killer. OMPM provides data for IT Pros to grasp. Often, what we are seeing is customers using OMPM to find document conversion issues, and then focus their testing only on documents with “red” issues. This is an easy way to rationalize an enormous volume of data into something more manageable. “Red” documents are often 5-20% of the total inventory. Rationalizing my inventory to only 5% of what was discovered sounds like an excellent use of my discovery process!
However, this approach has several flaws. As we’ve noted, the most important flaw is that OMPM provides conversion issues, and does not provide information that will help you determine if a red document will work in Office 2010. Additionally, focusing on “red” documents ignores the importance of the document, and treats all red documents as equally important (i.e., docs that must be tested). Thus, while you think you’re saving time, you’re actually wasting time, potentially focusing on documents that have conversion issues, but don’t add value to the business. Lastly, using OMPM in this manner provides a false sense of security. While you can say you’ve focused your testing efforts on documents that have been reported to have only red issues, you can’t say that you’ve gotten closer to determining “will my stuff work” in Office 2010.
We are finding that companies spend 12-18 months in getting ready to deploy Office 2010. Meaning, once the decision is made to deploy, it could be up to a year and a half until your end-users are using the new version of Office. Most of this time is eaten up by lengthy (and costly) document assessment using OMPM. In fact, we’re finding that people who do NOT use OMPM prior to an Office 2010 upgrade are deploying faster, cheaper, and without any additional risk.
A new feature of the 2010 version of OMPM is that the tool identifies “macro issues.” In short, it will provide two data points: a count of all potential object model issues, and a count of all potential 64-bit compatibility issues.
The object model issues, listed as Functionality Issue Count in the OMPM reporting tool, summarizes the total number of items in macro code that have been removed, changed, or deprecated from previous versions of Office. The 64-Bit issues, listed as x64 Compatibility Issue Count, lists the sum of all macro code declarations that are not explicitly listed as “safe for 64 bit Office.”
With this improved functionality, many feel that this insight is invaluable and must be uncovered prior to deployment. For instance, you wouldn’t want to use a document in Office 2010 that had 88 functionality issues and 3 x64 Compatibility issues, would you? That depends on:
If you’re not deploying 64-bit Office 2010, you can ignore all the data in the x64 Compatibility Issue Count column in the OMPM reporting tool. It doesn’t provide value in this situation, and is just noise.
The Functionality Issue Count data is a summary of removed, changed, or deprecated object model items. Most of these items are not impactful, but some of them may be impactful. How can you tell? Unfortunately, OMPM doesn’t make this distinction. So looking at the data doesn’t give you much to go on. Check out the article Understanding potentially impactful changes in the Office 2010 object model for more details about how object model changes may affect macros.
Lastly, while OMPM can tell you which documents have the most functionality or x64 macro issues, OMPM cannot tell you if the document/macro is important to the business. It would be a waste of time to spend testing and remediation cycles on documents that don’t provide business value. So using the volume of macro issues to help rationalize which documents should be tested often leads to inefficiencies.
Much of this article has described what not to do…which by itself is not so helpful. So if using OMPM for discovery documents and macros is bad…what is good? Start with the end-users. Canvas your customers. A major benefit (and challenge) of Office is that end-users can build their own solutions using Office, and that Office solutions are not managed by the IT organization. Further, many companies do not have the IT organization manage their Office documents, so the IT department has little insight into what Office documents are important to run the business.
You’ll find it is MUCH faster to partner with project managers, relationship managers, or designated business leads to identify what documents are critical to the business than it is to use OMPM to scan your entire environment and focus on the (gulp) wrong data. This partnering can also be leveraged for other IT initiatives and projects and help make enacting change in your environment more agile.
Most compatibility projects, regardless of platform, use the flow of “Inventory, Rationalize, Test, and Remediate” and with Office, it seems logical to use OMPM for the discovery, rationalize by filtering the “yellows” or “reds,” and testing and remediating your smaller subset. What this is inadvertently doing is rationalizing your list based on the wrong criteria. It’s kinda like car shopping based on color first. “Honey, here is a list of all the blue cars that you can pick from.” When you’re focusing your testing/remediation on the wrong set of data, you’re not reducing your risk. If fact, you’re increasing your risk by not focusing on the correct data.
Working with the business areas first to identify critical documents/solutions will provide an efficient means to perform the discovery and rationalization at the same time, as the business is validating the data as its being generated. The result is increased efficiency (which reduces time/cost) and lowered risk (by focusing on the right data).
OMPM is an excellent tool to perform a specific task. Using OMPM to find document conversion issues, and using that data to determine if there’s a business justification for converting your documents after your Office 2010 deployment is completed is a great way to obtain value out of your investment, and realize potential savings. Using OMPM to answer the wrong question will lead to a costly and inefficient upgrade project that will hinder your business’s agility and delay the productivity benefits that Office 2010 brings to your customers.
The concepts in this article are explained in more detail in this 1-hour video Solving Office Compatibility to Accelerate Office Deployments, recorded from the Microsoft SharePoint Conference in Anaheim, CA. Below is an intro to the video:
Office file and solution compatibility causes concern for organizations when they begin planning for an Office upgrade. This typically leads to extended deployment projects delaying the realization of the new version value. The key to keeping your deployment project on track is utilizing the right process and leveraging tools appropriately to understand the potential risks. This session will demonstrate how the right approach will address the expensive/long assessments, fear of the unknown, and increased costs. Meet the Office Compat team and learn how to leverage the programs and resources to expedite Office 2010 or Office 365 client deployments.
Using OMPM Part 1 - Identifying Document Conversion Candidates and Estimating Storage SavingsUsing OMPM Part 2 – Performing Bulk Conversion
In Part 1 of this series, we discussed how to use OMPM to identify “conversion candidates,” which are documents that pose virtually no compatibility risks when you convert them from the binary format (e.g., xls, doc, and ppt files) to the Open XML format (e.g., xlsx, docx, pptx).
Let’s take a look at the process of actually converting the documents. This involves using the Office File Converter Tool (OFC.EXE), and using an exported list from the OMPM Reporting Tool (OMPM.accdr).
To recap, you can use the OMPM reporting tool to create a “low risk” filter to identify documents that:
The following WHERE clause can be used to meet this criteria:
WHERE MaxIssueLevelID > 2 AND DATEADD(d,-30,GETDATE()) > ModifiedDate AND FileID not in (SELECT FileID from Uv_FilterMacroIssue)
To convert files that meet these criteria, you can use the OMPM reporting tool to export the filtered list. After you’ve selected Apply Filter to add your criteria, select the Export… button to export the list of files.
The output will be one or more XML files that contain the full path to all the files in your result set. The folder in which the files have been exported will be referenced by the file conversion tool, OFC.exe
Next, open the OFC.ini file (found in the “TOOLS” folder when you download and extract the OMPM Toolset), and modify the FileListFolder item to point to the same folder where you exported the files. For example, if you exported the file list to the D:\DataExport folder, the FileListFolder item would look like this:
FileListFolder=D:\DataExport
Using the FileListFolder item will result in OFC.exe converting all the files that have been exported by the OMPM reporting tool. Further, using FileListFolder rather than the [FoldersToConvert] section of the OFC.ini allows you to ensure that you’re only converting files that you’ve specifically tagged as low-risk “conversion candidates.” Using [FoldersToConvert] section simply points OFC.exe at one or more folders and says “convert everything.” In some cases this can be helpful, but if your goal is to selectively convert files while retaining the ability to automate the conversion, using FileListFolder provides much more control.
To facilitate moving the converted files to the original location(s), the ofc.ini file has a [ConversionInfo] section that allows you specify the destination folder structure. For example, you could reproduce the folder structure by specifying the following:
[ConversionInfo]
SourcePathTemplate=*\*\*\*\*\*\*\*\*\
DestinationPathTemplate=X:\*1\*2\*3\*4\*5\*6\*7\*8\*9
This would result in the converted files being placed in a folder structure similar to the source folder in on the “X:\” drive. OFC.exe also adds the computer name to the destination path. If you can reproduce the folder structure, you’re in position to implement a repeatable process to move the new files and delete the old ones. Even better, you could create a script to automate such a process.
Also, OFC can convert to a maximum depth of 10 folders. For example, DestinationPathTemplate=I:\Converted\*1\*2\*3\*4\*5\*6\*7\*8\*9\ works correctly. However, DestinationPathTemplate=I:\Converted\*1\*2\*3\*4\*5\*6\*7\*8\*9\*10\ does not work.
One mitigation for this is to map a drive letter to a folder structure (ie, connect the “x:\” drive to \\myserver\myshare\folder1\folder2\folder3\folder4) and perform a find-and-replace in the exported XML file(s) to replace the folder structure with the drive letter.
The impact of this limitation should be minimal, but it’s a pain to troubleshoot, so we wanted to make sure you’re aware of this limitation.
Below are some screen shots that show examples of the various components.
Here is an exported XML file from the OMPM Reporting tool:
Notes:
Here is an OFC.INI file (with all the comments removed):
You can find more information about the items in the OFC.ini file in the TechNet article Convert binary Office files by using the Office File Converter (OFC) and Version Extraction Tool (VET)
Here is my source location of my legacy files:
The next step is to run OFC.exe from a command-prompt. No command-line parameters are needed if you have the ofc.ini in the same folder. Here is a screen shot of OFC.exe running:
Below is a screen shot of the resulting folder:
Check out the folder structure D:\Converted\Ninja99\f$\Converted. The Destination Path includes the computer name and drive letter (or UNC name) in the folder structure. This facilitates moving the files back to the original location.
Also, note the Date Modified and Size columns. The conversion process retains the original modified, accessed, and created time stamps (which is good if you have archive/storage solutions that are triggered using these time stamps), and the sizes are significantly smaller than the previous versions.
So keep in mind that using OFC.exe to “convert” files will require you to clean up the legacy files and replace them with the converted ones. Thus, when determining the ROI of a bulk conversion project, the time investment of this clean up activity must be considered.
Performing bulk conversion is an environment optimization task that will help you realize the value of your investment in Office 2010 by reducing your storage needs, which will save you money. Bulk conversion should be considered an optional task, and it should not be performed when preparing to deploy Office 2010, it should be used only post-deployment...and on low-risk files.
Using Office Migration Planning Manager (OMPM) to identify “high-risk” binary format documents—that is, xls, doc, and ppt files that will have conversion issues--is a great way to determine which documents should not be converted after an Office 2010 deployment. Conversely, you also use OMPM to identify “low-risk” binary documents that are good candidates for conversion to Open XML, and then use another OMPM tool, ofc.exe, to bulk convert all the identified documents. Bulk conversions, you ask? Although many companies tell us they aren’t interested in bulk converting documents, and we actually recommend against converting documents as part of an Office 2010 deployment project, there are still good reasons to bulk convert: namely, reducing your network storage needs by 50% or more. This can translate into significant (and measurable) financial savings.
The overview seemed to provide a bit of a contradiction. First, we say “don’t convert as part of an Office deployment project,” and then we immediately get excited about some benefits of conversion. What gives?
First of all, converting documents as part of an Office deployment project can lead to problems, especially broken links. Office 2010 uses Compatibility Mode when opening binary files and disables certain features that are not backwards compatible, ensuring that the binary files remain compatible with previous versions of Office. Using Compatibility Mode requires no effort or configuration on your part, and users can continue working in their files as they did with previous versions of Office. So, if the new features are not desired in existing files, why do the extra work? Part of the Office 2010 migration project should be to identify faster, simpler ways to migrate to the new platform. Not taking on work that doesn’t add business value is a great way to save money. So, when preparing to deploy Office 2010, make sure you’re focusing only on tasks that are “deployment enablers.”
Spoiler alert: Using OMPM (or other tools) to scan your environment for document conversion issues is NOT a “deployment enabling” task. More on this later.
Once you’ve migrated to Office 2010, you’re in a position to take advantage of the new features in all new documents you create. This is also a great time to determine if performing bulk document conversion is worth the effort. Leveraging features that enhance productivity (helloooo Paste Preview and SmartArt!) and reducing your network storage needs are great “environment optimization” tasks that allow you to realize the value of your investment in Office 2010.
Using OMPM to identify low risk files – aka, conversion candidates – is a post-Office deployment task that helps you determine if bulk conversion is worth the effort. The data gathered by OMPM can be used to help determine the return on investment (ROI) of bulk conversion. Meaning, by using OMPM, you can help answer the question “What will my storage savings be if I convert a bunch of documents?” In an environment where the IT organization implements a charge-back model for network storage, using OMPM could also help answer the question “How much money will I save if I convert my docs?” Often, IT organizations are viewed as “costing the business money.” Using OMPM can help change that perception to demonstrate how IT is “saving the business money.”
Documents that are good candidates for conversion are ones where the projected impact to the business is minimal. When defining “low risk” for your organization, you could apply specific business rules, such as “exclude recently modified documents,” and combine that with specific data returned from OMPM, such as “exclude documents with yellow or red document conversion issues” and “exclude documents with macro issues.” What remains are documents that meet the following criteria:
OMPM categorizes potential document conversion issues into categories that indicate the severity of the issues. “Green” issues are mostly benign and will most likely have no impact. Examples of such green issues are Excel files that use labels in formulas (which are automatically converted in Excel 2010) or have charts in them. “Yellow” or “Red” issues are potentially more severe, and conversion of such documents may result in data or functionality loss.
Files with macro issues fall into two categories: (1) files that contain macros that use object model items that are either changed, removed, or deprecated since previous versions of Office, and (2) macros that call functions that are not specifically marked as compatible with 64-bit versions of Office. While OMPM doesn’t provide detailed information about the impact of these macro issues, we are excluding them from our potential conversion list…to reduce our risk.
Macro issues are listed separately from Red, Yellow, and Green issues. Meaning, a file could have 3,245 macro issues identified and listed as a “green” document for conversion issues. Or it could be listed as file with no conversion issues. This is an important distinction to note when we build our “low risk” filter.
We are also excluding recently modified files. For instance, we won’t convert any file that has been modified in the past 30 days. This will further ensure that we are only converting documents that will most likely never be used. Even if some of them will be used, we’re only converting ones with virtually no conversion risk. If 30 days isn’t sufficient for your environment, increase that number.
Note: the OMPM reporting tool only exposes the “last modified” date of files. It does not provide information about the “last accessed” date of files. Boo!
The basic process to convert your documents is below:
Step 1 – Gather your data:
To learn more about how to do these steps above (except for identifying your storage), check out our TechNet documentation for OMPM.
Step 2 – Analyze your data:
To determine which files are conversion candidates:
To estimate the storage savings of conversion:
Step 3 – Do the work:
To convert the conversion candidates:
This article will cover step 2. A separate article will discuss step 3. Why two separate articles? Step 2 is all about determining “Is it worth it to convert my documents?” If you determine that it’s worth it (from a financial perspective), go on to step 3, which is about performing the work. If you determine it’s not worth it, stop reading, and provide the data to you management that justifies your decision. You’ve now just saved yourself and your company some effort, which you’ve determined is not worth potential savings.
Open up the OMPM Reporting tool (OMPM.accdr) and select the OMPM Compatibility Link. In the Select a File Filter section, navigate to the bottom of the section and select the Customize SQL button. The following WHERE clause can be used to meet or criteria:
After adding the query, selecting the Apply Filter button will return all the files that meet the criteria.
Here’s what it looks like in the OMPM reporting tool
In the above example, about 74% of my documents are conversion candidates, as they meet my low-risk criteria.
If you’re not familiar with SQL, the above may look a bit confusing. The table below breaks down the query.
Now that I know how many documents (and what percentage of my documents) are conversion candidates, it would be valuable to know my estimated storage savings if I decided to convert them. While our documentation on TechNet indicates the Open XML files are “up to 75% smaller” than binary files, in the field we’re seeing 50-60% reduction in size when converting documents. For planning purposes, we recommend you estimate a 50% reduction in storage needs when converting document to the Open XML format.
The OMPM reporting tool doesn’t provide the cumulative size of all files listed. So you could copy and paste (blecch!) all the data from the OMPM reporting tool (using the Scanned Files tab) into Excel (for example) and then sum up the File Size column. Not too elegant and quite cumbersome.
An easier method is to connect to the OMPM database using SQL Server Management Studio (SSMS) and execute a query against the database directly. Here are some simple steps you can perform to do this:
1. Open SQL Server Management Studio.2. Connect to the server that contains the OMPM database.3. Select the New Query button.4. In the query editor window, copy and paste the below text:
SELECT
SUM(Cast(Size as BigInt))/1024/1024/1024
FROM
Uv_File
WHERE
(MaxIssueLevel = 'No Issues' or MaxIssueLevel = 'Green')
AND
DATEADD(d,-30,GETDATE()) > ModifiedDate AND
FileID not in (SELECT FileID from Uv_FilterMacroIssue)
This is very similar to the filter used in the OMPM Reporting tool, except we have access to more tables and views. As a result, we are able to use the MaxIssueLevel fields to specify “Green” and “No Issues” rather than the MaxIssueLevelID values. This makes the query a bit easier to understand.
The result is a single value that represents the total number of gigabytes used by all of the files that are conversion candidates. The amount of data were evaluating could be huge, and the file size is represented in bytes. This is why we’re using the CAST function to convert the File Size data into a data type that can handle very large numbers. We’re then dividing by 1024 three times to convert the bytes to kilobytes, then megabytes, then gigabytes.
Here’s an example of this query and the resulting sum.
In the above screen shot, we see that 44 GB are being used by all of our low-risk documents. To estimate the storage savings, we’ll use a very simple formula:
This gives you the ability to determine, “Is it worth it?” Meaning, we can take the estimated storage savings and determine if the ROI of performing a bulk file conversion is worth the investment. Again, if you’re in an environment where the IT department implements a chargeback model for network storage, you can now estimate the amount of money you will be saving your customers. Typically, chargebacks are recurring, so you would provide the amount of money you would save…per year! (Side note: Some people LOVE to see 3-, 4-, or 5-year forecasts. Being able to provide “Here is my 5-year savings forecast for this project” could really make people notice the value of document conversion!)
The recommended use for OMPM is to identify documents which are good candidates for conversion and then convert those documents. This is an activity that is best undertaken after Office 2010 has been deployed. In short, use OMPM as an analysis tool to help determine if there is an adequate return on investment for a document conversion effort.