Microsoft Business Intelligence Tools for Excel Analysts

Title page image

About the Authors

Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 16 years’ experience consulting and developing Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can visit Mike at www.datapigtechnologies.com, where he regularly shares Excel and Access tips and techniques.

Jared Decker has over fourteen years of experience in the IT industry and ten years of consulting experience focused exclusively on data warehousing and business intelligence. In addition to playing an architect or lead role on dozens of projects, he has spent more than five hundred hours in-house with corporations training their development teams on the Microsoft SQL Server, Tableau, and QlikView BI platforms. His breadth of experience entails everything from architecture and design to system implementation, with particular focus on business analytics and data visualization. Jared holds technical certifications in Microsoft (MCITP Business Intelligence Developer and certified trainer), Tableau Developer, and QlikView Developer and Trainer.

Bernard Wehbe has over 14 years of consulting experience focused exclusively on data warehousing, analytics, and business intelligence. His experience includes data warehousing architecture, OLAP, data modeling, ETL, reporting, business analysis, team leadership, and project management. Prior to founding StatSlice Systems, Bernard served as a technical architect for Hitachi Consulting in the Dallas, TX area.

Publisher’s Acknowlegments

Sr. Acquisitions Editor: Katie Mohr

Project Editor: Rebecca Senninger

Copy Editor: Lynn Northrup

Technical Editor: Mike Talley

Editorial Assistant: Anne Sullivan

Sr. Editorial Assistant: Cherie Case

Project Coordinator: Patrick Redmond

APPENDIX A: Understanding the Big Data Toolset

Big data refers to a broad set of tools that are designed to work with large data volumes. These tools are gaining popularity because they solve the limitations of traditional tools by scaling up on hardware and sharing the workload across many linked computers. Using this divide-and-conquer approach allows big data technologies to accomplish tasks that are not feasible otherwise.

When it comes to analytics, there are some special considerations regarding big data. You need an easy way to access and navigate platforms without the need to spend precious time learning new programming languages such as Java. In the past few years several tools have come available that offer a big data SQL solution: a tool that sits on top of a big data platform and allows you to write SQL queries against the big data engine.

In this appendix we review the big data SQL tools on the market now, giving you a sense of the big data landscape. You’ll also discover how you can connect to those tools from Excel.

Big Data SQL Offerings

There are several big data offerings that support a relatively easy-to-use SQL engine on top of Hadoop or another similar-style platform.

note.eps We cover the talked-about entrants into the market as of this writing. Note, though, that this space is quite new and many companies are jumping into it almost daily.

Most of these technologies have a similar approach to big data SQL. They scale up on hardware and use a divide-and-conquer approach to break big data volumes into small chunks and distribute the processing across multiple nodes. The main differentiator among the various technologies is whether or not they are Hadoop-based. For example, Amazon Redshift does not sit on top of Hadoop, but instead uses a different technology that allows similar scalability as Hadoop. On the other hand, Hortonworks and Cloudera are tools that are installed on top of Hadoop and take advantage of its scalability features.

Amazon Redshift

Provider: Amazon under the Amazon Web Services platform

Web Site: http://aws.amazon.com/redshift

Platforms: This tool only runs in the cloud on Amazon Web Services.

Technology Overview: Amazon Redshift is a fully managed cloud-based data warehouse service. You can think of it as a kind of DropBox for databases ranging from a few hundred gigabytes to a petabyte or more. The technology Redshift uses is built to make it easy to scale up on hardware as data needs grow. It also includes compression and a columnar-based design that is best suited for analytics queries and can support very large data volumes.

Pros: Amazon Redshift is a production-grade tool that delivers on its promises. Getting up and running with it is easy, and managing it is even easier.

Cons: It is only available in the cloud with no option for on-site installation. Connecting to the Amazon Redshift via Microsoft tools is a challenge from behind the firewall. Although, most big data SQL tools that are in the cloud have this same challenge.

Hortonworks Hive

Provider: Hortonworks (partners with Microsoft)

Web Site: http://hortonworks.com

Platforms: This tool is available on Hadoop and can be cloud- or on-premises–based.

Technology Overview: Hive is the open-source SQL implementation on Hadoop. Up to recently it did not allow real-time SQL queries. However, this has changed with the Stinger initiative, which has the objective of configuring Hive to allow real-time SQL queries.

Pros: On-premises or cloud installation. Allowing on-premises installation is critical if your organization has a no-cloud policy.

Cons: As of this writing, Hive is still relatively new and the real-time engine is in Beta release.

Cloudera Impala

Provider: Cloudera

Web Site: www.cloudera.com

Platforms: This tool is a real-time SQL engine that was developed by Cloudera and sits on the Hadoop platform.

Technology Overview: Impala is a proprietary SQL engine that is designed for analytics purposes and high scalability. It can read traditional Hadoop file formats and can span to multiple Hadoop nodes. It uses C++ instead of Java for performance and doesn’t translate SQL into MapReduce.

Pros: On-premises or cloud installation. Allowing on-premises installation is critical if your organization has a no-cloud policy.

Cons: Does not allow incremental update of data, forcing a complete rebuild when underlying data changes. This is a pretty big limitation for large datasets as it can take quite a bit of time to reload.

IBM Big SQL

Provider: IBM

Web Site: www.ibm.com/software/data/bigdata

Platforms: This tool is an SQL engine that was developed by IBM and sits on the IBM-variant of the Hadoop platform called BigInsights.

Technology Overview: Big SQL is a proprietary SQL engine that is designed for analytics purposes. According to IBM, Big SQL takes the SQL syntax submitted by the user and translates it to individual MapReduce jobs. Big SQL can also support real-time queries over a single node only.

Pros: On-premises or cloud installation. Allowing on-premises installation is critical if your organization has a no-cloud policy.

Cons: Does not allow real-time SQL queries against multiple nodes because it relies on MapReduce jobs for that.

Google BigQuery

Provider: Google

Web Site: http://cloud.google.com/products/bigquery

Platforms: This tool is a real-time SQL engine that was developed by Google using its proprietary technology.

Technology Overview: Google BigQuery is a proprietary SQL engine that is designed for analytics purposes and high scalability. BigQuery is the public version of Google’s own Dremel query service that Google has used for years to track device installation and analyze spam. Dealing in read-only datasets, Google BigQuery allows programmers to use SQL-like queries to extract and analyze billions of rows at a time.

Pros: Is capable and easy to administer.

Cons: Cloud-only installation, which can be limiting for some organizations. Per Google’s description, “append-only” implies some limitation on updates of historical data, which could be limiting for analytics datasets.

Facebook Presto SQL

Provider: Facebook

Web Site: http://prestodb.io

Platforms: This tool is an open-source SQL engine that was developed by Facebook and sits on top of the open-source Hadoop platform.

Technology Overview: Presto SQL was developed by Facebook to address the latency limitations of MapReduce jobs and allow interactive queries against large datasets stored in Hadoop.

Pros: On-premises or cloud installation. Allowing on-premises installation is critical if your organization has a no-cloud policy.

Cons: New tool relative to the others in this chapter.

Defining a Big Data Connection

For analytics purposes, your job will mainly involve accessing data from a big data platform. Loading the data into a platform requires specialized skills and assistance from your system administrators. However, after the data is loaded into the platform, you can access it via your analytics tools, including Excel.

Most big data tools allow you to access data via ODBC or JDBC drivers. With Microsoft tools, you use ODBC drivers. The first step in connecting to the platform is to create your ODBC connection.

note.eps Before you can connect to your platform, make sure that you have the drivers installed on your machine. Each tool has its own requirements for an ODBC driver. For example, Amazon Redshift requires you to install the PostgreSQL ODBC driver to connect to one of its clusters. You can find the proper driver for each on the tool’s Web site.

After you have installed the proper driver, follow these steps to connect to your platform:

  1. Open the Data Sources (ODBC) Administrator dialog box and click the System DSN tab (see Figure A-1).

    tip.eps If you don’t know where ODBC is installed on your computer, choose Start → Search, and type ODBC in the search bar.

    You may already have data sources defined because of other tools that use ODBC connections. In our case we have two Redshift connections defined.

    9781118821527-fg0101.tif

    Figure A-1: ODBC Data Source Administrator dialog box.

  2. Click the Add button to add a new ODBC connection.

    A window opens with a list of available drivers on your computer, as shown in Figure A-2.

    9781118821527-fg0102.tif

    Figure A-2: Select a driver from the available listed.

  3. Select the driver you installed.
  4. Click Finish.

    The Setup window opens, where you define your connection (see Figure A-3).

    This window is specific to the driver you’re using and the tool you’re connecting to. You get the connection information from your big data administrator.

    9781118821527-fg0103.tif

    Figure A-3: Driver Setup window.

  5. Enter your connection information, click the Test button to test your connection, and then click Save.

Your connection is now ready to be used in your analytics application.

Connecting to Big Data Tools with Excel

After defining your ODBC connection, you can access your big data tool by following these steps with Excel:

  1. From the Data tab, click the Connections button.

    The Excel Workbook Connections dialog box opens.

  2. Click the Add button at the top of the window.

    The Existing Connections window opens.

  3. Click the Browse for More button.

    The Select Data Source window opens.

  4. Click the New Source button.

    The Data Connection Wizard opens (see Figure A-4).

    9781118821527-fg0104.tif

    Figure A-4: Data Connection Wizard.

  5. Select ODBC DSN and click Next.

    The Connect to ODBS Data Source screen opens, with a list of ODBC connections that have been defined on your computer (see Figure A-5).

    9781118821527-fg0105.tif

    Figure A-5: Data Connection Wizard ODBC data sources.

  6. Select your ODBC connection and click Next.

    The Select Database and Table screen opens (see Figure A-6).

    9781118821527-fg0106.tif

    Figure A-6: The Select Database and Table screen.

  7. Select a table that you want to access and click Finish.

    The Workbook Connections dialog box opens again with the connection you just defined.

  8. Click Close at the bottom of the dialog box to return to the main workbook.

Modifying your connection

With a defined connection, Excel only allows you to connect to a table or a view. You can, however, change the SQL that has been built by the wizard to create a more sophisticated SQL query that would better suit your reporting needs. To accomplish this, follow these steps:

  1. On the Data tab, click the Connections button.

    The Workbook Connections dialog box opens.

  2. Select your defined connection and click the Properties button.

    The Connection Properties dialog box opens.

  3. Click the Definition tab (see Figure A-7).

    9781118821527-fg0107.tif

    Figure A-7: The Connection Properties dialog box.

  4. Modify the SQL in the Command Text box.

    Figure A-7 shows how we changed the SQL to return the top 10 rows only for that table, but you can get as sophisticated as you want with your query (provided your big data SQL tool supports the syntax you’re trying to use).

  5. When you’re done, click OK to save your changes.

Using your connection

Now that you have defined the connection in Excel, you can start using it.

  1. From the Data tab, click the Existing Connections button.

    The Existing Connections window opens.

  2. Select your connection and click the Open button.

    The Import Data dialog box opens (see Figure A-8).

    9781118821527-fg0108.tif

    Figure A-8: The Import Data dialog box.

  3. Select the type of view you want to create and click OK.

    Excel links the table to the query you defined in your connection. You can refresh this data as needed.

caution.eps Be careful not to return large amounts of data in your query as your source could have large data volumes.

APPENDIX B: Considerations for Delivering Mobile BI

A business intelligence platform is not complete without the ability to deliver dashboards and reports via mobile devices. The Microsoft BI platform addresses your mobile access requirements via an array of ever-increasing deployment options. As of this writing, accessing Microsoft BI functionality via mobile devices includes the following deployment scenarios:

  • With SharePoint 2010 or 2013 and a supported mobile device, you can view sites in the Business Intelligence Center, browse Excel Services reports, interact with PerformancePoint dashboards, and access Reporting Services reports.
  • With Power View and a supported mobile device, you can view dashboards and data visualizations in HTML5 (currently in Preview).
  • Using Office 365 and a supported mobile device, you can deploy Excel Services dashboards and Reporting Services reports. A licensing option called Power BI includes more BI functionality than is available with the standard Office 365 subscription.
  • With SQL Server 2012 SP1 or later and a supported mobile device, you can view Reporting Services reports, available in the SQL Server Report Manager, using Internet Explorer 10 (or later) or Safari. These reports can be accessed via an iPad, Windows phone, or Windows Surface.

In the following sections, we list each of these deployment scenarios along with caveats and prerequisites.

note.eps Check the online Microsoft resources frequently for updates. The list of available technologies for mobile use cases changes frequently, and you want to be aware of the latest options before committing to a deployment plan.

Mobile Deployment Scenarios and Considerations

There are several considerations for addressing mobile use cases, including the type of device (tablet, smartphone, and so on), the operating system running on that device, and the browser. We discuss each of these in the following sections.

In addition to the deployment scenarios that address hardware, software, and settings, there are considerations for offering reports and dashboards to your users via their mobile devices. One of the most important considerations is how the report or dashboard renders on a given device. The same report or dashboard can look completely different depending on whether it is viewed in a desktop browser, a tablet, or a mobile phone. Some planning and testing is required for large-scale deployments to ensure that your dashboards and reports have a professional and consistent look and feel. Though beyond the scope of this book, there are strategies to improve the user experience, such as creating multiple views of the same content so that the optimal user experience can be triggered depending on the device being used.

Mobile devices

The list of supported devices for SharePoint and other Microsoft BI applications is continuously changing. In general, Apple and any manufacturer running Windows Phone or Windows 8 or 8.1 OS are supported. Android is supported for accessing SharePoint 2013 sites using a rendering setting called contemporary view.

  • Apple: Both the iPhone and the iPad are widely used devices. iOS 5 or greater is needed to run most Microsoft BI functionality (some Microsoft BI applications require iOS 6 or greater).
  • Microsoft: In 2012, Microsoft released the Windows 8 operating system, which included many features intended to address mobile use cases. Microsoft's own tablet, Surface Pro 2, runs Windows 8. And as of this writing, there are numerous other manufacturers with tablets running the Windows 8 operating system, including Samsung, Acer, Lenovo, and Asus. Many of the Microsoft BI capabilities are compatible with mobile devices running Windows 8.

    The following Windows operating systems are currently supported for Microsoft BI functionality: Windows Phone 7.5 or greater, Windows RT, and Windows 8 or greater.

note.eps The list of supported devices, operating systems, and apps is constantly changing. To get the very latest information on what devices and operating systems are supported, use your favorite search engine to look up Mobile Support Microsoft Business Intelligence Tools.

Browser-based deployments on mobile devices

There is a wide range of options for viewing SharePoint and Reporting Services content using mobile devices. For example, SharePoint 2013 can be rendered using a setting called contemporary view, which optimizes the rendered view for mobile devices and expands the range of supported devices and browsers to include not only the iPhone, iPad, and Windows Phone devices, but also Android devices running Android 4.0 or later. Contemporary view achieves this level of flexibility by rendering Web pages using HTML5.

In addition, Power View, running in SharePoint or Office 365, supports the same level of flexibility by rendering in HTML5.

Running apps on mobile devices

For many mobile devices, native apps can be downloaded and run on the device. These apps are typically developed specifically for the operating system and user controls that are available on the mobile device. Several apps that offer Microsoft BI functionality have been developed for Windows 8 or greater and are available in the Windows Store for downloading on a compatible device. For example, there is a Power BI app that runs on Windows 8.1 if you have an Office 365 Power BI subscription account. The list of apps that provide enhanced Microsoft BI functionality on mobile devices is expected to grow.

Office 365

Launched in 2011, Microsoft Office 365 provides organizations with subscription-based pricing for Microsoft Office, SharePoint, Exchange, and other productivity applications. In addition, Office 365 serves as a cloud-based platform for hosting SharePoint and other business applications. By extension, an organization can license a given number of seats and provide these users with the BI functionality that comes with SharePoint.

In 2013, Microsoft expanded its Office 365 licensing models to include Power BI. This package includes Power Pivot, Power View, Power Map, and SharePoint. There are several key benefits to using Office 365 for mobile deployments. These include subscription-based pricing for SharePoint and reduced setup and maintenance.

To take advantage of the BI capabilities offered by Office 365, users require a compatible device or Web browser. Currently, supported devices include the iPad (running iOS 6 or greater) or any device running Windows 8 or greater. Desktop Web browser access is supported for Safari or Internet Explorer 10 or greater.

SQL Server Reporting Services

Some organizations run SQL Server Reporting Services in native mode, meaning that you can access reports via the Web, but outside of SharePoint. This is possible because SQL Server Reporting Services includes a built-in Web server, or alternatively, the option to leverage Internet Information Services, the standard Windows Web server, as a means for deploying reports over the Web. The Report Manager page serves as a catalog of reports that can be browsed by end users.

When viewing Reporting Services reports on a mobile device, you can leverage all the features that have made Reporting Services a popular enterprise tool. For example, you can expand and collapse report areas showing a plus or minus indicator, apply sorts and filters (also called parameters), and click hyperlinks. Reports can also be exported to TIFF, PDF, XML, CSV, MHTML, Excel, and Word.

When reports are hosted natively using Reporting Services, users can access them using iPad running iOS 6 or greater, or Windows Phone or Surface running Windows 8 or greater. In addition, SQL Server must be 2012 SP1 or greater.

note.eps When using Apple’s Safari browser, you cannot view reports in Report Manager, and the only report export options are TIFF and PDF.

SharePoint 2010 and 2013

This book has highlighted many applications that run as part of SharePoint and are intended to provide reporting, data visualization, and other business intelligence functionality for end users. Users with compatible mobile devices can access many of the same SharePoint features that are provided to desktop users. Aside from the ability to open Excel, Word, and PowerPoint documents, SharePoint mobile functionality includes

  • Browsing sites in a SharePoint Business Intelligence Center
  • Interacting with Reporting Services and Excel Services reports
  • Interacting with PerformancePoint dashboards
  • Exporting to PowerPoint or Excel

As of this writing, compatible mobile devices include iPad running iOS 5 or greater, tablets running Windows 8, devices running Windows Phone 7.5 or greater, and Windows Surface running Surface RT. Supported browsers include Safari and Internet Explorer Mobile.

New in SharePoint 2013, contemporary view is a user setting (that is, selected on the device) that causes pages to be rendered in HTML5. Not only does this view optimize the Web content layout for mobile devices, it also allows SharePoint content to be rendered on Android (as well as on the standard supported browsers).

note.eps In order to allow users to select contemporary view, the Mobile Browser View feature must be activated for the SharePoint site. This feature ensures that the mobile device is running a compatible browser and OS before rendering pages in HTML5.

The default setting of Classic View causes pages to be rendered in standard HTML format. This format looks exactly like SharePoint 2010.

Using the Full-Screen UI setting causes the content to look exactly like it would on a desktop computer, though more zooming and scrolling is required to see the entire screen.

stat.eps

StatSlice Systems is a business intelligence technology consulting firm headquartered in Dallas, TX which specializes in data warehousing and business analytics. Founded in 2006 by Bernard Wehbe and Jared Decker, StatSlice provides strategic data services that impart the skills, processes, technologies, applications, and practices needed to achieve better business decision-making. The company emphasizes highly involved client-interaction combined with a compliance with key business intelligence implementation principles.

The StatSlice team is comprised of high caliber talent nurtured in an environment that encourages resourcefulness, innovation, and creativity. Their forward-thinking philosophy is centered upon the distinct purpose of providing the best data-driven decisions that significantly increase the profit of their clients.

If you are ready to improve your efficiency and launch your business beyond the competition by realizing the potential in your data, contact StatSlice Systems for your strategic data service needs.

code.eps

www.statslice.com