What is the performance relationship between a Database and a Business Intelligence Server

This article will be a bit long; it covers a complicated topic that I have been studying for quite some time. 

I have run across the need to explain this topic on a number of occasions, and over time my explanations have hopefully become clearer and more succinct.

The concept that will be discussed here is the performance relationship between a database server and a business intelligence server in a simple data mart deployment. 

Rarely are data mart deployments simple, but the intention for this is to be a reference article to understand the relationships between the server needs and the performance footprint under some of the various scenarios to be experienced during the lifecycle of a production deployment. 

Here is a simple layout of an architecture for a DataMart

Very basic image, the D is the database server, the F is the front-end, the U are the users, and they are all connected via the network.

To be precise this architecture represents a ROLAP (Relational On Line Analytical processing) built on top of a dimensional model (star schema) implementation. The dimensional model is assumed to be populated and current with totally separate ETL processes that are not represented in this diagram.

The “D” represents any database server: Oracle, MySQL, SQL Server, DB2, whichever infrastructure you or your enterprise has chosen.

The “F” represents any front-end business intelligence server that is optimized for dimensional model querying, and is supported by a server instance: Business Objects, Cognos, Pentaho Business Analytics,
Tableau Server. The desktop specific BI solutions do not fit in this reference model for reasons we shall see shortly. 

In my early thoughts on the subject, I envisioned that the performance relationship in a properly done DataMart would be something like this: 

This is a good representation of what happens. 

On the left side of the chart we have the following scenario.

When the front-end server responding to a user interaction sends a request back to the database for aggregated data like: “show me the number of units sold over the last few years”

One could imagine the query being something like: Select Year, Sum(total_sold) from Fct_orders fo inner join Dim_Date dd on fo.date_key = dd.date_key.
The dutiful database does an aggregation, provided all of the statistics are current on the data, a short read takes place and more CPU and Memory but less Disk I/O is used to do the calculation.

In the graph this is represented by the high red-line on the upper left.

The results returned to the front end are small. A single record per year of collected data.
The CPU and Memory load on the front end server is tiny shown in green on the lower left.

On the right side of the chart we have the following scenario.

When the front-end server responding to a user interaction sends a request back to the database for non-aggregated data like: “show me all of the individual transactions taking place over the last few years”

One could imagine the query in this case to be something like: Select fo.*,dimensional data from Fct_orders fo inner join (all connected dimension tables).

In this case the database server has little option but to do a full table scan of the raw data and returning it.

In the graph this is represented by the lower red-line on the right (more disk I/O, less CPU and Memory), then the data is returned to the business intelligence server.

Our Front-End server will have to do some disk caching, as well as lots of processing (CPU and Memory) to handle the load just given to it, not to mention things like pagination, and possibly holding record counters to keep track of which rows the user has seen or not, among other things.

This graph seems to summarize the relationship between the two servers rather nicely. However, something is missing.

I had to dwell on this image for some time before I was able to think of a way to visualize the thing that is missing.

The network.

And even then there are at least two parts to the network.

The connection between the front-end server and the database, followed by the connection between the front-end server and all of the various users.

Each of these have a different performance footprint.

Representing the database performance, Front-End performance, and network performance for both the users and the system connections is something with which I continue to struggle.

Here is the image I have recently arrived at:

This chart needs a little context to understand the relationships between the 4 quadrants. 

Quadrant I is the server network bandwidth. In a typical linear relationship as the data size increases from the database to the front end the server network bandwidth increases.

Quadrant II is the database performance relationship between CPU/Memory and Disk I/O for a varying query workload. For highly aggregated queries the CPU and Memory usage increases, and the Server Network bandwidth is smaller because less data is being put on the wire.  For less aggregated data, and more full data transfers the Disk I/O is higher, Memory is lower, and back in Quadrant I the Server Network Bandwidth is higher.

Quadrant III is the Front-End server performance comparing CPU/Memory and Disk I/O when dealing with a varying volume of data. As the data increases from the database more resources and caching is needed on this server.

Quadrant IV is the User Network Bandwidth this is the result of the front end server responding to the requests from the user. As the number of users increase the volume of data increases and more of a load is put on the front end server. Likewise, the bandwidth increases as more data is being provided to the various users.

This image is an attempt to show the interactions between these 4 components.

The things that make this image possible is a well-designed dimensional model, a rich semantic layer with appropriate business definitions, and common queries that tend to be repeated.

This architecture can support exploratory analysis, however, the data to be explored must be defined and loaded up front. For exploratory analysis to determine which data points need to be included in the data mart, that should be done in a separate environment.

I created all three of these images with R using iGraph and ggplot2 with anecdotal data. The data shown in this chart is not sampled, but is meant as a representation of how these four systems interact.  Having experience monitoring many platforms supporting this architecture, I know for a fact that no production systems will actually show these rises and falls the way this representative chart is doing.

However, understanding that at their core they should interact this way should give a pointer to where a performance issue may be hiding in your architecture. If you are experiencing problems. The other use-case of this image is an estimation tool for designing new solutions.

All that being said, much of this architecture may be called in to question by new tools.

Some newer systems, Hadoop, Snowflake, RedShift actually change the performance dynamics of the database component.

The Cloud concept has an impact on the System Bandwidth component. If you have everything in the cloud, then in theory the bandwidth between the database server and the front-end server should be managed by your cloud provider. There may need to be VPC pairings if you set them up in separate regions.

If these are being run within a self-managed data center should the connection between the database server and the front-end server be on a separate VLAN, or switch? Perhaps.
Does the front-end server use separate connections for the database querying interface and the user facing interface? Should it?

Do you need more than one front-end server sitting behind a load-balancer? How many users can one of your front-end servers’ support? What are the recommended limits from the vendor? Should data partitioning and dedicated servers per business unit be done to optimize performance for smaller data? 

These are all types of questions that arise when looking at the bigger picture. Specifically when you are doing data systems design and architecture. This requires a slightly different touch than application systems design and architecture.

Thinking about applying this diagram in your own enterprise will hopefully give insight into your own environment.

Can you think of a better way to diagram this relationship? Let me know.
The code and text are posted here. 


How many times do you have to stand in the rain before lightning strikes?

Recently, I have seen this picture a number of times:

This is a cute little anecdote about taking risks and being open to opportunities.

Here is a counter thought:

How many people were invited to other rooms, and those people are not billionaires now?

How many people have spent nights and weekends coding or building someone else's idea only to never see a dime?

Now don't get me wrong. I absolutely love working with entrepreneurs!

The excitement of a new idea, the thrill of building things from scratch, the camaraderie of working on something that is new and rushing to get something to market before someone else builds something similar.

These are fun things to work on.

However, everyone should be committed to the goal with the same amount of buy-in.

As I wrote about previously Beware The Partnership where the technology person or team is the only one working on the project. This is called contracting.

If you and a friend have an idea, and you are both working various angles on the idea, go for it.

If you are not a technology person, and you need a "partner" to do the actual building part, you have just hired a consultant. They may work with you for some sort of percentage of future ownership (sweat equity), but at some point sweat, motivational speeches, possible future options do not put food on the table.

Never be afraid to take risks.

The risk for the technologist is spending time, effort and expertise on a project that may never pay off.  My caution for you if you are a technologis is this: Don't expect to make your expected hourly rate. Be flexible, negotiate maybe even suggest that the idea person pay for equipment of some other tangible if they are unwilling or unable ot pay you directly.

The risk for the idea person is that you may be paying for something that does not quite fit in with your vision. My caution for you if you are an idea person is this: Either be willing to pay for expertise that you do not have, or simply do not talk about your idea with anyone. If you do not have the ability to pay for expertise, use Lean techniques to figure out the quickest path to make money with your idea. If you are currently working, leverage your savings or take a portion of your current income and save it till you can afford to pay for expertise, experience, equipment or some other tangible item to help you build your idea.

If you can't risk losing a bit of money(for the idea person), or time (for the technologist) then don't get involved in building out something.

If you are currently in either of these situations, and are uncomfortable talking about these things, share this link with your business partner. Have a conversation about the uncomfortable topic of money early on. If you are willing to commit your future to an idea with your partner, you should be willing to discuss money, and you should do it sooner rather than later.

You do have to take risks to be successful. Sometimes it simply rains.

On rare occasion lightning strikes and you are able to convert from working on a side project to doing something you love full time.

Either way, you will get wet.

The question is, how wet are you willing to get?

Will you take a bath, or be singing in the rain? 


Predictive Analytics World New York 2016 Supercharging with Ensemble Models.

The Wisdom of Crowds
The Wisdom of Crowds (Photo credit: Wikipedia)
Dean Abbott taught this class on Ensemble models.

One of the in class demonstrations was an example from The Wisdom of Crowds. He passed around a bottle with some cereal in it. Everyone guessed, and then he averaged the guesses.

Two people were closer than the true answer, but an Ensemble model (An average of all of our individual guesses based on our internal model of the bottle and the size of the cereal.)

There were also hands-on demonstrations with Salford Systems predictive modeler. You can find out more about the tool at this link

Dean is a thorough instructor, and clearly could educate all of us on the various ways of doing predictive modeling.

English: A manually drawn decision tree diagra...
He talked about Logistic and Linear regression, decision trees, random forests, and how to combine these specific models with various options as an Ensemble model.

He touched just briefly on deep-learning.

I look forward to hearing from him again, I think every time I would be able to hear from him I would learn something new.

Dean recommended to read his book: Applied Predictive Analytics Principles I look forward to starting to read this on the flight back.

It has been an exciting time in New York. Whenever I attend these conferences, and workshops I always feel like the more I learn, the less I know.

I did take a few pictures, and made a few tweets about one day speaking at a future event. I think I have a lot to learn to be on par with these speakers.

Continuous learning is the key to expertise.


Predictive Analytics World New York 2016 Day 2

Daniel Kahneman
Daniel Kahneman (Photo credit: Wikipedia)
Day 2 kicked off with a bang.

John Elder speaking about the way we think and perceive solving problems. Daniel Kahneman's concept of System 1 thinking versus System 2 thinking applies to predictive analytics because many times our System 1 thinking can overwhelm our System 2 thinking.

The excellent book Fast and Slow thinking by Kahneman is a great overview of these concepts.

Dean Abbot and Karl Rexler followed up the great kick-off with a Question and Answer session. A couple of the more interesting questions were:

"How do you merge Analysis frameworks with Agile Frameworks?" (Answer: It's hard)


"Should Data Science report to business units trying to solve a problem, or Information Technology departments where the same resources can be shared and leveraged across the organization?" (Answer: It depends on your organization and support for Data Science.)

Pasha Roberts gave an amazing overview of Talent Analytics approach to understand workforce movement and flow of employees through an organization. Agent based models, Markov-chains and directed Graphs were the details of how to solve this problem. I was on cloud 9 it is so refreshing to hear about applications of these techniques to solving business problems. Most people I speak to about these techniques I lose quickly. :)

A few more vendor presentations, and some Q&A sessions as well as talks on Design thinking and graph analysis of food recommendations rounded out the rest of the day.

Tomorrow is more workshops. I will be attending the workshop by Dean Abbott on Ensemble Models.

New York has been a great trip, it is always a great experience to spend time with professional peers that are wrestling with some of the same problems and challenges.


Predictive Analytics World New York 2016 - Day 1

English: Phases of the CRISP-DM process França...
English: Phases of the CRISP-DM process Français : Phases du processus CRISP_DM (Photo credit: Wikipedia)
Day 1 was exciting.

Great keynote by Eric Siegel on understanding whether a discovery you have made is BS or not.

(Bad Science for those of you not there.)

Vast search introduces new considerations in working with predictive models in large data sets, because, in a large enough data set almost any conditions can be found.

After all, "If you torture your data long enough, it will confess to anything."

Really knowing whether any finding is valid is very important when dealing with big data.

I followed the track related to Uplift Modeling.

I will need to go through Eric's book on Uplift modeling to best understand it but the examples provided in the sessions on Day 1 were enough to not only whet the appetite, but also dive in and do some experiments.

I also met with the fine people at Elder research, it turns out we both worked on a very similar government project some years ago.

They have done some research, and applications on the integration of CRISP-DM with the Agile framework. This is most intriguing I have to follow up with them to learn more about how they married such different methodologies.

At the end of day 1 I signed up for dinner with strangers, there were three groups of about 8 folks each who were sent to various restaurants throughout New York City. We had some good conversations about our individual struggles to bring Predictive Analytics to the masses.

I made some new networking connections, and look forward to staying in touch with some really positive professionals.