In my last article, Sivad identified Davis’ business model that pertained to the dashboard and its respective metrics as self-defined by Davis. This article will cover the basics for another important piece to the realization of a dashboard: understanding the data sources behind the metrics and consolidation.
In Part 1 , Davis mentioned that there were two separate databases: one database stored the online sales and the other stored the telephone sales. This is a common scenario where there are disparate data stores and no unified data warehouse for analysis. Sivad does not profess to be an expert in databases; he stays focused on implementing dashboards but has a good enough understanding of Relational Databases (RDBs).
Sivad: “Your database team knows the data better than I do. I would strongly recommend that they consolidate their databases into one centralized storage. In my experience with working with other database groups, here are the options that I would present to them.”
- Replication or Batch Jobs to grab only the necessary raw data for the metrics into a centralized database;
- For real time data, use web services or any other network data transfer methodologies to pull directly from the databases.
Davis: “I’ll talk to the database team, and see what they think about your proposed solutions.”
Davis comes back an hour later, and relays the database team’s solution.
Davis: “Sivad, they said they will create a consolidated database and they’ll update it nightly, as I only need daily metrics. They will provide you with a data access API that would look something like this.”
Here is a quick description of the Data Access Layer design.
The Data Access Layer will abstract pulling data from the consolidated data source made by the application. In most cases, these “Database Functions” are stored procedures. The database team is thorough and provides previews of the expected data returned.
DailySalesRevenue will return a range of daily revenue
SalesRevenueByRep will return a rep’s revenue within the same range
SalesRevenueByState will return each state’s revenue within the same range
Sivad: “Great Davis! We’re ready to get underway with defining the Business Logic.”
Davis: “I’m getting exciting about this Sivad. I’ve been waiting many years for this, and it’s about time my company agreed to proceed with this dashboard initiative.”
The journey continues in the next article, and it will cover the concept of the Business Logic behind an application.
Article Summary:
This article covered a common database issue and discussed potential solutions. The proposed solution in this instance is a common one that I’ve used in the majority of my dashboard projects. In my next article I will discuss the user experience of a dashboard and how it is as important as the actual metrics themselves.
Technical Footnote:
I’ve recommended that many of my clients go with stored procedures whenever possible. This separates the application from the data sources in a maintainable manner, i.e. there are no database query strings in the application code. There are times when it makes sense to mix the Business Logic into the stored procedure, but most of the projects I’ve worked on usually handle the Business Logic from the application side. I will cover in detail the concept of the Business Logic in the next article but, in short, it handles how the user interface should consume the data. Here is a simple schematic design of a dashboard application.
In this article, we covered the Data Access Layer; subsequent installments shall cover the Business Logic, UI Functionality, and Presentation Layer and their inter-relationships.