For some reason the topic of “best practice BI” has been popping up very frequently of late. In the past 3 weeks I have been asked to speak on this topic at least 5 times. IN the hopes that I can head some of this repetitive discussion off (or at least leverage this blog with others in the future!) I thought I would take the time to at least set the framework of the discussion.
For some reason the topic of “best practice BI” has been popping up very frequently of late. In the past 3 weeks I have been asked to speak on this topic at least 5 times. IN the hopes that I can head some of this repetitive discussion off (or at least leverage this blog with others in the future!) I thought I would take the time to at least set the framework of the discussion.
I have to be upfront on the whole “best practice” term. If there was some sort of practices that really lead to leading class BI then they would be some common as to not be “best”. The people that are doing the “best” are going beyond the normal practices and really breaking down the barriers to innovation and analytics. I hope that by sharing what these leading examples are they will become a common practice. The underlying theme is that you want to move from reporting on the business to understanding the business well enough to actually take actions that direct and cause business to occur.
Editor’s note: Rob Armstrong is an employee of Teradata, a sponsor of The Smart Data Collective.
So with that off my chest I will get off my soapbox. The next comment I typically make is to create discussion around what you mean by BI? There are many flavors to this term and it gets thrown around very casually. Like many other terms in the data warehousing arena, it helps to first get everyone to agree on what each other means.
So, what do I mean by BI? I like to read an acronym backwards. BI is not about business intelligence, it is about having enough intelligence regarding your business that you can make, and take, relevant, timely, and profitable actions. Many see BI as simply reporting tools or “interactive analytic” tools. Others use the term to mean dashboards and data mining. I see the term encompassing all these areas. However, each of these purposes has some particular service level expectation and responsibility from the user community. I see it as follows:
Level 1: “Basic Canned” Reports: – These are the template reports that are either pushed directly to the user or are accessible via a portal type environment. Here the user has NO ability to change the content of the report but simply get the latest version of some pre-determined output. Think weekly sales report or monthly statements. These can also be the underpinnings of a starter dashboard environment.
Level 2: “Canned Ad-Hoc”: – Here the user is still getting pre-determined and pre-optimized reports but they are at least able to target parameterized dimensions. This may be such as number of weeks, districts or regions, or even customer segments.
Level 3: “Customized Canned Ad-Hoc” – Things are staring to get a bit more flexible in this layer. Here the users can not only define the dimension boundaries, but can also determine the columns or calculations that appear on the report. They are not allowed to create new calculations or derived columns but there is quite a bit of latitude to the user.
Level 4: “Create your own” – Now the user has pretty much free reign limited only by their security access needs. They can determine the columns, dimension ranges, and even create new metric calculations. This may or may not be done via a tool that creates the SQL. If a tool is not employed then the users need to be educated in SQL and optimization techniques.
Level 5: Data Mining – In the other levels the user is asking questions to get answers. In data mining the user is asking questions to understand what questions really need to be asked. They are looking for relationships and trends. This means very robust access, usually a sophisticated tool like SAS, KXEN, or R (just to name a few).
Now that we have a definition lets set the right expectations regarding response time service levels and IT or User requirements.
BI level | Initial Response Time Service Level | IT / User Requirement |
1 | Under 2-5 seconds | Highly optimized by IT; Well defined by Users |
2 | Under 10 seconds (can be tiered by parameter blocks) | Highly optimized by IT; users determine “typical” dimension blocks |
3 | Under 15-20 seconds (can be tiered again by blocks) | Optimized by IT; KPI’s defined by users, |
4 | 30 seconds – several minutes | Users understand SQL processing and creation. May also create temporary tables or store results. |
5 | None | Trained in data model and SQL practices |
Some last comments on the above table. As the users are getting more flexibility they need to accept that not all queries are “sub second” queries. They also need to understand that in order to get the performance they will need to work with the DBA or Application teams to ensure the correct optimization (such as secondary indexes or correct usage of partition eliminations) are executed.
In the above table I also noted that response time can be “tiered”. This means that if you only ask for 4 week and 3 regions you should expect that runs faster than someone who asks for 40 weeks and 8 regions. If users can have a general understanding of performance expectations then you will avoid a lot of arguments later.
I also note that the response times are “initial” ones. If users find that level 3 or 4 analytics are provide critical, insightful, and actionable information then they become candidates for further optimization or higher prioritization but that discussion needs to be based on the level of effort versus the tangible business benefits.
So, what do you mean by BI? Do you have a tiered level of expectations or do people as for BI thinking they are going to get something and then they are delivered something else?
Next time I will dive a little deeper on this topic with my thoughts on a particular section of BI: Dashboards. Stay tuned…
