Quadra

Connecting Technology and Business.

With Excel 2016 Data Analysis has become easier

Faster and easier ways to get data

Before analysis can begin, you must be able to bring in the data relevant to the business question you are trying to answer. Excel 2016 now comes with built-in functionality that brings ease and speed to getting and transforming your data—allowing you to find and bring all the data you need into one place. These new capabilities, previously only available as a separate add-in called Power Query, can be found natively within Excel 2016. Access them from the “Get & Transform” section on the Data tab.

Business Analytics 1

Use these capabilities to connect to data from a wide range of sources—like tables in public websites, corporate data in databases and cubes, cloud-based sources like Azure, unstructured data like Hadoop and services like Salesforce—then bring the data into your workbook’s data model or display them as tables within a worksheet.

The Query Editor, which opens when creating a new query or opening an existing one under Get & Transform, provides intuitive data shaping and transformation options, enabling you to prepare a data set with only the information you want to see and in the way you want to see it. Each step you take to transform the data can be repeated and rearranged to help you work faster and more efficiently. When you refresh the data connections, every step you took to transform the data is preserved. And once you have the information prepared, you can share your queries easily with others.

Additional features available under the Get & Transform section include accessing recent sources you’ve used in previous queries and options to combine queries together. With a premium version of Excel 2016 (MSOffice ProPlus or Office 365 ProPlus) and a Power BI license, you can also use search to discover corporate data available to you across your company that is shared in a corporate catalog or even share your own queries with others.

Integrating the Power Query technology also provides benefits for programmability. With these features integrated into native Excel, you can use VBA and the Object Model to program a related query and transform actions. And if you need more than the predefined actions provided, you can use the powerful query language (M) to create additional actions and capabilities.

Streamlined data analysis

Excel is known for its flexible and powerful analysis experiences, through the familiar PivotTable authoring environment. With Excel 2010 and Excel 2013, this experience was significantly enhanced with the introduction of Power Pivot and the Data Model, bringing the ability to easily build sophisticated models across your data, augment them with measures and KPIs, and then calculate over millions of rows with high speed. In Excel 2016, we focused on making these experiences more discoverable, consistent and streamlined, so that you can focus less on managing your data and more on uncovering the insights that matter:

  • Automatic relationship detection discovers and creates relationships among the tables used for your workbook’s data model, so you don’t have to. Excel 2016 knows when your analysis requires two or more tables to be linked together and notifies you. With one click, it does the work to build the relationships, so you can take advantage of them immediately.
  • Creating, editing and deleting custom measures can now be done directly from the PivotTable fields list, saving you a lot of time when you need to add additional calculations for your analysis.
  • Automatic time grouping helps you to use your time-related fields in your PivotTable more powerfully, by auto-detecting and grouping them on your behalf. Once grouped together, simply drag the group to your PivotTable in one action and immediately begin your analysis across the different levels of time with drill-down capabilities.
  • PivotChart drill-down buttons allow you to drill in and out across groupings of time and other hierarchical structures within your data.
  • Search in the PivotTable field list helps you get to the fields that are important to you across your entire data set.
  • Smart rename gives you the ability to rename tables and columns in your workbook’s data model. With each change, Excel 2016 automatically updates any related tables and calculations across your workbook, including all worksheets and DAX formulas.
  • Multiple usability improvements have also been made. For example, delayed updating allows you to perform multiple changes in Power Pivot without the need to wait until each is propagated across the workbook. The changes will be propagated at one time, once the Power Pivot window is closed.

Business Analytics - 2

Publishing and sharing in Power BI

A report is not complete without being able to share it with the right people. Once you’re finished preparing your data analysis, you can share it with your workgroup or clients through Power BI with just one button. Once published to Power BI, use your data models to quickly construct interactive reports and dashboards. With Excel Online support built into Power BI service, you can also display your fully formatted Excel worksheets as well.

Business Analytics - 5

Taking advantage of Excel Online

With rich features in Excel Online, your peers and clients are able to view and interact with your Excel data analysis and reports from anywhere. All you need to do is share your workbook through Power BI, SharePoint or OneDrive. Recently, we’ve included new features to make it even easier to consume and interact with your workbook from the web, including Search in Filters and the ability to change the settings of a value field in a PivotTable. With Office 365, Microsoft is providing more features and improvements to Excel Online on a regular basis.

Work together in Groups in Office 365

Getting things done at work means sharing information and collaborating across ad hoc groups and project teams.  But, often times the tools we use to bring people together are different in each app—distribution groups in Outlook, Favorites lists in Skype for Business, groups in Yammer.  That’s why Microsoft has introduced Groups in Office 365, so you can easily connect with the colleagues, information and applications you need to do more.

OWA Groups conversation feed

To support a project, one can easily create a new Group and invite colleagues. One can also search for and join existing Groups, which are open by default, to see all discussions, milestones and files and get up to speed quickly.  Of course, one can create private Groups as well for sensitive projects and content.

Groups will show up within the web experiences of Office 365 email and calendar and OneDrive for Business.  Yammer and Lync also will be added to the Groups experience to help users do even more.

Eligible Office 365 plans include the Office 365 Enterprise E1, E3 and E4 subscription, Office 365 Business Essentials and Business Premium plans and Office 365 Kiosk plan.

In some ways, Office 365 Groups behave like traditional email distribution groups in that you can send a message to a group and have it delivered to all the group members. But the problem with distribution groups is that if you’re not a member, you don’t get the information. And when you join, you don’t gain access to previous discussions.

An Office 365 group is a more persistent entity than an email distribution group because while you can use a group like a regular distribution group, it also stores discussions for new members to discover when they join. It’s kind of like an Office 365 Group is a cross between a distribution group and a public folder. And like a distribution group, an Office 365 Group is instantiated as a group object in the directory - in this case, within Azure Active Directory. 

Perhaps a more accurate comparison is to a combination of a distribution group and a site mailbox (or even a traditional shared mailbox). Groups are preferred to site mailboxes if you need a collaboration platform for a new project.

Document Fingerprinting for DLP in Office 365

Information workers in an organization handle many kinds of sensitive information during a typical day. Document Fingerprinting makes it easier for organizations to protect this information by identifying standard forms that are used throughout the organization.

Basic scenario for Document Fingerprinting

Document Fingerprinting is a Data Loss Prevention (DLP) feature that converts a standard form into a sensitive information type, which you can use to define transport rules and DLP policies. For example, organizations can create a document fingerprint based on a blank patent template and then create a DLP policy that detects and blocks all outgoing patent templates with sensitive content filled in. Optionally, organizations can set up Policy Tips to notify senders that they might be sending sensitive information, and the sender should verify that the recipients are qualified to receive the patents. This process works with any text-based forms used in an organization. Additional examples of forms that you can upload include:

  • Government Forms

  • HIPPA compliance forms

  • Employee information forms for HR departments

  • Custom forms

An organization might already have an established business practice of using certain forms to transmit sensitive information. After an empty form is uploaded to be converted to a document fingerprint and set up a corresponding policy, the DLP agent will detect any documents in outbound mail that match that fingerprint.

How Document Fingerprinting works

In the same way that a person’s fingerprints have unique patterns, documents have unique word patterns. When you upload a file, the DLP agent identifies the unique word pattern in the document, creates a document fingerprint based on that pattern, and uses that document fingerprint to detect outbound documents containing the same pattern. That’s why uploading a form or template creates the most effective type of document fingerprint. Everyone who fills out a form uses the same original set of words and then adds his or her own words to the document. As long as the outbound document isn’t password protected and contains all the text from the original form, the DLP agent can determine if the document matches the document fingerprint.

The following example shows what happens if you create a document fingerprint based on a patent template, but you can use any form as a basis for creating a document fingerprint.

Example of a patent document matching a document fingerprint of a patent template

A patent document matching a document fingerprint.

The patent template contains the blank fields “Patent title,” “Inventors,” and “Description” and descriptions for each of those fields—that’s the word pattern. When you upload the original patent template, it’s in one of the supported file types and in plain text. The DLP agent uses an algorithm to convert this word pattern into a document fingerprint, which is a small Unicode XML file containing a unique hash value representing the original text, and the fingerprint is saved as a data classification in Active Directory. (As a security measure, the original document itself isn’t stored on the service; only the hash value is stored, and the original document can’t be reconstructed from the hash value.) The patent fingerprint then becomes a sensitive information type that you can associate with a DLP policy. After you associate the fingerprint with a DLP policy, the DLP agent detects any outbound emails containing documents that match the patent fingerprint and deals with them according to your organization’s policy. For example, you might want to set up a DLP policy that prevents regular employees from sending outgoing messages containing patents. The DLP agent will use the patent fingerprint to detect patents and block those emails. Alternatively, you might want to let your legal department to be able to send patents to other organizations because it has a business need for doing so. You can allow specific departments to send sensitive information by creating exceptions for those departments in your DLP policy, or you can allow them to override a policy tip with a business justification.

Supported file types

Document Fingerprinting supports the same file types that are supported in transport rules.

Limitations of document fingerprinting

The Document Fingerprinting DLP agent won’t detect sensitive information in the following cases:

    • Password protected files

    • Files that contain only images

    • Documents that don’t contain all the text from the original form used to create the document fingerprint

Prevent Data Loss in Office 365

When was the last time you asked your employees to carry your company’s handbook containing all the company policies with them? Do your IT workers know whether a particular email message they’re sending may violate company policy and run the risk of being noncompliant? Are they sure whether an email they’re sending contains sensitive information? Almost every IT worker faces compliance questions like these daily.  

DLP Policy Tips inform your workers in real time. With the DLP Policy Tips in Office 365, admins can inform email senders that they may be about to pass along sensitive information that is detected by the company’s policies-before they click Send. This helps your organization stay compliant and it educates your employees about custom scenarios based on your organization’s requirements. It accomplishes this by emphasizing in-context policy evaluation.

Policy Tips not only analyzes email messages for sensitive content but also determines whether information is sensitive in the context of communication. That means you can target specific scenarios that you associate with risk, external communication for example, and configure custom policy tips for those scenarios. Reading those custom policy tips in email messages keeps your workers aware of your organization’s compliance policies and empowers them to act on them, without interrupting their work.

DLP Policy Tips is supported only in Outlook 2013, but even if your users don’t have the latest version of Outlook, you are still protected from disclosing sensitive data through back-end processing. Admins can configure rules and take actions by setting up DLP rules in the Exchange Administration Center (EAC). This ensures that a single DLP policy controls both the client and server endpoints, minimizing the admin administrative overhead.

How do Policy Tips work? Consider a real-life scenario. A company has an internal policy to warn its employees any time they include sensitive information like a credit card number in email communications. An employee is composing an email to a person, who works outside her organization. She includes credit card information in the mail, and immediately a DLP policy tip shows up in the message in Outlook.

https://officeblogswest.blob.core.windows.net/wp-content/migrated-images/78/1462.DLP_01.png

When you include sensitive information in an email message, a DLP policy tip alerts you before you send the message.

At this point the employee can decide to: send the email message with the credit card information, send the message with the credit card information and click Report to report a false positive, or delete the credit card information before sending the message. If she’s unsure what to do, she can click Learn more to understand her company’s policy, which her admin may have customized. 

Let’s look at another scenario. A company has recently set up a policy that blocks emails containing multiple credit cards or that need to be overridden with a business justification. An employee starts an email message to book the travel for multiple employees in the company and attaches a document that includes the personal credit card information of the employees. A different policy tip shows up, highlighting the new compliance requirement. In Outlook 2013, the attachment that is the cause of concern is also highlighted, making it easy for her to locate the information being questioned.

 

https://officeblogswest.blob.core.windows.net/wp-content/migrated-images/78/5040.DLP_02.png

A custom DLP policy tip alerts you about an attachment that may contain high-count sensitive information.

As these two scenarios show, data loss prevention empowers end users, making them part of the organization’s compliance process and ensuring that the business flow is not interrupted or delayed, because achieving compliance does not get in users’ way. At the same time, data loss prevention simplifies compliance management for admins, because it enables them to maintain control easily through the Exchange Administration Center in the Office 365 admin portal.

Policy Tips are similar to MailTips, and Admins can configure them to present a brief note in Outlook 2013 that provides information about your business policies to the person creating a message. Admins can configure policy tips that will merely warn workers, block their messages, or even allow them to override your block with a justification. Policy tips can also be useful for fine-tuning a company’s DLP policy effectiveness, because they allow end users to easily report false positives. If policy tips are not available to a user in Outlook, admins can still control compliance behavior by setting up rules in the Exchange Administration Center. For example, admins can set up an action to generate incident reports if a particular DLP event occurs. Such incident reports can help tracks events in real time, because a report is generated in real time and sent to a designated mailbox, such as the mailbox for incident manager account.

The figure below shows a sample incident report.

https://officeblogswest.blob.core.windows.net/wp-content/migrated-images/78/0777.DLP_03.png

Admins can generate incident reports for specific DLP events in Office 365.

 -gleaned from Office blogs