Company Description: One of the prestigious Pharmaceutical Company with a global presence. The organization is a Pharmaceutical product manufacturing Company, so the majority of the vertical in the organization is in line with the manufacturing process. All various department such as Finance, Operation, Sales etc. are all majorly associated with the streamlining the process of manufacturing which is the heart of the organization business.
Domain: Pharmaceutical
Current Data Landscape (Source Database): Oracle, MySQL and SAP Net Weaver
Reporting Database: SAP BO
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

Description: The traditional BI tool being used for the reporting was SAP BO and the majority of the dashboards were built on top of it. As mentioned, Company being one of the mature BI clients & they already had a robust
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.
Reports Description: As described in the model, the Data has been stored in multiple databases (Oracle, Excel, MySQL, and SAP Netweaver) and mapped with SAP BO where team extracts the data, clean data and prepare the report. Business Analyst combines the data and share with the Business User. For SAP Data sources they are using SAP BO to build the report and for other databases such as MySQL, JavaScript was used to build reports on MIS.
Problem Statement:
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:

Reports Description: Company is already having a mature Business Intelligence solution in place for a couple of years. Majorly utilizing the capabilities of Tableau & Micro Strategy in terms of BI Software. And also have an
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.

Solution: Tableau is being used to implement solution for majority of the use cases. The major problem being served by Tableau was the ease of implementing solutions on top of Tableau as well as reducing the turnaround time for each project.
Ease of report development and automation was the major use case that Tableau handled efficiently. This significantly
reduced the turnaround time of the projects.
With Tableau Implemented Solution Business Analyst team able to:
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.
Additionally:

  • 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

In the Company, the Tableau Consultant have been assigned the job of handling the manufacturing vertical. Some of
the Dashboards have been successfully completed are:
1. OEE Plant Machinery Dashboard

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.
On clicking/selecting an equipment, the end user is taken to a detailed dashboard of that particular equipment in the
selected date range.
Further selecting a particular Day from the Equipment Availability Trend Shows the day level data for the chosen
equipment.
2. Lead Time Analysis
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.
The KPI’s as mentioned above are majorly based upon the functional processes involved in manufacturing.
Such as

a. Avg. Time taken by Batches in each process individually. Sum of these averages gives us the Avg. Processing
Time.

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
values.

On Selecting a particular Function block such as Sifting or Coating etc. the user is taken to a detailed dashboard for
the chosen function.
3. CTO Capacity Utilization
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.
KPI’s and metrics, the end user are interested in:

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

The majority of the end user of the dashboard are Plant Heads & their subordinated, which make use of the
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
dashboards.

RESOURCES:

Tableau Desktop Trial Version:
https://www.tableau.com/partner-trial?id (14 days trial version)
Goldstone Technologies Limited:
https://www.goldstonetech.com/
Tableau:
https://www.tableau.com/

Banking operating cost across regions and branches

Company Description: One of the leading Universal Bank with its operations driven by a cutting edge core Banking IT platform. The Bank offers personalized banking and financial solutions to its clients in the retail and corporate banking arena through its large...

Unifying data across board for a global pharmaceutical company

Company Description: One of the prestigious Pharmaceutical Company with a global presence. The organization is a Pharmaceutical product manufacturing Company, so the majority of the vertical in the organization is in line with the manufacturing process. All various...

Empowering a retail organisation with advanced analytics

Company Description: One of the Leading retail corporation that operates a chain of hypermarkets, discount department stores and grocery stores.Domain: Supply chain ManagementCCurrent Data Landscape: SQL Server, MySQLReporting Database: Excel SheetsTraditional...