Vendor Accent

Business Intelligence: Ten tips for a good solution design

Sanjay Shah pens down tips for designing a good and successful BI solution

There are several Business Intelligence (BI) tools now available in the market, each one being one up on the other in terms of technical features and facilities.

However in the final analysis, these are tools and not solutions. BI solutions have to be built using such tools. With the same hammer and chisel one can become an ordinary sculptor or one can become Michelangelo!

Would a costly tool automatically lead to a good BI solution? While a good tool with good features is important, the most important part of the BI solution is the functional design. A BI solution is a highly creative area requiring expert domain knowledge and experience. Developing a BI solution is like making a movie. Using the same technologies some movies become super hits and some become flops. The difference is in the story, its treatment and its delivery.

So how should one go about building a Business Intelligence solution which would be useful to all levels of management? The following ten points would help you in designing a good BI solution:

• Interactive: This is of course the most fundamental requirement of a BI report. The report must be interactive. The user should be able to drag and drop, slice and dice and create different views of data on his own. There should be a method of creating and storing views of the report. Generally from the same BI report, various views can be developed by the user. However if he has to do this each and every time, it becomes unproductive. There should be a mechanism of storing views, and then recreating them at the click of a button. The BI designer should interact with the key users of the report and identify views which are likely to be important from a decision making viewpoint.
• 360 Degree View: A good BI solution should permit a cross functional view of the enterprise. If you take the high level Balance Sheet / P&L statement as a starting point, you should be able to see all aspects of the business.

For example when you are seeing the P&L Statement, you should be able to drill down to the product-wise sales or contribution. One of the first interests of management when they see a drop / increase in sales is to identify the products causing it. If they have to refer to another report for answering this question the timeliness and context of the information will be lost, leading to a failure of BI reporting. As product wise details are rarely held in the General Ledger Chart of Accounts, this would mean combining the P&L Statement details with the Sales & Contribution statement where appropriate. If the number of products is very large, you may include only the top 10 products causing the variation.

Another example is when Sundry Debtors (Accounts Receivable) balance is seen in the Balance Sheet you should be able to see your top n debtors. Again one of the key concerns of management, when they see an increase in account receivables, would be to identify the top debtors.. This would mean integrating the AR details along with the Financial details to enable drilling down to the AR and its aging.

• Intelligent Summarizations: Every BI report should have intelligent summarizations. The report should permit drill downs from these summarizations into underlying details. I would like to distinguish the intelligent summarizations from natural summarizations. There will always be some natural summarizations like those on time dimensions (sales by month), or by geographical dimension (sales by country) etc.. Once the dimensions are identified these summarizations will be provided by the BI tools themselves. By intelligent summarizations I mean those which will give an even better picture of the underlying data.

For example if you are studying Inventories, a dynamic classification into ABC Classes (value based), or FSN classes (Fast/Slow/Non Moving) or Inventory Turns would give a much better picture than just classifying inventory by its category.

Another example of inventory summarization would be to analyze the inventory increases by Quantity Variance and Price Variance.

In the area of Sales Analysis, a dynamic summarization into sales by amount categories e.g. 0-1million, 1-5 million etc. would give a much better picture along with the other natural summarizations.

The user should then be able to drill down from these summarizations to the lowest detail in order to identify problem areas.

• Actionable Information: The BI report should help in triggering actions. The report should therefore not only identify a problem area but also identify the person responsible for taking the relevant corrective action. The report should help in easily identifying exceptions.

For example if you are seeing the Accounts Receivable statement the open invoices should be summarized by reasons and the collector (person responsible for collecting the invoice). For example in a engineering project business open invoices could be classified collector wise and further classified into the following reasons:

• Normal
• Installation Problems
• Short Supply Problems
• Incorrect Rates
• Deduction Against TDS, Etc

Another example is creating a report for an FMCG company which identifies slow/non moving items in inventory in one geographical area, finding whether the same item is a moving item in another area, and creating a stock transfer recommendation report.

• Controls across Functional Areas: The design of the BI reports should ensure that key financial controls across different functional areas must tally so as to create a very high level of confidence in the numbers.

For example if the sales as per the sales analysis module and that as per the general ledger do not match (e.g. debit/credit note entries passed directly in GL), then this should be identified and such entries should be shown in the sales analysis module as a separate exception item so that the overall numbers match.

Similarly if the inventory value as per the inventory module and the inventory value as per the General ledger do not match, then this difference should be identified as a separate line and shown in the inventory statement. It should be shown as a separate exception item, so that the overall numbers match.

• Comparative Reporting: Wherever possible, the report should include comparisons with some norms. The comparison can be with budgets, forecasts, previous year’s numbers, industry standards etc. The key question to be answered should be ‘how are we doing in comparison with ….?’. As is generally the case, comparative reporting has much greater impact than absolute numbers.

For example if the year on year growth of my company is 30%, it sounds impressive, but if the year on year growth of the industry to which my company belongs is 60% then my company’s growth rate pales.

• Graphical Presentation: Senior managers have very little time to read through tabulations of data and decipher trends themselves. Graphical presentation strikes home the point immediately. Try to make your reports as graphical as possible, but without losing interactivity. For example if you are seeing the yearly trend of sales for the entire country, you should also be able to see the trend for a particular product or for a particular territory, in the same report.
• Presentation Quality Reporting: The reports from the BI solution should be directly presentable to the different managerial levels, without having to put them in any other presentation tool. There should be no manual intervention between the BI tool and the report given to management, so that there is no possibility of any changes. This would mean that particular attention is given to :
• Field names should be understandable by the user, and should not be database field names
• Report layout, formatting and its coloring should be pleasing to the eye
• Numbers should be in lakhs / millions etc. without decimal points, so that there is no unnecessary clutter.
• Widespread Usage and Security: BI reports should not be just available to the elite few of an organization. It should not appear that the elite few have tools by which they can ‘catch’ others. The BI reports with their analytical abilities should be available to all as per their requirements. After all the purpose is to improve the working of the entire organization. Because these should be widely used, they should also have adequate security. A person should be able to see only that data which is relevant to him, or which he is authorized to see. There should also be adequate protection to prevent such BI reports from being sent to unauthorized persons or used by ex-employees of the company.
• Intuitive, Easy, Extensible: The design of the BI reports should be intuitive and they should be easy to use and navigate. If the user has to use a new tool for this purpose, the chances of failure of the BI initiative are high. The BI tool should also be extensible by the user. If he wants to use the data to generate some projections or create different views, he should be able to do so easily and intuitively and without the help of IT support.

In our practice we have observed that only in rare situations intelligent BI reporting can be done straight out of the data coming from ERP. In real life scenarios a large number of business rules have to be applied, data from non-ERP databases has to be integrated and various additional dimensions (intelligent and natural) have to be created in order to make the data reportable. The intelligent BI reporting environment actually becomes an application in its own right.

The author is the CEO of Prosys Infotech Private Limited, a Pune based company specializing in developing BI solutions sanjay@prosysinfotech.com.