Background: The excel file "ALY 6030 Final Project Data Set. xlsx" contains sample pharmacy claims for made-up members of an insurance company that pays for pharmacy via a third party known as a Pharmacy Benefit Manager or PBM. The file also contains a data dictionary referring to the variables in the data along with their format for your reference.
The PBM has given you these sample records as a starting point so that you as the Developer can set up a test database and pre-program some common SQL query reports that will be expected from the reporting analysts and business users working at your company, once the full claims detail is made available in production in a few months from the PBM. Your work on the test warehouse will help ensure a smooth and successful rollout once the data warehouse goes live in production.
Part 1) Normalization
Your task is to eventually load this sample data into your test database and create a snowflake schema (as opposed to a star schema).
However before you do, you notice that the raw data does not meet 3NF. You make a note to let the PBM know they need to change their formats for a relational database. You also see some of your sample members appear more than once because they filled a different drug which further complicates the format.
For now you decide to fix this yourself directly in excel.
Tasks
Convert the raw data into a set of relational tables that meet 3NF standards. Feel free to do this directly in excel.
Each table you create should be either a fact table or a dimension table
You do not need to create a separate date dimension joined on date keys. Just include the dates as they appear in the data.
Save each table as its own .csv file. Indicate in your .csv filename whether your table is a fact table or a dimension table (e. g. name it dim_tablename. csv, fact_tablename. csv, etc.).
At this time you do not need to create or designate primary or foreign keys, that will be done in Part 2 using MySQL
Answer the following questions:
For each fact variable in your fact table, what type of fact is it? Additive, semi-additive, or non-additive?
In your fact table, describe the grain in one sentence. What does each fact row represent?
Show transcribed image text