Case Study 2
Friday, October 1st, 2021
Background
Flexi Travel is a bus company that provides shuttle services between cities. They are a company with a passion to provide their clients with the best possible service. The owners wanted to reduce the number of accidents that involve their buses. They use OneDrive for Business to store Excel sheets they use to record the accidents. The figure below shows a sample of the data.
They now have more than four years’ worth of data.
Challenges
Flexi Travel does not know where to start the analysis. They require an effective solution within their budget.
Requirements
They want data to be fetched from OneDrive for business and produce visualizations. Considering the situation, where there was a budget constraint they expected to see the total number of accidents by day of week and time. Further, they want to compare year on year, quarter over quarter, and month over month statistics. The visualizations should be refreshed automatically to reflect updates to the OneDrive data.
They also want to predict future accidents using regression analysis. Finally, they want to track income and expenses.
When the data was imported into Power BI, Power Query did not correctly determine the column headers for the accident tracker table. The fifth row contains the headers, as shown below.
Case Study 1
Friday, October 1st, 2021
Background
Sunrise Group Inc. is a retail giant that has been dominating the market for more than 40 years. It has around 150 stores in 6 states. The organization is known for being innovative, it has collected a massive amount of information but it is failing to utilize it fully. Sunrise Group Inc. has a variety of technologies to collect and manage the information which includes a data warehouse, an in-house business intelligence (BI) system and other reporting tools. Their daily sales are recorded manually in Microsoft excel. A sample of daily sales data is shown below.
Each branch is managed by a Branch Manager. The details of each Branch Manager are stored in a separate table that will be linked to the sales data by branch code. The database also includes a date reference table. A sample of this table is shown below:
In addition, they have an SQL Server Analysis Service cube that has millions of rows and SQL Server 2016 for the accounting system. So they rely heavily on the IT department to extract insights from the data they generate. Sales reports are sent from the branches to the head office by close of business every day.
Challenges
The sales figures sometimes reach the head office late and it becomes difficult to track the daily sales figures. The reports do not have the standard template, which makes consolidation very difficult and full of errors. The SQL Server database is not being utilized fully to produce real-time reports.
Requirements
With these challenges, the Sunrise Group Inc. have settled for Power BI as their choice and they have these requirements among others:
- The c-suite requires a visual that shows sales by region.
- Branch Managers want to drill down through monthly to daily figures.
- Branch managers should be able to view near real-time data.
- The Branch Managers must only see sales of their respective branches only.
- The Sales Managers require a visual to compare current year and previous year sales by date.
- The users want the flexibility of creating visuals to answer questions that do not have visuals on a report or dashboard.