Datawarehouse design for Pharmaceutical sales and Health Indicators affecting obesity, heart diseases and cancer
PHARMACEUTICAL SALES DATAWAREHOUSE DESIGN AND ANALYSIS
Problem Setting : Pharmacy is one of the main components of a thriving human civilization and is extremely important to the standard of living and defines the health and sanitation of the country or city. Hence it is extremely important that medicines are in the right hands and is distributed extremely efficiently across a vast range of networks. A database for a Pharmacy is an extremely efficient and an important tool in maintaining is distribution network. In this particular problem, we will see how to create a database and a multidimensional schema for a company called Glenn Pharma responsible for supplying medicines across Massachussetts. Problem Definition : This project intends to build the database design of a business model similar to a Pharmaceutical database. Keeping extensibility and scalability in mind, we will build a module that can be converted to a microservice architecture or transferred to a data warehouse to perform data analysis for the prediction of future trends in technologies. The transformed data is loaded in a data warehouse where analysis is done. A few of the analysis topics are mentioned below:
- What drug generates the maximum revenue
- Who are the top performing salesmen?
- Who are the biggest customers?
- What is the monthly sales analysis.
Data : The data is collected from the database of a Pharmaceutical company called Glenn Pharma and it is found on dataworld.org. It contains the details about the meetings held between salesmen and customers, the salesmen, the sutomers and the products which the company is currently dealing with.
Data Description : The meeting table contains the record of 2585 meetings held between customers and the sales representatives and also gives and information of the amount of sales intended for that meeting and whether the sale was converted or not. The product table contains a list of 30 products with each sales rep responsible for one product respectively. The customer table contains a list of all the customers and their contact information which will be useful to sales reps. We also have the inventory table which gives a list of all the products and their quantities which are stored in the data warehouse.
END GOAL : Our end goal in this project is to create a multidimensional model of the pharmaceutical database which will be useful for further analysis.
HEALTH INDICATORS AFFECTING OBESITY, HEART DISEASES AND CANCER ANALYSIS
PROBLEM SETTING Obesity increases the risk of several debilitating, and deadly diseases, including diabetes, heart disease, and some cancers. It does this through a variety of pathways, some as straightforward as the mechanical stress of carrying extra pounds and some involving complex changes in hormones and metabolism. There are many reasons why some people have difficulty losing weight. Usually, obesity results from inherited, physiological and environmental factors, combined with diet, physical activity and exercise choices. In this project, Community Health Status Indicators (CHSI) to combat obesity, heart disease, and cancer are major components of the Community Health Data Initiative. The selected dataset provides key health indicators for local communities and encourages dialogue about actions that can be taken to improve community health (e.g., obesity, heart disease, cancer). The health indicators are an important discussion to empower health consciousness and spread awareness about the ill effects of obesity and factors that cause the same.
PROBLEM DEFINITION Community Health Status Indicators (CHSI) to combat obesity, heart disease, and cancer are major components of the Community Health Data Initiative. The dataset provides key health indicators for local communities and encourages dialogue about actions that can be taken to improve community health (e.g., obesity, heart disease, cancer). The CHSI report and dataset was designed not only for public health professionals but also for members of the community who are interested in the health of their community. The CHSI report contains over 200 measures for each of the 3,141 United States counties. Although CHSI presents indicators like deaths due to heart disease and cancer, it is imperative to understand that behavioral factors such as obesity, tobacco use, diet, physical activity, alcohol and drug use, sexual behavior and others substantially contribute to these deaths. Our team is challenged to undertake research or analysis on this data and submit the findings. This project's purpose is to use data engineering and warehousing concepts to build data pipelines that receive data from a source, transform it, and store it in the best possible format for data visualization and to derive actionable and scalable insights from the data. We are trying to answer the following questions: • What are the major factors leading to obesity, heart diseases and cancer? • What is the reason behind largest number of deaths? • Top few factors of health illness in people? • What are some ways to improve mortality rate due to these health conditions?
DATA SOURCES Community Health Status Indicators (CHSI) to combat obesity, heart disease, and cancer are major components of the Community Health Data Initiative. This dataset provides key health indicators for local communities and encourages dialogue about actions that can be taken to improve community health (e.g., obesity, heart disease, cancer). The CHSI report and dataset was designed not only for public health professionals but also for members of the community who are interested in the health of their community. The CHSI report contains over 200 measures for each of the 3,141 United States counties. Although CHSI presents indicators like deaths due to heart disease and cancer, it is imperative to understand that behavioral factors such as obesity, tobacco use, diet, physical activity, alcohol and drug use, sexual behavior and others substantially contribute to these deaths.
CitationSource: https://catalog.data.gov/dataset/community-health-status-indicators-chsi-tocombat-obesity-heart-disease-and-cancer