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

[@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!

[@Nahassapemapetilon66]

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

[@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. ;)

[@josemario8928]

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

[@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!!!

[@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

[@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!

[@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

[@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

[@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.

[@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

[@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

[@jaimelincovilc.3149]

Great explanation: simple and well structured.

[@rabbilbhuiyan5666]

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

[@absentdata]

Glad to hear that

[@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)

[@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!

[@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

[@Negi_From_Pauri]

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

[@bushmaster75]

It's great. Very insightful and end to end project indeed. But how to add this project into your portfolio? In other words how could you showcase this to potential employer? Thanks

[@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.

[@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.

[@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 ?)

[@mohamedabdo-xg6tw]

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

[@absentdata]

Awesome, thank you!

[@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!

[@deepakrai6824]

Completed this project, thanks buddy!!

[@absentdata]

Fantastic. I hope you like the other ones also!

[@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!

[@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.

[@nazmussumon5105]

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

[@jaimefernandezgomez3709]

This content is gold

[@Codyflips]

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

[@absentdata]

Glad it helped

[@sowjanyakake5720]

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

[@rizzrak]

Really good tutorial / walkthrough of a project!

[@absentdata]

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

[@saidurrahman9480]

Great things to start 💙

[@jennywang3315]

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

[@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

[@adamzachary6947]

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

[@absentdata]

Glad you liked it!

[@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!!

[@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

[@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

[@user-jc3gd3sq4l]

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

[@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.

[@yashlokhande2859]

The discount you multiplied should be (1-discount), so it will give the exact numbers.

[@alexanderarmas3442]

Yeah, otherwise you only take into cosideration the discount amount. Great observation.

[@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

[@swatik777yandexru]

Very simple project :) Very good explaination!

[@absentdata]

Awesome

[@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)

[@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

[@Latif127]

Very good tutorial, thank you.

[@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?

[@raxotob7504]

@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?

[@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

[@princedjll4912]

Interesting and great idea!

[@absentdata]

Glad you think so!

[@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.

[@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?

[@mohamedabdirahman8395]

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

[@shravanramakrishnan8113]

Amazing tutorial, thank you so much !! .

[@orunmijakaosarat]

Thank you

[@cookiesleague]

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

[@nagulmeerashaik-br5tw]

Thank you @ Admin

[@YogChaudhary-r7j]

grate analysis parts of SQL

[@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

[@mmadubugwujoseph6208]

thanks for the walkthrough

[@absentdata]

You are most welcomed!

[@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.

[@miltonthegreat6520]

You are the best!

[@ozonefriendlyman]

The revenue calc is incorrect, adr * discount gives the discount value. It should be (1 - discount)

[@miray8939]

I noticed this also in my analysis,

[@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.

[@realtrendingfacts]

@Absent Data can you make a proper data manipulation video series using SQL, I've searched everywhere and it seems there aren't many sources available online that dig deep into data manipulation using SQL.

[@monicaekka6300]

helped alot... thanks ...

[@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.

[@yvonnechen3377]

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

[@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.!

[@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

[@tampham9564]

Thanks for your work!

[@SMART_Accounting]

Great tutorial!

[@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?

[@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

[@hechosrandom]

Excelllent , thank you!

[@absentdata]

You are welcome!

[@bodymohmed6493]

why not apply this filter for me>>??

[@gideonkayima5757]

Hello sir, this is much appreciated and very helpful project. However, I was enquiring if you can share the dataset so that we can also practice on it. Thank you

[@absentdata]

The dataset is in the description. :)

[@bit1733]

@@absentdata URL is www.absentdata.com/hotel_reve...

[@teamsurvival6172]

great work man keep it up please make another portfolio project

[@Muyiwa-p3e]

Thanks for the video. However, I feel you were basically talking to yourself while creating the visuals because I noticed you were in a hurry to complete it and this makes it challenging to follow you through the visualisation.

[@absentdata]

Thanks

[@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

[@elrevesyelderecho]

Thanks! Good video

[@absentdata]

Glad you liked it!

[@coinhawk]

It's crazy, bi experts have a short term window to be savants then AI

[@thinhle7033]

Thank you so much Bro's

[@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 ------------------------------

[@lingambalakrishna]

Knowledgeable thank u

[@absentdata]

Thanks and welcome

[@jayashukla4799]

Hi, this is wonderful. May I use this in my portfolio projects?

[@absentdata]

Yes of course!

[@jayashukla4799]

thank you so much

[@piyushramkar9404]

you are making great work sir which is helpful to me and also helpful to lots of people who trying to gain knowledge. As a beginner should I focus on SQL server or My SQL OR Both, also which tool mostly used in data analysis/data science industries.

[@absentdata]

Either one is fine because the syntax is virtually the same with some exceptions. It all depends on the organization you work for cause different departments and companies may use MYSQL, SQL, POSTGRES, TSQL..etc. So just learning SQL covers most of this.

[@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

[@zikriasmani3626]

Thank you for the video sir. I have a question during the visualisation process. The revenue appear as "Sum of Revenue" instead of "Revenue". How to solve it?

[@soniapatra2460]

right side under the fields simply type it where "sum of revenue is written"

[@daviedotcom]

I am trying to learn sql and I love how you are explaining everything. I tried to use union exactly as you did but I keep getting the same error. "Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists." I honestly don't even know what this means or what to do next. I've been stuck for two hours. I just want to combine my three tables. I also looked up other ways. I keep running into issues using inner join and full join.

[@absentdata]

Thank you for the compliment. This error usually occurs when you have different number of columns in the Union. I would start from scratch again if you following along in the tutorial.

[@ingridbena]

@AFIFI EL-KADY Thanks, I realized I forgot the ")" at the end of [ '$2020$' ]

[@ngoziokonkwo5280]

make sure you're writing the query on the database you created by right clicking on the database name and select new query. that resolved my error

[@chinyerenwigwe5166]

@@ngoziokonkwo5280 I am trying to use MS SQL but I have always been stuck even after uninstalling and reinstalling, I keep getting an error message like this "Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].['2018$'] ( [hotel] nvarchar(2..." failed with the following error: "Culture is not supported. Parameter name: culture 3072 (0x0c00) is an invalid culture identifier.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."

[@rejeeshthayyil]

Hi Thank you very much for your tutorial. sir, can you please explain me how you copying and pasting visualization. Thank you.

[@absentdata]

Copy and pasting which visualizations. You can export them out or you can just you the snipping tool and import it

[@papakow1346]

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

[@mdfaisalhossain9317]

wow your explanation is very good. do you have anything specific to sql where you teach from beginning ? Thanks

[@absentdata]

I will have some videos soon!

[@godsloveegbebu7878]

Much love

[@zhangmr7955]

Dear Sir, while I import the excel file it appears the wrong message: 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 ------------------------------ How could I fix it?

[@royaltzade]

Thanks for this