<jupyter_start><jupyter_text>Exercise 02 -  OLAP Cubes - Roll Up and Drill Down All the databases table in this demo are based on public database samples and transformations- `Sakila` is a sample database created by `MySql` [Link](https://dev.mysql.com/doc/sakila/en/sakila-structure.html)- The postgresql version of it is called `Pagila` [Link](https://github.com/devrimgunduz/pagila)- The facts and dimension tables design is based on O'Reilly's public dimensional modelling tutorial schema [Link](http://archive.oreilly.com/oreillyschool/courses/dba3/index.html)Start by connecting to the database by running the cells below. If you are coming back to this exercise, then uncomment and run the first cell to recreate the database. If you recently completed the slicing and dicing exercise, then skip to the second cell.<jupyter_code># !PGPASSWORD=student createdb -h 127.0.0.1 -U student pagila_star
# !PGPASSWORD=student psql -q -h 127.0.0.1 -U student -d pagila_star -f Data/pagila-star.sql<jupyter_output><empty_output><jupyter_text>Connect to the local database where Pagila is loaded<jupyter_code>import sql
%load_ext sql

DB_ENDPOINT = "127.0.0.1"
DB = 'pagila_star'
DB_USER = 'student'
DB_PASSWORD = '<PASSWORD>'
DB_PORT = '5432'

# postgresql://username:password@host:port/database
conn_string = "postgresql://{}:{}@{}:{}/{}" \
                        .format(DB_USER, DB_PASSWORD, DB_ENDPOINT, DB_PORT, DB)

print(conn_string)
%sql $conn_string<jupyter_output>postgresql://student:student@127.0.0.1:5432/pagila_star<jupyter_text>Star Schema   Roll-up- Stepping up the level of aggregation to a large grouping- e.g.`city` is summed as `country`TODO: Write a query that calculates revenue (sales_amount) by day, rating, and country. Sort the data by revenue in descending order, and limit the data to the top 20 results. The first few rows of your output should match the table below.<jupyter_code>%%time
%%sql
SELECT t.day, m.rating, s.country, sum(f.sales_amount) as revenue
FROM factsales f 
JOIN dimdate t ON (f.date_key = t.date_key)
JOIN dimmovie m ON (f.movie_key = m.movie_key)
JOIN dimstore s ON (f.store_key = s.store_key)
GROUP BY t.day, m.rating, s.country
ORDER BY  revenue desc
LIMIT 20<jupyter_output>* postgresql://student:***@127.0.0.1:5432/pagila_star
20 rows affected.
CPU times: user 5.28 ms, sys: 133 µs, total: 5.41 ms
Wall time: 27 ms<jupyter_text>day        rating        country        revenue                30        G        China        169.67                30        PG        India        156.67                30        NC-17        India        153.64                30        PG-13        China        146.67                30        R        China        145.66      Drill-down- Breaking up one of the dimensions to a lower level.- e.g.`city` is broken up into  `districts`TODO: Write a query that calculates revenue (sales_amount) by day, rating, and district. Sort the data by revenue in descending order, and limit the data to the top 20 results. The first few rows of your output should match the table below.<jupyter_code>%%time
%%sql
SELECT t.day, m.rating, s.district, sum(f.sales_amount) as revenue
FROM factsales f 
JOIN dimdate t ON (f.date_key = t.date_key)
JOIN dimmovie m ON (f.movie_key = m.movie_key)
JOIN dimstore s ON (f.store_key = s.store_key)
GROUP BY t.day, m.rating,  s.district
ORDER BY  revenue desc
LIMIT 20<jupyter_output>* postgresql://student:***@127.0.0.1:5432/pagila_star
20 rows affected.
CPU times: user 5.12 ms, sys: 0 ns, total: 5.12 ms
Wall time: 27 ms
{'fraction_non_alphanumeric': 0.0945372204924993, 'fraction_numerical': 0.035663741862439854, 'mean_word_length': 3.411985018726592, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 6, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '106303', 'license': 'Apache-2.0', 'n_tokens_mistral': 1228, 'n_tokens_neox': 1131, 'n_words': 409, 'path': '02-cloud-datawarehouses/01-intro-datawarehouses/L1 E2 - 2 - Roll up and Drill Down.ipynb', 'repo_name': 'yamanzi/Udacity-Data-Engineer-nanodegree', 'size': '3530'}