Holistic Inventory Optimization

The following was first published by me as a LinkedIn article.

Tens of millions or hundreds of millions of dollars or more, in addition to stock prices or market valuation, are at stake depending on the company’s inventory sizes. C-level executives want answers to the following critical questions which are both sides of the same coin.

• How to spend a fixed budget on safety stocks so that the average demand satisfaction for the whole inventory is the highest?
• How to achieve the desired average demand satisfaction for the whole inventory with the lowest budget of total safety stocks?

To illustrate the point, consider the following simple inventory with 10 stock items. Factors determining the total value of safety stocks including lead time, unit price, demand standard deviation, and demand satisfaction (DS). Among these factors, DS for the individual stock items is the only one that we can adjust readily.

Figure 1. Common Approach - Setting the Same DS to Each Item

A common approach is to simply set the DS for all parts to a single number. Some managers adopt ABC analysis to divide stock items into several groups based on their values and assign the DS to each group. When we set DS to 91% for all items as shown in Figure 1, the average DS is 91% and the total value of safety stock is $129,985.

However, we can do much better. By setting the DS for each item more intelligently, we can keep the average DS of 91% and reduce the total safety stock by about 16% (Figure 2). Or by assigning a different set of DSs, we can maintain the same total safety stock budget and increase the average DS from 91% to 93% (Figure 3).

Figure 2. How to Keep the Average DS of 91% and Reduce the Total Safety Stock by About 16%?

Figure 3. How to Keep the Same Total Safety Stock Budget and Increase the Average DS From 91% to 93%?

Interested readers may do an exercise. Here is a downloadable spreadsheet file that embeds the formula to calculate DS. One simply fills in whatever DS she/he desires for each item. The average DS and total safety stock will be calculated for you.

If you can produce the results as shown in Figures 2 and 3 or better, congratulations! If not, I would invite you to attend my webinar "Holistic Safety Stock Optimization - Putting the Horse Before the Cart" on Thursday, August 12, 2021

Even if you are able to solve the 10-item problem by trial-and-error, in the real world many businesses have far more stock items than 10, I would still encourage you to attend the event.

Free Online Calculating Inventory Safety Stock Calculator

I made a youtube video at https://lnkd.in/d3hECWU to introduce a free online tool for calculating inventory safety stock. A spreadsheet file that implements the calculator is downloadable. Here is the link to the calculator. Enjoy!

Oracle Function Returns Two Values

There is a table in a schema that contains three columns, p, low and hi. In the table, p is the primary key. I want to develop a function to return low and hi based on an input variable p. First I create a type.

create or replace type t_low_hi as object ( low number, hi number);  

Then I create a function that finds low and hi based on p, constructs a type object and returns it.

create or replace function f_prob (p_p number)  
return t_low_hi is  
p_Low number;  
p_Hi number;  
Str_sql varchar2(2000);  
begin  
Str_sql := 'Select low, hi from t_lookup where p=:1';  
Execute immediate str_sql into p_low, p_hi using p_p;  
return t_low_hi(p_low, p_hi);  
end;  
/ 

I call the function and retrieve low and hi for p with a value of 0.99.

select x.v.low , x.v.hi from (select f_prob(0.99) v from dual) x; 

Updated Online Chinese Document Analytics Tool

We have updated our free online tool for analyzing Chinese documents: https://aistrike.us/text-analysis.html A user fills in a textbox with the content and click Submit button. The tool identifies words, displays a word cloud picture and calculates a sentiment index for each sentence. Enjoy!

Identifying words in a sentence is necessary. Chinese words in a sentence are next to each other without spaces separating them, e.g. Chinesewordsinasentencearenexttoeachotherwithoutspacesseparatingthem. And yes, the division of words could be ambiguous. For example, "结婚和尚未结婚的" could mean "married and unmarried" ("结婚 | 和 | 尚未结婚的") or "married, monk, unmarried" ("结婚 | 和尚 | 未结婚的") .

Inventory Optimization - Calculate Safety Stock

Safety stock provides a "cushion" in inventory to address the uncertainty in a customer's demand. It is important to maintain the "right" amount of safety stock. If it is too low, we may not be able to fulfill a customers' orders in a timely fashion. On the other hand, safety stock that is too high incurs significant financial and/or logistics burden to the business.

The calculation of safety stock is based on a number of factors include historical customer demands, product lead time and fill rate (a.k.a., demand satisfaction). The team at safetystockcalc.com builds a website and describe a popular approach to calculate safety stock using an example. On the website, you may also find two free tools that are useful, an online calculator and a downloadable spreadsheet with all the formulas. Enjoy!

Online Safety Stock Calculator Screenshot

Spreadsheet Safety Stock Calculator Screenshot

An Online Tool for Analyzing Chinese Text

We have developed a free online tool for analyzing Chinese document. The URL of the tool is located at here.
A user fills in the textbox with the content and click Submit button. The tool identifies words, calculates frequencies for those words and display a word cloud picture.

. Enjoy!

Real COVID-19 Death Rate

In my last post, we did research on COVID-19 death rate based on the ratio between the number of deaths and the number of confirmed cases. However, this method is inherently flawed. Some infected people did not show up at a hospital or a testing station to get tested. As a result, the death rate is exaggerated.

Blood antibody tests on randomly sampled residents in Santa Clara, California in early April shows that the number of people infected is 55 to 85 times more than confirmed cases (https://www.cnn.com/2020/04/17/health/santa-clara-coronavirus-infections-study/index.html). Thus, the real death rate for people who infected with coronavirus is between 0.1% and 0.17% which are similar to that of flu.

We use the following charts to illustrate two ways of calculating death rates.

COVID-19 Death Rate (Flawed) = Number of Deaths/ Number of Confirmed Cases

COVID-19 Death Rate (Real) = Number of Deaths/Number of Infected

As we can see, the chance of COVID-19 bullet hitting the bullseye, i.e., causing death, is much slimmer that appears based on confirmed cases alone.

Study on COVID-19 Annualized Death Rate

Probably this is the first time you see a chart like this. When people hear COVID-19 death rate for older people is high, they panic. We did research and published a paper on COVID-19 death rate( Study on COVID-19 Annualized Death Rate). Please notice the death rates for COVID-19 are "annualized". But we have to look at things in context. When COVID-19 death rate is annualized, it can be compared with other statistical data that are on annual basis. This is the key contribution of our research.

Please click the chart for sharper view.
Only when COVID-19 death rate is annualized, it can be compared with other statistical data that are on annual basis. This is the key contribution of our research. The following are the conclusions.

Conclusions

We propose a method to calculate the annualized death rate (ADR) related to COVID-19. Based on ADR related to COVID-19 and the 2018 death rate for the population of the United States, we gain the following insights:

Incremental annual death rates related to COVID-19 for age groups 45-54, 55-64, 65-74, 75-84 and 85+ are 0.4%, 1.2%, 2.2%, 3.1% and 6.0%， respectively.

Percentages of incremental annual death rates related to COVID-19 for age groups 45-54, 55-64, 65-74, 75-84 and 85+ are 100.9%, 131.8%, 124.4%, 69.8% and 44.5%, respectively.

If the herd immunity strategy is used in the United States, the incremental annual number of deaths related to COVID-19 for people equal to or older than 45 will be between 1.1 and 1.47 million.

The PDF file for the paper can be downloaded Study on COVID-19 Annualized Death Rate

Querying Database Using Command Line Client and Powershell

I have found Powershell is a powerful tool. When it combines with a command line tool, such as mysql, we can perform sophisticated tasks easily. The output of Powershell is an object instead of a text string. We can perform SQL-like operations such as selecting columns and filtering rows using where clause. Since I found out the power of Powershell last year, I have been using Powershell as my major tool to query databases including SQL Server, MySQl and Postgre. In this post, I demonstrate how to query MySQL using mysql client and Powershell.

First, we run the following command to set up our host, user name, password and port.

mysql_config_editor set --login-path=local --host=localhost 
                         --user=root --port=3306 --password

When we run the above command at a shell, it will prompt us to input password. It will create a file .mylogin.cnf that is not humanly readable under home directory (I am using Mac. For Windows, the file is under %APPDATA%\MySQL). The reason of doing this is to avoid an annoying security warning message if we supply the password in mysql command. If you don't mind the warning message, you can skip the step. Then I write a Powershell function to query MySQL database. I save the scripts as mysql_f.ps1 on my Mac.

function runsql {
param($s)
mysql --login-path=local -D dataexp -B -e "$s" | convertfrom-csv -delimiter `t
}

I issue the following command from the shell

prompt>. "./mysql_f.ps1"

Now the function is defined. I test the function.

prompt>runsql "select 1 as val"

val
---
1

It worked. The following query displays the table in the current database.

prompt>runsql "select TABLE_SCHEMA, table_name, create_time 
       from information_schema.tables where table_schema=database()"

TABLE_SCHEMA TABLE_NAME             CREATE_TIME
------------ ----------             -----------
dataexp      flyway_schema_history  2019-11-21 11:56:31
dataexp      mdl_sampled            2020-01-14 10:40:06
dataexp      mdl_set                2020-01-14 09:46:59
dataexp      sample_user_id         2020-01-14 09:58:34
dataexp      t_all_pk1              2019-12-05 11:53:43
dataexp      t_all_pk1_int          2019-12-05 22:14:54
dataexp      t_all_tab_cols         2019-11-21 15:39:00

From the Powershell output, we select only table_name an create_time by piping the output to "select" command of Powershell.

prompt>runsql "select TABLE_SCHEMA, table_name, create_time 
       from information_schema.tables 
  where table_schema=database()" | select table_name, create_time
TABLE_NAME             CREATE_TIME
----------             -----------
flyway_schema_history  2019-11-21 11:56:31
mdl_sampled            2020-01-14 10:40:06
mdl_set                2020-01-14 09:46:59
sample_user_id         2020-01-14 09:58:34
t_all_pk1              2019-12-05 11:53:43
t_all_pk1_int          2019-12-05 22:14:54
t_all_tab_cols         2019-11-21 15:39:00

The above operation is possible because in runsql function, we use convertfrom-csv to convert text output of mysql to Powershell object. Convertfrom-csv is really a magic function! Once we have an Powershell object, we can do all sorts of things.

Optimizing Inventory Level: Reduce the Inventory Value and Increase Customer Satisfaction Simultaneously

For a large part manufacture company in the transportation industry, maintaining the optimal inventory level in their warehouses is crucial to its bottom line. When too many parts are produced and stored, it costs the company excessive financial investment and previous warehouse spaces. On the other hand, if not enough parts in the warehouses, customers will become dissatisfied when orders may not get fulfilled in time. Thus, there are two conflicting goals to balance when planning the inventory: reducing inventory value and increasing customer satisfaction. The optimal strategy is to find the sweet spot of inventory level for each individual part that is most economical and maintaining high level of customer satisfaction at the same time. In a recent project that Dr. Jay Zhou has preformed, he is able to reduce the inventory level for his client company by $16 million and still maintain the same level of customer satisfaction. This work is highly received by the client.

In this project, Dr.Zhou takes advantage of machine learning models and reduces huge number of parts to a much smaller number of homogeneous groups. The "Demand Satisfaction" are calculated for these groups.

Onsite Interactive Training Session for Data Analysts/Data Scientists

Dr. Zhou is offering a brand new service: a half-day (3 hours) interactive training session to help a company's data analysts/data scientists improve their skills and productivity. The format of the interactive training session is as follows:

1. Dr. Zhou first gives a 1.5 hours presentation to describe successful cases of using data analytics to solve business problems, share best practices, and talk about challenges.

2. For the remaining 1.5 hours, the audience asks questions and is actively engaged in discussion with Dr. Zhou.  This the best part!  As one of the scientists said, "Dr. Zhou has helped me solve an issue that I had struggled with for years".

The training session is designed for all data analysts/data scientists. Dr. Zhou shares his battle-tested strategies and best practices that are useful for them regardless what specific analytics tools or programming languages they use.

On April 23, 2019, Dr. Zhou delivered the interactive training session to one of the top three property insurance company located in Boston. It was extremely well received. The picture attached shows that I am making the presentation (in addition to people in the room, there are more people joining the meeting through the phone.) 

The following are the testimonials from two persons who took the course.

"We have learnt a lot from Dr.Zhou'", Gang Xu, Director, Data Science at Lincoln Financial Group

"Dr. Zhou gave us a great overview of procedures of doing a solid predictive analysis and illustrated real life AI consulting business cases. Dr. Zhou is really experienced in the AI space and his presentation was very well received by data scientists from Lincoln Financial Group. I would highly recommend any data science group to have Dr. Zhou sharing his experiences. "-  Dr. Hao Zhou, Principal Analyst,  Data Science at Lincoln Financial Group

The following are testimonials about my previous talks and training activities.

"It was a fortune to have Jay come to our computer science department to share his experience in solving business problems with predictive analytics on February 28, 2017. What Jay had presented in his 3 talks, each lasting for 1 hour in different topics of data mining, was totally impressive and beyond our wildest expectation. Having built competition-winning predictive models for some of the biggest companies and produced hundreds of millions of dollars’ savings, Jay shared the secret of his success with students and faculty without reservation. His strong presentations were such an inspiration for our computer science students and faculty and his methodology was innovative and powerful , even for very seasoned data scientists among the audience. Jay, thank you so much for your hard work preparing and delivering these presentations! " - Dr. Wei Ding, Professor at University of Massachusetts Boston

"Jay is more than just a coder, he is a great trainer, and a good presenter of theoretical data mining concepts so that they can be understood by most. "-James Lukenbill, Director of IT Project Management, Optum

Bio of Dr.Jiang Zhou

Dr. Jiang Zhou has two decades of experience building predictive models across industries including telecommunication, banking, insurance, and smart city. These solutions have resulted in over $200 million savings for clients. Dr. Zhou has been involved in three real world competitions to build best predictive models, i.e., a customer credit risk model for a top three cell phone company, a bank card fraud detection model for a top 15 bank, and a direct sales model for a marketing company. Dr. Zhou's models have won all these competitions. He has founded/co-founded data analytics companies, including Business Data Miners, Smart Credit and AI Strike. Previously, he was a chief statistician at Lightbridge, a vice president at Citizens Bank and a consulting member of technical staff at Oracle. Dr. Zhou is the author of an award-wining blog on data analytics https://www.deep-data-mining.com/

The normal price for the training service is $6,500. If your company is interested in the service, please contact Dr. Zhou at jay.zhou@deep-data-mining.com 

From Hype to Reality – Powering the AI-Driven Future of Insurance at Insurance AI and Analytics USA - by Ira Sopic

With 2018 witnessing unprecedented advances in the investment and deployment of artificial intelligence within the insurance industry, Insurance Nexus is delighted to announce that the Insurance AI and Analytics USA Summit will return to Chicago for a sixth time in 2019, welcoming more than 450 senior attendees to the Renaissance Chicago Downtown Hotel, May 2-3.

Featuring an agenda designed to tackle the biggest challenges and opportunities in AI and advanced analytics, Insurance AI and Analytics USA is a must-attend for any analytics, underwriting, claims or marketing innovators seeking to both achieve efficient and seamless operations and deliver valuable and relevant products and experiences. 

“It’s impossible to open a magazine without seeing hype about analytics changing every aspect of your life,” says Will Dubyak, VP Analytics for Product Development & Innovation, USAA. “The Insurance AI & Analytics USA Summit is the optimal place to cut through the noise, hear the latest thinking from industry leaders in analytics, and compare best practices with your colleagues”

Across three in-depth tracks, more than 40 expert speakers from leading North American carriers will explore and discuss the latest strategies and approaches being deployed to maximize the impact of AI, machine learning and advanced analytics across the insurance value chain.

Featuring a whole session dedicated to case studies, the practical retelling of success stories will ensure attendees discover how, and where, technological innovations are having the biggest impacts on insurance and walk away with a holistic roadmap for success.

Confirmed speakers so far include Tilia Tanner, Global Head of Analytics, AIG, Eugene Wen, VP of Group Advanced Analytics, Manulife and Jerry Gupta, SVP, Digital Analyst Catalyst, SwissRe, as well as:

•        Thomas Sheffield, SVP and Head of Specialty Claims, QBE
•          Glenn Fung, Chief Research Scientist, AI and Machine Learning Research Director, American Family Insurance
•         Laurie Pierman, Vice President, Claim Operations, Amerisure Insurance
•          Michiko Kurahashi, Chief Marketing Officer, AXIS Capital

Attendees to Insurance AI and Analytics USA will also become part of a truly international insurance community, with over 25 hours of networking and interactive discussions aplenty. In addition, our ‘Open Design Workshops’ will see attendees attempt to live-solve industry challenges, giving insight into how peers and competitors alike approach a challenge, and how their own methods might be improved.

“At QBE, we’ve spent a great deal of time figuring out how we can strategically deploy artificial intelligence in practical use cases to drive immediate value for business,” states Ted Stuckey, Managing Director, QBE Ventures. “We’re excited to share some of our experience at the Insurance AI and Analytics Conference in Chicago on May 2-3!”

In short, however you are seeking to leverage AI, Insurance AI and Analytics USA is the event for you. Don’t miss this unparalleled opportunity. Join us in making 2019 the year AI insurance changes, forever.

Ira Sopic

About Dr. Zhou's Oracle SQL for Data Science Course

On January 31, 2017, I was invited by Prof. Wei Ding at the Department of Computer Science, University of Massachusetts Boston, and gave 3 talks about my data science projects across different industries. These talks are extremely well received. The following is what Prof. Ding says about my talks.

"It was a fortune to have Jay come to our computer science department to share his experience in solving business problems with predictive analytics on February 28, 2017. What Jay had presented in his 3 talks, each lasting for 1 hour in different topics of data mining, was totally impressive and beyond our wildest expectation. Having built competition-winning predictive models for some of the biggest companies and produced hundreds of millions of dollars’ savings, Jay shared the secret of his success with students and faculty without reservation. His strong presentations were such an inspiration for our computer science students and faculty and his methodology was innovative and powerful, even for very seasoned data scientists among the audience. Jay, thank you so much for your hard work preparing and delivering these presentations!" -Prof. Ding Wei, Department of Computer Science, University of Massachusetts Boston

The audience are particularly amazed by how I come up with solutions using Oracle SQL environment. To share my expertise, I create the online course Oracle SQL for Data Science to show how to perform common data science tasks using Oracle SQL and the benefits for doing that.

I let Charlie Berger,Senior Director of Product Management, Machine Learning, AI and Cognitive Analytics at Oracle know about my course and he told me "Your course is amazing."

Deep Learning World

The premier conference covering the

commercial deployment of deep learning

DeepLearning World is the premier conference covering the commercial deployment of deep learning. The event’s mission is to foster breakthroughs in the value-driven operationalization of established deep learning methods. DLW runs parallel to the established PredictiveAnalytics World for Industry 4.0 at the same venue. Combo passes are available.

How to turn Deep Tech into Broad Application

The hype is over: deep learning enters the “trough of disillusionment”. Companies are realizing that not every business problem requires the deep learning hammer. Of course, there are use cases that are best solved with artificial neural networks: image, speech and text recognition; anomaly detection and predictive maintenance on sensor data; complex data synthesis and sampling; reinforcement and sparse learning; and many more applications show the potential of artificial intelligence for real-world business scenarios. At the Deep Learning World conference data science experts present projects that went beyond experimentation and prototyping and showcase solutions that created economic value for the company. The case study sessions will focus on how it worked and what didn’t work while the deep dive sessions will explain topics such as RNN, CNN, LSTM, transfer learning and further in analytical and technical detail. Meet the European deep learning community in May in Munich and learn from well-known industry leaders!

Deep-data-mining.com blog readers receive 15% discount with code: DDMPAWDLW

Predictive Analytics World for Industry 4.0

6-7 May, 2019 – Munich

Predictive Analytics World is the leading vendor independent conference for applied machine learning for industry 4.0.

Business users, decision makers and experts in predictive analytics will meet on 6-7 May 2019 in Munich to discover and discuss the latest trends and technologies in machine & deep learning for the era of Internet of Things and artificial intelligence.

Putting Machine Intelligence into Production

Smart Factory, Smart Supply Chain, Smart Grid, Smart Transport: artificial intelligence promises an intelligent and fully automated future but reality is: most machines, most vehicles and most grids lack sensors and even where sensors do exist they might not be connected to the Internet of Things. Many companies invested in their infrastructure and are experimenting with prototypes e.g. for predictive maintenance, dynamic replenishment, route optimization and more, but even if they succeeded in delivering a proof of concept they face the challenge to deploy their predictive model into production and scale their analytics solution to company wide adoption. The issues are not merely analytical but a combination of technical, organisational, judicial and economic details. At the Predictive Analytics World for Industry 4.0 experienced data scientists and business decision makers from a wide variety of industries will meet for two days to demonstrate and to discuss dozens of real-world case studies from well-known industry leaders. In addition, predictive analytics experts will explore new methods and tools in special deep dive sessions in detail. Finally, the Predictive Analytics World is accompanied by the Deep Learning World conference, which focuses on the industry and business application of neural networks. Take the chance, learn from the experts and meet your industry peers in Munich in May!

 Hot topics on the 2019 Agenda:

• Predictive Maintenance & Logistics
• Anomaly Detection & Root Cause Analysis
• Fault Prediction & Failure Detection
• Risk Management & Prevention
• Route & Stock Optimization
• Industry & Supply Chain Analytics
• Image & Video Recognition
• Internet of Things & Smart Devices
• Stream Mining & Edge Analytics
• Machine ~, Ensemble ~ & Deep Learning
• Process Mining & Network Analyses
• Mining Open & Earth Observation Data
• Edge Analytics & Federated Learning

… and many more related topics

PredictiveAnalytics World 4.0 will be co-located with Deep Learning World, the premier conference covering the commercial deployment of deep learning in 2019. Deep-data-mining.com blog readers receive 15% discount with code: DDMPAWDLW

Analytics & AI in Travel North America

Analytics & AI in Travel North America launched by EyeForTravel will take place on March 14-15 at the Hilton Parc 55 Hotel, San Francisco, USA. There will be over 350 senior data, analytics, pricing, product development and digital marketing experts from the world’s leading travel companies, the event will explore the strategies for brands to address the biggest opportunity right now – how to conquer hyper-personalization.
Confirmed speakers include Hilton’s SVP of Analytics, Google’s head of AI global product partnerships, Expedia’s Head of Platform – Loyalty, Wyndham Hotel Group’s Vice President of Global Revenue Management Operations and Sales, Carlson Wagonlit’s Principal Data Scientist, and many more.

Attendees can expect to explore insights into the following:

• Harnessing AI and Data to Transform your Loyalty Strategy: Discover how weaving AI into your business, capturing preference data and delivering a truly personalized service will give you the edge in winning loyal customers from your competition

• Overcoming Pricing Peril with Personalized & Real-Time Revenue Generation Tactics: Make the shift to real-time pricing on an individual level, Nail down the use-cases of how to overcome this, forecast like a pro and optimize direct revenue

• Getting Up Close and Personal with the Customer and Capitalize on Every Channel: Use AI to fuel CRM and CS to bring customer data to life at every touchpoint, use the rich and famous on social to avoid brand erosion and secure market share.

• Immersing Yourself in an AI-driven Predictive Future to Seize New Profits: It all comes down to being predictive if you want to turn new profits. Deliver AI-led futures in your company for more efficient internal mechanics and travel customer-centricity

• Driving Real-Time, Hyper-Personalization to Move Your Profit Needle: Delve into new levels of granularity, become the Amazon of travel and deliver the perfect travel itinerary every time for unstoppable loyalty

• Seizing Voice, AR and VR Makes You Grab that Conversion: Be part of the lucky few that benefits from voice enabled search, drive direct bookings and use AR and VR to give your customer the confidence to convert

• Dominating Direct Bookings Through A Mastery of Mobile: Create an AI-enabled mobile product that drives direct bookings, focus on UI and UX that screams out loyalty and bolster your bottom line

• Outclassing your Competition with Total RM and Surge Ancillary Sales: Build state-of-the-art infrastructure that supports ancillary revenue and squeeze every ounce of profit from all revenue streams

Please check out the following icon for more information.

Could Not Connect to Amazon RDS Oracle Database From Car Dealer WiFi

I am trying to connect to my Oracle database on Amazon RDS at a car dealer while my car is in service. My laptop is connecting to the public WiFi. When I try to connect to the Oracle server, I got "Error Message = IO Error: The Network Adapter could not establish the connection".

I realized the issue is caused by the new ip address not included Amazon Security Group inbound rules. I find my ip address. Then I log onto Amazon AWS console and find the security group associated with the DB instance. After I add a inbound rule 64.188.5.xxx/32, I am able to connect to the DB immediately.

Statistically Manufactured Personal Data

To avoid the trouble of dealing with personal data when we test our analytics processes, I have created mock personal data that closely reflect American population from statistical point of view. The largest data set has 1 million records with variables including first name, last name, sex, date of birth, social security number, address, phone number and email. The values of these variables are produced to be as realistic as possible to real American population. They represents about 0.33% of population in the United States.

These observations about the data 1 million mock personal data records are very close to the real statistics of the population in USA.

1.The top 4 states that have the most people are: California(138223 persons, %13.82), Texas(99217 persons, %9.92), Florida(69640 persons, %6.96) and New York(49979 persons, %5). These are close to the real distribution of the population in USA.
2. The female are 51% and the male are 49%.
3. Top 3 last names are Smith(10800 persons, %1.08), Williams(8000 persons, %.8) and Jones(6900 persons, %.69).
4. Top 3 female first names are Ava(4707 persons, %.93), Olivia(4508 persons, %.89) and Isabella(4311 persons, %.85) and top 3 male first names are Noah(5075 persons, %1.03), Elijah(4736 persons, %.96) and Liam(4434 persons, %.9).
5. The following table shows distributions of persons by age for both sexes. Women live longer than men.

                        Female           Male
Age Group        #        %       #  % 
   .Under 5 years 34603 6.81% 35656 7.25%
   .5 to 9 years 34707 6.83% 34010 6.92%
   .10 to 14 years 30192 5.94% 33013 6.72%
   .15 to 19 years 34361 6.76% 32689 6.65%
   .20 to 24 years 32512 6.39% 36647 7.45%
   .25 to 29 years 35626 7.01% 37278 7.58%
   .30 to 34 years 34344 6.76% 31977 6.50%
   .35 to 39 years 33325 6.55% 31927 6.49%
   .40 to 44 years 33332 6.56% 34456 7.01%
   .45 to 49 years 35070 6.90% 35443 7.21%
   .50 to 54 years 37321 7.34% 34876 7.09%
   .55 to 59 years 31623 6.22% 31315 6.37%
   .60 to 64 years 28801 5.67% 24218 4.93%
   .65 to 69 years 20999 4.13% 19881 4.04%
   .70 to 74 years 16617 3.27% 14065 2.86%
   .75 to 79 years 13520 2.66% 10272 2.09%
   .80 to 84 years 10693 2.10% 7983 1.62%
   .85 years and over 10754 2.12% 5894 1.20%

You may download a small file with 100 records free here. Free Download. Files with 5k, 50K, 250K and 1 million records are available for purchase at https://www.datamanufacturer.com.

File Name	Description	Price	Buy
dm_mock_person_100.csv	100 mock personal data records. CSV format.	free	Free Download
dm_mock_person_5k.csv	5K mock personal data records. About 0.7M bytes. CSV format.	$2.95
dm_mock_person_50k.csv	50K mock personal data records. About 7M bytes. CSV format.	$7.95
dm_mock_person_250k.csv	250K mock personal data records. About 35M bytes. CSV format.	$9.95
dm_mock_person_1m.csv	1 million mock personal data records. About 140M bytes. CSV format.	$39.95

Generate Random String in Oracle

The following query generate random email address.

SQL> select dbms_random.string('l', 8)||'@'||dbms_random.string('l', 7)||'.com' 
        email from dual;

EMAIL
------------------------------------------------------------------------------------
irslsxrf@wltikyv.com

The first parameter 'l' means string will be created in lower cases.

Find the Most Frequent Values

To find the most frequent values, we can use STATS_MODE function. The following query shows areacode in state Missouri.

SQL> select areacode from T_PHONE_AREA where state='Missouri' order by 1;
  AREACODE
----------
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       417
       417
       573
       573
       573
       636
       636
       636
       636
       636
       636
       660
       816
       816
       816
       816
       816
       816
       816
       816
       816
       816

34 rows selected.

In the following query, stats_mode(areacode) returns the areacode 314 that is the most frequent value.

SQL> select stats_mode(areacode) from T_PHONE_AREA where state='Missouri';

STATS_MODE(AREACODE)
--------------------
                 314