This article is my Summary about how to execute a plan for a business intelligence (BI) project, it is not necessary that all the mentioned facts will be the optimal and best methods nevertheless represent just my own experience and advises.
What is BI?
The ability to provide very distinctive reports and visualizations exhibits the facts about your business / activities which ultimately contributes into the broad vision of the organization. As contrary for operational reports, BI provides summarizations , KPIs , Recommendations , Comparative analysis , single view for all the underlying data and information.
Planning the project:
Before kick -of any task related to the project we should answer some questions:
1. Who needs BI Reports, for which objective he/she will use it ?
2. Are we ready in term of internal culture to accept this change?
After we answer those questions and determine that we are truly need this project let us imagine that we have a bank called (Freedom) which will be the imaginary organization that we are trying to implement the project steps on.
As answer for the above questions:
1. Who is need BI Reports, for which objective he/she will use it for?
Our CEO , Top management as well as branches managers would like to close monitor the bank performance , currently the reports delivered as word documents , detailed excel sheets which make the interpretations so difficult especially it arrives late (once a week) due to long process in gathering and preparing those reports.
2. Are we ready in term of internal culture to start this change?
Partially YES , we do have a few managers who appreciate the importance of BI reports but in other hand we have those ones who want to keep the heritage and do not change in any processes of making current reports as it directly linked to their reputation and being the people who designed it long time ago 😊
To overcome the internal conflicts that might happened in the future during the project execution we need to carry out a change management plan and I suggest being like:
1. Appraise the managers who expected to be inline with project outcomes and trying to nominate one of them as the project sponsor.
2. Design a series of workshops and orientations trying to eliminate the miss-understanding and inject the feeling of ownership of the new product and how will it contributes toward the organization stability and brings new responsibilities (People always feared of getting dismissed)
Before designing those workshops we need to perform very crucial assignment as outcome of answering the question (1) , by giving example about the current reporting process and how it will looks like after we finishing the projects milestones , if we succussed to design an imaginary reports that we are (expecting) to deliver that will be great.
Phase one – (Analysis)
I might assume that delivering a BI report is not quite different from designing a traditional operational reports , we have to analyse the current situation by dividing the Freedom bank for three parts
1. Top management
2. Supervisors (Direct Managers)
3. Power Users (Remaining Staff)
For each part we have to analyse their reports, what are they, who is preparing them , data sources? , data sources types ? digitized or not ? the current delivery time? The look and feel ? improvement areas? …etc
BUT , we have to start by the part one , being the targeted user group as well as sponsors of the project we should try to determine the most significant report(s) and kick-of our project by them , then descend for others and repeat the same process
We should have among our team business representatives as a product managers , who can review our bank vision and missions , conducting workshops to guide us in term of reports prioritization and playing the part of broker between developers and business team (Business Analysts).
Those guys are so crucial to project success, to be honest, the whole project fate will lie on their shoulders, so they should feel so welcomed into the team, transfer the sense of ownership to them.
Then lets start , why are we waiting for ….
Imagine we have determined the most important report is called (Total Loans and deposits) , and we decided this by direct answer from our CEO followed by a report called (Banking Highlights)
1. Total Loans and deposits:
As we agreed, we started by the most important report for our CEO and went to collect the information about this report , it was like following:
Report :Total Loans and deposits.
Users : Top management.
Format: Excel Sheet with diagrams.
Data sources :
1. Banking System (Digital)
2. Word document from (finance team)
3. Internet info (Tax chamber)
4. Branches Position (Fax + email)
When we analysed this report we discovered the following facts:
1. A group of 3 staff members are dedicated to only this report.
2. The report composed of different facts correlated by date dimension (week), those facts are gathered from different sources:
2.1. Most of the data gathered from our internal banking system.
2.2. To calculate our growth rate, we need to gather data from a word document sent by email from finance department.
2.3. We should surf the website of tax chamber to determine the tax rate which we have some calculations depend on
2.4. Our branches are working on offline banking system, so they had to send their data sometimes by email and by fax due to internet issues.
Having said that , now its time to real solutions…
Phase Two – Methodologies
It worth to say that BI reports are visualizations built on top of data warehouse composed of different dimensions and facts designed as certain report need them.
Dimension as it known for you , used to slice and dice our data , enabling us for filter , drill and accumulate our information (facts).
e.g. – Date , Branches , Currency , Sectors ,….etc
Facts are our metrics , financial numbers , counts , …etc represents our business.
e.g. – Sales , Deposits , Loans , Count of products , Salaries ,….etc
simply, we need to relate all our required report facts by the required filters (Dimensions)
Back to our example we have to come up with a process that enable us to digitize all our data (enhance number 1) targeting to collect them on daily bases , so our report will be daily rather than weekly (enhance number 2).
To accomplish that we decided to design excel templates to be distributed on both finance team and our branches as well as create internal network shared folder into our Fileserver to be dropped at
So rather than receiving word documents and fax files we should now have those data into that folder daily)
Now its time for a process and deadlines…
Each branch , as well as finance team has to drop their document into their nominated folders not later than 9:30 am (after discuss that with them) then our ETL process will be triggered at 9:45 am.
The same ETL process will be used to pull data from our banking system and from Tax website.
The big task before that is to cleanse the data by identify the missing , unify the data elements …etc
For example you might face the issue of having the same branch name came in two different versions, one like (Freedom Western Branch) , and in other source came like (Freedom West Branch)
At this point you need to pick the right one and correct the other to it , there are many data quality applications which might help you to accomplish that , at the end of this summary I will list all tools I’ve tried.
The notification of load success result should be sent to dedicated data engineer who will be required to interfere in case of any issues happened (Quality and Reliability).
After reports data has been gathered, now we already know that from CEO requirements the report should be filtered on following dimensions:
3. Sub-regions (Districts)
Then our dimensions can be changed (Lazy loading) by adding the NEW regions and subregions right after daily load.
Then we have our fact table which is (Total of Loans and Deposit ) should be relates to those above three dimensions , we have decided to follow Star schema as our main structural Data warehouse pattern
In computing, the star schema is the simplest style of data mart schema and is the approach most widely used to develop data warehouses and dimensional data marts. The star schema consists of one or more fact tables referencing any number of dimension tables – Wikipedia
Hence our design might take the following diagram:
After we made sure that all our data loading process has been completed successfully our next task is Cube processing.
Phase Three – OLAP
This part is to guarantee the efficiency and rapidness of delivering our reports , basically in operational reporting we are performing all our calculations , selections , cleansing at the time of report generation , this called OLTP (Online Transactional Processing) but as contrary to that, OLAP processing enabling us to pre-aggregates , analyse our data and just deliver the ready-made information upon request (Online Analytical Processing), the official definition of OLAP is like following- Online analytical processing, or OLAP, is an approach to answer multi-dimensional analytical queries swiftly in computing. OLAP is part of the broader category of business intelligence, which also encompasses relational databases, report writing and data mining.
In this phase we are going to design our Analytical Cube built on top of our formerly designed data warehouse , the cube will incorporate all the required measures and calculations written by specific analytical querying language such as MDX.
Then after we tested our cube results we will schedule its processing to be occur daily at 10:00 am after we have loaded all our new data .
Phase Four – Visualizations
Its not matter how much efforts you have putted before if your final meal it’s not tasty enough , so we have to be so careful about designing our final dashboards and visualizations that will be delivered to report consumers and in our case => CEO..
So either you will be promoted or …. FIRED ☹
Our visualization tool will be connected to the CUBE as a data source , encompasses of different areas contain the all required information and filters to view and play with the report , as example I found following dashboard in the internet , it might answer the questions of our CEO and fulfills his requirements :
Phase Five – Publishing:
Our selected tool will probably assist us in term of how can we deliver our dashboards, the best two methodologies you can deliver your reports on is mobile + Intranet
We can publish our dashboards and reports into our internal network portal (if we don’t have it we should add this sub-project to our tasks lists 😊 ) then we can offer this report via mobile app , many of tools at the market such as tableau and Power BI are empowered by dedicated mobile apps that can be connected to your account enabling you navigating your reports online anywhere any time.
Then we should train our CEO on how to use the report , listing all enhancements that we have done earlier , communicate them for all staff , preparing them to the next journey for other report (Banking Highlight) which should look like following at the end 😊
Sure we will get benefit from already created dimensions just adding the missing ones plus new facts and our star schema will grow more become more interconnected.
Tools and Technologies:
Here I am not trying to advertise or nominate any tools as the best ones , basically because I’ve worked on single stack and never tried others but ultimately all of them will enable you to perform the whole tasks which are the same and very known for any BI project , but regarding my experience I have used the following tools and I found them very straight foreword and easy to learn :
Analysis Phase :BizAgi , Microsoft Visio
ETL: SQL Server Integration Services (SSIS)
Data Cleansing: Data Quality Services (DQS) , Excel
Data Mastering : Master Data Management (MDM)
OLAP: SQL Server Analysis Services (SSAS) ,MDX
Data warehouse : SQL Server
Visualizations/Reports/Dashboard : PowerBI
Publishing : Microsoft SharePoint
I am repeating once again, any similar tools might produce the same results, so you are not required to pick the listed tools and technologies.
Phase Six – What Next?:
The just created data warehouse will be like a bone for a big skeleton of data projects, as we should know BI is provide you by fancy reports about what has happened , but how about the future? How about our next year’s sales? COVID-19 effects on our business? recommendations for new alternatives of our business model? Best methods to adhere with? things should avoid insuring high profitability? and many further questions can be answered if we dive into data science projects and identifying data wisdoms
I will dedicate another article about these projects , meaning while I hope you enjoyed reading this summary , and if you have any queries or contributions please feel free to add them for the benefit of all.
Mohammed Awad SirElkhatim Nasir