Power BI Challenge 5 - Optical Data

Hi All

Welcome to the fifth instalment of the Enterprise DNA challenge.

The first four challenges were beyond expectation and we want to enhance and build on the start we have made to date.

To learn more about the challenge and how it works be sure to check out the forum below!

The challenge was created to help embed and improve contestants’ power bi skills. We encourage beginners and newbies to participate as one of the fastest routes to mastery is to tackle problems and get hands on.

The challenges are designed to mimic scenarios that are typical of what an analyst will be working on in power bi. We have a wealth of knowledge at Enterprise DNA and as many of you will have seen in the posts no two individuals tackle the problem in the same way.

While there is a competition taking place this shouldn’t detract or discourage anyone from entering as the core aim of these challenges remains to be to help improve your power bi capabilities. We have seen from the entries to date, that with each challenge users work is improving and the standard of reports they are creating is improving.

Opportunities to have your work reviewed by experienced campaigners and experts is normally very limited however given the format of the challenge and the Enterprise DNA mantra there is every opportunity to have your work reviewed not only by the experts but the CEO Sam Mckay himself. I think the quote below from one of the experts sums this up perfectly.

‘I built complex Excel models for 30 years and Bill Gates never provided one single comment on my work, but Sam just wrote an entire page on a report I submitted, and he did the same for everyone else. That’s amazing, and truly an opportunity that people will probably never experience in any other context.’

So I Just want to reiterate that these challenges are designed for all to participate in and there is no barrier to entry. The biggest competition needs to be with yourself to use these challenges as stepping stones in your development.

So if your sat on the edge or feeling as if the work being submitted is beyond you I encourage you to make a start and get involved you will thank me later.

We have been listening to feedback from contestants and been working closely with some of the Enterprise DNA team as to how we can improve the challenge.

Timing

The pace of the challenges was becoming a little relentless and for some it was difficult to complete the task in the given time. So we have decided that challenges will be released on a Monday and run for 14 days so for challenge 5 that means;

Date of release: 03/08/2020

Close Date: 16/08/2020

Winner Announced:19/08/2020

We are encouraging all participants to document and share how they went about their development hopefully the additional time will allow for more of you to get involved with this aspect.

Remember: The weekly winner will receive a complimentary membership to the platform that they can share with anyone and the opportunity for your work to be showcased across our channels.

THE BRIEF

You are working at large opticians and they are having some issues with their current appointment system and can’t access their reporting.

The supplier is working on fixing the issue however its going to take them some time to fix the issue.

Penny (Optical Manager) has been working with IT to try and formulate an interim solution.

IT have suggested that going forward reporting should be done outside of the appointment system in a specific business intelligence tool.

Penny is yet to be convinced that this is the right solution and has little experience in BI and doesn’t want to become reliant on IT for reporting.

IT have manged to persuade her that Power BI can provide a solution which is fit for purpose and allows her to self-serve.

IT have manged to take an extract of data from the appointment system for the proof of concept.

They have now asked you as the Power BI expert to create a model that Penny and her team could easily navigate and demonstrates the capabilities of Power BI.

Penny has written several requirements that she is expecting from the solution:

As a user I need a report that allows me to see patient details- Age, last appointment, smoker, driver, when the next appointment is due, private or subsidised patient, eye test results.

Age needs to be calculated

The next appointment due is based on the following logic.

Age under 25- require an eye exam once a year

Age 25 upto and including 55- once every 6 months

Age greater than 55- should be having an appointment once every 3 months

The next appointment due is only suggested it is possible that a number of patients will often visit before their appointment is due however we want to ensure we are complying and are aware of patients who are due for an appointment.

She would love for there to be an indicator or formatting to show her which patients were due for appointments.

She doesn’t currently have this capability in her reporting but would love to be able to generate a list of patients needing appointments which she can then share with the customer contact team and help plan for resourcing.

When she selects a patient, she wants to see all their historic appointments not just the most recent. As part of the compliance framework they need to ensure that the opticians are carrying out the required tests and due diligence. She currently monitors this bye checking the left eye and right eye values from the appointments and see the difference if any between appointments and ensure that random numbers haven’t been entered.

The ball is now in your court and you need to help convince Penny that Power BI is the reporting tool for her and her team.

SUBMISSION DUE DATE - Sunday, 16th August 2020 (PST)

Please can you all submit your PBIX files to powerbichallenge@enterprisedna.co

The data set can be downloaded below.

Optical Data Set.xlsx (1.1 MB)

Best of luck!

Any issues or questions please reach out.

Haroon

Enterprise DNA

15 Likes

Very unique data set. Excited to jump into this

2 Likes

No so easy. I like it !

2 Likes

Looks challenging this time. :+1:

1 Like

Welcome @MudassirAli great work on the previous challenge. Looking forward to reviewing some more awesome reports :).

Regards,
Haroon

1 Like

Thanks @haroonali1000.
It’s good that we have enough time now to submit the reports. I am confident that there will be awesome reports this time around.
Fingers Crossed!

2 Likes

Hi @haroonali1000, It is a great data set and eager to dive into it.
Quick question, while doing some preliminary analysis, I noticed that the column values for left eye value and right eye value on the Excel source are Decimals with one decimal places:-


When loaded to Power BI they have more than one decimal places :-

Is it ok to round them of to two decimal places instead like the following? :-

Thanks.

Hello @Neba.

Good spot.

Normally the values are given to 1 decimal place, using 2 wont detract from the analysis or result in you being penalised. So what ever you are comfortable with.

Regards,
Haroon

@haroonali1000 Great. Thank You.

@haroonali1000 One more thing I noticed is that the appointment dates on the Excel source do not show time, but, when I loaded the data to SQL Server using SSIS, the appointment dates also contain times of appointment.

On Excel Source :-

Excel Data on SSIS Preview:-

When Loaded to SQL Server after creating a Date & time Column:-

My question is, is it important to use the times on appointment date in our analysis ?

Thanks and Regards.

No @Neba and all other participants in this instance just date is required please ignore time.

Thanks,
Haroon

1 Like

@GuyJohnson Yes the columns can be saved as dates only. I was inquiring if the time stamps are relevant for the report to be built.

Sorry, I’ll withdraw the comment

@GuyJohnson No You don’t have to Sir. It’s a valid point.

Don’t worry about it, the comment was misleading

1 Like

@haroonali1000 - for purposes of this report, what day is TODAY? Are we assuming time zero is as of Monday, August 17th? The first time penny will see our POC … ?

Hi @ChadD, you can assume the 17th.

Regards,
Haroon

Hi @haroonali1000,

Thank you for this challenge. Really had a lot of fun working on it. :grinning:

I know there is more time left, but in a couple of days I have to go to the hospital so I have to submit.

I am looking forward to see all the submissions.

Daniel

9 Likes

Great Work @uriah1977 a very professional looking report.

All the best and hopefully see you back on the forum soon.

Thanks,
Haroon

Thank you very much @haroonali1000,

I was forgotten to post a link to the report, but here it is.

Daniel

Challenge eDNA 5 Daniel Akerina.pbix (741.7 KB)

1 Like