市场分析数据报表和分析平台(一类是用于业务的数据驱动，对接Hadoop一类的大数据平台。一类是用于企业内部包括财务、人力的运营管理。) -国外 --Tableau --qlikview --SAP BO、 --Oracle BIEE --Power BI 微软国内BI工具：FineBI、永洪、BDP等等 -国内 --神策分析 --FineBI http://demo.finebi.com/WebReport/ReportServer?op=fs --Finereport http://demo.finereport.com/ReportServer?op=fs --阿里的 --永洪 --BDP https://me.bdp.cn/home.html --数据观 https://shujuguan.cn/product

数据整合数据准备是数据分析的第一步，商业数据分析平台一般都可以方便快捷地将你所需要的数据进行集中，轻松解决数据分散、类型不同等问题。简化数据获取流程，节省了整合、清洗数据所花费的大量时间，使你无需再关注数据存储与管理，专注于数据分析。

很多工具都提供了多种灵活的方式帮你完成数据接入，从本地的数据库、Excel文件到使用第三方服务生成的数据，以及散落在网络中的公共数据，一应俱全，轻松便捷打破数据孤岛；配合上相应的数据管理服务，让数据流转尽在掌握，一步迈入大数据时代。

数据接入五步走，包含文件上传、整套直连数据源和三种同步工具：

文件上传：上传本地文件进行分析，支持Excel与CSV、PDF，同时支持文件的追加、替换、下载等操作。
公网直连数据源：最简单快捷的方式，通过平台直接连接外部数据，实现数十种数据一键对接，涵盖如百度推广的第三方服务数据、轻量级数据库连接、如天气数据的公共数据。
内外网隔离同步客户端：本地海量数据接入解决方案，适用于内外网隔离的本地亿级数据库及其他不易对接数据源。
文件同步宝：办公文件同步工具，支持Excel与CSV，使零散文件成为数据生态中的重要一环，架起数据分析的桥梁。
OpenAPI：面向开发者的可编程数据推送接口，可根据所需场景自行开发数据同步程序，为愿意自行开发的用户提供便利。

数据源

1 数据库
    1.1 MySQL
    1.2 SQLServer
    1.3 Oracle

MySQL，SQL Server，Oracle，PostgreSQL，Hive，Access，SQLite，Firebird，DB2。 2 网络营销 2.1 百度搜索推广 2.2 百度搜索推广（小户） 2.3 百度网盟推广 2.4 百度实况 2.5 百度指数 2.6 优酷土豆DSP 2.7 搜狗搜索推广 2.8 搜狗搜索推广（小户） 2.9 搜狗网盟 2.10 搜狗网盟推广（小户） 2.11 今日头条 2.12 神马搜索推广 2.13 360点睛搜索推广 2.14 腾讯广点通 2.15 新浪扶翼推广 2.16 有道智选 2.17 新浪微博粉丝通 2.18 360展示网络 2.19 腾讯智汇推 2.20 有道易投 3 在线客服 3.1 53客服 3.2 快商通 3.3 百度商桥 3.4 TQ客服 3.5 Live800 3.6 诚信通 3.7 美洽客服 4 数据统计 4.1 BDP网站统计 4.1.1 功能说明 4.1.2 使用流程 4.1.3 部署检测 4.2 百度统计 4.3 CNZZ 4.4 友盟应用统计 4.5 Google统计 4.5.1 准备工作 4.5.2 添加数据源 4.5.3 自定义报告配置 4.6 GrowingIO 5 企业管理 5.1 Excel 5.2 CSV 5.3 明道OA 5.4 锦衣卫 5.5 EC 5.6 金数据 5.7 伙伴云 5.8 支付宝口碑商户 5.9 支付宝支付 5.10 管易ERP 5.11 微信支付 6 公共数据 6.1 天气数据 6.2 居民收支数据 6.3 人口数据 6.4 国民经济数据 6.5 移动应用排名数据 6.6 农副产品价格数据 6.7 采购经理指数 6.8 居民消费价格指数 6.9 本地生活数据 6.10 视频播放数据 6.11 地理区域数据 6.12 人民币汇率数据 6.13 企业信息数据 7 同步工具 7.1 文件同步宝 7.2 同步客户端 7.3 OpenAPI

Heywood Healthcare Has Used Monarch for Data Prep for 13 Years

Elaine Smith works at Heywood Hospital in Massachusetts and has been a Monarch user since 2003.

Monarch allows Elaine to streamline her data extraction processes and focus more attention on creating actionable insights from her accounting reports.

Dana-Farber Cuts Time for Data Access and Transformation Using Monarch

Tom Pellerin at Dana-Farber Cancer Institute has been using Monarch for 15-16 years.

As a reimbursement manager, he handles monthly net-revenue calculations that require combining disparate reports for analysis.

Monarch allows him to extract and blend data from text reports, PDF reports, and databases to analyze variances from revenue on a monthly basis.

My name’s Tom Pellerin and I work at Dana Farber. I’ve been using Monarch probably for about 15 to 16 years. I’m a reimbursement manager so I do monthly net revenue calculations that we use various reports for and use Monarch to help expedite and analyze the data that we’re using. It allows us to take data from PDF reports that we might need to extract some of the information from and then analyze that data. We extract it and put it into Excel using Monarch. It also allows us to access various databases and pull that information into Monarch and filter things down to let us analyze various pairs. And, also we pull in text reports to get GL data. We use that to look at variances from revenue and expenses on a monthly basis. Also we use Monarch for our various ad hoc analysis to basically help us gather the information that we need and filter things down. You spend a lot of time adjusting, eliminating page headers if you have to parse things in Excel. You’d have to parse out all the page headers, this eliminates that. You’d have probably a lot of mistakes from data that you’re not capturing or wrong numbers that you wouldn’t be capturing. If you build summaries in the model it allows you to calculate back that the data you extracted matches the reports. At Datawatch Monarch it definitely is a huge time saver and it is a tremendous analytical tool, and I would recommend it to anybody. Buy Datawatch Monarch.

Datawatch在此有两个意思，一个指的是美国上市公司Datawatch Corporation (NASDAQ-CM:DWCH)；Datawatch创建于1985年，提供能对任何数据进行优化的可视化数据恢复软件（无论数据的种类、数量或速率如何，都能进行优化），从而推出了用以发现改善业务的高价值解读的新一代分析软件。软件具有将报告、PDF 文件和 EDI数据流等结合了实时流数据的结构化、非结构化和半结构化数据源整合进丰富可视化分析应用的独特能力，让用户能动态发现对其业务的任何运营方面都能带来影响的关键因素。对任何数据集都能执行可视化恢复的这一能力使 Datawatch 在大数据和可视化市场独树一帜。全世界各种规模的机构都使用了Datawatch，包括包括《财富》百强榜上的99家公司。Datawatch总部位于马萨诸塞州切姆斯福德，在纽约、伦敦、慕尼黑、斯德哥尔摩、新加坡、悉尼和马尼拉设有办事处，合作伙伴和客户遍布全球100多个国家。

另外Datawatch指的是一款大数据可视化产品，也是本文要重点说的大数据可视化技术。一、Datawatch产品概述

Datawatch开发的数据可视化工具可以帮助公司每个层级的管理者、分析员和决策者监控和分析数据//www.dbbit.cn，从而增加公司收益，提升业务效益。该平台和主要特点包括：

1、最全面直观的视觉信息分析能力。 2、无缝对接任何数据源。 3、快速开发，部署和培训，确保快速有效的投入使用。 Datawatch产品图

Datawatch产品图二、DatawatchDesktop介绍

DatawatchDesktop是一款用于实时数据处理、数据可视化和大数据分析的软件。处于这些新一代分析工具的最前沿。包括DatawatchDesigner和Datawatch Modeler。DatawatchDesktop允许用户根据任何数据源设计可视化以及设计和构造模型，可用于设计和开发用于个人使用或部署在服务器的Datawatch环境模型。

Datawatch桌面的搭建，满足了大数据的需求、提供了互动性挖掘功能，不受数据类型（种类）、大小（容量）或者是传输时间（速度）的影响。

①可视化数据发掘。Datawatch的专利StreamCube内存分析引擎，支持动态整合和直观的分段分层。通过简单拖放，就可以在仪表盘中建立层级关系和过滤器，更直观地显示出异常值，并查看数据子集之间的相互关系。在应对大数据的容量和类型时，需要更强大的时间序列可视化功能，比如Datawatch特有的范围图（HorizonGraph)，能在单个屏幕上对比多数列。Datawatch桌面还能提供一些列专门为分析历史数据设计的专业视觉化系统，更加简易，更加高效。预置连接器，能简捷地获取和合并来自数据源的信号，包括来自信息中介的数据源，以及来自复杂事件处理引擎的数据。在短短几分钟内，就能预见和分享新的见解。

②数据的动态及静态分析。Datawatch桌面具有独特的动态数据可视化能力——通过使用来自CEP引擎和消息中介等来源的数据源，分批地将数据信息不断推进系统之中。所以能切实看到正在发生的事情，全面了解业务绩效的表现。

③从现存报告中抽取和传递数据。数据各不相同，很少能呈现出可供分析的完美结构。很少能呈现出可供分析的完美结构。某些公司内最具价值的信息常常被深锁在静态运营报告中，这些报告能够提供所需的可视化信息集，但是缺乏灵活性。有些关键数据甚至来自公司外部（如发票、报表、表单、市场数据等），但是你无法访问底层系统。Datawatch桌面允许用户访问、抽取任何数据信息并将其转化为实时数据，供显示、分析并与其他用户以及系统分享。不必编程，企业用户便可以在Datawatch桌面上打开报告或文件，即点即选，数据立即就能提取出来。系统创在了可复用模型，定义了数据到行和列的转换。仅需一次点击，就能将最新的数据集显示于仪表盘上，开始可视化数据发掘工作。 Datawatch产品图

Datawatch世界杯大数据分析图示，点击可放大 Datawatch

Datawatch产品演示，点击可放大 Datawatch

Datawatch大数据分析图示，点击可放大

（1）DatawatchDesigner可视化建模工具

DatawatchDesigner是桌面创作工具，它允许用户组装和发布新的检测和分析以及仪表盘到网上，能快速分析设计监测和频繁更改的大型数据集。该软件集成了多种专为快速理解和解释信息的可视化Datawatch使用我们的StreamCube™OLAP数据模型上的即时数据的汇总与切片与切块。DatawatchDesigner擅长在三个方面：一是能将商业智能转化成直观的、可在学术上验证图形显示，易于理解和使用；二是能够连接到任何数据源。DatawatchDesigner是专门为监测和频繁更改大型数据集的快速分析所设计的。该软件集成了多种专为方便用户快速理解和解释信息的可视化Datawatch，并能适应在StreamCube™OLAP数据模型上的即时数据的汇总与切片和切块。

DatawatchDesigner在工作组级别、甚至在公共网站级别部署整个企业，大部分设施均达到并且具有扎实的、系统的工作。DatawatchDesigner具有良好的可扩展性，易于管理并且需要最少的持续的IT支持。用户可以在短短几个小时内使系统运行且能够处理最复杂的数据源，甚至是正在不断更新的数据库。

（2）DatawatchModeler数据挖掘与分析模块

Datawatch Modeler（原名Datawatch ModelerProfessional）可以处理所有的数据，无论是结构化数据源，还是传统的非结构化或半结构化的EDL流、PDF文件、报告或文本文件。在DatawatchModeler中数据都可以变得标准化和结构化。

Modeler可以评估、组织和集成信息，为每个用户提供一个全方位观察任何类型的业务问题和机会。此外，通过部署Datawatch可视化数据库探索解决方案，Modeler能为个人用户提供价值，并且将价值扩展到整个企业。三、DatawatchServer

为了充分发挥大数据的真正潜力，用户必须能够快速地分享每一份数据并且让数据覆盖整个企业。有了 Datawatch Server，每个部门的用户可以通过包含大量信息的互动式仪表盘戒一个安全的、可扩展的解决方案，快速而方便地发布和共享有价值的信息。

无论是在 web 浏觅器、平板电脑还是使用着 HTML 5最新技术的智能手机上，都可以通过一个丰富的交互式环境，将结极化数据、非结极化戒半结极化数据源 PDF 文件和EDI数据流和实时来源(如 CEP 引擎，分时数据或机器数据)迚行实时展示和分析，以便仍各个维度更加深入地了解公司状况。

（1）对您的大数据进行交互式的数据发现。

作为 Datawatch 可规化数据发现解决方案中一个不可分割的部分，无论任何类型，大小或交互速度的数据都可以通过Datawatch Desktop进行交互式的探索。通过这个解决方案，您可以利用创建模型，轻松地自动地从各种类型的现有报告中抓取和转换数据，例如:

① ERP， BI， CRM不其他LOB和遗留系统，像文本文件，PDF和XPS文件 ② 电子表格和桌面数据库，如Excel和Access ③使用OLEDB和ODBC的关系数据库 ④CEP引擎、消息代理戒分时数据等实时数据源

接着， Datawatch Server 将这些模型和现有的报告系统输出，存储在一个安全和网络化的仓库内，用户能够很便捷地在web 浏觅器访问并且能够在企业内发布。任何人都可以快速、便捷和很安全地访问任何存储报告、报表、发票、日志和 PDF文件，实现可视化并且动态转化为鲜明的报告数据。

（2）分析数据流和历史数据。

让 Datawatch 在数据可视化发展中独树一帜的是它可以将静态和实时数据可视化的能力——通过使用 CEP引擎和消息代理等这类不断实时推送信息到系统的实时数据源。当用户发现异常信息时，可以结合实时数据和历史数据，在通过 DatawatchServer便可以清楚的清楚地了解到正在发生的事情，以便更快更好地做出决策和采取行劢。

（3）不仅仅是大数据，更是敏捷的数据。

在这商业飞速发展的今天，随着对大数据的价值的全新认识，公司不仅需要访问数据的能力，还需要快速的访问到数据。Datawatch是唯一能实现快速和便捷地访问数据，并且让仸何人都能从任何数据源中以最快的完成大数据可视化的解决方案。

Monarch For Qlik Thumbnail http://www.datawatch.com/monarch-data-preparation-for-qlik/ 假如你已经在使用自己研发的或者外购的分析工具来发现新的商机。但和大多数机构一样，你并不能直接访问你所需要的底层数据。通过需要花费大量的时间来准备数据，自然而然留给分析挖掘的时间就很有限。 Datawatch 就是这一种快速简单的解决方案可以为诸多分析工具准备数据

自动从现有的报表、网页、PDF文档中抽取数据这样子你就可以拿到原本被锁在SAP Cognos 等一系列现有系统中的数据
从所有关系型数据库、salesforce、Hadoop、NoSQL和其他数据源从访问数据
通过简单的点选即可实现80多种数据处理功能
将数据导入到分析工具当中

You’re already using Qlik to uncover new business insights. But like many organizations, you still don’t have access to the underlying data you need. And you’re spending too much time preparing data and not enough time analyzing it. Datawatch is the fastest and easiest solution for Qlik users to prepare and blend all data.

With Datawatch you can:

Automatically extract and use data from existing reports, web pages and PDF documents so you can finally use the data locked in SAP, Cognos, legacy or any application
Access and blend data from all databases, Salesforce, Hadoop, NoSQL and other sources
Over 80 functions to manipulate and enrich data with simple point and click
Export prepared data directly to Qlik

Posted by Ellen Wilson on February 15, 2017

In a world where the amount of data and complexity of sources is rapidly growing and expanding, it is becoming more essential for data to be easily organized for analysis. This process typically includes manually converting data from one raw form into another format to allow for more convenient consumption and organization of the data. This manual process of data extraction, especially from PDF reports, is an arduous task for business users in every industry.

In many cases, business users must spend hours re-keying data from PDF reports into a worksheet. Once this process is complete, users still have to blend the data with other datasets before they can begin analyzing the information. Often VLOOKUPs are used, but as many analysts know, this formula leaves much to be desired when fields are formatted differently from one report to another. Therefore, extracting data from a PDF report and blending it with other datasets could take hours, days or even weeks each time.

For business users who spend too much time manually extracting and manipulating data, there is an easier way. With Datawatch Monarch PDF to Excel conversion, which has been around since 2005, data extraction problems are issues of the past. Monarch dramatically reduces the time spent collecting and organizing unruly data before it can be utilized, due to its intelligent recognition algorithm that automatically identifies the structure of the data in a document. Datawatch Monarch’s sophisticated PDF report extraction technology sets it apart from all other data preparation tools on the market. Just ask MasterCard’s 13-person reconciliation team, who each week spent 40-80 hours re-typing data line by line from multi-page PDF reports into Excel. Using Monarch, MasterCard was able to gain back the 40-80 hours spent on data extraction, and refocused its attention the data analysis and strategy.

Monarch’s simple, user-friendly PDF to Excel report extraction is a powerful way to help organizations of all sizes save time, money and effort while unlocking hard-to-reach data. Learn more about Monarch’s capabilities by downloading our eBook, 10 Ways Data Preparation Can Enhance Excel or starting your free 30-day trial of Monarch.

BEDFORD, Mass. – March 1, 2017 – Datawatch Corporation (NASDAQ-CM: DWCH) today announced that the Datawatch Monarch self-service data preparation platform is in high demand among healthcare organizations seeking to overcome the common hurdles to data access, reconciliation and reporting. In fiscal year 2016 alone, 118 healthcare organizations turned to Monarch to radically expedite data analysis and fact-based decision making. More than 720 hospitals and other healthcare services providers now rely on Monarch to improve the preparation and analysis of patient, physician and financial data and gain insights vital to driving down operational costs, increasing productivity, maintaining regulatory compliance and improving quality of patient care.

According to Grandview Research, the global healthcare analytics market will grow to $42.8 billion by 2024 as organizations look to leverage data for financial applications, operational and administrative purposes. This surge is being driven by the increased use of electronic health records (EHRs) and the digitization of financial records and insurance claims processing. Self-service data preparation solutions are vital to ensuring that all the data critical to the analytical processes are pulled into the proper format, thus confirming the highest data quality.

Datawatch Monarch allows healthcare administrators to easily access, manipulate, enrich and combine disparate data from virtually any source, including EHRs, HL7 messages, 835/837 insurance remittance forms and claim denial documents, into a secure database or reliable spreadsheet. Users can eliminate manual, time-intensive data entry and reconciliation and instead spend more time analyzing information to make better, faster decisions related to HIPAA compliance, managing cash flow, cutting costs and identifying gaps in the revenue cycle process. Monarch also allows data masking that redacts certain parts of the patient’s information like their Social Security Number, medical history and other details to ensure privacy and security. Because the platform also empowers organizations to unlock data within PDFs, HTML files and static reports, they can get a broader view of data for analysis to identify trends in administrative processes, patient demographics, medical histories, diagnoses, medications and lab results.

Each day, Datawatch Monarch is being used to solve data problems that have plagued healthcare organizations for decades as well as address new challenges brought on by the exponential growth of data. Some of the most common use cases for Monarch among the existing customer base include:

Accounting and Finance

Datawatch Monarch is the industry standard for data preparation for revenue cycle management. The platform streamlines the collection, reconciliation, scrubbing and submission of patient healthcare and financial information, helping organizations to recognize and report revenue faster.

Pam Klein, manager of support systems at Financial Recoveries, a medical billing and collections firm for hospitals and medical practices, uses Monarch to manipulate the healthcare data flowing in and out of her office, “I’ve used Monarch for years to convert our clients’ data files into our system, or to manipulate our own data to our clients’ exact import specifications. This was just not possible before and my company would need to spend thousands of dollars for other firms to prepare the data for me. Monarch has saved my life for over 20 years.”

Data extraction is a critical reason why Tom Pellerin, reimbursement manager at Dana-Farber Cancer Institute uses Monarch, “Our monthly net-revenue calculations are based on several different PDF reports and databases, and Monarch helps to expedite this process by gathering, filtering and blending data into an Excel spreadsheet. It is a significant time-saving solution and a tremendous analytical tool.”

Regulatory Compliance

Datawatch healthcare customers are also using Monarch to improve operational processes as well as ensure regulatory compliance.

According to Patricia Hickey, senior clinical analyst at Piedmont Henry Hospital in Stockbridge, Georgia, “With Datawatch, we can provide actionable data that impacts performance, increases revenue and ensures HIPAA compliance. We can register patients quickly and with more accurate information, so staff can efficiently handle individual care while our office team can improve patient billing and timely reimbursements from the government and insurance companies. With Datawatch providing the right data for proper analysis, workflow and policies have evolved to support the organization’s goals.”

Physician Performance

Clark Carpenter, network administrator at Southeastern Med, a community hospital in Cambridge, Ohio explained, “With Datawatch, I am able to create and share powerful visualizations and uncover insights that I haven’t been to find in the past. It is the ability to access our unstructured data that lets us better track hospital-acquired infections, thus reducing these incidences and costs. Additionally, we are improving our operational processes through dashboards that gauge physician performance and our endoscopy unit saves at least 15 hours a week by eliminating the manual reporting.”

“Datawatch Monarch has gained significant traction in the healthcare market to date, helping business users, across all levels of an organization easily acquire and prepare data for operational reporting and analytics,” said Michael Morrison, CEO of Datawatch. “Our latest version of Monarch delivers data socialization capabilities for data preparation that will transform the way that our healthcare customers think about and interact with their data to drive better business and clinical outcomes. Organizations will gain an unprecedented ability to share curated data sets, collaborate and boost individual and department productivity – all while maintaining strong data governance.”

For more information about how Datawatch Monarch is being utilized within the healthcare market, visit: http://www.datawatch.com/in-action/industries/healthcare/.

The next big trend to hit the data analytics market this year is data socialization. According to Forbes, data socialization adds social functionality to a data strategy and is changing the way organizations think about how to share data and how they operate when it comes to making that data available.

“Companies are looking for ways to reap the benefits of self-service data solutions,” said Dave Wells, analyst at Eckerson Research. “An online data marketplace where users and analysts can search datasets, see how their peers are using the information and then select the right dataset for their needs minimizes redundancy, increases efficiency and prevents the use of incomplete data and resulting flawed analysis. Data socialization’s agile, crowdsourcing technique will also lead to effective data governance as IT can gain valuable insights on how users are using data through socialization and crowdsourcing techniques, enabling them to provide validated data sets to business users for analysis.”

Monarch Swarm embodies all the capabilities of Datawatch’s self-service data preparation solution as well as advanced collaboration, data cataloging and crowdsourcing features to fit this need. The platform’s key features include:

Social Landing Page – Quickly locate data assets and see how others are using and rating the data for preparation and analysis
Web-Based Data Preparation – Access robust data preparation capabilities anytime, anywhere
Data Cataloging and Browsing – Easily search data sets and folders indexed by user, type, application and unique data values to quickly locate the most relevant information for analytical use
Personalized, User Interface – Using machine learning capabilities, receive suggestions on relevant data sources and data preparation actions
Data Governance – Share sanctioned data sets for reuse and consistency to support auditing and regulatory compliance reporting
Gamification – Leverage motivational concepts and techniques to encourage decision makers to engage and collaborate with one another – both to drive participation and to better their ability to make more informed decisions

The Rising Demand of Data Prep for Healthcare

Posted by Datawatch on May 3, 2017

对于那些想要解放自身的数据访问、整合以及报表分析等能力的医疗机构来讲，自助式数据准备平台的需求愈来愈强。根据Grandview的调研到2024年，随着医疗机构想要在财务、日常运营、行政三个方面发挥数据的作用，全球医疗数据分析市场将增长至420亿。得益于电子病历、电子健康档案的广泛应用以及财务保险数据的数字化。自助式数据准备解决方案主要是为了将所有分析所需要的数据提取成规定的格式，高质量的数据才能保证数据分析的顺利开展。

Self-service data preparation platforms are in high demand among healthcare organizations seeking to overcome the common hurdles to data access, reconciliation and reporting. According to Grandview Research, the global healthcare analytics market will grow to $42.8 billion by 2024 as organizations look to leverage data for financial applications, operational and administrative purposes. This surge is being driven by the increased use of electronic health records (EHRs) and the digitization of financial records and insurance claims processing. Self-service data preparation solutions are vital to ensuring that all the data critical to the analytical processes are pulled into the proper format, thus confirming the highest data quality.

2016年 In fiscal year 2016, 118 healthcare organizations turned to Monarch’s self-service data preparation tool to radically expedite data analysis and fact-based decision making. More than 720 hospitals and other healthcare services providers now rely on Monarch to improve the preparation and analysis of patient, physician and financial data and gain insights vital to driving down operational costs, increasing productivity, maintaining regulatory compliance and improving quality of patient care.

Dana-Farber Cancer Institute’s reimbursement manager, Tom Pellerin, uses Monarch and states, “Our monthly net-revenue calculations are based on several different PDF reports and databases, and Monarch helps to expedite this process by gathering, filtering and blending data into an Excel spreadsheet. It is a significant time-saving solution and a tremendous analytical tool.”

If you are faced with the challenges of data quality with reconciliation and reporting, try a free version of Monarch today.

The Excel Conundrum (Part 1): Why Most People Struggle with Data

当我们想要利用数据来解决业务上的一些问题的时候，通常这个过程都相当费劲。很多时候并不是因为我们对分析的技巧不够熟悉，更多是因为我们使用的工具不够强大，或者工具本身学习起来很费劲，不容易使用。

When tasked with using data to solve a business problem, many of us struggle to get the answers we need. Far too often, the issue is not that we have shortcomings in our analytical skills, but rather that there are limitations in the tools we have at our disposal, or that these tools have special nuances to learn and we simply don’t have the time to learn them.

你是属于哪一类呢？ Do you fall into this category? Here are a few questions you can use to self-qualify:

你是否花费在收集数据上的时间远比分析得到指导业务决策的观点上的更多Do you struggle to gather the data you need, rather than spending that time generating constructive insights that drive smart business decisions?

你是否会花费很多时间在采集、过滤和解析数据？ Do you find yourself spending too much time collecting then filtering and parsing data? Do you spend hours trying to figure out how to tackle your data challenges, only to find that you need to code macros or build complex formulas just to get to the original task of performing analysis?

尽管Excel的分析和报表功能我们甚是喜欢，但全球亿万用户中只有极少一部分是真正的专家，其他人只用到了其中一部分，因此在数据分析过程中浪费了太多的时间。 As much as we love Microsoft Excel for its analysis and reporting capabilities, only a small percentage of the billions of users worldwide are truly experts and the rest are only getting a fraction of the value and spending too much time on their data analysis projects as a result.

Why does this happen?

数据本质上是很脏的，不完整或者不是我们便于使用的格式。重复的记录、不完整或过时的数据、亦或是没有正确解析异构系统中记录得到的字段都会产生脏数据。在最开始应付脏数据的时候，我们能够接受手动调整来达到我们的目的。但没过多久你就会被这样的手动调整整麻木了。

Data is inherently “dirty”, incomplete or not formatted in a way that can be used effectively. Dirty data could be caused by duplicate records, incomplete or outdated data, or the improper parsing of record fields from disparate systems. The first few times we have work with dirty data, we accept having to do a few ‘tweaks’ to get it to fit our needs. But soon those few tweaks explode into mind numbing manual efforts that derail the entire process.

设想你的分析工作依赖来自多个不同系统中的数据。为了能够在Excel中对数据进一步深入分析、得到报表并进行总结，对于每个人来说，都会涉及到多个很麻烦又很耗时的步骤 Imagine you want to perform analysis that requires data from several different systems. Working in Excel to get data ready for deep analysis, reporting or summarization involves a series of tasks and activities that are too challenging, tedious and time-consuming for the average person.

事实上，数据分析人员通常要花费80%的时间在数据准备过程中。 In fact, data analysts often spend up to 80% of their time correcting these issues with data preparation.

如果你不再需要再浪费时间在网上浏览视频或者翻阅《Excel从入门到精通》来学习如何写代码、写公式、那些特殊的语法或特殊功能来实现你的目标，或者是请教别人?怎么样做到无师自通？ What if you no longer had to spend hours on YouTube tutorials or digging through an ‘Excel for Dummies’ book trying to learn code or formulas, find that special syntax or figure out that complex feature to achieve your data objectives, or reach out to your in-house Excel guru – again – for help yet again?

绝大多数的人不知道有一种更好的办法可以实现数据的自动化清洗、处理，只需要数分钟而非数小时。 Most people don’t know there’s a better way to automate data clean-up and manipulation so it will take minutes instead of hours. They’re not aware there are solutions to the day-to-day usability and data integrity issues they face with Excel.

在介绍这种方案之前，我们先看看为什么使用excel会浪费时间，产生不准确的数据。 Before we discuss the solution, in Part 2 of the blog series, let’s dig a little deeper into the realities of Excel that lead to wasted time and inaccurate data. So – what are the prime culprits?

Verifying Data 数据确认

在打开Excel 通常我们的任务是要审核一下数据表识别出其中的错误以免不完整或者有误的数据在数据可视化上带来的时间精力的浪费。One of the most basic tasks we do with Excel is reviewing a data table to identify errors so we can avoid wasting time and effort in visualizing wrong or incomplete data.

但是通常这都是个人肉的工作，你要打开多个单元簿，不停的来回切换上下滚动找到没问题的数据表格 However, it’s a manual process during which you’ll need to have multiple spreadsheets opened and are constantly switch views or scrolling all over to find the tables with the right data.

Extracting Data From Locked Data Sources (e.g. PDFs)

大家是不是通常碰到一些数据或者图表你拿到的就只有PDF文件，你想要复制这些报告文档中的图表和数据。 How many times have you come across the perfect chart or set of data – but the only copy you have is in a PDF? Most times, you have to copy charts and data from those documents and reports.

然而要将数据从PDF中搬运出来是个很漫长的过程，你需要手动复制，然后对格式进行调整

However, transferring data from PDF files can be a long process, as it has to be manually copied and formatted.

Using Data From Live & Dynamic Websites

网络上充斥着大量有价值的数据，可以丰富和强化你所在做的数据分析，这类数据通常以一定规律进行更新，来确保数据准确性。找到一个第三方维护的网址，是一种极其高效可靠的获取准确及时信息的方式。

There’s a wealth of valuable data available on the web that can be used to enrich your data analysis. Such data is usually updated regularly, which means it’s precise and accurate. It’s faster and more reliable to go to a third party maintained website (such as a Financial institution for interest rates (..or whatever) to get accurate, timely, and precise information.

但当你从网站上拷贝数据的时候，页面的样式很难处理干净，因此，你会得到一大堆杂乱的超链接、随机的广告，一些信息缺失的情况，这些会耗费你数个小时来处理。另外如果只要网站上的数据更新了你就可以自动的得到这些数据无需回到这个网站重复人肉搬运的工作该有多好？

However, when you copy data from a website, the layout rarely transfers cleanly. As a result, you have a mess of hyperlinks, random ads, and missing information that’ll take hours just to clean up. Furthermore, wouldn’t it be nice if when the data updates on the webpage, you automatically have that data without having to constantly go back to the site and fight through the same painstaking process all over again.

更不用说要在现有图表基础上合并一些数据，我们要花数个小时来进行格式的整理

Not to mention, merging the data with existing charts can mean hours of work spent in reformatting.

Combining Data Sets

Joining multiple data sets in Excel can involve a number of steps and complex maneuvers using VLOOKUPs, custom coding. and manual copy-and-pasting.

Besides being time-consuming, these manual operations are error-prone and you could end up spending even more time tracking down and fixing mistakes. It can also be very difficult to get data from multiple sources to join cleanly (e.g. joining data from a big data source or database with a basic CSV file) Fixing Missing Data

Don’t even get me started on deal with “nulls”. After importing data, you often must search for “nulls,” replace them with a value and repeat the process for each row and column in each data set, which is a highly manual and time-consuming process. Masking Sensitive Data

In order to comply with corporate or regulatory standards, you will need to mask your data using complex macros to protect sensitive data in your reports. You either need to spend time coding the macros or get someone else with the technical knowledge, and the time, to do it for you.

Amongst other things, people often forget that the data is only hidden—not completely removed—from the file and this could become a security risk. People are rushed and trying to do too many things at once, or have too many spreadsheets open, and mistakes happen. Consolidating Data Tables

When you need to combine rows from different spreadsheets, you have to manually copy-and-paste the information. This may be fine for a few dozen rows, but the process is tedious and time-consuming. It’s often impossible when the number of rows balloons into the hundreds or thousands. Version Control and History of Work

When you share your files and have multiple parties working on the data, Excel doesn’t allow you to track the activities or implement version control. This can lead to duplicative work, inaccurate data, and frustration. Reconciling Reports

Every time new data is changed in an Excel spreadsheet, you have to manually update reports or dashboards and BI tools that are pulling from this data. If you have a large data set, changes can be hard to identify. Repeating Data Prep For New Data

When new data comes in, you have to repeat all the data prep steps manually. If you want to have all your reports updated dynamically as soon as data is changed or added, you need to spend a lot of time creating complex macros to do the job.

Now that we’ve reviewed the difficulties with Excel that give the average person fits, keep an eye out for Part 2 next week where we’ll share the secret to tackling these problems.

What Is Data Preparation?

Data preparation is most often used when: --Handling messy, inconsistent, or un-standardized data 处理杂乱的、不一致的或非标准数据 -- Trying to combine data from multiple sources 整合来自多个数据源的数据 --Reporting on data that was entered manually 根据手动输入的数据制作报表 -- Dealing with data that was scraped from an unstructured source such as PDF documents 处理从PDF等非结构化数据源中提取的数据

业内领先的自助式数据准备解决方案供应商 Datawatch Monarch is the industry’s leading solution for self-service data preparation. -- Built for business users not rocket scientists 专为业务人员设计 -- Automatically extract from reports & web pages 自动从网页报告中提取数据 -- Combine, clean and use with your favorite tools 可以与惯用常用根据进行数据整合、数据处理和使用

The key steps to your data preparation:

Data analysis – The data is audited for errors and anomalies to be corrected. For large datasets, data preparation applications prove helpful in producing metadata and uncovering problems.
Creating an intuitive workflow – A workflow consisting of a sequence of data prep operations for addressing the data errors is then formulated.
Validation – The correctness of the workflow is next evaluated against a representative sample of the dataset. This process may call for adjustments to the workflow as previously undetected errors are found.
Transformation – Once convinced of the effectiveness of the workflow, transformation may now be carried out, and the actual data prep process takes place.
Backflow of cleaned data – Finally, steps must also be taken for the clean data to replace the original dirty data sources.

Here’s an example:

There are multiple values that are commonly used to represent the same U.S. state. A state like California could be represented by ‘CA’, ‘Cal.’, ‘Cal’ or ‘California’ to name a few.

A data preparation tool could be used in this scenario to identify an incorrect number of unique values (in the case of U.S. states, a unique count greater than 50 would raise a flag, as there are only 50 states in the U.S.). These values would then need to be standardized to use only an abbreviation or only full spelling in every row.

表单数据提取】每天收到企业各部门的采购订单，word、PDF版外，还有大量纸质订单。这些数据对于企业来说都非常重要，那么如何将这些数据提取出来供企业中的其他系统使用呢？答案是——通过Lotus forms可以将这些非结构化的文档，转换成结构化的XML文档，集成到企业系统中，超方便。

【Apache Tika 1.0发布，开源的文档检索工具包】 Apache Tika是一种利用现有的解析类库,从不同格式的文档中(例如HTML、PDF、Doc等)检测和提取元数据、结构化文本内容的工具包。检测文档的...

和秋叶一起学Excel# S04-2 pdf粘贴excel每个数据各占一行！我惊呆了!被逼无奈我使出了word大法，先粘贴到word里，居然是一个漂亮的表格，然后复制粘贴excel，ok!练习2掉阴沟了，选系统的逗号不好使，看课件才发现要复制一下！另外终极挑战成功！嘿嘿，跳过该列，搞定！ @秋叶 @文剑武书生KING】

1.Smallpdf：O2.iLovePDF：O3.PDF.io：O4.PDF编辑王：O5.PDF to Excel Converter：O6.PDFmyURL：O

大猫嗷：根本没必要另外下载转换器，这是adobe acrobat本身自带的功能。选“文件----导出----excel格式”。

：讯捷pdf转换可以pdf转Excel格式免费版转5页内收费无限制pdf页数

其实，我是不会告诉你，我下午又试了一下，在人家pdf文件里选中文本右键菜单，里面有个选项叫做“复制为表”[摊手][摊手][摊手]这就尴尬了

建设单位为了统一规范招标文件，发出的工程招标文件都是PDF版的，这加大了施工单位的工作量。需要人工把一本厚厚的PDF工程量清单表格手工录入并转换成excel，这需要耗费施工单位一周甚至更多的时间，并不能保证表格里数据的正确率，因此导致了很多施工单位废标。施工单位的朋友，你想几分钟之内成功把PDF版招标工程量清单直接转换成EXCEL吗？还在为PDF转换成EXCEL而烦恼吗？下面，小匠给施工单位的朋友介绍一款免费在线转换工具。 https://weibo.com/ttarticle/p/show?id=2309404114245322152640

wanghaisheng / wanghaisheng.github.io

自助式的数据准备平台工具datawatch #117

Why does this happen?