Yelp Data:

https://www.kaggle.com/yelp-dataset/yelp-dataset?select=yelp_academic_dataset_review.json

Restaurant Inspection:

https://data.world/lasvegasnevada/restaurant-inspections/workspace/file?filename=restaurant-inspections-1.csv

Violation Code (with explanation):

https://opendataportal-lasvegas.opendata.arcgis.com/datasets/restaurant-inspection-violation-codes/data?page=6

 

January 2017 – November 2017 Data 

Purpose: To identify if 2017 restaurant inspections in the Nevada Area have any affect on their Yelp ratings.

KPIs:

• Name of restaurants with the highest percentage change in monthly average review stars before and after the inspection.
• Name of restaurants that had the largest percentage in monthly average “useful” review stars before and after the inspection.
  
• What is the average review (per restaurant) and frequency of inspection (monthly).

Grain:

The review table has a grain of: day, business, review, useful

The inspection table has a grain of: day, grade, inspection result

Dimensions:

Date (date, month), Location (address, city, state, postal code), Business (business_id, name, category), Review Text (Junk Dimension), Inspection Violations (Violation code, explanation)  

Facts:

• review fact table (transaction fact table) (xx  located at xx received a xx review star on xx date.)
• Inspection_grade fact table (Factless fact table) (xx located at xx received an inspection grade of xx with the inspection result of xx and xx violations codes on xx date.)

Dimensional Model Diagram:

https://drive.google.com/file/d/1QK4d7ya1AR7kcnf3pP2N1zVmEfmmBIS1/view?usp=sharing

Image of Dimensional Model Diagram:

https://drive.google.com/file/d/1cs_c-j0j-lXrURQsNnSKEgQTCD7FhFK-/view?usp=sharing