Question

For our discussion, we will cover the selection of an analytic tool. Over the past few years several analytic tools have sprung up in the market. Tools from familiar names such as Microsoft and SAS are popular, but so are Tableau and QlikView. But when it comes to selecting a tool for an organization, consideration must be given to the analytic features. In your discussion, be sure to address the following points:

What are some of the key features common to all analytic tools?

What features are unique and separate the most popular analytic tools?

Advancements in analytic capabilities using Microsoft Excel.

The power of Excel pivots, charts, and dashboards for analysis.

Be sure to include substantial content in your posts and be specific!

Answer 1

1.) Key Features:

-> All the BI tools generally provides Basic Data Visualization

-> Basic Filtering, Pivots and Cross tabs are generally provided by BI Tools.

2.) Unique Features of Analytic Tools:

-> Data sources

The ability to integrate BI analytics tools into your current reporting processes seamlessly is a major advantage. This can allow you to make the most of the data that you already have buried away in multiple data sources and locations, as well as to smooth the transition to the new analytics platform.

To facilitate both activities, it’s important that these analytics tools are able to support the various data sources you already have in the business, be this ERPs, CRM systems, marketing automation packages, Excel spreadsheets or something more.

->Web-based front end

Traditional business intelligence platforms often required manual installation onto desktop computers, a costly and time-consuming process for organisations with large user bases. Additional hardware, infrastructure and software upgrades were commonly required to run the latest versions of these platforms, exacerbating the cost of deployment further.

With Cloud-based BI analytics tools, on the other hand, all you need is access to a web browser and a decent internet connection.

Logging in via a designated URL, all you need to do is input your credentials and you can be up and running on the tool, accessing analytics capabilities at anytime, anywhere in the world and on any device

->Data filters/ Drill-down

BI analytics tools are often equipped with a range of filters, drop-downs, slicers and search functions that make it easy for users to find the information they need quickly.

Users will also require the ability to drill-down into data, going from a high-level overview of performance, right down to a detailed, granular view.

-> Security

Security is a massive priority for companies looking to invest in new BI analytics tools, particularly those based in the Cloud. And rightly so – after all, this is your business critical data we’re talking about, potentially containing things like confidential employee, pricing and customer records.

->Data visualisation

Data visualisation capabilities in BI analytics tools can be a great way to bring your data to life. Dashboards, charts, graphs, gauges and other visuals can really help to illustrate the key messages in your data and can be an invaluable tool for presenting information in a quick and easy to digest manner

->Self-service

BI analytics tools were traditionally kept under lock and key by experienced IT users, the only people in the business with the necessary skills and knowledge to operate such complex, manual systems.

Thankfully, that’s no longer the case and a shift in focus towards self-service platforms means that access to these tools has been democratised to users across the business. With that in mind, BI vendors are making their tools more user-intuitive and easy-to-use than ever before.

->Mobile applications

The ability to access critical business reports, dashboards and analyses on the go is an increasingly important feature of modern BI analytics tools.

As the use of smartphones and tablets in businesses becomes more prevalent, it is important that business applications are supported on these platforms. Analytics tools are no exception to this, and mobile applications for iOS and Android devices in particular should be expected as standard.

Analytics using Excel:

-> Pivot Tables

There is no other feature in Excel I being used more often and with success. Almost the first thing I always do when analyzing data is pivot the data in multiple manners and analyze the patterns/results. Pivots can process a large amount of data in a short period of time and are optimized in the Excel back-end for performance.

PivotTables allow you to transform and analyze data in a structure manner. Just select a range of data (data in columns with headers) and select the ROWS, COLUMNS and VALUES for your Pivot Table! You can also create custom columns (based on formulas), summarize data by groups/rows/columns etc. There is almost no limit in the possibilities.

-> Filtering and Sorting Data

Filtering and sorting your data is just as useful as using PivotTables. Excel is meant to transform and analyze data and filtering/sorting is one of the key elements. When provided with a table of data you will probably want to sort the data in a descending/ascending manner or filter out rows based on some features (values in certain columns). This is a must-know feature.

-> Conditional Formatting:

Conditional formatting, as its name suggests, changes the format of a cell dependent on the content of the cell, or a range of cells, or another cell or cells in the workbook. Conditional formatting helps users to quickly focus on important aspects of a spreadsheet or to highlight errors and to identify important patterns in data.

Conditional formats can apply basic font and cell formatting such as number format, font colour and other font attributes, cell borders and cell fill colour. In addition, there is a range of graphical conditional formats that helps with visualising data by using icon sets, colour scales, or data bars.

-> Look Up Excel Functions

Although these functions are not exactly a separate feature they are considered to be one of the most useful and most often used when analyzing data.

• VLOOKUP – search the first column of a range of cells, and then return a value from any cell on the same row of the range
• HLOOKUP – as above but for columns instead of rows
• INDEX – returns the value of an element in a table or an array, selected by provided index
• MATCH – searches for a specified item in a range of cells, and then returns the relative position of that item in the range

-> Data Analysis Excel Tools

All tools needed in doing basic data cleansing can be found in the Data Tools section of the Data ribbon.

-> Mixed Type Charts

Mixed type or combo (combination) charts combine two styles of charts, such as Excel’s column chart and line chart. This format can be helpful for displaying two different types of information or a range of values that varies greatly.

For example, we can use a column chart to show the number of homes sold between June and December and a line chart to make it easier to identify the average sale price by month. To create this chart, highlight all the data and select the Insert Combo Chart option in the Charts group of the INSERT ribbon tab.

-> VBA Macros

• Cleansing/filtering/sorting/copying data
• Custom algorithms (custom analysis of data sets)
• Custom Excel UDF functions (User Defined Functions)
• Excel Forms (making custom user forms to input data or create a custom UI)