In this blog post, Madushan Dahanayake talks about how he self-acquainted the features of Power BI as he was looking forward to a career in business intelligence. Madushan joined Fortude as an Associate BI Consultant in 2017 March after graduating from University at Buffalo, New York.
During the last few weeks, I have been getting myself acquainted with Power BI and its features. Finding data sources (to practice with), cleaning them, creating data models, and creating reports and dashboards have been very interesting. Ultimately, I was given the challenge to develop a Stock Movement Intelligence Solution for the ‘WideWorldImportersDW’ sample database for SQL Server 2016. You can access this database here.
This is a snapshot of the database set up:
The database is already set up with Dimension tables and Fact tables. Hence, coming up with the data model was not a challenge. The only thing left for me was to figure out which fields I need to get together to help me solve the problem. The ‘Quantity on Hand’ field was the most important as it provided the most recent stock information we had.
In the end, I constructed the following table.
Then I had to figure out a way to filter the products that were due to be filled on the stock. I wanted to give business users the flexibility to choose the information they wanted to display on the reports. As a result, I thought of creating fields that were ‘sliceable.’ Using the IF function in two Calculated Columns allowed me to categorize the data at ‘Reorder’ and ‘Target Stock’ levels.
One of the main objectives of this project was to alert hypothetical report users when a certain stock item goes out of stock. With the data I have, the easiest solution to this was to find the average monthly sales for an item and to calculate how many months it would take to deplete our current stock. To make it even more useful for the business users, I further refined to show the number of days left to reorder an item, considering the lead time for each product.
At this point, I was quite happy with how the solution turned out, but I wanted to see if I could do further analysis that might be useful for business users. Again, based on the data I was working with, the only other insight I could think of was to try and account for seasonality. Where earlier I calculated average monthly sales for an item, this time I calculated average sales for a given month and an item.
By doing this, I could show seasonal demand for any item. A business user can now see how the demand would look like for any item up for re-order in the coming months and adjust orders accordingly.
Additionally, I created sales and profit analysis reports on the same database. For this, I used a comparatively straightforward process where I pulled data from the sales fact table.
The final Stock Movement report looked like this.
There you have it. My first documented foray into BI. Hope you found it educational and interesting.
Author: Madushan Dahanayake Associate Consultant – BI, Project Delivery
