Data Analyst Portfolio Project #1 | Build a Database | Develop SQL | Create a Dashboard

[@absentdata]

Made two mistakes in the video. At 26:35, I added weekdays twice. You should add weeknights also. Additionally, At 27:20 for discount it should be (1-discount)

[@brilliancetventertainment9416]

Can you make a .bak file for the database and upload it to make it easier to get up and running? Loading via the data wizard is very problematic as I am using Studio 2019

[@jaykgitau8048]

kindly write out the formula to use for revenue..or what is the correct revenue...lol :,(

[@maxblack396]

Why discount should be (1-discount)? Please explain

[@Mahmoudalgindy]

​@@brilliancetventertainment9416 Hi, it seems you are using Office 64 bit, to solve that issue it's very simple but known to many Open: SQL Server 2019 Import and Export Data (64-bit) or the exe file: DTSWizard.exe It will be in this path: C:\Program Files\Microsoft SQL Server\150\DTS\Binn\DTSWizard.exe Please let me know if this solved your issue.

[@CHANDU_TG]

@@maxblack396 Revenue amount will be after discount i.e. 80% revenue and 20% discount.

[@donbogdala5428]

By far the best SQL/Power BI training class thus far!

[@absentdata]

I love that comment. Share the video with anyone you think it helps

[@Nahassapemapetilon66]

This is exactly what I've been looking for months!

[@vivekpandya8106]

The database construction, SQL construction is master piece. For a beginner its great way to start the analyst journey. Thanks for making quality video❤

[@absentdata]

You're very welcome!

[@MRHaro188]

This channel is one of those hidden gems! Thanks for the walkthrough!

[@absentdata]

Thanks for the compliment. Spread the word. Let's grow the channel!

[@JustIgnorantHQ]

I totally agree with you. I am a beginner to Data Analysis and this has helped me understand SQL to PowerBi like a 5 year old. ;)

[@makeitbueno]

Sql union, join하고 power bi로 넘기고, interactive dashboard로 담아서 stakeholder들이 필요로 할만한 insights를 담아주는 과정이 깔끔하게 술술 이해됐어요. 몰입해서 보게 된 흐름 좋은 강의였습니다!

[@josephjoestar995]

One of the best projects walkthroughs on KZbin, simple dataset but clearly walks through the project as well as attaching to a real life dummy scenario too e.g. what do our stakeholders want to see and what questions should this data answer, well done and thanks for the vid 👍

[@absentdata]

That is such a great compliment. I'm very happy you found value in it!!!

[@skaguy7]

I completed the tutorial. I had issues downloading MSSMS and connecting to the server. I figured that out. Then, had trouble importing the data. I figured out that problem. Smooth sailing after that. Great practice project. Thank you!

[@absentdata]

Awesome. So glad you were able to get things sorted. You should also try the other projects now

[@MrHassaballahJR]

Did you also have a problem with importing thr data? Can you run me through what you did? I keep getting an error when importing from excel

[@JaRoJoCT1021]

Thank you so much! Stumbled across this video, late one night. Watched it through first; was so easy and clear to follow! You made it all make sense and click for me!!! Thanks again!! Fastest I ever hit the subscribe button lol!

[@sparklingtravellers]

Great way to learn the actual flow of Data from SQL to power bi.. Thanks mate for your support...

[@sahilsingrajput8834]

can you please help me what should i write at revenue formula at 26:35

[@gabrielroomvm6209]

This is simple and great, Im doing this for my personal portfolio, thanks a million!

[@honeydelhivlogger8047]

kzbin.infokWOBZOpyyTE?feature=share4 power bi Dax functions

[@josemario8928]

The correct DAX for Revenue is: "=([stays_in_week_nights]+[stays_in_weekend_nights])*([adr]-([adr]*[Discount]))"

[@rabbilbhuiyan5666]

Very useful resources to get familiar with complete data analyst project using sql database and powerBI tools

[@absentdata]

Glad to hear that

[@ianfang7582]

Thanks for such a great video, But there are a few concerns I would like to point out, first of all, in regards the Revenue and I believe we can't just simply multiple days and adr, the total should subtract those reservation_status =" Canceled". Secondly, parking lot size question about the percentage of occupation soulld be equal "car parking spaces" /" reservation status "instead of "car parking spaces" /"Total nights" because no matter how many nights you stay, one reservation only corresponding one parking sport.

[@jaimelincovilc.3149]

Great explanation: simple and well structured.

[@Negi_From_Pauri]

love u the great man i have never seen this type of project anywhere in any youtube channe

[@jaimefernandezgomez3709]

This content is gold

[@deepakrai6824]

Completed this project, thanks buddy!!

[@absentdata]

Fantastic. I hope you like the other ones also!

[@mayurvirwani]

Thank you for sharing this Project sir. Very helpful and insightful.

[@absentdata]

Great! I am glad you like. Feel free to share it with someone who it might help.

[@MohamedHassan-qk7po]

Revenue = stays_in_weekend_nights+stays_in_week_nights *adr you write it stays_in_week_nights +stays_in_week_nights in adding Revenue column in Power BI it's 10.23M not 14.79M

[@santiagorubio2980]

Great video! I think there's a mistake in minute 27.24. You sum the same columns (stays_in_week_nights + stays_in_week_nights) Instead is (stays_in_week_nights + stays_in_weekend_nights).

[@brandonsager223]

Yes

[@dadimontv8414]

Absent Data after UNION you lost 40k rows. In [8:43] you can see totally is 141k rows and after union [9:10] is only 100k rows. I think you should use UNION ALL because UNION add only distinct values(rows) by default.

[@amirk692]

This video is the end of my search for how to make a project or how a project is made in data analytics

[@absentdata]

wooohoo! Please share this video with anyone who you think it will help!

[@saidurrahman9480]

Great things to start 💙

[@papakow1346]

9:13 can I know why you used union instead of union all. Thanks 🙏

[@irinaodinokova4157]

Great tutorial, but i am not sure if Revenue should really be including the bookings that have been cancelled? There is a field in the dataset called is_cancelled, and common sense is telling me that the records identified as is_cancelled = 1 should not be accounted for (customers most likely didn't pay for those bookings)

[@mindful9114]

43:25 after clicking slicer did not get a bar slicer like in the video, I got a list/dropdown like the hotel dropdown next to it. how to change it to look the same as in the video

[@absentdata]

Click the the down carrot symbol in the upper right of the slicer and you will be able to choose "Between" which will give you a horizontal slider.

[@toddallred5943]

I had the same problem. If I click on the slicer button first then drag reservation_status_date into the slicer then it will create a slider. But if I drag in the reservation_status_date in first then it does not give me the option to change it to a slider (only a drop down)

[@rizzrak]

Really good tutorial / walkthrough of a project!

[@absentdata]

Thanks! Please share the video and help the channel grow.

[@CB-yc6wz]

Keep getting the error below when trying to import the Excel data "The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data)"

[@mohamedabdo-xg6tw]

Great Video, thank you for sharing end-to-end project! I 've subscribed from this Tutorial.

[@absentdata]

Awesome, thank you!

[@SashaGalashka]

Oops. Am i the only one who got stuck at the very beginning? Could anyone please help me to figure out what should i put into "Server name" field at 2:30? What i have is an empty one, and anything i add (name of my PC?) in it causes an error. How to connect to existing server or create a new one? Thank you.

[@lucasdelange4153]

I have exactly the same problem! Could use some help?

[@lucasdelange4153]

Solved it: kzbin.info/www/bejne/Y3_EZ4iYbsx2hdU Kinda strange this isn't explaiend in the video, but it does seem logical.

[@SashaGalashka]

@@lucasdelange4153Thanks a ton, Lucas! I literally have been waiting for solution :)

[@nazmussumon5105]

Very good project. Thank you for the good work. Kudos!!!

[@nehasharma-jd9kc]

I feel there is one more mistake at 36:27 where took adr in place of avg of adr, the same error made for avg of discount. pls, correct me if I am wrong.

[@Codyflips]

Thankyou sir I love how calm methodical the video is it really helped me

[@absentdata]

Glad it helped

[@ngehpeace6772]

Thank you so much for this vid. it lifted some weight off my shoulders. Please can you make a video on automation. From database to powerbi? Thanks in advance

[@absentdata]

Great suggestion!

[@YogChaudhary-r7j]

grate analysis parts of SQL

[@mohamedabdirahman8395]

Having problem in loading the excel file into mysql workbench. any help?

[@adamzachary6947]

Man, great tutorial :) Thank you so much! It helped me.

[@absentdata]

Glad you liked it!

[@teamsurvival6172]

It should be a parameter in the revenue table stays_in_weekend_nights you repeated "Stays_in_week_nights in power bi. but a great work! hats off for you. Please make Some other portfolio project ASAP.

[@husseinadam4724]

I'm subscribing thank God I found this channel after losing my direction in this KZbin

[@absentdata]

Love that comment!! Glad you found value in the video!!

[@yvonnechen3377]

It turns out you need to install sql server to use the ssms

[@estherchichi9651]

Thanks for the video. Pls 🙏, I need help. At the point I was to import the data, I couldn't find "SQL server native client" in the choose destination. And I couldn't go further from there. Pls I need your help

[@maestrorobi4467]

Have you found a fix? I'm experiencing the same.

[@jennywang3315]

Thanks so much for this video, it's tremendous helpful and useful!!

[@kacybennett180]

At 41:40, wouldn't it be necessary to know the Total car parking spaces available to truly confirm if we need to build a larger parking lot? That way you can do required/total to get the % that is filled. I am loving this video by the way thank you!

[@absentdata]

For sure, this is would a great addition to the analysis!

[@irshaadmohammed9024]

do you know what is the total car space?

[@yaseenkalo1741]

Hello, Firstly it is a great project for beginners. 26:20, I think, you did a mistake if I am not wrong. Actually you add stays_in week_nights twice instead of adding stays_in week_nights and stays_in weekend_nights. Thanks.!

[@tharindusamarakoon8831]

Hi! Thank you for the guide! It gave me a lot of valuable insight. Can you please provide the kaggle link to the data set? I know that it is hosted in your website as well but I would like to view the kaggle dataset as well. Thank you very much!

[@EllaZhao-bh8vc]

can you please introduce how to use a mac system to follow this instruction? I was stuck in the first step 😂 (by the way, can you give me some personal courses and be my tutor ?)

[@etothez9898]

Wow, thank you very much for this walkthrough. This is exactly what I was looking for :D

[@sahilsingrajput8834]

can you please help me what should i write at revenue formula at 26:35

[@Voyager_AU]

I tried to do this in MySQL but the importing of the files was taking FAR TOO LONG so I downloaded MS SQL Server. First time using that flavor of SQL so this will be a learning experience on that front as well.

[@absentdata]

This is a great alternative!

[@honeydelhivlogger8047]

kzbin.infokWOBZOpyyTE?feature=share4 power bi Dax functions

[@thetasworld]

10:00 is that a temp table or a cte? Also I find it a little weird that we get adr values on canceled reservations and 0 adr on not canceled reservations...

[@aish000]

Cte

[@swatik777yandexru]

Very simple project :) Very good explaination!

[@absentdata]

Awesome

[@successfulvictorypublisher6090]

Thank you so very much for this excellent training Sir! Keep it up!!

[@absentdata]

Most welcome!

[@ememobongumoren]

Hello, Thanks so much for this great tutorial. I was following the tutorial and realised my results were significantly different. I want to confirm if the dataset was updated after the time of the video. I want to be sure I am on the right path. Thanks again

[@zamreenhynus]

Hi, I found some of my values were different as well. Could you tell me your values so we can compare them?

[@Latif127]

Very good tutorial, thank you.

[@tahjscott429]

great video. just started learning sql and data analytics but i watch these videos and i get a better understanding of what i am learning. question though, why not clean the data? for example where there were null values etc, or is that not always necessary?

[@absentdata]

Its depends on the use case. However, Power BI is designed for data cleaning. Sometimes, it's easier adlnd more prudent to do it in SQL. However , for a small dataset, its easier do any needed manipulation in Powe Ni

[@TaherYounis]

at start more thank you , at the second i start work as data analyst so i need excel file to start training and apply steps as i learn it from you , ath the last more thanks agine

[@absentdata]

Congratulations on your data analyst role. You can get the data set in description

[@sowjanyakake5720]

reservation_status_date datatype is text. Before doing Revenue Line chart need to change the data type to date.

[@princedjll4912]

Interesting and great idea!

[@absentdata]

Glad you think so!

[@mmadubugwujoseph6208]

thanks for the walkthrough

[@absentdata]

You are most welcomed!

[@cookiesleague]

after joining the two tables my discount only has a value of 1 or 0. 1 for complimentary and 0 for everything else

[@samiatsoliu]

Thanks for the shared knowledge. Kudos to you sir. I don't have any experience in data analysis, am able to do the Excel project properly and we'll cleared but power BI and SQL is not properly cleared. Am from NIGERIA, don't know if you have someone here that can explain for me. Am trying to download power BI but not going through so as to continue the lesson learnt so far. Thank you very much sir

[@absentdata]

You can't download the power bi desktop? Try this link www.microsoft.com/en-us/download/details.aspx?id=58494

[@shravanramakrishnan8113]

Amazing tutorial, thank you so much !! .

[@harpcourt]

I am getting an error "The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data)" I've installed accessdatabase_x64 for 64 bit OS and still not working when trying to import excel data into SSMS

[@ramabijigiri6433]

Hey I too got the same problem did you find the solution

[@elrevesyelderecho]

27:15 why a custom column? A measure would work better? 🤔

[@absentdata]

Measure or column would work. I wanted to keep it simple by using similar actions from SQL to Power BI. However measure works perfectly and keeps table smaller.

[@Xaxobit]

@AbsentData Thank you for this wonderful piece. Please for adr and Discount it looks like their trend is for the sum instead of the average values. Am I missing something?

[@yashkhanna6825]

why is revenue multiplied by discount this would not give the real revenue after discount it should be multiplied by (1-discount ) correct me if i am wrong

[@mustaphaabubakar8336]

while importing the data got the error pre-execute stopped says culture is not supported

[@saifilicious1749]

can you please fix the bugs of your website?70% of the website is covered with ads and when i remove the add the entire portion becomes white

[@techsnail8581]

good job but i think u ve made in error in calculating the revenue = ((stay_in_weekend _night + stay in week ) * adr) * (1-discount)

[@SMART_Accounting]

Great tutorial!

[@marahsaleh7163]

thank you so much great work , but when i download the dataset file and open it by mysql nothing apeare in the workbench , why???????????

[@aidanthompson5053]

hi, can you post me the link to the excel dataset, i can't see it in the description

[@luckykumar1665]

In the dataset, I can see the 2nd column as "is_canceled". for this column we have true=1 and false = 0 value. Should not we have considered that for actual revenue calculation? Just need some clarification on my understanding.

[@absentdata]

Of course, you can consider it.

[@melancholiadementia]

revenue calculation = ([stays_in_weekend_nights]+[stays_in_week_nights])*([adr]*(1-[Discount]))

[@simplesingh5334]

Hey! I accidentally wrote 2018 instead of 2019. And even after correcting it the sum command would only give me the sum of 2018 and 2020. What could possibly have gone wrong ?

[@melancholiadementia]

@@simplesingh5334 check the union of tables in power query, it's the only idea i think

[@honeydelhivlogger8047]

kzbin.infokWOBZOpyyTE?feature=share4 Power bi dax

[@019_devarghachattapadhyay5]

Why 1-discount ? Could you please explain it in brief?

[@martaplp]

@@019_devarghachattapadhyay5 By using (1 - [Discount]), we're calculating the discount value correctly as a percentage off the original price. E.g. if the discount is 0.2, it will calculate as 20% off, resulting in 0.8 (1 - 0.2) of the original price.

[@Yosh-b9o]

i'm quite confused about the filtering out of 2014 because the arrival year is still 2018 and 2014 is only the reservation date

[@lautaropintos8457]

hi, first of all, I really enjoy this video and it was just so much helpful to understand the process to do a project of analysis. I have 1 question, with the union command, don't you leave some register out of the set? because with all the records from the 3 tables, the row count is much higher that the row count after doing the union

[@aish000]

Yes, so it should be Union All

[@raushanpandey4553]

What in case of excel data updates every day..and we want to create data base

[@teamsurvival6172]

great work man keep it up please make another portfolio project

[@miltonthegreat6520]

You are the best!

[@okdokd3385]

Thank you so much for this great video. very kind of you. I am unable to correct the mistakes. Please help. Thank you very much again.

[@nagulmeerashaik-br5tw]

Thank you @ Admin

[@missmalpractice]

How did you duplicate the line shape down at around 28:43?

[@ramabijigiri6433]

Hai sir Iam facing a problem while importing data "The Microsoft. ACE.OLEDB.16.0 Provider is nit registered on the local machine.(system.Data)

[@bodymohmed6493]

why not apply this filter for me>>??

[@MrHassaballahJR]

I have been stuck for the past 2 hours trying everything possible (with chatgbt) to solve the error Microsoft.ACE.OLEDB.12.0 not found. I dont want to jump right into powerBI like others since the whole purpose for me is to get expereince in building databases and integrating that into my work ANY help would be appreciated to jump start this

[@divinejude2953]

I keep getting this error "Microsoft.ACE.OLEDB.16.0 Not registered on the local machine" when I try to import my Excel file I also converted it to CSV and try to import but some rows keep dropping Please I need help.

[@emineomur2952]

Hello, Thank you for the video it means a lot to me.I have a question.Why did we used join statement for market segment column we already had that column in hotel table?

[@aish000]

To fetch the discount column as we didn't have it in the hotel table.

[@johnatanezequiel1713]

i had a problem while loading the file on SSMS, so i tried to convert the workbook into excel 97 - 2003 but the max of rows is 65k so i lose some of rows on the [2019] since it has 79k rows, what to do in this case?

[@diegoj9056]

I like SQL, but I think Power query works more fluid for me

[@absentdata]

Good Point. Just all depends on your access and resources. The SQL will help you limit the am out of data coming into Power BI if required

[@diegoj9056]

@@absentdata yes, sometimes it's not possible to use M language, for example with direct query

[@mohitvind2623]

im not getting native client option in dropdown list while importing please help

[@putulsaini6788]

anyone please help me to import this excel file into sql serve! it showing error "The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data)" I installed but still showing same issue.

[@MrHassaballahJR]

Have the same issue as well. Did you manage to solve it?

[@putulsaini6788]

@@MrHassaballahJR I just directly imported in normal way as we do

[@mucahitugurlu7324]

I learned a lot of things in this tutorial sir and I really appreciate it for that, but the problem is you're going a bit fast.. especially at the visualizing part. As someone beginner, it was a bit challenging for me.

[@absentdata]

Thanks for the feedback! I am glad you found it challenging but valuable.

[@joshdee7639]

You should try out beginner tutorials then. Power BI is not for a 'beginner' to learn

[@CreamerDylan]

I'm having the hardest time with this error: TITLE: SQL Server Import and Export Wizard ------------------------------ The operation could not be completed. ------------------------------ ADDITIONAL INFORMATION: The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) ------------------------------ BUTTONS: OK ------------------------------ Any advice?

[@biscotto]

I am getting an error when I try to import the data TITLE: SQL Server Import and Export Wizard ------------------------------ The operation could not be completed. ------------------------------ ADDITIONAL INFORMATION: The 'Microsoft.ACE.OLEDB.16.0' provider is not registered on the local machine. (System.Data) ------------------------------ BUTTONS: OK ------------------------------

[@ArsamSaleemsMusic]

Hello. Shouldn't the sparklines for adr and discount show the average and not the sum? Is this a mistake or am I thinking about it wrongly?

[@Le3ktrill]

Would there be a reason why the tables do not appear after the importation process? I'm not seeing any of the tables I selected and everything said successful at the end.

[@tampham9564]

Thanks for your work!

[@stevenekelem]

am having error connecting my excel dataset to sql server

[@fust2rol5]

Hi there, great tutorial. Just have a question, I have installed on my laptop mysql and postgte SQL. Can I use them to follow the course or I m forced to download sql server? Thanks upfront

[@absentdata]

Yes but some of connection from MYSQL will not be the same as Microsoft SQL Server Studio

[@araijeahmed6127]

I'm asking a question! Why do I utilize SQL Server when you have Power Bi and can join and combine all of your table with it?

[@absentdata]

A few reasons. It most organization data is stored securely in database. For data governance and security, having a single source of data in a database, data lake or data warehouse is needed. You can bring in the data with manipulating it from the database to Power BI, However, if you can improve the performance by structuring the data in the database, you can improve the performance of the dashboard.