Getting into designing dashboards and need some help identifying each technical layer along the way

Question

So I will be embarking on designing a dashboard that will display KPI's and other relevant information for my team. Since I am in the early stages of this project and am not very familiar on the technical process behind designing a dashboard, I need some questions vetted out first before I go and shop for some solutions to avoid reinventing the wheel.

Here are some of my questions:

1. We want a dashboard that can provide live-time information via our data sources (or as close to live-time as possible). What function allows a dashboard to update itself with concurrent datasources? From a conceptual standpoint, I can understand creating a dashboard out of Microsoft Excel, and having the dashboard dependent on the values you may have set within your pivot table.
2. How do you make a dashboard request information from multiple datasources on its own? Just like the excel example, a user may have to go into the pivot tables to update values, but I want to know how would a dashboard request this by itself and what is the exact method from a programming standpoint? Does the code execute itself every time you refresh the webpage?
3. How do you create datasources organically? I know for some solutions such as SharePoint BI Center, there are pre-supported datasources like an excel sheet or SharePoint and it's as easy as uploading your document and letting the design handle the rest. However, there are going to be some datasources that I know that will need to be fetched. Do I need to understand something else like an event recorder in order to navigate this issue?

Answer 1

Introduction

The dashboard (or a report, respectively) is usually the result of a long chain of steps. Very much simplified it could look like this:

src1
  |------\
src2     |              /---- Dashboards
  |------+---[DWH]-[BR]-+
src n    |     |        \---- Reports etc.
  |------/  [Big Data]

Keep in mind, this is only a very, very simple structure of a data backend / frontend.

DWH means Data Warehouse, where data might be stored temporarily (you referred to this as fetching). This could be a database, could be a Big Data engine, could be a combination of both...

Afterwards, there are Business Rules (BR). Those might be specific rules in how different departments calculate and relate to data, but also simple things like algebra.

Questions

So, the main question should not be about the technology:

• What software should we choose?
• How can we create a dashboard?

but on the contrary focused on your business processes (see it like a top-down view):

• How does our core process look like? Where would I like to measure data?
• How would department a calculate sales in difference to department b? Should all use the same rule?
• Where does everyone store the data? Can we access it? Do we need structural data?
• And, very easy to forget but also easily sometimes one of the biggest parts: Is the identifier of a business object (say, sales id) everywhere build and formatted in the same way?

Conclusion

When those questions are at least in the back of your head and you keep working in this direction, more or less automatically data will spill out at certain points of that process.

Then it won't matter if you use Excel, a small-to medium app like Tableau, Tibco Spotfire, QlikView, Power BI or you want to go full scale with a big Hadoop backend, databases and JasperReports, Apache Drill, Pentaho, SSIS on top of it... it will come out eventually.

TL;DR

Focus on the processes first. Make sure to understand them. Draft in Excel. Then proceed in getting the data and the tools you need to help your use cases. It will work out much better from a "top-down" approach than trying to solve your requirements with tools only.