Assignment$-2$ Data Warehouse. Answer the questions and show work using diagrams.

Suppose that a data warehouse for a Hospital chain consists of the five dimensions' date,

Hospital branch, visit type, services, patient, and the two measures time of visit and billing

amount, where billing amount is the amount that a patient pays for a particular visit starting

on a given admission date. Patients may be children, adults or elderly. Hospitals are in US and

the visit type include Emergency, Consulting or Admission

a$)$ Draw a snowflake schema diagram for the data warehouse.

b$)$ Starting with the base cuboid, what specific OLAP operations should one perform in order

to list the total billing amount of children patients in the Charlotte branch in $2015$ for all

Emergency type of visits.

c$)$ If each dimension has four levels $($excluding all$),$ how many cuboids will this contain

$($including the base and apex cuboids$)?$

Suppose that a data warehouse contains $20$ dimensions, each with about five levels

of granularity.

a$)$ Users are mainly interested in four particular dimensions, each having three frequently

accessed levels for rolling up and drilling down. How would you design a data cube

structure to efficiently support this preference?

b$)$ At times, a user may want to drill through the cube, down to the raw data for one or two

particular dimensions. How would you support this feature?