AudioStream, a digital music streaming start-up has recently experienced rapid growth in its business. However, with this growth they have encountered significant challenges in scaling their systems. In particular, they have struggled to gather operational intelligence about their business processes due to the unavailability or lack of easily accessible information. This has meant that they have not been able to pro-actively react to operational inefficiencies or take advantage of opportunities effectively. In their most recent corporate review, senior management have decided that they need to revamp their data analytics infrastructure with a key focus on redesigning their data platforms. AudioStream's CTO has contracted you as an expert in the field to lead this initiative. AudioStream is a music streaming platform, it focuses on three main groups: Content Creators, Content Consumers, and Advertisers. Their platform provides music streaming services to both independent and music label-affiliated artists across a variety of genres. AudioStream initially established itself by supporting up-and-coming artists and providing them with a platform to share their music. Single artists or bands can upload their music to the platform, either as singles or entire albums. Consumers can access AudioStream's services either through a premium subscription or by streaming music for free with ads. Users can create their own playlists, follow their favourite artists, and upvote their favourite songs. By adding songs, artists, albums, or playlists to their library, users can be served with content which complements their listening preferences. AudioStream is looking to inform its business strategy for the three main groups, Content Creators, Content Consumers and Advertisers by answering the following business questions; What is value in terms of Content Creators, Content Consumers and Advertisers? Who are the high valued Content Creators, Content Consumers and Advertisers? What are the customer segments that can be seen and what genre of music are trending among these segments, and how that information be leveraged promote similar music and artists? What is the average listening time for our subscribers, and how can we improve engagement to increase listening times? What are the most popular music genres in different regions and across demographics, and how can we adjust our marketing and promotional efforts accordingly? What are the customer retention rates for our different subscription plans, and how AudioStream reduce churn to retain more subscribers? . What are the most effective channels for customer acquisition and retention, and how can we optimize our marketing and sales efforts to leverage these channels? What is popularity of each artist, album, and song, what trends can be observed with the content being consumed? What promotional offers and discounts are most effective in retaining users? The following are some of the metrics that AudioStream is looking to capture; Creator Metrics: Number of songs uploaded Number of artists using the platform Time spent on the platform by artists Revenue generated from artists Playlist adds: The number of times a specific song has been added to a user's playlist. Followers: The number of users who follow a specific artist or playlist on the platform. Discovery rate: The percentage of new songs or artists a user discovers on the platform. Consumer Metrics: Number of active users: The number of unique users who engage with the music streaming platform. Time spent on the platform by users: The amount of time a user spends listening to music on the platform. Number of songs streamed: The number of times a specific song has been played on the platform. Skip rate: The number of times a user skips a song before it finishes playing. Retention rate: The percentage of users who continue to use the platform over time, often used as a measure of user satisfaction and loyalty. Number of playlists created User engagement: metrics such as the number of playlists created, songs saved, and shares on social media. Conversion rate: The number of users who upgrade from the free version of the platform to a paid subscription. Advertiser Metrics: Click-through rate (CTR)-The number of times that a user clicks on an advertisement displayed on the platform, relative to the total number of times it was shown. Cost per click (CPC) - The amount of money an advertiser pays the platform each time a user clicks on their advertisement. . Number of ads served - The number of advertisements being served and when they are being served Conversion rate - The number of users who make a specific action after clicking on an advertisement, such as signing up for a free trial or subscribing to a premium service. Advertisement Impressions: the number of times an advertisement is displayed on the platform. Advertisement impressions are a measure of the reach and visibility of an ad campaign. TASKS: Task 1: Data Warehouse Design: Follow Kimball's four step approach to create a data warehouse design. You must determine the required number of dimensional models, with justification. Each dimensional model should be further improved by applying fact table and dimension table techniques. (15 Marks) Task 2: Describe five insights you can derive from the dimensional model that you have created which support the business needs. (5 Marks) MARKING RUBRIC: Criteria Task 1: Kimball's four step approach (5) Task 1: Dimensional Model (8) Task 3: Data Warehouse Design- Business Insights (5) Clear, concise, and effective use of Kimball's approach Report Format and The required number of dimensional models is determined and justified, and fact table and dimension table techniques are effectively applied Five Insightful and relevant business insights are described in line with the created dimensional model The report follows a clear Presentation (2) structure, makes good use of language, and is well-formatted and professional in appearance. Effectively uses visual. Mostly clear and concise use of Kimball's approach, missing some necessary elements The required number of dimensional models is determined and justified, but fact table and/or dimension table techniques could be always effectively applied applied more effectively Three to four insightful and relevant business insights are described in line with the created dimensional model C The report follows a logical structure, has errors in language, presentation, and effective use of visuals. Somewhat unclear or unconcise of Kimball's approach, missing several necessary elements The required number of dimensional models may be unclear or insufficiently justified, and fact table and/or dimension table techniques are not One to two insightful and relevant business insights are described in line with the created dimensional model The report is somewhat adequate, but still has noticeable errors and inconsistencies. D Has made reference to the approach but has not used it effectively The required number of dimensional models is unclear or unjustified, and fact table and/or dimension table techniques are not effectively applied Business insights are limited or poorly described and not in line with the created dimensional model The report does not follow a logical structure is poorly formatted, contains errors, and is not presented well.