SELECT distinct(b.rankyear) FROM magazine_details a left outer join ranking b on b.mag_id=a.sno where b.app_status != 0 and a.cat_id=58 and b.rnkid is not null group by a.year order by a.sno desc limit 1
SELECT distinct(a.year), count(*) as count_yr, a.image_name,a.url,a.label1,a.label2,a.date, b.rnkid,b.rnktitle,b.rankpicname,b.weburl,a.cat_id,b.app_status,b.mag_id,b.master_stat FROM magazine_details a left outer join ranking b on b.mag_id=a.sno where b.app_status != 0 and a.cat_id=58 and b.rnkid is not null and b.rankyear = '2019' group by a.year order by a.sno desc
Effective Usage Of Analytics And Business Intelligence
Subho Nath, Assistant Director IT, CSM Bakery Products
In Bakery manufacturing service, we strive in operational excellence. To gain competitive advantage over our competitors, we are engaged in continuously improving business decisions and operations. We bet on short and long term strategy with a pragmatic approach. However, even though there is a significant amount of data in our company’s various platforms, often critical decisions were made from rudimentary spreadsheet.
CSM has embraced business intelligence and analytics in functional areas like finance, sales, procurement, supply chain and operation. We have adopted a balanced approach with in house, off-the-shelf products and hosted solutions to meet business expectations. In our multibillion dollar company, we spend significant amount each year in non-product related category across the globe. We have accounts payable transactions in multiple ERP platforms. We have compiled this information for past few years in a single consistent platform which we then feed to a third party consortium to analyze our spend behavior. The third party consortium has repository of expenses from companies in manufacturing vertical. It also has information of preferred vendors from whom market leaders are buying similar items. By comparing CSM information with industry trends, the Procurement team is able to make better decisions about global and regional purchases. It provides the Procurement team with global purchasing visibility to help prioritize their effort towards immediate ROI. Furthermore, it allows us to buy parts at a lower cost as this third party has special agreements with vendors for bulk consumption. Instead of building our global spending analytics in-house, we have opted for an off-the-shelf hosted solution to realize business value quickly.
Contract management is crucial in manufacturing industry, as our product cost varies with commodity price. We contract flour, wheat, milk, egg and many commodities with six months to 24+ months out. Often time we extend same contract agreement from one term to another depending on our demand and volatility in commodity prices. The Finance team needs visibility of future commodity costs, so that they can quote to the customer for finished products
We have built sophisticated analytics around contract management using the Microsoft BI stack. We use data from our ERP systems and industry trends reports. We have developed various analytic reports and OLAP cubes in MS SSRS and SSAS; which allow our Procurement and Finance teams to make better business decisions.
Operational excellence depends upon how efficiently we manage our supply chain finance. We have built finance analytics using MS BI Stack where the ERP system feeds information to the MS SQL Server. We also feed information from other user driven applications which are built in .Net architecture. Some of the MS SSAS cubes are built every 30 minutes. Finance team uses this BI tool to analyze monthly expenses, manage actual/flex budget, perform head count analysis and overhead planning. Finance also uses the BI tool to track how a plant initiative is performing against the promised ROI in a specific time period. This information is available to the corporate OPEX team who can then slice and dice the data on various dimensions like safety, off quality etc. to come up with recommendation for process improvement.
We have made significant improvements in plant operational performance by capturing real time data from each production line. We feed production line heartbeat to a third party hosted solution. We capture production scheduling information from ERP and send to the same third party. This tool has in built analytics which allows business to analyze how efficiently each line is performing as per the schedule in real time. If a line is not operating against our benchmark, business can address this in real time at the line level with specific plant personnel.
We have built Sales Analysis tool in Microsoft BI stack to help CSM Sales and Finance teams. ERP information is fed to SQL Server on a daily basis. Various OLAP cubes are built out of this information which is used by corporate business users and sales representative in the field. Corporate Finance and Sales team have the capability to analyze sales data in customer/ item/ region with PTD, YTD, Year-to-Year and other time dimensions. The tool also provides granular information about rebate, brokerage commission and many other attributes. The tool is also used to build pricing models. Sales representative can get sales trend of their areas of responsibilities (territory/ customer) and build their action items. We have built role based authority, so that a sales rep can only see his/her area of responsibility.
Having a correct Computerized Maintenance Management Systems (CMMS) across all plants directly impacts operational excellence. We have implemented third party CMMS solution in a plant which provides various functionalities including preventative maintenance, inventory management, expiration of important documents like vendor insurance, warranty and the like. Analytics around them helps plant management to take action like scheduling maintenance before it affects normal production run, reaching out to vendor to procure renewed insurance and taking proactive action on specific inventory before it runs out. Savings in the plant where we have implemented this system has been significant compared to a plant without the benefit of this CMMS system. We are in a process of implanting the same CMMS tool across all plants in the US. We are planning to implement the same systems across the globe depending on the plant size.
In summary, our approach of blended development between in-house work and third party hosted solution helps our business community realize the benefits of analytics quickly. It is always a challenge when deciding what will be the right approach when we address a new business issue. We use internal ROI matrices to make our decisions. We are always actively looking into addressing more functional areas which can benefit from the power of analytics.