Alura Challenge BI

Alura has a challenge modality, the Alura Challenge, which encourages the development of skills in case studies similar to those existing in the job market. In its second edition, the Business Intelligence challenge is carried out in four weeks and three projects with different levels of complexity are created. For the resolution, BI concepts and techniques are applied to develop dashboards to assist the decision making of fictitious companies.

• Week 1: Alura Films needs to analyze IMDb data for a new production
• Week 2: Alura Foods wants to analyze Indian restaurant market
• Week 3: Alura Skimo needs to analyze its sales metrics.

🎬 Week 1: Alura Films

In this challenge, Alura Films wants to make a new production. There was analyzed data from Genre, Gross, Stars and Directors and Rating (IMDb and Metacritic).

Take a look at this dashboard created in Power BI: https://bit.ly/3JJcfLP

🔶 Two .csv files were used to import the data. The entire ETL process was done in Power Query using the main functions such as: renaming columns, splitting columns by delimiter, changing column types, merging columns and replacing values. Auxiliary queries were created to process Star and Gender information.

🟢 Here we can take a look at the top rated movie and appearances by the star and director. Also, gross revenue information (total and average per year) and most common genres. At the top, there are buttons for navigating through the dashboard pages.

🟢🟢 The second page shows more metrics about the movies, such as Top Voted and Top Rated on IMDb, releases by year and gross revenue by genre. There are also some filters to refine the selection.

🟢🟢🟢 On this page there is information about the Stars and the Movies Directors. There is search and filter tools to choose a Star and/or Director by name or in a list.

🟢🟢🟢🟢 The last page allows to make some combinations of filters and searches. Results are displayed on cards with important information and a table.

🔶 Each visual about movies shows a custom tooltip with information from the movie. The IMDb and Metacritic rating are dynamic and can be displayed in three colors according to their value 🟩 🟧 🟥. The cover images of the films were inserted with the visual "Image Grid".

🍽️ Week 2: Alura Foods

In this week, Alura Foods is looking for an opportunity to expand its restaurant business to India. Location and operation data of the restaurants were analyzed, such as country, city, coordinates, type of cuisine, price, rating, delivery and table booking.

The Power BI dashboard is available at: https://bit.ly/AlFoods

🔷 The database was divided into five .json files. All files were connected by folder and the exchange rate is collected by an API. The ETL process was done in Power Query by expanding columns, changing column types, replacing values and deleting useless data. Two auxiliary queries were created to organize the data and work with the categories of cuisine.

🟡 At the beginning, you can see the general numbers of restaurants in India. Quantity of restaurants, restaurants by city, average rating and cost (in four currencies), map with location, percentage with delivery and type of cuisine are displayed on this screen. On the left side there are buttons for navigating the dashboard pages.

🟡🟡 In this part it's possible to see more information about the restaurants. On the right side are some filters to refine the parameters and improve the analysis. The results are shown by the best restaurants and cuisines. There are two donut charts to represent the rating and price range distribution.

🟡🟡🟡 The last page allows to make some combinations of filters and searches. Results are displayed on cards with important information and a table.

🔷 Each restaurant visual has a custom tooltip with more information about the place. The visual "Image Grid" was used to insert the illustrations of the restaurants.

🍦 Week 3: Alura Skimo

In the third week, Alura Skimo needs a dashboard to track all necessary sales metrics. You can see the dashboard here.

🟥 The database was available in DUMP files and restored using MySQL. The Power BI connection was made by a MySQL localhost.

🟥 The entire ETL process was done in Power Query using the main functions like changing column types, replacing values, renaming columns and created a date table. For this dashboard, some measures were created used in the metrics and analysis visuals, such as total revenue, higher profit, total sales commission, difference between scenarios and production cost. The measures related information between tables (RELATED function). In addition, parameters were created to analyze some interactive scenarios. All measurements and parameters were organized in folders. Finally, the dashboard was created in Portuguese, but it is easy to translate in Google Chrome.

🟥 The panel has a menu bar on the left side. There are five buttons to navigate between pages and two buttons to link to the dashboard creator's GitHub and Linkedin pages. All buttons were made with bookmarks. The panel design was created in MS Power Point.

🟤 On the first page, you can see general information such as the best selling product, the most profitable seller, an interactive donut visual sorted by product category, total revenue area chart, and a year slicer (shown as buttons).

🟤🔴 By clicking the second button on the menu bar, located on the left side of the page, you can go to the products page. There is an area chart with total sales by category, a stacked bar chart with the best sales of flavors, three single cards and a multi-line card with the best selling products. For more information about the products, there is a table below the page. Also, at the top it is possible to choose some filters to perform analysis.

🟤🔴🟣 On the sales page two top 3 rankings are shown, the highest revenue seller and the highest revenue product. There is an area chart that shows the percentage difference, per month, in revenue between the current year and the previous year of the analyzed period. This graph also has a trend line of the revenue difference. Some slicers on the top could refine the analysis.

🟤🔴🟣🔴 On the fourth page you can see seller information, total revenue by month, revenue difference between current and last year and sales by state. Filters are on the left side, next to the menu bar. As on the other pages, all graphics are interactive.

🟤🔴🟣🔴🟤 The last page contains scenario analysis. The base scenario and three pre-defined scenarios were created and their difference is shown on multi-row cards. There is a line chart with a trend line showing the profit in the chosen scenario. Furthermore, it is possible to build a new scenario by changing the three metrics used: sales, selling price and production cost. Finally, there is a date slicer to customize the analysis period.

Thanks for your attention!

Back to the top.