In terms of the BI Landscape, the organization is already had a mature solution in place with other aspects of BI such as ETL and BW being utilized to enhance the process. Company have been actively (presently) using Tableau as their major reporting tool for majority of the reports that have been developed in the past.
Traditional Solution Model
architecture in place for BI Consultants to work with. Certain vertical under Company had not been exposed to BI (such as Manufacturing) before so multiple projects are being carried out simultaneously for the same.
1. Time Consuming- Daily creation of reports.
2. Manual efforts required from Admin team to extract, clean, prepare data daily and Business Analyst team to
create reports and share with the end Business users.
3. Static excel reports.
4. High turnaround time.
Tableau’s Approach For Solving Company’s Problem Statement:
Tableau Solution Model:
established BW solution of SAP Net Weaver in place & making use of databases such as Oracle and MySQL depending upon the source where the data is being extracted from. Majority of the reporting happens on top of these sources.
For non-SAP Sources, the source data lies in multiple datasets and are aggregated to a staging database of MySQL where capabilities of ETL Tool such as Talend is used to transform the data as per the Business Logic & then this staged data is made available to BI Consultants for further report development.
For SAP Sources, the data from multiple points are aggregated into SAP Net Weaver BW solution, where transformation & cleaning of the data is done. This data is stored as a query 7 made available to Consultants for reports generation.
Ease of report development and automation was the major use case that Tableau handled efficiently. This significantly
reduced the turnaround time of the projects.
As highlighted in the high level diagram of the solution model, it is evident that not only company has a rich BI
interface comprising of tools such as Tableau, Micro strategy & Power BI but to enable such tools to function
smoothly and without any limitation an ETL level is introduced on top of the Database and Data Warehouse
architecture. This streamlines the data being brought into the BI Tools and also improves the performance of the BI
Tools in multiple folds. Also, introduction of an ETL level enables company to create Business logic at the Data source
level instead of doing it in a reporting tool which makes it reusable.
- To publish reports and share with the business users via their in-house web portal.
- To restrict end users to access data as per their role.
- To schedule subscription to receive reports screenshots in the mail.
- To automate reports refresh process for multiple dashboards (from various data sources).
- To enable ease of creating the reports.
- Reducing the turnaround time for BI Projects.
Dashboards Prepared For Company
the Dashboards have been successfully completed are:
This dashboard essentially capture the equipment level details in dimension such as Equipment Category,
Equipment Working Status (Idle, Pause, Working etc.). This data in turns helps us compute the overall Equipment
Operational Efficiency for a particular equipment.
KPI’s and metrics, the end user are interested in:
OEE = Performance * Quality * Availability (for an equipment)
To compute OEE all the individual metrics of the batch such as:
- Performance (determined by the Rated speed of an equipment vs. the actual speed).
- Availability (computed based on the running time by total time for the equipment).
- Quality (Taken as a constant (100%) in this particular case) is usually has a list of metrics which needs to be
quantified based on which the completed quality for the batch is decided.
selected date range.
This dashboard records the time spent by each batch in various stages of manufacturing (such as Sifting, Blending,
Compression, Coating & Packaging) and also the time each batch spends in between each function. This overall
picture helps us pinpoint the bottlenecks in the process of manufacturing also highlighting the Turn Around time
for the complete as well as individual processes.
a. Avg. Time taken by Batches in each process individually. Sum of these averages gives us the Avg. Processing
b. Similarly Average Wait Time by batches is computed by finding out the time Batches have been waiting in
front of each individual process. Sum of these averages gives the Avg. Wait Time
c. Avg. Processing Time + Avg. Wait Time = Total Cycle Time.
This helps the business keep track of the bottleneck by tracing the value of the averages against the standard
the chosen function.
The main aim of the dashboard is to map the standard time allotted to each batch of a particular product against their
actual, which indicated the overall utilization % for each of the batches.
Here the main KPI’s being used are:
- BCF (Batch Charge Frequency, is the avg. frequency or time taken to start the next batch of the same product)
- BCT (Batch Changeover time, indicated the avg. time taken to completed a single batch).
Additionally Yield% is also computed at the batch level to make sure along with the pace the quality output
generated by the system is not hampered.
End user who have utilized the Dashboard
dashboard to streamline their processes by identifying the bottleneck of the system. Also these dashboards
are being displayed live in the plants for the workers to understand & quantify the work being done in the
plant over a period of time.
Presently, these dashboard are being used by Senior Management of the plant to make audits for various
aspects of the plant (such as Machinery, Man power, Processing Time etc.). As the historic data is
transparent & it is easier for them now to make valuable decisions based on the analysis done on the