Dashboards, Reporting & Business Intelligence

Previous Part Build Excel Dashboards – 3

We will Add Calculations and Formulas

Add Calculations
Since this is a raw extract of actual transactions, it may need to add additional calculations and columns to create useful metrics.

For e.g in our sample file, we have the “Unit Price” and “Quantity” columns. We will create calculation to derive the “Amount” column

Right click on the Quantity Column, Navigate to Popup Menu

Create ‘ –>Create Calculation
It shows a calculation wizard.

1. Click on “Unit Price”
2. Click on * multiplication operator
3. Click on Quantity
4. Rename the column to “Amount”
5. Click on “Use this Formula”

As you see, the “Amount” column is added right next to the Quantity column

Next – Add Date Hierarchy Columns – Excel Dashboards – 5

Previous step : Excel Dashboards – 2

Create Dashboard and Table portlet

Once connected, it will show you a list of action items to pick from

We will click on “New Blank Dashboard“

In the Dashboard Name, we type in “Order Management Analytics”

Click OK.

It will present you with a table browser showing all the available worksheets as tables

Expand the node to see all the worksheets.

In this example file, we just have one worksheet named “order_data”. Note how, it attaches a $ sign at the end, which indicates this is an Excel Worksheet.

So make sure you do not have any dollar sign when naming the Excel worksheet and no other special characters and no white or blank characters. Replace blank characters with “_” underscore character

Double Click on the “order_data$” node

Click on the “* alias=ord” item at the very top to select all the items

You may change the name of any column or any other property such as sort and group by.

We will leave it as it is and click on the “Create Table” button.

This action creates a Table Portlet (Qlet) in the Dashboard canvas

We will use this Qlet as our basic building block for the following exercise

Next : How to build Excel Dashboards – 4

Previous Step: Excel Dashboards – Part 1

Launch Dashboard Software and Connect

Launch InfoCaptor Dashboard Software on your Desktop and click on the connect button on the toolbar

The connection wizard has items that simplify connecting to different data sources.

For Excel Files, Click on the Excel button

Once you click, it automatically selects the appropriate Driver for you

Click on the “Access/Excel File” button.

Select the order_raw.xls file. It populates the connect name for you. But you can change this name. Connect Name is for your reference

NOTE: Make sure that the order_raw.xls file is not open in MS Excel. The Excel file is locked once you connect to it. If the file is open in MS Excel, you may get connected but not able to query and may get unpredictable results.

Now, click on the Connect button
Upon successful connection, the connection wizard disappears and a new action wizard pops up.

Next : How to build Dashboards – 3

Databases are often under lock-n-key of the IT Department and not possible to connect directly due to security reasons. So if you plan to build a Dashboard prototype or a Dashboard Mockup it makes sense to export the data to Excel and build it.

Modular Dashboard Design
Lot of the online tutorials and Dashboard e-books teach you to build dashboard in Excel file. They provide you with templates, you fill in the information and build the dashboard using Excel charting techniques.

If you use Excel Templates for your real dashboarding needs then you may end up with any of the below problems.

” Big Bulky and bloated excel files
” Undocumented Macros
” Zero verification on the data authencity
” No way to refresh the dashboard (lost productivity)

“Best Practise for Dashboard Design”
Modular Design – Keep Data and Presentation Separate
How? – Use Excel only for storing your data. Nothing more.

Do not build any visualization in the same place where your Data resides.

Benefits of this approach
” Your dashboard or presenation layer is independent – Dashboard definition is stored separately
” Increased productivity – When you have fresh data just refresh the dashboard, no redevelopment needed
” No Messy Excel Macros or coding knowledge required – Just drag and drop tables and visually build Tables, Charts, Speedometer, Gauges, Dials, Thermometers and more charts
” Simplified Distribution and Presentation – You can Export the Dashboard to PDF or HTML and just send them as attachments
” Save Time – Just build the dashboard presentation layer only once and automate the refreshes.

Lets begin Building Dashboard

Data Preparation

Export your transaction data to Excel file by running a report or requesting your IT department.

There are certain rules to be followed when you get the data in Excel files.

Here is a sample Excel dump of Order Entry data.
The first row is always treated as the column names.

If you inherit an Excel file with pivots and charts all over the places then try to copy just the raw data in another worksheet in the same file or create another file. If it is not possible to keep the first row as the column names then you could define “named ranges”. In the dashboard designer it is possible to access the data using Cell ranges but then future updates to the Excel file could break the dashboard, for e.g if the data goes beyond the Cell range.

NOTE: Make sure column names do not have any funky characters. Just keep it to simple alpha-numeric column names.

If so, Why do you hate Dashboard Dials

Visual merchandising, primarily and most notably a retail term, is the art of optimizing the presentation of a product or service in order to stimulate sales. A retailer is looking to create a welcoming and comfortable environment for customers by stimulating consumer senses through a variety of mechanisms at its disposal.

Have you been involved in any Dashboard Building excercise?

If yes, did you incorporate any kind of Dials or speedometers? Please share your inputs

Here is a little “ego-boost”, InfoCaptor featured in the Small Business Dashboard solutions

If you have not checked it yet,

“IT Dashboard” is a new, one-stop clearinghouse of information that allows anyone with a web browser to track federal IT initiatives and hold the government accountable for progress and results. A part of USASpending.gov, the dashboard allows you to see what IT projects are working and on-schedule (and which are not), offer alternative approaches, and provide direct feedback to the chief information officers at federal agencies – in effect, keeping tabs on the people who are responsible for taxpayer dollars for technology.”

IT Dashboard tracks government spending and you can check it online at http://usaspending.gov

There is way to get your own customized version of the dashboard at

http://it.usaspending.gov/?q=content/data-feeds

Organizations cannot continue to spend 80% of their time collecting, copying, pasting, and reformatting data, 10% weeding out errors, and then realize in the remaining 10% that the resulting data is not structured in a way that allows it to be analyzed. They cannot rely on undocumented macros and formulas when no one remembers how to maintain them.

Let us focus on the two worst side effects of using Excel for reporting or any kind of Dashboards

• Spend enormous time building it
• No documentation on the Macros and Formulas

The hard truth is that most organizations are not equipped with managerial information that is properly structured to make prudent business decisions.

Excel is a very flexible tool, but to create a dashboard you can’t just paste some data and add a few charts, can you? The dashboard must be maintained and updated and if you want to minimize the cost associated with that tasks you must impose some structure to your data.

Exactly!

How do you achieve the seperation between Data and Presentation?

Most spreadsheet users have a widely held misconception that their spreadsheet application is some sort of database. It’s not. It is a calculator effective at manipulating and viewing data, but it is not particularly good at storing and managing it in a reliable, secure, scalable way.

What do you get when you use Excel for Data and Presentation?

• Big, Bulky and bloated spreadsheets
• Hidden worksheets, Hidden Cells, Hidden Rows
• A Spider worksheet – linking to multiple files across the network
• Undocumented Macros and Functions – Impossible to delegate
• Non -Scalable solution
• 100% chance of mis-representation or missing information

How could you live with false representation or missing information when you cannot control what data is fed to the pretty Pie Chart?

Here are some Best Practices for building dashboards using Excel

Best Practices

• Seperate Data and Presentation layer
• Organize your Excel file into a single worksheet if your data is flat and very less number of columns
• If you are extracting data from a database and want to create quick Prototypes, then pull individual tables into seperate worksheets so it maintains the relational integrity.
• Having seperate worksheets will benefit parameter lookups on your dashboard
• Use the Excel file only as a Data-source

So how do you build a dashboard?

Use a dedicated Dashboard Designer tool that can connect to your Excel Data over ODBC.

You could try one of the Dashboard Builders listed below

• Tableau
• Business Objects (Xcelsius)
• Cognos
• Excel Dashboard Designer

Check out Excel Dashboard Tool if you are looking for the following things

• Auto-Refresh – Build the dashboard and set it on auto-refresh
• Web Based Excel Dashboard – Put the Excel file on the webserver and connect it over http to Excel
• Visual Development – Zero Coding or Macro Development Knowledge needed
• Centralized Presentation – Excel data file on webserver or common file share Drive
• Common Parameters for Dashboard Charts – Wire all objects in the dashboard together
• Unlimited Drilling – Drill from Summary to Detail – No Coding needed
• Build Speedometer, Gauges, Dials, Thermometer charts for your Excel Data
• Other standard charts include, Bar,Pie,Stacked Bar, line, Bubble,Scatter, Polar, Waterfall and many more
• Dashboard Mockups – Build Quick Prototypes or Mockups of dashboards

Excel Users now have a powerful way of building dashboards that are easy to build, simple to use and extremely powerful.

Excel dashboard Designer is a all purpose reporting and dashboard development application.

Here is a brief process of how you build dashboard with Excel data

1. Open Excel File ( create as many connections to multiple excel files)
2. Visually join all worksheets or query single worksheet
3. Create parameters (pivot tables)
4. Launch the chart factory
5. Create as many charts
6. Repeat the process from step 2 to build another set of charts

Check this video

Visit Excel Dashboards

First Let us understand what is a KPI?

(source DashboardZone)

A KPI (Key Performance Indicator) is simply a metric that is tied to a target. Most often, a KPI represents how far a metric is above or below a pre-determined target. KPI’s usually are shown as a ratio of actual to target and are designed to instantly let a business user know if they are on or off their plan without the end user having to consciously focus on the metrics being represented.

SMART is an abbreviation for the five conditions of good KPI’s:

* Specific – It has to be clear what the KPI exactly measures. There has to be one widely-accepted definition of the KPI to make sure the different users interpret it the same way and, as a result, come to the same and right conclusions which they can act on.

* Measurable – The KPI has to be measurable to define a standard, budget or norm, to make it possible to measure the actual value and to make the actual value comparable to the budgeted value.

* Achievable – Every KPI has to be measurable to define a standard value for it. It is really important for the acceptance of KPI’s and Peformance Management in general within the organization that this norm is achievable. Nothing is more discouraging than striving for a goal that you will never obtain.

* Relevant – The KPI must give more insight in the performance of the organization in obtaining its strategy. If a KPI is not measuring a part of the strategy, acting on it doesn’t affect the organizations’ performance. Therefore an irrelevant KPI is useless.

* Time phased – It is important to express the value of the KPI in time. Every KPI only has a meaning if one knows the time dimension in which it is realized. The realization and standardization of the KPI therefore has to be time phased.